MySQL - Connection blocked because of many connection errors - How to find root cause?

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings


sp_executesql with user defined table type not behaving correctly

sp_executesql is for executing ad-hoc T-SQL. So you should try:

EXECUTE sp_executesql N'exec dbo.Repro @MetricData',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3


Joining relations based on distance between geometry columns?

ST_MakePoint(lat, long)

That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint

AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0

That doesn't make any sense, it should be

ST_DWithin(origin, ST_MakePoint(long,lat), radius)

I think this is what you want,

SELECT *
FROM user AS u
INNER JOIN regions AS r
  ON u.id = r.userID
  AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY

maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?

Don't forget to add your spatial index. In this case you can actually add a compound index.

CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);

See also,

Finding the nearest geo points across two tables?


The possibility to define a composite unique constraint for specific cases

Add a foreign key column in the user table that references the primary key of the address table. Call it default_address_id. This enforces uniqueness because it can have only one value at a time for a given user.
Then drop the is_default column in your address table because it risks disagreeing with the foreign key in the user table.


design database for electrical meter data?

The data in your CSV file gives enough information about which meter shows what reading at what time. This table, I believe, should not be decomposed as the information that every row contains is comprehensive in itself. There is no point in creating a Times table here, as there are going to be a lot of times when readings will be taken, hence storing times in a lookup table would be waste of disk space.

If I was developing a real time system for your requirements, the table you have given would have been a part of a larger schema. I would create something like the following:

TABLE customer
( customerID                     INTEGER        PRIMARY KEY
, cust_name                      VARCHAR(100)   NOT NULL
, customer_billing_address       VARCHAR(200)   NOT NULL
, registration_date              DATETIME
, ...
, ... -- Any other columns that you may think appropriate
, ...)

TABLE meter
( meterID                        INTEGER        PRIMARY KEY
, customerID                     INTEGER
, meter_address                  VARCHAR(200)   NOT NULL
, start_date                     DATETIME
, ...
, ... -- Any other columns as per your requirements.
, ...
, FOREIGN KEY (customerID) REFERENCES customer(customerID)
-- ^^ This is because one customer may have multiple meters.
)

Now I will have a meterReading table taken from your question.

TABLE meterReading
( meterReadingID                 INTEGER        PRIMARY KEY
, meterID                        INTEGER        NOT NULL
, readingTime                    DATETIME       NOT NULL
, voltage                        NUMBER(18,2)   NOT NULL
, ... -- any other columns
)

Now if I want to see the total voltage eaten up by a meter in a month, as meter readings are incremental, i.e., they don't reset, I would simply write a query like the following:

SELECT meterID, MAX(voltage) - MIN(voltage) as consumption
  FROM meterReading
 WHERE readingTime BETWEEN TO_DATE('01-JAN-2014)
                       AND TO_DATE('31-JAN-2014) + 86399/86400;

The last + 86399/86400 was because of the following:

When we use only date values and not time information, most databases treat it as beginning of the day, e.g., 31-JAN-2014 00:00:00.
There are 86400 seconds in a day. So, + 86399/86400 adds one whole day minus one second. So, it is just one second before 01-FEB-2014.

Next, if I want to see the list of customers and how much power they consume in a month, I would simply write the following:

  SELECT c.cust_name, SUM(mr.consumption) AS total_consumption
    FROM customer c INNER JOIN meter m
         ON c.customerID = m.customerID
         INNER JOIN (SELECT meterID
                              , MAX(voltage) - MIN(voltage) as consumption
                       FROM meterReading
                      WHERE readingTime BETWEEN TO_DATE('01-JAN-2014)
                                            AND TO_DATE('31-JAN-2014)
                                                + 86399/86400) mr
         ON mr.meterID = m.meterID
GROUP BY c.cust_name
ORDER BY SUM(mr.consumption);


Connecting Oracle 11g to Postgres via dblink

Community Wiki answer collecting answers left in question comments

a_horse_with_no_name:

The ODBC driver needs to be on the computer that connects to the Oracle server not on the Oracle server itself. Plus: you can't use dblink for this, Postgres' DBLink can only connect to other Postgres servers. But you can use a foreign data wrapper. You only need the Oracle client libraries (not the ODBC driver) on the Postgres server, nothing needs to be installed on the Oracle server.

If you don't have administrator access to the Oracle database nor can you change anything in Postgres, then there is nothing you can do. Oracle's DBLink can only connect to other Oracle servers. You could install Oracle's "Heterogeneous Services" to be able to connect through ODBC (the Postgres ODBC driver, not the Oracle one) but that again would require that you have admin access to the Oracle database.

You need install Oracle's "Heterogeneous Services" then install the ODBC driver for Postgres, not the one for Oracle.

EdStevens:

It's more than just the ODBC driver.  You'll need to install the Oracle Transparent Gateway for ODBC.  The Oracle database sends a client request to the Oracle listener, that uses a gateway config file to further resolve the request to an ODBC DSN. The full instructions are pretty complex but I have an article outlining the pieces and their connections. That should give you a good overview and framework with which to understand the more detailed instructions.

You'll need both the Oracle gateway and the Postgres ODBC driver. The best link I could find was this:

Where to download DG4ODBC from?

Of course, when you get there, you'll want to adjust for your version.  For the ODBC driver, that would come from Postgres.


The error in session pooling mode indicates that you are doing something different that is not connected with pgBouncer: if the application has the same session all the time, the only explanation for a missing prepared statement is that it either was never declared or deallocated. That should happen just the same without pgBouncer.
The maxim here is that everything should work with session pooling mode.
The error with transaction pooling mode indicates that you are using the same prepared statement name for different statements. Now when an application thread gets some other thread' session in the next transaction, and it gets the prepared statements along with it, that is no surprise.


SELECT m.code,
       i.TradeCode,
       f.Ftrdate
       FROM ( SELECT MAX( Fintrade.Ftrdate ) as Ftrdate,
                     Fintrade.ID as ID
                     FROM Fintrade
            GROUP BY Fintrade.Ftrdate )  as f
INNER JOIN (SELECT itemtrans.iteID as iteID,
                   itemtrans.FtrID as FtrID,
                   MAX( itemtrans.TradeCode ) as TradeCode
            FROM itemtrans
            GROUP BY itemtrans.FtrID ) as i on f.ID = i.FtrID
INNER JOIN ( SELECT MAX( material.code ) as code,
                    material.id as id
            FROM material
            GROUP BY material.id ) as m
                    on i.iteID = m.id


Find total duration of each consecutive series of rows

Query 1, tested in SQLFiddle-1

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,          -- the first bed a patient uses
                                           -- can be omitted
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
  ( SELECT patient_id, bed_id, admitted, discharged
    FROM t
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT *
            FROM t AS prev
            WHERE prev.ward_id = @ward_id_to_check
              AND prev.patient_id = t.patient_id
              AND prev.discharged = t.admitted
          )
  ) AS st
JOIN
  ( SELECT patient_id, admitted, discharged
    FROM t
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT *
            FROM t AS next
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = t.patient_id
              AND next.admitted = t.discharged
          )
  ) AS en
    ON  st.patient_id = en.patient_id
    AND st.admitted <= en.admitted
GROUP BY
    st.patient_id,
    st.admitted ;

Query 2, which is the same as 1 but without the derived tables. This will probably have better execution plan, with proper indexes. Test in SQLFiddle-2:

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
    t AS st    -- starting period
  JOIN
    t AS en    -- ending period
      ON  en.ward_id = @ward_id_to_check
      AND st.patient_id = en.patient_id
      AND NOT EXISTS
          ( SELECT *
            FROM t AS next
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = en.patient_id
              AND next.admitted = en.discharged
          )
      AND st.admitted <= en.admitted
WHERE
      st.ward_id = @ward_id_to_check
  AND NOT EXISTS
      ( SELECT *
        FROM t AS prev
        WHERE prev.ward_id = @ward_id_to_check
          AND prev.patient_id = st.patient_id
          AND prev.discharged = st.admitted
      )
GROUP BY
    st.patient_id,
    st.admitted ;

Both queries assume that there is a unique constraint on (patient_id, admitted). If the server runs with strict ANSI settings, the bed_id should be added in the GROUP BY list.


How can I determine the average number of transactions per second in MySQL?

Have you looked at stats from \s? I wasn't sure by 'transaction' you simply mean queries.


Im not sure if this answers all of your questions but this sounds very similar to what I do with our data warehouse. However the way i interpret your post you would want to have 1 table with your data, and another with your table definition meta data.

For the data table add a column to flag is_current. (Either an int or bit datatype)
Also have 2 date columns valid_dt_from & valid_dt_to.

If a row changes you update  the old row is_current = 0, and valid_dt_to =getdate ().

Insert the new row with is_current =1 and valid_dt_from = getdate ().

You can do the above with a single merge query.

To get the current data select * from table where is_current = 1.

Add new columns as they are needed. But don't delete old columns. In theory if you have to reconstruct the table to previous point in time all rows from that time should have null values at that time.

Create a similar table to define your data table definition. Add a record to show when each field gets added, removed and if it is current.

Your select query could be built dynamically. Where is current =1, or reconstructed as at a point in time.


Create a MySQL database with charset UTF-8

Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868's answer):

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Original answer:

Try this:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

For more information, see Database Character Set and Collation in the MySQL Reference Manual.


Database Recovery Model - Simple vs. Full

Does the SIMPLE Recovery Model perform the same transaction logging as FULL Recovery Model?

Technically, no.  The simple recovery model minimally logs certain operations under certain conditions, just like the bulk-logged recovery model.

  keep record of each insert/update/delete transaction?

Yes, your data modifications will be logged (with a few DML exceptions, like SELECT INTO and INSERT ... SELECT under certain conditions).

  then why should FULL Recovery model be used?

For point-in-time recovery.  With the simple recovery model, you can only restore full and differential backups.  In other words, if you take a full backup at 2pm today, and you have a disaster at 5:05pm that demands a restore, then the most recent backup that you can restore will be your 2pm backup.  3+ hours of data loss.

But if you were in the full recovery model, and you had a full backup at 2pm today, followed by hourly transaction log backups and if the same disaster happened at 5:05pm, you could restore your 2pm full backup, and then restore your transaction log backups in between.  Depending on if your database's transaction log was affected or not by the disaster (in other more technical words, if you can backup the tail of the log) then you could potentially have zero data loss, by recovering to the point of the disaster.  Even if your transaction log is no longer available, and you can't take a tail log backup then you could still restore to your most recent transaction log (your SLAs will dictate your recovery point objective, and that will influence how frequently you take your transaction log backups).

  Where does the data loss potentially occur on SIMPLE Recovery Model?

Provided that the database isn't salvageable at all, then it'll be from the last full/differential backup to the time of disaster.

Restore a SQL Server Database to a Point in Time


What are the risks of upgrading DB major version in replica and then promoting it as master?

There are risks.  I doubt if anyone can enumerate all the risks.
Plan A:  Dump all the data via mysqldump (or other dump utility); upgrade the system; load the data.  This usually lets you safely do multi-major-version upgrades.
Plan B:  Upgrade one major version at a time.  (This has always been a requirement in MySQL, especially when going to 8.0.  MariaDB does a better job at relaxing the limitation to a single major.)
Plan C:  Get RDS to do the work.  I would expect that they have well-tested upgrade paths for when they force an upgrade.  (The Question is unclear as to whether your 10.1 is currently hosted by AWS or yourself.)
I would go with 'C' since they have all the tools to do everything with minimal downtime, etc.  They will probably use snapshots, etc, which you don't have easy access to.


Append-only and snapshots with PostgreSQL?

The SQL:2011 standard has a feature known as temporal tables or system-versioned tables which might be helpful for this type of use-case.

Basically, whenever you update or delete a record in the database, the database automatically holds on to the previous version of the record, including the time period when it was valid.

PostgreSQL doesn't support this feature natively, but there's an extension called temporal_tables. A tutorial can be found here. A few other DBMSes support temporal tables natively, including SQL Server 2016 and MariaDB 10.3.


Assuming you can't further increase the memory size then there are a couple of options.

If you aren't familiar with the command line and you really want to stick with a GUI style import then you can use BigDump (http://www.ozerov.de/bigdump/).  I used it once but it's been a while.  From what I remember you will download a file named bigdump.php (with some instructions) and put it on your webserver in the directory with the MySQL DB dump file that is too large to import via PHPMYADMIN.  Then navigate to it using your browser - something like http://your-website.com/bigdump.php.
If you are familiar with the command line and using a Linux based system then you can use code like like this:

mysql -u USERNAME -p DATABASENAME < FILENAME.sql

The database and user (with privileges to the database) will need to exist prior to running this command though.  Note I copied the above command from another source.  I always do my dumps and restores like this:
DUMP: mysqldump -u DB_USER -h DB_HOST -pDB_PASSWORD DB_NAME | gzip -9 > DB_NAME.sql.gz
RESTORE: gunzip < PATH_TO_DUMP/DB_NAME.sql.gz | mysql -u DB_USER -pDB_PASSWORD DB_NAME

Finally, and most painfully, you could choose to dump individual tables or groups of tables into small enough dumps.  Then restore these individual dumps one at a time via PHPMYADMIN.

FTP & RESTORE instructions:

Download FileZilla here: http://filezilla-project.org/download.php?type=client

Typically you can FTP into your server (especially a shared host) using the same login and password you would for SSH (you command line login).  Just make sure to set up the connection to your server properly in FileZilla.

Once connected your default path will likely be your $HOME folder (on nix machines).  If you aren't planning on doing a lot of FTPing just dump what you want in there.  You can always move it from the command line.

If you FTP'd a MySQL Dump into your $HOME folder then the RESTORE command for a DB named mydb and a db dump file named mydb.sql.gz (in the $HOME folder) would be:

gunzip < $HOME/mydb.sql.gz | mysql -u DB_USER -pDB_PASSWORD mydb

where you would obviously need change the DB_USER and DB_PASSWORD values to what user and password you setup for the DB.

Remember:

The DB must already exist (but be empty) before the RESTORE command is executed
The DB_USER and DB_PASSWORD must be setup for the DB before the RESTORE command (you can do this from PHPMYADMIN - Privileges tab)
When you set up DB_USER from PHPMYADMIN it will default to only allowing DB_USER to access the DB from localhost (the server the DB is hosted on).  I'm assuming this is fine for your situation grant.  When you get into more advanced DUMPs and RESTOREs you may want to do it from another machine entirely which isn't hard but requires a couple more pieces of information be provided and DB_USER with the proper privileges.

FTP the DB and RESTORE it... Step by Step

Locate zipped MySQL dump file on your desktop (Example: C:\Web\DB\Dumps\mydb.sql.zip)
Download and install FileZilla
In FileZilla go to File->Site Manager and click on the New Site button (lower left quadrant of GUI). Set the host to a domain (Example: dest.com) on that resides on your DESTINATION server. Set Protocol to FTP or SFTP (this depends on your hosting, ask them).  Set the User and Password (Example: User = grant, Password = grant's password) - if the FTP protocol is SFTP then any set of valid SSH login credentials should work.
(In FileZilla) Click Connect (bottom left most button)
(In FileZilla) Once connected to the remote server (should be your DESTINATION server) use the left side of FileZilla to navigate to the directory where the dump file you located in Step 1 resides.  (The path for the local machine is on the far left, about half way down and it is labeled Local site:.  In our example the textbox next to Local site: would read C:\Web\DB\Dumps.)
(In FileZilla) Locate the Remote site: label directly to the right of Local Site:.
(In FileZilla) There is no technical reason you can't put the DB DUMP in the folder tha FileZilla opens up to.  It will likely default to your $HOME directory.  If you don't have any reason to put the DB Dump in a specific directory - such as for your own organizational purposes - then leave the path on the Remote site alone.  Write down that path (Linux Example: /usr/grant).
(In FileZilla) Locate the file on your local machine on the left half of FileZilla just under the Tree shown beneath the Local site: label you found in Step 5.  Double click on that file and FileZilla will FTP it to your Remote Site.
Within PHPMYADMIN on your DESTINATION server, create a new database (type mydb into the Create new database textbox and Click the Create button).
(In PHPMYADMIN) Choose mydb from the menu on the left.
(In PHPMYADMIN) Click on the Privileges tab (upper right).  Once on that page click on Add New User.
(In PHPMYADMIN->Privileges->Add New User) Provide a username (Example: mydb_grant) and password (Example: mydb_password).  In the Host dropdown choose Local.  Make sure that the radio button next to "Grant all privileges on database" is selected.  Then click Go (bottom right of screen).
SSH into your DESTINATION server.
(In your SSH terminal) Navigate to the directory your wrote down in Step 7.  Using a command like - cd $HOME or cd /usr/grant.  (On Linux machines $HOME is an environment variable that will equal something like /usr/grant.)
(In your SSH terminal) Execute the following command: gunzip < /usr/grant/mydb.sql.gz | mysql -u mydb_grant -pmydb_password mydb (there is no space between -p and the password because that is the way it needs to be, that is not a typo).

Once that last command completes your DB has been RESTORED.  I don't recommend using the DB user and password I provided simply replace them with more secure choices of your own.

DUMP and RESTORE on different servers without FTP

If you wish to skip the FTP stuff and don't mind redoing the DUMP then you can do it this way:

Login into the DESTINATION server (where you want the DB to reside - we'll call it destination.com)
Create the DB, User, and proper privileges as mentioned above on the DESTINATION server
Make sure the privileges on the SOURCE server allows for SOURCE_DB_USER to access the DB remotely - either allow access from dest.com or from % (% - everyone and everywhere if they have the right username and password)
On the DESTINATION server run: mysqldump -u SOURCE_DB_USER -h source.com -pSOURCE_DB_PASSWORD mydb| gzip -9 > $HOME/mydb.sql.gz where source.com is the SOURCE server (where the DB currently resides)
Then (on the DESTINATION server) run: gunzip < $HOME/mydb.sql.gz | mysql -u DEST_DB_USER -pDEST_DB_PASSWORD mydb

That should be all you need to do to move the DB from one server to another.  Caveats are that I've only ever done this on my own dedicated Ubuntu (with standard LAMP) and Dreamhost's VPS servers (which I believe run CentOS but I could be wrong).


What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?

If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to NVARCHAR(4000) on the way out as that is the most that can be stored by a SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since this scenario concerns storing the value in a local variable or returning in a result set (as opposed to how it is stored in the table), there is no reason to use less than 4000, and no use in specifying MAX since the value stored in the table couldn't hold that data anyway.

However, why would a path be stored as a SQL_VARIANT in the first place? Even if the total length never exceeds even the default 260 characters, it would still be best to store as NVARCHAR(500), or NVARCHAR(1000), or something like that.


Here's one attempt:

SELECT MIN(created_at), MAX(created_at)
FROM (
    SELECT created_at
    FROM runs
    ORDER BY id DESC
    LIMIT 10000
) as x;

Your query is evaluated as:

SELECT MIN(created_at), MAX(created_at) FROM runs

Then it is ordered according to id (which doesn't make sense) and a maximum of 10000 rows is returned (only 1 row exists)

My query first picks the 10000 most recent id's

SELECT created_at
FROM runs
ORDER BY id DESC
LIMIT 10000

From there it picks min and max for created_at


Restore MySQL dump to a container fails with ERROR 1114 "The table is full"

"Table is full" implies a disk space problem.  But there are other issues:
How much RAM in your container?
tmp_table_size=2G
max_heap_table_size=2G

not more than 1% of RAM available.  Anyway, those are used during complex SELECTs; they are not used during INSERTs.

dump of size around 18GB

Depending on the datatypes, etc, etc, the amount of disk needed could be a lot less or a lot more than 18GB.  I would be skeptical about 30GB being enough.  The dump is text; the data may be something else.  For example, a 4-byte INT could take 2 bytes ("0,") in the dump or 12 bytes ("-1234567900,") in the dump.  After loading into the database it is likely to take 8-12 bytes, depending on overhead.  That might be a 6x expansion (2->12) or a shrinkage (12->8).  (That's just one example.)
If you have integers arbitrarily in BIGINT, that 4 bytes becomes 8.  Shrinking the datatypes (where appropriate) will shrink the disk space needed.  Please provide SHOW CREATE TABLE; there may be some other obvious suggestions.
And what is "overlay"?  It really looks like it hit a limit of 5GB.


That error is simply telling you that the volume(s) you have tempdb on is/are full. Unless you have explicitly altered the file layout of tempdb it will all be on C:. You must be pretty low on space where tempdb is unless the amount of rows for that 24 hour period is massive.

Anyway, spooling large amounts of data into a temporary table onyl to delete most of them immediately is pretty much always the wrong way to go: with decent indexes on that table you should be able to extract the relevant data with a SELECT query like so:

SELECT user_id, MIN(action_date)
FROM   your_table
WHERE  action_date BETWEEN <start> AND <end>
GROUP BY user_id

The above will list every user who touched the system (so has entries in that table) between the dates/times specified along with the first time they touch the system in that time period.

It would be useful to list in your question what keys and indexes you currently have on that table. An index with action_date is going to be pretty much essential. A compound index that covers action_date and other columns involved may be even better, though will take more space to store, depending on the balance of data. How any unique users do you have and over how many days has this data been recorded? (again: add such details to your question so everyone sees them easily, not just in a comment added to this answer)

If you just want to list users who have never logged an action before this time, then you need something like:

SELECT user_id, action_date
FROM   (
       SELECT user_id, action_date=MIN(action_date)
       FROM   your_table
       WHERE  action_date BETWEEN <start> AND <end>
       GROUP BY user_id
       ) derived_table
WHERE NOT EXISTS (SELECT user_id FROM your_table WHERE action_date < <start>
                  AND derived_table.user_id = your_table.user_id)

I'm using the original query as a derived table here to avoid the chance the the query planner will try to apply the NOT EXISTS check to every one of those 1,800,000,000 rows, instead this way is should definitely find the users who were active today and run the sub-query once for each of them once only (so the sub-query will run at most once per user). For this to work you are going to need an index covering user_id (one covering user_id, action_date would most likely be better still).

Of course if you don't have sufficient indexes already adding them is going to be a very I/O intensive operation as for each SQL Server will need to read all those 1.8 billion rows and write all the index rows to go with them.


You created the database with double quotes, so the name is case sensitive now. You need to use the dreaded double quotes when dropping the database as well:
drop database "testV5";

Note that using quoted identifiers is discouraged


error while trying to retrieve text for error ORA-12154

If those lines really start with spaces in tnsnames.ora, that will not work. Remove the leading spaces in the first line (MY_ERP =), becasue an alias can not start with a space.

You can also try setting TNS_ADMIN to C:\Oracle\ora90\network\ADMIN, because ORA-12154 means the connection identifier could not be resolved, mabybe PL/SQL Developer searches for tnsnames.ora somewhere else:

$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause:  A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
//   - If you are using local naming (TNSNAMES.ORA file):
//      - Make sure that "TNSNAMES" is listed as one of the values of the
//        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA)
//      - Verify that a TNSNAMES.ORA file exists and is in the proper
//        directory and is accessible.
//      - Check that the net service name used as the connect identifier
//        exists in the TNSNAMES.ORA file.
//      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
//        file.  Look for unmatched parentheses or stray characters. Errors
//        in a TNSNAMES.ORA file may make it unusable.
//   - If you are using directory naming:
//      - Verify that "LDAP" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Verify that the LDAP directory server is up and that it is
//        accessible.
//      - Verify that the net service name or database name used as the
//        connect identifier is configured in the directory.
//      - Verify that the default context being used is correct by
//        specifying a fully qualified net service name or a full LDAP DN
//        as the connect identifier
//   - If you are using easy connect naming:
//      - Verify that "EZCONNECT" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Make sure the host, port and service name specified
//        are correct.
//      - Try enclosing the connect identifier in quote marks.
//
//   See the Oracle Net Services Administrators Guide or the Oracle
//   operating system specific guide for more information on naming.

Finally: Oracle 9 was release 18 years ago, install a recent client, if your database is compatible with it.


SQL Server - Alter table vs drop and create

I recently did this in my organization wherein we wanted to handle a table with billion + rows.

All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo

Test below process on a small table and get your self comfortable before doing it in PROD.

create 2 schemas fake and shadow with authorization dbo.
Create a table with the columns and data types you want in shadow schema e.g. create table shadow.Correct_Table ...
Insert the data and create all the indexes that the original table has in the shadow schema table.
This way you have identical copies of table with data and indexes but they are in different schemas (logically separated).
Once done update stats on the table with shadow schema.
Switch the schemas (This is a metadata operation and is extremely fast)

--- ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;

BEGIN TRANSACTION;

  ALTER SCHEMA fake TRANSFER     dbo.original_table;
  ALTER SCHEMA dbo  TRANSFER  shadow.Correct_Table;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER fake.Lookup;

Do a final check to see if everything went as planned. You should do a select count(1) from dbo.Correct_table
Once step 7 is confirmed and you are happy, drop the shadow.table, shadow schema and fake schema as clean up.


How to index an property object in MongoDB?

I was able to make this work after altering your model. Hopefully this still fulfills the use case you are targeting. To bypass the dynamic nature of your fields, I created a field named "field" and a field named "value". This allows me to create a multikey index on known fields.

db.myColl.insert(
{

"user_id" : NumberLong(24),
"request_datetime" : ISODate("2015-06-02T15:52:12.000-05:00"),
"total_amount" : 102.62,
"status" : NumberLong(1),
"adfields" : [
    {field:"27", value : "413703562015060200132"},
    {field:"28", value: "S003POS4"},
    {field:"29", value: "HAYLEYS"},
    {field:"30", value: "1"}
    ]

})

Then I executed a find against this for db.myColl.find({"adfield.field":"30"}) for a before picture of the query. I also used db.myColl.explain().find() to analyze it.

Then I created the index via     db.myColl.createIndex({"adfield.field"1,"adfield.value":1"}).

I executed the find from above and sure enough I see an IXScan and isMultiKey is true in the explain() output.

Again; hopefully the alteration to your model still keeps with the spirit of what you are attempting to accomplish.

You can read more details about mutlikey via http://docs.mongodb.org/manual/core/index-multikey/ .


What is the difference between select from function and select function

It's not the FROM that's causing the different query plans. It's the bad aliasing.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT generate_series(1,10));

And

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT generate_series FROM generate_series(1,10));

will return the same plan. As will this,

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT x FROM generate_series(1,10) AS gs(x));

However, this won't.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT a FROM generate_series(1,10));

Notice: you forget to alias the generate_series to a Now you're essentially running a correlated subquery. It's not just more work, but it's returning the entire table and not just 10 rows.


Excel --> MS SQL; skip blank calculated rows

I would import the entire spreadsheet into an empty "temp" table, then run a sql statement to copy the rows I want across to my permanent table.

This way it is easy to setup rules so that you only get the rows you want and it is easy to test both the import and the copy jobs separately.


Can a table column be written to treat two different values as the same value?

This isn't a perfect solution but may work for you.

Rename your source table.

EXEC sp_rename 'countries','countries_source'

Then put a view on top of it

CREATE VIEW countries AS
SELECT fields,
    CASE WHEN CountryCode = 'GB' THEN 'UK' ELSE CountryCode END AS CountryCode
FROM countries_source

Your existing SELECT queries won't have any problems.  I think you will have to modify your UPDATE queries and might have to update your INSERT and DELETE queries as well.  But some quick tests should let you know if that's the case.


Can large data deletion affect Innodb buffer pool performance?

I restarted yesterday the server with making some changes in the configuration file :
innodb_buffer_pool_size = 48
innodb_buffer_pool_instances = 32
innodb_read_io_threads  = 16
innodb_write_io_threads 8

The buffer pool is used, no more slowness noticed until now (after some refresh).
However, still not understand the reason of the impact of deletion on the buffer pool. Thus I believe that the bad way the app is using queries via ORM is the source of this issue.


MySQL workbench can't deal with MariaDB's new Invisible columns

MySQL Workbench currently supports MySQL version 5.6, 5.7 and 8.0 and has support for MySQL invisible columns indexes, (recursive) CTEs, JSON and all other (for a GUI relevant) features added to MySQL (like cached sha2 passwords).


Is there a SELECT statement to subtract rows of the same column?

If I understood your question correctly...

SELECT mem_id,
       Sum(CASE TranType
             WHEN 'C' THEN Amount
             WHEN 'D' THEN -Amount
           END) AS Amount_Owed
FROM   YourTable
GROUP  BY mem_id;


How to force a delayed member to become primary in case of failure in mongodb

Firstly, it is not recommended to have a replica set with even number of members. Hence, it will be better to add an arbiter or another data bearing node in the DC2

Coming to your question, the DR servers are not supposed to be used for ensuring availability they are meant to provide limited functionality during recovery from a disaster. Assuming the configuration that you have provided:

a.) How i will use DR mongodb as its a delayed member.

For your configuration assuming 1 vote per server, the majority will be 3. If DC1 goes down then no server will be able to get majority votes. Hence there will be no primary. Thus for the duration of the downtime your application will be able to service only read requests for the time up-to which the data has been synched on the delayed node.

b. When the DC come back online then how i will sync the database from DR to DC.

Since the application is in read only mode, hence once the DC1 comes up, there will be a primary election and server in DC1 with the latest data will be elected primary and the server in DC2 will be relegated to the secondary and the replication would take care of the synch


ORACLE Shifting from from one version to another

Since you upgrade your OS, at some point you need to relink your Oracle software. In that case, easiest is to make a new software installation on the new server. Unmount the luns where the database is located from the old server and mount that on the new server where the database files should be mounted on the same locations as on the old server.

I hope that the ORACLE_HOME is not on the same lun[s] as where the database is located. If ORACLE_HOME does exist on the same LUN[s], make sure to create the new ORACLE_HOME on a different PATH and make sure that config files like /etc/oratab and listener.ora reflect this change.

This should give the quickest migration.
If there is to be done more than just re-mount the LUN[s], make sure to have a capable DBA involved. That will save you a lot of downtime and problems.


Is there any way to get recently inserted value in a table without using aggregate functions?

Your existing index doesn't help you out at all, since you are actually asking for the last timestamp of every possible user. This means the only way for MySQL to answer this is to do a full table scan.

As @ypercube commented, an index on (user_id, login_time) is good for this kind of query - MySQL could utilize such an index without actually going to the table.

Adding an index to a table will indeed lock the table; but there are solutions to that as well. Take a look at oak-online-alter-table or pt-online-schema-change (disclaimer: I'm author of the first), both of which are scripts which allow online table alteration via simulation, triggers and incremental copy.

You might also want to solve this in a completely different way: create a new table like

CREATE TABLE last_user_login_time (
  user_id INT UNSIGNED PRIMARY KEY,
  last_login_time DATETIME
);

And

INSERT INTO last_user_login_time
  VALUES (the_user_id, NOW())
ON DUPLICATE KEY UPDATE last_login_time=NOW()

to this table upon any new login. Thus, keeping the last login time per user at any given time. This means more writes (an additional write per login), but I suspect this table should be very small and the impact proportionally small. Reading from it, of course, is trivial.


What are the implications of creating an Integration Services Catalog (SSISDB) where msdb-deployed packages exist?

Yes, you can use both the Package Deployment Model (msdb) and the Project Deployment Model (SSISDB) in tandem with no ill-effects.
As a consultant, if I walked into a place that had both, it'd indicate that they've been using SSIS before the 2012 release and there's been no reason to upgrade existing work to a new model. In heavily regulated industries, going through the bureaucracy just to make your life easier often isn't a consideration. That or someone had a very good reason for using the legacy/msdb.
A lot of "design sins" (lack of configuration, lack of logging) in the Package Deployment Model aren't a concern with the Project Deployment Mode (logging is automatic and connection managers are easily externally configurable). The Project Deployment Model also makes parent/child design patterns more easily implemented.
From a management perspective, that's the real beauty of the SSISDB. If you want to run a package, you call the native procedures and it spins up dtexec. For packages in msdb, you are looking at either an xp_cmdshell call or a SQL Agent job as there's no native way to  run them.
Backup and recovery of an SSISDB is a touch more complex due to key management but it's documented.
Deployment of packages uses two different mechanisms (dtutil vs isdeploymentwizard/managed object model/bulk insert)
Execution uses the same executable: dtexec


Cannot rejoin or add instance to MySQL InnoDB cluster

This has been fixed in MySQL Shell 8.0.23: https://dev.mysql.com/doc/relnotes/mysql-shell/8.0/en/news-8-0-23.html
An instance that belongs to an InnoDB Cluster is identified by its server UUID. If the UUID changed after the instance had left the cluster, for example because you used MySQL Enterprise Backup to restore from a backup, then the instance could not be rejoined to the cluster. Now, if the cluster encounters this situation, it checks the metadata to see if the instance can be identified using its host and port. If found, the metadata is updated based on the options used for the rejoin operation. This check is executed during the Cluster.rejoinInstance() and Cluster.rescan() operations.

Additionally, a check is executed to verify the serverId of all the instances is registered in the metadata as an instance attribute. If it is not, the metadata is updated accordingly. This check is executed on add, rejoin and rescan operations. (Bug #26649039)

Please upgrade MySQL Shell to 8.0.23.


I need a tool that if i do any schema change in the develop server it will create a sync script that i can run on the production.

I would suggest you to take a look at Redgate's schema compare. Its worth the investment.

Note: I have used Redgate's schema and data compare and they are life saver.

Alternatively, you can use SQL Server Data Tools (SSDT)

  SSDT includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.


The subquery computes, for each user in USERS, the latest msg_id value:

SELECT *
FROM MessageTable
WHERE msg_id IN (SELECT (SELECT msg_id
                         FROM MessageTable
                         WHERE msg_to   = Users.user_id
                            OR msg_from = Users.user_id
                         ORDER BY msg_id DESC    -- or by a timestamp
                         LIMIT 1)
                 FROM Users)


Intentionally recursive instead of insert trigger does not want to nest

An INSTEAD OF trigger will not directly call itself when performing operations.

Consider if you had wanted the trigger to insert the row only if it had an even value, it would look very close to the first statement and you wouldn't want it to recurse.

From the CREATE TRIGGER Page:

  If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions.

That doesn't mean that INSTEAD OF can't be part of recursion. If the trigger makes changes to another table, it will call the triggers for those. The triggers could bounce back and forth and create recursion that way.

I believe that you can get the behavior you intend with a combination of INSTEAD OF to control the original insert and an AFTER trigger to add the additional row.


Why is Convert being used in this statement and how does it differ from CAST?

Convert style

If SalesYTD type is float or real:

1 = Always 8 digits. Always use in scientific notation.
If type is money and small money:

1 = Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

  Cast will use the default value for style: 0

If SalesYTD type is float or real:

0 (default) = A maximum of 6 digits. Use in scientific notation, when appropriate.
If type is money and small money:

0 (default) = No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

There are other differences and default values for style with other types but I think it covers your data types. See CAST and CONVERT (Transact-SQL)

  SUM window function

For each partition (=TerritoryID) it takes all SalesYTD, order them by DATEPART(yy,ModifiedDate) and sum them. Note that the Order part is not necessary or could be anything since you only do SUM and AVG but using it change the behaviors (see hypercube answer about it and moving average). With ROW_NUMBER and others, it would be important and mandatory.

Diffence between SUM and moving SUM (@hypercube answer):

declare @data table(TerritoryID int, SalesYTD int, ModifiedDate date)
insert into @data(TerritoryID, SalesYTD, ModifiedDate) values
(1, 1, '20100101')
, (1, 2, '20110101')
, (1, 3, '20120101')
, (1, 4, '20130101')
, (1, 5, '20140101')
, (2, 6, '20150101')
, (2, 7, '20160101')
, (2, 8, '20170101')

Select *
    , [Moving SUM] = SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate))
    , [SUM] = SUM(SalesYTD) OVER (PARTITION BY TerritoryID)
From @data

Output:

TerritoryID | SalesYTD | ModifiedDate | Moving SUM    | SUM
---------------------------------------------------------------------
1           | 1        | 2010-01-01   | 1 => 1        | 10 => 1+2+3+4
1           | 2        | 2011-01-01   | 3 => 1+2      | 10 => 1+2+3+4
1           | 3        | 2012-01-01   | 6 => 1+2+3    | 10 => 1+2+3+4
1           | 4        | 2013-01-01   | 10 => 1+2+3+4 | 10 => 1+2+3+4
2           | 6        | 2015-01-01   | 6 => 6        | 21 => 6+7+8
2           | 7        | 2016-01-01   | 13 => 6+7     | 21 => 6+7+8
2           | 8        | 2017-01-01   | 21 => 6+7+8   | 21 => 6+7+8

See OVER Clause (Transact-SQL) for window functions.


Temp postgres table ignores "excluding constraints"

I'd like to know what I'm not understanding correctly about excluding constraints.

What is happening here is that primary key (col1) creates a primary key constraint but doesn't create a separate not null constraint. It adds a not null "modifier" to the column. This works as a constraint, ie. no nulls are allowed in col1 but it is not a named constraint. That's why it's not removed by the excluding constraints, it's a column property.

As the CREATE TABLE documentation states:

  Not-null constraints are always copied to the new table. CHECK constraints will be copied only if INCLUDING CONSTRAINTS is specified. ..

If you had the table defined with an explicit CHECK column is not null constraint instead:

create table test (
    col1    integer,
    col2    integer,
    col3    text,
    unique (col1),
    check (col1 is not null)
);

then the excluding constraints would have succeeded. But if the table has a primary key defined, then all the columns of this primary key are created with the not null modifier and a create .. like test will copy these modifiers.

Your workaround is valid. Either of these will work:

create temporary table t as select * from test where false ;
create temporary table t as select * from test limit 0 ;


MySQL trigger before insert replace the data with relational table

It may help you in case of AFTER INSERT.

CREATE TRIGGER trigger_name AFTER INSERT ON orders
    FOR EACH ROW BEGIN IF (SELECT COUNT(*)
              FROM transaction
              WHERE order_id=NEW.order_id) != 0
    THEN
        UPDATE transaction SET active = 0 WHERE order_id=NEW.order_id;
    END IF;
END


An uncommitted transaction updates a column; how can a different transaction see the old column value?

You can use Read Committed Snapshot Isolation (RCSI) on your database, which uses space in TempDB to keep track of committed versions of the data.

Be aware that RCSI adds 14 bytes to every versioned row, so you might see some additional page splits in the database. It also requires that TempDB's space and I/O be monitored to make sure it's not slowing things down.

Read more by Paul White and in this question.


Did the Change Tracking internals change from SQL Server 2008 to 2012?

One doesn’t use the min_valid_version to track the changes. This is only used to validate if your client has to be reinitialized, if the metadata has been cleaned up before client could consume the changes.

CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)

Obtains the minimum version that is valid for use in obtaining change tracking information from the specified table when you are using the CHANGETABLE function.

Min_valid_version changes with the cleanup version and doesn’t depend on the changes to the user table. Every time cleanup thread runs there could be an update to min_valid_version irrespective of data changes.

Previous to 2012, min_valid_version was marked same as cleanup version, when in fact it should be one more than the cleanup version as metadata for that version has already been cleaned up. In 2012 that is what they changed to make sure they update the right min_valid_version.

One should not be tracking change using min_valid_version, instead should be saving the last_sync_version after every sync and call the CHANGETABLE to enumerate the changes after the last sync version.

By design - Min valid version changes with the cleanup version and doesn’t depend on the changes to the user table. Every time cleanup thread runs there could be an update to min valid version irrespective of data changes.

Resolve - Change procedure to use 'current_version' instead of 'min_valid_version'


Optimize very slow SQL joins on multiple tables or use different engine?

Your query using joins looks ok but it will help speed up the queries if you convert the timestamp_idx key to include session_id. This way the database engine won't have to to another/second lookup/sort and on tables with millions of rows, it will have significant improvement in response times.

ALTER TABLE <table name> ADD KEY ix_covering(`timestamp`, `session_id`,`request_id`);

Drop both indexes on all tables AFTER you have tested that the new index as per above does improve the performance.

Also, instead of

SELECT DISTINCT
    `A`.`session_id`

try

SELECT `A`.`session_id`
FROM `logins` `A`
INNER JOIN `profile_views` `B` ON `B`.`session_id` = `A`.`session_id`
WHERE `A`.`timestamp` > x
GROUP BY `A`.`session_id`
ORDER BY `session_id` DESC
LIMIT 100;


Loop through all databases and remove all users

I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server.

There is no need to delete all the users.

What you can do is use sp_help_revlogin to move all the logins that you are interested or your applicaiton/s need to the new server.

Once you move your database/s to the new server, you even dont have to sync up the users as the logins that you created from above script will be created with the SID from the old server.

As a side note, below script will help you fix the orphaned users:

WITH cte AS
(
SELECT dp.name AS DBUser,
       dp.sid AS DBSid
  FROM sys.database_principals dp
       LEFT OUTER JOIN sys.server_principals sp
         ON dp.sid = sp.sid
 WHERE sp.sid IS NULL
   AND dp.type = 'S'  -- SQL_USER
   AND dp.principal_id > 4
)
-- join to logins with the same name to get the sql statement to un-orphan the user
SELECT cte.*,
       sp.name,
       -- generate the SQL script to un-orphan the users that can be.
       LinkOrphanUserToLoginSQL = N'ALTER USER [' + cte.DBUser + N'] WITH LOGIN=' + sp.name + N';'
  FROM cte
       -- LEFT JOIN allows seeing all orphaned users
       LEFT JOIN sys.server_principals sp
         ON cte.DBUser = sp.name
        AND sp.type = 'S'
ORDER BY DBUser;


ODBC Driver "Oracle in OraClient11g_home1" required but can't seem to get it on Windows 10

Never mind. I was looking in the 32bit drivers without realizing. But the problem I have now is that it's a 64bit driver and the Excel sheet that utilizes the connection is looking for the 32bit one.


stop all nodes.
On first node run -> galera_new_cluster
Once it is started.
Then on Second node run - > service mysql start


How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

Something like

WHERE
   (@p1 IS NULL OR SomeCol1 = @p1)
   AND
   (@p2 IS NULL OR SomeCol2 = @p2)
   AND
   (@p3 IS NULL OR SomeCol3 = @p3)
   AND
   (@p4 IS NULL OR SomeCol4 = @p4)

This will probably run slower then using separate SELECTs separated by IF. However, you do have 16 permutation. So I'd probably combined both techniques:

IF (common combination of NULL parameters #1)
 SELECT ...
ELSE IF (common combination of NULL parameters #2)
 SELECT ...
ELSE
 SELECT using WHERE above


How can I have SQL Server listening on more than one IP address?

On the Protocol tab of configuration manager, there is a "Listen All" option, make sure that is set to yes.

Keep in mind that having an active listener on a public connection is asking for trouble, best to do it over a VPN, or filter allowed incoming connections by IP address, or at least use something other than the default port.


First of all, I highly recommend the scientific paper in which Dr. Edgar Frank Codd published the relational framework to the general public in 1970, i.e., A Relational Model of Data for Large Shared Data Banks. There, in section 1.1, “Introduction”, Dr. Codd himself states that:

  This paper is concerned with the application of elementary relation theory to systems which provide shared access to large banks of formatted data.

  © Association for Computing Machinery. Communications of the ACM, Volume 13, Issue 6 (pp. 377-387), June 1970.

So, yes, the terms relation and (hence) relational come from a mathematical background. Dr. Codd —who, apart from his academic and research credentials, had about 20 years of first-hand experience in computing and information processing— envisioned the enormous advantages of applying the relation (an abstract construct, naturally) in the field of data administration.

I am not a mathematician but, basically speaking, a relation is an association between sets, a set being a collection of elements (this external resource gives a definition of mathematical relation that may help to understand it from a different perspective). When working with the aid of a SQL database management system (DBMS for brevity), a well-known approximation of a relation is a table, in which case the association takes place between the types of its columns. Evidently, in SQL platforms that do offer DOMAIN support (e.g., Firebird and PostgreSQL), the association occurs between the domains fixed for the columns of the table in question; see the sections below for significant details.

In that respect, I am going to cite again Dr. Codd, who in section 1.3, “A Relational View of Data”, asserts that:

  The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ⋯ , Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on.1 We shall refer to Sj as the jth domain of R. As defined above, R is said to have degree n. Relations of degree 1 are often called unary, degree 2 binary, degree 3 ternary, and degree n n-ary.

  1 More concisely, R is a subset of the Cartesian product S1 × S2 × S3 ⋯ × Sn.

  © Association for Computing Machinery. Communications of the ACM, Volume 13, Issue 6 (pp. 377-387), June 1970.

And I agree with other answers in that it is very relevant to point out that Dr. Codd made some adaptations to the mathematical relation in order to get the most out of it regarding data management, and they are explained in the paper referred to before and throughout his extensive bibliography.

Relation and relationship

A situation worth bringing up is that, when dealing with these subjects, there may arise confusion due to the similarities that exist regarding the everyday (non-mathematical, non-technical) definitions of the terms relation and relationship —which, as a non-native English speaker, I find particularly understandable—.

The entity-relationship view and the relational model

Other factor that I think may as well cause confusion (and is closely associated with the technical connotations of the two terms brought up above) is that, when learning to design databases, a student or practitioner is typically first introduced to the methodology proposed by Dr. Peter Pin-Shan Chen in the entity-relationship view of data (published in 1976), which suggests two different implements (i.e., the entity and the relationship) to delineate a conceptual schema, and then, only after the definition of said schema is stable, the student or practitioner is introduced to relational terms and instruments (e.g., the relation) when declaring the logical layout of the pertinent database. Within the conceptual frame of reference, relationship holds connotations that are much more closer to the everyday sense of the word.

Then, perhaps, that circumstance also adds to the relation and relationship issue —but the sequence of firstly defining the conceptual schema and subsequently declaring the corresponding logical design is of course quite appropriate, as I will detail in the following sections—.

Responses to each of your subquestions

I consider that having included those three subquestions is really pertinent because they establish a broader context for the post, so they should not be overlooked. In this way, apart from exclusively addressing why the terms relation and relational are used (which certainly is very significant and is the title of the post, but it is not the entire post), said subquestions can assist in comprehending more of the scope of the relation and the relational model when one is involved in a whole information management project (quite relevant since this a site about database administration) and is therefore working at different levels of abstraction. In this manner, I am going to share my take on those particulars below.

Subquestion no. 1

  Why is, for example, a Person, considered to be a "relation"? In English, a relation is a noun that describes how two entities are associated. It doesn't refer to the entities themselves. In the context of relational databases, "relation" refers to the entities themselves. Why?

Conceptual level

In a given business environment, Person can be considered an entity type depending on how the people who work there (business experts and database designers) conceptualize it. And, yes, in that business environment, there may be different properties of interest with respect to the Person entity type, e.g., Name, BirthDate, Gender, etc.

Moreover, the Person entity type may hold certain relationship (or association or connection) types with itself or other entity types; e.g., Person may be associated with an entity type named UserProfile, which in turn may have its own properties of interest, let us say, Username and Password.

But, (a) the entity types, (b) their corresponding properties, (c) the relationship types between entity types and (d) the relationships between the properties themselves are notions that “belong to” the particular business environment in which they are deemed of significance. They are devices used by database designers that work closely with business experts in order to define a context-specific conceptual schema, at the design phase.

Thus, at the conceptual level we basically work with the structure of the ideas that arise in the real world’s segment of interest, i.e., (1) prototypes of things and (2) prototypes of relationships between prototypes of things, we do not work with (3) relations —employing this last term in the sense of the relational framework of data—.

Logical level

After Person was precisely delineated as an entity type at the conceptual level, and if one wants to implement a relational database that conveys the meaning of Person and all the concepts associated with it, then the facts about entities of that type can be managed by virtue of a mathematical relation at the logical level, and take advantage of the science based operations that can be performed on that abstract construct (i.e., define it, constrain it and manipulate it).

Yes, one can name a certain relation Person when defining the logical arrangement of a database, but that does not transform the “real world” concept of Person into a relation, one approaches it as such because of the benefits that are obtained when managing information about it, e.g., applying relational algebra operations on it to derive new relations (and therefore one is deriving “new” information). Said benefits become more evident taking into account the fact that the entities of a certain type make up a set, and the values of a certain property make up a set too.

And, yes, as mentioned in preceding paragraphs and in other answers as well, one of the paramount aspects of a relation is the connection that exists between its domains —that are typically used to represent the properties of entity or association types that are part of a conceptual schema—. For example, let us say that we have declared the following (ternary) relation:

Salary (PersonNumber, EffectiveDate, Amount)

…and let us suppose that, in the business environment in question, the tuple —which (i) stands for a particular entity, i.e., an instance of an entity type from the applicable conceptual schema, and (ii) whose SQL counterpart is a row—

Salary (x, y, z)

…would carry the meaning

“The Salary payed to the Person identified by PersonNumber x on EffectiveDate y corresponds to the Amount of z”.

Accordingly —to describe things in an approximate manner—, the connection between the three domains is of prime importance, they are all related (and, yes, a unary relation would involve one domain only). The connection among all the values of a certain domain is very significant too, as they constitute a set of a precise type. Also, the contents of each tuple of the Salary relation must fit in the structure of the assertion illustrated above.

Conceptual-level relationships and logical-level relations

As demonstrated, I have now dealt with database management at two different levels of abstraction, namely conceptual and logical —and there is yet a lower level known as the physical one, which in SQL DBMSs typically involves, e.g., indexes, pages, extents, etc.—.

So, in accordance with the notions explained before, at the logical level one works exclusively with (a) mathematical relations, where (b) the conceptual relationships or associations are represented by (c) the values contained in the tuples of such mathematical relations, and said values are usually delimited via FOREIGN KEY constraints so that they can represent the applicable relationships accurately.

And, yes, associative entities, i.e., instances of relationship types with a many-to-many (M:N) cardinality ratio, can be conveyed by way of the tuples of a single mathematical relation —with the corresponding constraints declared appropriately, of course—.

Subquestion no. 2

  I understand that relational model came after the hierarchical and network models. But in those models, the entities also have relations to one another. So why call this model the relational model? Is there a more specific phrase/term? Or maybe we should say that all three models are relational models, but the hierarchical and network models are specific types of relational models?

Network and hierarchical DBMSs preceded their formal theoretical support

It is opportune to point out that the theoretical support around the hierarchical and the network approaches was, in fact, created in terms of previously existing DBMSs, with the aim of, among other aspects, testing and establishing the soundness of (1) said kinds of software and (2) the linked data management practices —an upside-down phenomenon, from my point of view—.

Incomplete in comparison with the relational framework

That being said, although there are hierarchical and network DBMSs that predate the relational model, and even when Dr. Codd referred to each of those approaches as a “model”, none is defined as such in the same way that the relational framework is. The relational paradigm provides scientific constructs for the (i) definition, (ii) restriction and (iii) manipulation of data, and the hierarchical and network approaches lack full theoretical support to cover all of the three sorts of constructs previously mentioned.

Network and hierarchical features

Also, as stated before, the entity and relationship types are conceptual-level devices, they do not belong to the hierarchical or network approaches, each of which offers particular mechanisms to represent said aspects:

The network paradigm entails two devices for data representation, i.e., nodes and arcs (and that characteristic of course implies two different kinds of data manipulation operations) which, when contrasted with the relational model that (as per the information principle) requires only one construct (the relation), makes evident the needless complexity that working in a network fashion involves. For instance, given that it resorts to two representation instruments, the network approach imposes an impractical query bias that hinders data manipulation.
For its part, the hierarchical view proposes representing the data by way of (physical!) files made up of records (which in turn consist of fields) organized in a three-like arrangement; i.e., one parent record chained with possibly many child counterparts via pointers, which produces a physical access path with regard to data manipulation. This approach is also unfavourable because it presents a entanglement among conceptual and physical aspects, so the changes in the physical storage arrangements require a reorganization of the data structures, which in turn demands changes in the concerning data manipulation operations.

As shown, the hierarchical and network views impose their constructs on the data to be managed, whereas the relational model proposes administering the data elegantly in its natural structure by means of sets of associated facts (from which n subsequent types of sets, not anticipated at the design phase, can be derived and so on!).

The relational model does not have sub models

And, quite important, neither the hierarchical nor the network views are specific types of relational models, they are simply other paradigms that someone may follow to (a) build DBMSs and to (b) create databases, but please bear in mind that the hierarchical and network approaches are considered obsolete for decades now.

Subquestion no. 3

  What if we have standalone entities that don't relate to one another. Say, Person, Door, and Tree. Is the term "relation(al)" still applicable?

Yes, it is perfectly applicable if one is (1) managing information about those entity types by dint of adapted mathematical relations and (2) performing the applicable relational operations at the logical level in a certain database administered with the support of a given relational DBMS.

It does not matter if, at the conceptual level, said entity types hold no relationship types with other entity types (and it is worth noting that an entity type can have a relationship of one-to-zero-one-or-many cardinality ratio with itself), and thus one is not conveying nor enforcing any relationship between the values of the tuples of the relations under consideration.


Converting HSTORE to an EAV in a view using "each" or "lateral"

Experimental approach

Tested on PostgreSQL 9.6.2, with out of the box configuration, on Mac OS X 10.12, with SSD as storage.

We create the small and huge tables (I call them s and t, for simplicity).

CREATE TABLE s
(
    s_id integer primary key,
    owner text
) ;

CREATE TABLE t
(
   t_id integer primary key,
   s_id integer REFERENCES s(s_id) ON UPDATE CASCADE ON DELETE RESTRICT,
   filterable_column integer,
   user_data hstore
) ;

I've assumed that t_id and s_id where primary keys of the respective tables (t and s) and that s_id references s(s_id). This setup creates proper indices.

We populate the tables with some data (2 M rows on t, to make timings a bit more reasonable; I've tested also with 1 M rows, comparisons continue to hold).

-- 19999 rows into s
INSERT INTO
    s
    (s_id, owner)
SELECT
    s_id, 'owner' || s_id AS owner
FROM
    generate_series(1, 19999) s0(s_id) ;

-- 2 M rows into t
INSERT INTO
    t
    (t_id, s_id, filterable_column, user_data)
SELECT
    t_id,
    (random()*19998+1)::integer AS s_id,
    (random()*19998+1)::integer AS filterable_column,
    ( 'a => ' || (random()*99+1)::integer ||
     ',b => ' || (random()*99+1)::integer)::hstore AS user_data
FROM
    generate_series(1, 2e6) AS s0(t_id) ;

We create now the two views:

CREATE VIEW v1 AS
SELECT
    t.t_id,
    t.filterable_column,
    s.owner,
    (each(t.user_data)).key,
    (each(t.user_data)).value
FROM
    t
    LEFT JOIN s ON s.s_id = t.s_id ;

CREATE VIEW v2 AS
SELECT
    t.t_id,
    t.filterable_column,
    s.owner,
    u.key,
    u.value
FROM
    t
    LEFT JOIN LATERAL each(t.user_data) AS u ON TRUE
    LEFT JOIN  s ON s.s_id = t.s_id ;

We perform a sanity check to make sure v1 and v2 give actually the same results:

-- Sanity check: are both views the same?
-- Check that the views are the same...
SELECT
    (SELECT count(*) FROM
      (
        SELECT * FROM v1
        EXCEPT
        SELECT * FROM v2
      ) AS s0
    ) = 0
    AND
    (SELECT count(*) FROM
      (
        SELECT * FROM v2
        EXCEPT
        SELECT * FROM v1
      ) AS s0
    ) = 0   AS v1_equals_v2 ;
-- Response: yes, they are

At this point, we compare how long does it take to read both views:

EXPLAIN ANALYZE
SELECT * FROM v1 ;

EXPLAIN ANALYZE
SELECT * FROM v2 ;

The execution plans and timings are this for v1, and this for v2. The timing for v1 is 18,857 s, and for v2 13,697. The ratio is, approximately 10:7.  v2 performs better when you need to check all the data.

When you need to check for a range of values (for t_id):

EXPLAIN ANALYZE
SELECT * FROM v1
WHERE t_id BETWEEN 200000 and 210000 ;

You get this plan (v1), and this plan (v2). Timings are: 125 ms and 89 ms. Again, about a 10:7 ratio.

Conclusion

So, from a purely experimental point of view, I would use v2, it is about a 30% faster.

You can check all the tests (with less data) at rextester. (The difference in timings is much less when the number of data points is reduced.)


Best approach to compare specific columns of 2 tables in different databases?

So, your approach is good in my opinion, with little tweaks:

First, when you create the temp table, or when you are generating the hash value, use DATE function (You mentioned is but not sure you're using it in the query):

SELECT ...., crc32(CONCAT(column1, DATE(column10))),

so you don't have to deal with 'fuzziness' [Assuming that you want the preciseness to be at day level]

To get the records that exist in one table, and not the other:

SELECT applicationtable.id, applicationtable.column1
FROM applicationtable
LEFT JOIN backendtable USING (id, column1)
WHERE backendtable.id IS NULL [AND backendtable.column1 IS NULL]
UNION
SELECT backendtable.id, backendtable.column1
FROM backendtable
LEFT JOIN applicationtable USING (id, column1)
WHERE applicationtable.id IS NULL [AND applicationtable.column1 IS NULL]

To get the records that have different values for the same PK:

SELECT applicationtable.id, applicationtable.column1
FROM applicationtable
INNER JOIN backendtable USING (id, column1)
WHERE applicationtable.crc32_value <> backendtable.crc32_value

Be careful though:

If NULL value is allowed in column2 or in column10, CRC32 would give the same "null", even if the second field has a value, and this will not be reported by the query as "NULL <> NULL"
It is very unlikely to get collided CRC32 values, but it is not impossible. MD5 would be an option, but more expensive one.
Having [AND backendtable.column1 IS NULL] in the WHERE clause is not necessary if the field is defined to be NOT NULL


What is the apgcc schema in Amazon RDS Postgresql?

I received the answer from Kevin Jernigan of Amazon:

  A recent change in the Aurora PostgreSQL infrastructure made some internal schemas visible to customers. The “apgcc” schema is used for internal maintenance, and we are happy to drop it for you if it is causing an issue. We will address this in a future release.


MYSQL Including Missing Values Using the Previous Most Recent Record

If I understand the question correctly you want the lag value where there is no direct value.

Permit me a little OCD.  Steer well clear of any table or field names that are reserved words in any of the ANSI or ODBC standards or MySQL reserved words.
I would ditch the tbl_ prefix as well.

I've renamed your date field to Perioddate

Try this

SELECT  v2.type_id,DT.PeriodDate, V2.my_value
FROM (
    SELECT C.PeriodDate,MAX(V1.PeriodDate) AS PrecedingDate
    FROM tbl_calendar AS C
        LEFT JOIN tbl_values AS V1
        ON C.PeriodDate>=V1.PeriodDate
        AND V1.type_id = 100
    GROUP BY C.PeriodDate
) AS DT
    INNER JOIN  tbl_values AS V2
    ON DT.PrecedingDate = V2.PeriodDate
    AND V2.type_id = 100

The way it works is that the derived table builds up a list of dates and the highest date in the values table that is either less than or the same as the entry in the calendar table.  If there is no value the same then the highest date will be the preceding date.

On the subject of indexes make sure that your most selective column comes first i.e. the PeriodDate column, followed by your type_id.

What does the auto_increment column give you apart from a surrogate key?
If PeriodDate and type_id should be unique then make this your primary key.

I don't know if the query optimiser in MySQL does anything special with unique keys but it certainly does in other DB platforms.


Postgres Case-Insensitive search for Jsonb column tag keys

You need to iterate over all key/value pairs in order to compare them using lower()
Something along the lines:
select mt.*
from my_table_name mt
where exists (select *
              from jsonb_each_text(mt.tags) as t(tag, value)
              where lower(t.tag) = 'key1'
                and lower(t.value) = 'value1');

This assumes your column tags is defined as jsonb (which it should be). If it's a json you need to use json_each_text() instead.


How do you calculate how much hardware resources you need for a database?

The short answer:  Can't tell.  Not enough info.

The long answer...

If your data is growing at 10%/month, it will be about a year before it is 64/24 times as big.  So if you grow the RAM and the buffer_pool by 64/24, you are somewhat likely to have the same cache performance of the buffer_pool.  After only one year.

The 99% utilized doesn't really say anything other than your working set is at least 16GB.  That is not surprising since the dataset is much bigger than that.

30% CPU means that you have an average of 1.3 cores running at any time?  If so, then I suspect you have some slow queries.  Let's look at them to see if they can be improved.  With luck, fixing a couple of queries could delay the need for a beefier machine.

8 cores (versus 1.3) says you are likely to have plenty of cores.  But you may outgrow your current 4.

Since you will need to move all the data, I strongly recommend setting innodb_file_per_table=1 on the new machine before starting the move.  (I assume you are using InnoDB.)

Upgrade MySQL when you make the move.

Do not set query_cache_size bigger than 50M; that may be a source of the high CPU.  If you are writing to all the tables "all the time", then it would be better to completely turn off the Query cache.  (This may be another way to stall the purchase.)

What percent of I/O capacity are you using now?

Will the new RAID have a Battery Backed Write Cache?  That makes writes virtually free.

If you want more analysis, please provide

RAM size (currently 24GB)
SHOW VARIABLES;
SHOW GLOBAL STATUS;   -- STATUS, not VARIABLES.

ADDENDA - Review of VARIABLES and STATUS

**Observations

Version: 5.6.13-log
24 GB of RAM
You are running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

innodb_buffer_pool_size should be increased to about 70% of RAM.
increase innodb_log_file_size to about 300M.  Luckily, this is easier as of 5.6.8
Do something about frequently changing databases ("USE"); see Com_change_db, below.
read_buffer_size = 128K
See if it makes sense to clump multiple statements into transactions.
Tackle the slow queries.

Details

( innodb_buffer_pool_size / _ram ) = 12,884,901,888 / 24576M = 50.0% -- % of RAM used for InnoDB buffer_pool
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((21354175 + 179458342) ) / 1821852 = 110 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_log_writes ) = 306,041,138 / 1821852 = 167 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,821,852 / 60 * 48M / 314550301184 = 4.86 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.

( tmp_table_size ) = 179M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Handler_read_rnd_next ) = 766,168,067,814 / 1821852 = 420543 /sec -- High if lots of table scans
( Select_full_join ) = 29,673,902 / 1821852 = 16 /sec -- joins without index
( Select_scan ) = 70,915,674 / 1821852 = 39 /sec -- full table scans
( Select_scan / Com_select ) = 70,915,674 / 771524414 = 9.2% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
( Created_tmp_tables ) = 75/sec
-- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (30050199 + 842 + 264 + 0 + 287315111 + 278) / 1821852 = 174 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Com_change_db / Connections ) = 953,574,484 / 23878 = 39,935 -- Database switches per connection
( Com_change_db ) = 953,574,484 / 1821852 = 523 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.
( Com_admin_commands ) = 523/sec
-- Admin commands -- is some 3rd party cluttering the traffic?

( Threads_created / Connections ) = 1,339 / 23878 = 5.6% -- Rapidity of process creation
-- Increase thread_cache_size (non-Windows)

( read_buffer_size ) = 65536
-- http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_buffer_size
-- 128K might be better, depending on things that are hard to predict at this point.

ADDENDA 2 - back to the question

Generally, it seems that scaling in the direction you mentioned should work well for you.

You seem to be running pretty well now.
I itemized several things that will help you live for a short while with what you have.
A tough one:  getting rid of the unnecessary 3rd-party-isms (change_db, etc)
A tough one:  locating the slow queries and improving them
You already have a recent 5.6, so handling more connections on the 8 cores should be easy.
There is a lot of InnoDB I/O, but nothing to indicate that the cache is "inefficient".  With more ram, the most important thing to change will be innodb_buffer_pool_size (to 70% of available RAM).
If you grow by 10%/month, a year or two from now, you will be happy to have that "huge upgrade", and not have to upgrade again.

One thing worries me...  You are at about peak capacity of ordinary drives now.  (I see 110 IOPs from InnoDB.)  If that grows at 10%, you may run out of I/O capacity first.  Your new config helps only a little.  Alternatives:  RAID-5 (striping and parity, but need at least 3 drives) or SSDs (more costly).


How to limit sql server table to 100,000 rows max

The best solution is to "write a batch task that periodically deletes the unneeded rows."


Simplest way to check if current user has permission to a MySQL table (from PHP / mysqli) for some desired operation?

Perhaps this can lead to some SELECTs to solve your problem?

For the 'ana_index':

mysql> SHOW GRANTS FOR ana_index@localhost;
| GRANT USAGE ON *.* TO 'ana_index'@'localhost' IDENTIFIED BY PASSWORD '*...'
| GRANT ALL PRIVILEGES ON `test`.* TO 'ana_index'@'localhost'

These tables were populated:

mysql> SELECT * FROM mysql.user WHERE user = 'ana_index'\G
*************************** 1. row ***************************
              Host: localhost
              User: ana_index
          Password: *1E62D0DB2482ED4455102B56D823285C02BAAF8F
       Select_priv: N
       Insert_priv: N
       Update_priv: N
       Delete_priv: N
       Create_priv: N
(etc)

mysql> SELECT * FROM mysql.db WHERE user = 'ana_index'\G
*************************** 1. row ***************************
             Host: localhost
               Db: test
             User: ana_index
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: N
(etc)

mysql.user says what permissions, if any, the user has for all databases.

mysql.db says what permissions the user has for particular database(s), test in this example.


How do I implement a database/table as a stack

If you're asking about which database to use, it really depends on personal preference and what you want out of it. As I'm only familiar with MySQL, I'll answer the other part of the question assuming MySQL:

you will want to use INNODB because your table is going to be write-intensive and for large tables, the row-locking of INNODB will be a life-saver over MyISAM.

As far as table design, it seems you only really need one table:

CREATE TABLE `wordpress`.`<table_name>` (
`id` smallint(4) NOT NULL AUTO_INCREMENT UNSIGNED,
`user` varchar(30) NOT NULL,
`filename` varchar(255) NOT NULL,
`date_insert` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE `userFile`(user, filename)
) ENGINE=`InnoDB`;

I went with an arbitrary 'id' column set to AUTO_INCREMENT because the primary key is replicated in every entry of every index. So, doing a primary key of (user, filename) could cause performance issues if your filenames are extremely long.

The size of your 'id' column depends on how big your table is going to grow. Unsigned Smallint will give you 65k rows.

User and filenames are varchar, because they'll vary on length drastically I assume.

The date_insert is just a way to order your results based on when it was inserted (helpful for your POP)


First, you should use:

... WHERE DB_NAME(database_id) IN ('msdb', 'xxx', ...)

or

... WHERE database_id = DB_ID('msdb')

Syntax for ALTER DATABASE is:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

In your case values are as follow:

database_name = MSDB
logical_name = MSDBData (or MSDBLog)
'new_path\os_file_name' = 'C:\Data\MSDBData.mdf'

Then you can follow this procedure for MSDB:

execute ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = '...new path' )
and/or execute ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog, FILENAME = '...new path' )
stop SQL Server service
Move MSDBData and/or MSDBLog to their new location
Start SQL Server service
Check new file location

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id = DB_ID('msdb')

You must replace value such as MSDB, file location, file name with the correct values for your environment.


Flashback Data Archive (formerly known as Oracle Total Recall) let you store history for all the changes to one or more tables automatically.  If you have the Advanced Compression option, Oracle will apply a number of optimizations to the history table that will significantly decrease the amount of space that is required and that will generally make queries against history more efficient.


relation between customer and product with review

Your relationships are correct, however, they would allow for a customer to have records of products they've never bought. Is this something you want? Otherwise, you'd be better off establishing the relation between Review and OrderDetails (the intermediate table for Order-Product which in your ERD doesn't have a name), rather than to Product directly. That being said, your ERD would probably be clearer if it'd be [Order]---*[OrderDetail]*---[Product], where OrderDetail includes quantity, price used, discount, special notes, etc.

Also, Rate should perhaps not be a separate entity, but rather be Review's attributes.


How to compact the current MS Access database from VBA function

This is simply not possible. Compacting and repairing a database requires the database to be closed. As such, you can't compact and repair a database between steps in a sub or procedure, since the database is open when running the procedure.

You might notice the Compact and repair button on the ribbon requires an exclusive lock, closes the database, then compacts and repairs, and then reopens it.

My advice: either run the process from an external database, a VBScript file or PowerShell. Run the first part of your batch, close the file, compact and repair, reopen, run second part

Sample code

Dim fileLocation As String
DBEngine.CompactDatabase fileLocation, fileLocation & "_1"
Kill fileLocation
Name fileLocation & "_1" As fileLocation

You might also notice the Access compact and repair button doing something similar. If you run compact & repair, it moves the data to a database called Database.accdb in your current folder (name might vary based on existing names/database type), then deletes your current database, and then renames the new one.

Well, but nothing is impossible, right?

Well, some things are, but this isn't one of them, if you're willing to do some weird trickery. As I just said, the main problem is that the current database has to be closed. So, the workaround does the following:

Programmatically create a VBScript file
Add code to that file so we can compact & repair our database without having it open
Open and run that file asynchronously
Close our database before the compact & repair happens
Compact and repair the database (creating a copy), deleting the old one, renaming the copy
Reopen our database, continue the batch
Delete the newly created file

Luckily, I had some time to spare, so I came up with the following solution:

Public Sub CompactRepairViaExternalScript()
    Dim vbscrPath As String
    vbscrPath = CurrentProject.Path & "\CRHelper.vbs"
    If Dir(CurrentProject.Path & "\CRHelper.vbs") <> "" Then
        Kill CurrentProject.Path & "\CRHelper.vbs"
    End If
    Dim vbStr As String
    vbStr = "dbName = """ & CurrentProject.FullName & """" & vbCrLf & _
    "resumeFunction = ""ResumeBatch""" & vbCrLf & _
    "Set app = CreateObject(""Access.Application"")" & vbCrLf & _
    "Set dbe = app.DBEngine" & vbCrLf & _
    "Set objFSO = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
    "On Error Resume Next" & vbCrLf & _
    "Do" & vbCrLf & _
    "If Err.Number <> 0 Then Err.Clear" & vbCrLf & _
    "WScript.Sleep 500" & vbCrLf & _
    "dbe.CompactDatabase dbName, dbName & ""_1""" & vbCrLf & _
    "errCount = errCount + 1" & vbCrLf & _
    "Loop While err.Number <> 0 And errCount < 100" & vbCrLf & _
    "If errCount < 100 Then" & vbCrLf & _
    "objFSO.DeleteFile dbName" & vbCrLf & _
    "objFSO.MoveFile dbName & ""_1"", dbName" & vbCrLf & _
    "app.OpenCurrentDatabase dbName" & vbCrLf & _
    "app.UserControl = True" & vbCrLf & _
    "app.Run resumeFunction" & vbCrLf & _
    "End If" & vbCrLf & _
    "objFSO.DeleteFile Wscript.ScriptFullName" & vbCrLf
    Dim fileHandle As Long
    fileHandle = FreeFile
    Open vbscrPath For Output As #fileHandle
    Print #fileHandle, vbStr
    Close #fileHandle
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    wsh.Run """" & vbscrPath & """"
    Set wsh = Nothing
    Application.Quit
End Sub

This does all the steps outlined above, and resumes the batch by calling the ResumeBatch function on the database that called this function (without any parameters). Note that things like click-to-run protection and antivirus/policy not liking vbscript files can ruin this approach.


Why is alert.log not located in path indicated by BACKGROUND_DUMP_DEST in oracle 12c (12.1.0)

BACKGROUD_DUMP_DEST parameter specifies the path where debugging trace files for the background processes like PMON, DBWn etc.

  BACKGROUD_DUMP_DEST

  This parameter is ignored by the diagnosability infrastructure introduced in Oracle Database 11g Release 1 (11.1), which places trace and core files in a location controlled by the DIAGNOSTIC_DEST initialization parameter.

The alert logs, trace files are  found  in Automatic Diagnostic Repository (ADR) Home determined by DIAGNOSTIC_DEST.
<diagnostic_dest>/diag/rdbms/<dbname>/<instname>.

The alert log location is <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace/.

  DIAGNOSTIC_DEST

  Derived from the value of ORACLE_BASE ($ORACLE_BASE). If the $ORACLE_BASE environment variable does not exist, then $ORACLE_HOME is used.

The following files are located under the ADR home directory:
Alert Logs
Trace Files
Core Files
Incident Files

Reference:DIAGNOSTIC _DEST


Oracle Table Design, Storing Small Values

The documentation states the size of datatypes here.

0 (the number) is stored as 1 byte. Other single digit numbers will be stored in 2 bytes (one for the exponent, one for the mantissa). You can test this yourself by creating a test table and using the VSIZE() function on test data (doc link).

A CHAR(n) will be stored in n bytes.

I'd always store numbers in a NUMBER. Unless you're dealing with trillions of rows, the space saving will not be significant enough.


Need some help with this query or some ideas for change it to better model

It's not a good idea to encode access rules in text fields.  Even more generally, it violates normal form to encode anything in any field.

Instead, create a new table:

ServiceAllowedUserTypes(
    service_id int foreign key references service,
    user_type int);

If there's a row for the service's id and the user's type, then the user is allowed to order that service.


Select multiple columns in IF clause conditionally?

SELECT
  id,(CASE WHEN signal_field =1 THEN firstname END) as first,
     (CASE WHEN signal_field =1 THEN lastname  END) lastname as last,
     (CASE WHEN signal_field!=1 THEN concat(firstname,lastname) END) as firstlast
FROM mytable;


Multiple "model" DBs -- or db creation 'profiles'

I agree I would create an example of each type and then back each up.  Once they are backed up you can drop the examples.  Then when you need a new "California" type you just run something like this:

RESTORE DATABASE <NewDBName> FROM DISK = 'x:\ModelDBs\California.bak'
WITH FILE = 1,
MOVE 'CaliforniaDataFile' TO
    'E:\Data Files\<NewDBName>.mdf',
MOVE 'CaliforniaLogFile' TO
    'F:\Log Files\<NewDBName>.ldf';

You could even create a template to generate most of the script for you.

The other option is to create "creation" scripts for each.  Create a blank DB and run your creation script for the appropriate type.  This can be automated to a large degree but I still like the backup/restore method better.


Is it possible to rebuild a dropped index?

I agree that "correct answer" isn't correct.

You can't rebuild indexes that have been dropped, you would need to create them again, and it would make no sense to do the insert last after the indexes have been dropped and created anyway.

I presume the correct answer is in fact something like

Disable the non clustered indexes.
Do the Insert
Rebuild the non clustered indexes.

As this is a heap this could be implemented with

ALTER INDEX ALL ON OrdersHistory DISABLE
INSERT ...
ALTER INDEX ALL ON OrdersHistory REBUILD

Which has zero impact on existing permissions, and low administrative effort compared with other possible alternatives such as the solution you propose of dropping and recreating all indexes.


Multiple PostgreSQL running as primary server on pgpool

Old post, but as I have just been through the pain of configuring pgpool, and have encountered similar problems, I'll answer here.

I have exactly the same setup, but I made the choice to go through this excellent tutorial (in 6 parts ouch). It intends to build a full pgpool/pgsql HA infrastructure, where there is no single point failure with one pgpool server, as two pgpool are installed along two postgresql primary and standby servers in streaming replication.

Anyway it is quite easy to adapt in order to keep pgpool one one separate server like I did.

Regarding our problem here, I think it could be related to an incomplete primary/standby setup. And anyone having this problem shall try to setup streaming replication at postgresql level before trying to attach new node with pgpool (particularly when you have a database that is not empty).

In postgresql.conf I would check REPLICATION section on both servers and make sure that the standby server has hot_standby = on. Make sure also that you have correct recovery.conf file on standby server.


Queue Implementation with Custom Consistent Absolute Ordering

(I'll compete with myself.  Here's an Answer for a "large" number of items.)
Updating 1K rows is not very fast, due to saving rows in case of rollback.  Incrementing/decrementing the sequence numbers (column seq) for a range of rows, then changing one the sequence in the one row that was moved.  That's 3 UPDATEs -- one slow, one fast.
The UI would need to provide some way for the user to specify which item to move from some seq to a new seq.
A column in the table would contain the sequence number.  seq would effectively be UNIQUE, but saying so might slow it down.
Suppose the UI tells the db that "Seq=222 needs to move to after seq=567.  (after "0" for inserting at front.)
BEGIN;   -- need atomicity
-- Move item out of the way (assuming "0" is not otherwise used):
UPDATE t SET seq = 0 WHERE seq = 222;
-- Shift the items that need to move:
UPDATE t SET seq = seq - 1   -- "-" or "+" depending
    WHERE seq BETWEEN 222+1 AND 567;
-- move the item in question:
UPDATE t SET seq = 567 WHERE seq = 0;
COMMIT;

Moving the other direction needs a couple of changes.


Schema design for products with redundant values

Add to the color table 'black,white' as this is predominately a stock control system.

This comma separation can be handled in the UI to display in a friendly way.


You can use a case expression:

select case when number between 0 and 9 then '0' else '' end
    || number as n
from t;

Note that || is the sql-standard operator for string concatenation. Unless properly configured MySQL will treat || as logical OR. This behavior is determined by the setting PIPES_AS_CONCAT. You can also enable this and other sane settings with ANSI. You can see what settings you have with:

select @@sql_mode;

It can be changed using:

set @@sql_mode=@@sql_mode || ',PIPES_AS_CONCAT';

or by modifying the config file

However this is purely presentation so I think you should do the formatting of the result in the application instead.


Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table:

WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ON clause.

Of course I would probably remove the non-sargeable MONTH() function from the JOIN, since this is going to force a complete scan on the remote table, and write it this way instead - also eliminating the join to your local months table:

DECLARE
  @OName      VARCHAR(50) = 'John',
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS
(
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
(
  -- convert those to months:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT
  m.m,
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'
GROUP BY m.m
ORDER BY m.m;

Just ignore the m column in the output (you can't order by it without including it in the output, and it doesn't make sense to order by the name).

Another suggestion: don't use 'single quotes' as alias delimiters. Forms of this are deprecated and it also makes column alias look like string literals. Use [square brackets] instead.