MSSQL peer to peer replication what is the best practice to drop and create multiple table I have a Transactional Replication server in peer to peer topology with 3 server node.
My development strategy was Agile, so that will be a frequency change on the database etc.
Scenario: I need to drop 20 tables out of 100 tables, and create another 30 tables at a time.
May I know what is the best/common practice to do it?
What I can think of was,

Drop all replication article and subscription for 3 server node.
Drop the 20 tables and create the 30 tables on each server node.
Resetup back the peer to peer topology replication to the 3 server node.

(P/S: I am new in this replication technology, do guide me if my step is wrong.)

Yeah, this is the problem with Transactional Replication: every time you change something you need to recreate the publisher and the subscription.
I think your three steps are right.


How to query a postgresql database to determine it's cluster and version (and port)? I can quickly query the database name with SELECT current_database(); but I've been searching for way to also query the cluster name and version with no luck - hoping someone can suggest a solution.

That would be

SELECT current_setting('cluster_name'), current_setting('server_version');

You can get all parameters that way. For automated processing, server_version_num is often better.


SQL Server Lock Escalation clarity required I want to know whether Enabling the Trace Flag 1211 globally prevents the Lock Escalation even in the event of the Huge Memory Pressure.
I have 2 Questions regarding this:-

After Enabling Trace Flag do we still need to alter each and every Table in the User Database to Disable Page Locks.
Suppose if the answer is Yes then we would still need to disable Page Locks at each and every Non Clustered Indexes on the above Tables to prevent Page locks / Lock Escalation.

1: No, the trace flag is enough to disable lock escalation. No need to do an ALTER TABLE for every table. If you had to do an ALTER TABLE, then the trace flag would be meaningless.  Per the official documentation on that trace flag:

Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks.
...
...trace flag 1211 prevents escalation in every case, even under memory pressure...

2: No, no need to disable page locks in order to get rid of lock escalation.
I, of course, agree with the others about how you don't want to use this trace flag. I mainly wanted to provide an answer to the question you posted.


pg_upgrade from 9.3.5 to 9.6 with PostGIS (Windows) Versions

current:

PostgreSQL: 9.3.5 64-bit
PostGIS: 2.2.3
notes: 2.2.3 is the latest available on StackBuilder for 9.3.5;

upgrade

PostgreSQL: 9.6 64-bit
PostGIS: 2.3.1
notes: 2.3.1 is the only version available on StackBuilder for 9.6, although in principle I could downgrade to an archived 2.3.0 from OSGEO's installer archives, but that's as close as I can get to matching versions (which it turns out would not resolve anything anyhow).

DB size (not counting tables under 1MB in size): ~230GB. Not trivial, but not 'big data' either. Large enough that pg_dump would be a monumental PITA.

The Problem

If I enable the PostGIS extension on the destination (9.6) and run pg_upgrade with the appropriate source and destination bin and data directories, I get

New Cluster database "postgres" is not empty
Failure, exiting

This is because the PostGIS extension creates a table (spatial_ref_sys) when installed; the table can't be dropped without dropping the extension.

If I disable PostGIS in the destination (9.6), pg_upgrade fails because the destination lacks the appropriate loadable libraries (hint: libraries to do with PostGIS).

pg_upgrade offers the dandy suggestion that I remove the functions using the libraries from the old installation, which sounds like a recipe for disaster to me.

UPDATE: just tried deleting one of the PostGIS functions (ST_Locate_Between_Measures() - I've never used it and am unlikely to). Can't be done without dropping PostGIS, which can't be done because all the geometry columns in the data have GIST indexes.

I had tried to make the PostGIS versions conformable across the two PostgreSQL versions, but that can't happen, apparently - at least, not on Windows.

My guess

My guess is that it's monumentally unlikely that the entire PostGIS-enabled world can't update their PostgreSQL versions in a reasonably straightforward (i.e., non-pg_dump) way.

PostGIS folks tend to care a lot about how long stuff takes to do (try doing spatial comparisons on 3 million polygons without a GIST index to see what a long query looks like), so it stands to reason that someone will have worked out how to upgrade rapidly.

Reach Goal

In the best of all worlds I would pg_upgrade --link, because once I migrate to the new version, the old cluster is dead to me.

CODA/inb4

Why would anybody run a Windows machine to do anything that requires reliability?, I hear you ask.

Legacy systems ("It was like that when I got here, officer") plus inertia (both institutional and personal)

I'm going interstate this weekend, so maybe the DB will just have to pg_dump itself across while I'm gone.

PostGIS 2.2 isn't built for PostgreSQL 9.6 on Windows
PostGIS 2.3 isn't built for PostgreSQL 9.3 on Windows.

You have two options

Do a hard upgrade.
Build for 9.6 PostGIS 2.2, then do a soft upgrade

I would do a hard upgrade. Seriously 230 GB is not much to bring in. You'll live. (I think).

  My guess is that it's monumentally unlikely that the entire PostGIS-enabled world can't update their PostgreSQL versions in a reasonably straightforward (i.e., non-pg_dump) way.

They do have a method for doing a quick and easy upgrade, but it requires the server to have both versions of PostGIS. Then you can run,

ALTER EXTENSION postgis UPDATE TO "2.3.2devnext";
ALTER EXTENSION postgis_topology UPDATE TO "2.3.2devnext";

That method isn't readily available to you because I can't find anyone who has compiled PostGIS 2.3 for MS Windows PostgreSQL 9.3, but strictly speaking it's possible.

If you were using Ubuntu/Debian this wouldn't be a problem as a precompiled version of PostGIS is already in the repository for all versions of PostgreSQL also in the repository.


How to group by on two fields and count CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user1` varchar(255) DEFAULT NULL,
  `user2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `usuarios` (`id`, `user1`, `user2`) VALUES
(1, 'pepe', NULL),
(2, 'pepe', 'juan'),
(3, 'juan', NULL),
(4, 'juan', NULL),
(5, 'juan', 'pepe'),
(6, NULL, 'pepe'),
(7, 'pepe', 'juan');

I need to make a query: I have done this:

SELECT
`user1`,
COUNT(`id`) AS Total
FROM usuarios
WHERE (`user1` is not null)
GROUP BY `user1`
UNION
SELECT
`user2`,
COUNT(`id`) AS Total
FROM usuarios
WHERE (`user2` is not null)
GROUP BY `user2`

But the result is:

user1    total
juan      3
pepe      3
juan      2
pepe      2

I need to remove duplicate names, add up the total and this is the result:

user1    total
juan      5
pepe      5

SELECT users, COUNT(*) FROM (
    SELECT
    `user1` AS users
    FROM usuarios
    WHERE (`user1` is not null)
    UNION ALL
    SELECT
    `user2`
    FROM usuarios
    WHERE (`user2` is not null)
) sq
GROUP BY users;

Do the grouping outside. The trick is to use UNION ALL instead of UNION. The latter one uses a distinct on the result. You don't have to include the id column in the subqueries, so you can later count it, because it's the primary key and doesn't allow null values anyway.


MySQL database freezing after 'use' I recently made a rather stupid mistake and corrupt my Ubuntu installation.  To resolve it, I booted with a live cd and copied the database files over.  However, now I have copied the folders from the old installation into the new one, whenever I type "use database_name" it just freezes on this:

mysql> use my_database_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

And then freezes without giving the terminal.

On the first "use" command after login, MySQL scans database, tables and columns name for auto completion. If you have many db, tables it could take a while.

To avoid that, launch your client with the -A option (or --no-auto-rehash)

mysql -uroot -p -A

You could also add the disable_auto_rehash variable in your my.cnf (in the [mysql] section) if you want to disable it completely. This change does not require a reboot (it is a client, not server, variable).


Update my local db based on scripts executed in my server database I need to create an solution that update my local db version based on changes that was made on my server database.
First, I need to check the differences that has exists between these databases and so, based on a number of control,
and I will be able to know whom scripts i need to execute in my local db. I will create an table to do  control of what was executed and not.
But I need an 'insight' on how i will store the script that was executed on my server db to be able to execute it again in my local database to make the update,
and make both of them stay in the same 'version'. This need be done just by click on a button on my application, in other words, I pretend to automate this task.

P.S.: Sorry for my English :-)  this is the first time that I make an question on a English forum.

Rather than trying Opensource or free tools, I would highly recommend using Redgate tools for schema comparison. (note: I am not working for or affiliated to Redgate, but have and is using the schema and data compare and trust me they are life saving !)

For sql server : SQL Compare.

Useful resource for automating using Powershell or command line.

If you want to code the solution by yourself using Powershell, then look at Deploying Database Changes with PowerShell

Note: Just to mention, there is Data compare to sync data as well.


InnoDB files created even innodb_file_per_table is OFF in mysql-5.7 We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0.

We decided to change innodb_file_per_table to 0 because there are over 6000 databases with 40 tables in each of them. We have to migrate to this solution actually because current solution with innodb_file_per_table=ON is causing too many performance issues giving "too many open files" at error log frequently.

In this current server We are not able to do a simple backup (dump) because mysql-server-5.6 is crashing in --all-databases parameter.

We think if we configure a new mysql-server-5.7 with innodb_file_per_table=OFF we will get better performance..

Any advice/help will be welcome.

Not matter what you configure innodb_file_per_table to be (0 or 1), the following still occurs

MySQL creates a database folder
MySQL creates every table's .frm in a database folder

