Must an index cover all selected columns for it to be used for ORDER BY?

Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?

This is a loaded question because there are factors that determine whether an index is worth using.

FACTOR #1

For any given index, what is the key population? In other words, what is the cardinality (distinct count) of all tuples recorded in the index?

FACTOR #2

What storage engine are you using? Are all needed columns accessible from an index?

WHAT'S NEXT ???

Let's take a simple example: a table that holds two values (Male and Female)

Let create such a table with a test for index usage

USE test
DROP TABLE IF EXISTS mf;
CREATE TABLE mf
(
    id int not null auto_increment,
    gender char(1),
    primary key (id),
    key (gender)
) ENGINE=InnODB;
INSERT INTO mf (gender) VALUES
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
ANALYZE TABLE mf;
EXPLAIN SELECT gender FROM mf WHERE gender='F';
EXPLAIN SELECT gender FROM mf WHERE gender='M';
EXPLAIN SELECT id FROM mf WHERE gender='F';
EXPLAIN SELECT id FROM mf WHERE gender='M';

TEST InnoDB

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mf
    -> (
    ->     id int not null auto_increment,
    ->     gender char(1),
    ->     primary key (id),
    ->     key (gender)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO mf (gender) VALUES
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.06 sec)
Records: 40  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |   37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |   37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql>

TEST MyISAM

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mf
    -> (
    ->     id int not null auto_increment,
    ->     gender char(1),
    ->     primary key (id),
    ->     key (gender)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO mf (gender) VALUES
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.00 sec)
Records: 40  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |   36 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | mf    | ALL  | gender        | NULL | NULL    | NULL |   40 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Analysis for InnoDB

When the data was loaded as InnoDB, please note that all four EXPLAIN plans used the gender index. The third and fourth EXPLAIN plans used the gender index even though the requested data was id. Why? Because id is in the PRIMARY KEY and all secondary indexes have reference pointers back to the PRIMARY KEY (via the gen_clust_index).

Analysis for MyISAM

When the data was loaded as MyISAM, please note that the first three EXPLAIN plans used the gender index. In the fourth EXPLAIN plan, the Query Optimizer decided not to use an index at all. It opted for a full table scan instead. Why?

Regardless of DBMS, Query Optimizers operate on a very simple rule-of-thumb: If an index is being screened as a candidate to be used for performing the lookup and Query Optimizer computes that it must lookup more than 5% of the total number of rows in the table:

a full index scan is done if all needed columns for retrieval are in the selected index
a full table scan otherwise

CONCLUSION

If you do not have proper covering indexes, or if the key population for any given tuple is more than 5% of the table, six things must happen:

Come to the realization that you must profile the queries
Find all WHERE, GROUP BY, and ORDER BY` clauses from those Queries
Formulate indexes in this order

WHERE clause columns with static values
GROUP BY columns
ORDER BY columns

Avoid Full Table Scans (Queries lacking a sensible WHERE clause)
Avoid Bad Key Populations (or at least cache those Bad Key Populations)
Decide on the best MySQL Storage Engine (InnoDB or MyISAM) for the Tables

I have written about this 5% rule of thumb in the past:

May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Mar 22, 2012 : Why does MySQL choose this execution plan?
Mar 09, 2012 : index not being used
Jan 18, 2012 : MySQL status variable Handler_read_rnd_next is growing a lot
Dec 27, 2011 : MySQL - fastest way to ALTER TABLE for InnoDB
Jul 29, 2011 : MySQL Query Optimization : Indexing and Pagination
Jul 12, 2011 : MySQL very slow query when changing one WHERE field despite no index/key

UPDATE 2012-11-14 13:05 EDT

I took a look back at your question and at the original SO post. Then, I thought about my Analysis for InnoDB I mentioned before. It coincides with the person table. Why?

For both tables mf and person

Storage Engine is InnoDB
Primary Key is id
Table access is by secondary index
If table was MyISAM, we would see a completely different EXPLAIN plan

Now, look at the query from the SO question : select * from person order by age\G. Since there is no WHERE clause, you explicitly demanded a full table scan. The default sort order of the table would be by id (PRIMARY KEY) because of its auto_increment and the gen_clust_index (aka Clustered Index) is ordered by internal rowid. When you ordered by the index, keep in mind that InnoDB secondary indexes have the rowid attached to each index entry. This produces the internal need for full row access each time.

Setting up ORDER BY on an InnoDB table can be a rather daunting task if you ignore these facts about how InnoDB indexes are organized.

Going back to that SO query, since you explicitly demanded a full table scan, IMHO the MySQL Query Optimizer did the correct thing (or at least, chose the path of least resistance). When it comes to InnoDB and the SO query, it is far easier to perform a full table scan and then some filesort rather than doing a full index scan and a row lookup via the gen_clust_index for each secondary index entry.

I am not an advocate of using Index Hints because it ignores the EXPLAIN plan. Notwithstanding, if you really know your data better than InnoDB, you will have to resort to Index Hints, especially with queries that have no WHERE clause.

UPDATE 2012-11-14 14:21 EDT

According to the book Understanding MySQL Internals

Page 202 Paragraph 7 says the following:

  The data is stored in a special structure called a clustered index, which is a B-tree with the primary key acting as the key value, and the actual record (rather than a pointer) in the data part. Thus, each InnoDB table must have a primary key. If one is not supplied, a special row ID column not normally visible to the user is added to act as a primary key. A secondary key will store the value of the primary key that identifies the record. The B-tree code can be found in innobase/btr/btr0btr.c.

This is why I stated earlier : it is far easier to perform a full table scan and then some filesort rather than doing a full index scan and a row lookup via the gen_clust_index for each secondary index entry. InnoDB is going to do a double index lookup every time. That sounds kind of brutal, but that's just the facts. Again, take into consideration the lack of WHERE clause. This, in itself, is the hint to the MySQL Query Optimizer to do a full table scan.


Why does a SELECT with no WHERE cause a full table scan, (or does it)?

EXPLAIN QUERY PLAN shows only an estimate, and the LIMIT clause is ignored when doing this estimation.
(And this number is so misleading that recent versions of SQLite do not show it.)

A plain SELECT * FROM TableA LIMIT x is the fastest way to get x rows from a table. SQLite computes result rows on demand, so it will not read the entire table but stop scanning after x rows.

Table scans are likely to be inefficient when you are searching for a single row or a few rows, or when you have a filter that greatly reduces the number of rows to be returned. But when the query is going to return all rows anyway, it is not possible to reduce the number of rows that actually need to be read from disk, so the best way to reduce disk I/O is to avoid touching anything but the table itself. (In this case, a covering index can replace a table.)


What you are needing help is called Grouped Concatenation in SQL Server

This sql fiddle that I created will help you.

Basically you use stuff + FOR XML PATH.

If you are on sql server 2017 (vNEXT) then STRING_AGG is an inbuilt function that you can use

-- sql server vNEXT

SELECT [ColumnA]  =  [ColumnA],
       [ColumnBs] = STRING_AGG(ColumnB, ',')
 from dbo.Table1
 group by ColumnA


What is the meaning of Microsoft Compatibility Level?

MSDN ALTER DATABASE Compatibility Level explains the behavior of the various levels. (just scroll down and you will find the comparison tables for the following topics )

you can find more specific comparison tables if you just change the Version from MSDN top menu (they are linked for top 3 versions in following list)

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008 and R2
110 = SQL Server 2012
120 = SQL Server 2014
Differences Between Lower Compatibility Levels and Level 120

sample line (The older query optimizer is used instead of new SQL Server 2014 engine which includes substantial improvements to the component that creates and optimized query plans )

Differences Between Lower Compatibility Levels and Level 110


How to hide/disable tables without dropping them to check redundancy?

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.


SQL agent jobs hung state (SSIS jobs hung)

We noticed similar behavior few times within a year on one of the production server with SQL Server 2014 SP2 installed.

Symptom: All SQL Agent jobs that run SSIS packages suddenly hanged in running state, while no real activity on data engine side.

Resolution: Both times this issue was resolved by pushing all pending windows updated patches to be installed. After this SQL Agent was restarted and jobs behavior was normal again.

Seems the issue is somehow related to .NET stack on a server and OS that we obtain via WSUS


Instead of editing the existing trigger, try to open a new SQL Console and try this :

DROP TRIGGER IF EXISTS `AuditSetExpiryTokens`;
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `AuditSetExpiryTokens` BEFORE
INSERT ON `token` FOR EACH ROW
BEGIN
 SET NEW.expiry=DATE_ADD(NOW(),INTERVAL NEW.duration SECOND);
 SET NEW.delete_date=DATE_ADD(NOW(),INTERVAL(NEW.duration+(3600*2)) SECOND);
END$$
DELIMITER ;


How to convert postgresql dump file into mysql dump file

You can do this using the MySQL Workbench Migration Wizard, which was introduced in MySQL Workbench 5.2.41.

There's a blog post on the MySQL Workbench site here that details how to perform a migration.

Note that it will not be able to migrate more complicated database features.


SQL Server column type = Date not accepting dates as varchar in insert statemant

SQL Server, internally, stores dates in a binary representation. All dates from whatever source are in the same representation. So a date is a date is a date whether it originates in Paris, London or Stockholm.

Since humans aren't great at communicating in binary, by and large, we have to pass values to SQL Server in a form we can understand. This is generally text. The insert statement you show has a great many strings representing dates. Some are parameters to DATEADD, some are predicates. To be able to calculate and compare correctly the server must convert these strings and dates to a common representation. The rules of precedence say that will be the internal date representation.

In order to translate a string to a date SQL Server must know which part of the string to interpret as years, months, days etc. To do that it uses the culture of the current session. This information can be found from a DMV

select language, date_format
from sys.dm_exec_sessions
where session_id = @@spid;

For me this returns

language   date_format
---------- -----------
us_english mdy

I have no problem running your insert statement. However, if I change it thus

set language British;

language  date_format
--------- -----------
British   dmy

I get your error. The same happens for set language French; because they share the dmy format. This leads to colaring the culprit for your error as the upper bounds on the WHERE and BETWEEN clauses. Either change the session settings to match your query (above or DATEFORMAT only) or vice versa, or use CONVERT with styles to tell SQL Server how to parse your strings.

I couldn't get it to work by changing the date columns to varchar. Don't know what's going on there.

There is a deeper point here, however. You jump through a great many hoops to get your insert data just right. This is wasted effort. No matter how the data is presented it is stored internally in a single way.

In particular those three "French" columns are all defined as date. Date is an internal representation. When you eventually select these rows back you'll find they're all presented in the same way.

What you're trying to achieve is date formatting not date storage. That is properly the responsibility of the UI or report-printing software. SQL Server does not store dates in any format. It has a binary representation.

That said, it does offer the CONVERT and FORMAT functions for when they must be used. You can use this and computed columns to achieve your outcome.

drop table if exists #t;

create table #t
(
    d date,
    FullDate as FORMAT(d, 'MMMM dd, yyyy'),
    MonthShortName as FORMAT(d, 'MMMM'),
    FrenchLongYear as FORMAT(d, 'MMM dd, yy')
);

insert #t(d) select GETDATE();

select * from #t;

d           FullDate        MonthShortName  FrenchLongYear
----------  --------------  --------------  --------------
2020-04-02  April 02, 2020  April           Apr 02, 20


You can check for the existence of (non-)UTF-8 data by comparing byte length to character length on a column, e.g.:

SELECT * FROM MyTable
WHERE LENGTH(MyColumn) <> CHAR_LENGTH(MyColumn)

Multibyte characters will have a greater LENGTH (bytes), so you'll need to look for where that condition isn't met.

Note that MySQL's utf8 character set isn't true Unicode UTF-8 as it only supports a maximum of 3 bytes per character. If your MySQL is later than 5.5.3 you can use utf8mb4 to get 4 bytes per character.


can i use mysql views to update and delete the records in mysql

Yes, but with caveats. Some aspect of your view probably breaks one or more of these conditions:

http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

  For a view to be updatable, there must be a one-to-one relationship
  between the rows in the view and the rows in the underlying table.
  There are also certain other constructs that make a view nonupdatable.
  To be more specific, a view is not updatable if it contains any of the
  following:

  Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
  DISTINCT
  GROUP BY
  HAVING
  UNION or UNION ALL
  Subquery in the select list (fails for INSERT, okay for UPDATE,    DELETE)
  Certain joins (see additional join discussion later in this section)
  Reference to nonupdatable view in the FROM clause
  Subquery in the WHERE clause that refers to a table in the FROM    clause
  Refers only to literal values (in this case, there is no underlying    table to update)
  ALGORITHM = TEMPTABLE (use of a temporary table always makes a view    nonupdatable)
  Multiple references to any column of a base table (fails for INSERT,    okay for UPDATE, DELETE)

If you post the view definition we might be able to tell you which one


How to include "Actual Row" counts in Execution plans pulled from the Query Cache or Query Store?

You are not going to see actual row counts from the plan cache as it stores estimated plans with estimatesd row counts and compiled parameter values.

If you truly believe you need actual row counts to come up with a performance solution you have to actually execute the query or trace it using Profiler or EE specifically using the Showplan XML Event Class.


Difference between DATE(time_field) AND time_field in a WHERE clause

Just addressing this question

  What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?

The difference is how the Query Optimizer treats them.

If the WHERE clause has time_field > 'x', this signals the Query Optimizer to try looking for any index so as to take advantage of it doing a range scan.
If the WHERE clause has DATE(time_field) > 'x', this signals the Query Optimizer to throw all indexes under the bus because the DATE function has to be called across the whole table (in the form of a full table scan) or the join result.

Depending on the key distribution of time_field, DATE(time_field) > 'x' triggering a full table scan just happens to be better than a range scan on an index if the values make up a significant percentage of the index. This is even more true using InnoDB because a full table scan passes through the clustered index (where PRIMARY KEY and row data coexist), while a secondary index on time_field would cause a lookup of the secondary index  in addition to the clustered index. If such a secondary index had a lopsided key distribution, such an index would be ignored in favor of a full table scan anyway.

This conjuecture is only indicative of your current dataset. Someone else's dataset may have a better (evenly distributed, more balanced) key distribution, resulting in time_field > 'x' working better than DATE(time_field) > 'x'.

To see such differences, run the EXPLAIN on both SELECT queries. The results may be different.


Can someone guide me how to compare tables with SSIS

If you really want to do this in SSIS, add 2 sources with the SQL from each table and link them to a Merge transformation, which will do a FULL JOIN (select it from the drop down) operation on your columns orderid between the 2 data sets. SSIS needs both data sources to be ordered by the column you are joining so either add an ORDER BY orderID on both your sources and mark the column orderID as SORT on the advanced editor (so the merge knows that data is sorted by that column), or add a custom Sort transformation between the source and the merge on each flow to explicitly order by this column.

Then you can add a Derived Column transformation right after the merge with an expression to determine if either:

OrderID1 is NULL (record is from data set 2).
OrderID2 is NULL (record is from data set 1).
OrderID exists on both data sets.

If you also need to know if data changed when both order ID's match, then you will have to write an awful expression comparing each column against each other, making sure to consider NULL values. SSIS isn't good to build expressions (they are hard to read and even harder to maintain), that's why I always recommend to do this directly on SQL.

Finally link the derived column to your destination database.

It's worth mentioning that from your FULL JOIN example, the following CASE scenario:

case
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    when t1.orderid = t2.orderid then 'matching'
    when t1.orderid != t2.orderid then 'not matching'
    else 'error'
end

Can be simplified into:

case
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    else 'matching'
end

Since you are joining by orderid, either they don't join on each side (option 1 & 2) or they match exactly (else), there is no way that they can join but differ in value.


That is a big fat "it depends on your application(s)". You say how large the total data is but not the commonly active data-set? All in one DB or several? If several is it the same structure in each (i.e. multi-tenant with one tenant per DB) or a mix of different application databases? What sort of read and write IO loads do the database(s) see and what level of concurrent requests. How much memory on the database server? If it is very read heavy and the data is small enough to fit in your RAM then disk choice is not going to make a lot of difference.

There are a few very general rules for databases:

SSD will trump traditional disks by a fair margin unless your data is large enough for that to be far too expensive. If your data is too large for SSDs to be affordable perhaps use partitioning (either manual by separating tables and/or databases between different drives and/or "real" partitioning to spread the data more granularly) to keep active data on the faster drives and archive data elsewhere.
As in the last point: separating data between different drives/arrays can help performance greatly in some cases even if the drives are all of the same performance levels, by reducing IO contention.
RAID for drive error protection and speed: RAID10 is generally recommended. R5 and R6 can have a performance issue for workloads with any significant write activity. Sometimes four drives are used as two independent RAID1 arrays instead of one RAID10 (two separate mirrored volumes instead of one striped set of two mirrors) to separate heavy IO onto specific disk sets - for some workloads this can be advantageous but it is more work to manage.
Have as much RAM as you can throw at it: if your data all fits in memory that'll be as fast as it can go.


Advantages of a hierarchical schema for private chat

One advantage is that the system can distinguish each thread of conversation. That makes it easier for the computers and for the people. For example, machine analytics will benefit by knowing who replies to whom, and hence who the cliques, influencers and outliers are. People will benefit from being able to segregate each topic of conversation. Responses to direct questions do not get lost in the general traffic. Long-ago posts can be brought back into play without having to explicitly pull context. You can get notification on responses to your messages, rather than just general traffic in the chat room, which is more helpful.

Say I log onto a chat. There are some messages to which I want to reply. One says "Fancy a movie on Tuesday?" the other "Fancy skating on Tuesday?" I reply "yes" and "no." What will happen on Tuesday? I could write "I want to go skating but not see a movie." From my point of view clicking the "reply to" button and typing two single words is much easier and conveys the same information.

As an aside, I'd take statements of an absolute nature about how third-party systems are written with a pinch of salt until they are substantiated by knowledgeable sources.


SQL to select all siblings with specific child

In the WHERE you can simply filter for the parts which have an assemblie id equal to the assemblie id of 'Part B'

SELECT a.*, p.*
FROM assemblies a
LEFT JOIN parts p USING (AID)
WHERE p.AID IN
  (
    SELECT AID FROM parts WHERE P_NAME='Part B'
  );


Transaction log backup - Only full recovery model?

No, this is not correct.

See SQL Server Recovery Models

If you are utilising the BULK recovery model, this will function the same as the FULL recovery model apart from specific minimally logged operations (SELECT INTO, BULK INSERT, CREATE INDEX...)

If you do not set up log backups, you risk your log files filling.


Can SQL use an index while creating another?

You can do

CREATE TABLE T(A INT, B INT, C INT)

INSERT INTO T
SELECT 1,1,1
FROM master..spt_values

GO

CREATE INDEX ix ON T(A);

GO

--This won't benefit from existing index as noncovering
CREATE INDEX ix ON T(A) INCLUDE (B) WITH(DROP_EXISTING = ON);

GO

--This can benefit from existing index
CREATE INDEX ix ON T(A)  WITH(DROP_EXISTING = ON);

GO

DROP TABLE T;

There is no particular advantage of doing so in the first case.

Whilst the existing index provides the desired sort order it will not be used for this as it does not contain the newly added column B and so it would require a lookup back to the base table for each row. The plan shows a table scan and sort.

The use of DROP_EXISTING here could provide benefit if you were removing an included column though - as in the second DROP_EXISTING example (as the original index would both have the desired order and cover all columns).

The plan for this shows that the original index was read to create the replacement.


Zero Maintenance self-hosted off-line databases?

Disclaimer
  I am in no way affiliated with Gupta, Gupta Technologies, OpenText or any of their subsidiaries. The statements made are my own personal experience or are statements taken directly from the "OpenText Gupta Development Tools and Databases" site.

Intro

This is my personal opinion based on a CRM system we were using back in the 90's which replicated data between salesperson's laptops and satellite and/or central database servers running the same RDBMS.

OpenText Gupta Development Tools and Databases

I'm still in possession of binder containing the following books:

SQLBase - Database Administrators Guide
SQL Console Guide
SQLBase - SQL Talk Command Reference

Managing Database

The database was very easy to maintain once you had the knack of running everything via SQL Talk, a simple SQL Tool with an an input and an output window.

There was a SQL Console back then, which gave you a bit more leverage on the system, but it wasn't really required.

In my personal experience the database (laptop and/or server database) performed at or near peak level for nearly a year before you had to set off a simple:

REORGANIZE;

Another statement to verify that everything was OK, was the simple:

CHECK DATABASE;

Backups are similar to handel (if required), by issuing one or two of the following commands:

BACKUP DATABASE FROM <database name> TO <directory name> ON CLIENT;
BACKUP LOGS FROM <database name> TO <directory name> ON CLIENT;
BACKUP SNAPSHOT FROM <database name> TO <directory name> ON SERVER;

However they did recommend to:

  BACKUP SNAPSHOT is the recommend way to backup your database and log files because it is easy and provides you with a backup from which you can recover the database in one step.

Performance

The database (used to) perform(s) very well up to a size of about 2 GB, after which the database would have to have been split into multiple database files.

Comparing Size of Databases

When we had to switch from Gupta SQL Base to Microsoft SQL Server for licensing reasons, the main database of one of our "Technical Applications" which used to be 2 GB in size grew to 8 GB in size just from the conversion. No additional features added. No new indexes. Just a conversion of the DDL, content and indexes from Gupta SQL Base to Microsoft SQL Server.

Company Statements

I can fully support some of the statements made on the homepage for the RDBMS OpenText Gupta SQL Base product:

  OpenText™ Gupta SQLBase is a fully relational, high performance, embedded database that allows organizations to manage data closer to the customer, where capturing and organizing information is critical. SQLBase provides a self-recovering, highly automated embedded database architecture that enables users from corporate IT to ISVs to deploy SQLBase embedded in software solutions having automated deployment and configuration and automated maintenance. With its small footprint and low TCO, SQLBase is the embedded database of choice for ISVs and organizations around the world.

  SQLBase integrates very well with many development tools, is highly secure and available for latest operating systems. The integrated tools allow testing for developers and to setup and configure automated maintenance with ease.

...including two of their statements made on their "Benefits" summary:

  Automate Installation, Configuration and Maintenance

  SQLBase provides automated deployment and configuration. Automated maintenance further reduces total cost of ownership of database solutions that include SQLBase.

  Support for TD Mobile Mobile Workforce Apps

  SQLBase is a great central database for TD Mobile mobile workforce apps. Very easy integration into the development system and easy application deployment make SQLBase a very good choice for these types of apps. Flexible licensing options allow for easy centralized deployment.

History

The Gupta Database which was initially owned by Gupta Technologies, has been handed around quite a few times (Centurasoft, Unify, ...) and is now associated with OpenText.


I found a way to do this using two batch files:

test1.bat
------------------------------------------------------------------------
db2cmd -c -i -w "test2.bat"
PAUSE

test2.bat
------------------------------------------------------------------------
set db2instance=INST1
db2pd -db MYDB -hadr

This is effectively the same as running in interactive mode but if anybody has a way to do this using only one batch file please let me know!


Streaming replication archive folder taking all space- Postgresql- 9.4

Does the archive exist only to support the streaming replication, or do you want it for other reasons like PITR?  (Since you attempted to configure pg_archivecleanup in the first place, I guess I can assume you don't want it for other reasons...)

Because your restore_command is currently broken, then you know that the WAL archive has not been needed thus far to support streaming replication--because if it had been needed it would not have worked.  That is, your replica has never fallen far enough behind that the master has removed the WAL from pg_wal before the replica had fetched it.  If you think that this will always continue to be the case, then you just turn achive_mode = off and delete your current archive.  There is no point in maintaining an archive that is not and cannot be used.

Since the introduction of replication slots, pg_archivecleanup is more-or-less obsolete.  If you want a backstop in case the replica doesn't naturally keep up, you can make a shared mount point and use pg_archivecleanup on that mount point if you want. But configuring a replication slot is a better way to accomplish the same thing.


I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column
https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png
↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.


Insert from one row to another using cases

so it's work for me..

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
    `ctg`,
    `msg`,
    `id`,
    `date`,
    CASE `status`
        WHEN 'empty' THEN 'N'
        WHEN 'filled' THEN 'Y'
    END
FROM `request`


What is the advantages of using auto_increment in database as primary key over set of attributes?

circle_share1 does not require much in-depth design thought.  circle_share2 requires some actual brain cycles to determine if the primary key will have the desired performance.

Rows in circle_share1 will be written to the disk in the exact order they are inserted, thereby making inserts quicker.

Rows in circle_share2 may be inserted anywhere in the table, necessitating page-splits thereby fragmenting the data and possibly resulting in slower performance.

Neither way is the correct way in all circumstances - the best way depends on your data, and how it will be created in the user and circle tables and inserted into the circle_shareX table.


How to get all distinct words in a column

SELECT DISTINCT
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.col_name, ' ', numbers.n), ' ', -1) col_name
FROM
  (SELECT 1 n UNION ALL SELECT 2
   UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN table_name
  ON CHAR_LENGTH(table_name.col_name)
     -CHAR_LENGTH(REPLACE(table_name.col_name, ' ', ''))>=numbers.n-1
ORDER BY
  col_name;

You can see it here: http://sqlfiddle.com/#!2/b5be5/2/0

reference: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows


Hallengren @DatabasesInParallel sql_message_id 50000 in sysjobhistory table

The Message 50000 in msdb.dbo.Sysjobhistory is intended.

Take part of Ola Hallengren's index optimize procedure:[dbo].[IndexOptimize], part of the integrity checks: [dbo].[DatabaseIntegrityCheck]  or part of the backup procedure: [dbo].[DatabaseBackup], the same RAISERROR() Statements will be shown:

  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120)
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max))
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Platform: ' + @HostPlatform
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Parameters: ' + @Parameters
  SET @StartMessage = REPLACE(@StartMessage,'%','%%')
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Version: ' + @VersionTimestamp
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10) + ' '

  RAISERROR(@StartMessage,10,1) WITH NOWAIT

The RAISERRROR()'s are going to be executing when the procedure runs.

Looking into the history, we see the 'error's' shown:

select message From msdb..sysjobhistory
where sql_message_id =50000

Result

Executed as user: DOMAIN\test. ...0000)  Server: test [SQLSTATE 01000] (Message 50000)  Version: 12.0.5546.0 [SQLSTATE 01000] (Message 50000) ...

With the same Server and Version info as defined in the previous T-SQL examples.

Why does the 'error' start at 50000?

It starts at 50000, because starting from 50000 and upwards is where user generated errors can be defined.

Why are there raiserror()'s in the procedures?

My opinion is that the RAISERRROR() is added to add additional information to the history DMV, as to know what is being executed, and have additional information without it being a mess. It gives you control over what will be shown.

I will leave more in depth reasoning to Ola himself.

Subsequentially the history is added to the SSMS Job Activity monitor that we (Most of us) know and use:

So to answer the question, it is coded into the proc with reasoning behind it.

RAISERROR() 50000 default example

You can only generate custom errors with raiserrror when using an error number starting from 50000.

Testing with a job, with no specific error number:

Result:

Starts at 50000.


status table join slows down query by 10 seconds

A PRIMARY KEY is a UNIQUE KEY and is an INDEX.  Don't re-declare id as UNIQUE.

Don't start an index with the PRIMARY KEY; it is likely never to be used.

Don't index flags; they are unlikely to be used.

So, I have just argued against all 6 secondary indexes, leaving only PRIMARY KEY(id)

There are only 3 rows in assessment_status, correct?  It is actually faster to do a table scan than to use an index.

Your first query seems to need to find the one row with completed = 1, then find the rows in au with au.idStatus=3, correct?

The EXPLAIN that you provide does not match the SELECT.  There are 2 tables in the select, but only one row in the explain??

Ditto for the second EXPLAIN.  But, there I see id=5 and id=3, implying that there are even more tables!  It could be that the order of tackling the tables was drastically changed; this could be a cause or clue of the timing differences.

How big is au?  The missing line in the EXPLAINs probably says that it will be a table scan, and gives an estimate of the number of rows.

Was the 12-second run tried twice?  I ask because the first run may have spent a lot of time fetching data from disk and the second would be running entirely from cached data in RAM.  This could also explain why the other query took only 0.6 sec.

If you are going to have the Question focus on a portion of the real query, then provide a shrunken test case that demonstrates it.  Else it may not be a valid shrinking, and we cannot necessarily help your real query.

Still, I will guess at what is going on.  The first query (I guess) had only one thing in the WHERE clause, a reference to as_s.  This tricked the Optimizer into deciding to start with that table.  But because of stuff not in evidence, some other table would be better to start with.  The second query seems to have taken that path; it could not start with as_s anyway.  A guess: there is no WHERE in the second query?


connection to Informix still active despite closing?

Check your connection pooling (settings in IBM Data Server Provider).
I never used it, but the docs on IBM Data provider say:

  When a connection is first opened against a database, a connection pool is created. As connections are closed, they enter the pool, ready to be reused within the same process by other applications that need connections.

So even if you close the "session" from you app to Informix DB, a connection is still active between IBM Server Provider and the Informix instance.

Hope this helps.


TSQL substitute for MAX() string ranking

Use RANK() function

with CTE_X as
(
  select *,
  RANK() over (partition by Col1
             order by Col2 DESC) rnk
  from t

) select * from CTE_X  where rnk = 1 ;

For reference (BOL) http://msdn.microsoft.com/en-us/library/ms176102.aspx


Strategy-design of a database for medication

I would suggest you merge the 1st 2 tables, since every box will contain a single type of medicine. This way querying your database would be faster as well.

Eg. You want to get the qty of medicines of a particular type, you will have to query by joining these 3 tables. Instead, add the medicine name column in the second table and for medicines with no stock you can get the data from a single table, instead of querying 2 tables.


Force index using the unused index columns having effect as order by for the result set?

There is no guarantee of any order without an explicit ORDER BY clause.
This is a fundamental aspect of SQL which deals with sets. Sets, by definition, have no order. An "Ordered Set" is no longer a set, it is a cursor.
Even if you happen to see rows come back in a certain order repeatedly due to indexes or execution plan operators, you should never assume it will always be that way.
I've seen many application fail due to making such assumptions.

I'm not sure what 'some sort of limitation' prevents you from using ORDER BY, but as a general rule, and since the order in this case is only for presentation purposes only, you should consider ordering the rows at the client side instead of at the database.
The client (or presentation tier) is responsible for presentation aspects such as order, fonts, colors, indentations etc.

Also note that your LIMIT clause can pick any 100 arbitrary rows without an ORDER BY.
Don't expect the LIMIT to pick up the first 100 rows based on some index order.

HTH.


Domain Certificate Authority Generated Certificate and SQL Server - Keyset does not exist

For my particular issue I needed to give the user access to read the Cert.

So, with the administrator of SQL Server you have to:

Open CertLM and find the certificate that you are going to use for Always Encrypted
Right click the Certificate
Press All Tasks > Manage Private Keys
Press the Add... button if the username is not there
Find your User or Group in AD
Give that User or Group read access.


Find out the number of rows/columns changed - SQL Server?

Because sys.indexes is the recommended catalog view...

The reason behind non-using of row/colmodctr is another one (you can find it in sys.sysindexes (Transact-SQL) article:

  In SQL Server 2000 and earlier, the Database Engine maintained
  row-level modification counters. Such counters are now maintained at
  the column level. Therefore, the rowmodctr column is calculated and
  produces results that are similar to the results in earlier versions,
  but are not exact.

  If you use the value in rowmodctr to determine when to update
  statistics, consider the following solutions:

  Do nothing. The new rowmodctr value will frequently help you determine
  when to update statistics because the behavior is reasonably close to
  the results of earlier versions.

  Use AUTO_UPDATE_STATISTICS. For more information see, Statistics.

  Use a time limit to determine when to update statistics. For example,
  every hour, every day, or every week.

  Use application-level information to determine when to update
  statistics. For example, every time the maximum value of an identity
  column changes by more than 10,000, or every time a bulk insert
  operation is performed.

So now you can use modification_counter column of sys.dm_db_stats_properties (Transact-SQL) for the statistics of interest:

  modification_counter

  Total number of modifications for the leading
  statistics column (the column on which the histogram is built) since
  the last time statistics were updated.


SQL Server 2016 Bad Query Plan locks up DB once a week

I'm going to answer your questions in a different order than you asked them.

4. Is this a symptom of a larger problem?

The new cardinality estimator in SQL Server 2016 could be contributing to the problem. SQL Server 2012 uses the legacy CE and you didn't experience your problem on that version. The new cardinality estimator makes different assumptions about your data and can generate different query plans for the same SQL. You may experience better performance for some queries with the legacy CE depending on your query and your data. So, some parts of your data model may not be the best match for the new CE. That's okay, but you may need to work around the new CE for now.
I would also be concerned with inconsistent query performance even with daily stats updates. One important thing to note is that gathering statistics on all tables will effectively wipe out all query plans from the cache, so you could have an issue with statistics or it could have to do with parameter sniffing. It's hard to make a determination without a lot of information about your data model, data change rate, statistics updating policies, how you're calling your code, etc. SQL Server 2016 does offer some database level settings for parameter sniffing which could be helpful, but that could affect your whole application instead of just the one problematic query.
I'll throw out an example scenario which could lead to this behavior. You said:

Some users could have 1 permission record, some, up to 20k.

Suppose you gather stats on all tables which wipes out all query plans. Depending on the factors mentioned above, if the first query of the day is against a user with just 1 permission record then SQL Server may cache a plan which works well for users with 1 record but works terribly with users with 20k records. If the first query of the day is against a user with 20k records then you might get a good plan for 20k records. When the code is run against a user with 1 record it may not be the most optimal query but it may still finish in ms. It really does sound like parameter sniffing. It explains why you don't always see the issue or why sometimes it takes hours to show up.

1. Will the new index fix the problem, making it never choose the bad plan again?

I think that one of the indexes that you added will prevent the problem because accessing the required data through the index will be cheaper than doing a clustered index scan against the table, especially when the scan cannot terminate early. Let's zoom in on the bad part of the query plan:

SQL Server estimates that only one row will be returned from the join on [Permission] and [Project]. For each row in the outer input it will do a clustered index scan on [Appointment]. All rows will be scanned from this table, but only those matching the filtering on [Start] will be returned to the join operator. Within the join operator the results are further reduced.
The query plan described above can be okay if there really is just one row sent to the outer input of the join. However, if the cardinality estimate from the join is wrong and we get, say, 1000 rows, then SQL Server will do 1000 clustered index scans on [Appointment]. The performance of the query plan is very sensitive to estimation issues.
The most direct way to never get that query plan again would be to create a covering index against the [Appointment] table. Something like an index on [ProjectId] and [Start] should do it. It looks like this is exactly the [idx_appointment_start] index that you created to address the issue. Another way to discourage SQL server from picking the query plan is to fix the cardinality estimate from the join on [Permission] and [Project]. Typical ways to do that include changing the code, updating statistics, using the legacy CE, creating multi-column statistics, giving SQL Server more information about local variables such as with a RECOMPILE hint, or materializing those rows into a temp table. Many of those techniques aren't a good approach when you need ms level response time or have to write code through an ORM.
The index that you created on [AppointmentAttendee] is not a direct way to address the problem. However, you will get multi-column statistics on the index and those statistics could discourage the bad query plan. The index may provide a more efficient way to access the data which may also discourage the bad query plan, but I don't think there's any kind of guarantee that it won't happen again just with the index on [AppointmentAttendee].

3. How do I make sure this doesn't happen to another query/plan?

I understand why you are asking this question but it's an extremely broad one. My only advice is to try to better understand the root cause of the query plan instability, to validate that you have the right indexes created for your workload, and to carefully test and monitor your workload. Microsoft has some general advice on how to deal with query plan regressions caused by the new CE in SQL Server 2016:

The recommended workflow for upgrading the query processor to the latest version of the code is:

Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)

Enable the query store on the database. For more information about enabling and using the query store, see Monitoring Performance By Using the Query Store.

Wait sufficient time to collect representative data of the workload.

Change the compatibility level of the database to 130

Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change

For cases where there are regressions, force the prior plan in the query store.

If there are query plans that fail to force or if performance is still insufficient, consider reverting the compatibility level to the prior setting and then engaging Microsoft Customer Support.

I'm not saying that you need to downgrade to SQL Server 2012 and to start over, but the general technique described may be useful for you.

2. Should I "force" the plan that works well now?

It's entirely up to you. If you believe that you have a query plan that works well for all possible input parameters, are comfortable with the query store's functionality, and want the peace of mind that comes with forcing a query plan then go for it. Forcing query plans that had regressions is part of Microsoft's recommending upgrade policy to SQL Server 2016 after all.


Query that only get one result per field

Assumptions:

if multiple employees have the same job and date, display the one with the
lowest id
an entry for web devel. was accidentally left out of the sample desired output

Sample data:

create table employees
(id          int
,employee    varchar(30)
,job         varchar(30)
,`date`      date);

insert into employees values
(1,'John',   'sysadmin',  '2017-12-01'),
(2,'Charles','programmer','2017-11-24'),
(3,'Peter',  'programmer','2017-11-29'),
(4,'Chris',  'g.designer','2017-06-30'),
(5,'Paul',   'web devel.','2017-10-08'),
(6,'Maria',  'g.designer','2017-11-05'),  <=== same job/date as 8/Bob
(7,'Abigail','programmer','2017-09-24'),
(8,'Bob'    ,'g.designer','2017-11-05');  <=== same job/date as 6/Maria

We'll use a derived table to order the data by job, date and id, and then with the help of some variables (@jname, @jcount) we'll find a single employee for each unique job who has the max(date):

select e.id,
       e.employee,
       e.job,
       e.`date`

      /* initialize our variables

          @jname  = keep track of previous job name
          @jcount = keep count of # records with same job name */

from  (select @jname  := '',
              @jcount := 0 ) v

join  (select /* if we've got a new job name, set @jcount = 1, else increment by 1,
                 and since we're ordering by 'date desc', @jcount = 1 = max(date)  */

              @jcount := if(job != @jname, 1, @jcount + 1) as jcount,

              /* save current job name */
              @jname  := job,

              id,
              employee,
              job,
              `date`

       from   employees

       order by job,
                `date` desc,
                id) e           /* determine which employee to display if tie for job/max(date) */

where  e.jcount = 1  /* only display our records where jcount = 1 = max(date) for a given job name */

order by id;

id employee job        date
-- -------- ---------- ----------
1  John     sysadmin   2017-12-01
3  Peter    programmer 2017-11-29
5  Paul     web devel. 2017-10-08
6  Maria    g.designer 2017-11-05

NOTE: While Bob and Maria have the same job/date values (g.designer/2017-11-05), Maria is displayed because she has the lowest id value.

Here's a db-fiddle


Error in login in with root user in new mysql server

Please run the following:
mysql -uroot -pPa$$w0rd -ANe"SELECT USER() ReqUserLogin,CURRENT_USER() AllowedUserLogin;"
mysql -uroot -pPa$$w0rd -ANe"SELECT user,host,password FROM mysql.user WHERE user='root'"

The first query will show you two things:

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

The second query will show you all root users. It is possible only one root user got the password embedded and left the other without one.
Run this query to assign the same password to all root users:
UPDATE mysql.user A INNER JOIN mysql.user B USING (user)
SET A.password=B.password WHERE A.password='' AND B.password<>'';
FLUSH PRIVILEGES;

While you are at this point, you may as well get rid of anonymous users:
DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES;

and disable public access to test databases
DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;

I wrote about this before:

Cannot drop anonymous user from mysql.user
Is this a normal set of MySQL privileges?
Limiting database security

UPDATE 2012-06-13 15:58 EDT
I think I understand the problem. Look back at your original command to create the password:
/usr/bin/mysqladmin -u root password Pa$$w0rd

In Linux, $$ stands for something. Just run echo $$ and see:
[root@... ~]# echo $$
30837
[root@... ~]# ps -ef | grep 30837
root      2354 30837  0 15:50 pts/0    00:00:00 ps -ef
root      2355 30837  0 15:50 pts/0    00:00:00 grep 30837
root     30837 30835  0 14:49 pts/0    00:00:00 -bash
[root@... ~]#

You should have done this:
/usr/bin/mysqladmin -u root password 'Pa$$w0rd'

Here is a working theory. Run that command echo $$ (let's pretend $$ is 30837)
Try to login like this:
mysql -u root password -p"Pa$$w0rd"

If this does not work, try this:
mysql -u root password -pPa30387w0rd

If this last one works, you have the password with the number embedded.
Here is how to change it to Pa$$w0rd:
mysql -u root password -pPa$$w0rd -e"SET PASSWORD = PASSWORD('Pa$$w0rd');"

Give it a Try !!!


What is the best way to include a large amount of configuration data in a SQL project?

Your intuition is right here - it's not common to have a 600 MB SQL script as part of your SSDT project in source control.

Especially in the world of distributed version control systems, like git, you're talking about potentially numerous copies of that file, on various machines, and tracking the history of it in detail.

However, there is no "built-in" way of dealing with static data other than post deploy scripts, as you've noticed:

SQL SSDT Database Projects: Source Control Lookup Data

One solution would be to create a one-time-use SSDT project, and all it has is your configuration data scripts.  Then you can produce a dacpac file from that (which is really just a compressed folder), and "reference" that dacpac in your main project.

The advantage of this approach is that you get a compressed deploy script, but it's in a format that easily fits with your existing deployment pipeline (rather than just throwing the script file into a .zip archive).

Here's what that looks like.

I have an SSDT project with the AdventureWorks sample database schema in it.  The resulting dacpac is 80 KB:

I'll create a second project, and all it contains is a Post Deploy script, which references my static config data script:

Post_Person.ContactType.sql is a 10 MB script file with a bunch of insert statements
it's configured as "Build Action" = "None" and "Copy to Output Directory" = "Do Not Copy"

This produces a dacpac file that is 34 KB.

Based on that compression ratio, which depends a lot on your data, your 600 MB could get as small as 2 MB (there is a lot of repetition in a script like this, which lends itself to good compression).

Then you can add the dacpac as a "Database Reference" in your main SSDT project:

At this point, you could discard the "InitScripts" project.  Or you could store it in a separate standalone repository, that less people will need, in case you ever need to update and regenerate the dacpac.

Note that, in the end, your build / deploy process will need to deploy both dacpac files - your "main" one, and the "InitScripts" one only on initial setup (or you could deploy it every time, and have the init scripts check for existing data before taking any action).


error: must be owner of relation USER_ACCOUNT_ID_seq

From http://www.postgresql.org/docs/9.5/static/sql-altersequence.html

  You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the sequence's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.)

Restarting the sequence on table truncation is essentially the same as altering the sequence in a separate statement

TRUNCATE TABLE "MySchema"."USER_ACCOUNT";
ALTER SEQUENCE my_seq RESTART;

So, the above restriction applies to your original TRUNCATE with RESTART statement. You must be the owner of that sequence or be a super user.

You can GRANT the sequence owner's role to the role that's going to execute your statement by doing

GRANT roleA TO roleB;

assuming that roleA owns the sequence you are trying to restart.

Once you are done with all the TRUNCATE/ALTER SEQUENCE statements you can REVOKE the sequence's owning role from roleB

REVOKE roleA FROM roleB;


MySQL won't start after upgrading to PHP 5.3

I think the problem stems from the fact that the upgrade was done trying to use the mysql schema folder of the previous version of MySQL, since the message "Can't open the mysql.plugin table" appears.

MySQL 5.1/5.5 uses a table called mysql.plugin

Prior versions (MySQL 5.1/4.x/3.x) do not use mysql.plugin.

I also noticed another oddity. The error message also said the highest file format is Barracida. The innodb_file_format of earlier versions of mysql is Antelope. You may want to export the InnoDB data in the previous version of mysql you had running. Otherwise, you may not have proper access to the InnoDB files.

Here is what you should do:

Step 1) Make a backup copy of that mysql folder. (/var/lib/mysql/mysql and /var/lib/mysql)

Step 2) You should reinstall the MySQL 5.0 version that was there before.

Step 3) Once mysql 5.0 is back up, perform a mysqldump of all databases except the mysql schema to /root/OldData.sql. Make sure you get triggers, stored procedures and views as follows:

For example, if you have 5 databases, (information_schema, db1, db2, db3, and mysql), do not include information_schema and mysql. Just mysqldump all the others.

mysqldump -hhostaddr -umyuserid -pmypassword --routines --triggers --databases db1 db2 db3 > /root/OldData.sql

Step 4) Capture the mysql grants from MySQL 5.0 as follows:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql

Step 5) service mysql stop

Step 6) mv /etc/my.cnf /etc/my.cnf1

Step 7) Clear away /var/lib/mysql (Make sure you did Step 1)

Step 8) Uninstall mysql 5.0

Step 9) Install mysql 5.5 (MySQL 5.5 does not auto-install anymore)

Step 10) mv /etc/my.cnf1 /etc/my.cnf

Step 11) service mysql start

Step 12) Login to mysql and from the mysql client run the scripts

Step 13) Load the two SQL scripts

mysql> source /root/MySQLGrants.sql
mysql> source /root/OldData.sql

You should fine from here.

Give it a Try !!!


Always name your table and column with meaningful words.  Abbreviations are subjective. It may save few bites but very difficult to comprehend.


Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

One common method is to use the VALUES clause, and CROSS APPLY the two columns aliased as a single column, then get the MIN and MAX of each.

SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest
FROM   dbo.Users AS u
CROSS APPLY ( VALUES ( u.CreationDate ), ( u.LastAccessDate )) AS x ( CombinedDate );

There are other ways of writing it, for example using UNION ALL

SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest
FROM   dbo.Users AS u
CROSS APPLY ( SELECT u.CreationDate UNION ALL SELECT u.LastAccessDate ) AS x(CombinedDate);

However, the resulting query plans seem to be the same.


How to manage very large amount of data in mysql

Use a "staging table".  Insert the unprocessed data into a separate table, then remove it when processed.

How big will the table eventually become?  A million row -- no problem.  A billion rows -- let's discuss more details.

For really high speed ingestion, see my blog.


How to assign MongoDB the complete RAM of system?

You don't need to do anything. MongoD -process will use all available memory automatically. You may not "notice" it, because mongod -process don't eat all your memory, but in reality, all free memory is used to cache data files (data itself and indexes; weight is on indexes). That's why, available memory should be bigger than active data and indexes are.

If you use wiredTiger, data and indexes stays compressed in the memory too and not only on the data-store.


Generally the fastest way to duplicate a table is simply:

CREATE TABLE table2 AS SELECT * FROM table1;

Parallel INSERTs may be faster, but only with a very fast disk subsystem (when data is interleaved on many drives). Otherwise this will be slower.

Once you're done with modifying table2, it can take the new name with:

BEGIN;
DROP TABLE table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;

The DROP TABLE command needs an exclusive lock, which affects concurrent readers in a way you may want to anticipate:

DROP will wait for any pending read on the table from other transactions to finish.
Any new transaction attempting to read that table in the meantime will be put it in wait, and then fail since the original table1 no longer exists. The error would look like "could not open relation with OID oid"

To avoid the second issue, you may rename table1  to old_table1  instead of dropping it, and then drop it only later outside of the transaction, when these readers are done with it. So the sequence above would become:

BEGIN;
ALTER TABLE table1 RENAME TO old_table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;
...
DROP TABLE old_table1;


Does Oracle Streams replicate unused columns?

That seems a large setup effort just to reclaim space from an unused column.  If you really want to recover that space (and presumably not take an outage to achieve it) you could look at DBMS_REDEFINITION

SQL> create table t ( x int primary key , y int, fatcol char(1000) );

Table created.

SQL> insert into t
  2  select rownum, rownum, rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> select bytes from user_segments where segment_name = 'T';

     BYTES
----------
  12582912

1 row selected.

SQL>
SQL> alter table t set unused column fatcol;

Table altered.

SQL>
SQL> CREATE TABLE small_t as
  2  SELECT *
  3  FROM   t
  4  where 1=0;

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'T', 'SMALL_T');

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER TABLE SMALL_T ADD (CONSTRAINT SMALL_T_PK PRIMARY KEY (x));

Table altered.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'T', 'SMALL_T');

PL/SQL procedure successfully completed.

SQL>
SQL> select bytes from user_segments where segment_name = 'T';

     BYTES
----------
    196608

1 row selected.


How to compare performances of two databases

You can not compare the performance of 2 databases by just looking at their sql plans. The same queries can have very different plans. When the versions are different all kinds of computations for the cost are different. The plan that a query follows in execution could very well be different than the plan you get from explain plan from your client.

The only way to compare the performance is to measure the execution of the queries or processes and compare those timings. You could use AWR to compare the database performance. A certain workload will generate a certain thoughput and consume more or less dbtime. If the dbtime spent and the elapsed time of your jobs is lower, you can safely say that the performance in that database is better compared to the one where the times are higher.

In 11g, with the auto tuning tasks enabled and auto accept sql profiles enabled, it could very well be that the next run already shows better timings.


Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int?

One bit per answer

If you are willing to limit the number of questions to 64, then BIGINT UNSIGNED will work somewhat easily, but limited to 64 true/false values.  Let's say q is the column being used.

q & 1<<0    -- the bit for question 1 (assuming question numbers start at 1)
q & 1<<22   -- the bit for question 23
(q & 1<<22)  = 0   "false" for q23
(q & 1<<22) != 0   "true"  for q23
(q & 0x7FF)  = 0x7ff   "true" for all of q 1-11

Etc.

The SET datatype is implemented similarly but is clumsier to use for your case since it wants you to name each bit.

If you need more than 64, then BINARY and BLOB would let you have up to 4 billion bits (in LONGBLOB), but it is much messier to extract a single bit from them.

INT UNSIGNED has only 32 bits (4 bytes), thereby not satisfying your "50".

One byte per answer

It is probably not unreasonable to have the answers in rows -- a table with (student, question_number, answer).  In this case answer could be any of these.  Each takes 1 byte and is about as easy to work on as any other.

CHAR(1) CHARACTER SET ASCII NOT NULL.  -- and insert "T"/"F" (or whatever)
TINYINT UNSIGNED NOT NULL   -- 0=false, 1=true
ENUM("F", "T") NOT NULL


SQL Server PerfMon: Compilations/sec > Batch Requests/sec

Sorry, not to disparage Thomas' advice, but please take "general rules" with a grain of salt, or just throw them out the window altogether.

Baseline.

What is normal for your system? Is the system currently responding ok?

If there is no performance issue, don't try to compare your system to some number someone plucked out of the air or potentially based off some very specific system and workload years ago, and drop everything to try to "fix" it.

Specifically, batch requests and compilations don't have a very nice and handy correlation in ALL scenarios. You need to understand your workload before you start panicking because your counters hit some threshold someone put in a post somewhere. If all of your batches consist of exactly one statement, then yes, having more compilations/sec than batch requests/sec might seem out of the ordinary (but still might not indicate a problem). In most cases, you are sending more than one statement in a batch. If this is the case - and particularly if you are using things like ORMs or a lot of highly variable dynamic SQL, where you will be suffering from a high number of compilations - I would really not be surprised to see one counter higher than the other.

Whether you need to do something about that, in that case, is a completely different problem.


Shared network location in Availability Groups configuration

That is for a backup to be taken and restored to all your secondaries, for an initial synchronization of the database. Generally that share is just for a one-time use, it will not use it again once they are in sync.

If you want to use it to sync up the databases it can just be a shared folder on the local server that all your replicas have access. The SQL Server service account for your other replicas would need read access to the share if you use it.

Alternatively if you restored the database, yourself, to the other replicas using WITH NORECOVERY you could just use the Join Only option.

The last option, simply means join it to the primary for right now and that is it. I will work on adding it and sync'n the database up to all the replicas later.


Using mysqldump to initialize slave database

When setting up replication on a new slave for the first time, you have to explicitly give it all the information it needs:
Suppose you have

Master Host is 10.20.30.40
replication user is 'repuser'
replication password is 'reppass'

First, create the replication user on the Master like this:
CREATE USER 'repuser'@'%' IDENTIFIED BY 'reppass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Next, enable replication on the Slave like this:
CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repuser',
master_password='reppass',
master_log_file='dummy',
master_log_pos=1;

Now, load the mysqldump into the Slave.
Line 23-25 of the dump file has the CHANGE MASTER TO command, but it specifies log file and position only. This will set those values on the Slave.
Finally, run these on the Slave
START SLAVE;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Replication should now be running. You can verify it as running when you see

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master:

Keep running SHOW SLAVE STATUS\G on the Slave until Seconds_Behind_Master is 0
YOUR ACTUAL QUESTION
You initially said

I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO" in the output file to set the bin log file and position for the slave. I'm assuming (as per the doc) than it should be enough for the slave to know where to start.

To reiterate what I said earlier

Line 23-25 of the dump file has the CHANGE MASTER TO command, but it specifies log file and position only. This will set those values on the Slave.

To clarify, when a Slave is initialized with all 6 options for the first time, then you can give it a CHANGE MASTER TO command with just master_log_file and master_log_pos. Using --master-data does not supply host, port, username and password. Even with MySQL 5.7, CHANGE MASTER TO supplies master_host and master_port. Still, username and password are not.


How to update mongodb collection with ssh from a shell script?

For me this works fine

ssh mongodb.domain.com "mongo --eval='db.getSiblingDB(\"testi\").empty.update({},{\$set:{key:1}},{multi:true})'"

The catch is \ before $ because otherways shell will "expand" that $set "enviroment variable", what of course don't exists.


Let me guess: you are using Alpine Linux, which uses musl as its C library, which uses ICU collations by default.

It is known that the collations in Alpine Linux don't work quite as they should, which is the cause of the problem. I am not sure if that is PostgreSQL's fault or not.

At any rate, you should use a different Linux distribution that uses glibc.


DNS lookup failed with error: '11001(No such host is known.)'

this problem was actually caused by a particular way the trusted domains US-Europe was configured, (beyond the scope of this question) however, using the scripts that you can see on the original question, then working with the sysadmins, and then finding server names that could be authenticated on both ends (source and destination) resolved the case - after altering the routers as per this example:

--targer server
USE [cola]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')
DROP ROUTE [rou_ORCARoute]

CREATE ROUTE [rou_ORCARoute]
WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,
BROKER_INSTANCE  = N'CD718202-CB34-4DCD-BADC-7800C5F2FF3D' ,  --BROKER ID OF ORCASTG
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server
GO

--source server
USE [ORCASTG]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_CAUKRoute')
DROP ROUTE [rou_CAUKRoute]

CREATE ROUTE [rou_CAUKRoute]
       WITH  SERVICE_NAME  = N'svc_receiveStatusChangeService' ,
       BROKER_INSTANCE  = N'452711F3-60CD-47E6-BFA4-3BD8C9D6AB56' ,  --BROKER_ID OF COLA
       ADDRESS = N'TCP://mytargetserver.mycompany.com:4022'--IP address of target server
GO


The $date operator is a part of the MongoDB Extended JSON spec and that's what you get as default with mongoexport but you don't want to use it in the query criteria.

Try using the ISODate() constructor instead:

db.analysisDTO.find({"signals" : {
        "$elemMatch" : {
           "date" : {
             "$gte" : new ISODate("2001-12-01T22:00:00.000Z")}}}});

The reason for the 7 minute length is likely because the entire data set is being scanned into memory to check all fields.


SELinux is preventing pg_ctl from read access on the file postgresql.conf

It looks like you changed the context of the /mnt/db/pg_data directory, but didn't apply the changes to everything underneath.

You can verify this by running ls -Z on the files in question. If you don't see 'postgresql_db_t', then the changes weren't applied.

restorecon -Rv /mnt/db/pg_data/ should set the SELinux contexts correctly. Forcing a relabel of your entire filesystem with touch ./autorelabel and a reboot would also work.

I also noticed that in your service file, you set the PGDATA variable to:

PGDATA=/mnt/db/data

instead of

PGDATA=/mnt/db/pg_data

As you did in your restorecon command, and that could cause problems as well because /mnt/db/data probably doesn't exist on your server.

Lastly, if pg_ctl doesn't have the correct SELinux context, it won't be able to read the data directory either.

So, verifying the context with ls -Z on pg_ctl and changing it if needed is also a good idea.

You can find nice documentation on these SELinux commands here:

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security-Enhanced_Linux/sect-Security-Enhanced_Linux-Working_with_SELinux-SELinux_Contexts_Labeling_Files.html

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security-Enhanced_Linux/sect-Security-Enhanced_Linux-SELinux_Contexts_Labeling_Files-Persistent_Changes_semanage_fcontext.html

Hope that helps. Good luck. =)


How do I impose a CONSTRAINT on a TEXT column in PostgreSQL 12?

Here is one method to do it:

create table test
(
  dd char NOT NULL,
  constraint dd_c check ( dd in ('a', 'b', 'c', 'd'))
);


Streaming replication setup, sane setup?

This is a really broad topic, and not one I am going to cover in detail here. One point to note however: in my experience, SR can occasionally break due to network issues between datacenters (or ever servers), so disabling log shipping isn't a good idea unless you truly don't have the bandwidth to spare between master and slave(s).


Can't connect mongo shell to Atlas cluster

@ashishkumar148, I would like to say that as per MongoDB BOL  Here , MongoDB Atlas cluster only able to connect with MongoDB 3.4 version, due to Mongo Shell with TLS/SSL support.

I have gone through you mongo shell log , where i have found out that as you are using  MongoDB shell version: 3.2.17.

I am also attaching the Sandbox MongoDB Cluster connect screen shot to you.

Note:- Before making the connection with MongoDB Atlas with MongoDB shell, make sure that you have successfully configured your IP address through Mongo Atlas IP whitelist Here .And the IP status is Active. And during the connection the User ID & Password should be used, which you have Assigned in MongoDB Atlas cluster creation time.


bcp command Incorrect syntax near '�'. Character is actually: "ä"

I believe that this is an encoding issue between the shell and bcp / SQL Server. SQL Server expects UTF-16 Little Endian, but Linux isn't using that. The default for my Linux VM is UTF-8 via en_GB.UTF-8.

<TL;DR> Use the "queryout" bcp command and specify "SELECT * FROM ..." instead of using the "out" command and simply supplying a table name.

What follows is my testing...

I got the list of available locales / encodings using:

$ locale -a

returned:

C
C.UTF-8
en_AG
en_AG.utf8
en_AU.utf8
en_BW.utf8
en_CA.utf8
en_DK.utf8
en_GB.utf8
en_HK.utf8
en_IE.utf8
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ.utf8
en_PH.utf8
en_SG.utf8
en_US.utf8
en_ZA.utf8
en_ZM
en_ZM.utf8
en_ZW.utf8
POSIX

I tried several of those options by setting:

$ export LC_CTYPE=C.UTF-8

and then trying again using:

$ export LC_ALL=C.UTF-8

Nothing seemed to make a difference. And each time I tried with various combinations of square brackets without -q and then with -q, and then no square brackets both with and without -q.

I even tried injecting the bytes that would equate to the UTF-16 LE character of ä via $'\xe4\x00' and even $'\xe4'$'\x00', but no improvements.

HOWEVER,

what did work was changing the bcp command from out to instead be queryout, and then changing the table name to be part of a SELECT statement (I removed the -r ~ switch only here to make the command line not scroll horizontally, but it was in my testing). I created the table in [tempdb] and ran the following:

bcp "SELECT * FROM tempdb.dbo.[Täble_Name]" queryout tab -c -k -S127.0.0.1 -Usa -Ppass

No problems there. But interestingly enough, I changed the accented ä to a non-accented a:

bcp "SELECT * FROM tempdb.dbo.[Table_Name]" queryout tab -c -k  -S127.0.0.1 -Usa -Ppass

and received the following error:

  SQLState = S1000, NativeError = 0
  Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

That is an error from bcp and must be referring to the meta-data of tempdb since the only column in my test table uses the INT datatype.

Now, my instance-level Collation is accent-sensitive, so I wasn't really expecting the non-accented a to work (though I did expect an "invalid object" error). So, in order to test accent-insensitivity, I created a new Database with a Collation of Latin1_General_100_CI_AI_KS_WS_CS, create that same table in the new DB, and added a few rows. I then ran the following two tests:

bcp "SELECT * FROM ImportTest.dbo.[Täble_Name]" queryout tab -c -k  -S127.0.0.1 -Usa -Ppass

bcp "SELECT * FROM ImportTest.dbo.[Table_Name]" queryout tab -c -k  -S127.0.0.1 -Usa -Ppass

and both worked!

Going back to the initial bcp command of just specifying the table name instead of a query, I was able to get ImportTest.dbo.[Table_Name] and ImportTest.dbo.Table_Name to work. However, I was still not able to get any combination of ImportTest.dbo.[Täble_Name] to work; all variations got the same errors as before.


Why does the concatenation order change with a User-Defined Table Type?

The order changes because the order of the tables involved in the cross join has changed. In one plan @ValueTable is the outer (driving) table, in the other @ColumnNames is the outer table.

for each row in @ColumnNames
    for each row in @ValueTable
    concatenate the result
    //produces Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

for each row in @ValueTable
    for each row in @ColumnNames
    concatenate the result
    //produces "Forename - Ellis, Surname - Ellis, Forename - Ali, Surname - Ali,"

Order is never guaranteed unless specified in the query, so if consistent order is important you should add a stable order by clause defining order for both tables inside the STRING_AGG definition.

SELECT CONCAT(STRING_AGG(CONCAT([ColumnName], ' - ',  [sc].[Value]), ', ')
                        WITHIN GROUP (ORDER BY [ColumnName],  [sc].[Value])  , ', ') -- QueryLineA
FROM        @ValueTable AS [sc]
INNER JOIN  @ColumnNames ON 1=1


Table for optional parent/child relationship

The solution you outlined is one valid option - assuming that an item can only belong to a single person at any given time.

In PostgreSQL you can enforce mutual exclusion between the two fk columns with a simple CHECK constraint:

  either a Parent or Child must exists

... you can add a simple CHECK constraint:

CHECK (a IS NOT NULL OR b IS NOT NULL)

Would demand at least one NOT NULL column - but also allow that both parent_id and child_id exist. If you want to disallow that, too, make it:

CHECK (a IS NOT NULL AND b IS NULL OR b IS NOT NULL AND a IS NULL)


Using Solr/Lucene for searching non-text tables?

Will/can Solr/Lucene searches be faster than PostgreSQL even if no
  full-text search is involved?

Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really.

Solr is a search engine. PostgreSQL is a relational database engine.

Solr is built from the ground up to do one thing well, search. It cannot do the work of a transactional database system. Solr cannot guarantee ACID compliance, it does not efficiently manage concurrency, it is not a viable candidate for OLTP workloads.

PostgreSQL is built from the ground up to do one thing well, transactional processing. It can also do a reasonable job of providing search type facilities but that is not its primary role. It does guarantee ACID compliance, it does manage concurrency, it is not a search engine.

  The 4m rows only change once a year.

In which case you probably don't need the key features of a relational database engine.

You need a search engine. Could be Solr, Lucene, ElasticSearch, Amazon Cloud Search or one of a dozens others but first and foremost it will be a search platform, not a relational database.


How to query in such a way that values becomes columns?

I think what you are looking for is called a Pivot.  I work primarily with Microsoft SQL Server, so I don't know all the inter-workings of this.

There appears to be a really well received Stack Overflow post that answers a similar question for MySQL.

https://stackoverflow.com/questions/7674786/mysql-pivot-table

Hopefully that give you what you are looking for.


MYSQL : unable to add privileges to newly created user

SO, Google answers it as :-

  Google Cloud SQL does not support SUPER privileges which means that GRANT ALL PRIVILEGES statements will not work. As an alternative, you can use GRANT ALL ON %.*.

They have mentioned it here

Thanks for helping anyways :)


mongodb - "replication oplog window" possibly reduced due to profiling - when profiling disabled no change?

OK, i think i know the answer. The OPLOG collection is a capped collection. It overwrites over time. The profile level was set to 2 for a short period of time logging all operations. I guess it will take time to overwrite these operations on a capped collection again and as a result increase the op log window.

Would be interested in anyone elses take on this issue.

Thank you.


What is best practice for changing the compatibility level - before or after the SP2 and cumulative updates are installed?

SQL Server 2016 SP1 is out-of-support so you should upgrade to supported service pack first.
Then the best practices for altering the database compatibility level are documented here: Change the Database Compatibility Level and use the Query Store


An error occurred on the always on dashboard

In short

The issue is due to the dashboard executing queries referencing the required_copies_to_commit column from the master.sys.availability_groups dmv that was changed to required_synchronized_secondaries_to_commit.

The solution is to upgrade SSMS (testing done on the 17.4 version).

SSMS 17.0

When retrying this on a machine with SSMS 17.0 installed

The same error occurs:

  invalid column name required_copies_to_commit

This is due to the query being different when opening the dashboard on 17.0:

      select * into #tmpag_availability_groups from master.sys.availability_groups
    ...
    SELECT
    CAST(
            serverproperty(N'Servername')
           AS sysname) AS [Server_Name],
    AG.name AS [Name],
    ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
    ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
    ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
    AR2.replica_metadata_id AS [ID],
    ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
    ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
    AG.group_id AS [UniqueId],
    ISNULL(AG.basic_features, 0) AS [BasicAvailabilityGroup],
    ISNULL(AG.db_failover, 0) AS [DatabaseHealthTrigger],
    ISNULL(AG.dtc_support, 0) AS [DtcSupportEnabled],
    ISNULL(AG.is_distributed, 1) AS [IsDistributedAvailabilityGroup],
    ISNULL(AG.cluster_type, 0) AS [ClusterType],
    ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit]
    FROM
    #tmpag_availability_groups AS AG
    LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
    INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
    INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
    ORDER BY
    [Name] ASC
   ...

A column that no longer exists is used

ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit]

Instead of the correct column on newer versions of SSMS:

ISNULL(AG.required_synchronized_secondaries_to_commit, 0) AS [RequiredSynchronizedSecondariesToCommit]

SSMS 17.4

Disclaimer: versions 17.1, 17.2 and 17.3 might also show the correct dashboard. I did not test these yet

When tracing the queries being run by ssms (17.4 or higher) when the dashboard correctly opens, this query runs

select * into #tmpag_availability_groups from master.sys.availability_groups
        ...
SELECT
    CAST(
            serverproperty(N'Servername')
           AS sysname) AS [Server_Name],
    AG.name AS [Name],
    ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
    ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
    ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
    AR2.replica_metadata_id AS [ID],
    ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
    ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
    AG.group_id AS [UniqueId],
    CAST(ISNULL(AG.basic_features, 0) AS bit) AS [BasicAvailabilityGroup],
    CAST(ISNULL(AG.db_failover, 0) AS bit) AS [DatabaseHealthTrigger],
    CAST(ISNULL(AG.dtc_support, 0) AS bit) AS [DtcSupportEnabled],
    CAST(ISNULL(AG.is_distributed, 0) AS bit) AS [IsDistributedAvailabilityGroup],
    ISNULL(AG.cluster_type, 0) AS [ClusterType],
    ISNULL(AG.required_synchronized_secondaries_to_commit, 0) AS [RequiredSynchronizedSecondariesToCommit]
    FROM
    #tmpag_availability_groups AS AG
    LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
    INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
    INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
    ORDER BY
    [Name] ASC
...

 select * into #tmpag_availability_groups from master.sys.availability_groups

SSMS version

(17.4)

As a side note, some of these columns have not been added to the sys.availability_groups dmv reference

In the end, the dashboard opens correctly.

Due to the correct columnname being used

required_synchronized_secondaries_to_commit

The solution

You have to upgrade to a higher version (17.4 worked for me) of SSMS to get the correct and existing DMV columns in the query.


MySQL GROUP_CONCAT Query, is there a better way?

Don't you need to group the inner stuff by ED as well?

I think the inner 2 selects can be combined:

( SELECT  `ET`, `STATUS`, `LS`,
          CONCAT(`STATUS`, ' (', COUNT(*), ') ') AS cnt2
    FROM  actest
    GROUP BY  `ED`, `LS`, `STATUS`
) as b

To get the "total count", add an expression that has a correlated subquery:

( SELECT COUNT(*) FROM actest WHERE LS = b.LS AND DATE(ET) = b.ED )  AS TotalCount,

Putting it all together... (I'm not sure I got it right.)

SELECT  DATE(`ET`) AS ED,
        `LS`,
        ( SELECT  COUNT(*)
            FROM  actest
            WHERE  LS = b.LS
              AND  DATE(ET) = b.ED ) AS TotalCount,
        GROUP_CONCAT(DISTINCT(cnt2) ORDER BY  cnt2 SEPARATOR ', ') AS STATUSES
    FROM
      ( SELECT  DATE(ET) AS ED,
                `STATUS`,
                `LS`,
                CONCAT(`STATUS`, ' (', COUNT(*), ') ') AS cnt2
            FROM  actest
            GROUP BY  DATE(ET), `LS`, `STATUS`
      ) as b
    WHERE  ET >= CURDATE() - INTERVAL 7 DAY
      AND  ET >  CURDATE() - INTERVAL 0 DAY
      AND  `STATUS` > 0
    GROUP BY  `ED`, `LS`;

(I simplified the date expression, and showed how to get the 7 days leading up to midnight this morning.)
(I suspect I mangled some details; check carefully.)


PostgreSQL: relation would be inherited from more than once

The error indicates, "the table is already inherited with same or other parent table.

Here is an example:

Create three tables Table_A, Table_B & Table_C and insert similar values to all of th three tables.

    CREATE TABLE Table_A
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_A(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_A(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_A(EmpId,EName)
    VALUES(3, 'Alok');

    CREATE TABLE Table_B
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_B(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_B(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_B(EmpId,EName)
    VALUES(3, 'Alok');

    CREATE TABLE Table_C
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_C(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_C(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_C(EmpId,EName)
    VALUES(3, 'Alok');

Then execute ALTER statement to add inheritance. For both alter statements executed successfully.

    ALTER TABLE PUBLIC.Table_A INHERIT PUBLIC.Table_B;

    Result:
    ALTER TABLE
    Query returned successfully in 132 msec.

    ALTER TABLE PUBLIC.Table_A INHERIT PUBLIC.Table_C;

    Result:
    ALTER TABLE
    Query returned successfully in 137 msec.

Now execute the same ALTER statement again and it'll result similar error.

    ERROR:  relation "table_c" would be inherited from more than once
    SQL state: 42P07

You can use following query to find inherited tables.

SELECT
    A.*, c.relname AS child, C.relname AS parent
FROM
    pg_inherits A
INNER JOIN  pg_class AS B ON (A.inhrelid=B.oid)
INNER JOIN pg_class AS C ON (A.inhparent=C.oid);


Filter on join condition and performance

The difference is your second Execution Plan is using an Index Seek (which is typically a more performant operation) on your Customer table:

This is because your additional filter results in a cardinality that returns much less records and makes an Index Seek operation more applicable as opposed to an Index Scan. Notice the difference in thicknesses between the arrows in your first Execution Plan and the second one. There's a lot less rows being returned in the second plan because of your second query's JOIN predicate helping filter down the data upfront.
This also results in a more efficient operation being used to JOIN the two Tables together, a Nested Loops operator instead of Merge Join.
To answer your last question, this is neither a coincidence nor always guaranteed to be the case, Execution Plans and what the SQL Engine does under the hood is fairly complex, and therefor testing and comparing plans and results is the best way to understand what's going because speculating for a given scenario is difficult and varies from one scenario to the next.
In this case it just worked out that the filter you chose reduced enough records from the User table upfront, that the INNER JOIN to the Customer table was a lot smaller and allowed it to use an Index Seek operation which is more performant (and again also resulted in the JOIN operation to be something more efficient too, a Nested Loops operation).
You can see the reduction in records to JOIN to in your Customer table by looking at the "Estimated Number of Rows" (in this case, you should use the Actual Execution Plan instead of the Estimated Execution Plan when possible) property when you highlight over the Index Scan operation on "OSUSR_6VB_MSS_CUSTOMER" in the first Execution Plan vs the Index Seek operation in the second Execution Plan, went from 31,877 rows to approximately 472 rows:


Whether you use a backup or a snapshot, you need to routinely pre-stage the data needed for reinitialization near the subscriber.  The goal is to always  have a backup/snapshot that is within the Distribution Retention Period, and close to the subscriber.

Also whenever you are using a snapshot to initialize a remote subscriber, it's better to copy the snapshot local and use an Alternate Snapshot Location.

Also note that if you have a single subscriber database with multiple subscriptions, you can't initialize from a backup.


How can I add another .ibd file to my existing table when the memory space is full?

You have the table is full error?

Check you are not out of disk space?

innodb_file_per_tablespace will affect tables newly created.

ALTER TABLE xxx FORCE will cause the table to be recreated in its new tablespace and the old one removed.