Ideally, you would create the database, load a bit of sample data, measure the size, and extrapolate. That is, by far, the more accurate method of estimating the size of a database in 5 years.

If you do want to compute the database size, you would generally start by figuring out how many rows will fit in a single block. For simplicity, we'll assume that rows are never deleted and that updates never change the size of the row. We'll also assume no compression is being used.  Otherwise, things get a bit (more) complicated.

Calculate the size of the data in the row.  For fixed-size data types (i.e. DATE, CHAR), that's just the size of the type.  For variable-size data types (i.e. NUMBER, VARCHAR2), that's the average size of the data in the column.  There are a couple of bytes of additional overhead, but you can pretty safely ignore that-- they're going to be swamped by the errors in estimating the size of your actual data and in the subsequent estimate of the number of rows per block.

If you expect that each row will have x bytes of data, the number of rows per block will be

<<rows per block>> =
  floor( <<database block size>> *
          (1 - <<pctfree of table>>/100) /
          <<size of row>> ) + 1

assuming that a row is smaller than <<database block size>> * <<pctfree of table>>/100.  If the row is larger, then don't add 1 to the result of the floor.

Once you know the number of rows per block, the estimated size of the table will be

<<size of table>> =
  ceil( <<number of rows in table>> / <<rows per block>> ) *
    <<database block size>>

Walking through an example

My database block size is 8k and we'll assume that I'm using the default PCTFREE of 10 (meaning 10% of the block is reserved for future updates that increase the size of the row).    I'll create a simple two-column table

SQL> create table foo(
  2    foo_id number,
  3    foo_str varchar2(100)
  4  );

Table created.

If foo_id is going to be the primary key with values from 1 to 1 million, each foo_id will consume between 1 and 7 bytes of space.  But I also know from doing the test that, on average, it'll take ~6 bytes (actually 5.89 bytes).  Of course, the larger the foo_id values get, the more space, on average, each foo_id requires.  Oracle needs, on average 1.1 bytes per element to store the numbers 1-10, 1.92 bytes to store 1-100, 2.89 bytes to store 1-1,000, 3.89 bytes to store 1-10,000, 4.89 bytes to store 1-100,000, and 5.89 bytes to store 1-1,000,000.  So, let's estimate for our example that foo_id will require 6 bytes and foo_str will require 50 bytes because the average foo_str is roughly 50 bytes.  So we'll estimate a row size of 56 bytes.

The number of rows per block

<<rows per block>> =
  floor( 8192 *
          (1 - 10/100) /
          56 ) + 1

which works out to 132 rows per block. If we want to estimate the size of a 1 million row table,

<<size of table>> =
  ceil( 1000000 / 132 ) *
    8192

which works out to 59.19 MB.

Now, let's test our estimate

We'll insert 1 million rows where foo_id goes from 1 to 1,000,000 and foo_str is a string with a random length between 1 and 100.

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    select level, dbms_random.string( 'p', dbms_random.value(1,100))
  3      from dual
  4*  connect by level <= 1000000
SQL> /

1000000 rows created.

Our estimate of the average row length was spot on (note that, in reality, you won't be nearly this close-- your estimate of variable column sizes will not be nearly so accurate)

SQL> exec dbms_stats.gather_table_stats( 'SCOTT', 'FOO' );

PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf

  1  select avg_row_len, num_rows
  2    from user_tables
  3*  where table_name = 'FOO'
SQL> /

AVG_ROW_LEN   NUM_ROWS
----------- ----------
         56    1000000

But how big is the actual table?  The most common measure is to look at the size of the segment which is 72 MB.

SQL> select sum(bytes)/1024/1024 mb
  2    from user_segments
  3   where segment_name = 'FOO';

        MB
----------
        72

Our guess was off by ~20% and that was when we were perfect on our estimate of the size of a row.  That's because Oracle allocates space to tables in chunks called extents that we've ignored.  There are different algorithms for this that depend on the setup of the tablespace.  Assuming recent Oracle versions where all tables are in locally managed tablespaces, you would be choosing between uniform extent allocation and automatic extent allocation.  In my example, the tablespace I'm using is using automatic extent allocation.  The exact algorithm for that, in turn, may depend on the version of Oracle you're using.  In my case, though, the first 16 extents are 64 kb, the next 63 extents are 1 MB, and the last extent is 8 MB

SQL> ed
Wrote file afiedt.buf

  1  select bytes, count(*)
  2    from user_extents
  3   where segment_name = 'FOO'
  4   group by bytes
  5*  order by bytes
SQL> /

     BYTES   COUNT(*)
---------- ----------
     65536         16
   1048576         63
   8388608          1

That means that I probably got a bit unlucky and I had just a bit more data than would fit in 79 extents totaling 64 MB so I had to allocate an 80th extent that was 8 MB in size, for a total of 72 MB.  We can use the dbms_space package to get more details about how much space is being used.  When we do that, we see that we're actually only using 66.22 MB of the 72 MB that have been allocated.  So our actual estimation error is really only ~10%

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_space_used NUMBER;
  3   l_space_allocated NUMBER;
  4   l_chained_pct NUMBER;
  5  BEGIN
  6    dbms_space.object_space_usage(
  7      'SCOTT',
  8      'FOO',
  9      'TABLE',
 10      NULL,
 11      l_space_used,
 12      l_space_allocated,
 13      l_chained_pct);
 14    dbms_output.put_line('Space Used: ' || TO_CHAR(round(l_space_used/1024/1024,2)) || ' MB');
 15    dbms_output.put_line('Space Allocated: ' || TO_CHAR(l_space_allocated/1024/1024) || ' MB');
 16    dbms_output.put_line('Chained Percentage: ' || TO_CHAR(l_chained_pct));
 17* END;
SQL> /
Space Used: 66.22 MB
Space Allocated: 72 MB
Chained Percentage: 0

PL/SQL procedure successfully completed.

Is there a shortcut?

If you're using a recent version of Oracle, you can use the dbms_space.create_table_cost procedure to estimate the size of a table.  There are a couple ways of doing this.  The first option is to pass in the size of the row.  Using our 56 byte estimate, that yields a table size estimate of 64 MB with 63.52 MB used which is pretty close

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_used_bytes NUMBER;
  3   l_allocated_bytes NUMBER;
  4  BEGIN
  5    dbms_space.create_table_cost('USERS',
  6                                 56,
  7                                 1000000,
  8                                 10,
  9                                 l_used_bytes,
 10                                 l_allocated_bytes);
 11    dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
 12    dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
 13* END;
SQL> /
Used Bytes: 63.52 MB
Alloc Bytes: 64 MB

PL/SQL procedure successfully completed.

You can also specify the data types of the columns that will be in your table.  Since there is no way to specify the average size, though, this tends not to be nearly as accurate as doing it yourself.  In our case, though, it's pretty good because our VARCHAR2 column happened to be populated with strings that were, on average, half the maximum size of the column.  In our case, though, it correctly estimated the allocated size of 72 MB with 67.94 MB used.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_used_bytes NUMBER;
  3   l_allocated_bytes NUMBER;
  4   l_cols sys.create_table_cost_columns;
  5  BEGIN
  6    l_cols := sys.create_table_cost_columns(
  7            sys.create_table_cost_colinfo('NUMBER',10),
  8            sys.create_table_cost_colinfo('VARCHAR2',100));
  9    dbms_space.create_table_cost('USERS',
 10                                 l_cols,
 11                                 1000000,
 12                                 10,
 13                                 l_used_bytes,
 14                                 l_allocated_bytes);
 15    dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
 16    dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
 17* END;
SQL> /
Used Bytes: 67.94 MB
Alloc Bytes: 72 MB

PL/SQL procedure successfully completed.


MS SQL Creating Tables and Adding Values by a Trigger

If you have a [Product] table and a [ProductTransaction] table, and your goal is to have [Product].[Quantity] always be the sum of the transaction table, then your trigger will need to take into account INSERT, UPDATE, and DELETE operations. You will also need to ensure that nobody ever directly updates the [Product].[Quantity] value, as it will result in inconsistencies.
One way to get around this without using a trigger would be to use a view. For example:
CREATE OR REPLACE VIEW vw_inventory AS
SELECT p.[ID], p.[Name], SUM(txn.[Quantity]) AS [Quantity]
  FROM [Product] p INNER JOIN [ProductTransaction] txn ON p.[ID] = txn.[ProductID]
 GROUP BY p.[ID], p.[Name]

Then you can query off the view:
SELECT * FROM [vw_inventory]
 WHERE [ID] = 4;

Otherwise, if you really want to use a trigger, then you'll need to set an AFTER trigger on the transaction table:
CREATE TRIGGER [trg_product_quantity]
    ON [ProductTransaction]
 AFTER INSERT, UPDATE, DELETE
    AS
 BEGIN
    SET NOCOUNT ON;
    UPDATE [Product] p
       SET [Quantity] = tmp.[Quantity]
      FROM (SELECT z.[ProductID], SUM(z.[Quantity]) as [Quantity]
              FROM [ProductTransaction] txn
             WHERE txn.[ProductID] IN (inserted.[ProductID], deleted.[ProductID])
             GROUP BY z.[ProductID]) tmp
     WHERE tmp.[ProductID] = p.[ID];
   END

Note: Be sure to double-check and modify this SQL code. I may have mixed in a little MySQL/PostgreSQL by mistake.

Hope this gives you something to think about


Backing up databases on SQL Server hosted in Windows Azure VM

The various options are all well documented here, including a decision matrix to help you decide.


How to let customers use their own reporting tools on a SaaS application?

Do not permit ad hoc querying against the operational database, no matter if it is internally generated or external. The potential to lock data or saturate resources is too large. Client B is not going to forgive the SLA breach caused by client A submitting a malformed query then going for lunch.

Separating each customer's data from the others' is important, too. Even if the data isn't "sensitive" it is not professional and can lead to awkward conversations when A realises their outcomes are so much worse than B's. The question doesn't make clear if the source is multi-tenant.

One solution is to replicate / mirror / copy data from the operational DB to a second, separate, isolated DB. Use whatever tool your product supports. This may be an opportunity to do some transformation if required and supported by the tool. The lag between primary and secondary is to be negotiated with the customer in the light of license, set-up and support costs. Grant read-only access on this secondary to the client. Ideally the primary and secondary would be on separate boxes with a firewall between since the latter will be externally accessible. Be kind to yourself on the SLA you agree for the secondary.

If the client can accept a reporting lag on the order of hours you could simply ftp the data to them on a schedule.

Is there a better option than VPNs? I'm not a networking expert, but I'd say that unless you have a specific problem with using a VPN, go with it.


MS Access 2010 adds unwanted wildcard at end of select clause

You apparently have the "Output all fields" option enabled for query design. To turn it off,

choose "Options" from the "File" tab of the ribbon,
on the "Object Designers" tab, remove the checkmark beside "Output all fields" in the "Query design" section.


How does one connect to a schema within a PDB (other than schema SYS)?

Turns out there was a typo error in the tnsnames.ora file - the HOST ip address field was invalid for the system in question.
Example below (values have been anonymized for security reasons)

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.11)(PORT = 1555))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB)
    )
  )