EXAMPLE
Your database directory (datadir) is /var/lib/mysql
You run the following commands
CREATE DATABASE IF NOT EXISTS mydb;
USE mydb
CREATE TABLE tb1 (a INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE tb2 LIKE tb1;

When innodb_file_per_table is 1

You get /var/lib/mysql/mydb
You get /var/lib/mysql/mydb/tb1.frm
You get /var/lib/mysql/mydb/tb1.ibd
You get /var/lib/mysql/mydb/tb2.frm
You get /var/lib/mysql/mydb/tb2.ibd

When innodb_file_per_table is 0

You get /var/lib/mysql/mydb
You get /var/lib/mysql/mydb/tb1.frm
You get /var/lib/mysql/mydb/tb2.frm

The major difference is the fact that data and index blocks for newly created tables end up inside the system tablespace file known as ibdata1 when it is 0.
Just as a pictorial reference, please note Percona's view of InnoDB

YOUR QUESTION
Changing innodb_file_per_table to 0 does not make currently created tables just migrate into ibdata1. You have to manually move those tables into ibdata1.
This will not alleviate the too many open files issues. Why ?
Regardless of storage engine, mysqld always checks for the existence of tables by first checking for the tables .frm file. Then, the mysqld process will reference the table's intergrity based on the storage engine.
In the case of InnoDB, the data dictionary of the InnoDB table is then checked.
Two file handles are opened

One file handle for the .frm
One file handle for the .ibd when accessing the table

If you move the table and index data from the .ibd into the ibdata1 file, you get this

One file handle for the .frm
One file handle for the ibdata1 when accessing the table

All the file handles will pile up inside ibdata1 instead of separate files.
STILL WANT TO MOVE TABLE INTO ibdata1 ?
While I highly discourage this, it is possible
In the example I gave you, in order to move a table into ibdata1 after setting innodb_file_per_table to 0, just run this
USE mydb
ALTER TABLE tb1 ENGINE=InnoDB;
ALTER TABLE tb2 ENGINE=InnoDB;

This recreates the table, and migrates the data and indexes into ibdata1. If you are willing to do this 240,000 times (6000 X 40), be my guest.
WHAT CAN YOU DO FROM HERE ?
You could setup MySQL Replication and perform backups on a Slave
You do the following

STOP SLAVE;
Perform mysqldump on the Slave without --single-transaction
START SLAVE;

or

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
Shutdown mysqld
Make a physical copy of the datadir
StartUp mysqld
START SLAVE;

GIVE IT A TRY !!!


Query not returning result if the join fails (how to get it to return zero) My query is trying to find monthly records with missing days, ie if month has 31 days but less than 31 records I want to detect it. This works fine unless all 31 records are missing when nothing is returned! (By the way Daily is a big table). So how do I get the transmitters with no records at all in a particular month to return zero?

  SELECT SUM(w.amount) AS MonthlyTotal,COUNT(*) AS Days,DAY(LAST_DAY('2016-06-01')) AS DaysInMonth
    FROM Daily  w,Transmitter t
    WHERE t.SiteID='1907' AND t.UtilityID='1' AND w.TransmitterID=t.TransmitterID  AND NOT t.deleted
    AND w.TimeStamp  BETWEEN '2016-06-01' AND LAST_DAY('2016-06-01') GROUP BY t.TransmitterID;

Before going into the main problem, there are a few more issues with the query:

the use of string literals ('1907' '1')  for values that are compared with columns that seem to have integer type (SiteID, UtilityID). If the columns are integers, use integers, not strings:

WHERE ... t.SiteID = 1907 AND t.UtilityID = 1

using BETWEEN and LAST_DAY() for datetime/timestamp comparisons. This - assuming that the w.Timestamp column is indeed a TIMESTAMP will give you incorrect results, unless all your timestamps have 00:00:00 time part. The LAST_DAY('2016-01-01') will be '2016-01-31 00:00:00' and you lose the whole last day of the month, except the very first second.
If you change that to BETWEEN '2016-06-01' AND '2016-07-01 is somewhat better but still wrong as you you'll get a few results from the next day (in July)!
One way that works correctly and with all datatypes (DATE, DATETIME, TIMESTAMP) is to use inclusive-exclusive range checks, with >= and <:

WHERE ... w.TimeStamp >= '2016-06-01' AND w.TimeStamp < '2016-07-01'

If the t.Timestamp column is of DATE type, then ok, BETWEEN can be used although I prefer the consistency of the above suggested code.
using old ANSI syntax without JOIN. This is not an error but it's harder for debugging when there are many tables and error-prone (we might forget a joining clause):

FROM Daily w, Transmitter t
WHERE ... AND w.TransmitterID = t.TransmitterID AND ...

It's better (in my opinion) to use the new (since 1992 ;) explicit JOIN syntax. It's also easier to change an INNER join to a LEFT or RIGHT outer join:

FROM Daily w JOIN Transmitter t
     ON w.TransmitterID = t.TransmitterID
WHERE ... AND w.TransmitterID = t.TransmitterID AND ...

Now for the main problem, the solution is to start from the Trasmitter table and then LEFT JOIN the details (Daily) table:

SELECT
    t.TransmitterID,
    SUM(w.amount)                        AS MonthlyTotal,
    COUNT(w.TransmitterID)               AS Days,
    DATEDIFF('2016-07-01', '2016-06-01') AS DaysInMonth
FROM Transmitter t LEFT JOIN Daily w
               ON  w.TransmitterID = t.TransmitterID
               AND w.TimeStamp >= '2016-06-01'
               AND w.TimeStamp  < '2016-07-01'
WHERE t.SiteID = 1907
  AND t.UtilityID = 1
  AND NOT t.deleted
GROUP BY t.TransmitterID ;


MariaDB Insert queries stuck frequently I am currently doing high insertions into my MariaDB 10.4.12 database via scripts.
However, sometimes the insertion queries just get stuck/piled up at what it seems to be at the Update or Commit state.

The above insertions that I am mostly doing (95% of the time) are INSERT IGNORE INTO table1 ('b1','c1','a1') VALUES (?,?,?)
Sometimes, they can even go up to 300+ seconds, until eventually all the insertions queries slowly get cleared up within 5-10 seconds (it seems like something is stuck)
I have thought of few things to improve the insert operations :

Do BULK insertions instead of many single insertions
Use transactions
Increase innodb_log_file_size

However, ontop of doing that, I would like to know if this is due to other reasons.
When I checked my iotop , half of the time it shows that mysqld is having 99.99% I/O
I am not sure if the disks are failing (2TB x4 in mirror setup) or is there some configuration settings that I did not tuned properly?
Table schema :
table1 (
  a1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  b1 char(64) DEFAULT NULL,
  c1 bigint(20) unsigned DEFAULT NULL,
  d1 datetime DEFAULT NULL,
  e1 tinyint(1) DEFAULT NULL,
  f1 int(11) DEFAULT NULL,
  LastUpdate timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (a1),
  UNIQUE KEY idxc1b1 (c1,b1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

my.cnf :
[mysqld]
symbolic-links=0

innodb_thread_concurrency=32
innodb_buffer_pool_size=240G
innodb_buffer_pool_instances=64
innodb_page_cleaners=32
innodb_purge_threads=1
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_autoinc_lock_mode=2
innodb_file_per_table=1

max_connections=10000
skip_name_resolve=1
tmp_table_size=134217728
max_heap_table_size=134217728
back_log=1000
wait_timeout=900
innodb_log_buffer_size=32M
innodb_log_file_size=768M
open_files_limit=1024000
max_allowed_packet=512M

Update #1 : Added SHOW GLOBAL STATUS and SHOW ENGINE INNODB STATUS during queries piled up (stuck for 120s+)
GLOBAL STATUS : https://justpaste.it/3hxey
INNODB STATUS : https://justpaste.it/5uren
Update #2 : Added SHOW VARIABLES
SHOW VARIABLES : https://justpaste.it/3chha
Update #3 : After replacing the disks (which are also SSDs), the issue is solved. This means that choosing the right SSD disk model is very important for a database high insertion/write rate

[very good] Do BULK insertions instead of many single insertions -- Yes.  Inserting 100 rows in a single INSERT can run 10 times as fast.

[good] Use transactions -- Multiple single-row INSERTs save some by doing this.

[it depends] Increase innodb_log_file_size -- There are metrics in GLOBAL STATUS that would help determine the utility of this.  (Your 768M is probably big enough.)

[minor] char(64) -- Is it truly fixed length?  If not, use VARCHAR.  If it is just ascii, then say so.  If it is hex, then consider using UNHEX() and putting it into BINARY(32).  Shrinking the size of the table always helps, but usually by only a small amount.

[safety] max_connections=10000 -- Shrink to something reasonable.  If you ever hit 10000, the system would (1) really bog down, and (2) swap, which would be terrible for performance.

[?] innodb_buffer_pool_size=240G -- This implies that you have at least 300GB of RAM?  How big is (or will be) the table?

[RAID] 4 drives just Mirrored?  No striping?  Hardware (better) vs software controller?

[2 unique keys] Each row must check both before committing.  Is the CHAR(64) a hash of some kind -- hence "random"?

[stumbling] If you have too many clients, they will just stumble over each other.  Your image show a rather long list.  (I don't have a good number for where InnoDB stops improving and starts stumbling.  But it sounds like you have too many.)

The first item will probably be the most productive.  But we can discuss the rest.


Dynamic casting from text within plpgsql Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query of the form:

UPDATE table SET attribute = x WHERE table_id = y

This is so common that I have a stored procedure that performs this task:

CREATE OR REPLACE FUNCTION setvalue(
    relname text,
    row_id integer,
    colname text,
    newvalue text)
RETURNS void AS
$BODY$
BEGIN
  EXECUTE format('UPDATE %I SET %I = $1 WHERE %I = $2', relname, colname, relname || '_id', colname) USING row_id;
END;
$BODY$
LANGUAGE plpgsql;

Unfortunately this doesn't work for non-text types. For instance, updating a date column gives ERROR: column ... is of type date but expression is of type text. Assuming the text is a valid literal representation of the intended type, is there a safe way to get the DBMS to figure out the right type and do the right thing?

My own solution so far is to paste the string literal into the query:

EXECUTE format(
  'UPDATE %I SET %I = ' || quote_literal(newvalue) || ' WHERE %I = $1 ',
  relname, colname, relname || '_id') USING row_id;

or just

EXECUTE format(
  'UPDATE %I SET %I = %L WHERE %I = $1',
  relname, colname, newvalue, relname || '_id') USING row_id;

This works for, e.g., appropriately formatted date types ('1990-05-04'). Probably this sacrifices being able to re-use query plan.


whether to merge partitioned table with only partially overlapping columns I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL)  in general.

I have data with a few "cross-sectional" tables (e.g. the parents of each individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately).

I wonder if it is a good idea to merge/union the annual tables into long panels.

The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary tables or views.

However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. I would also spend time on how to merge tables when there is a (rare) type change over the years (usually strings vs. TINYINTs). The merged tables could be too big anyway: think of tables ~70 GB in some cases, while my server has 128 GB RAM. Many use cases would focus on a subset of years anyway.

Actually some links I found were about how partitioning would improve performance (at least if I can learn to code using partitioning):

http://msdn.microsoft.com/en-us/library/ms190787.aspx

How Does Table Partitioning Help?

Improve performance by partitioning

Do you have any thoughts about this? Maybe more concrete questions would be:

How cumbersome would it be (for a novice!) to code joining data from
the annual tables separately, for multiple series of tables? (E.g.
UNION ALL some columns of tax returns for 1997-2008, then join all residential
data for these 12 years year-by-year, then join employment records
year-by-year etc.)
How much slower would this run than simply selecting years from the longitudinal tables?
Shall I be worried
        about the longitudinal tables having the size on the same order of
        magnitude as my physical memory, or MonetDB handles it equally
        efficiently (it would need to access many of the small annual tables
        anyway, that also takes memory). Can at least the missing values for entire years be stored efficiently?

Having all your data in fewer, common tables is obviously going to make your coding simpler.  I would suggest you pursue this option.

Partitioning is not that scary.  Annualised data like yours is the archetypal usage for partitioning.  It will help with your query performance and may help with your data management tasks (load / delete), depending on MonetDB's capabilities.

You say some data is in different formats in different years - tinyint v/s character.  This will be a problem for you whatever architecture you choose because you will eventually want to combine these in one resultset.  Fix this sooner rather than later.  Fixing in the data and table definition will be less work overall than fixing it in each query as you write it.

In summary 1) Fairly, especially if you have to reconcile type differences 2) "A bit" if your queries are simple to "a lot" of you get it badly wrong 3) not a MonetDB expert but most RDBMS handle data many times the size of RAM without problem.


