Database design for bigger tracking table

Honestly, 912 million records may seem daunting but it's nothing that regular indexing won't be able to handle, especially since your main query is to return only the current day's prayer types for the current user which is a very small amount of data. I've managed Tables with 10s of billions of rows in them and proper indexing allowed us to return small sets of data in milliseconds runtime, on very modest hardware. And I'm sure Tables in the 100s of billions would've been fine as well. Note this was using SQL Server instead of MySQL but the principals are the same on how indexing works in regards to storing and fetching data.
At your current rate (no user growth) for example, you wouldn't hit 100 billion rows for about 100 years. If you're able to implement Akina's suggestion then you'll be able to cut the amount of data down by 80% too. So that 100 billion rows now becomes only 20 billion in 100 years. (In other words, it withstands the test of time. :) If you're able to archive data after X number of years you can also alleviate the data load of your Table.
Finally in regards to indexing I think to support a query that will select all prayer type records for a given user only on a given day, you should create the index on user_id, date (and depending on which MySQL engine you're using include type_id if it's not by default) for best indexing.


how do I use sa to access local sql server database with jdbc

You should not be connecting any application to your database installation using the installation owner account ("sa").  Instead, create an account dedicated for the use of your Application and grant that account the right privileges.
Why?
Always keep the biggest and best tools in the box for yourself, because it's your job to clean up the "mess" made by everyone (and everything) else!
If an application connects with an "overpowered" account, like "sa"' and that application gets hacked, then your database can be wrecked in all sorts of nasty, painful and expensive to recover ways!
Save yourself the grief.


What is the difference between XtraBackup and rsync?

rsync is just a brute force byte-by-byte copy of data from place to another. You would have perform multiple rsyncs until the last rsync, which is very quick. Then, you would do a full shutdown of MySQL and perform one more rsync.

XtraBackup is a comprehensive tool that behaves like rsync but has directed purpose in life. It can start off copying all InnoDB data and tablespaces. It has the ability to create checkpoints internally and perform in-place an InnoDB crash recovery to help get perfect point-in-time backup. XtraBackup also has an extra feature which allows for the creation of incremental backups. Another additional feature is the creation of InnoDB log files, also built by an in-place crash recovery. There is also some wrapper software to provide frozen copying of MyISAM tables as well.

Both methods are great. XtraBackup simply implements InnoDB transactional features into most of the initial copying. Xtrabackup creates files that can dropped into an established MySQL infrastructure. Fair to say, XtraBackup provides instrumentational backup and materialized, very usable InnoDB files.

Using rsync forces you to manage the copy process multiple times and cap it off with a mysql shutdown to require one intervention of rsync.

Using one over the other may be a personal preference. One thing must be acknowledged: XtraBackup makes backups that are somewhat bigger that the data that was copied. I leave the choice of method to the reader of this question.

I prefer rsync because it has simplicity of use, I can determine the specific point in time before the process begins, I also have total control as to locking mysqld or shutting it down, when such control can be performed, and in whatever order I designate.

Both backups styles have one thing in common : With XtraBackup, the actual point-in-time is a moving target until the backup process is complete and you have to trust XtraBackup (hey 200,000 downloads thus far. Facebook is one of the biggest users of it.  It has earned a lot of trust). In other words, if I start XtraBackup at midnight and the backup lasts till 2:20 AM, then the backup's actual point-in-time is 2:20 AM. Using rsync has the same moving target problem because you have to do multiple rsyncs manually and then determine when to issue a 'service mysql stop' prior to the final rsync.

These methods differ in that mysqld must be shutdown using the rsync method, whereas XtraBackup acts independently of mysqld. In fact, XtraBackup does not interact with mysqld very much at all, except when dealing with MyISAM tables via the wrapper software. XtraBackup tries to build fully functional InnoDB data and log files using InnoDB storage engine operations separate and distinct from mysqld.

Shutting down mysql is very necessary for the rsync method for a very big reason: The InnoDB Buffer Pool can hold "dirty pages", which are data and index pages (1 Page = 16KB) that have not been written back to disk. Using rsync does not catch the data changes pending in the InnoDB Buffer Pool. To speed up the shutdown of mysqld for the final rsync, please run this command:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This command will keep the number of dirty pages to a bare minimum. This command should be run about one hour before you perform the rsync method of backup. This might even help XtraBackup as well though XtraBackup does not require a mysql shutdown.


The first GA version of MySQL 5.5.8. You are using MySQL 5.5.6-rc

You need to get the latest version of MySQL 5.5


SQLite: What is the use of specifying data types?

Further to Vérace’s answer:

While SQLite doesn’t enforce storage classes as data types, you can apply stricter type-checking using typeof() in a constraint, e.g.

Some_Quantity real check (typeof(Some_Quantity) = 'real')

You do lose automatic type coercion, e.g. it would prevent the values '1' or 1 from being stored in a real column (you would have to specify it as 1.0).  However, you can allow multiple types:

Some_Quantity real check (typeof(Some_Quantity) in ('real', 'integer', 'null')

SQLite's type flexibility is also handy for allowing “out-of-band” indicator values that would otherwise not be valid for the column’s type:

Year check (typeof(Year) = 'integer' or Year in ('illegible', 'not disclosed'))


With only the pfile and datafiles, you will not be able to open the database "by the book". You can recreate the lost controlfiles with a CREATE CONTROLFILE statement (specifying the remaining datafiles), but the next step would be a recovery, using the redo logs, which you do not have.

If you want to try to salvage some data from the database, you can open it without recovering. After you have created the controlfile and your database is in MOUNT state:

alter system set "_allow_resetlogs_corruption"=true scope=spfile;
startup force mount
alter database open resetlogs;

This is not guaranteed to work, the database may remain open or crash immediately/randomly depending on the scale of inconsistency of the datafiles.


Definitely sounds like a double hop (Kerberos) issue. When you're rdp'd into the box, then the cube is only 1 hop away so I'd guess that's why it works.

Start a profiler trace on the analysis services server, then query the linked server. Check the NTUsername field in the trace. If Kerberos Delegation is failing, the NTUsername will be something like "ANONYMOUS LOGON".

From Server1, what does the following return?

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

What Delegation permissions did you set up in AD for the machines?

Does the spn match the server name you are trying to connect to? If you have alias's, cnames, etc, you'll need an spn for each that you wish to connect to via kerberos.

Also, try out the Kerberos Configuration Manager to verify your settings. This can be particularly effective for reviewing what SPNs actually exist.


Selecting rows with a foreign key OR a join table relationship

I concluded that the only / most appropriate way to achieve this is simply with a UNION. These two queries unioned together:

where user_id=123
it is related to user 123 through ThingUser


MySQL views are a terrible solution to anything because they are VEERRYYY slow and also cause lots of issues during database backup and restores (due to @DEFINER keyword).

You store the id of the current user in the application session session and always use it as a filter for any queries that are executed since its a foreign key in each table.


This is likely an artifact of the MySQL Query Cache.

You execute the SQL query, MySQL caches its result and the next execution if fast. When you run the script to insert the data in tables referenced by your query, the result cache gets invalidated and the query must be executed "for real" the next time.

From the MySQL documentation linked above:

  A query mix consisting almost entirely of a fixed set of SELECT
  statements is much more likely to benefit from enabling the cache than
  a mix in which frequent INSERT statements cause continual invalidation
  of results in the cache.


If my Data, Log and Tempdb drives are on the same LUN, are they using 1 physical drive?

To expand on Aaron's comment of:

  A LUN on a SAN is usually made up of a number of drives. Your data
  will be striped across all of them in some configuration, e.g. RAID 5
  or RAID 10. You'll have to ask your SAN administrator for more
  details; we can't possibly tell you about your physical
  implementation.

Each SAN implements this differently. For example, some can define exact RAID groups, type, etc. Some you just put disks in and they create their own internal sets and configuration - you just tell it how big of a LUN you want and where to zone it. Some SANs will automatically tier your data and you may be on many different sets of drives. Others will yet again operate completely in cache and only flush to disk like a checkpoint operation would in SQL Server.

This also doesn't take into account anything in the fabric that may be acting as yet another intermediate tier such as SVCs.

Really, the question you should be asking is, "Is one LUN allowing me the throughput I require in order to properly run this instance of SQL Server and all of the associated databases."

If there is only one LUN, most drivers for the HBAs won't multipath. This may or may not matter depending on your fabric - but most of the time it helps. Additionally we don't know the queue depth of the HBAs (though not all implementations have such a configurable item) and don't know the typical profile of IO for that server.

Just some extra items to think about :)


how to disable delete in replication Cassandra datastax

You can disable delete,insert and update using the built-in Cassandra security

CREATE USER test_user WITH PASSWORD xxxx NOSUPERUSER;
GRANT SELECT TO test_user ON my_table;

Unfortunately, it will also disable INSERT and UPDATE. Right now there is no way to disable only DELETE.

Indeed, even if you disable DELETE and allow INSERT & UPDATE, it is still possible for the user to delete data, by just inserting null or updating with null values because inserting null == DELETE in CQL semantics


Why does SQL authentication sometimes fail and how can I make it work reliably?

When your machine is not connected to the network, it uses the cached information for authentication purposes.  This is how you can take the laptop home and log on with your domain account.  If you created a login for a domain account while you weren't connected to the domain and then they tried to log in with that account, it would fail because that information doesn't exist on your laptop and it can't reach the domain to authenticate the credentials.

When you are on the same network as the domain, your machine recognizes this and tries to communicate with the domain controller on regular intervals.  When a communication failure occurs, your local machine is essentially orphaned at that time so any domain resources become unavailable until it can re-establish communication.  If you disable your network connection and log off/on and then try I would bet it works fine.  It might even work if just disable the network, I don't have a laptop to test this out on so I can't say with certainty. This is based on my understanding of Active Directory and experience so it might not be 100% accurate but it is definitely what I have observed.  Like John M suggested, you might want to use a sql login since you are having problems.


Finding all date ranges that overlap a target date range

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another vacation, as an inner join.