The file was probably copied from another server (as a template) and the HOST ip never updated.

Given the above, the correct way to connect would be:

conn myschema/mypasswd@PDB;


Create a new instance on a SQL server, but I need to use the default DB instance

There is no need to install an extra instance if you want to connect to the default instance using an instance name.

You can create an alias for that.

Open SQL Server configuration manager on the client machine , and under SQL Server native client configuration for both the 32 and 64 bit node add an alias like this:

And then you can connect using your alias name:


How do you get an average of job history times?

Updated to satisfy the new requirement. Note that you may be tempted to use CONVERT(TIME, but resist - this is brittle, in the event any job runs longer than 24 hours.

SELECT
  job_name = name,
  avg_sec  = rd,
  avg_hhmm = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
  SELECT
    j.name,
    rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000'
       + CONVERT(VARCHAR(6),run_duration),6),5,0,':'),3,0,':')))
  FROM msdb.dbo.sysjobhistory AS h
  INNER JOIN msdb.dbo.sysjobs AS j
  ON h.job_id = j.job_id
  WHERE h.step_id = 0
  GROUP BY j.name
) AS t
ORDER BY job_name;


There are three important parts to reading query plans here,

Did it run. If so,
How many times?
Was it correlated?

Sample Data

You didn't provide any sample data, so let's create some.

CREATE TABLE foo AS
SELECT x FROM generate_series(1,100) AS x;

And, now let's run a basic query with subquery, outside of the possible range of execution.

EXPLAIN ANALYZE
SELECT
  x,
  (CASE WHEN x>200 THEN (SELECT sum(x) FROM foo) END)
FROM foo;