Reordering rows in a chapter table when creating, sorting, and deleting I am creating an application where an Author can create a Book with say 10 Chapters, and let Readers read it. But I feel that the Author should be able to rearrange Chapters in order, sometimes DELETE a Chapter, or INSERT a new Chapter in between others.

My idea so far is to add a column to my chapter table called chapter_number and let the users add the numbers to it when creating or editing chapters, and based on that sort it when fetching it from the DB.

I am having trouble understanding how I should handle extra additions, say, an Author wants to INSERT a Chapter with chapter_number as 4, and expect the rest of the rows to automatically sort themselves. I understand that the way to handle this is to have chapter_number saved in multiples of 10 or 100 so that we have a 'room' between the rows.

But it feels a bit hacky to me. Is there a better way?

Edit:

The main thrust of my first answer (now at the bottom of this post) did not properly address the question which was about:

  how I should handle extra additions, say, an Author wants to INSERT a
  Chapter with chapter_number as 4, and expect the rest of the rows to
  automatically sort themselves.

and not how to design a (flexible) schema for the storing of book and chapter data.

I'll leave the schema design stuff in place under a line, just in case anyone is interested in the future and deal with the core issue in this edit.

You'll have to ensure that your chapters remain in the correct sequence programmatically, either using TRIGGERs or through your coding environment of choice or perhaps a combination of both? You mentioned in comments that you use Django - Python is a very powerful tool and should be well capable of tackling any programming task that is thrown at it - programmer competence being a given.

My own personal view is that the database is the last bastion of defence of your data and that any data manipulation/treatment/processing should be done as close to the server as possible. Since you apparently haven't settled on an RDBMS yet, it's impossible to give any definitive code that would help resolve the problem.

All I will say is that PostgreSQL's PL/pgSQL is vastly more powerful than MySQL's procedural language extension (see here and here - a quote from the second reference: It closely resembles Oracle's PL/SQL language. - although admittedly not as sophisticated). À vous le choix!

Do as much of the work as you can server-side using it - I would give some thought as to how using the WITH RECURSIVE construct might be of help. Googling the term PostgreSQL recursive query produces many helpful pages! A good example of its power can be seen in an answer to a recent question here.

Finally, I couldn't let this go without my pet rant (was in first answer - moved to here)!

<getting_on_to_white_charger>

I'm firmly of the belief that those who produce database systems are remiss and lackadaisical in their implementation of more features from within DDL.

If rules are stored in the structure of the database itself, then no matter how stupid, inexperienced or just plain lazy your contract programmer, student intern or half-wit cousin that your wife insisted you give a job to is, they still can't mess up the system!

The vastly underrated Firebird RDBMS is one of only two systems) that support CHECK constraints with subqueries! A small glimpse into the power of this feature can be seen in this example (from yours truly!).

</getting_on_to_white_charger>

Phwew! Deep breath - relieved that I got that off my chest! :-).