pseudo example:

create table rangeindex (
  vacation_id bigint,
  year int,
  week int,
  primary key (year,week,vacation_id),
  index (vacation_id))

select v2.*
    from vacation v1
    join rangeindex r1 on r1.vacation_id = v1.vacation_id
    join rangeindex r2 on r2.year = r1.year and r2.week = r1.week
    join vacation v2 on v2.vacation_id = r2.vacation_id
where v1.vacation_user = ?
    -- and the usual start/end comparisons to filter unwanted pseudo hits

Similar things are used for example spatial coordinate stuff, dividing linear coordinates to grid cells that can be indexed and scanned more easily.

Update: fixed primary key


MongoDB Replication - “Failed with No route to host”

Firewall! Go to the command line of s1-b5 and give command mongo --host 's2-b6:26052' you should have a successful login to "second" node. I guess that these two nodes are actually one machine?!? And when you use "external IP address", traffic goes thru the firewall. So, iptables-save command should show that this port 26052 is open for traffic.


How to detect a circular reference in a hierarchy table?

Here's a thought:

Add a column depth SMALLINT UNSIGNED NOT NULL DEFAULT '0'
Repeatedly bump depth to be at least 1 more than the parent:

UPDATE MyTable AS child
  JOIN MyTable AS parent ON child.ParentId = parent.Id
  SET child.depth = GREATEST(child.depth, parent.depth + 1)
  WHERE child.Id != 0;   -- the root says at depth=0
If rows affected drops to 0, you have no loops.
After awhile, any nodes in loop(s) will have large depth values.


What triggers the SSRS to generate a log file or modify a current one?

The log in question I believe is

Report Server Service Trace Log and it turned ON by default

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-http-log?view=sql-server-ver15

I figured this by checking where the log is stored in the question.

  Trace log behavior is managed in the configuration file
  ReportingServicesService.exe.config. The configuration file is found
  in the following folder path:

  \Program Files\Microsoft SQL Server\MSRS13.\Reporting
  Services\ReportServer\bin.

if you want to disable this logging you can do the below.

  DefaultTraceSwitch    Specifies the level of information that is reported
  to the ReportServerService trace log. Each level includes the
  information reported by all lower-numbered levels. Disabling tracing
  is not recommended.   Valid values are:

  0= Disables tracing. The ReportServerService log file is enabled by
  default. To turn it off, set trace level to 0.

You can tweak this value based on your requirements.

0= Disables tracing. The ReportServerService log file is enabled by default.
   To turn it off, set trace level to 0.

1= Exceptions and restarts

2= Exceptions, restarts, warnings

3= Exceptions, restarts, warnings, status messages (default)

4= Verbose mode

More info here


Difference between idx_tup_read and idx_tup_fetch on Postgres

When looking at the source code of the view, then you'll see that idx_tup_read is the result of calling pg_stat_get_tuples_returned() and idx_tup_fetch is the result of calling pg_stat_get_tuples_fetched()
The manual describes the two functions as follows:

pg_stat_get_tuples_returned(oid)
Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index
pg_stat_get_tuples_fetched(oid)
Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index


MySQL updating table with values selected from same table in one go

As per my understanding, you need to update last_purchase field with modified_at field by joining customer table and sale_order table using the conditions you mentioned in your query.

Then try the following query

UPDATE customer   AS c
  JOIN sale_order AS s
   SET c.last_purchase = s.modified_at
 WHERE c.customer_id = s.customer_id
   AND c.country = s.country
   AND s.created_at > c.last_purchase;


Copy large PostgreSQL table to multiple csv files with limited row number

If the records don't have a embedded newlines in text fields, so that there is a strict [one line = one record] mapping, you may pass the output of \copy csv in psql to the Unix command split. For instance:

\copy (your query here) TO program 'split --lines 1000000' (format csv)

See the options of split to change the format of the names of output files or the destination directory.
It can also be used server-side with COPY instead of \copy if you're superuser.

If the records may have embedded newlines, it's more complicated because with the above method, a record might span two consecutive files, making each file an invalid CSV file in isolation. For instance:

\copy (select E'1st line\n2nd line') TO program 'split --lines 1' (format csv)

would produce two files

xaa: "1st line

and

xab: 2nd line"

If the goal is to concatenate the files back into a single file to process it, then it doesn't matter, but if they must be processed individually, a different method should be considered.

It's possible in psql, but a bit involved (as opposed to writing it in a programming language). As of PostgreSQL 12, csv is a native output format in psql so a cursor on the query might be used with FETCH 1000000 statements doing the actual cut-and-retrieve. The skeleton for a piece of script that should work would look like this:

\pset format csv
\pset tuples_only on
begin;
declare c cursor for your-query-here;
fetch 1000000  from c \g file1.csv
fetch 1000000  from c \g file2.csv
... as many times as necessary...
close c;
end;

Because there is no looping construct in psql and assuming you don't know in advance how many fetch steps are needed, you have to generate that piece of script in a previous step that computes the count(*) of the resultset and emits (count(*)+NR-1)/NR times fetch NR from... commands to numbered files like above, where NR is your number of records per file.


Autovacuum and VACUUM behave differently. They do both obtain the same lock, but the autovacuum will get cancelled if it is detected to be blocking something else.  DROP or TRUNCATE will be blocked, but in the case of autovacuum it will only for at most 1 second (or whatever "deadlock_timeout" is set to).

An exception to this is an autovacuum "to prevent wraparound".  Those will not get cancelled, which can be a major problem.  Especially in the case of DROP or TRUNCATE, since allowing those to happen would obviate the need for the wraparound vacuum in the first place.