The plan will show that the case is accompanied for, but never executed.

 Seq Scan on foo  (cost=2.26..4.51 rows=100 width=4) (actual time=0.017..0.047 rows=100 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (never executed)
           ->  Seq Scan on foo foo_1  (cost=0.00..2.00 rows=100 width=4) (never executed)
 Planning time: 0.101 ms
 Execution time: 0.118 ms
(6 rows)

You can see that with (never executed) on the Aggregate line. However, if we set it to something like CASE WHEN x>20 THEN (SELECT sum(x) FROM foo you'll see a lot more

 Seq Scan on foo  (cost=2.26..4.51 rows=100 width=4) (actual time=0.020..0.095 rows=100 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (actual time=0.043..0.043 rows=1 loops=1)
           ->  Seq Scan on foo foo_1  (cost=0.00..2.00 rows=100 width=4) (actual time=0.006..0.019 rows=100 loops=1)
 Planning time: 0.092 ms
 Execution time: 0.158 ms
(6 rows)

Here we can see that the Aggregate is looped through loops=1 time. PostgreSQL realizes that it isn't a correlated subquery and it's a just a reduces it to a literal (essentially). Now let's make sure it's correlated.

EXPLAIN ANALYZE
SELECT
  x,
  (CASE WHEN x>20 THEN (SELECT sum(f2.x)+f1.x FROM foo AS f2) END)
FROM foo AS f1;

Now you'll see this plan

 Seq Scan on foo f1  (cost=0.00..228.50 rows=100 width=4) (actual time=0.020..3.210 rows=100 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=80)
           ->  Seq Scan on foo f2  (cost=0.00..2.00 rows=100 width=4) (actual time=0.005..0.017 rows=100 loops=80)
 Planning time: 0.104 ms
 Execution time: 3.272 ms

Here the key is that the aggregate has loops=80 which itself requires loops=80 seq scans.

This is all general, but it's all I can give without your sample data, or query plans.


How to create a Unique (Index) constraint across multiple tables?

You could use a sequence for generating the values for ID fields. The documentation explicitly mentions your use case. The example C on said page has a sample implementation about this.

  Use sequences instead of identity columns in the following scenarios:

  The application requires sharing a single series of numbers
  between multiple tables or multiple columns within a table.

Be aware that there are a few catches. For example, sequence values are reusable, are not unique by default and can contain gaps. Pay attention to the Limits part in the docs.


MongoDB cannot be started after moving the server

From the error it looks like you are hitting the mongodb's namespace limit 128 bytes.

One of the namespace; may be db.collection.index is 10078 is too long thus causing this issue.


What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions?

If the index would only be used for a single UPDATE and never again after that, it is probably a good idea not to create it.
The UPDATE will then run for a long time, but if that is a one-time occurrence and you can make sure that there are no other big data modifications going on at the same time (which could lead to deadlocks or table bloat), that should be acceptable.
After all, creating an index also takes a long time.


SQL Server 2008 R2 DB stuck on restoring

I ended dropping the database and restoring the backup:

DROP DATABASE DBNAME;

Then Restored database with SQL Studio.


As they stated 6 positions is all you need for Google maps - likely enough to not lose precision with a float. They may have correcting logic and likely don't have any mission critical apps running off standard API access (maybe??). Good answer concerning precision of coordinates here https://gis.stackexchange.com/questions/8650/how-to-measure-the-accuracy-of-latitude-and-longitude.

Decimal is a basic numeric field... your queries will be restricted to operations appropriate to decimals. This might be all you need if you're just storing the data and have an application layer that does any additional logic with the locations. If you're just appending Lat/Long to another table then you may already have all the indexing you need. For example, a Property table might include geo-coords, but it is primarily accessed by Property Name, Property Codes, etc. by users and systems. This should be simpler to read, maintain, and doesn't require any specific query/spatial knowledge.

If you want the benefits of GIS then you should store data in it's spatial specific data types. This will allow geospatial indexing, and direct, DB level manipulation of the coordinates. You can write queries that provide bounding boxes or calculate distances. This may be more efficiently handled at the DB level, especially for set selection. Instead of dumping a lot of unnecessary decimals to an app and expecting a mobile phone to do routing or distance calculations (as if decimals is all it would need!) you can use SQL spatial implementation to answer questions and find locations.

In SQL for spatial data you could create queries like this:

DECLARE @g geography = 'POINT(-121.626 47.8315)';
SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address
ORDER BY SpatialLocation.STDistance(@g);

To do the same with decimal points you would either need an API to feed that into or create your own logic to determine distance between lat/long pairs.

You can still get your spatial data out in the form of a lat/long numeric pair, but if that is all you're going to be using (e.g. feeding into an API) then just store it as a decimal.

DECLARE @g geometry;
SET @g = geometry::Parse('POINT(3 4 7 2.5)');

SELECT @g.STX; --3
SELECT @g.STY; --4
SELECT @g.Z; --7
SELECT @g.M; --2.5

The above code creates a point with the geometry data type and each SELECT returns the requested part of the point definition.


How to properly align columns in SQLite?

With .mode tab the output is not guaranteed to be aligned because af the nature of the TAB characters. On the other hand .mode column should be working fine.

Since it looks like empty fields for NULL values are creating problems, you can change the way NULL values are displayed with .nullvalue SOMESTRING. I personally like .nullvalue [NULL].

Summing it up:

sqlite> .mode column
sqlite> .headers on
sqlite> .nullvalue [NULL]
sqlite> SELECT * FROM people;
name            surname             age         place
--------------  ------------------  ----------  ------------------
John            Smith               34          assistant manager
Donna           Patterson           [NULL]      [NULL]

Also be sure to check the .width option to set the number of characters per column (negative values to right-align, 0 to reset) in case you need it.


Which field should be the candidate for index in this case?

In order to make this blisteringly fast, what I would do is the following - with the proviso that I know nothing about your server RAM, CPU or disk config.
Bear in mind that a decade's records will be ~35M/year - so ~350M for a decade - most servers should be able to cope with a relatively small table like this - but again, I don't know what else is happening on your server.
1. Do not call a field "DATE" - it's an SQL keyword and this can

make debugging difficult, and,
make porting your app to a different server difficult should you choose to do so.

2. I would make id (perhaps visit_id would be better?) the PRIMARY KEY of the table - you already have practically that situation anyway.
Calling all tables' PRIMARY KEY "id" can again make debugging tricky, since you won't know which table's id field is causing any issues.
3. I would put an INDEX on both ip and visit_date - make any queries very fast - 100K/day ~ 1.2 inserts a second which any reasonable server should be able to easily cope with.
4. Your data is essentially historical.  Rename the table above to current_vist. Then you can create an EVENT (and/or Google "How to create MySQL events") to fire at, say, 03:00 on Sunday morning (or whenever your system is quiet - in conjunction with your backup schedule).
Just a word about backups - always backup all of your data - it obviously does't need to be to hand for every record, but I'd keep it anyway. A gzipped mysqldump will be tiny in any case.
5. Have this event add summary data for the last day (say, Saturday at midnight), week, month, even year) to some summary tables.
6. Then delete from current_visit from Saturday at midnight - that way, you have at most (if you do this weekly) 700K records in your active current_visit table. This will make backups very quick - and inserts also.
7. You can then perform summary queries over days, months, years by summing over the the different tables for your dates of interest. You can also create VIEWs for those periods of interest.
8. Depending on your backup strategy and/or your other server activity, you can use your backups to take the summary data to another machine and perform historical queries at your leisure without stressing your production server.
9. Another use case is the ARCHIVE engine which is designed for non-updatable data - I know it's not your case, but it might be of use for others? I haven't used it, so I can't day - motto is test, test and test again!


Native MariaDb encryption and clustering

Encrypted Logs: You can encrypt the InnoDB redo log and binary log. See the limitations on which types of logs are not encrypted.
Encryption in transit for all data protocols: You can enable secure communications, see e.g. Securing Communications in Galera Cluster
Native DB encryption: Yes, assuming you're using the InnoDB storage engine, you can encrypt all tablespaces, individual tables, or everything excluding individual tables.
High availability for 2 sites with ~30 ms latency between each: You can have a Galera cluster on each site, and have asynchronous master-slave or master-master replication between them. The latency depends on the link between the sites.
1-2 local copies at each site for Backups/reporting etc: Galera cluster has a full copy of the databases in each node.
Sub 30 minute site failover time: Failover can be automated with a database proxy such as MariaDB MaxScale or ProxySQL, or a more general proxy like haproxy.
Minimal application re-engineering: MariDB Galera cluster works much like a single MariaDB server instance from the application's point of view, but note that every table must have a primary key (a single or compound key), and that only the InnoDB storage engine is supported. Also note that unless you're only ever writing to a single node at a time (e.g. read-write splitting which can be configured e.g. with MaxScale), your application needs to take into account that transactions can fail both on individual statements within the transaction and after the commit - see Galera’s big gotcha for MySQL users for details.
Assume AWS or Vault KMS: I don't know.

You may want to look into a solution such as SeveralNines' ClusterControl which I believe can automate a lot of this.


How can I model a database structure to retain student information?

You need to normalize the design. A column can, under sane circumstances, only hold a datum (singular).

So something like this

CREATE TABLE student (
  studentid int AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE class (
  classid   int AUTO_INCREMENT PRIMARY KEY,
  classname text
);
CREATE TABLE student_class_grade (
  classid   int REFERENCES class,
  studentid int REFERENCES student,
  grade     float
);

This is super simple, so each student has a studentid, each class has a classid, and you can put as many entries as needed for stupid_class_grade. This doesn't account for the extra complexity needed for weighing tests and homework differently in the same class.


ON DELETE CASCADE on pg_class in PostgreSQL does not work

Constraints are only checked when you execute SQL-level DML commands such as DELETE or UPDATE.  They are not checked when you effect a system catalog data change using a DDL command such as DROP TABLE.  Those take a different code path internally, even though they end up modifying the same data.

As a consequence, you currently cannot do what you are trying to do.  In PostgreSQL 9.3, there will be event triggers, which will address the space your problem is in.


Will a DBCC CHECKDB with repair have to rescan?

To add to the answer above, if you're on Enterprise Edition, where a normal DBCC CHECKDB will run in parallel, when you run with a repair option it must run single-thread, so it will also take more time.

Any DBCC CHECK* command has to scan all the data as the results from any previous DBCC CHECK* command are

not persisted anywhere
in no way can be trusted to still be accurate.

Also be wary of REPAIR_ALLOW_DATA_LOSS on SQL Server 2000 - there are many repairs that do not work correctly, that I fixed for SQL Server 2005 as part of my general rewrite of the code base.

Thanks


MariaDB Galera Arbitrator failing to sync with SSL

There is a flag socket.ssl = yes that the options of garbd need which for some reason is not documented that needs to be enabled for the arb to try to connect via SSL.


I don't believe there is an extended event for a trace starting.  One thing you could do is to write a SQL Agent job that queries sys.traces and if anything but the default trace exists, take whatever action you deem necessary.


How to Permanently solve MySQL error 2002 (HY000)

You have a memory problem, something very typical in a memory-bound system, and that can be confirmed on the line:

150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool

As InnoDB cannot allocate memory for its buffer pool, it fails, and MySQL cannot start if the InnoDB engine fails (MySQL 5.5).

Nothing on your my.cnf seems out of the ordinary, but given that you only have 2GBs of ram, and if I understand it correctly, that is shared with the OS and a PHP/Wordpress installation, you have to be very conservative. MySQL is already taking 1.2 GB for the buffer pool, you should monitor the memory usage (top, ps) and making sure MySQL is the process to blame (compared to, for example, Apache). If it is, reduce your innodb_buffer_pool_size and/or some of the other buffers, such as the key cache or the query cache. I would set the buffer pool to ~500MB for this non-dedicated server.

You seem to be running 5.5, for other folks running 5.6, one of the main causes I have seen in MySQL support regarding out of memory killings in small-memory systems is the extra static memory needed for the performance_schema. In those cases, I recommend disabling it or reducing max_connections to avoid too much overhead.


"Main" folder is not found at "/var/lib/postgresql/9.3/"

pg_conftool

Debian and Ubuntu provide their own utilities. To get this information, use pg_conftool. Here is what it looks like,

pg_conftool show -s data_directory;
/var/lib/postgresql/9.5/main

For reference,

Usage:
  /usr/bin/pg_conftool [options] [<version> <cluster name>] [<configfile>] <command>

Options:
  -b --boolean  Format output as boolean
  -s --short    Print only value
  -v --verbose  Verbose output
  --help        This help

Commands:
  show <parameter>|all
  set <parameter> <value>
  remove <parameter>
  edit

pg_lsclusters

If you're running multiple versions of PostgreSQL on the same machine, you may find pg_lsclusters useful. It will show you all of the versions running.

pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

You can parse that pretty easy, here we use awk to get out the Ver and Data directory,

pg_lsclusters | awk '{print $1,$6}'
Ver Data
9.5 /var/lib/postgresql/9.5/main

If you have JSON.pm installed you can use pg_lsclusters -j to output JSON,

Error: Please install JSON.pm for JSON output (Debian: libjson-perl)


Changing Auto-Growth Setting in AOAG for log file

Set it on Primary replica only and it gets applied to all secondaries.

You can test it out on a small db .. add it to availability group and change the autogrowth setting.


Create multi-key JSON object with integers or floats as values

Update

According to your sample data and desired result you can get it using json_object_agg Postgres function.

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);

select json_object_agg(str, num)
from test;

| json_object_agg        |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |

dbfiddle here

You can use row_to_json Postgres function.

create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);

select row_to_json(f)
from   (select f1,f2,f3,f4 from foo) f;

| row_to_json                                |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |

dbfiddle here


Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Looking at it as a grammar problem, ANY is defined as
(in Row and Array Comparisons):

  expression operator ANY (array expression)

But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators:

  When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM
  constructs

Since PostgreSQL has user-defined operators, we may define an operator/function combo for this purpose:

create function is_distinct_from(text, text) returns bool as
'select $1 is distinct from $2;' language sql;

create operator <!> (
 procedure=is_distinct_from(text,text),
 leftarg=text, rightarg=text
);

Then it can precede ANY:

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <!> any(array[null, 'A']);

 count
-------
     3
(1 row)


Joining Two Rows (single table) to Single Row, Multiple columns

You Simply have to do a Two JOINs on the same Table

SELECT n.name, i1.info AS info_1, i2.info AS info_2
FROM names n
LEFT JOIN info i1 ON i1.ID = n.ID
LEFT JOIN info i2 ON i2.ID = n.ID_2

Just like here http://sqlfiddle.com/#!9/9f703e/1/0

For the Summed one, You have to use COLEACES to calculate the NULL values like the following

SELECT n.name, (COALESCE(i1.info, 0) + COALESCE(i2.info, 0)) AS Info_Sum
FROM names n
LEFT JOIN info i1 ON i1.ID = n.ID
LEFT JOIN info i2 ON i2.ID = n.ID_2

Just like here http://sqlfiddle.com/#!9/9f703e/3/0

In your next question I hope if you create the Table in SQLFiddle or dbfiddle and share it Or put the CREATE code so we can built the Table quickly to provide faster answers.


What strategy does MySQL optimizer use when choosing between two indexes?

Index selection is based on the estimated number of rows that will be accessed when using the different indexes.  The order the indexes is declared in should only make a difference if the estimated cost is the same for both indexes.  In your case, the three columns org, country, and author_id can be used for index lookup. Any index that start with these three columns may be used and will have equal cost.

The most likely reason for your observations is that the InnoDB index statistics have not been updated after the bulk insert.  If I do ANALYZE TABLE on the two tables, the optimizer selects i0 index instead of author_id.

If you want to understand more about why a particular query plan is chosen, I recommend to look at the optimizer trace for the query.


MySQL query not using an index when table contains many records

The table size is not the villain.  It's the estimated number of rows.

The query optimizer, in this case (MyISAM, key starting with rtime, etc), will do something like this:

estimate the percentage of the table to scan, based on "WHERE rtime BETWEEN..."
If that is "small" (say, less than 20%, but that is not a hard number), use the INDEX; else do a table scan.

Step 1 depends on the "statistics" that are kept with the MyISAM table.  The stats are usually pretty accurate, but they can become less accurate.  ANALYZE TABLE is the fix for that.  (I don't think I have ever seen a need for ANALYZE being run more than monthly; usually it is not needed at all.)

The reason for the to-INDEX-or-not-to-INDEX question goes something like this...  When using the INDEX, the execution has to bounce between index 'rows' and data rows.  The data rows are potentially randomly scattered, leading to (potentially) lots of I/O.  Hence, doing a 'table scan' is preferred after some point.


SQL Server recommended MAXDOP settings for NUMA

Typically I recommend to my clients leaving the MAXDOP alone.  SQL Server is NUMA aware so it knows which NUMA node the processors are in and which NUMA node the memory is in and it'll do it's best to assign the work to a scheduler on the correct NUMA node for the data that you are looking for.

Looking into this sort of stuff also requires knowing how many NUMA nodes you have.  Is each physical processor in its own NUMA node or are they node in the same NUMA node?


SQL 2016: OPENDATASOURCE Msg 7357, Level 16, State 2, Line 1

Found the issue...

The table is no longer a table - it's a SYNONYM over the same table schema that has moved to a named schema from dbo, and has a new name: SystemVersion.  OLE DB provider choked! (SQLNCLI or SQLOLEDB)


CrystalDiskMark Test Discovers Lopsided Throughput; What Setting Should be Changed?

This is an old question bumped to the front page, so the MattyZDBA has probably resolved the issue by now, but for future readers:

When posting a question about storage performance it is important to give as much detail about the storage configuration as you can: drive types (traditional, SSD), connectivity to the drives (local nVME drives, Gbit LAN, fibre channel, ...), array size, cache details, ...
While generic artificial benchmarks like CrystalDiskMark have their place, you will get much more useful results from tools designed more specifically for testing database loads on storage arrays. Better yet, if you have a specific application in mind, tests specific to that would give even more realistic results for you (though if it is your own application you might have to construct those tests yourself).
Test : 4000 MB assuming that means the benchmark tool ran each of its steps with 4GB of data, that is not enough to properly test a good modern storage array and could explain the faster-write-than-read behaviour. If the array has a large battery-backed cache then you may find that hardly any of that 4Gb has even gone to the drives yet and all that this has tested is the connectivity between the array and the machine you are testing on. The read operations were presumably reads from parts of the array not just written to or otherwise recently accessed, so they were pulling data from the drives instead of just interacting with faster memory.


How to generate a unique barcode without using identity or sequence in SQL Server 2012

Why do you feel uncomfortable storing the id yourself?
This is a valid approach for this problem.

You could also use -T272 as a startup parameter to prevent the gaps during id generation.
I read that this could have impact on the performance of the id generation, but I never had no problems in my tests.
See this link for further information: http://www.dfarber.com/computer-consulting-blog/2014/2/13/how-to-solve-identity-problem-in-sql-2012.aspx

Please consider that there are many different types of barcodes. If you use the common GS1-128 for example, you can create alpha-numeric codes to increase the possible combinations and prevent an overflow.


This typically means that the database specified in the connection string, in the SSMS dialog's options tab, or the user's default database is offline or otherwise not accessible (it could be restoring, single_user mode, offline, autoclose, etc).

What database are you trying to connect to? Check its status in sys.databases:

SELECT state_desc, user_access_desc, is_auto_close_on, is_in_standby
  FROM sys.databases
  WHERE name = N'database_name';

If you are not specifying a database, then check the default database for the login:

SELECT default_database_name
  FROM sys.server_principals
  WHERE name = N'login_name';


How to upload multiple csv files to sql server without constrains

Using SSMS, you can run the "Import Data" command (right click DBv2, go to "Tasks", and select "Import Data...").

This will allow you to import one of your .CSV files into a new table. The column names, datatypes, and sizes will be set by default from what's in the .CSV file; you can view and edit the actual CREATE TABLE command before you run the import. This would let you set up IDENTITY columns and primary keys immediately. If you're using an identity column, do make sure you tell it to keep the existing values.

Once all tables are loaded, you can go back through and set up the foreign key relationships.

Alternately, if you're careful about the order in which you load the tables, you could even set the foreign key constraints up in the CREATE TABLE statement, as long as the referenced tables have already been loaded.

Note that this will be a somewhat tedious process, if you have a large number of tables to load. You would need to set each one up individually, manually.

Most of the ways I can think of to automate this would require that the data already be in a database, instead of in a number of .CSV files. Of course, you've said you want to use the GUI tools anyway.


Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

These are two different questions and you shouldn't ask compound questions on StackExchange.

What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?

Create your own ID. PostgreSQL doesn't create you IDs for userspace unless you explicitly request it: which you can do with GENERATED BY DEFAULT AS IDENTITY (or serial in pre-PostgreSQL 10)
How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

You don't. PostgreSQL isn't intentionally slow if there was a faster and safer way to do something by default, it would be done. Without knowing what compromises you're willing to make that the server isn't, there is no way to answer this question.

If you're not willing to make additionally compromises, it's not likely you could safe more time without a total rewrite, or merely preplanning the UPDATE.

An extension doesn't magically make everything faster.


Unexpected Always On Availability groups failover happening--- databases changing to resolving

it is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.

I've seen this pattern (PRIMARY -> RESOLVING for a brief period -> PRIMARY, and the same for the SECONDARY) before.  In my case, we were using a file share witness as a third node for quorum.  When this "not failover" happened, it was because the server where the file share witness lived had been taken down for maintenance.

Windows Server Failover Clustering (WSFC) uses a process called the Resource Hosting Subsystem (RHS) to monitor the health of resources in the cluster.  This includes both AG nodes, and in my case the file share witness resource.

While the AG (and cluster) can stay up and running with just the two AG nodes, after the file share witness has been down for 5 minutes, a process called RHS Recovery is started.  You can read more about that here: Understanding how Failover Clustering Recovers from Unresponsive Resources

If all your resources are hosted in the same RHS process (which is the default behavior), this will cause that "blip" in AG availability while the RHS.exe process is restarted.

Normally after one failure, the problematic resource should be automatically marked as "SeparateMonitor" started up in it's own RHS.exe process (so that it doesn't continue to cause problems for your other cluster resources).  So I'm surprised this happened more than once, but maybe someone changed the setting back, or the behavior is different for different resources or Windows versions.

You can check if that setting is on, or set it manually, in the Cluster Manager.  Here's a screenshot:

Anyway, this problem, and especially your comment, sounded very familiar to me so I thought I'd share the experience in case it helps you or others.

I blogged in detail about the troubleshooting process I used for this situation here: Troubleshooting an AG Failure

You should see evidence that RHS Recovery is the root cause of your problem by pulling the cluster log (run the Get-ClusterLog PowerShell command) and checking for messages around the time of your AG blip that mention "DeadlockMonitor", which is what checks "down" resources and initiates the recovery process.


Does a SQL Server update nvarchar statement overwrite the same address on disk if the new value is the same size?

There are so many aspects to this, but the short answer is that you have to expect that old values can be on disk (as this is from what I understand what the question boils down to).
So, this falls under the same category as below question, with the same answer:
Q: Can we protect from a Windows admin to be able to do anything inside a SQL Server and see all data in a SQL Server?
A: No. But you can try to make sure that what they read appear as garbage to them. I.e., a suitable encryption implementation.
If you are truly interested in this, I suggest you first read up on the storage architecture of SQL Server. Without that, you won't be able to make heads of tails of the answers. I.e., make sure you understand database files, pages, transaction logging, heaps, B-trees etc.
The update can be done in place, on the same page. That goes for an index as well. If you modify the key of an index column then the row has to move (DELETE/INSERT internally). If the row doesn't fit, then you get a forwarded record or a page split. Another factor is that after a delete, (and an update can be delete/insert, remember), the old row will initially still be there as a ghost row. But even after that, the page isn't "compacted on delete", i.e., the storage reclaim is done when the "free" space is needed at some later time.
And then you have transaction logging.
And backups.
If you have versioning enabled, then old row versions are in tempdb and cleared up when not needed, after some time.
And the added complexity how other, perhaps less basic, technologies affects the picture. I'm thinking of things such as:

Columnstore indexes
Memory optimized tables
Change Data Capture
Replication
HA solutions such as Availability Groups
And whatever more I could come up with given some more time to think about this

I think you get the picture by now. No, you can't claim that the old data is gone. Encryption might be an important component to this, depending on your particular situation.


Does SQL Server cache data results for DAO?

While I would usually leave an answer this short as a comment, this seems to be worthy of breaking with good practice.

No. Nonsense. Absolutely not.

The answer that @gbn gave to the question you reference is valid, regardless of the method of query. Stored procedure or adhoc, the same applies... Query results are not cached.

  However, the source table and index data and metadata will be cached
  after the 1st use (subject to continued use, load and memory pressure
  though)

  That is, the results of a query will be evaluated every execution but
  the tables(s) (and any indexes etc) used by the query will most likely
  be in memory already.

The "some sort of caching for DAO" is client/API behaviour, irrelevant and unbeknownst to SQL Server.


Unable to import mysql.connector in a Python venv session

You might want to check out the following article:

Can not get mysql-connector-python to install in virtualenv (Stackoverflow)

The accepted answer states the following:

  Several things. There is an inconsistency in package naming so you may want to do:

pip search mysql-connector

  to find out what it is called on your platform. I got two results mysql-connector-python and mysql-connector-repackaged.

  so try this first:

pip install mysql-connector-python

  this may additionally give an error like this:

Some externally hosted files were ignored (use
  --allow-external mysql-connector-python to allow).

  so finally this should do the job:

pip install mysql-connector-python --allow-external mysql-connector-python


Cannot login in as root user after MySQL installation (RHEL)

In the security section of the documentation it states:

  If you do not know the initial random password, look in the server
  error log.

Try running the following command. It works for me on my 5.7 installation (which has the same instructions as the 8.0 version of the documentation):

sudo grep 'temporary password' /var/log/mysqld.log

It should tell you the initial password to use for root.


Effiiency of multiple OR predicates versus a single range

For 10 values where you have no index, this is such a micro-optimization that you shouldn't worry about it.  The database has to read all the rows, and those reads are going to be less efficient that doing a few comparison operations.

The first can more efficiently be written as:

where KeyColumn in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

This should have the same efficiency as the or version for most database engines.

The better question is which way of describing these values better matches the business problem?  You want the code to be easily understood and maintainable.  Use a list if that makes sense.  Use a range if that makes sense.

If you had hundreds of values, then definitely go with the second approach.  It is cleaner and easier to understand and write and should be faster.

How you write the query does affect optimization, and that in turn depends on the database engine.  Some databases are smarter about optimization than others.  Most of the effect would be on the use of indexes and the effect that other filters in the where clause have on index usage.


Move values from one table to another one via stored procedure in sql server 2008

You can easily implement your requirement using a trigger on Login table that will log info in the audit_login table.

the key is using table trigger to log info in the audit table for auditing.

I will see if I can provide a working code. But you got the idea now.

Update: below is a prototype code that will help you.

Note: you dont need any Stored procedure, A trigger will do the job.

You can put try/catch to handle errors, which I have not done as this is only to help you understand the concept.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logins]') AND type in (N'U'))
DROP TABLE [dbo].[Logins]
GO
create table Logins (userid varchar(15) not null
                        ,username varchar(max)
                        ,[password] varchar(50)
                        ,[status] varchar(1)) -- S = Success, F= Fail
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit_login]') AND type in (N'U'))
DROP TABLE [dbo].[audit_login]
GO
SELECT [userid],[username],[status]
    ,AuditDataState=CONVERT(VARCHAR(10),'')
    ,AuditDMLAction=CONVERT(VARCHAR(10),'')
    ,AuditUser =CONVERT(SYSNAME,'')
    ,AuditDateTime=CONVERT(DATETIME,'01-JAN-1900')
    into dbo.audit_login