Initial answer (schema design - didn't address the question):

  I understand that the way to handle this is to have chapter_number
  saved in multiples of 10 or 100 so that we have a 'room' between the
  rows.

This is one way of tackling the problem - however, see below!

  But it feels a bit hacky to me. Is there a better way?

You are correct, it is desperately "hacky" and it's great that you are looking for a more elegant solution.

Such a solution exists in the form of joining or linking tables. These are more formally called "Associative Entities" and are really helpful when there is (and/or may be) an arbitrary number of connections between one entity (i.e. table) and another. (There are actually 17 alternative names for this construct mentioned on the wiki page).

I did something like this many moons ago, so this is from memory, but I think that you should be able to adapt it for your needs. There is a fiddle here. The example is PostgreSQL, but it works for every server on the dbfiddle.uk palette. The reason for this is these two lines in the chapter_author table:

ca_is_primary_author CHAR(1),

and

CONSTRAINT ca_is_primary_author_ny CHECK (ca_is_primary_author IN ('Y', 'N'))

I used this construct because neither Oracle nor SQL Server have a BOOLEAN type and I wanted to keep the example as generic as possible. You may well want to make use of features of your own particular RDBMS when you come to apply any solution?

My idea of the tables' DDL would look something like this:

author table:

-- 1000 is very long for a name, but there could be a corporate author?
-- For PostgreSQL you could just use a TEXT field (1GB) or for MySQL you could
-- use MEDIUMTEXT (16MB). I used VARCHAR here because it will work for all servers,
-- but you may want to make use of types provided by your RDBMS of choice. Oracle,
-- and SQL Server undoubtedly have their own equivalents

CREATE TABLE author
(
  author_id INTEGER NOT NULL PRIMARY KEY,
  author_name VARCHAR (1000) NOT NULL, -- 1000 is long but maybe corporate?
  author_contact VARCHAR (30) NOT NULL -- mobile number?
  --
  -- other fields
  --
);

work table:

-- could be called "book", "tome", "volume" - whatever
-- note that there is no "author" field - many works are multi-author
-- and/or compendiums or the results of conferences/symposia/whatever
-- even different chapters may have different authors - see below

CREATE TABLE work
(
  work_id INTEGER NOT NULL PRIMARY KEY,
  work_title VARCHAR (100) NOT NULL,
  work_publisher INTEGER NOT NULL, -- could point to a publisher table?
  work_subject VARCHAR (1000) NOT NULL
  --
  -- other fields
  --
);

chapter table:

-- There is no "chapter_author" **_FIELD_**.
-- This is because even one chapter may be written by more than one author - this
-- is especially common for academic works where a "chapter" might well be
-- a scientific article in a journal!
-- This situation is covered in the "chapter_author" **_TABLE_**!

-- There is no "chapter_work" **_FIELD_**.
-- This is because a given chapter may appear in more than one work - a compendium
-- for example like Michael Stonebraker's "Readings in Database Systems"
-- https://www.thriftbooks.com/w/readings-in-database-systems-mogan-kaufmann-series-in-data-management-systems_michael-stonebraker/1147410/#isbn=1558605231&idiq=5917200
-- which is a compilation of articles previously published elsewhere
-- This situation is covered  in the "work_chapter" **_TABLE_**!

CREATE TABLE chapter
(
  chapter_id INTEGER NOT NULL PRIMARY KEY,
  chapter_number INTEGER NOT NULL,
  chapter_title VARCHAR (100) NOT NULL,
  chapter_subject VARCHAR (1000)  -- abstract of chapter?
  --
  -- other fields
  --
);

chapter_author table:

This is our first Associative Entity - it (obviously enough!) links chapters to authors!

CREATE TABLE chapter_author
(
  ca_work_id INTEGER NOT NULL,
  ca_author_id INTEgER NOT NULL,
  ca_is_primary_author CHAR(1),
  CONSTRAINT chapter_author_pk PRIMARY KEY (ca_work_id, ca_author_id),
  CONSTRAINT ca_work_fk FOREIGN KEY (ca_work_id) REFERENCES work (work_id),
  CONSTRAINT ca_author_fk FOREIGN KEY (ca_author_id) REFERENCES author (author_id),
  CONSTRAINT ca_is_primary_author_ny CHECK (ca_is_primary_author IN ('Y', 'N'))
);

work_chapter table:

-- Having the three fields as a PRIMARY KEY means that you can't have the same
-- chapter in the same position in the same work. You may need a mechanism to ensure
-- that there are no gaps in the sequence of chapters as you INSERT, UPDATE and DELETE?

CREATE TABLE work_chapter
(
  wc_work_id INTEGER NOT NULL,
  wc_chapter_id INTEGER NOT NULL,
  wc_chapter_position INTEGER NOT NULL,
  CONSTRAINT work_chapter_pk PRIMARY KEY (wc_work_id, wc_chapter_id, wc_chapter_position),
  CONSTRAINT wc_work_fk FOREIGN KEY (wc_work_id) REFERENCES work (work_id),
  CONSTRAINT wc_chapter_fk FOREIGN KEY (wc_chapter_id) REFERENCES chapter (chapter_id)
);

As I mention in the table definition, you will need to have some mechanism to enforce a "no gaps" rule in the sequence of the chapters - unfortunately, DDL isn't (yet) sophisticated enough to be able to enforce such complex rules.

As an aside:

p.s. welcome to the forum!


SQL Server Insert Into - How to identify the column that is causing truncation error I have a stored procedure that inserts 650 fields into a table. The insert is failing with a truncation error.

It's a simple

INSERT INTO
SELECT (a bunch of fields)
FROM (a bunch of tables)

Below is the error message:

  Msg 8152, Level 16, State 14, Procedure DSP_Procedure, Line
  1075 String or binary data would be truncated.

Is there a quick way that I can identify what field is causing the truncation error?

The fact that the select statement  to be inserted into the table has 650 fields makes it difficult to pinpoint which field is causing the truncation error.

I am thinking I can maybe comment out blocks of fields at a time so as to only have the SP insert 100 fields at a time and then run the SP 6 or 7 different times until i can at least narrow down to a group of 100 fields that will contain the field that is causing the truncation error.

Alternatively I am thinking that maybe I can just SELECT INTO a new table and then compare the data lengths in the table vs the data lengths of the target table I am trying to insert into in my SP to see which field contains a longer than expected field length...

I am using SQL Server 2014.

Any easier alternatives?

Unfortunately, you have encountered quite old a "feature". There's been a Connect ticket open since 2008, and for almost ten years this hasn't been significant enough to warrant a fix.

The standard workaround is, like you figured, a select into... followed by comparing table metadata. Another possibility is binary searching the offending column, but that's manual work too. There are some hacks for metadata comparison, but simple, elegant solution doesn't exist. Maybe some third-party tools would be of help, but I am not aware of such.


SQL Server 2017 and SSRS on Same Server? I heard that you cannot have SSRS and SQL Server on the same server, I have not been able to find anything that says this or otherwise, is this a true fact?

No it's not. You can have both of them running on the same server. Here is a screenshot of SQL Server 2017 Database Engine and Power BI Report Server (newest version of SSRS):


How to model ski rental equipment I've spent a good amount at time at looking at related posts regarding products for rent but still have a few unanswered questions.

If we consider the just the requirements at hand which is how to model ski rental product packages and ski rental products, the scenario is this.

If we consider a ski rental package that consists of skis, poles, ski boots and insurance, number of days rental, how should I model this taking into account the addition of ski boots and insurance is optional and priced differently.

For example, a client may wish to rent skis for 6 days but a different client may want to rent the same skis with ski boots and insurance for 7 days.  The pricing for each choice is dependent on the items within package as well as the duration.  The pricing is typically on a sliding scale where the longer you rent, the better saving is made (when braking it down into a daily cost).

I've got the following model as a start :

customer
--------
customer_id
customer_name

orders
------
order_id
customer_id
order_date
order_type (purchase/rental)

order_items
-----------
order_item_id
order_id (FK in orders table)
product_id (FK in product table)
price
discount

product
-------
product_id
product_name
sale_price (if product available for sale)

rent_detail
-----------
order_id (FK in orders table)
date_out
date_return

product_rental_price_category
-----------------------------
product_rpc_id
name (e.g. : standard, returning customer, student)

product_rental_price
--------------------
product_id (FK in product table)
product_rpc_id (FK in product_rental_price_category)
number_of_days
price

Now, the real problem I have is with the inclusion (or exclusion) of the optional parts within a package, such as boots, helmet or insurance.

Any thoughts would be greatly appreciated.

Most of that model looks very good to me.
I think the piece you're missing is the concept of a package.
I would make some products packages (or sets) of products.
product
product_id
product_name (could be "diamond package" rather than "ski")
sale_price (if product available for sale)
is_set (boolean indicator that this is a set, not a single item)
set_detail
set_detail_id (just a serial / autonumber)
set_product_id (references the product_id of the set)
set_item_id (references the product_id of the item within the set, e.g. ski or boot)
With that, I believe the rest of your model, including the complicated pricing, works.


Import CSV to MySQL with long text fields containing line breaks I'm trying to import a CSV file (';' separated) to a MySQL table.
Normaly, I use a SQL like:

LOAD DATA LOCAL INFILE 'local-path/file.csv'
INTO TABLE tmp_table
CHARACTER SET latin1
FIELDS TERMINATED BY ';'

It always works fine to me, but now I have a CSV with a text field including many '\n','\r', and stuff like that. When I make the import, apparently, MySQL interprets the line breaks as a new field, spoiling all import.

I'd like to know if exists some method to indicate to MySQL to ignore any other symbol except the ';'.

I'm using MySQL workbench to make the import. My CSV have 3 fields (locale,id,terms) and my table have columns locale - VARCHAR(5), id- VARCHAR(50) and terms - TEXT (here is the problem).

Here I found some similar problems, but apparently is not the same thing, I'm not looking for multiple delimiters, I just want for a way to ignore \n and \r from text fields when I'm importing the CSV.

According to the MySQL docs, you can set both the field and line delimiters in the LOAD DATA statement:

[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

That should enable you to import the TEXT data.  Just be careful in choosing your delimiters!


Merge difference MySQL DBs into a single new server I have 4 servers which has it's own MySQL server along with databases and users.
Now we have a new server dedicated only for MySQL and we would like to merge those MySQL servers into this new one.

What is the most efficient way to move those MySQL servers data (databases, users) into our new server?

btw, we use centos for all of our servers, all databases are mysql 5.1, and users and db names are uniques

This consider all database names are different on all server.

As you said all servers are mysql 5.1, You can take mysqldump on each server and restore it on new server

Step 1 :  Take dump from all old server like below

Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords.

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer1.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer2.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer3.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer4.sql.gz

Step 2 : Copy dump from all old servers to new server

Step 3 : Extract all zips on new server

Step 4 : Import extracted dump files on new server like below

mysql -u... -p... < MySQLDataServer1.sql
mysql -u... -p... < MySQLDataServer2.sql
mysql -u... -p... < MySQLDataServer3.sql
mysql -u... -p... < MySQLDataServer4.sql


MongoDB text index and text search I have a MongoDB collection db.articles,it has 2 keys ,"title" and "abstract".I want to do text search on these two keys.For example, the search text is "physics",and I want all the documents whose "title" or "abstract" contains keyword "physics" is returned.But how to create the text index to meet my command is really confusing me:

Should I create two separate text indexes for both of them like this:

db.articles.ensureIndex({title:"text"})
db.articles.ensureIndex({abstract:"text"})

or

should I create a index in a single command and give the equal weight:

db.articles.ensureIndex(
                     {
                       title: "text",
                       abstract: "text",
                     },
                     {
                       weights: {
                                  title: 1,
                                  abstract:1,
                                },
                       name: "TextIndex"
                     }
                   )

I am already get used to the operation find(),whose query granularity is key,that is ,you should  indicate the key you want to query on.But for text index, it seems like a document granularity,you cannot indicate the key you want to query on , instead ,you can only indicate the document name.So , what can I do if I want to do text search on a special key?

So, no need to test as it turns out, I just remembered the key deciding limitations.  You can only create one text index on a collection (reference here), so you have no real choice.  Additionally, MongoDB can only use one index at a time to satisfy a query (until index intersection is introduced in 2.6).

Hence, the only workable option is to create the compound index as you outlined, on both fields as a single index:

db.articles.ensureIndex(
                     {
                       title: "text",
                       abstract: "text",
                     },
                     {
                       weights: {
                                  title: 1,
                                  abstract:1,
                                },
                       name: "TextIndex"
                     }
                   )

The same remains true for 3 or 4 fields, you would first have to drop the existing index, then create the new index and include 3/4/other.


Query not using index with jsonb - Postgres For some reason it seems as Postgres is not using the index we created. This is the query I'm testing:

SELECT  "public"."influencers".*
FROM "public"."influencers"
WHERE (ig -> 'id' @> '"4878142508"')
LIMIT 1

And after running EXPLAIN:

    ->  Seq Scan on influencers  (cost=0.00..32800.14 rows=216 width=1110)

Which indicates (as I see it) that no index is being used.

This is our database and indexes created:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And the indexes created (relevant index first - the one I think it should be used):

CREATE INDEX idx_btree_ig_id
    ON public.influencers USING btree
    ((ig ->> 'id'::text) COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE INDEX ads_search_idx
    ON public.influencers USING gin
    (search_vector)
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal1
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'administrative_area_level_1_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal2
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'administrative_area_level_2_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal3
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'administrative_area_level_3_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_country
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'country_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_locality
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'locality_id'::text))
    TABLESPACE pg_default;

CREATE UNIQUE INDEX ig_unique_id
    ON public.influencers USING btree
    ((ig ->> 'id'::text) COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE TRIGGER influencers_search_vector_update
    BEFORE INSERT OR UPDATE
    ON public.influencers
    FOR EACH ROW
    EXECUTE PROCEDURE tsvector_update_trigger(s, e, a, r, c, h, _, v, e, c, t, o, r, \, 0, 0, 0, p, g, _, c, a, t, a, l, o, g, ., s, i, m, p, l, e, \, 0, 0, 0, s, e, a, r, c, h, _, f, i, e, l, d, \, 0, 0, 0);

CREATE TRIGGER update_search_field_influencers_on_insert
    BEFORE INSERT
    ON public.influencers
    FOR EACH ROW
    EXECUTE PROCEDURE update_influencer_search_field();

CREATE TRIGGER update_search_field_influencers_on_update
    BEFORE UPDATE
    ON public.influencers
    FOR EACH ROW
    EXECUTE PROCEDURE update_influencer_search_field();

What's wrong?

Index idx_btree_ig_id will be used only if you write WHERE part like ig ->> 'id' = '"4878142508"'. (I assume that ig has structure like {"id": "4878142508", ...})

If you want to use operator @> you need to create gin index on that column, then you can use this index like so: ig @> '{"id":"4878142508"}'

CREATE INDEX idx_gin_ig_id ON public.influencers USING gin (ig jsonb_path_ops);


Cannot re-attach SQL Server Express database to the same server after detaching? I migrated a database in a test environment from one server running SQL Server Express 2008 R2 to another running the full version of SQL Server 2012.
I did that by detaching the database from the first server, copying over the .mdf and .ldf files, and attaching those to the new server.
That worked well, except I see some performance losses, hence I want to do some comparisons in terms of speed and maybe execution plans and here starts my problem:
I cannot reattach the original database I left on the old SQL Server Express instance.  I did not do anything to it since it was detached and it worked like a charm until I did.
But now, when I want to attach it to the original SQL Server Express instance, I get this message:

CREATE DATABASE or ALTER DATABASE failed because the resulting
cumulative database size would exceed your licensed limit of 10240 MB
per database. (.Net SqlClient Data Provider)

Sad thing is, this is party true, the database is 10,911,616 KB in size (1,024 KB for logs), which, honestly, stumps me, since I didn't do anything with it after detaching and it worked up until then.
However I am very certain, there is lots of free space inside that database (something around 9GB)
So this is a three-part-question:

Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach?

Is there a way to free up space inside that .mdf file without actually attaching it?

Or could it be feasible to take the database I already attached to the new (full) SQL Server instance, shrink that in size and then attach it to SQL Server Express instance? (or would attaching to the full SQL Server instance change the format of said .mdf file to be unusable by SQL Server Express?)

Additional Server information:

New Database Server has version Microsoft SQL Server 2012 (SP1) -
11.0.3128.0 (X64)

Old Database Server has version Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Database is running in compatibility level SQL Server 2008 (100)

Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not
changed (at least by me), then not being able to re-attach?

You have 2 problems:

Your 'old' server is SQL Server 2008 R2, and the new one is SQL Server 2012.   You cannot attach a database from a SQL Server 2012 instance on a SQL Server 2008R2 instance.
The limit of 10Gbs is exceeded.

What to do:

attach your database to a SQL Server 2012 (or higher) instance.
Export data from  that instance to  your SQL Server 2008 R2 instance.

Is there a way to free up space inside that .mdf file without actually attaching it?

You do not actually need it. I do not know such a way.

Or could it be feasible to take the database I already attached to the new (full) SQL Server, shrink that in size and then attach it to the SQL Server Express instance?  Would attaching the file to a full version of SQL Server change the format of said .mdf file to be unusable by SQL Server Express?

You can do it if you will work on SQL Server 2012 Express.  No - the format will not be changed.


Possible to disable THE postgres role? Postgresql 9.6, if it matters.
We have an audit finding which says that the "postgres" role must not be used at all.
After creating a new SUPERUSER role, is it possible to disable role "postgres" (or do some jobs like autovacuum/autoanalyze require it)?  If they are the only ones which need it, I could disable autovacuum/autoanalyze and rely on manual vacuums and analyze.

You can specify the name of the database superuser at the time of running initdb.
It will default to the name of the user running the initdb command itself, but specifying --username or -U will change that.
https://www.postgresql.org/docs/9.6/app-initdb.html
Let me highly suggest that you don't even for a second consider trying to take away any privileges from the database superuser role, regardless of its name.
Many privilege checks within the database are coded in such a way that the superuser is always allowed to continue, no questions asked. It is, after all, the superuser role.
I can only assume that your auditors are either clueless or were referring to some use of the postgres role by applications or by developers. In the latter cases, it is definitely advisable to restrict access and create appropriate roles/users within the database for these tasks.
This is my recommendation: make sure that user postgres cannot login from anywhere (besides locally via unix sockets and localhost, and possibly via network for streaming replicas) via pg_hba (and use at least peer and md5 authentication).
Changing the superuser's name does in no way increase security.


Oracle - Connect Via SqlPlus I have a local installation of Oracle. I want to add the XMLDB package to it. As step 1, I need to connect to it via sqlplus. And I am not able to.

The installation has a database called Pm.

Here is what I have tried on the command line:

sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 12:11:44 2016

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

Enter user-name: SYSTEM@PM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: SYSTEM@Pm
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: system@PM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\sgupta>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 12:14:15 2016

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

Enter user-name: system@Pm
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Here is what the tnsnames.ora file looks like:

# tnsnames.ora Network Configuration File: C:\app\sgupta\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

PM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Pm)
    )
  )