(For lovers of technical details: the lock mode is set in line 1590 of src/backend/commands/vacuum.c, it doesn't depend on auto or manual (on 12dev HEAD) . The cancellation is done in src/backend/storage/lmgr/proc.c based on a flag set in src/backend/storage/lmgr/deadlock.c. It is done there because the code that checks for deadlocks is a convenient place to check for being blocked by autovac.)


Is Fuzzy Matching available in SQL Server 2019 Standard?

Fuzzy Matching is still enterprise only
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-features-supported-by-the-editions-of-sql-server?view=sql-server-ver15


Using an index when leading column is not in the predicate

In my case the leading column in the index is unique ... it strikes me it has all the information to know its unique.

It does not. That primary key does not guarantee that either a or b are unique, just that all combinations of a & b are. There could be many rows for which b = 1 is true, maybe all of them, maybe none of them. When you search for a specific combination of a and b then it can use the index to do a simple seek.
With that table definition, your first query is like asking "find all words in the dictionary where the second letter is 'a'". You can't answer that with a single seek. Your second query is like asking for "words starting 'aa'" which is easy to answer with that index.
Note though that some database systems are able to perform a skip-search to speed up the first query, which would help here, essentially looking for a=1 and b=1 then a=2 and b=1 then a=3 and b=1 and so on (it isn't quite this but as near to as makes little odds). If operation is supported it may be used if the datatype of a and the selectivity indicated by the index stats suggest it might be appropriate. No version of SQL server supports this operation though. Oracle does unless you have a really old version (> a decade or two), as does SQLite, IIRC both call the operation a "skip scan". Further note that in every circumstance where a skip-scan is better than a full index scan, having an index on the second column to use would be more efficient, often significantly so, meaning that while the feature might make some queries better in databases that are not optimised for them, if you expect such queries you should optimise your table(s) for them by having the extra index even if your DBMS supports skip-searches.

it uses some other NC index

To explain why that particular index is selected and used instead of any other, we would need to know how that index and all the other indexes are defined, and perhaps see the query plan. Also you don't say how the index is used - I assume it was scanned rather than being used for seeks.


Are PostgreSQL clusters and servers the same thing?

tl;dr

Yes.

Confusion arises from the official terms used in the SQL standard versus more commonly used terminology.

Cluster = database server
Catalog = a database
Schema = a namespace, a grouping of tables

Computer > Cluster(s) > Database(s)/Catalog(s) > Schema(s) > Table(s)

See my Answer to a similar question on Stack Overflow for more info.

Briefly here…

An installation of Postgres on a computer is called a cluster, per Section 4.13 of SQL-92 titled "Clusters of catalogs".

That cluster has one or more databases, each database called a catalog per the SQL standard. Each database contains one or more schemas, each schema being a namespace and security boundary for a collection of tables.

You can have more than one cluster on a computer. This is typically done for different versions of Postgres. For example, you could have Postgres versions 9.5, 9.6, and 10 all installed, each listening on a different port for incoming connections. That would be three clusters in that computer, each containing catalogs that contain schema that contain tables that contain your rows of data defined by columns.


Netezza is an appliance that is designed to excel at large table scans, so that's why you're getting such fast results on that system.

For your SQL Server, you can greatly speed up the row count by querying from the sys.dm_db_partition_stats DMV.

SELECT s.name AS [Schema], o.name AS [Table], SUM(p.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats p JOIN sys.objects o
ON p.object_id = o.object_id JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE p.index_id < 2
AND o.object_id = object_id('MyTable')
GROUP BY o.name, s.name;

In a high transaction environment, this DMV is not guaranteed to be 100% accurate.  But from your question, it sounds like you are just doing row counts to verify each table after your migration, so this query should work for you.


Can't add a node to PXC due to SST error

Finally I figured out that AppArmor was blocking the SST. The most resources about setting up the Cluster I read were about SE Linux.

I put the mysql profile into the complain mode:
https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql


SQL 2008 Cluster Install, "Cluster shared disk Availability Check"

Well, it looks like when the first sql server install ran, it also configured the file server services, which were still trying to hold the disk and the network name.  Odd thing is, the disks where all showing 0 dependencies.  This probably could have been fixed if we opened a case with microsoft, maybe cleaned up some registry entries, etc, but the guys from out windows team wanted to just rebuild from bare metal, as it would be quicker and easier.  I was on board with that, so that is the solution I am implementing


Implications of a second index on a timestamp field in a sequential table

Implication is that you need ~1144Mb extra storage to index column time for ~100M rows...

Because the InnoDB engine wiil store the data off an PRIMARY or UNIQUE index within an non PRIMARY or UNIQUE index, as result your secondary index will be become larger

How much larger?? you can calculate it with this formula

int = 4 bytes
datetime = 8 bytes

100000000 records * (4 + 8 bytes) =
100000000 * 12 bytes ~ 1200000000 bytes ( 1144.40918 Mb ) extra storage (note index records/page overhead are not in the calculation)

An larger index size will slow down inserts, delete and only updates when you update an value whats indexed..
An larger index size in thoery can slow down selects because off the InnoDB index page off 16K (read http://www.ovaistariq.net/733/)
But still it depends on innodb configuration and cached data within the innodb buffer pool..

Or maybe you can use your approach by using an lookup table

CREATE TABLE tracker_snapshot_lookup (
    tracker_date DATE NOT NULL
  , tracker_snapshot_start_id INT UNSIGNED NOT NULL
  , tracker_snapshot_end_id INT UNSIGNED NOT NULL

  , PRIMARY KEY(tracker_date)
  --   Covering index below is overkill...
  -- , PRIMARY KEY(tracker_date, tracker_snapshot_start_id, tracker_snapshot_end_id)
) ENGINE = InnoDB;

insert into tracker_snapshot_lookup values('2013-11-13', 1, 10000);
insert into tracker_snapshot_lookup values('2013-11-14', 10001, 20000);

If you use an JOIN or deliverd table the MySQL optimzer can use in worse case

1 index key (Random disk I/O) lookup on tracker_snapshot_lookup.date (assuming with WHERE tracker_date = '2013-11-13' )
1 table (Random disk I/O) record key for tracker_snapshot_start_id and tracker_snapshot_end_id (not necessary when you make it an covering index)

Based on tracker_snapshot_start_id and tracker_snapshot_end_id MySQL will most likly choose an range scan (sequential disk I/O what is low costing with I/O waittime) on the tracker_snapshot table.

Your savings

DATE            3 bytes
INT NOT NULL    4 bytes

So in one year you lose on storage...

Table data

356 days * (3 + 4 bytes)
356 * 12 = 4272 bytes ( 0.004 Mb )

Index data

356 days * (3 bytes) = 1068 bytes ( 0.001 Mb )

It's magic because you use that ~1143Mb storage space for more important data


oracle regexp_replace to remove duplicate duplicate strings and double separators

Would the following be acceptable (not a REGEXP_REPLACE, but maybe easier to understand)?

Test table/data:

create table t as
select 'row1' c1 , 'value1' c2 from dual union all
select 'row2' ,  'value2' from dual union all
select 'row3' ,  null from dual union all
select 'row4' ,  'value2' from dual union all
select 'row5' ,  'value3' from dual union all
select 'row6' ,  'value1' from dual union all
select 'row7' ,  null from dual;

select * from t;

C1      C2
row1    value1
row2    value2
row3    null
row4    value2
row5    value3
row6    value1
row7    null

Query:

select
  listagg(c2 , ',') within group ( order by c2 )
from (
  select distinct c2
  from t
  where c2 is not null
);

result
value1,value2,value3

Dbfiddle here.


Altering same table across multiple databases

I think the fundamental question you need to ask yourself is how much of a guarantee you need that this has propagated to all db's.  Do you need every change to commit or roll back together?  If so you should probably write a script in a programming language of your choice (Perl, Python, or the like), send the DO block there, and then use PREPARE TRANSACTION on every db connection (you'd open up one connection per db).  If all of these are successful you commit.  If any one fails, you roll back.

This is actually one very helpful application of PostgreSQL's transactional ddl in that you can guarantee that all your databases have schemas which are kept in sync.


I think „UTC Timestamp“ is not the correct term since UTC is defined in terms of Leap Seconds.

What is a more usual term here is TAI time or GPS time, both use no leap seconds and UTC is defined in terms of TAI (GPS is basically TAI starting at a different offset, so it has 19s less leap time).

The difference of UTC time to TAI time cannot be calculated (unless you have both stamps, then you just need to subtract them). Instead, tTo get the official offset at any point in the past and some time in the Future you need to lookup the official tables, this is the authoritative source: http://hpiers.obspm.fr/eop-pc/index.php

I think some timezone Information files/libraries also have that information, but have not seen any support in commercial databases.


How do I move a MySQL data directory to an external hard drive?

The new directory must be accessible by mysqld.  In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory?  Note that it can't even do a cd:  "Can't change dir".
Check for SELinux or AppArmor being in the way.


Is there a difference between running the source command from the mysql command line client and '<' from outside it? if so, when should I use each?

They are the same, but you can use only one depending in which environment you are.

for example when you are in DOS/ linux prompt you can only run mysql.exe

in Mysql command  mode you only can use source.

so there is no need to change the environments to import the sql file.


Sort results by number of matching words

Add a fulltext index on content and use "match", as in
SELECT *, match(content)
  against ('you surround' IN NATURAL LANGUAGE MODE) as score
  FROM Sentence
  Order by score desc

See this fiddle


Oracle drop database - Can images stored in RMAN catalog be retained?

It sounds like you need to set up a recovery catalog in a central database, which you can read up on how to set up a Recovery Catalog and maintain it from the Oracle Database 12c Docs.

Once you have the recovery catalog, then dropped database backup records are still maintained with their respective retention policies:

Connect to the recovery catalog:

sqlplus rman/ @catdb

Get the dbid for the database in question:

select dbid, name from rc_database order by name;

Point the recovery catalog to the database using the DBID from the previous query:

call dbms_rcvman.setdatabase(null,null,null,<DBID>,null);

List the backups for that database:

select * from rc_backup_files;


Ubuntu, postgresql, show whole running sql command

You would want to query the query column of pg_stat_activity with a query like

SELECT query
FROM pg_stat_activity
WHERE state = 'active';

This will show the first 1024 characters of the query.

If that is not enough, raise the track_activity_query_size parameter.


MonetDB ODBC driver memory allocation error on Windows

They just released new server & driver packages (7/25/2014). I had the same issue with the old drivers but with the 7/25 release it is gone.


Finding contiguous ranges in grouped data

Since MySQL doesn't support ROW_NUMBER(), You can use a variable to create a group like this.

Sample Data

CREATE TABLE `Rings` (
    ID_RingType CHAR(2),
    Number MEDIUMINT UNSIGNED,
    ID_User INT(11)
);

INSERT INTO `Rings` VALUES
  ('AA',1,1),
  ('AA',2,1),
  ('AA',3,1),
  ('AA',11,1),
  ('AA',12,1),
  ('AA',13,1),
  ('AA',14,1),
  ('AA',15,1),
  ('AB',16,1),
  ('AB',17,1),
  ('AB',18,1),
  ('AB',19,1),
  ('AB',20,2),
  ('AB',21,2),
  ('AB',22,2);

Query

SET @grp = 0;
SET @preNum = 0;

SELECT ID_User,ID_RingType,MIN(Number),MAX(Number) FROM
(
SELECT
ID_RingType,
ID_User,
Number,
@grp := CASE WHEN Number = @preNum + 1   THEN @grp ELSE @grp + 1 END grp,
@preNum := Number
FROM `Rings`
ORDER BY ID_RingType,ID_User,Number
)T
GROUP BY ID_RingType,ID_User,grp

Output

ID_User | ID_RingType | MIN  | MAX
1       | 'AA'        | 1    | 3
1       | 'AA'        | 11   | 15
1       | 'AB'        | 16   | 19
2       | 'AB'        | 20   | 22


Server Status Variables

  Connections

  The number of connection attempts (successful or not) to the MySQL server.

I.e. it is a counter which can increase only. Each attempt to connect to server, both successful and falling, increase it. Each connection, both existing and already broken, is taken into account. So this variable value can be used only for to calculate the average number of connections per some time period.

  Threads_connected

  The number of currently open connections.

It is the amount of real external connections in any state. Do not include inner server services connections (sheduler connection, for example).

  Threads_running

  The number of threads that are not sleeping.

It is the amount of real connections not in sleeping state, including inner server services connections (sheduler connection, for example).

You can see a correlation between last 2 variables while compare the output of the commands

SHOW GLOBAL STATUS
    WHERE Variable_name IN ( /* 'Connections', */
                            'Threads_connected',
                            'Threads_running');

and

SHOW PROCESSLIST;


Does a SQL Server job run on the server or the local machine?

When a SQL Server agent job is run it is run on the SQL Server machine not on your workstation.

It sounds like you don't have the drivers setup correctly on the SQL Server.

Keep in mind that if you are using a 64bit machine you'll need 64bit drivers and god only knows how DTS will handle 64bit stuff.


Try this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/America/Sao_Paulo America/Sao_Paulo

Here a sample output:

INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1767214412, 2)
,(@time_zone_id, -1206957600, 1)
,(@time_zone_id, -1191362400, 2)
,(@time_zone_id, -1175374800, 1)
,(@time_zone_id, -1159826400, 2)
,(@time_zone_id, -633819600, 1)
,(@time_zone_id, -622069200, 2)
,(@time_zone_id, -602283600, 1)
,(@time_zone_id, -591832800, 2)
,(@time_zone_id, -570747600, 1)
,(@time_zone_id, -560210400, 2)
,(@time_zone_id, -539125200, 1)
...
;
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -11188, 0, 'LMT')
,(@time_zone_id, 1, -7200, 1, 'BRST')
,(@time_zone_id, 2, -10800, 0, 'BRT')
;

From MySQL documentation

  mysql_tzinfo_to_sql tz_file tz_name

  This syntax causes mysql_tzinfo_to_sql to load a single time zone file
  tz_file that corresponds to a time zone name tz_name


Unfortunately for you, it does not work that way. Users in multiple roles are subject to the complete set of permissions from all of their roles, and deny permissions always trump grant permissions. You have to keep that in mind as you design your user and role assignments.

In this case, you might find that you don't need the DENY rule at all. A user can't do anything without explicit grant permissions, so if you remove the deny rule from your staff role they likely still won't be able to delete anything: the rule is redundant for them.


This is what foreign data wrappers are for.

There is one FDW for Oracle: https://laurenz.github.io/oracle_fdw/

A complete(?) list of available FDWs can be found in the Postgres Wiki: https://wiki.postgresql.org/wiki/FDW

See also CREATE FOREIGN TABLE


The syntax is almost correct. You need the keyword values and some commas that are missing:

with x (x, y, z) as
  (values
    (2, 5, 6),
    (6, 3, 2)
  )
select *
from x;


I believe you will have to do some XML query work to get that estimated cost.

See if this is what you are looking for:

   ;WITH XMLNAMESPACES
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT TOP 1000 st.text
        ,cp.size_in_bytes
        ,cp.plan_handle
        ,QP.query_plan
        ,n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
    WHERE cp.objtype = N'Adhoc'
        AND cp.usecounts = 1
    OPTION(RECOMPILE);

This will return the cost for individual statements inside of a batch. You might need to work in some grouping if you need the total estimated subtree cost for the entire batch.


Oracle Client vs Oracle Instant Client on Linux!

If you only need to run SQLPlus, grab "Instant Client Package - Basic" and "Instant Client Package - SQL Plus: Additional libraries and executable for running SQL Plus with Instant Client" from http://www.oracle.com/technetwork/topics/linuxsoft-082809.html & install them.

oci.dll is a windows file, so I have no idea what could be achieved by installing that on Linux.

unzip the files into a location of your choice & set LD_LIBRARY_PATH and PATH to point to that directory. If you need any further help after that, edit your question.


Can I mysqldump an entire database while using where='condition' on individual tables?

You will have to custom script each table

The --where option is designed to help mysqldump a single table

Here is a post I made back in August 15, 2011

Is it possible to mysqldump a subset of a database required to reproduce a query?

Here is an crazy example

Suppose the table is this in the myworld database:

CREATE TABLE data_for_world
(
    id int not auto_increment,
    continent varchar(16),
    primary key (id)
);
insert into data_world (continent)
values ('Asia'),('Africa'),('Eupore'),
('NorthAmerica'),('SouthAmerica'),
('Australia'),('Antarctica');

and you want to break it up into two dumps

one with continents that start with A
one with continents that do not start with A

You will need three files (one for the database schema, and two for the data)

mysqldump --no-data --database myworld > myworld_schema.sql
mysqldump --no-create-info myworld data_for_world --where="substr(continent,1,1)='A'" > data1.sql
mysqldump --no-create-info myworld data_for_world --where="substr(continent,1,1)<>'A'" > data2.sql


How to store mp3 files in MySQL database using phpMyAdmin?

In my personal opinion (however I am new to databases as well), I would look to store the relative URL,  which would be the VARCHAR from here you could store the relative path to the file (from where the script is being called from)  and load the file that way.

However it may be worth writing the SQL for your tables as well.  Just in case you need to alter or tweak your table structure in the beginning it makes it simpler to alter.  so it would look something like this:

create database music;

use music;

create table tbl_album(
    album_id int(5) not null auto_increment,
    album_name varchar(50)  not null,
    album_artist varchar(50) not null,
    album_genre varchar(50),
    ablum_year varchar(4),
    primary key(album_id)
);

create table tbl_tracks(
    track_id int(5) not null auto_increment,
    track_number int(2),
    track_name varchar(50) not null,
    album_id int(5),
    track_url varchar(100) not null,
    primary key(track_id),
    foreign key (album_id) references tbl_album(album_id)
);

PS: If you do choose to write the SQL for your tables I would recommend MySQL workbench which is free and a nice tool to use (http://mysqlworkbench.org/)


This is a précis of the relevant section in the SQL Server 2008 internals book.

SQL Server first acquires an IX lock on the index (or index partition).

The reorganization then (assuming the LOB_COMPACTION option is not OFF) compacts any LOB pages belonging to the index by moving pages out of low density uniform extents (less than 75% / 6 pages full) into other uniform extents with free space.

Then it moves onto the leaf pages of the index.

The simplified description in the book is that it first  determines the first logical page and the first physical page. If different it takes X locks out on both pages and swaps them using one additional new page as a temporary storage area. Then it does the same for the second physical/logical pair and so on.

It is somewhat more complicated than that however. The book mentions that it also looks at a sliding window of eight logically consecutive pages and determines whether rows can be moved around between these pages to allow a single page to be emptied and removed (while taking into account the index fillfactor).

As re-organization does not allocate new pages to the index it cannot reapply the fill factor if the pages are fuller than required however. Additionally no defragmentation is carried out for pages in mixed extents.


How can I get Elastic IP/host from KIbana interface

Kibana always connects to the elastic host that is mentioned within kibana.yml file that been used during installation/configuration (example value within file as follows):

elasticsearch.hosts: ["http://localhost:9200"]

In case if it configure to points to multiple elastic nodes for high availability of kibana (as follows). Requests to available hosts will be routed in a round robin fashion (top to the bottom of a list and then starting again)

elasticsearch.hosts:
 - http://es-host1:9200
 - http://es-host2:9200

To know available nodes, you may use nodes API from Kibana - Dev tools

GET /_nodes

# or Use following
GET /_cat/nodes?v


when a table trigger fires, what user does the trigger execute as

There is no need to elevate permissions with EXECUTE AS with ownership chaining. To ensure rows are inserted into the audit table only via the trigger, simply don't grant INSERT permissions on the audit table. As long as all the objects are owned by the same user (e.g. dbo), the ownership chain will be unbroken and permit the trigger to insert into the audit tables without direct permissions.


There is a special option: zero_damaged_pages=on that you can use on postgresql.conf, it is documented here.

This option will allow for a pg_dump (or pg_dump_all) that do not stop on critical errors and get as much data back as possible, but you will loose the data that cannot be read.:

(exceprt from documentation, I added the strong.

  Detection of a damaged page header normally causes PostgreSQL to
  report an error, aborting the current transaction. Setting
  zero_damaged_pages to on causes the system to instead report a
  warning, zero out the damaged page in memory, and continue processing.
  This behavior will destroy data, namely all the rows on the damaged
  page. However, it does allow you to get past the error and retrieve
  rows from any undamaged pages that might be present in the table. It
  is useful for recovering data if corruption has occurred due to a
  hardware or software error. You should generally not set this on until
  you have given up hope of recovering data from the damaged pages of a
  table. Zeroed-out pages are not forced to disk so it is recommended to
  recreate the table or the index before turning this parameter off
  again. The default setting is off, and it can only be changed by a
  superuser.


SSIS with MultiSubnet Availability Groups

If you are setting up an availability group listener across multiple subnets and plan to use static IP addresses, you need to get the static IP address of every subnet that hosts an availability replica for the availability group for which you are creating the listener. Usually, you will need to ask your network administrators for the static IP addresses.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server#Prerequisites

So your SSIS packages will point to the Listener name which will have multiple static IP addresses each on a different subnet.

https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/

Regarding failover of SSIS, you can have the SSIS Packages deployed on the Primary as well as the secondary server either on the AG Instance itself or on a different standalone instances of the servers. As part of deployment process, you can make sure that every package you deploy on primary SSIS instance is also deployed on the secondary. Either on Primary or Secondary SSIS Instance, the packages will always point to the Listener name of the AG and hence failover of AG instance should not impact the SSIS package executions.

If the server on which the primary SSIS instance resides goes down the you can enable the SSIS jobs on the Secondary standalone SSIS Instance.


I solved. The problem was that I did not have the library libsasl2-modules-gssapi-mit. Once I installed everything works fine


Database design scenario for budget (whole and daily)

The easiest solution (probably) would be:

Have one table for the whole budget for each campaign (let users have more than one ad at a time :-):

CREATE TABLE budget
(
    campaign_id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    /* Assumption: your currency doesn't have decimals; or you're using cents
       as currency unit */
    original_budget    INTEGER NOT NULL,
    available_budget   INTEGER NOT NULL,
    daily_budget_limit INTEGER NOT NULL,

    -- Security checks
    CHECK(original_budget > 0),
    CHECK(original_budget >= available_budget),
    CHECK(daily_budget_limit > 0),
    CHECK(daily_budget_limit <= original_budget)
) ;

Have a second table for the budget of just today

CREATE TABLE todays_budget
(
    campaign_id INTEGER NOT NULL REFERENCES budget(campaign_id) PRIMARY KEY,
    original_daily_budget INTEGER NOT NULL,
    available_daily_budget INTEGER NOT NULL,

    CHECK(available_daily_budget >= 0)
) ;

Every day, at 00:00, perform the following operations:

Subtract the spent_budget (original_daily_budget - available_daily_budget) from the budget
DELETE everything from todays_budget
INSERT new rows to todays_budget, with both original_daily and available_daily budget equal to the min of (daily_budget_limit, available_budget)

This can be done with the following queries:

Subtract remaining from yesterday

UPDATE
    budget
SET
    available_budget = available_budget -
        (SELECT
            original_daily_budget - available_daily_budget
        FROM
            todays_budget tod
        WHERE
            tod.campaign_id = budget.campaign_id)
 WHERE
      campaign_id IN
          (SELECT
              campaign_id
          FROM
              todays_budget
          WHERE
               available_daily_budget > 0
          ) ;

Clear out everything from todays_budget

DELETE
FROM todays_budget ;

And fill it up again

INSERT INTO todays_budget
   (campaign_id, original_daily_budget, available_daily_budget)
SELECT
    campaign_id,
    CASE WHEN available_budget > daily_budget_limit then
        daily_budget_limit
    ELSE
        available_budget
    END AS original_daily_budget,
    CASE WHEN available_budget > daily_budget_limit then
        daily_budget_limit
    ELSE
        available_budget
    END AS available_daily_budget
FROM
    budget
WHERE
    available_budget > 0 ;

And very time you show an ad: Subtract one from available_daily_budget

UPDATE
    todays_budget
SET
    available_daily_budget = available_daily_budget - 1
WHERE
    campaign_id = ((the corresponding campaign_id)) ;

Notes

I'm using SQL to describe your scenario. It is possible to do the same with noSQL databses. I wouldn't. This works well under a relational model.
If using SQL, steps 1 through 3 should be wrapped in a transaction, under SET ISOLATION LEVEL SERIALIZABLE; to avoid concurrency issues. If any of the check constraints fails, the whole transaction will be aborted and you'll have to retry it as a whole.
I've tried to use SQL Standard as much as possible, specific databases could allow for (slightly) simpler queries.
I am ignoring very many details that should be taking into consideration in a realistic situation.


Explode-Implode

In the first derived table (voc) you have what call the "explode-implode" syndrone.  First the JOINs generate a lot of rows, then the GROUP BY whittles it back to a few rows.

Sometimes the solution is to turn a JOIN into a subquery:

SELECT  ...,
        b.foo
    FROM a
    LEFT JOIN b
    GROUP BY a.id

-->

SELECT  ...,
        ( SELECT foo FROM b WHERE ... = a... ) AS foo
    FROM a

Note that the GROUP BY goes away.  This example works especially well for LEFT JOIN because the "optional" nature of foo is preserved.

(The GROUP BY in cc is not the same situation.)

Derived JOIN Derived

MySQL is terrible (pre-5.6) or poor (5.6+) at dealing with JOINing two subqueries (voc and cc).

Plan A:  Turn one of those subqueries into a JOIN with the other.

Plan B:  Put one of the subqueries into a TEMPORARY TABLE and give it an appropriate index: (user_id).

Indexes

In the absence of SHOW CREATE TABLE, must do some guessing...

vos: INDEX(type, status, best_id)
b:   INDEX(date)
c:   INDEX(calltype, start, duration, user_id)

And change COUNT(id) to COUNT(*).

Note:  start BETWEEN '2017-02-04' AND '2017-05-04' is "inclusive".  Consider changing to

    start >= '2017-02-04'
AND start  < '2017-02-04' + INTERVAL 3 MONTH

Please provide EXPLAIN SELECT ... after adding those indexes.

I see a possible divide-by-zero; maybe you want

IFNULL(voc.bestvastonbep_count / cc.call_count, 0)


Cannot reset MySQL default root password after initial installation

Restart the service without authentication

Ubuntu/Debian

service mysql stop
service mysql start --skip-grant-tables --skip-networking

Other sysv

If you're on a sysv Linux use this

/etc/init.d/mysqld stop
/etc/init.d/mysqld start --skip-grant-tables --skip-networking &

Resetting

Then you need to login as root

mysql -u root -p

Then when logged in run

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Difference between other guides

There are a lot of "guides" on this, for clarity,

Some guides use the older SET PASSWORD syntax, that's pre 5-7.6, don't use that anymore.
Some guides just don't use --skip-networking, that's unsafe.
Some guides don't connect with the -u root that's more error-prone.


Table Diagram and Access File of Contact Database for Church

The design looks OK to me, though I wouldn't store phone numbers as INTEGERs, because in reality they are character sequences.
Since you want to track marriages of members, may be that should the parent for marriages, and not contacts.
If a member passes away, you may still want to maintain his/her record with a flag, instead of deleting it. This will preserve marriage and reference records, as well as emergency contacts. To accommodate that you'd want to add the membership end date and the termination reason attributes.


How to debug database server freeze (not accepting connections, timeouts etc)

So this question is how would you go about finding the problem here? What commands or steps would you run to get more info?

Monitoring Tool

Do you have a monitoring tool?  If so, check and see what the wait stats are / were during (or at least leading up to) the incident.

Logging Queries and Waits

If you don't have a monitoring tool, I would recommend setting up SQL Server Agent jobs to log information about wait stats and active queries to a table for later analysis.  There are a number of ways to do this, such as:

How to Log Activity Using sp_whoisactive (for active queries)
How to Log Wait Stats to Table with sp_BlitzFirst (for wait stats)
Wait statistics, or please tell me where it hurts (great overview of your top waits)

DAC

While the problem is occurring, you could connect to SQL Server using the dedicated admin connection, and then run sp_WhoIsActive to see exactly what is running and slowing things down.  Despite not accepting connections from other sources, you should still be able to connect this way.

  When I say it hangs, I mean that nothing goes through on any database. The whole DB server just stops working. The OS and server is fine though. Its just the SQL Server instance.

Blocking

This sounds very much like you're experiencing a long blocking chain that never gets resolved - possibly due to long-running queries, but it could also be due to issues with replica synchronization.  Keep an eye out for LCK_* waits (for the blocking chains).

Slow Synchronous Commits

You mentioned "always on" and if by this you mean Availability Groups, then look out for HADR_SYNC_COMMIT (for the replica synchronization issues).  If the secondary gets overloaded or overly busy, throughput could stall on the primary while waiting for acknowledgements from the secondary.

Poison Waits

It's also possible you're experiencing "poison waits."  Look carefully for those waits in the wait info you start logging (specifically RESOURCE_SEMAPHORE* and THREADPOOL).  This seems less likely to me though, as it probably shouldn't be going on for long periods of time.

  Btw, I am not a DBA but I dont quite get why SQL Server doesnt automatically failover since nothing works on master

This means the server is still responding to "are you up?" checks from the cluster manager, likely because the problem is blocking-related, which is "expected behavior" from SQL Server.  Things are running, they're just not running well.

Additionally, depending on your setup, you might never get automatic failover (if, for example, you have an availability group configured with only an async replica as the secondary).


Why is an unindexed range operator (<@) faster than using BETWEEN with an index?

First, the index scan is slower than necessary, because your work_mem is not big enough to contain the bitmap. Increase it until you get no more “lossy” heap blocks during the bitmap heap scan.
The second plan is faster than the first because it uses two parallel workers (which the other plan cannot). But it uses way more resources: it keeps three processes busy for 117837 milliseconds (plus an additional 27465 milliseconds to collect the results), while the bitmap index scan is done in 153140 milliseconds.


If you're concerned about the downtime mysqldump might cause you can look into hot backups. For example percona-xtrabackup.

Here is what I would do:

Create a slave with xtrabackup (set master_info_repository to
table if you have MySQL 5.6.2+)
Have it replicated and let it catch up
Stop the replication: stop slave
(optional) Make a backup of master info file if you have MySQL older than 5.6.2
Make a note of the position with show slave status
Make the mysqldump
Destroy the database and all the files in mysql_datadir and remove all the relay logs
Restore the database with mysql_install_db and execute the dump (make sure you have file_per_table settings)
(only on <5.6.2) If necessary restore replication (if it was in table than the dump contains the information so no need)

Then you can do the switchover and you'll have only a couple of seconds of downtime.


Order by date then Group By by Name / Type before proceeding with the rest of results

When adding fields to an ORDER BY clause, you must put them in the correct sequence. If your order by clause went from

ORDER BY lt.TimeIn DESC

to

ORDER BY lt.TimeIn DESC, emp.Emp_Lname, emp.Emp_Fname

then the result set will be sorted first by the TimeIn value, then by name for those rows that had matching TimeIn values.

Assuming that you want to sort by Last Name, then First Name, then Time In, you would use the order by:

ORDER BY emp.Emp_Lname, emp.Emp_Fname, lt.TimeIn DESC

This will sort first by the Last Name, then First Name, then within those sorted groups you would sort by Time In, giving you the results you are hoping for.


Where I can find the tnsnames.ora file for my Oracle Windows client?

At least for older releases, I'd generally just create my own. It's a plain text file but you probably need to start with an example.


Help restoring a mongodump 3.4 backup into a different instance

Answer is

mongorestore --port 27017 --authenticationDatabase admin -u my_admin_user -p password --gzip /root/mongodb_backup_full_data_dump/

So, you just tell the directory (basedir) where "all" backups are. mongorestore will "restore" those files (in those sub-directories) to databases (come from directory name).

SO, if you change that "test_db" directory name (mv test_db other_test_db) to "other_test_db", it will restore those files to "other_test_db"


Understanding MongoDB's contra-indications for read preferences

1) All members of a replica have roughly equivalent write traffic; as a result, secondaries will service reads at roughly the same rate as the primary.

By definition, all data-bearing members of a replica set maintain the same data set so the write traffic is roughly similar for secondaries - but not necessarily identical if compared to a primary. Depending on your workload and server configuration, it's not uncommon to see secondaries with different I/O activity than the primary.

Replica sets are designed to support redundancy and high availability as a key feature, with reads directed to the primary by default (aka a read preference of primary). Servicing secondary reads is a lower design priority than ensuring successful replication and minimizing replication lag.

One interesting caveat is that writes do not have to be applied identically on the primary and the secondaries as long as reads from a secondary always reflect a state that previously existed on the primary. In particular, writes on secondaries are applied in batches using multithreaded replication which improves the write concurrency of secondaries with the expected side effect of adding latency to secondary reads under load.

If you read from secondaries you will have to deal with the possibility of eventual consistency (if there is replication lag) or intermittent latency (if the secondary is actively applying a lot of replicated writes). Replication lag can also vary depending on which secondary you are connecting to, so results may not be as your application code expects if subsequent page requests fetch data from secondaries with significantly different lag.

I would amend this contra-indication warning to suggest that "secondaries will likely service reads at a similar or lesser rate than the primary".

  2) Distributing read operations to secondaries can compromise availability if any members of the set become unavailable because the remaining members of the set will need to be able to handle all application requests.

This counter-indication is referring to capacity planning and possible consequences of failover.

  If I have three members in my replica set, then it seems like common sense that read traffic can be made one-third by distributing it evenly, provided I don't care much about data freshness.

The specific warnings you highlighted are for read preferences of secondary (only read from a secondary) and secondaryPreferred (read from a secondary if available). Both of these read preferences exclude reading from a primary.

With either of these two read preferences and a healthy three member replica set, your reads could be split between the two secondaries. However, if one secondary is unavailable the surviving secondary will need to service 100% of the secondary read load rather than 50%, which could overwhelm your deployment without careful planning. This is exactly the read preference you've asked for, but perhaps not the outcome you expected.

A better alternative would be to use a read preference of nearest, which may read from the primary or from secondaries.

  As for point no. 2, isn't that the failover situation that MongoDB is fundamentally good at solving?

Replication handles recovery from failover in a properly configured deployment, but capacity and deployment planning are separate exercises. The defaults are generally chosen to be reasonable, so if you want to change them it is worth understanding possible implications.

  I mean, if the secondary dies, does the client lose all capacity to read? As I see it, there should be a new secondary should be automatically elected to replace it (and if there are none, the load should transfer to the primary).

A three member replica set has a fault tolerance that allows any one node to be unavailable while still maintaining a primary. If you have two nodes unavailable, the surviving node will become a secondary. With any read preference aside from primary you could still service reads in the sole survivor scenario.

For more discussion on secondary reads, see: Can I use more replica nodes to scale?.


Time taken to drop/add index is lesser than adding a column. Why is that?

Let me say in a simple way. When you create a new  table/index and then you are inserting 10 rows so, index value will be 1 to 10. Now you are deleting 5 and 6 the row in the table so ,holes are formed. Next time while inserting rows from 11 to 20 then index 5 and 6th rows need to be refilled that's why it's taking time. If you drop a column and recreate index then value is auto increment that's why it's faster and there is no hole.


Short Answer
The update lock is sufficient, but you can achieve what you want more simply with:
UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE)
SET @NextOrderNumber = NextOrderNumber,
    NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID;