FROM dbo.Logins
WHERE 1=2

go
CREATE TRIGGER Logins_Insert
ON dbo.Logins
FOR INSERT
AS

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'New','Insert',SUSER_SNAME(),getdate()  FROM INSERTED

GO
CREATE TRIGGER Logins_Delete
ON dbo.Logins
FOR DELETE
AS

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'Old','Delete',SUSER_SNAME(),getdate()  FROM DELETED
GO

CREATE TRIGGER Logins_Update
ON dbo.Logins
FOR UPDATE
AS

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'New','Update',SUSER_SNAME(),getdate()  FROM INSERTED

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'Old','Update',SUSER_SNAME(),getdate()  FROM DELETED

GO

Now insert some data :

-- Insert New Data
insert into Logins
values ('kin001', 'kin','$tr0ng01','s')

select * from audit_login

-- Update the current Data
update Logins
set userid = 'kin-up-01'
where userid = 'kin001'

select * from audit_login

-- delete the data
delete from Logins
where userid = 'kin-up-01'

Below will be the end result:


MySQL not using primary key on inner join

Notes

A FOREIGN KEY adds a constraint; it does not add an index if there is already one there.
Datatypes should match, but what you had was "close enough" for JOINing.
What was wrong -- The columns of an INDEX are looked at left-to-right.  Since there was no mention of processID (the first column), the index was not useful.
When JOINing, MySQL usually starts with one table, then repeatedly reaches into the other.  It picked lk.  Why?...
When practical, MySQL will start with the table that it can do some filtering on.  But neither table had any useful indexes.  So?...
As a fallback, MySQL starts with the 'smaller' table, lk_transaction_types and did a full table scan ("ALL").