CM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CM)
    )
  )

PM20154 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Pm20154)
    )
  )

And I have checked that the service is running:

Not sure why I keep getting the error 'ORA-12154: TNS:could not resolve the connect identifier specified'.

TLDR; Copy listener.ora and tnsnames.ora from app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN
to app/sgupta/product/11.2.0/dbclient_1/network/admin

I had the tnsnames.ora and listern.ora files in C:/app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN.

When I used tnsping on the command line, i was getting following error message:

tnsping PM

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Product
016 15:10:08

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\sgupta\product\11.2.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

I noticed how it was looking in the C:\app\sgupta\product\11.2.0\client_1\network\admin folder. And that the sqlnet.ora looked like following:

# sqlnet.ora Network Configuration File: C:\app\sgupta\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

So I figured sqlnet.ora is expecting a TNSNAMES file in the same folder.

So I copied listener.ora and tnsnames.ora from app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN
to app/sgupta/product/11.2.0/dbclient_1/network/admin.

After that tnsping worked. And sqlplus also connected.

tnsping PM

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 17-FEB-2
016 15:14:40

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\sgupta\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki
-E6330.callidussoftware.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = Pm)))
OK (20 msec)

C:\Users\sgupta>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 15:15:01 2016

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

Enter user-name: SYSTEM@PM
Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production


What is the difference between ASYNC_IO_COMPLETION and IO_COMPLETION wait types? Could someone explain what is the main difference between these two wait types?

I think you should first understand the difference between Sync IO and Async IO. The information about the basic nature of both the I/O's can be found in Bob Dorr's I/O presentation blog see the section Async vs Sync IO.
In very simple meaning Async IO is one in which after putting I/O request the program or calling code will not wait for the I/O operation to complete but will get busy with other task, later it can come and check and see if the I/O request has been completed or not. Similarly for Sync IO the calling code will wait for acknowledgement that I/O request is done. Quoting from the Bob Dorr's blog

In Windows the I/O APIs allow sync and async requests. Sync requests are calls to the API such as WriteFile that will not return control to the calling code until the operation is complete. Async hands the request off to the operating system and associated drivers and returns control to the calling code. The calling code is free to execute other logic and later come back to see if/when the I/O completes.
SQL Server uses mostly Async I/O patterns. This allows SQL Server to
write or read a page and then continue to use the CPU and other
resources effectively. Take the example of a large sort operation. SQL
Server can use its read ahead logic to post (async request) many pages
and then start processing the first page returned by the request. This
allows SQL Server to use the CPU resources to sort the rows on the
page while the I/O subsystem is fetching (reading) in other pages at
the same time. Maximizing the I/O bandwidth and using other resources
such as CPU more effectively.

Now the wait time here represents time spent in waiting Async/Sync IO request to complete.


Efficient way to perform approximated search? I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.

As it happens though, the evnt_time can be displaced in a few seconds between these tables. Thus the join has to behave sort of table_a.id1 = table_b.id1 and table_a.id2 = table_b.id2 and table_a.evnt_time ~ table_b.evnt_time, where:

~ means approximately, given + or - seconds
There must be a way to give ~ parameters of how precise should be. e.g.: table_a.evnt_time ~(2) table_b.evnt_time will join table_a.evnt_time with table_b.evnt_time - 2s, table_b.evnt_time - 1s, table_b.evnt_time, table_b.evnt_time + 1s and table_b.evnt_time + 2s.

To tackle the situation, there are a few possibilities I am experimenting, but my doubt is on which architecture should I use to perform a very efficient "approximated join" - these tables are partitioned, sub-partitioned and each sub part may contain billions of records... (although I have a reasonable amount of resources).

For once, I thought of storing the unique sencond of the event (i.e. second it happened since julian calendar) on both sides so the real join (give a "~(2)") could simply look like:

select *
from
    table_a,
    table_b
where
    table_a.id1 = table_b.id1
    and table_a.id2 = table_b.id2
    and (table_a.evnt_sec = table_b.evnt_sec
        or table_a.evnt_sec = table_b.evnt_sec + 1
        or table_a.evnt_sec = table_b.evnt_sec + 2
        or table_a.evnt_sec = table_b.evnt_sec - 1
        or table_a.evnt_sec = table_b.evnt_sec - 2)

But I am unsure of how efficiently would that perform with the indexes and scans..

This is just an example, I am not stuck to any sort of architecture.
Also, I am using Oracle 11gR2.

I think that what you need to use is a Nonequi join

select *
from
    table_a,
    table_b
where
    table_a.id1 = table_b.id1
    and table_a.id2 = table_b.id2
    and table_a.evnt_sec BETWEEN table_b.evnt_sec -2 and table_b.evnt_sec +2


Pros and cons of DBCC SHRINKDATABASE operation I have read MSDN - Microsoft Manual according to this issue, but I still do not have clear understanding, when it is useful to apply this option and when it is not. Please advise.

As you know DBCC SHRINKDATABASE attempts to shrink both data and log files.

Personally, I would probably never use SHRINKDATABASE. Instead I'd use DBCC SHRINKFILE, and even then would only use it on datafiles to resize them correctly, or on log files if I was trying to shrink them back in order to grow them out again to the correct number of virtual log files (VLFs). The other use case for datafiles (if you had multiple) would be if you were looking to remove them; you would first need to shrink with EMPTYFILE.

In a properly configured database, you should never need to use either SHRINKDATABASE or SHRINKFILE. You should have pre-allocated and sized your log and data files correctly, and set caps to avoid files growing out of control. Prevent the log file from needing to grow in the first place through correct sizing (to accommodate the longest possible running open transaction) and regular log backups.

For debate on VLF sizing please check out the SQLSkills article:

Transaction Log VLFs – too many or too few?


Cannot login to SQL Server from Visual Studio, same credentials work on SSRS page I am running into a strange issue.  I have a SQL Server login that I use for running reports on SSRS.  When I go to the SSRS page I can modify the stored credentials to use that login:

Now when I try to build that same login right into the report in Visual Studio I get the following error:

I am not sure why I can login from one place but not the other.  I am using SQL Server 2014 Standard and VS 2013.  Does anyone know what permissions setting needs to be change, or what to do to be able to deploy my report using this?

I solved my own problem.  I needed to grant data reader rights to that database for the user.  I do that by right clicking the user name under Security\Logins and choosing properties.  In there I go to User Mapping.  Make sure the user is mapped to the database being queried and under the Database Role Membership make sure db_datareader is checked.


How to Calculate Month over Month User Retention based on already active users (not based on user signup date)? I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup date) based on user Activity( i-e In December 2018, 500 users were active. Then, how many of them were active in Jan,Feb,Mar....Till Now?Same activity should be performed for Jan 2019,Feb 2019... till now users).

I have tried the hard coded way which is to get the users of Dec 2018 in on Table and then Get the Jan 2019 users in other Table and join both tables based on the user_ids, but for that purpose i have to write a lot of joins. Need a dynamic way to check month over month user retention and for all months after Dec 2018(because the data is available from this month onward).

Postrgesql Code #

select A.year_month_id,count(distinct A.user_id) as November_Users,count(distinct B.user_id) as December_Retained_Users
FROM (
        select date_trunc('month', ua.created_at) as monthly,
        ua.user AS user_id
        FROM     user_activity ua
        WHERE    ua.event_type='StartSession'
        and     cast(ua.created_at as date) between cast('20181201' as date) and cast('20181231' as date)
        GROUP BY 1,2
    ) AS A
left Join
    (
        select date_trunc('month', ua.created_at) as monthly,
        ua.user AS user_id
        FROM     user_activity ua
        WHERE    ua.event_type='StartSession'
        and     cast(ua.created_at as date) between cast('20190101' as date) and cast('20190131' as date)
        GROUP BY 1,2
    ) AS B
on A.user_id=B.user_id
group by 1

user_activity Table #

id | user | event_type   | created_at
1  | A1   | StartSession | April 29, 2019, 3:59 AM
2  | A2   | StartSession | December 29, 2018, 1:07 AM
3  | A3   | StartSession | December 9, 2018, 4:59 PM
49 | A31  | StartSession | May 25, 2019, 11:59 AM
100| A46  | StartSession | April 29, 2019, 3:56 AM

Expected Output #

Month |Monthly_Active_Users| Jan_Retained|Feb_Retained|Mar_Retained|.......
Dec   | 500                |  300        |  200       | 330
Jan   | 700                |  N/A        |  450       | 410
Feb   | 1000               |  N/A        |  N/A       | 820
Mar   | 920                |  N/A        |  N/A       | N/A
.
.
.
.
Aug   | 100                | N/A         |    N/A     | N/A

I am not sure I understood your requires completely, but anyway.

In Postgres I would probably do something like this:

with per_month as (
  select to_char(a1.created_at, 'yyyy-mm') as month,
         array_agg(distinct a1.user_id) as active_users
  from user_activity a1
  where a1.created_at >= timestamp '2018-12-01'
  group by month
), m2 as (
  select month,
         active_users,
         lead(active_users) over (order by month) as january_users,
         lead(active_users,2) over (order by month) as february_users,
         lead(active_users,3) over (order by month) as march_users,
         lead(active_users,4) over (order by month) as april_users
  from per_month
)
select month,
       cardinality(active_users) as num_users,
       (select count(*) from (select unnest(active_users) intersect select unnest(january_users)) t)  as january_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(february_users)) t)  as febrary_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(march_users)) t)  as march_retained
from m2;

The first CTE simply aggregates all users per month into an array. The second CTE then simply creates on column per month with the user of that month using a window function.

And the final query then calculates the intersection between the users of the starting month with each of the following months by unnesting the arrays and counting the size of the intersection.

Online example: https://rextester.com/VYNSFX1788 (note that it generates random data, so each time you run it you get a different result)


Find which objects are using my table column in the given schema Lets say I have a table 'MyTable' having a column 'MyColumn'. I want to find out which procedures, triggers, views, functions are using that column. Currently I have this script which gets most objects with the given name. Please let me know if this script returns all the objects using the given column. Is there in-built function for this? Thanks

CREATE procedure [dbo].[Find] (@q varchar(255))
as

BEGIN
(SELECT DISTINCT
    o.name AS Object_Name,o.type_desc AS 'Type', m.definition AS 'Complete Name'
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@q+'%')

UNION ALL

(SELECT t.name AS Object_Name,'Table' AS 'Type', c.name AS 'Complete Name'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%'+@q+'%')

UNION ALL

SELECT OBJECT_NAME(id) AS Object_Name, 'Trigger' AS 'Type',text AS 'Complete Name'
FROM syscomments
WHERE [text] LIKE '%'+@q+'%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1

ORDER BY 2,1,3;
EnD

Best is to use FREE tool from Red-Gate SQL Search - quickly find SQL in SSMS. You can even profile it while it is running to get the sql code and use that.

Also, refer to Aaron's post on : Keeping sysdepends up to date in SQL Server 2008.

Phil Factor has a great writeup on finding object dependency at Exploring your database schema with SQL


How can I convert the first 100 million positive integers to strings? This is a bit of an diversion from the real problem. If providing context helps, generating this data could be useful for performance testing ways of processing strings, for generating strings which need to have some operation applied to them within a cursor, or for generating unique, anonymous name replacements for sensitive data. I'm just interested in efficient ways of generating the data within SQL Servers, please don't ask why I need to generate this data.

I'll try to start with a somewhat formal definition. A string is included in the series if it only consists of capital letters from A - Z. The first term of the series is "A". The series consists of all valid strings sorted by length first and typical alphabetical order second. If the strings were in a table in a column called STRING_COL, the order could be defined in T-SQL as ORDER BY LEN(STRING_COL) ASC, STRING_COL ASC.

To give a less formal definition take a look at alphabetical column headers in excel. The series is the same pattern. Consider how you might convert an integer to a base 26 number:

  1 -> A, 2 -> B, 3 -> C, ... , 25 -> Y, 26 -> Z, 27 -> AA, 28 -> AB, ...

The analogy isn't quite perfect because "A" behaves differently than 0 in base ten. Below is a table of selected values that will hopefully make it more clear:

╔════════════╦════════╗
║ ROW_NUMBER ║ STRING ║
╠════════════╬════════╣
║          1 ║ A      ║
║          2 ║ B      ║
║         25 ║ Y      ║
║         26 ║ Z      ║
║         27 ║ AA     ║
║         28 ║ AB     ║
║         51 ║ AY     ║
║         52 ║ AZ     ║
║         53 ║ BA     ║
║         54 ║ BB     ║
║      18278 ║ ZZZ    ║
║      18279 ║ AAAA   ║
║     475253 ║ ZZZY   ║
║     475254 ║ ZZZZ   ║
║     475255 ║ AAAAA  ║
║  100000000 ║ HJUNYV ║
╚════════════╩════════╝

The goal is to write a SELECT query that returns the first 100000000 strings in the order defined above. I did my testing by running queries in SSMS with the result set discarded as opposed to saving it to a table:

Ideally the query will be reasonably efficient. Here I'm defining efficient as cpu time for a serial query and elapsed time for a parallel query. You may use whatever undocumented tricks that you like. Relying on undefined or non-guaranteed behavior is okay as well but it would be appreciated if you call that out in your answer.

What are some methods of efficiently generating the data set described above? Martin Smith pointed out that a CLR stored procedure probably isn't a good approach due to the overhead of processing so many rows.

Your solution runs for 35 seconds on my laptop. The following code takes 26 seconds (including creating and populating the temporary tables):
Temporary tables
DROP TABLE IF EXISTS #T1, #T2, #T3, #T4;

CREATE TABLE #T1 (string varchar(6) NOT NULL PRIMARY KEY);
CREATE TABLE #T2 (string varchar(6) NOT NULL PRIMARY KEY);
CREATE TABLE #T3 (string varchar(6) NOT NULL PRIMARY KEY);
CREATE TABLE #T4 (string varchar(6) NOT NULL PRIMARY KEY);

INSERT #T1 (string)
VALUES
    ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'),
    ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'),
    ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), ('U'),
    ('V'), ('W'), ('X'), ('Y'), ('Z');

INSERT #T2 (string)
SELECT T1a.string + T1b.string
FROM #T1 AS T1a, #T1 AS T1b;

INSERT #T3 (string)
SELECT #T2.string + #T1.string
FROM #T2, #T1;

INSERT #T4 (string)
SELECT #T3.string + #T1.string
FROM #T3, #T1;

The idea there is to pre-populate ordered combinations of up to four characters.
Main code
SELECT TOP (100000000)
    UA.string + UA.string2
FROM
(
    SELECT U.Size, U.string, string2 = '' FROM
    (
        SELECT Size = 1, string FROM #T1
        UNION ALL
        SELECT Size = 2, string FROM #T2
        UNION ALL
        SELECT Size = 3, string FROM #T3
        UNION ALL
        SELECT Size = 4, string FROM #T4
    ) AS U
    UNION ALL
    SELECT Size = 5, #T1.string, string2 = #T4.string
    FROM #T1, #T4
    UNION ALL
    SELECT Size = 6, #T2.string, #T4.string
    FROM #T2, #T4
) AS UA
ORDER BY
    UA.Size,
    UA.string,
    UA.string2
OPTION (NO_PERFORMANCE_SPOOL, MAXDOP 1);

That is a simple order-preserving union* of the four precalculated tables, with 5-character and 6-character strings derived as needed. Separating the prefix from the suffix avoids sorting.
Execution plan

* There's nothing in the SQL above that specifies an order-preserving union directly. The optimizer chooses physical operators with properties that match the SQL query specification, including top-level order by. Here, it chooses concatenation implemented by the merge join physical operator to avoid sorting.
The guarantee is that the execution plan delivers the query semantic and top-level order by specification. Knowing that merge join concat preserves order allows the query writer to anticipate an execution plan, but the optimizer will only deliver if the expectation is valid.