The WITH (SERIALIZABLE) hint is not strictly required if there is a unique index on CustomerID.
Longer Answer
The update lock hint is sufficient in the code provided. Only one transaction can obtain an update (U) lock on a resource at a time, and update locks are held to the end of the transaction. The update lock is converted to an exclusive (X) lock just before the change is made. The exclusive lock is also held to the end of the transaction.
Taking an update lock when reading therefore provides the concurrency guarantees you are looking for. To be clear: Once the update lock is acquired (by the select), no other transaction can acquire an update lock on the same resource until the first transaction has committed or aborted.
You could also run the transaction (or single update statement) at the SERIALIZABLE isolation level without hints. After all, the guarantee you are looking for is that the transaction should execute according to a serializable schedule. If you are uncomfortable relying on knowledge of locking internals, specifying the desired isolation level, and letting SQL Server handle the details, is probably simpler.
Obviously you would need code to handle errors or possible deadlocks as well. I assume you omitted this from your example. A belt-and-braces approach would also SET XACT_ABORT ON for the procedure to ensure almost all possible errors will abort the transaction instead of silently continuing.
It would also be possible to write a robust locking implementation manually using application locks (with sp_getapplock and sp_releaseapplock), but in all honesty using the built-in serializable isolation level is probably simplest.
For more information, see my series of articles:

SQL Server Isolation Levels


Unable to connect to postgres inside a docker container

postgres peer authentication depends on the linux/unix user that runs psql being the same as user in the -U postgres parameter. As you are running on root locally this isn't true.
Having a postgres user outside the container won't necessary map to the postgres user inside the container.
Recommend changing authentication mechanisms


"Transaction Isolation Modes" let you see data even after it has been deleted.  Some modes effectively "take a snapshot of all data", then let you see only that data until you COMMIT.

I doubt if that is what is causing what you see.  It would be very rare.  It would need longer timeouts than are reasonable.  Have you changed any from their defaults?

Note that I am also saying that you had to start a transaction and and not finish it for a long time; plus sometime in the middle a DELETE occurred from a another connection.

Look at the VARIABLES tx_isolation and autocommit and %timeout%.  Show them to us, plus sketch out the transactions you have.

Another answer:  You have a caching layer outside MySQL that is causing the problem.


How do I reindex a MYSQL table using a different disk?

There is the Percona Tool called pt-online-schema-change
If you can tolerate downtime, you can try the following:
Suppose you have the following

datadir is /var/lib/mysql
MyISAM table named mydb.mytable
You have large disk volume mounted on /backup

You could do the following
mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
#
# Create Symlinks Pointing to Another Disk
#
cp /var/lib/mysql/mydb/mynewtable.MY[ID] /backup/.
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
chown mysql:mysql /backup/mynewtable.*
ln -s /backup/mynewtable.MYD /var/lib/mysql/mydb/mynewtable.MYD
ln -s /backup/mynewtable.MYI /var/lib/mysql/mydb/mynewtable.MYI
mysql -u... -p... -e"INSERT INTO mydb.mynewtable SELECT * FROM mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mytable RENAME mydb.myoldtable"
#
# Erase Symlinks
#
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
#
# Move Newly Made Table Back
#
mv /backups/*.MY[ID] /var/lib/mysql/mydb/.
mysql -u... -p... -e"FLUSH TABLES"

Seems like a lot of work, eh ??? Try the pt-online-schema-change first.
UPDATE 2012-08-22 11:56 EDT
I am not sure what would happen, but please hear me out on this suggestion: Try executing a repair on an empty .MYI file.
How do you do that? Take my idea from my first suggestion and augment to swap the new and old .MYI.
mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
cp /var/lib/mysql/mydb/mytable.MYI /backup/.
rm -f /var/lib/mysql/mydb/mytable.MYI
cp /var/lib/mysql/mydb/mynewtable.MYI /var/lib/mysql/mydb/mytable.MYI
mysql -u... -p... -e"REPAIR TABLE mydb.mytable"

This should perform an in-place linear index rebuild.
Give it a Try !!!


select a value from different tables depending on a condition

You could use something approximately like this; I think you will see how.

CREATE VIEW TransactionEntity
AS
SELECT 2 AS EntityType, Branch.* FROM Branch
UNION ALL
SELECT 3 AS EntityType, Stockist.* FROM Stockist
UNION ALL
SELECT 4 AS EntityType, ...

The tables may have different column names from each other, in which case you shouldn't use "*".  The column names of the view are the column names assigned in the first SELECT statement, but those can be changed from actual column names.

A different method, more complicated, more efficient (I think), is this:

SELECT
      tr.*
    , COALESCE(br.branchname, st.stockistname, - other columns - )
      AS entityname
FROM
transactions     tr
LEFT OUTER JOIN
branch           br
ON
( tr.toentity = 2 AND tr.toid = br.branchid )
LEFT OUTER JOIN
stockist         st
ON
( tr.toentity = 3 AND tr.toid = st.stockistid ) -- corrected mistake of ':' should be '.'
LEFT OUTER JOIN
- other tables that are needed -

The result of COALESCE is the first of its terms that is not NULL.  So, if the transaction is to a branch, then branchname produces the value.  If the transaction is to a stockist, then branchname is NULL but stockistname produces the value.  And so on, for the other tables that may hold the data that you need.

(Updated - typing mistake in the code example, corrected, see the comment)


Postgresql return results of dynamic SQL

You can create a function that returns a table, here is an example.


If you are running this as a SQL Server Agent job step you will need to use the Operating system (CmdExec) step type, not a PowerShell type.

Within the CmdExec it would be best to create a file on the machine with all of the steps that you require and then call that.

For example create a file C:\PowerShellScripts\MyScript.ps1 which contains...

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1
Copy-SqlLogin -Source ServerA -Destination ServerB

And then call that script using powershell.exe "& 'C:\PowerShellScripts\MyScript.ps1'" in the CmdExec job step.


How to replace a single table with Identity Primary Key column from a database backup?

Based on the information you have provided in your question, I think you will need to restore a good backup (with the missing rows for Incidents) into a test database.  You'll need to insert into the real current Incidents table the missing rows from the backup version of the Incidents table (making sure you properly use the SET IDENTITY_INSERT command).  Using SET IDENTITY_INSERT ON allows explicit values to be inserted into the identity column of a table thus allowing you to insert the deleted rows.  Make sure you issue the SET IDENTITY_INSERT OFF after you have completed your inserts.  You'll need to do the exact same comparison and insert for the children tables for each of the parent rows you are restoring since the children were automatically deleted when the parent rows were deleted (due to cascade delete FK's).

After you have addressed all children tables, you should be able to add the FK's back again with CHECK.

You might want to prototype this on a test server.

--Demo IDENTITY_INSERT
--Create test table
IF OBJECT_ID('dbo.TestTable') IS NOT NULL
    DROP TABLE dbo.TestTable
GO

CREATE TABLE [dbo].[TestTable] (
    TestId INT identity(1, 1) NOT NULL
    ,TestColumn VARCHAR(5)
    ) ON [PRIMARY]
GO

--insert a few test rows
INSERT INTO TestTable (TestColumn)
VALUES ('one'),('Two')

--select the two rows - the id values will be 1 and 2
SELECT * FROM TestTable

--delete the two rows
DELETE FROM testtable

--set identity_insert on to allow you to explicitly insert id values
SET IDENTITY_INSERT dbo.TestTable ON

--insert id 1 and 2
INSERT INTO TestTable (TestId,TestColumn)
VALUES (1,'one'),(2,'Two')

--set identity_insert off
SET IDENTITY_INSERT dbo.TestTable OFF

--select the current rows - should be id 1 and 2
SELECT * FROM TestTable


In the end, I had to give myself explicit execute permission on the database.  Even though I was the owner of the database, a sysadmin for the database, and administrator on the server, etc. - SQLCMD would only work if I had the execute permission.


That should work. I created a file like:

[lelle@dustbite src]$ cat apa.csv
20130807183000000

and a table like:

[lelle@dustbite src]$ db2 "create table tt (ts timestamp not null)"

and loaded like:

[lelle@dustbite src]$ db2 "load from apa.csv of del modified by timestampformat=\"YYYYMMDDHHMMSSUUU\" insert into tt"
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file
"/home/lelle/src/apa.csv".

SQL3500W  The utility is beginning the "LOAD" phase at time "07/29/2016
20:35:42.233607".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "1" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "1".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "07/29/2016
20:35:42.482947".

Number of rows read         = 1
Number of rows skipped      = 0
Number of rows loaded       = 1
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1

Could it be the reason that you did not quote the " so that the shell got rid of those?

If I do:

[lelle@dustbite src]$ db2 "load from apa.csv of del modified by timestampformat="YYYYMMDDHHMMSSUUU" insert into tt"

I get the error:

SQL3192N  In the filetmod a user specified format "TIMESTAMPFORMAT" beginning with the string "YYYYMMDDHHMMSSUUU" is not valid.

If that is not the problem, can you provide a small sample of rows that fail during load?


TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices

The idea of CREATE USER ... WITHOUT LOGIN isn't so you can map it later with ALTER USER ... WITH LOGIN (SQL Server simply refuses, as youhave found).

Instead, the idea is for application developers to change the security context before issuing SQL statements, by first doing EXECUTE AS USER = '<user>' WITHOUT REVERT to shed permissions.

This kind of gets you halfway to where you want to be.  You ultimately still need to map some SQL Server logins to some SQL Database Users, but you reduce your need to know in advance what they will be, because you declare access-control permissions against the 'WITHOUT LOGIN' user.

See:

User without login purpose
SQL SERVER – Importance of User Without Login – T-SQL Demo Script


At the moment this does not seem like a possible value to get, which is unfortunate as this does seem like a handy value to have.

Ok, I did find it (mostly). There is a last_execution_time field in the sys.dm_exec_query_stats DMV, though that DMV comes with the following warning:

  An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Also, I am not sure how the value of last_execution_time is affected by multiple concurrent executions of the exact same SQL across multiple sessions since sys.dm_exec_query_stats is per query batch, not per session.

Below is what I have so far. There is maybe one thing I kinda want to clean up, but this should already account for the first query in a batch which will not have a corresponding entry in sys.dm_exec_query_stats since that gets populated (ideally, but not always in a timely manner) after the current statement / query completes. And it seems to work for single-query batches, so try it out.

SELECT req.session_id AS [SessionID],
       req.start_time AS [BatchStartTime],
       req.total_elapsed_time as [BatchMilliseconds], --[TotalElapsedTimeMs],
       CASE WHEN stat.last_execution_time IS NOT NULL THEN
                DATEADD(MILLISECOND,
                        (stat.last_elapsed_time / 1000.0),
                        stat.last_execution_time)
            ELSE req.start_time END AS [StatementStartTime],
       DATEDIFF(MILLISECOND,
                CASE WHEN stat.last_execution_time IS NOT NULL THEN
             DATEADD(MILLISECOND, (stat.last_elapsed_time / 1000.0),
                stat.last_execution_time)
                  ELSE req.start_time END, GETDATE()) AS [StatementMilliseconds],
       T2.login_name AS [LoginName],
       req.command AS [SQLCommand],
       req.[status] AS [QueryStatus],
       req.wait_type AS [WaitType],
       req.wait_time AS [WaitTimeMs],
       blocking_session_id AS [BlockingSessionId],
       req.row_count AS [RowCount],
       req.cpu_time AS [CpuTimeMs],
       sqltext.[text] AS [QueryBatch],
       SUBSTRING(sqltext.[text],
                 req.statement_start_offset / 2,
                 CASE req.statement_end_offset
                      WHEN -1 THEN DATALENGTH(sqltext.[text])
                      ELSE (req.statement_end_offset - req.statement_start_offset) / 2
                 END) AS [CurrentQuery]
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions T2
        ON T2.session_id = req.session_id
LEFT JOIN sys.dm_exec_query_stats stat
        ON stat.[sql_handle] = req.[sql_handle]
       AND stat.statement_end_offset = (req.statement_start_offset - 2)
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) sqltext
-- WHERE req.session_id = 56
ORDER BY req.start_time;

I have been testing with your query (slightly modified) and the following SQL running in another tab / session (the TOP(1), TOP(2), etc, and 10.1, 10.2, etc on the WAITFOR times are to make it easily visible which statement is being processed when looking at the current statement field):

SELECT GETDATE() AS [StartTime];
GO
SELECT TOP(1) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.1';

SELECT TOP(2) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.2';

SELECT TOP(3) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.3';

SELECT TOP(4) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.4';

Then just run your query several times and it is pretty clear that dm_exec_requests.start_time and dm_exec_sessions.last_request_start_time are:

the same value, and
do not change at all during the execution of that batch.

I did try getting the execution plan via CROSS APPLY sys.dm_exec_text_query_plan(req.plan_handle, req.statement_end_offset, req.statement_end_offset) tplan, but that was NULL so not very helpful.

In order to test with your query, I had to remove the first two columns and the reference to the dbo.GetExecutingSQLStatement function. This is probably something you could also get rid of since you have that logic in this query already via CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]). You just need to make the following three changes:

T3.FullStatement (Select field # 1) becomes sqltext.[text]
T3.ExecutingStatement (Select field # 2) becomes SUBSTRING(sqltext.[text], req.statement_start_offset / 2, CASE req.statement_end_offset WHEN -1 THEN LEN(sqltext.[text]) ELSE (req.statement_end_offset - req.statement_start_offset) / 2 END)
Remove the SUBSTRING (your current last field). If you look closely at the SUBSTRING I am suggesting directly above, it is essentially what you had at the end there, expect it corrects for two mistakes:

Your SUBSTRING did not account for when there was only 1 query in the batch, in which case statement_end_offset is -1 and your substring gets an error
Your SUBSTRING did not account for the translation of double-byte byte count into character count.

But I digress.


Does SQL Server evaluate functions once for every row?

Certain functions that are known to be runtime constants go through the process called constant folding. By 'folding' a constant an expression is evaluated early in the query execution, the result is cached and the cached result instead when needed. The expression in your query DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) is, afaik, a runtime constant and thus will be folded and evaluated only once per query.

As trivia: the RAND() function that one would expect to be unfoldable is actually foldable, which leads to some unexpected behavior. But other, for instance NEWID(), are not foldable and will force an evaluation per row.


AWE allocated pages are not reported by Task Manager. The "Using locked pages for buffer pool" message means the production server is using AWE. Do not trust Task Manager. Use SQL Server own counters: SQL Server, Memory Manager Object, SQL Server, Buffer Manager Object.

Read Fun with Locked Pages, AWE, Task Manager, and the Working Set…


How to change the replication State Transfer SST to IST in Galera Config

This is not the whole truth. It is not about SST vs. IST it is about SST followed by an IST.

SST is a full sync, IST is an incremental sync between 2 nodes.

Sometimes you have to use an SST. For example, when you sync your second Galera node with the first one the first time. Later on, when both nodes have the full data set and you take out one node and bring it in again, Galera will choose if IST is possible and if not choose SST.

You can force an SST in favour of IST but not the other way around.


Should I use XML for data storage on client side?

This also depends on what kind of records you want to be stored, whether it is semi-structured data that is best formed using XML, or plain key-value based records. SQL Server Express can store both kinds, but involves an additional install. However, to avoid extra installs, you can check out SQL Server Compact and SQLite, both of which are installed as a part of the program you will be distributing and will run embedded with it. Also, several NoSQL databases may cure your needs - just give Google a hint and check the answers. Some of them are also compatible with Linq or whatever means you are using to store and extract data from your code.

SQL Server Compact may be downloaded here:
http://www.microsoft.com/en-us/download/details.aspx?id=30709
SQLite
is available here: https://www.sqlite.org/download.html


Go through this link:
http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

Since all values are constant and same creating non clustered index will not help here.


Comparing date and timestamp variables in a where clause in a procedure

Assuming the DATE from user interface can contain times besides 00:00:00 (midnight) and that you only want to know if the DATE from the user interface and the TIMESTAMP fall on the same day (not same hour or minute or ?), try this:

...

myDatabaseDate    DATE;
myDatabaseNextDay DATE;

BEGIN

  -- Change TIMESTAMP to a DATE type with CAST, then TRUNCate time to 00:00:00.
  myDatabaseDate    := TRUNC( CAST(myDatabaseTimeStamp as DATE) );
  myDatabaseNextDay := myDatabaseDate + 1;

  SELECT something into some_var
  FROM MYDATABASE
  WHERE inputDate < myDatabaseNextDay
  AND inputDate >= myDatabaseDate;

  ...

Note 1: Because "inputDate" is a database column and could be indexed, we don't want to do TRUNC(inputDate) because then the index won't be used.

Note 2: Doing the CAST before the SQL statement prevents this PL/SQL compilation warning: PLW-07204: conversion away from column type may result in sub-optimal query plan ... Edit: After testing this on 10.2 XE, even the "myDatabaseDate + 1" causes the warning so, although it seems like doing too much to please the compiler, I added "myDatabaseNextDay := myDatabaseDate + 1;"


Can Postgresql exploit "accidental" clustering in plans?

The planner is unaware of this type of clustering and so can't make decision based on it.
The two-step sampling method used by ANALYZE can generate skewed samples in this situation, possibly leading to a drastic underestimate of n_distinct.  It is hard to predict what the consequences of that might be without digging into the details of individual queries.


The mysql command you show seems (experimentally, not from actual knowledge) to generate output which is compatible with PostgreSQL's TEXT format, not the CSV format.  Newlines are represented by the two characters \n, tabs by \t, and literal backslashes by \\.
So try to import it in that format, which is the default:
\copy tab FROM /tmp/file


Does SQL Server have a Date_Format function?

Not yet.

You need to use CONVERT with a style parameter or hack something together with DATEPART or DATENAME.

SQL Server 2012 will have the FORMAT function though that accepts a .NET Framework format string

Syntax:

FORMAT ( value, format [, culture ] )

Example Usage

SELECT FORMAT(getdate(), 'dd/MM/yyyy', 'en-US' )

You could always use CLR integration and create your own UDF that does the same thing for 2005 or 2008.


MySQL Security - Storing lots of sensitive phone numbers securely?

If you store encrypted data in the database, the data is not understandable anymore without a layer of code on top. This might seem like a good move, but if one takes a closer look, it complicates things and does not provide more security.

An example which becomes a lot more complicated: "Compute the number of phone numbers with a special area code". How to do this in a normal DB? Well something like SELECT COUNT(*) FROM phonenumbers WHERE phonenumber LIKE "XZY%" will work. How to do this, if the database cannot understand its own data? Transfer everything to the application, decrypt it and compute the result there. That can obviously be really cumbersome with large databases. Basically all advantages one receives through using a DBMS are nullified.

Why this is not more secure? One does not even have to hack your database anymore, it is enough to hack the application. Likely this is easier, since the application uses a lot of custom code. Further the firewall will allow access to the application servers, while it will likely prohibit access to database servers from outside.

What to do instead? Secure the database server and restrict access to sensitive data. You can (should) create a dedicated area for sensitive data, which can only be accessed with dedicated database accounts. This credentials should be used rarely in your application, and should not be handed over easily to e.g. developers. You could even set up an own dedicated isolated database server for such sensitive information.


create trigger which inserts rows depending on enum values?

It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS.

DELIMITER $$

DROP TRIGGER ins_addresses $$

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN

DECLARE column_list char(200);
DECLARE current_value char(200);
DECLARE counter INT DEFAULT 0;
DECLARE num_enums INT;

  /* Format goes from "enum('shop1','shop2')" to "shop1,shop2" */

  SELECT REPLACE(SUBSTR(COLUMN_TYPE,6,LENGTH(COLUMN_TYPE)-6),'''','')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'stackex'
  AND TABLE_NAME = 'addresses'
  AND COLUMN_NAME = 'shop'
  INTO column_list;

  SET num_enums = LENGTH(column_list)- LENGTH(REPLACE(column_list, ',', ''))+1;

  WHILE counter < num_enums DO
    SET current_value = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(column_list, ',', counter+1),
          LENGTH(SUBSTRING_INDEX(column_list, ',', counter )) + 1), ',', ''));

    INSERT INTO addresses(articleID, shop, createtime)
    VALUES (NEW.articleID, current_value, CURRENT_TIMESTAMP);

    SET counter = counter + 1;
  END WHILE;
END$$
DELIMITER ;

Test:

mysql> use stackex;
Database changed
mysql> insert into searcharticles values ( 1, 'foo','bar' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from addresses;
+-----------+-------+------+--------+---------------------+------------+
| articleID | shop  | url  | status | createTime          | updateTime |
+-----------+-------+------+--------+---------------------+------------+
|         1 | shop1 | NULL |      0 | 2012-08-17 09:32:33 |       NULL |
|         1 | shop2 | NULL |      0 | 2012-08-17 09:32:33 |       NULL |
+-----------+-------+------+--------+---------------------+------------+
2 rows in set (0.00 sec)

mysql>

I actually hate myself for answering this! Change the data model to use a lookup table, shops.

It'll break horribly if the shop enum names have commas or quotation marks in them (probably).


What happens to a SQL Server 2019 Standard instance with an Annual Subscription Licence when it expires?

SQL Server has no technical enforcement of licenses (except for Evaluation Edition which is time-bombed).
An installation of SQL Server has a "product key" to activate the installation for a particular SKU, but this is not a license.
The assignment of licenses to servers is handled externally.


What is the benefit of two FK, when one of them can be deduced from another table?

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop.

There should be a single foreign key referencing two columns in categories.

create table products (
  ...
  primary key (shop_id, category_id, product_id),
  foreign key (shop_id, category_id)
    references categories (shop_id, category_id)
);

Think about it this way. Conceptually, normalization starts with a single relation that contains all the attributes.

shop_name  category_name    product_name
--
Wibble     Chain saws       Stihl 350
Wibble     Chain saws       Poulan 3X
Wibble     Pole saws        Black & Decker 14 foot electric
Wibble     Pole saws        Corona 15 foot

Thursby    Pole saws        Hitachi electric
Thursby    Pole saws        Remington electric
Thursby    Chain saws       Husqvarna 460
Thursby    Chain saws       Poulan 3x

It should be clear that the only candidate key is {shop_name, category_name, product_name}. It's in 5NF. There's no redundant data.

Replacing text with ID numbers won't improve that.

A design that requires you to chase ID numbers through a hierarchy of tables is an anti-pattern. It's like building an IMS database in SQL. (IMS was a problem the relational model intended to solve.)


You found a (known) limitation of the optimizer of MySQL.

The problem with DELETE FROM table WHERE column IN (SELECT ...) is that the subquery (SELECT) is executed for each row of the table that has a different value for column.  In your case, as it is a PRIMARY KEY, it is executed a million times.  More details in http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html.

The reason explaining why the SELECT * FROM table WHERE column IN (SELECT ...) is faster is that the optimizer is rewriting the query as a JOIN.  You can see that as the EXPLAIN results of the 2 queries are different (MySQL 5.6.21):

mysql> EXPLAIN SELECT * FROM test_jfg.hours WHERE id IN (SELECT id FROM test_jfg.holdkey WHERE persons_id=60);
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+------------------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                 | rows | Extra                        |
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+------------------------------+
|  1 | SIMPLE      | holdkey | ALL    | NULL          | NULL    | NULL    | NULL                |  920 | Using where; Start temporary |
|  1 | SIMPLE      | hours   | eq_ref | PRIMARY,id    | PRIMARY | 4       | test_jfg.holdkey.id |    1 | End temporary                |
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN DELETE FROM test_jfg.hours WHERE id IN (SELECT id FROM test_jfg.holdkey WHERE persons_id=60);
+----+--------------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | hours   | ALL  | NULL          | NULL | NULL    | NULL | 997457 | Using where |
|  2 | DEPENDENT SUBQUERY | holdkey | ALL  | NULL          | NULL | NULL    | NULL |    920 | Using where |
+----+--------------------+---------+------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

According to http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_select_type, DEPENDENT SUBQUERY means:

  For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context.

To solve that, I do not see other solutions that doing 2 (or more) queries:

Get the results from the SELECT,
If the resultset is small, do a DELETE ... IN (explicit list)

If the resultset is large, you will need to do many DELETE like above with a subset of the list.


TimescaleDB database design - should I use inheritance?

A) No, I don't believe that type of inheritance is supported in
Timescale.
B) This won't help query performance at all, it'd probably
hurt it significantly and be a big mess.
C) I'd recommend just having
each data type as a column in the table and for each pair id filling
in the correct data type. Then you can store which pair ids use which
data types in a second table, and when you're constructing your query
you grab the correct column. You can also do the correct continuous
aggregates, grouped by pair id, with the correct aggregation for each
data type, which should mean only one continuous aggregate.

Finally, you can aggregate and then join like that, but probably not for all of the thousands of columns, there'll be some limit where that gets quite inefficient and also very difficult to deal with...are you actually using thousands of columns in a single calculation? And is it really more efficient to have them in row form? You might consider using array_agg(row(pair_id, data) ORDER BY pair_id) GROUP BY time or the like  to do something like what you want. you could also use JSON or some other thing as your return type if that's better, but I would also see if you can limit your queries to say 10s or hundreds of columns. Or split the whole thing into real tables with related columns and use the composite types to fix some of this...


Unfortunately, I would have to go with

  rely on human intelligence and debugging to figure this out for yourself

Here is why:

Suppose you have the following table

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

MySQL would have to look for a table called Staff. If it finds the table Staff, MySQL has to find a column in that table called StaffID.

For a table with 10 foreign keys, this algorithm would be followed 10 times. Sadly, MySQL does not generate a error stating which table or column name caused the CREATE TABLE to fail. You could just remove one of the foreign keys and try it with 9, then 8, and so on.

Here is a strange case that require intelligence:

What if you did a mysqldump of InnoDB data from a MySQL Databases running in Windows and try to import it into Linux ?
What if you did a mysqldump of InnoDB data from a MySQL Databases running in Linux and try to import it into Windows ?

There is a case sensitivity issue. The foreign key references may fail due to this.

I have discussed this back in March 2014 : creating table result in errno 150


MySQL crashes and currently the error.log file is huge

For the 4GB droplet, change the config: innodb_buffer_pool_size to 1500M and restart.

For the 16GB droplet, change the config:

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_page_cleaners = 12

Revised Analysis  (After running more than a day)

Observations:

Version: 5.7.24-0ubuntu0.18.04.1-log
16 GB of RAM
Uptime = 1d 02:59:16
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

A lot of table scans and many are big.  This may be interfering with other InnoDB operations, hence indirectly stalling the page_cleaners.

Change to innodb_lru_scan_depth = 256 as a possible solution to the page_cleaner problem.

Details and other observations:

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 511,813 / 786384 = 65.1% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4,456,398,848 / 12288M = 34.6% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Handler_read_rnd_next / Com_select ) = 10,900,684,560 / 1418310 = 7,685 -- Avg rows scanned per SELECT. (approx)
-- Consider raising read_buffer_size (128K now; unclear whether raising it will help)

( Select_scan ) = 233,714 / 97156 = 2.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 233,714 / 1418310 = 16.5% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Connections ) = 131,201 / 97156 = 1.4 /sec -- Connections
-- Increase wait_timeout; use pooling?

Abnormally small:

Innodb_dblwr_pages_written / Innodb_dblwr_writes = 2.31

Abnormally large:

Com_show_plugins = 0.26 /HR
Com_show_privileges = 0.037 /HR
Com_stmt_close = 21 /sec
Com_stmt_execute = 21 /sec
Com_stmt_prepare = 21 /sec
Innodb_buffer_pool_pages_free = 511,813
Performance_schema_file_instances_lost = 9
innodb_page_cleaners = 12
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210

Abnormal strings:

innodb_fast_shutdown = 1
innodb_large_prefix = ON
log_slow_admin_statements = ON


You'd have to substitute the datetime in the sp_trace_create
That is, drop and create each day unfortunately (as you noted). There is no "alter trace"
Overwritten. The 1, 2, etc suffix is for rollover: not for trace startup
It isn't locked.
The NTFS ownership and permissions are not inherited from the parent folder. You need to change this using Explorer or via scripting (cacls, subinacl) (not sure exactly sorry, been some time)