To make the query more efficient, usually the filtering (WHERE) is tackled first:

WHERE l.escoID = 2
  AND lk.isActive = 1
  AND lk.isInbound = 1;

To help the Optimizer picks l as the first table, have

INDEX(escoID, ...)

Or, to help it pick lk, have

INDEX(isActive, isInbound, ...)  -- in either order

The ... is optionally more columns.

Without understanding the distribution of the data values, let me proceed with deriving the optimal indexes for each case.

SELECT  COUNT(0)
    FROM  edi.ediLoad l
    INNER JOIN  edi.lk_transaction_types lk
        ON lk.transactionTypeID =
            l.transactionTypeID
    WHERE   l.escoID = 2
      AND  lk.isActive = 1
      AND  lk.isInbound = 1;

Case:  Start with l:

l:   INDEX(escoID,             -- first, so as to satisfy `WHERE`
           transactionTypeID)  -- added to make the index "covering"

A "Covering index" contains all the columns (of one table) needed to satisfy the entire query.  This lets the processing work only in the index BTree, and ignore the Data BTree.

lk:  INDEX(isActive, isInbound, transactionTypeID)  -- in any order

Again, this is 'covering'.

Case:  Start with lk:

lk:  INDEX(isActive, isInbound,  -- first, in either order
           transactionTypeID)
l:   INDEX(escoID, transactionTypeID) -- in either order

In neither case was either PRIMARY KEY useful.  So, I claim your original question was ill-advised.  Instead, you should have asked "how can I make this query more efficient".  That is the question I have been answering.

One more thing.  Let's combine the two cases to minimize the number of indexes.

lk:  INDEX(isActive, isInbound,  -- first, in either order
           transactionTypeID)
l:   INDEX(escoID, transactionTypeID)  -- in this order

With InnoDB, the PK is "clustered" with the data. PRIMARY KEY(transactionTypeID) is sufficient for the second case, but not the first.

More on creating indexes.


Contiunous rise of mysqld memory consumption

We tracked it down to glibc. With glibc-2.12-1.149.el6_6.4.x86_64, memory is not fully freed when closing tables.

It seems that under high concurrent connections, accessing partitioned tables increases Open_tables which increases memory usage, however memory is not fully freed when the table is closed using glibc.

Newer glibcs faced the same problem as well.
Switching to jemalloc-3.6.0-1.el6.x86_64 has resolved our problem.

Test environment
CentOS release 6.6 (Final)
kernel 2.6.32-504.el6.x86_64
mysql-5.6.16

physical ram 2G
bufferpool 256M
other default

memory usage with gblic-2.12-1.166.el6_7.7.x86_64
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 1.0G

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.2G

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.4G

memory usage with jemalloc-3.6.0-1.el6.x86_6
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 582M

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 583M

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.2G
"flush table" -> 584M


Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?

Looks like MB vs MiB, ie decimal SI vs binary megabyte:

regress=> SELECT round(25.17*1000*1000 / (1024*1024),1);
 round
-------
  24.0
(1 row)

You've done your conversion into megabytes using decimal SI units (MB) not binary megabytes (MiB). Divide by 1024 not by 1000, or use pg_size_pretty:

regress=> select pg_size_pretty( 3072 * 8192::bigint );
 pg_size_pretty
----------------
 24 MB
(1 row)

Compare:

regress=> select
          pg_size_pretty( 3072 * 8192::bigint ),
          (3072::real * 8192)/(1024*1024) AS binaryMiB,
          (3072::real * 8192)/(1000*1000) AS decimalMB_SI;
 pg_size_pretty | binarymib | decimalmb_si
----------------+-----------+--------------
 24 MB          |        24 |    25.165824
(1 row)

The GUC display code uses kiB / MiB / GiB too, but (IMO) mislabels it kB / MB / GB:

regress=> SELECT 24*1024*1024;
 ?column?
----------
 25165824
(1 row)

regress=> SET work_mem = 25165824;
SET
regress=> SHOW work_mem;
 work_mem
----------
 24GB
(1 row)