Maintenance Plan Backups failing since KB4013106 installed "Hotfix 4422 for SQL Server 2016 (KB4013106)" installed for our installation of SQL Server 2016 over the weekend.  Since then, all our backups are failing.

The backups are done via Maintenance Plans.

Anyone else experiencing the same issue?

  Source: Backup DbNameHere (Logs) Description: The Execute method on the
  task returned error code 0x80131501 (Failed to connect to server
  (local).). The Execute method must succeed, and indicate the result
  using an "out" parameter.  End Error  Warning: 2017-04-17 08:50:01.12
  Code: 0x80019002     Source: Daytime backups      Description: SSIS
  Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method
  succeeded, but the number of errors raised (2) reached the maximum
  allowed (1); resulting in failure. This occurs when the number of
  errors reaches the number specified in MaximumErrorCount. Change the
  MaximumErrorCount or fix the errors.  End Warning Error: 2017-04-17
  08:50:01.15 Code: 0xC0024104

Edit the maintenance plan and change the credentials used for Local server connection to Use Windows NT Integrated Security.

Open the Maintenance Plan in Design view.
Click Manage Connections
Select Local server connection and click Edit
Under Enter information to log on to the server, choose Use Windows NT Integrated security
Save your changes
Execute the plan/schedule to verify that this has cured the problem

No idea what causes this issue, but for us, it seems to have been directly after Hotfix 4422 for SQL Server 2016 (KB4013106) was installed automatically by Windows Update.


Is Oracle free to download? I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free?

I found this link: Oracle Database 11g Release 2 Downloads, and it seems that it is downloadable. However, I know that Oracle Server is proprietary. So how does that work?

Also, will there be PL/SQL along with the download of Oracle 11g?

If you're really only looking to learn about the development side and have no interest in the administration or installation side at the moment, a quicker route might be to download a prebuilt developer VM image for Virtualbox. That can get you up and running very quickly, and you can connect to the DB running inside the VM from outside, so you can continue to do your development in your current environment.

The overview echos what others have said about licensing kicking in once you ship an application (but Oracle licensing is a quagmire, you'll need to talk to Oracle about it if/when you get past playing around):

  Learning your way around a new software stack is challenging enough
  without having to spend multiple cycles on the install process.
  Instead, we have packaged such stacks into pre-built Oracle VM
  VirtualBox appliances that you can download, install, and experience
  as a single unit. Just downloaded/assemble the files, import into
  VirtualBox (available for free), import, and go (but not for
  production use or redistribution)!

For experimenting it's ideal, in my experience. Of course, it's unsupported, but as you won't have a support contract anyway that's a bit of a moot point.

As David said, PL/SQL is integral to the database, not a separate component, so it is available in the VM image. You get some tools too, including SQL Developer, but you might have less friction running that natively and connecting it to the DB in the VM.


How to properly design outgoing/incoming mailserver configuration per user? I'd like to store incoming and outcoming mailserver configuration per user, it's not a real live project, but to learn database-design (never done before).

Let's say I have a mail_server_configuration table.

| MailServerConfigurationId | Host | Port | Username | Password

and my user table:

| UserId | IncomingMailConfigurationId (+FK) | OutgoingMailConfigurationId (+FK)

That is my current approach.

Q1: Is that the way to go or am I missing something?

Q2: What about data redundancy about hosts and ports? What if two users are using the same host&port? Should there be a "host" table?

Because you're doing this to learn database design, I'd get strict.  I'd also recommend Louis Davidson's book on database design.  He focuses on SQL Server, but it's a really well-done book no matter which database platform you're using, and his chapter on normalization is outstanding.

Your user table has repeating attributes:  two mail configuration IDs.  In a real-world business scenario, this might slide, but it is a violation of first normal form.  The way to correct that is to break those out into a separate table, turning columns into rows.

Looking at hosts and ports, there are a couple of possibilities.  Do you want to limit appropriate ports, or "know" which port is which (e.g., 25 for SMTP, 110 for POP3, 143 for IMAP)?  If so, create a ports table.  In that case, you could use either a surrogate key or the port number as the primary key because well-known mail ports are distinct.  On the other hand, because servers can use "weird" ports, you might not be able to create that table (or you may need to leave it open for users to add new ports to the list).  In a business application, this would be a business decision.  In an academic application, I'd probably create the table and make a foreign key connection to the mail server configuration table.

Regarding hosts, you have a similar question.  If you want to limit hosts, check for common misspellings, or be able to display more information about the particular host (e.g., pop3.google.com's application name is GMail), you would want to create a Host lookup table, as well as application logic which lets users create new hosts.  But there's something else to think about with hosts:  how far down the rabbit hole do you want to go?  Are you going to analyze based on the host's top-level domain (TLD)?  If so, you would want to break out the domain into its own table.  In fact, you might have three separate tables:  host prefixes (mail, pop3, smtp, imap, etc.), host name (google, yahoo, etc.), and host top-level domains (.com, .co.uk, etc.).  Your Host table would then have a Prefix ID, a Hostname ID, and a TLD ID and you could build the final hostname up from that.  Whether this makes any sense at all for you to do depends upon the application:  if you're using the mail server information to shoot e-mails out but aren't analyzing it further, then this is going too far; otherwise, if you do need to split out hostnames or domains as part of your application, keeping it all as "pop3.google.com" would be a violation of first normal form.

Finally, looking at the mail server configuration table, you have port on there.  If your users have different credentials for receiving and sending e-mails, that would make sense, but otherwise, you end up duplicating data:  username Bob with password PW at mail.yahoo.com will be the same regardless of whether he's connecting to port 25 or 110.  The port really is more for communication than authorization, so it makes some sense here to split it out.  You'll still have some "duplication" of sorts because of hosts that have pop3.hostname.com and smtp.hostname.com servers, but I wouldn't worry too much about that, especially if we don't need to split hosts out.

Assuming that you don't need to split out hosts any further, the table design would look a bit like the following:

Host
----------------------------
HostId smallint (PK; surrogate)
Name varchar(75) (AK1)
[additional host attributes]

Port
----------------------------
PortId smallint (PK; surrogate)
Number int (AK1)
Name varchar(20)

Direction { Inbound; Outbound }
----------------------------
DirectionId tinyint (PK; surrogate)
Name varchar(8) (AK1)

User
----------------------------
UserId int (PK; surrogate)
[additional user attributes]

UserHostCredential
----------------------------
UserHostCredentialId (PK; surrogate)
HostId (FK; AK1)
UserId (FK; AK1)
Username
Password

UserMailConnection
----------------------------
UserMailConnectionId int (PK; surrogate)
UserHostCredentialId int (FK; AK1)
DirectionId tinyint (FK; AK1)
PortId (FK)

AK here means alternate key.  You'd put a unique key constraint wherever you see that.  I numbered them to show you instances in which there are multi-column alternate keys, like mail server configuration.

This is a bit more work than your two-table design, but not really that much more.  I would tend toward adding the Host and Port tables simply to get rid of potential update inconsistencies; for example, if Google's new POP3 server is pop3.googlemail.com instead of pop3.google.com, you would have to update all of the rows to match this.  But if somebody misspelled a row (or you didn't catch it in your updating process), you now have a data anamoly.  In the hosts table, you make one update and you're done.

But one important thing to note (and something that I've tried to hint at up above) is that business rules shape database design.  I'm working from one set of assumptions, some of which may not exactly match a particular scenario.  For example, I'm assuming that a username and password typically should relate to one person at one host.  But if a number of different people all use the same credentials (for whatever reason), this may no longer apply.  In that case, I'd move UserId off of UserHostCredential and onto UserMailConnection and then rename UserHostCredential to HostCredential.  Both of those are "valid" database designs, and which one is correct depends upon the business purpose.

Similarly, I'm assuming that Port isn't necessarily determinate of direction (inbound or outbound).  If you are able to make that determination--e.g., that 25 is always outbound, and there are no ports which are both inbound and outbound--then the direction stops being an attribute of a particular connection and instead becomes an attribute of the port itself.  If that is the case, you would move DirectionId from UserMailConnection up to Port and make PortId part of the alternate key for UserMailConnection.  Again, both are valid, fully-normalized table designs, but which business rule holds would determine which is the "correct" one for your purposes.


AlwaysOn AG versus FCI Throughout MS documentation for AlwaysOn Availability Groups (AG) and AlwaysOn Failover Cluster Instances (FCI) I see the following pattern:

FCI is for HA scenarios.
An AG synchronous secondary replica, co-located with the primary, is for HA scenarios.
An AG asynchronous secondary replica, in a different datacenter, is for DR scenarios.

Here is an example MS link discussing this.
Since option #1 and #2 are both for HA scenarios, how do I decide between them?
If MS published costs and RPO/RTO metrics for both #1 and #2, it would be fairly easy to decide which I want.
Or perhaps there is a different way to understand the ROI differences between these options.  For example, perhaps option #2 is best suited for VLDB's and option #1 is best suited for very high transaction volumes.  I don't know.
So again, what is the selection criteria a DBA uses to choose between options #1 and #2?
To further complicate things, I know that options #1 and #2 can be combined!  When is it wise to combine the two options?  When is it pointless to combine the two options?  I know when these options are combined, the AG no longer supports automatic fail-over.  It's interesting trivia, but doesn't answer my question.
Incidentally, I intend on provisioning my final solution into Azure IaaS.  If I use an Always On FCI, I will likely create the quasi-SAN using Storage Spaces Direct (S2D).
Update
I've found two articles that give a comparison.  The first is MS docs, and the other is Choosing the Right Availability Tech.  Both have a chart like this:
╔═════════════════════════════╦══════════════════════════╗
║ FCI                         ║ AG                       ║
╠═════════════════════════════╬══════════════════════════╣
║  * Server Level             ║  * Database Level        ║
╠═════════════════════════════╬══════════════════════════╣
║  * Requires shared storage  ║  * Uses direct           ║
║     (SAN or Storage Spaces  ║     attached storage     ║
║     Direct)                 ║                          ║
╠═════════════════════════════╬══════════════════════════╣
║  * RTO from 30              ║  * RTO typically less    ║
║     seconds to 20 minutes.  ║     than 30 seconds      ║
╠═════════════════════════════╬══════════════════════════╣
║  * RPO: no data loss.       ║  * RPO: ???              ║
╠═════════════════════════════╬══════════════════════════╣
║  * Only Passive Secondaries ║  * Active or Passive     ║
║                             ║     Secondaries          ║
╠═════════════════════════════╬══════════════════════════╣
║  * One SQL Server           ║  * Multiple SQL Server   ║
║     instance/license        ║     instances / licenses ║
╚═════════════════════════════╩══════════════════════════╝

The article didn't comment on AG RPO, but I read elsewhere that there is no data loss when recovering from a synchronous secondary replica.  I don't know if that is true and I don't know what the RPO of asynchronous secondary replica might be.  In Azure there is an AG resource that requires two domain controllers are created with the AG (regardless if you have your own domain controllers).  I don't know if an Azure FCI has the same heavy-handed requirement.
Most significantly - I still don't know why it is valuable to combine the two techniques.  I've only read that it "improves" or "maximizes" availability.  That is a vague claim IMO.
More trivia: I also spotted a discussion suggesting that an FCI of two nodes should be avoided.

In general, FCI is more beneficial compared to AG because of various reasons already mentioned above. Of course, 2-node FCI is completely great but should not be built on top of storage spaces direct since S2D is known to be problematic in less than 4 nodes deployments (source).

For Azure, I would rather stick to a VSAN that is designed to work in 2 or 3 nodes scenarios like (example).

Here is a bit more information on FCI vs AG that you might find useful too


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

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

These commands should be run from the OS command line not the SQL prompt.


I used you dbfiddler code in the following solution that uses CROSS APPLY

declare @price_list table(product varchar(20), price int, date_price datetime, base int, active tinyint);
insert into @price_list values
('0125',  90, '2017-01-01', 1200, 0),
('0125', 100, '2017-01-25', 1000, 1),
('0125', 110, '2017-02-27', 500,  1),
('1200', 140, '2017-01-01', 2000, 0),
('1200', 150, '2017-02-01', 1500, 1),
('1200', 160, '2017-02-27', 1000, 1);

declare @orders table(product varchar(20), order_date datetime, price int, base int);
insert into @orders values
('0125', '2017-02-19', 100, 1000),
('0125', '2017-02-20', 100, 1000),
('0125', '2017-02-21', 100, 1000),
('0125', '2017-02-22', 100, 1000),
('0125', '2017-02-23', 100, 1000),
('0125', '2017-02-28', 110,  500),
('1200', '2017-02-19', 150, 1500),
('1200', '2017-02-20', 150, 1500),
('1200', '2017-02-21', 150, 1500),
('1200', '2017-02-22', 150, 1500),
('1200', '2017-02-23', 150, 1500),
('1200', '2017-02-28', 160, 1000);

declare @new_date_price datetime = '2017-02-21';

-- add a new price to the list
--
insert into @price_list values ('0125', 105, @new_date_price - 1, 1300, 1);
insert into @price_list values ('1200', 155, @new_date_price - 1, 1400, 1);

-- update orders price and base, according products price list date price
--
-- I'd like to avoid use 2 (or more) subqueries
--
UPDATE o
SET o.Price = ca.price
    ,o.base = ca.base
FROM @Orders o
CROSS APPLY (
    SELECT TOP 1 pl.price AS price
        ,pl.base AS base
    FROM @Price_List pl
    WHERE pl.product = o.product
        AND pl.date_price <= o.order_date
        AND active = 1
    ORDER BY pl.date_price DESC
    ) ca
WHERE o.product IN (
        '0125'
        ,'1200'
        );--<<< select distinct product from inserted
-- final result
--
select * from @price_list order by product, date_price;
select * from @orders order by product, order_date;


Is it possible to backup and restore InnoDB tables on Oracle's MySQL 5.7.19 Server with Percona Xtrabackup? I have the next configuration:

OS: Ubuntu 16.04.2 x86_64
MySQL Server: Ver 5.7.19-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) installed from Ubuntu's repos

Percona Tools: innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7) installed from the Percona's repos.

The problem is the next: When I do the backup with innobackupex and then restore - my InnoDB tables got corrupted.

2017-07-28T15:49:50.078080Z 4 [Warning] InnoDB: Cannot open table forum/domain from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

The backup/restore happens on the same server. MySQL server has the defaults settings, innodb_file_per_table option is not set directly, but Oracle's MySQL has that enabled by the default.

mysql> show variables like '%innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

In order to do the backup, I do:

innobackupex --defaults-extra-file=/root/.my.cnf \
--include=forum.* --parallel=9 \
--extra-lsndir=/home/forum \
--stream=xbstream \
--no-timestamp /home/forum 2>/home/backup.log | lzop -c > /home/forum.xbs.lzo

the xtrabackup_info file from the result:

uuid = 850f3d9e-73ab-11e7-8b45-448a5b2c32e7
name =
tool_name = innobackupex
tool_command = --defaults-extra-file=/root/.my.cnf --include=forum.* --parallel=9 --extra-lsndir=/home/forum --stream=xbstream --no-timestamp /home/forum
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 5.7.19-0ubuntu0.16.04.1
start_time = 2017-07-28 17:43:27
end_time = 2017-07-28 17:43:36
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 14341896999
partial = Y
incremental = N
format = xbstream
compact = N
compressed = N
encrypted = N

As the restore, I do the next:
unarchive the file:

lzop -dcfU /home/forum.xbs.lzo | xbstream -x --directory=/home/forum.restore

Apply Log:

innobackupex --apply-log --redo-only /home/forum.restore --use-memory=1G 2>/home/restore.log

Prepare the backup for usage:

innobackupex --apply-log /home/forum.restore --use-memory=1G 2>>/home/restore.log

After that my directory has:

root@restore-test /home/forum.restore # tree
.
├── backup-my.cnf
├── forum
│   ├── db.opt
│   ├── domain.frm
│   └── domain.ibd
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── xtrabackup_checkpoints
├── xtrabackup_info
└── xtrabackup_logfile

The final is:

stop MySQL server
chown -R mysql:mysql /home/forum.restore/forum
mv /home/forum.restore/forum /var/lib/mysql/forum
start MySQL

The database is there, but when i try to do any operation with the table (SHOW/DESCRIBE/etc) - I'm having the error listed above.

After doing some research and re-reading the manuals I found the next:

I had done so called partial backups, that mean that the restore process is a little bit different - you need to specify --export option when doing the restore process (see Restoring Individual Tables)

If you are on Percona Server you can try to use innodb_expand_import option and avoid of some routine operations.
If you are on MySQL server - you must also to do the schema dumps. Then do the tricks with ALTER TABLE xxx DISCARD TABLESPACE and ALTER TABLE xxx IMPORT TABLESPACE for an every single table. (See here)

I did the restore without specifying the --export, so had the problems with those .frm's.
If you have stored procedures/etc - maybe you need to backup them as well. I have no chance to check that.


And Java Justice For All Recently, I thought I was gettint into a Oracle Database Administration and Development project, with a heavy load of plsql programming in the form of stored procedures and such.

I was struck when, the other day, I was told I shouldn't rely so much on plsql as it can lead to undesired behavior on Production Systems, and that it would be ideal to perform all the necessary transformations on the data outside of the database, and use the database more as a repository than anything else (only insert intoand select should be used. Just store data, don't operate with it in any other way).

Not only that, but we were asked to do it all in Java (the face I made at that moment...). Since then I have cracked my head trying to figure out an easy way of satisfying these requirements and the best idea I could come of is trying to replicate a relational engine in a Java project. I know there are plenty of frameworks and libraries, like hibernate, that would somehow help me with the first steps of this project, but as you might have assumed, I could only use these to retrieve data from the database, not to pass to the db the queries that should be performed to make all necessary transformations (that would only be adding extra steps to the first, forbidden, scenario). I would still need to replicate operations like joins, unions, etc.

Here are my cries for help:

Is it true that plsql can lead to undesired behavior in Production Systems? (Oracle database)
Is replicating the work of the relational engine a sensible idea?
Are there any tools that can really help in a situation like this?
Should I just slap the genius behind this whole idea back to his senses?

For the same reason a carpenter does not use one tool for his job, companies should not rely on one tool for development of systems. There is nothing wrong with PL/SQL when used for the correct jobs. We use it for ETL and reporting on our warehouses, together with other BI tools. We also use C++ if we need to manipulate huge amounts of data in memory (on the server) and then persist changes or write the results as text or html reports.

For OLTP (you say "production") we use java together with frameworks such as Spring and Hibernate (and JDBC) to make communication with Oracle easier.  You should then investigate your middle tier because you need efficient connection pooling for production - we find that Weblogic is stable for this.  Your architect is correct to mention inserts and simple selects if your OLTP consists of mainly transactional tables, but nowadays "OLTP" systems are complex and hybrid and Oracle has to manage a wide range of DML, concurrency and locking strategies.  If you have the luxury of designing OLTP from scratch, keep it simple and clean.

You can find a full list of development tools here.


MySQL - Are column names stored in each row in full length? MySQL: I'm not finding this in the manual or in google, but do column names get stored in full in each row?

I suppose that this might change depending on compression, but leaving compression aside, suppose I have a normal InnoDB table with a column's name "really_long_name_for_a_column". Will this name (29 bytes) be stored in each row for every row in the table? Or is a shortened version recorded for each row or nothing at all?

Thanks!

The answer is of course an emphatic no. It would be a very silly thing to store them in each and every - of the possibly billion - rows.

Column names are metadata and are stored only once, in the system tables.

The format of the rows and how they know where a column ends and the next starts depend on the engine of the table. It differs between InnoDB, MyISAM, Memory, etc. Check the respective documentation for more details.

Having said that, nothing prevents you from writing - and using - an engine of your own that stores the column names in each and every row. It would still be a very silly thing to do. You should also consider where the names will be kept when the table has 0 rows, eg. immediatey after creation of the table or after it has been truncated.