(It's GB because work_mem is measured in kb not bytes; ignore the factor-of-1024 difference).


SELECT friend_id, COUNT(*)
    FROM num_friends
    GROUP BY friend_id;

This will assist in making it fast:

INDEX(friend_id)

With that index, you will find out that the original query is so fast that you don't need to cache the results.


Decision tables in the database: a valid solution?

The additional module will be used by no more than 20 people on an intranet.

"People" is a fuzzy term when you're talking about legal applications. Supporting 20 lawyers is a lot different than supporting 1 lawyer, 4 paralegals, and 15 staff.

  For legal cases new business rules are more like "everything is an
  exception"

And that's one reason why. Within case management, certain kinds of things are simply deterministic. Timing of responses, deadlines for filing, things like that. But the rules that govern were written by lawyers for lawyers. Companies that build case management software have lawyers on staff to read the rules for procedure (which change over time) and to verify that their software complies with the rules.

Some of them have a lot of lawyers.

Other kinds of things are governed by whimsy. For example, some case management packages let users "template" their tasks. Entering something like "Deposition" on the calendar might also calendar a series of earlier tasks that have to be done to prepare for the deposition.  Under the pressure of multiple, incompatible deadlines, parts of a task chain get dropped or given short shrift.

Now, to (finally) get to your question . . .

Decision tables in the database are a viable solution. In fact, decision tables constitute a database whether they're stored in Oracle or in application code. There are obvious trade offs to storing decision tables in application code.

But decision tables in the database are probably not a viable solution for you. The problem you have, if I understand you correctly, is that you think you might need to simplify the architecture to decision tables in order to eliminate (or at least to greatly reduce) the use of Microsoft Workflow. With just two of you and a minimal budget, success will be difficult at best.

I can suggest two things.

Can you economically produce a prototype of a replacement system
built on decision tables?
Can you use better hardware on the existing system?


Create pgAdmin database on local machine

PgAdmin is just a tool to work on existing database. @FabrizioMazzoni is right - you have to install PostgreSQL server first, and the you can use PgAdmin to work on it. New Server Registration is a form to connect to an existing database server, not to create one.


"Error in query (1030): Got error 168 from storage engine" when creating table

apparmor (and selinux) provide additional access controls beyond permissions that can cause these permission denied (13) errors. This occurs generally on systems where data or log files differ from the default.

With apparmor you would add to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:

/media/sdc/mysql/ r,
/media/sdc/mysql/** rwk,

And then reload the apparmor service:

service apparmor reload

If it was selinux:

semanage fcontext -a -t mysqld_db_t "/media/sdc/mysql(/.*)?"
restorecon -Rv /media/sdc/mysql


Partitioning not working need primary key?

The problem is that the function (MONTH()) and the LESS THAN values are incompatible.

Apart from the fact that I don't see the value of using TO_DAYS() (what is the advantage there compared to a simple date?), you should use (TO_DAYS(receiptDate)) instead of (MONTH(receiptDate)).

Or, more simply (if you want a day-by-day partitioning), I'd write just

RANGE COLUMNS (receiptDate)
...
LESS THAN ('2012-10-20')

See a very simple working example at SQLFiddle.


I was in the same position when I started my career as a SQL Server DBA, but studied mostly ORACLE (10g) at my university .. and that OCA stuff as well...

I agree to @KennethFisher that both RDBMS are different and comparing them is like - comparing Apples and Oranges.

To answer your question regarding - storing tables as balanced trees ...

Both Oracle & SQL Server support B-Tree Indexes which keeps data sorted and allows searches, sequential access, insertions and deletions in logarithmic time.
B-Tree is well optimized for systems that read and write large blocks of data. Note that SQL Server uses this structure for Non-clustered Indexes and Oracle
uses it as a default to store the storage location of the table internally.
Oracle has a concept of ROWID (row id) and UROWID (universal Row ID) where in the key values and a Unique reference to the storage location of the record is contained and this represents the physical location on the disk that the record is stored.

e.g. Reference Overview of ROWID and UROWID Datatypes

SELECT ROWID, last_name FROM employees WHERE department_id = 20;

ROWID              LAST_NAME
------------------ ----------
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG

  An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

  OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.

  FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).

  BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

  RRR: The row in the block.

Hence the concepts are completely different in both RDBMS - ORACLE and SQL Server. Also, PK's created in Oracle are nothing but Balanced non-clustered indexes with ROWID for fast access and therefore no concept of either clustered or non-clustered indexes.

Now, this gets more interesting and different when it comes to B+Tree indexes:

B+Tree structures are similar to B-Tree structures, but the table records (actual data) is stored in the leaf nodes of the Primary key Index allowing fast access for exact match or range scan searches on the PK of the table.
Oracle uses what is called IOT (Index Organized Tables) and SQL Server uses what is called Clustered Indexes.

Lets look at Clustered Indexes and Index Organized tables (IOT) ...:

From Oracle Doc,

  An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

From SQL Server Doc,

  In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.

Statistics for IOT's includes the Physical scatter of rows whereas SQL Server does not include Physical location of the rows in statistics and hence Clustered Index in SQL Server is better than a HEAP - data is sorted by the clustered key and good estimates are obtained for the data to be searched.

At last some good references :

Statistics in Oracle and SQL Server - by Jonathan Lewis and Grant Fritchey
Oracle Heap Tables or SQL Server Clustered Indexes? - by Jonathan Lewis and Grant Fritchey
Index Organized Tables – the Basics - Excellent series !
Fixing heap fragmentation
Internals and Deletes

I will add more points when I come across that are worth mentioning...


SQL Server Pivot Multiple Column and Value

If you have to do it on the SQL Server side, you can use GROUP BY with a CASE statement:

SELECT PERIOD, EMPL_NMBR, EMPL_NAME
    , IN_1 = MAX(CASE WHEN DAYX = 1 THEN [IN] END)
    , OUT_1 = MAX(CASE WHEN DAYX = 1 THEN [OUT] END)
    , IN_2 = MAX(CASE WHEN DAYX = 2 THEN [IN] END)
    , OUT_2 = MAX(CASE WHEN DAYX = 2 THEN [OUT] END)
    , IN_3 = MAX(CASE WHEN DAYX = 3 THEN [IN] END)
    , OUT_3 = MAX(CASE WHEN DAYX = 3 THEN [OUT] END)
    , IN_4 = MAX(CASE WHEN DAYX = 4 THEN [IN] END)
    , OUT_4 = MAX(CASE WHEN DAYX = 4 THEN [OUT] END)
FROM @data
GROUP BY PERIOD, EMPL_NMBR, EMPL_NAME;


Fastest way to determine size of groups of FILESTREAM objects

Don't know if its the fastest but I grouped the Filestream column into batches of 5000 rows then calculated each batch size before creating a running total:

CREATE PROCEDURE CalculateFSSize @RowCnt
AS

BEGIN

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'FSSizeBatch' AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.FSSizeBatch

CREATE TABLE FSSizeBatch(BatchID INT
                                ,StartID INT
                                ,EndID INT
                                ,BatchCnt INT
                                ,BatchSize DECIMAL(18,4)
                                ,RunningTotal DECIMAL(18,4))

DECLARE @BatchID INT
    ,@StartID INT
    ,@EndID INT
    ,@BatchCnt INT

DECLARE cur_1 CURSOR FOR

    SELECT ((Rank - 1) / @RowCnt) + 1 AS BatchID
            ,MIN(ID) AS StartID
            ,MAX(ID) AS EndID
            ,COUNT(*) AS BatchCnt
    FROM (
        SELECT RANK() OVER (ORDER BY ID) Rank
            ,ID
        FROM [dbo].[FS]
        ) T
    GROUP BY ((Rank - 1) / @RowCnt) + 1
    ORDER BY BatchID

OPEN cur_1
FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO FSSizeBatch(BatchID, StartID, EndID, BatchCnt, BatchSize)

SELECT @BatchID
        ,@StartID
        ,@EndID
        ,@BatchCnt
        ,SUM(CAST(DATALENGTH([JSONDATA]) + DATALENGTH([ZIPDATA]) AS DECIMAL)/1024/1014 )
FROM [dbo].[FS]
WHERE ID BETWEEN @StartID AND @EndID

  FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt
END

CLOSE cur_1
DEALLOCATE cur_1

;WITH a AS (
        SELECT BatchID, RunningTotal, SUM(BatchSize) OVER(ORDER BY BatchID ROWS UNBOUNDED PRECEDING) AS RowTotal
        FROM FSSizeBatch
        )
UPDATE a
SET RunningTotal = RowTotal

SELECT *
FROM FSSizeBatch

END


Join to get most specific record for tenant without PIVOT

Thanks for the great data setup. Here's one way to get these hits in SQL.
As Aaron mentioned, this can be a CTE without any varibles.
WITH cte AS (
                SELECT  t.AccountId
                        ,t.CompanyId
                        ,t.FacilityId
                        ,CASE
                             WHEN s.AccountId IS NOT NULL THEN 1
                             ELSE 0
                         END + CASE
                                   WHEN s.CompanyId IS NOT NULL THEN 1
                                   ELSE 0
                               END + CASE
                                         WHEN s.FacilityId IS NOT NULL THEN 1
                                         ELSE 0
                                     END AS HitCount
                        ,s.SettingValue
                FROM    dbo.Tenant AS t
                        LEFT OUTER JOIN dbo.Settings AS s ON t.FacilityId LIKE ISNULL(CAST(s.FacilityId AS VARCHAR), '%')
                                                             AND   t.CompanyId LIKE ISNULL(
                                                                                CAST(s.FacilityId AS VARCHAR)
                                                                                ,'%'
                                                                                )
                                                             AND   t.AccountId LIKE ISNULL(
                                                                                CAST(s.AccountId AS VARCHAR)
                                                                                ,'%'
                                                                                )
            )
SELECT  t.AccountId
        ,t.CompanyId
        ,t.FacilityId
        ,t.SettingValue
FROM    cte AS t
        CROSS APPLY (
                        SELECT  MAX(t2.HitCount) AS MaxHits
                        FROM    cte AS t2
                        WHERE   t.AccountId = t2.AccountId
                                AND t.CompanyId = t2.CompanyId
                                AND t.FacilityId = t2.FacilityId
                    ) AS hc
WHERE   1 = 1
        AND hc.MaxHits = t.HitCount;


Shortest Query to Combine Count and Unique (Oracle)

Not that I recommend it, but:

select count(distinct ora_hash(col1)||'|'||ora_hash(col2)||'|'||ora_hash(col3))
from my_table
where cond1 = 'A'
and cond2 = 'B'
and cond3 = 'C'

A nested query seems like "the right answer" to me.


You get 11 languages (well, 10 if you count us and british english as the same) from SELECT DISTINCT language_id FROM sys.messages
and 33 from SELECT DISTINCT lcid FROM sys.syslanguages.

If not listed in sys.messages, messages for the default language are used.

As for SSRS:

  If you install Reporting Services in a default configuration, Setup
  determines the report server language when it creates the report
  server database. The language resource that Setup uses is the one that
  mostly closely matches the language of the operating system: If an
  exact match between the operating system language and a SQL Server
  language is found, Setup will use the exact match. For example, if the
  operating system language is U.S. English, Setup will use the SQL
  Server language resource for U.S. English as the report server
  language. If an exact match cannot be found, a close match will be
  used instead. For example, suppose the operating system is U.K.
  English. Because U.K English and U.S. English are in the same language
  family, Setup chooses the SQL Server language resource that most
  closely matches U.K English (in this case U.S. English). If there is
  no close match available, the report server language will be U.S.
  English.

https://msdn.microsoft.com/en-us/library/ms156493%28v=sql.105%29.aspx


How to count the reads on index in MySQL

Metrics
It is possible to find the number of rows looked at in the tables for a single query.  These count rows of index or data, not counted separately, for your connection.  If no other queries are running, total counts are possible.
SHOW SESSION STATUS LIKE 'Handler%';

Here is a discussion of using that to help with optimization: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts
From
SHOW GLOBAL STATUS LIKE 'Innodb_%';

you can get certain global counts for disk accesses, etc. for all InnoDB statements from all connections.  (This is not precisely what you are asking for, but may be interesting.)
Another source is the system database performance_schema.  (In older MySQL versions, information_schema had some tables, but there are mostly moved to PS now.)
If you elaborate on the goals of your question, we might be able to help in more detail.
Optimizing that query
Start by using the modern JOIN syntax:
FROM lineitem AS l
JOIN part AS p  ON p.partkey = l.partkey

That was a common part of the ORs; this makes it so the Optimizer can do the JOIN more efficiently.
In case the Optimizer decides to start with part, have this
lineitem:  INDEX(partkey)  -- (but see below)

Now I see that l.shipinstruct = 'DELIVER IN PERSON' is common across the ORs, so pull it out.  Then this would probably be useful
lineitem:  INDEX(shipinstruct)  -- (but see below)

But, all of that probably still won't do as much Optimization as turning the OR into UNION ALL:
( SELECT ... Brand#52 ... )
UNION ALL
( SELECT ... Brand#11 ... )
UNION ALL
( SELECT ... Brand#51 ... )

Then we can build some even better indexes:
part:  INDEX(brand, container)
part:  INDEX(brand, size)
lineitem:  INDEX(shipinstruct, quantity)
lineitem:  INDEX(shipinstruct, shipmode)
lineitem:  INDEX(partkey, shipinstruct, quantity)
lineitem:  INDEX(partkey, shipinstruct, shipmode)

There are many ways the Optimizer might pick to run the query; I think these cover all the bases.


MariaDB: Created_tmp_disk_tables high after start

You may have other limiting factors involved. Here are some suggestions:
Additional Buffers
The per-connections buffers you may need to increase includes

join_buffer_size
sort_buffer_size

Queries will demand temp table based on join and sort operations. These may need to be increased.
Improve Queries
Queries that need to do joins and sorts can be reduced if queries had proper indexing. I would suggest that you profile all your queries using pt-query-digest and create whatever indexes that table need to reduce the incidence of temp table creation.
Create a RAM Disk
You should map a chunk of your RAM to be a dedicated RAM disk. Then configure tmpdir to point to it. See my post Is it bad to create many mysql temporary tables simultaneously? on how to set one up.


Postgres \copy from CSV: how to format range variables?

Removing the explicit cast to tstrange worked. The new CSV file looks like

t_range|field1
'[2014-06-02 07:00:00,infinity)'|'sometext1'
'[2014-06-02 07:00:00,infinity)'|'sometext2'

It seems to me that \copy is fine with automatically attempting to cast to the appropriate datatypes, but the explicit cast causes it to choke.

One can also simplify the CSV file a bit in this case by removing the quotes:

t_range|field1
[2014-06-02 07:00:00,infinity)|sometext1
[2014-06-02 07:00:00,infinity)|sometext2

and modifying the import command:

\copy my_table FROM 'my.csv' DELIMITER '|' CSV HEADER;


How to find the last error codes and messages of failed statements executed by a user?

You can create an AFTER SERVERERROR trigger and log the relevant information to a table.

Here is an example: http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html


Personally i have staging tables for extract, transform and persistent data storage.

Whether you do full exports or incremental loads will depend on what tools you have, your strategy and whether your app schema and data support it. Sometimes you cant avoid full exports.

Adding a column to a dimension isnt a big deal, but backfilling historic data could be very difficult or may not be possible at all. Trying to reconstruct how an app looked at a point in time retrospectively would be a major undertaking. You would need a very good case to justify that.

All of the things you mention are possible, but only you can decide if the cost/benefit is worth it.


What is the difference between select count(*) and select count(any_non_null_column)?

COUNT(*) will include NULLS
COUNT(column_or_expression) won't.

This means COUNT(any_non_null_column) will give the same as COUNT(*) of course because there are no NULL values to cause differences.

Generally, COUNT(*) should be better because any index can be used because COUNT(column_or_expression) may not be indexed or SARGable

From ANSI-92 (look for "Scalar expressions   125")

  Case:

  a) If COUNT(*) is specified, then the result is the cardinality
                of T.

  b) Otherwise, let TX be the single-column table that is the
                result of applying the <value expression> to each row of T
                and eliminating null values. If one or more null values are
                eliminated, then a completion condition is raised: warning-
                null value eliminated in set function.

The same rules apply to SQL Server and Sybase too at least

Note: COUNT(1) is the same as COUNT(*) because 1 is a non-nullable expression.


Database/Storage engine suggestion for a project involving heavy inserts?

Let's break this question up into a few parts.

Q: I need to insert 1mm rows a day. Is that a lot?

Not really. 1mm divided by 24 hours divided by 60 minutes divided by 60 seconds gives you about 12 inserts per second. For a rough frame of perspective, it's not unusual to see 1,000 inserts per second in typical commodity servers with no tuning.

Granted, your load won't be perfectly averaged out like that - you'll have bursts of load - but I wouldn't make database platform decisions based on less than 10k-20k inserts per second. Any platform out there will work fairly well.

Q: How should I structure the data?

Zoom out - don't think table, think databases. If you're going to be keeping this data permanently, and it's truly insert-only with no updates, then you probably want to start a new database for time lengths. Your inserts may only go into one table in one database, but every year, create a new database (MyApp_2015) and seal the old 2014 data as read-only. You can stop backing it up (as long as you've still got a good backup once), stop doing index maintenance, statistics updates, etc.

The PHP will only ever have to know about the current database for inserts, making your design a lot easier. The archival process becomes a DBA task much later down the road as long as you go in knowing that there will be more than one database involved.

If you were doing more than 1,000 inserts per second sustained, and you wanted easier performance management, then I'd also suggest building sharding into the initial design regardless of the database platform. Don't get me wrong, any modern database can handle over 1,000 inserts per second, but designing sharding in now just gives you more flexibility later on. At 12 inserts per second, it's just not worth the design/testing hassle.

Q: How should I do reporting?

In an ideal world, reports would not be done against the live server. Run the reports against a restored or replicated copy of the database. This does two things: it reduces load on the live server, and it validates your backups, guaranteeing that you've got your valuable data elsewhere.


SQL Server shrink command 'estimated_completion_time' keeps going up

SHRINKDATABASE and SHRINKFILE won't actually release the space to disk until the very last moment: it has to move all the contents around within the files first (which is the part that takes a long time).

For why the progress doesn't seem constant: the free/used space is spread out across a large file, so it is going to "skip ahead" when it encounters an empty patch and "slow down" when it hits a section of used pages.

As mentioned in the comments, I would highly recommend using SHRINKFILE instead of SHRINKDATABASE, since you can control the target sizes of each individual file, and give each one a reasonable target. For example, I usually try to leave 15-25% free space in each data file.


I see the point you are making: if there is more than 1 student with the same name, enrolled in course 107, with a percentage greater than 90%, then your query will not distinguish between them.

Given the limited amount we know about the dataset, the solution provided does do the job, but I would have added the caveat about duplicate student names, or proposed an alternative solution:

SELECT S.name,
       count(*)
FROM   Students as S,
       Registration as R
WHERE  R.rollno=s.rollno
  AND  R.courseno=107
  AND  R.percent>90
GROUP  BY S.name

and in this way, we list distinct names, but are aware when there are multiple records selected for the same name.


It is being used as a Modulo Operator; returning the remainder of a number divided by another.

In your example, the WHERE clause is limiting the results to only those where the Col1 value divided by 3 leaves a remainder of 1. (e.g. 4,7,10, etc.)


As an example

SELECT a.*, b.*
-- SELECT COUNT(*)
FROM      datatable a
LEFT JOIN datatable b ON  a.id   = b.id
                      AND b.type = 'B'
WHERE a.type = 'A'
  AND (CASE @filterA
       WHEN 'A active'     THEN a.is_active
       WHEN 'A not active' THEN NOT a.is_active
       ELSE 1              -- A is any
       END)
  AND (CASE @filterB
       WHEN 'B active'     THEN b.is_active
       WHEN 'B not active' THEN NOT b.is_active
       WHEN 'B absent'     THEN b.id IS NULL
       ELSE 1              -- B is any
       END)

@filterA and @filterB are variables/literals with user filters choices inserted/transferred into the query as a parameter or literally (edit WHEN conditional values for to match).


What are the benefits of moving redologs, data- and controlfiles to another location?

Seperate locations may have different performance characteristics or high availability or other properties.
For example you may use slower and cheaper storage for data that is rarely accessed, or a location with lower redundancy settings for temporary files.
To be honest, we rarely ever bother with this.
In a clustered environment, the data is stored in a shared location, but the Oracle database software is typically stored on some local storage location.


mysqltuner: "MySQL started within last 24 hours - recommendations may be inaccurate." Why?

It is a basic caution that a MySQL instance running under 24 hours (1 day) may not have seen the entire query workload and thus the recommendations might be affected.

Of course, 24 hours doesn't guarantee the entire workload either, as you might have scripts that run weekly or monthly.

On a separate note, I would caution you on taking the advice of mysqltuner too strictly. Specifically don't make any changes that you don't fully understand just because the script says to do so.


Understanding JOINs and why the syntax works the way it does

The answer is in the parentheses:

SET price=(SELECT price FROM new WHERE new.id=original.id)

This is a scalar subquery.  It only ever returns one value (or no value, in which case it effectively returns NULL).  As @ypercube mentioned in comments, if there were ever more than one matching id in new then this expression would throw an error, since a scalar subquery can't and won't deal with more than one possible value.

This is also (by at least some definitions) a correlated subquery, containing a reference to a table (original) that is not mentioned in the FROM clause of the subquery.

The subquery is, essentially, executed once for each row in original in order to find the needed value in new, and that's how the rows don't get mixed up -- this expression is evaluated for each row in the original table.

At least, conceptually, that's how it happens.  The optimizer is free to decide --  within the limits of its design -- if there's a better way to perform your query than the way you've written it, as long as the chosen approach will still provide the exact same result (with the exception of the ordering of rows, which is by definition, undefined, unless you explicitly ORDER BY in a SELECT statement).  MySQL 5.6 brought some changes in subquery optimization that were largely improvements.

Although sometimes they are absolutely essential, subqueries can be a red flag that a query's logic could be improved, to make things easier on the optimizer and get the work done faster.  This is one of those cases.

A better, arguably clearer, and perhaps significantly better-performing way to write the example query would be this:

UPDATE original o
  JOIN new n ON n.id = o.id
   SET o.price = n.price;

There's no need to include WHERE n.id = o.id because the join will not only join the rows on that criterion, it will also exclude all rows that can't be joined.  The caveat here is that if id in the new table isn't unique, for a different reason than the error the original query would throw.  In this case, the result isn't deterministic, because you're not able to tell the server which of the more-than-one potentially matching row, so it will pick one, and you can't choose which one.  But if id is unique in the new table, there's no problem.

MySQL has historically been not the best at handling WHERE ... IN ( ... ) in some cases, and this rewrite also removes the need for that construct.


I did the following

CREATE TABLE L(
Time_Series_TS TIMESTAMP,
Channel VARCHAR(10),
Operation VARCHAR(10),
Function VARCHAR(10),
Duration INT);

Then

INSERT INTO L VALUES('2014-06-10 09:00:03.457', 'Channel1', 'Operation3', 'Function15', 15);
INSERT INTO L VALUES('2014-06-10 09:00:08.245', 'Channel2', 'Operation5', 'Function10', 22);
INSERT INTO L VALUES('2014-06-10 09:00:22.005', 'Channel1', 'Operation3', 'Function15', 48);
INSERT INTO L VALUES('2014-06-10 09:01:03.457', 'Channel2', 'Operation3', 'Function15', 296);
INSERT INTO L VALUES('2014-06-10 09:01:08.245', 'Channel2', 'Operation5', 'Function10', 225);
INSERT INTO L VALUES('2014-06-10 09:01:22.005', 'Channel1', 'Operation3', 'Function15', 7);
INSERT INTO L VALUES('2014-06-10 09:01:16.245', 'Channel2', 'Operation5', 'Function10', 10);
INSERT INTO L VALUES('2014-06-10 09:01:47.005', 'Channel1', 'Operation3', 'Function15', 20);

I added a few records to your sample for checking. Then ran this query

SELECT MINUTE(Time_Series_TS) AS Minute, Channel, Operation, Function,
COUNT(*) AS "Count/min", SUM(Duration) AS Duration
FROM L
GROUP BY Minute, Channel, Operation, Function
ORDER By Minute, Channel, Operation, Function;

Which gave

+--------+----------+------------+------------+-----------+----------+
| Minute | Channel  | Operation  | Function   | Count/min | Duration |
+--------+----------+------------+------------+-----------+----------+
|      0 | Channel1 | Operation3 | Function15 |         2 |       63 |
|      0 | Channel2 | Operation5 | Function10 |         1 |       22 |
|      1 | Channel1 | Operation3 | Function15 |         2 |       27 |
|      1 | Channel2 | Operation3 | Function15 |         1 |      296 |
|      1 | Channel2 | Operation5 | Function10 |         2 |      235 |
+--------+----------+------------+------------+-----------+----------+

Which appears to be the result you want (note 63 as the 1st duration as per my earlier comment). Is this the result you wanted? You can then use HOUR() and DAYOFMONTH() and even YEAR() to aggregate over these also with this query.

For performance, I did create an index

CREATE INDEX L_Index ON L(Channel, Operation, Function) using BTREE;

and explained the query before and after creating it, but there was no difference. This is hardly a surprise, since the optimizer probably said that there's no point in using one for such a small table. Obviously, I can't test
with your data, but there are a couple of points. If you are performing this operation over a large number of records with a large no. of fields, you may run into issues and if you create many indexes, your insert performance will decrease. Is it possible for you to categorise your data in some way to reduce the number of fields - i.e. split your big table into ones with a smaller number of fields? Check out different scenarios, test and see what happens with your data, your queries, your application and your hardware.

[EDIT]

For something more human readable, you might like to try something like

SELECT TIME(FROM_UNIXTIME(UNIX_TIMESTAMP(Time_Series_TS) - MOD(UNIX_TIMESTAMP(Time_Series_TS), 60))) AS Minute,
..
..

for your first field.

[EDIT - Response to UPDATE-1]

OK - so in my schema, you are indexing by (Minute, Channel, Operation, Function)?
See here for the docco on composite indexes in MySQL. If your queries have a predominatly left-right orientation, i.e you [always | usually] query Channel first and then Operation, then Function, you could try an index on Minute + (the usual three). If it's fairly arbitrary, then you could try using 6 indexes, but this will hit insert performance. How much, I can't say, but if this is a DW type app which performs the analysis, you can batch the inserts and only occasionally take the hit for that. You'll have to do a few tests with realistic data and EXPLAIN your queries - with realistic sample data, as I said earlier, the Optimiser with just a few records ignores indexes because the table is too small. Interestingly, on the MySQL man page given above, there's a hashing strategy which looks interesting - take MD5 hashes of CONCAT(Your_Column_List_Here). One other thing that I can suggest is that instead of using the

SELECT TIME(FROM_UNIXTIME(UNIX_TIMESTAMP(Time_Series_TS) - MOD(UNIX_TIMESTAMP(Time_Series_TS), 60))) AS Minute,...

Just remove the TIME() function and then you'll be storing INTs which appears to be better than indexes on DATETIMES - see here for a benchmark. Also as previously mentioned, you should remove your data from Production and perform the  OLAP/DW on another machine. You could also test out the InfiniDB solution that I suggested. It's drop-in compatible with MySQL (no learning curve). Then there are all the NoSQL solutions - we could be here all day :-). Take a look at a few scenarios, evaluate and test and then choose what best fits your budget and requirements. Forgot: Make your OLAP/DW system read only for performing queries - no transactional overhead! Make the OLAP/DW tables MyISAM? This last one is controversial - again, test and see.


Can I change max_allowed_packet for a MySQL database, but not others in the same instance?

According to the MySQL Documentation on max_allowed_packet

  The maximum size of one packet or any generated/intermediate string.

  The packet message buffer is initialized to net_buffer_length bytes,
  but can grow up to max_allowed_packet bytes when needed. This value by
  default is small, to catch large (possibly incorrect) packets.

  You must increase this value if you are using large BLOB columns or
  long strings. It should be as big as the largest BLOB you want to use.
  The protocol limit for max_allowed_packet is 1GB. The value should be
  a multiple of 1024; nonmultiples are rounded down to the nearest
  multiple.

  When you change the message buffer size by changing the value of the
  max_allowed_packet variable, you should also change the buffer size on
  the client side if your client program permits it. The default
  max_allowed_packet value built in to the client library is 1GB, but
  individual client programs might override this. For example, mysql and
  mysqldump have defaults of 16MB and 24MB, respectively. They also
  enable you to change the client-side value by setting
  max_allowed_packet on the command line or in an option file.

Based on this, your only chance to set it for your connection would be when starting the client program:

mysql -Dmydb --max-allowed-packet=1073741824

After all

max_allowed_packet is an option for mysql client
max_allowed_packet is an option for mysqldump client

You cannot set it for the connection because it is a global option for how packets are used.

I just tried to run this in MySQL 5.6.15 for Windows and MySQL in CentOS release 6.5 (Final) and I got this message

mysql> set max_allowed_packet = 1024 * 1024 * 1024;
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

Please read my posts on the MySQL Packet and its global effects

Apr 21, 2011 : What does the MySQL “max_allowed_packet” setting actually control?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Feb 16, 2012 : Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?
Aug 14, 2012 : "Mysql has gone away" heavy multiple insert
Jul 03, 2013 : What max_allowed_packet is big enough, and why do I need to change it?
All my posts in the DBA StackExchange for max_allowed_packet


Due to how PostgreSQL handles transactions and concurrency, MVCC - Multi-Version Concurrency Control, you can get bloat. In PostgreSQL, when you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, it simply marks the row as unavailable for future transactions, and for UPDATE, under the hood it's a combined INSERT then DELETE, where the previous version of the row is marked unavailable.

While the data is marked unavailable, it is still there, and the space cannot be used. To then mark the space as available for use by the database, a vacuum process needs to come along behind the operations, and mark that space available for the database to use. It isn't returned to the operating system, however. That only happens when there are no active rows in an entire page, which can be uncommon in some workloads. This can be a good thing for some workloads, because you can just simply update the space on the individual pages inside of the data files, without needing to add additional data files.

Problems come about with bloat when there are are excessively large numbers of dead tuples versus live tuples. Walking along and checking all of the visibility flags takes time, and having more data files for a relation results in additional unnecessary IO load. Bloat is especially noticeable on indexes, which can also have many dead tuples, sometimes many more than the table. Bloat can slow index lookups and scans, which will show up in slowly increasing query times and changing query plans.

You can restore space by using pg_reorg, pg_repack, CLUSTER, or VACUUM FULL. This will go through and reorganize the files, moving tuples and reorganizing to make sure that there are no dead tuples, which will eliminate the bloat.

Bloat can also be efficiently managed by adjusting VACUUM settings per table, which marks dead tuple space available for reuse by subsequent queries.

You can use queries on the PostgreSQL Wiki related to Show Database Bloat and Index Bloat to determine how much bloat you have, and from there, do a bit of performance analysis to see if you have problems with the amount of bloat you have on your tables.


What Happens When Identity Range IsExceeded?

If you attempt to bulk insert more rows then available identity values between syncs you will get an error:

  The identity range managed by replication is full and must be updated
  by a replication agent.

The key here is to allocate range sizes large enough to accommodate for the amount of inserts that may occur between syncs.  You can specify the range sizes in article properties when adding articles to the publication.


How can I append a new datafile in an existing InnoDB tablespace?

Plan A:  Reformat the disk to be NTFS.  Caution:  This destroys all data, so you would need to do a major backup and restore.

Plan B:  Turn off innodb_file_per_table, go into my.cnf and configure the main tablespace in a way I have not seen used in more than a decade -- namely something like ibdata1:3G,ibdata2:3G,ibdata3:3G,ibdata4:3G,ibdata5:3G.  That way, you are pre-specifying the partitions and InnoDB (unless it has forgotten how) will be able to handle tables of arbitrary size (> 4GB) if they are in that tablespace.  Finally, ALTER TABLE foo ENGINE=InnoDB to move the table into that tablespace.

Yuck.  Turn back the clock because of Windows.


I have following two MySQL queries and i want to execute it inside one procedure

If you need to loop over the output of one query to another you can use cursors.

For eg, in this stored routine to find in all tables databases we have looped over:

DECLARE table_cur CURSOR FOR SELECT ...
OPEN table_cur;
table_loop:LOOP
...code...
END LOOP;
CLOSE table_cur;

So in your case declare cursor for first query and loop over each recipeid.


How to estimate needed undo retention for a operation?

There are a number of things you can do to ensure successful completion:

Optimize your query. Use DBMS_XPLAN to display your query execution plan, then work to eliminate things that could slow it down, like full table scans, by having proper indexes on join columns. See this article, this article and this article for details on getting started.

Make sure you have accurate statistics gathered on your source tables. This whitepaper from Oracle explains optimizer stats in detail.

If your CTAS query is failing because there have been too many changes to the underlying data, then consider running the command only when there are no competing workloads on the database.

If everything else fails, look at changing Undo settings using the retention guarantee and the Undo Advisor as described here.


We can debug this.  Create your FK with this code below, which includes the EXCEPTIONS clause.  It will identify the specific rows in your child table options responsible for the errors.

CREATE TABLE fk_exceptions (
    row_id     ROWID
  , owner      VARCHAR2(30)
  , table_name VARCHAR2(30)
  , constraint VARCHAR2(30)
);

ALTER TABLE options
  ADD CONSTRAINT fk_options_products
  FOREIGN KEY (prefix, suffix)
  REFERENCES products (prefix, suffix)
  EXCEPTIONS INTO fk_exceptions;

You'll still get your ORA-02298, but the evidence will be harvested and deposited into fk_exceptions table.  Your culprit row(s) will be revealed by:

SELECT * FROM options WHERE rowid IN (SELECT row_id FROM fk_exceptions);


"For each group of results fullfilling condition X, select only result with smallest column value A"

A first fix would be:

SELECT p2.*
from @p p2
JOIN (
    SELECT min(p_validUntil) Min_P_ValidUntil, P_Key
    FROM @p p
    WHERE @qKey = (P_Key/10)*10
    AND @qDate <= P_ValidUntil
    group BY P_Key
) p3 ON p3.Min_P_ValidUntil = p2.P_ValidUntil AND p3.P_Key = p2.P_Key

thanks.


Constellation Schema; Relationships between fact tables

Not really. Fact tables has rows which are a intersection of all the joined dimensions. Joining a fact table to another fact table doesn't really make sense - two facts should be joined through a conformed dimension.
I don't consider it acceptable.
No. Measures quantify the intersection of all the dimensions, they enable you to measure that item not change the granularity or definition of that item. That is what Dimension tables are for.