A good tool to help you tune MySQL config is the Percona Configuration Wizard for MySQL. This asks you some questions about your system and your workload, and gives you some approximate tuning values to start with.

Of course the term tuning suggests that further refinement may be beneficial, but this happens over time as you monitor your workload, resources usage, and bottlenecks. It's not something we can answer once and for all in a post like this one.

However, I have other comments about your config:

max_allowed_packet = 32M

This is not going to take effect, because you override it with a smaller value later in your config file. See below.

binlog_cache_size = 2M

This is 64x the default. Unless you have a good reason to increase it, don't. Note that the binlog cache is allocated per user thread, so if you have 100 concurrent transactions, this would use 200MB of RAM. Suggest you remove this line and use the default.

max_heap_table_size = 64M

This constrains tmp_table-size. See below.

read_buffer_size = 4M

This is 32x higher than the default. It's used only during table-scans of MyISAM tales. It would be better to create appropriate indexes and avoid table-scans. Suggest you remove this line and use the default.

read_rnd_buffer_size = 32M

This is 128x higher than the default, and 16x higher than the maximum allowed value of 2M. This is used only for MyISAM reads in MySQL 5.5 and earlier, and in MySQL 5.6 it's also used for multi-range reads. But it's probably not necessary to increase it. Suggest you remove this line and use the default.

sort_buffer_size = 16M

This is 4x higher than the default. In MySQL 5.6, it's 16x higher than the default. This is also a buffer that is allocated for each user thread, so be careful how much you increase it. Suggest you remove this line and use the default.

join_buffer_size = 8M

This is 64x higher than the default, so be sure you have a good reason for increasing it. It's used during index scans and joins on unindexed columns. It would be better to index tables better. Keep in mind that the join buffer can be allocated for each user thread, so this 8MB amount can account for unpredictable memory growth. Suggest you remove this line and use the default.

thread_cache_size = 20

Modern Linux kernels can create threads a lot faster than in the old days, so it's no longer as important to keep a thread cache. Suggest you remove this line and use the default.

thread_concurrency = 16

Are you running on Solaris? If not, this variable has no purpose. Suggest you remove this line.

query_cache_type=1
query_cache_size = 1024M

This is a lot of RAM to dedicate to the query cache, and it's probably overallocated. The query cache has some downsides, especially when you have many concurrent connections. Frequently we actually recommend to disable the query cache unless you can demonstrate you're getting a lot of bang for the buck.

Disable by setting both query_cache_type=0 and query_cache_size=0.

ft_min_word_len = 4

This is the default. Suggest you remove this line and use the default.

default-storage-engine = MYISAM

Recommend to set the default storage engine to InnoDB.

I recommend against using MyISAM for anything. MyISAM supports table-locking only, it's susceptible to corruption in crashes, it's not being developed anymore, and it's gradually being phased out. Claims that MyISAM is faster than InnoDB are based on very old versions of MySQL. Even as far back as 2007, benchmarks show that InnoDB is faster than MyISAM under most workloads.

One of the last reasons to use MyISAM, fulltext indexes, is also obsolete. InnoDB is getting fulltext indexes (they were introduced in 5.6, but they're not really usable yet). Sphinx Search is better for fulltext search anyway.

thread_stack = 192K

This is actually lower than the default of 256K on 64-bit systems. Suggest you remove this line and use the default.

transaction_isolation = REPEATABLE-READ

This is the default. Suggest you remove this line and use the default.

tmp_table_size = 4G

This is not going to allow any tmp tables higher than max_heap_table_size, which you set to 32M earlier. So if you want large tmp tables, you must also increase that config variable to match. But be careful, because you could end up with multiple threads populating 4G tmp tables in memory, and then start swapping.

key_buffer_size = 16G
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 246M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

These are used only by MyISAM, and you don't need them if you don't use MyISAM.

innodb_additional_mem_pool_size = 16M

This is moot on modern Linux kernels, where dynamic memory allocation is actually more efficient than letting InnoDB preallocate memory. Suggest you remove this line and use the default.

innodb_buffer_pool_size = 12G

This should be enough to hold all your InnoDB data and indexes, if possible, plus maybe about 10% extra for the change buffer.

You can query the size of your InnoDB data and indexes:

SELECT SUM(data_length+index_length)/1024/1024 AS total_InnoDB_size_in_MB
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'InnoDB';

If that total greater than the size of your physical system RAM (64G), then increase the buffer pool as much as is practical. Usually the advice is about 80% of your system RAM, assuming you don't have any other big consumers of memory on the same server. If you do, subtract the memory needed for other processes.

If your data is a lot smaller than your system RAM, then there's no benefit to allocating a big buffer pool. Only one copy of each page of data and indexes is held in the buffer pool.

innodb_data_file_path = ibdata1:10M:autoextend

This is the default.

innodb_file_io_threads = 4

This variable is not recognized after MySQL 5.0 (or 5.1 unless you enable the InnoDB plugin). Use innodb_read_io_threads and innodb_write_io_threads. Besides, you're setting the variable to the default value anyway, so you don't need this line in your config file.

If you're still running a version of MySQL old enough that this variable is recognized, I strongly suggest you upgrade.

innodb_thread_concurrency = 16

This is probably outdated, unless you're running a very old version of MySQL. Suggest you remove this line and use the default of 0 (unlimited thread concurrency) unless you observe a bottleneck.

innodb_flush_log_at_trx_commit = 1

This is the default. Suggest you remove this line and use the default.

innodb_log_buffer_size = 8M

This is the default. Suggest you remove this line and use the default.

innodb_log_file_size = 256M

Is this enough? Too much? Here's a blog to help: http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

innodb_log_files_in_group = 3

There's no benefit to changing the innodb log file in group. Suggest you remove this line and use the default.

innodb_max_dirty_pages_pct = 90

This was the default in MySQL 5.0. Now the default is 75. But it's not likely you'll have so many dirty pages regardless. Suggest you remove this line and use the default.

innodb_lock_wait_timeout = 120

The default is 50 (seconds), and should be adequate for most workloads. Do you have a reason to increase this to 120?

max_allowed_packet = 16M

You set max_allowed_packet to 32M earlier. Keep in mind that if you set a given variable in the config file more than once, the last setting takes precedence. So your packets are limited to 16M. If you intend them to be allowed up to 32M, then take out this line.

[myisamchk]

[mysqlhotcopy]

myisamchk and mysqlhotcopy are only used for MyISAM and ARCHIVE tables. If you migrate to use InnoDB, you won't need to use these tools.


Postgres 8.4 -- connection refused (remote)

The 10.0.0.1 is the address of your client.  What you need to put into listen_addresses is an address on the server.  If you don't want to use *, then it should be the address of the server on the same network as the client.  In this case this would probably be some other 10.* address.  Check with /sbin/ifconfig or ip addr list what address that would be on your host.


Performance issues of `fetch first n rows only`

not sure i understand correctly. do you mean this is SLOWER or FASTER?

insert into new_table (new_column)
select column as new_column
from table
-- no WHERE clause
fetch first 1000 rows only;

in general, if there is a where-clause, any database engine must read records, and 'drop' those that don't match the where clause. the 'read-loop' must read from disk until 1000 matches are found. the best case will encounter NO non-matches; the worst case will read the complete dataset

without a where-clause, the database engine only has to read 'the first 1000 records', thus every IO operation can be used for the resultset. so, that should be faster.


How to execute a query based on the condition if a column exists?

Since you aren't using column y, you can ignore it in your first query. Then, you can simplify this with a single statement:

SELECT
    x,
    A = z
FROM table1
WHERE y!=0 and z is not null


Do valid to/from columns on a fact table make sense?

The validity depends on how users want to look at the data.  You are looking at it as just a transaction fact. Other types of fact tables include periodic snapshots and accumulating snapshots.  If you want to see all the times that someone corrected a row to help decrease erroneous entries, the effective dates may be appropriate so it's clear that the transaction was updated. This creates a fact table that is somewhat similar to a type 2 SCD.

The Kimball Group has an article that directly addresses your question.

Here's a Kimball Group design tip  that talks about effective dated accumulating snpashot fact tables.

You may be correct that you should just add transactions that reverse the original row. That sounds like it could be a valid solution if you just need to see the transactions and sum them up. This is how I've seen most accounting data work. Kimball says the effective dated fact tables may be useful for quickly calculating account balances at a paritcular point in time, especially for tracking slowly changing balances. But this is a fairly rare case. I think your concern that it will be confusing for users is also valid. You have to decide if that can be overcome by education and if its worth it for the added analytical capabilites in the data.

I haven't had to do this much in my data warehousing experience because most of my facts were simple transactions or periodic snapshots. But I have created several effective dated bridge tables for many-to-many relationships.


How do I change the default external text editor in psql?

There are environmental variables you can set, from man psql

PSQL_EDITOR, EDITOR, VISUAL
Editor used by the \e, \ef, and \ev commands. These variables are examined in the order listed; the first that is set is used.
The built-in default editors are vi on Unix systems and notepad.exe on Windows systems.

The PSQL_EDITOR is if you want to specify a unique editor for just PostgreSQL. You can probably ignore that as not many people have a different editor for that, but in the event you're using a SQL Editor, go for it.
On Linux, you probably want to change EDITOR. On Ubuntu and Debian, that's done like this,
sudo update-alternatives --config editor


PostgreSQLpgagent "Running at: is not runing"

since you're specifying

pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres

change .pgpass to match it

127.0.0.1:5432:*:postgres:mypass

or specify

*:5432:*:postgres:mypass

Edit: And change the permissions of .pgpass back to 0600 else you'll get the message:

WARNING: password file "~/.pgpass" has group or world access; permissions should be u=rw (0600) or less

and for command line, it prompts for password


Do the action blocks of an ALTER TABLE statement run atomically?

Every SQL statement is run inside a transaction. At minimum (and by default) each statement runs in its own transaction.

The documentation says:

  By default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

So either both actions succeed, or both are rolled back.


ALTER USER WITH LOGIN throws error "Windows NT user or group not found"

This error will happen if the login you are attempting to use does not exist.

Example:

CREATE LOGIN username WITH PASSWORD = '131fEWF$#@FR$#Vverf';
CREATE LOGIN username2 WITH PASSWORD = '131fEWF$#@FR$#Vverf';

USE tempdb;
CREATE USER username FOR LOGIN username;

ALTER USER username WITH LOGIN = username2; /*Successful*/

DROP LOGIN username;

ALTER USER username WITH LOGIN = username; /*Unsuccessful*/

You should check to ensure that the login was not removed prior to your attempt to run the ALTER USER command.


It is a calendar you need. How to get it? Using a row generator technique.
As I don't have your table, I'll use Scott's EMP and count employees hired in every month. Calendar I'm going to create will contain months between MIN and MAX hiredate (you can create any dates you want, of course).
EMP table's contents:
SQL> select empno, ename, hiredate
  2  from emp
  3  order by hiredate;

     EMPNO ENAME      HIREDATE
---------- ---------- ----------
      7369 SMITH      17.12.1980
      7499 ALLEN      20.02.1981
      7521 WARD       22.02.1981
      7566 JONES      02.04.1981
      7698 BLAKE      01.05.1981
      7782 CLARK      09.06.1981
      7844 TURNER     08.09.1981
      7654 MARTIN     28.09.1981
      7839 KING       17.11.1981
      7900 JAMES      03.12.1981
      7902 FORD       03.12.1981
      7934 MILLER     23.01.1982
      7788 SCOTT      09.12.1982
      7876 ADAMS      12.01.1983

14 rows selected.

SQL>

Query that does the job:
SQL> with
  2  -- MIN and MAX hiredate
  3  minimax as
  4    (select min(trunc(hiredate, 'mm')) mindat,
  5            max(trunc(hiredate, 'mm')) maxdat
  6     from emp
  7    ),
  8  calendar (datum) as
  9  -- calendar, based on MIN and MAX hiredate
 10    (select add_months(mindat, level)
 11     from minimax
 12     connect by level <= months_between(maxdat, mindat)
 13    )
 14  -- the final result
 15  select
 16    to_char(c.datum, 'yyyy-Month', 'nls_date_language = english') datum,
 17    count(e.empno) cnt
 18  from calendar c left join emp e on trunc(e.hiredate, 'mm') = trunc(c.datum, 'mm')
 19  group by c.datum
 20  order by c.datum;

DATUM                                            CNT
----------------------------------------- ----------
1981-January                                       0
1981-February                                      2
1981-March                                         0
1981-April                                         1
1981-May                                           1
1981-June                                          1
1981-July                                          0
1981-August                                        0
1981-September                                     2
1981-October                                       0
1981-November                                      1
1981-December                                      2
1982-January                                       1
1982-February                                      0
1982-March                                         0
1982-April                                         0
1982-May                                           0
1982-June                                          0
1982-July                                          0
1982-August                                        0
1982-September                                     0
1982-October                                       0
1982-November                                      0
1982-December                                      1
1983-January                                       1

25 rows selected.

SQL>


Can SSDT deploy SQL server service broker objects?

It would appear so. I have not tried these, but the following is the list of items that can be added to an SSDT SQL Server Database Project:

Broker Priority
Contract
Endpoint (ServiceBroker over TCP)
Endpoint (SOAP over HTTP)
Event Notification
Message Type
Queue
Remote Service Binding
Route
Service


Export pump all schemas except Sytem related schemas

As @a_horse_with_no_name mentioned, you can use the exclude parameter to limit the objects to be exported.

It's very simple to use and it supports in/ like... operators.

Usage example:

expdp username/password directory=datapump dumpfile=x.dmp logfile=y.log full=y exclude=schema:"in ('SYS', 'SYSTEM', ..., ...)"


Location of Maintenance Plan's Back Up Database Tasks information (SQL Server 2005)

In SQL 2000 they were in the msdb database.

Specific table information can be found here:
http://www.toadworld.com/platforms/sql-server/w/wiki/9438.msdb-databases.aspx#Database_Maintenance_Plan_Tables

In SQL 2005 maintenance plans are modified SSIS (or DTS) packages and are stored in the dbo.sysdtspackages90 table.  (from: http://www.sqlservercentral.com/Forums/Topic501078-5-1.aspx )

In SQL 2008 and 2012 they are in the sysssispackages table.


MySQL Replication Stops, But Still Says it is Running

What catches my attention is the word 'NETWORK'.

Communication between Master and Slave is implemented as bidirectional.

According to the MySQL Documentation on Replication

  Binlog dump thread.  The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

  The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

  Slave I/O thread.  When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

  The slave I/O thread reads the updates that the master's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave's relay log.

  The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Given this description of the I/O aspect of Replication, what could you look for ???

FIREWALL

The connection between Master and Slave requires that the firewall be open. Unfortunately, I have seen occasions where the firewall was open on the Master and a Slave would connect as usual. The Slave would have the I/O thread show up in the processlist like nothing was wrong. The Master would do the same. All of a sudden, 60 seconds later, the I/O thread disappears from the processlist of the Master, but remains visible on the Slave.

Given that scenario (that I eyewitnessed between two Amazon EC2 servers in two different AZs (Availability Zones)), the solution back then was to check the Security Groups and get port 3306 open in the Slave's AZ.

TIMEOUT

MySQL has settings for timing out network connections

From the MySQL Docs:

rpl_semi_sync_master_timeout : A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds). This variable is available only if the master-side semisynchronous replication plugin is installed.
net_read_timeout : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
net_write_timeout : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.
slave_net_timeout : The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. Prior to MySQL 5.7.7, the default was 3600 seconds (one hour). In MySQL 5.7.7 and later the default is 60 seconds (one minute).

CONCERNS

Why talk about the network like this ??? You can be victimized in the form of data drift. Back on Jun 17, 2014, I answered the post I have been tasked with Mysql Master-Master replication?. I briefly mentioned the network as an unsung hero in data drift:

Networking

You must make sure the network traffic is not inundated, your switches are fine, any crossover cabling to bypass the network is not defective, there are no dropped packets,.
In terms of MySQL, the Replication I/O thread is dependent on the network. You must make sure mysqld can regularly heartbeat its master. Perhaps semisync replication could be installed and configured for this.

YOUR ACTUAL QUESTION

You running STOP SLAVE; and START SLAVE does not find the root cause but does indeed solve the problem at hand. How ??? All this does is disconnect both the I/O and SQL Threads and then reconnect from scratch.

You could also have done

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

which would also do just fine, especially if the SQL thread is busy and you do not want interrupt it.

You will need to check the connection between the Master and Slave for dropped packets.

If your monitoring has the same time granularity as the MySQL timeout values, you have nothing to alert you when it does happen. You would have to poll MySQL more frequently. As an alternative, you could probably create some kind of SNMP setup to monitor MySQL so if SNMP info from does not update in a timely manner you could detect MySQL being down or not responding without ever connecting to MySQL.

My answer may not have fully defined the root cause, but I do have two suggestions:

SUGGESTION #1

Look into your max_allowed_packet setting. Many times in the DBA StackExchange I have affectionately called the MySQL Packet the Silent Killer of DB Connections. The I/O thread is just as much as DB Connection as any other. I would make sure max_allowed_packet is always set to 1073741824 (which is 1G).

SUGGESTION #2

You could manually set the I/O Thread Heartbeat. How ?

According to the MySQL 5.5 Documentation for CHANGE MASTER TO

  MASTER_HEARTBEAT_PERIOD sets the interval in seconds between replication heartbeats. Whenever the master's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval.

  Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2.

  Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value.

Based on these paragraphs and the default value for slave_net_timeout (60 Seconds), it appears that the I/O thread should heartbeat every 30 seconds. You could change the heartbeat period to 10 seconds like this:

STOP SLAVE;
CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 10;
START SLAVE;


Run subquery once / optimize join with subquery without needing to use temporary table

I was under the impression that when you have a subquery in a join like this the SQL engine would get the data first before joining it with the outer query.

The query optimizer considers various physical execution strategies that are guaranteed to produce the same results as the original logical query specification. So, no, there is no general inference you can make from the written form of the query about how it will actually be executed.

The decision the optimizer makes is based on estimated costs, which are determined to a large extent by the size (cardinality and width) of the data expected at each stage of the pipeline, and the type of physical operation performed.

If your query happens to result in inaccurate cost estimates, the strategy chosen by the optimizer is quite likely not to be optimal, perhaps not even very good at all.

Inaccurate costs have many causes, including unrepresentative statistics on the base objects, and the use of opaque, or difficult to estimate, predicates in the query. With good statistics, a reasonable sized query, simple comparison operations, and a relational schema, the chances are quite good that estimates (and hence the optimizer's physical plan selection) will be at least good enough.

One way to simplify the optimizer's work is to materialize parts of a complex query (or one that simply does not get good estimates) into temporary tables. These give the optimizer direct cardinality information, and SQL Server can usually automatically create statistics on the temporary table to provide further good information. Last, but not least, the temporary table allows you to add specific indexes to it, where that is worthwhile.

When used sensibly*, temporary tables are a great way to provide information to the optimizer, producing reliable execution plans even as the distribution and size of the underlying data changes.

If you have done all you can with statistics, indexing, and the written form of the query, using a temporary table might well be optimal here. It's impossible to say for sure, based on the information given in the question, and not knowing the expertise level of the author.

* Sensible use means e.g. limiting the number, size, and width of temporary tables required, as well as the frequency of use. SELECT * INTO #temp FROM dbo.MyHugeTable; is not sensible, for example.


Secondary standby server log shipping backup job error

It is clear the credential used for the copy job cannot be validated by the server that stores the T-Log backups or copied T-Log backups.

What account have you configured for log shipping copy job?

To access the destination disk, the admin there has to create an NT user and grant permissions. Ask for the help of your Windows administrator.

Verify that the account in use by SQL Server Agent has a login at the secondary server with sufficient permissions.

Make sure a login is created at Windows level with proper access to destination server.


Made a backup of the database, restrored it.

In the new database the invalid, nameless view has been dropped by pgadmin.


How can I do a differential query (delta plus/minus) telling me what rows are in view A that are not in view B and vice versa?

You can use a FULL OUTER JOIN for this

WITH T1
     AS (SELECT trantype,
                product_code
         FROM   vwVIEW1
         WHERE  KEY = 'DEMO'),
     T2
     AS (SELECT trantype,
                product_code
         FROM   vwVIEW2
         WHERE  KEY = 'DEMO')
SELECT *
FROM   T1
       FULL OUTER JOIN T2
         ON T1.trantype = T2.trantype
            AND T1.product_code = T2.product_code
WHERE  T1.trantype IS NULL
        OR T2.trantype IS NULL


How to get the name of the current database from within PostgreSQL?

The function current_database() returns the name of the current database:

 SELECT current_database();

It's an SQL function, so you must call it as part of an SQL statement. PostgreSQL doesn't support running functions as standalone queries, and has no CALL statement like some other SQL engines, so you just use SELECT to call a function.


So many columns in a primary key sounds strange, at least.  That means to me that the data is not really adhering to a sensible uniqueness definition - this is more often than not like this in the case of logs.  For this reason, I wouldn't even try to find a PK here, just define indexes based on the queries used against the table.  One could call this opportunistic indexing ;)

Note that in general, in a relational models PKs are very important.  Here it is more the case the data is not really relational, only stored in a RDBMS.


pt-query-digest slowlog output format looses query cache hit information?

It appears to be a known bug that's already been reported since 2013.

So the actual answer to my question is no: by now it's impossible not to loose this information when outputting a slowlog with pt-query-digest.


how to convert my PostgreSQL database table into CSV?

On Windows, PostgreSQL runs as "Network Service". "Network Service" will need write permissions to c:\tmp if you want it to write there.

Generally I recommend using psql's \copy command instead of copy, as it doesn't need superuser permission in the db, and can write wherever your shell user can write.

Open up cmd:

> cd c:\tmp
> set PGDATABASE=$nameOfYourDatabase
> set PGUSER=$pgUsername
> set PGPASSWORD=$thePassword
> psql

> \copy persons to 'persons_db.csv' with (format csv, header true);
> \q


How to convert a variable to be used in where clause

Since the data type of the job_id in the msdb.dbo.sysjob table is uniqueidentifier.

So you need to create the @job_id_char as a uniqueidentifier variable, like
DECLARE @job_id_char uniqueidentifier
then only it can be used in the WHERE clause of msdb.dbo.sysjobactivity table.

Or you can JOIN the tables by the the job_id like

SELECT sja.start_execution_date, .....
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
WHERE ..... -- condition related sysjobs tables


How To Migrate From MSDE 2000 to SQL Server 2008R2 Express

Relying on the service to shut down gracefully and detach your database files cleanly is risky. I have often seen this result in corrupted files that can not be used elsewhere, and which fail to start up even when starting back up the source instance which corrupted them. Moving the files after detaching them poses a similar risk - if they get damaged in or after transit, what are you going to fall back to?

If you don't have a backup of the database, either approach could result in disaster, since you are left with zero copies of your database.

If you do have a backup of your database, copy that, and restore it. If the backup isn't recent enough, create a new one. This is a much safer approach because your original database is still intact. If something goes wrong with the file during transit, or there are issues trying to restore it on the destination server, you can always start over - having lost nothing but time.


Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on?

You should not have your backup files on the SQL server as you would lose those backup if ever there is an issue with the SQL server.

Best practice would be to store your backup outside of the SQL Server (on a shared folder for example) that can be accessed by both node of the AG.

As the primary node can switch from one or the other node, you should code your backup job to only backup on the "prefered" node (which will be set in your AG).

I would also recommand you to use something else then SQL Maintenance jobs (have a look at : https://ola.hallengren.com/)

Usually, the backup are running on the primary node as you cannot do diff backup on a secondary node and as the secondary node data files could be behind the prod one (even in synchronous mode).

For the cleanup job, you could simply use a powershell script that will delete files from that shared folder.


Azure SQL | Granting Security Groups DB Role permissions

After more reviewing of this question, I was able to find that the syntax to add Security Groups in Azure SQL is below
CREATE USER [Group Display Name] FROM EXTERNAL PROVIDER;

You can confirm with one of Microsoft's articles
To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:
CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
And here is a second article that covers this topic. There is no CREATE GROUP syntax. To add a Sec Group it's done by using the CREATE USER syntax.
After which, you can proceed to run the ALTER ROLE command and use the display name of the Group to add the Group to the database role.


all the guys..

First of all, really appreciate to you all for replying...

When I input the follwing

select   file_name,
         file_id,
         tablespace_name,
         status,online_status
from     dba_data_files
order by tablespace_name;

The output is

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE STATUS    ONLINE_
---------- ---------- --------- -------
/home/oracle/db/oradata/ORAC12/ID/sysaux01.dbf
    28 SYSAUX     AVAILABLE ONLINE

/home/oracle/db/oradata/ORAC12/ID/system01.dbf
    27 SYSTEM     AVAILABLE SYSTEM

/home/oracle/db/oradata/ORAC12/ID/ID_user01.dbf
    29 USERS      AVAILABLE ONLINE

There is no such as "VOLUMT" tablespace. When I re-try to create a "TEST01" tablespace, eventhough it's already deleted, it shows 'the file exists already'..

When I get into the directory, there are other SID's as well. And, one of them has "VOLUMT". However, they are different "SID" so they can have same name of tablespace, isn't it?


Table values being cut-off by table lines

You should format your label so that there is a background color of white (instead of default none).

Right-click on one of the labels and select Series Label Properties.  Select Fill.  By default this is set to No Color.  Change it to White.  Click OK.  That should take care of it!

ADDITION: It may be possible to write an expression to change the distance between the label and the bar.  When you select one of the labels, check out the properties window.  You would need to play around with SmartLabels - specifically MaxMovingDistance and MinMovingDistance.  You would need to have chart lines at specific intervals and then build the expression around the value being within one of those intervals.

I know that is short on details, but hopefully the idea sketch gets you started in the right direction!


How to get the column name having a particular value from a SQL Server table?

This can be achieved with a CASE..WHEN combination, with one WHEN for each column of the table in question.  I use a temporary table to demonstrate the technique but it works just as well for normal tables.

drop table #T;

create table #T(PK int, c1 int, c2 int, c3 int);

insert #T values (1,1,3,5);

select
    PK
    ,case 5  -- The particular value you're looking for
        when c1 then 'c1'
        when c2 then 'c2'
        when c3 then 'c3'
        -- one WHEN for each column of the table
        else 'not found'
    end
from #T;

Most often the CASE is written with the column name given and various values listed.  I've inverted this so multiple columns can be checked against a single, known value.  This could be achieved just as well using the longer form of CASE i.e.

CASE
    WHEN C1 = 5 THEN 'C1'
    WHEN C2 = 5 THEN 'C2'
...

A second option is UNPIVOT, which turns columns into rows.  It is well documented in Books Online, though it may take some concentration to follow the examples properly.  There are several questions on SO and dba.SE on this topic if you want examples.  The output from an UNPIVOT will look something like this:

PK  ColName  ColValue
 1  C1       1
 1  C2       3
 1  C3       5

You can query this with a simple WHERE ColValue = 5.

I have difficulty understanding what use you have for this as each column in a table should have different meaning.  A value of "5" in the "Age" column is very different to "5" in the "Shoe Size" column.  They cannot be meaningfully compared or combined.  Perhaps you have a one-to-many relationship hidden in your schema i.e. you have a table with columns named, say, Value1, Value2, Value3 etc.  If so I'd suggest you re-examine you design if at all possible.


You can get it by using group_concat()

create table t (notes varchar(200), id int, name varchar(100), emp_Id int);

insert into t values ('1. Finish Designing Queries', 10, 'John', 2);
insert into t values ('2. Finish Designing Queries', 10, 'Jack', 5);
insert into t values ('3. Other note', 20, 'Jack', 5);

select notes, id, group_concat(name, ':', emp_id)
from   t
group by id;

notes                       | id | group_concat(name, ':', emp_id)
:-------------------------- | -: | :------------------------------
1. Finish Designing Queries | 10 | John:2,Jack:5
3. Other note               | 20 | Jack:5

dbfiddle here


Dealing with data stored as arrays in a MySQL DB

Just as I was about to post a comment that this looked like no serialization format I've ever seen, I had a lucky flash of insight.  (What programming language would make it altogether too easy to stash an array in a string like that? Ah...)

I think you may find that what you're looking at is the output of the serialize() function in PHP... the companion function is unserialize().

I'm not aware of a mechanism for deserializing this natively in MySQL (but then again, I wasn't aware of the whole thing a few minutes ago)... but if I had to do it natively in MySQL, I would go to the source code of common_schema for ideas and helper functions like get_num_tokens(), which returns a count of the number of tokens found in a given string of delimited text.  There is genuine outside-the-box genius lurking in common_schema.

Untwizzling scalar values into rows is not something easily done in MySQL but prettify_message() in common_schema provides an example of how it is technically possible... by splitting the strings and writing what you find in to a new table, it seems possible and maybe even borderline practical if you're not dealing with a massive data set.  Figure that part out and you could theoretically even build a trigger to keep your more-properly-structured table syncronized whenever one of those serialized columns is updated.

Or you could write something in php to read from the database, deserialize the arrays, iterate them, and stuff a table, but doing it natively in MySQL would be much more fun and at least offers the potential to keep another table always-current when you need it without an external script.


IS NOT DISTINCT FROM vs row-wise equality with =

This behavior is documented under Composite Type Comparison,

  The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors (as in Section 9.23.5) or comparing a row constructor to the output of a subquery (as in Section 9.22). In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.

This essentially means that the standard behavior can only be seen in the following syntax (ROW() is the row-constructor):

SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON ROW(t.*) = ROW(g.*);

versus,

SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON ROW(t.*) IS NOT DISTINCT FROM ROW(g.*);

Just using the ROW constructor with the type will not work either ON ROW(t) = ROW(g)


Auditing sysadmin members by a specific group

One option may be to add individual sysadmins as users to the databases in question, add those users to a custom role with no explicit permissions (effectively like public), and setup the database audit specification to track actions for the custom role.


Postgres optimizing join of 2 large tables

First, about the count query: the explain gives me impression that the query takes time due to IO. The table fco_chr_res contains more than 34M rows and one row in index is around 20 bytes, which results into more than 600MB read from the index. This definitely will not fit work_mem, which is 4MB by default. So it is good to pay attention how good is the disk IO.
It was mentioned that it is timeseries data, i.e., it can be assumed that no data arrives with old timestamps. In such case count on old data can be pre-calculated and stored in an aggregation table. I don't think MATERIALIZED VIEW can help here, since refreshing it to include newly arrived data will re-calculate entire view. Continuous aggregates in TimescaleDB are designed to deal with aggregates with time bucketing in timeseries data, however joins are not supported (which I discuss later).
Another note is that the explain plan shows that the estimated number of rows assumes that there are no referential constraints between the tables, while the comments clarify that they are. Thus introducing foreign keys might help query planner to calculate better estimates.
As discussed in comments applying count is not a common case, instead columns from all three tables will be returned from a query with the same joins. If the query projection will include columns outside indexes, then the query plan will be different and it might increase the amount of data processed significantly. Notice, the indexes are large and close to the actual tables in size, since they include almost all columns. You might want to reconsider them, but they will be needed for foreign keys and duplicate elimination.
Such query produces millions of rows, which is incomprehensible for humans. Thus there is a question how the join result is used and analysed. In particular, will there be grouping and aggregations and/or selections applied to the join result?
Since the question explicitly asks about TimescaleDB and I affiliated with it, I expand my answer with TiemscaleDB. If the data and its usage are timeseries related such as data are inserted mainly in time order and queries do aggregation on time or limited to time intervals, then TimescaleDB can be a good fit and be used to get better performance and to utilise some timeseries specific features. TimescaleDB optimises how data are stored, so it is efficient to insert recent data and to query data in a time interval. Performance can be further improved by using community features: continuous aggregates and compression. In particular, continuous aggregates materialise aggregation results continuously by adding results on new data on schedule without recalculating materialized results if not need. However, a continuous aggregate can be defined on a single timeseries table (hypertable) and no joins can be used. The reason is that in general changes to one of the table (e.g., fco_assets) can affect materialised result, which will waste previous work. A possible solution is to create continuous aggregates on each timeseries table (hypertable) and then join the continuous aggregates in queries. To further investigate if TimescaleDB is applicable, I suggest to check TimescaleDB support slack, where TimescaleDB users discuss also such issues.
Summary of recommendations from the above:

Check disk IO and see if it can be improved: a better disk(s) or a cloud machine with better disk IO;
Materialize the result of count for old data. The automation will require some work;
Define foreign keys;
Reconsider indexes;
Understand better your application if it fits timeseries use cases supported by TimescaleDB.


Schemas are not only a great security tool (which is reason enough to use them), but they are also perfect for logical separation.  And it seems as though this is what you are practicing.

Even if the current requirement doesn't need special security, say down the road all of the database objects that are related to Auditing should be secure to a database role.  If these objects were scattered throughout the dbo schema, then you would have to explicitly deny permissions on the individual objects.  But with the Audit schema, you do a single deny and you're set.

I personally practice the use of schemas.  Like all things in databases, though, there is a happy medium.  I wouldn't create a schema for each granular aspect of the data layer.  There is such thing as too many schemas and separation.  But I'm guessing you aren't anywhere close to that.


There is a wikipedia database you can download from the following link:

Wikipedia:Database download

  Wikipedia offers free copies of all available content to interested users. These databases can be used for mirroring, personal use, informal backups, offline use or database queries (such as for Wikipedia:Maintenance). All text content is multi-licensed under the Creative Commons Attribution-ShareAlike 3.0 License (CC-BY-SA) and the GNU Free Documentation License (GFDL). Images and other files are available under different terms, as detailed on their description pages. For our advice about complying with these licenses, see Wikipedia:Copyrights.


Passing values to variables defined in SSIS package at run time

You can have the values specified in a .dtsconfig file, or you can set them using the /SET option of dtexec.exec


Non-sequential Primary Key performance in PostgreSQL 9.3

I have 3 solutions for you:
Direct reference of sequence and using concat
One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here:
https://stackoverflow.com/a/17925601/4206293
Using a UUID
Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html
Use a trigger for these solutions
For both solutions: you can use a trigger to set the primary key.
Example:
-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger()
RETURNS TRIGGER
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table
INSERT INTO test (name) VALUES ('test text');

you get your primary key '1001'
Using MINVALUE and MAXVALUE
Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space:
CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;


T-SQL (SQL Server 2012) Update based on lookup table

My first question is:

  How would you write a query (SELECT) with a similar pattern?

I can see several problems with the UPDATE statement:

UPDATE on 2 tables (tblTitan t, tblLevel l)
No FROM clause
Old JOIN syntax (TableA, TableB VS FROM TableA INNER JOIN TableB ON ...)
Invalid (missing) expression in the WHERE clause (usage of < and > operators like a BETWEEN operator: A > B AND < C)

A SELECT statement would be like this:

SELECT titanId, titanName
    , t.titanLevel, t.titanStep
    , l.levelNo , l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience >= l.levelExperienceMin
    AND t.titanExperience <= l.levelExperienceMax ;

tblTitan t, tblLevel l is replaced by a proper INNER JOIN clause
the WHERE clause is replaced by the ON clause in the INNER JOIN
the invalid BETWEEN like WHERE clause is replace by 2 expresions with < and >

From this valid query, the FROM ... JOIN ... WHERE part can be kept and the SELECT can be replaced by an UPDATE t and a SET clauses.

The UPDATE statement can be writen like this:

UPDATE t
    SET t.titanLevel = l.levelNo
        , t.titanStep = l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience >= l.levelExperienceMin
    AND t.titanExperience <= l.levelExperienceMax
-- WHERE titanId = ...;

Sample data with 3 levels and 3 titans:

DECLARE @tblTitan TABLE (titanId int, titanName varchar(15) , titanExperience int, titanLevel int, titanStep int)
INSERT INTO @tblTitan(titanId, titanName, titanExperience, titanLevel, titanStep) VALUES
    (1, 'Bob', 110, 55, 66)
    , (2, 'Sam', 220, 77, 88)
    , (3, 'Isa', 50, 22, 33)

DECLARE @tblLevel TABLE(levelId int, levelNo int, levelStep int, levelExperienceMin int, levelExperienceMax int)
INSERT INTO @tblLevel(levelId, levelNo, levelStep, levelExperienceMin, levelExperienceMax) VALUES
    (1, 1, 1, 0, 100)
    , (2, 1, 2, 101, 200)
    , (3, 2, 1, 201, 300)

Output of SELECT before UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 22            | 33        | 1         | 1
1       | Bob       | 55            | 66        | 1         | 2
2       | Sam       | 77            | 88        | 2         | 1

Output of SELECT after UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 1             | 1         | 1         | 1
1       | Bob       | 1             | 2         | 1         | 2
2       | Sam       | 2             | 1         | 2         | 1


Correct way to uninstall SQL Server 2008 ( or above )

That's pretty much all there is to it. You may want to backup or transfer your database files/backups before the un-installation process, but getting rid of the application is that simple.

As per Mirosoft Support KB 955499,

  Open the Programs and Features item in Control Panel.To do this, click Start, type appwiz.cplin the Start Search box, and then click
  appwiz.cpl in the Programs list.
  Select the SQL Server component to
  uninstall, and then click Change/Remove. This will start the SQL
  Server Installation Wizard.
  Setup Support
  Rules will run to verify your computer configuration. To continue,
  click Ok.
  On the Select Instance page, use the drop-down box to
  specify an instance of SQL Server to remove, or specify the option
  to remove only the SQL Server shared features and management tools.
  To continue, click Next.
  On the Select Features page, specify the
  features to remove from the specified instance of SQL Server. To
  continue, click Next.
  Removal Rules will run to verify that the
  operation can complete successfully.
  On the Ready to Uninstall page,
  review the list of components and features that will be uninstalled.
  The Setup Progress page will display Setup status.
  On the Completing Setup page, click Close to exit the Installation Wizard.
  Repeat
  steps 2-9 until all SQL Server 2008 components have been removed.


MySQL architecture for different word clouds

CREATE TABLE words (
    user_type ENUM('anonymous', 'users', 'organizations') NOT NULL,
    group_id SMALLINT UNSIGNED NOT NULL,
    word VARCHAR(...) NOT NULL,
    ct MEDIUM INT UNSIGNED NOT NULL,   -- how often user used the word
    PRIMARY KEY(user_type, user_id, word)
) ENGINE=InnoDB;

This will let you build an overall cloud, or a cloud for an individual user/organization.

SELECT word, SUM(ct) FROM words;  -- overall

SELECT word, ct
    FROM words
    WHERE user_type = 'organization'
      AND group_id = 123   -- for a particular user

You have not really mentioned a requirement any date.


Opened_tables value and tweaking table cache

Dived Opened_tables by Uptime to get tables opened per second.  But even that is not very useful.

Much more useful are the 3 values of SHOW GLOBAL STATUS LIKE 'table_open_cache%';  More than a few misses or overflows per second is worth noting.

There are many other metrics; provide the data that Wilson mentioned; see can dig further.

Only one FLUSH TABLES since boot up?  No big deal.

mysqladmin status does not provide much useful info.

Be aware that the values in STATUS have different flavors, has many flavors:

Counters -- monotonically increasing since startup.  In most cases, dividing by Uptime is appropriate.  Eg: Opened_tables; Com_%.
High-water-marks -- eg:  Max_used_connections
Current values -- eg: Threads_running (usually 1, namely the query that is fetching the values)


That just means it can't find that file at the moment. Either wait for when the drive to be available, or, as Martin suggested, try setting the database offline: ALTER DATABASE FOO SET OFFLINE

If that works, you should be able to follow it up with a DROP: DROP DATABASE FOO.

Then delete the files at your leisure.


I don't know exactly if 5.5 require much memory than 5.0 but look this example :

In 5.0 MySQL Official Documentation:

innodb_buffer_pool_size : The default value is 8MB.

In 5.5 MySQL Official Documentation:

innodb_buffer_pool_size : The default value is 128MB.

If you paste your both "show global variables" results (5.0 and 5.5) we'll can see exactly what variables have gone up...

Max.


How to connect to a named SQL Server instance without specifying its name?

If there is no default instance, then you could force the named instance to listen on port 1433.  SQL Server Configuration Manager, then navigate to the networking bit and turn off dynamic ports.

This article goes into details on how you can do that for multiple instances running on the same server, but it gets complicated fast.  How to Add a Hostname alias for a SQL Server Instance.

So there are the methods, but why do you want to do it that way?


First, create a password policy (profile) that captures your time period. Then assign that profile to the user.

CREATE PROFILE SHORT_LIFE_PROFILE LIMIT
    PASSWORD_LIFE_TIME 3
    PASSWORD_GRACE_TIME 1/24;

ALTER USER rdj7 PROFILE SHORT_LIFE_PROFILE;

The password grace time is the number of days the user receives a "soft warning" saying their password will soon expire. During that period, they can still log in with the old password. After that time runs out, the password has expired and they are done. In this case, I've set that to 1 hour.

See the oracle docs for other parameters you can set in this profile.


Tokudb 5.7 [Warning] The partition engine is deprecated and will be removed in a future release

It remains to be seen.

Oracle's MySQL 8.0 is

Moving partitioning support into the InnoDB engine.
Removing MyISAM.
Building a "Data Dictionary" (in InnoDB tables) that replaces .frm files, etc.

This leads to action items for the "forks" -- MariaDB, Percona, Aurora, etc.:

Continue to keep MyISAM, TokuDB, Aria, etc.?  Or abandon?
Abandon Partitioning for that engine?  Or re-implement it?
Implement usage of the Data Dictionary for that engine?  Or keep old mechanism?

Attend Percona Live in April; there will be plenty of Percona, etc, folks there to address the question (if they have decided by then).


SQL Server: Backup failed because there is a mismatch in file metadata

When we do a DBCC, no errors get reported, DBCC CHECKDB ('MyDB', repair_allow_data_loss) also does not return any issues

This is really interesting as DBCC should report errors if metadata is corrupted. Have you tried to run a DBCC CHECKCATALOG; ?

Backup failed because there is a mismatch in file metadata for file 65537

I know it sounds basic but have you checked what database does file 65537 belongs to? Make sure the DBCC CHECKDB and DBCC CHECKCATALOG are run against the good database. But judging from the fact that you have problems with your backups, I guess you are looking at the right database. Unless it would be msdb? Since backup sets are in msdb so when you do a backup it inserts rows in msdb also.
You should check Paul Randall's blog, he has a post on fixing metadata corruption without a backup :
https://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-metadata-corruption-without-a-backup/
Did you try to restore your backups to see if they are corrupted?
Do you have filestream enabled? From what I'm reading, file 65537 would be related to filestream.
https://www.sqlshack.com/sql-server-filestream-database-corruption-and-remediation/
There's also a great blog by Paul Randal on filestream you should check it out.
https://www.sqlskills.com/blogs/paul/filestream-directory-structure/
-------------- EDITED for FileStream corruption -------------------------
You should check the answer on this question that explains what happens when Filestream is corrupted.
https://stackoverflow.com/questions/15302673/handle-sql-filestream-data-corruption-and-backup
And in the link on sqlshack, there are steps to recover a database from filestream corruption.


What I am looking for is why I am getting a DELETE/DELETE deadlock.

It appears the deadlock occurs because:

spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422
spid 166 ecid 3 requests an update (U) page lock on the same page, and is blocked
spid 54 ecid 2 requests an update (U) page lock on the same page...

Pages are being prefetched for the query, with update locks acquired by ecid 0. That is step 1 above. In step 3, a child thread of the same parallel query (ecid 2) requests the same lock. Normally this would not be a problem. SQL Server knows ecid 0 and ecid 2 are threads of the same parent process. Unfortunately, step 2 gets in the way of this, and a deadlock results.

That said, you shouldn't really care much about why the deadlock occurs, the important question is how do you avoid it. The answer is to provide an efficient access path for the DELETE. The statement needs to find rows WHERE Username = @P1 AND UserDate = @P2, so you should have an index keyed on these columns.

And of course you do have such an index. The real question is why your problems started occurring after you added filtered indexes.

The answer to that is extra column information is needed to locate the filtered index rows to delete (and to check their predicates). If the query uses a narrow/per-row execution plan, the execution engine is unable to fetch the extra columns in the Clustered Index Delete operator, as it would in a wide/per-index plan.

You can find more details about that, and a worked example in this blog post.

In this case, the column information needs to come from the part of the plan to the right of the Clustered Index Delete, and so a parallel clustered index scan is used, and you get a slow query with high deadlock potential.

The answer is to do one of the following:

Remove the filtered indexes
Add filtered index key/include/predicate columns to the existing name/date index
Force a wide update plan (no supported way to do this)
Run the query under snapshot isolation (not RCSI)

Option 2 would be my strong preference.

Option 4 (thanks Jack Douglas) has the advantage of removing deadlocks, and ought not cause any "update conflicts", given the disjoint nature of the changes, but it does require enabling snapshot isolation at the database level, explicitly changing the isolation level, and will not fix the underlying issue: you'll still end up with a wasteful parallel table scan, where a nice index seek is what you really want.


Can I use the database and query it while full-text indexing?

Your comments note that you're specifically talking about full-text indexing.

You can indeed query the database while a full-text index is being created. Full text indexes are created in the background on SQL Server 2005, 2008, and 2008R2.

You can continue to query the database using the LIKE operator, although of course the queries won't be as fast as a full text index, and your database server's performance may be degraded while the index is being created.


(reposting swasheck's comment as reply)

sqlite3 database1.db
> ATTACH DATABASE 'database2.db' AS database2;
> INSERT INTO mytable SELECT * FROM database2.mytable;


How can I change TIMESTAMP to TIMESTAMP(3)?

I'm answering for my own question.

I found an answer solves the exact problem.

https://stackoverflow.com/a/23671577/330457

Shortly, I needed to specify the same type for DEFAULT.

ALTER TABLE <table>
MODIFY COLUMN <column>
TIMESTAMP(3)
NOT NULL
DEFAULT CURRENT_TIMESTAMP(3) -- See?


Improving lots of Left Joins ( SQL Server 2008 - procedure )

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join?

Something like this:

SELECT *
INTO #temporary
FROM (
    SELECT CodJob, Quant, TipoCons, Perc
    FROM tblConsolidadoJob
    ) AS SourceTable
PIVOT
(
    MAX(Perc)
    FOR TipoCoins IN ([Amostra],[TGWTotal],[TGWOnly],[TGWDesig],[Desvio],[DesvioSD],[DesvioD])
) AS Pivoted

and then use #temporary table on your left join instead of tblConsolidadoJob. I might be wrong tho. But this could be worth trying?


dual table postgres doesn't return timestamp

Don't create dual tables

Why are you using a dual table in PostgreSQL to begin with? PostgreSQL has an implicit dual table if there is no from-clause.

SELECT now(); -- works fine.

You can also

SELECT * FROM now(); -- works fine.

For more information, see my answer to Why does SELECT count(*) return 1?

Creating a dual table should still work

You'll never need a dual table with PostgreSQL, and you shouldn't create one: it just convolutes the syntax. It's also foreign for most PostgreSQL users. That said, it's easy to demonstrate that it works..

test=# CREATE TABLE dual AS ( VALUES (true) );
SELECT 1
test=# SELECT now() FROM dual ;
              now
-------------------------------
 2017-10-05 15:34:34.359092-05
(1 row)


Mixed Editions in a 3 Node SQL Server Always on Availability Group

You might missed that the Availability Groups are only available in Enterprise Edition.

In you scenario (with mixed edition), you would neither able to do following AG Configuration:

Availability Groups
Required all replicas running Enterprise Edition

Basic Availability groups (limitations)
It limit to two replicas only

Distributed Availability groups
Standard Edition only support Basic Availability Groups and Basic Availability Groups cannot be part of Distributed AGs


You should be able to use a subquery to get the max date and then join that to you table to return the remaining columns:

select a1.*
from log_AP a1
inner join
(
  select max(DialDateTime) as max
  from log_AP
  group by date(DialDateTime)
) a2
  on a1.DialDateTime = a2.max


How to use a avg expression in the WHERE between clause?

Can you try this instead? Instead of having it in the WHERE clause, use it in HAVING clause.

SELECT u.id, AVG(rating_value) avg_rate
FROM dt_tutor_comments u
GROUP by u.id
HAVING AVG(rating_value) BETWEEN 1 AND 2


Change Data Capture for Asynchronous Secondary Replica

Change Tracking and Change Data Capture are, while having similar goals, actually separate features.  For AlwaysOn Availability groups, the limitations indicate that you can't use the synchronous Change Tracking feature at all, though the asynchronous Change Data Capture is possible to enable: It's just not a feature Microsoft will support for Active Secondaries.

Generally, things like that aren't supported because there exists a supported way to do so.  In this case, enabling CDC on the primary node of an AlwaysOn Availability Group is completely supported, allowing the captured changes to be replicated along to the Active Secondaries, so would likely be the path you should explore for your SSIS needs on the secondaries.

You can read more about the differences between Change Tracking and Change Data Capture here, and more about setting up CDC on the primary replica here.


Many columns vs few tables - performance wise

Assuming we are talking about 1:1 relationships among all tables.

Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate (redundant) index on each of these columns ... Size does matter for performance.

This is even true if many columns are NULL in most rows because NULL storage is very cheap:

Configuring PostgreSQL for read performance

While retrieving all columns a single table is substantially faster than 5 tables joined together. It's also much simpler. Five tables may be tricky to join if not all rows are present in all tables. With WHERE conditions targeting a single table, it's easy enough to append other tables with LEFT JOIN. Not as trivial if you have predicates on multiple tables ...

Vertical partitioning may still improve performance of certain queries. For example, if 90 % of your queries retrieve the same 5 columns out of the 65 available, this would be faster with a table just holding these 5 columns.

What is retrieved from disk during a query? (my answer)

OTOH, you might be able to cater for such queries on a few selected columns with a "covering" index allowing for index-only scans.

Another candidate for vertical partitioning: If you have lots of updates on just a few columns, while the rest hardly ever changes. It might be considerably cheaper to split rows in such a case, since Postgres writes a new row version for every update. There are exceptions for big values stored out-of-line ("TOASTed"). More details:

What is retrieved from disk during a query? (Daniel's answer)
Update all columns from another table

It really depends on the complete situation. If in doubt, go with the simple solution of having a single table, especially if it portraits reality well: In your example, those are all attributes of a car and make sense together.


When running `make installcheck` I get can not connect to database

If you're trying to build an extension on Ubuntu, you may have to set the port to isolate which version of PostgreSQL you wish to test the extension on with PGPORT. You can see here make install check is connecting on 5433 with the socket name .s.PGSQL.5432,

You likely want port 5432

make PGPORT=5433 installcheck


How does mysql optimiser chooses a composite Index

1) SELECT * FROM tbl WHERE affiliate_id = 300;
2) SELECT * FROM tbl WHERE funnel_id = 300;
3) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300;
4) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300 AND created_timestamp BETWEEN 600 AND 700;

INDEX(affiliate_id, funnel_id, created timestamp) handles queries 1,3,4, but not 2.  It is slightly suboptimal for 3 and 1 because it is bulkier than INDEX(affiliate_id, funnel_id) or INDEX(affiliate_id)
Depending on how/when you added the FKs, MySQL is sometimes smart enough to not add a new index because it can see that an adequate one exists.  Do SHOW CREATE TABLE to see the collection of indexes that exist now.  If you see some redundant indexes, DROP them.
If you are creating the table fresh, then this order might be best:

Create this table with INDEX(affiliate_id, funnel_id, created timestamp) and INDEX(funnel_id).  Create other tables and their optimal indexes.
Then add the FKs.
Get rid of any shorter indexes which are a prefix of a longer one.  The FKs should remain happy that there is some index starting with the column it needs to check.

If you provide CREATE TABLEs for the various tables, plus a few INSERTs.  I may play around with it.
Back to your question of what the Optmizer does...
When only x is needed, and given a choice between INDEX(x) and INDEX(x,y), the optimizer will pick the "smaller" index, namely (x).  This is not always optimal.
An example is INDEX(lat), INDEX(lat, long) and WHERE lat BETWEEN ... AND long between ....  In this case the query execution would take advantage of the 2-column index for ICP (Index Condition Pushdown), but it seems to be too dumb to realize it.  It picks the 1-column index and has to work harder to check long.  (This issue may vary with versions of MySQL and MariaDB; there have been a lot of changes in the area.)


Advice on a basic design, first time database design

Responses to your individual questions

If I want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the same member listed twice?

If —as stated in your specifications— a Person can be a Member of only one Club, and you are interested in storing this datum simply as true or false, one option is adding a BIT(1) or a BOOLEAN (TINYINT) column to the Person table, and you may wish to call this column IsClubOwner. In this way, you can register the fact that a determined person is a club owner exclusively one time. Apart from that, this method also allows the possibility of retaining several people as owners of the same club occurrence. You can see a logical level depiction of this approach in Figure 1.
However, you are looking for the best approach and, according to my experience, such approach entails the development of a much more expandable and versatile structure. In this respect, follow the progression of a modeling exercise for these and other points below, in the sections entitled “Covering your remaining specifications”, “Person as Member of multiple Clubs” and “Member and Owner as separate entity types”.

Should I put all my tables on automatic increment of an id or would this be a bad idea? (benifits/downsides?)

If you face an explicit requirement that indicates the definition of a table with a column of such characteristics, and that column has a valid contextual meaning or serves a particular purpose like being a surrogate for a wide natural PRIMARY KEY (PK), then yes, you should proceed that way.
Otherwise, if you do not have said requirement, I consider it would be unnecesary since you have to store and manage a meaningless extra column and (perhaps?) also an additional INDEX in your database.
As usual, you have to analyze each case along with its overall repercussions in order to decide how to carry on.

If I add foreign keys in the foreign keys tab, will these automatically correspond to the correct table or do I have to add these to the columns also?

In this regard, my advice for you is to create your database structure manually, to code your own DDL statements until you get a firm grasp of the subject. If you do so, it will be easier for you to understand the processes that graphical tools are performing “under the hood”.
To me, for instance, creating a statement like the following:
CONSTRAINT FK_PersonPhoneNumber_to_Person FOREIGN KEY (PersonId)
REFERENCES Person (PersonId)

Is much more instructive than using GUI tools to execute this kind of tasks, especially now that you are building your first designs.

And my probably most noobiest question of all... Do I put the foreign keys of phone_number and email in their respective table linking to a person_id or should I put phone_number and email ids in the person table?

Personally, I think that this and all your other questions are completely valid and well contextualized.
Returning to the technical aspects that concern us, this precise inquiry offers a good opportunity to review the two following assertions:

A Person can be reached through zero-one-or-many PhoneNumbers
A PhoneNumber can be used to reach one-to-many People

So, one can conclude that there is a many-to-many relationship between Person and PhoneNumber, therefore, I suggest the creation of an associative table named PersonPhoneNumber to represent said relationship in your database. The PK of this table should be composed by two different columns: PersonId (a FOREIGN KEY [FK] pointing to Person.PersonId) and PhoneNumber (a FK that makes reference to PhoneNumber.Number). For a logical level description of all of the above, see Figure 1.
On the other hand, let us examine the two propositions that follow:

A Person can be contacted via zero-one-or-many EmailAddresses
An EmailAddress can be used to contact exactly one Person

Then, yes, you should set a FK referencing to Person from the EmailAddress table, and this table should have a composite PK too, which would be comprised of the columns PersonId and Address. In this manner, you can ensure that the same combination of EmailAddress.PersonId and EmailAddress.Address can be inserted only once.
If you also wish to ensure that a given EmailAddres.Address can be stored in one sole row, you just have to establish a UNIQUE CONSTRAINT for this column.
Proposed logical data models
In order to expose my suggestions more clearly, I included four distinct IDEF1X[1] logical models that are shown in Figure 1, Figure 2, Figure 3 and Figure 4. I will provide an explanation of the most relevant features displayed in each one of them in the corresponding sections. You can as well download from Dropbox a PDF that integrates in a single model the majority of the elements under discussion.
Covering your remaining specifications
Relating People and Addresses
Let us inspect the two following (slightly reworded) assertions that are relevant to people and addresses:

A Person can only have one Address
An Address can belong to different People (couples or siblings)

So, to deal with these restrictions, you may opt to implement a data model similar to the one you can see in Figure 1.

Taking into account the referred model, you should follow the next steps:

Create a table called PersonAddress fixing a relationship between Person and Address. Set the columns PersonId and AddressId as the compound PK of this table.

Configure a UNIQUE CONSTRAINT for the PersonAddress.PersonId column to ensure that a specific value can be inserted in at most one row of said table. At the logical level, this circumstance implies that PersonAddress.PersonId has become an ALTERNATE KEY[2].

If the value of AddressId in a determined PersonAddress insertion attempt has not being already stored, then let the insertion continue, otherwise, when such value already exists in a row, you have to check that (a) the PersonId who has registered that AddressId is also registered as  the Marriage.WifeId if the PersonId is a male (datum derived via the Person.GenreCode) or (b) that the PersonId is the Marriage.HusbandId when the PersonId is a female (derived by virtue of Person.GenreCode, as well). If one of these conditions is met in the appropiate situation, then you should let the INSERT go on.

If the above conditions were not fulfilled, there is still a chance for a PersonAddress insertion try to be succesful. You have to check that the PersonId value that is involved in said insertion try shares at least one Progeny.ParentId with the PersonId that has already registered the PersonAddress.AddressId. If this condition is satisfied, then it means that they are stored as Siblings in the database, so this INSERT must succeed.

As in any relational database implementation, you should seriously consider executing your DML operations inside ACID Transactions so that you can protect the integrity and consistency of the data you are working with.
Attending the requirements you added in comments: Addresses and Phone Numbers serving equally Clubs and People
On condition that you want to give the chance for addresses and phone numbers to serve both people and clubs, you might make use of a supertype-subtype relationship. Here is an answer in which I give a more detailed treatment to this kind of structures, in case you are interested.
In the present scenario, you could define Person and Club as subtypes of a new entity named Party, a term commonly used in legal circles to stand for (a) a person or (b) a group of persons (as noted in sense no. 6). With this method, the relationships between Addresses (or PhoneNumbers) and People and Clubs would be defined through Party, the supertype. See Figure 2 for a depiction of this suggestion.

Party and Address
So we can read in this new model that:

A Party keeps zero-one-or-many Addresses
An Address is kept by one-to-many Parties

Thus, there is a many-to-many relationship involving Party and Address that is expressed by way of the PartyAddress entity.
Party and PhoneNumber
Furthermore, we can interpret that:

A Party is reached through zero-one-or-many PhoneNumbers
A PhoneNumber is used by one-to-many Parties

That is why I added the PartyPhoneNumber entity that describes the many-to-many association that takes effect between the Party and PhoneNumber entity types.
Party and Club or Person
Then, it can also be read that:

A Party is either a Club or a Person

Hence, Party supplies a connection from either Clubs or People to Addresses (or PhoneNumbers).
Person as Member of multiple Clubs
As @aldwinaldwin mentiones in his answer, if you want to provide the functionality for a person to be a member of multiple clubs, then you can include a table called ClubMember that would be acting as another many-to-many relaltionship, this time, naturally, interconnecting Person and Club.
I am going to add to the above that this table can also be useful in the goal of storing any concrete person as owner of multiple clubs, by means of the inclusion of the already mentioned IsClubOwner boolean column. In fact, one can say that this new table is the representation of an integral entity type in its own right.
As demonstrated in Figure 3, such table requires a composite PK made up of the columns ClubId and MemberId (a role name[3] given to PersonId), and these columns have to be defined also as FKs pointing, correspondingly, to Club and Person.

A more adaptable structure
Employing this setting you can also comply with the initial rule that states that a Person can be a member of only one Club, but you just have to add a UNIQUE CONSTRAINT to the MemberId column, so that a certain value can be entered in no more than one occasion. So, as you can note, this structure is much more adaptable that the one shown in Figure 1, since by dropping the UNIQUE CONSTRAINT (or INDEX) you would open the aforementioned functionality of permitting a person to become a member of different clubs at the same time.
Member and Owner as separate entity types
As you know, the storage several facts about the role carried out by a person as owner of a club —besides its mere existance in a true or false attribute— can be very advantageous. For example, you may want to keep the effective date in which a definite person became the  owner of a club, hence you can manage this situation by introducing a separate entity type called ClubOwner, as presented in Figure 4.

Once you construct a table based on this new entity type, you can add the fitting columns that represent the characteristics that come into play exclusively when a Person is the Owner of a Club. As depicted, this table would hold a PK composed of the FK columns referencing Person.PersonId and Club.ClubId, this way any combination of ClubOwner.OwnerId (or ClubOwner.PersonId, if you prefer) and ClubOwner.ClubId can be inserted in just one opportunity.
Of course, with this configuration you can still derive in boolean form if a Person is the Owner of a particular Club with the aid of a query that returns a scalar value which can be evaluated to true or false.

Notes
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical papers authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship theory, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.
2. An ALTERNATE KEY is an attribute (or a combination of attributes) that holds values that uniquely identify an entity occurrence but was not chosen as the PK of the pertinent entity type; each entity type can have zero, one or more ALTERNATE KEYS. In an IDEF1X model, they are are indicated as “AK” plus its respective number, e.g., AK1, AK2, etc. They are usually implemented in a SQL DDL structure via a UNIQUE CONSTRAINT (or a UNIQUE INDEX).
3. Role names are denotations (or aliases) assigned to FK attributes in order to express the meaning that they hold within the scope of their respective entity. Their usage is recommended since 1970 by Dr. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.


Here is a way to get the name of the month in French, then you can use CONCAT to construct the date however you want:

SET @month=month("23/05/2016");
SELECT CASE @month
    WHEN 1 THEN "janvier"
    WHEN 2 THEN "février"
    WHEN 3 THEN "mars"
    WHEN 4 THEN "avril"
    WHEN 5 THEN "mai"
    WHEN 6 THEN "juin"
    WHEN 7 THEN "juillet"
    WHEN 8 THEN "août"
    WHEN 9 THEN "septembre"
    WHEN 10 THEN "octobre"
    WHEN 11 THEN "novembre"
    WHEN 12 THEN "décembre"
END INTO @nom_de_mois;
SELECT @nom_de_mois;

Working example:

mysql> SET @month=month("2016-05-23");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CASE @month
    ->     WHEN 1 THEN "janvier"
    ->     WHEN 2 THEN "février"
    ->     WHEN 3 THEN "mars"
    ->     WHEN 4 THEN "avril"
    ->     WHEN 5 THEN "mai"
    ->     WHEN 6 THEN "juin"
    ->     WHEN 7 THEN "juillet"
    ->     WHEN 8 THEN "août"
    ->     WHEN 9 THEN "septembre"
    ->     WHEN 10 THEN "octobre"
    ->     WHEN 11 THEN "novembre"
    ->     WHEN 12 THEN "décembre"
    -> END INTO @nom_de_mois;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nom_de_mois;
+--------------+
| @nom_de_mois |
+--------------+
| mai          |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(date_format("2016-05-23", "%d"), " ", @nom_de_mois, " ", date_format("2016-05-23", "%Y")) AS date_in_French;
+----------------+
| date_in_French |
+----------------+
| 23 mai 2016    |
+----------------+
1 row in set (0.00 sec)


mysqldump is capable of dumping in three ways

SQL
CSV
XML

SQL
The standard format has directives in addition to regular table creation and INSERTs
The paradigm is always the same

CREATE TABLE
DISABLE KEYS
LOCK TABLE
INSERT INTO ...
INSERT INTO ...
...
INSERT INTO ...
UNLOCK TABLES
ENABLE KEYS

Even with the characters you are coding and delimiting around, please consider mysqldump also processes TEXT, BLOB, BINARY, and VARBINARY fields. Unless you remember to use the --hex-blob option, you could find your delimiter processing somewhat flimsy in the presence of those datatypes.
Another aspect to deal with is if you make each row a separate INSERT using --skip-extended-insert, thus increasing the amount of delimiters to parse and count.
CSV
You could export data in CSV format. You could even set the options for delimiting.

lines-terminated-by
fields-terminated-by
fields-enclosed-by
fields-optionally-enclosed-by
fields-escaped-by

This would eliminate the need to guessing the delimiters by presetting them.
XML
If you export the data as XML (using --xml), the anatomy of the dump may still be subject to the  issues around TEXT, BLOB, BINARY, and VARBINARY fields.
EPILOGUE
If you need to examine how data is batched by mysqldump, see my post How we take dump with batch of records option?
MySQL (eh, Oracle) has basically left it to the discretion of the Community Users to parse mysqldumps. Using --hex-blob has probably saved many developer headaches in order to simplify text processing of mysqldumps. This would relieve MySQL of having to document mysqldump output characteristics.
As for your utility you are coding, you are either going to see strings delimited or hex encoded. No need to sweat details past these two possibilities.
I just thought of something else: doing mysqldump from Linux vs. Windows. Keep in mind that whichever OS platform you use to run mysqldump. code for that platform only. Otherwise, you may need two little programs, one for Linux and one for Windows. (I won't mention MacOS at this point)


mysql_upgrade check if it's neccessary to run it

mysql_upgrade will generate a file informing if it was run on the datadir. If it was run for the specific version you have, the  mysql_upgrade_info file would contain the current version string and will prevent a new run of mysql_upgrade unless --force is used. Cat $datadir/mysql_upgrade_info file to know which version was ran on last.

Even if for some reason that file was missing, running mysql_upgrade is relatively safe- so much that most packages and init systems run it automatically on upgrade/start. I don't like to do it automatically, because I like to have control over when it is run- For example, it can take a very long time with many files, and it can create metadata locks due to the nature of the DDL commands it executes, but 99% of the installations can run it safely, assuming no other traffic is ongoing.

  DB is large enough, 900+ tables and about 150Gb of size

My 2TB database with 100K tables and 100K views takes around 30 minutes to run, to give you an idea (SSDs). My other 2TB servers with a few dozen tables just takes <10 seconds.

My recommendation is to just run it, unless you have long running queries on it. It can be killed in general in the worst case scenario. The problems when it is not run are worse (although it is true those normally only happen on major version upgrades or during the first minor versions within a major version).


All my database on the fail over server showing in "Synchronizing" state

Firstly, if you could drop the database from any one of replica, it means the database is not yet part of any of availability groups, before dropping the database it must be removed from availability group first.

Assuming that your current scenario has 2 nodes in 1 cluster, I would recommend first to add witness (preferred File-share from different server other than 2 nodes) in WSFC to ensure the quorum healthy, so that while you restarting 1 server there would be 2 votes available (1 Node, 1 File-share witness), and the WSFC could smoothly fail-over the roles (Availability groups) from one to another node.

For the synchronizing issue, please do the following

Make sure the network connection is stable between 2 nodes especially with endpoint port (Default: 5022) which you can identity from any availability group properties right click on AG Name -> Properties -> look at availability Replicas section (bottom) example: TCP://<IP ADDRESS>:5022. Ensure the failover mode is Automatic

Try Suspend data movement and Resume, you can do this for any one of the database that is already part of availability group
If step 2 completed without any error, verify the hardening of LOG by REDO worker is normal which you can identity with following query, result can describe with the delay between LAST SENT (Primary) - LAST RECEIVED  (Secondary)- LAST HARDENED (Secondary) which is not available in AG Dashboard

select  db.name,
        db.database_id,
        ag.name as GroupName,
        state_desc,
        recovery_model_desc,
        log_reuse_wait_desc,
        AGDB.truncation_lsn,
        Rep.replica_server_name,
        rep.endpoint_url,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        (redo_queue_size / 1024.0) as redo_queue_size_MB,
        last_redone_time,
        last_redone_lsn,
        DBRepStats.end_of_log_lsn,
        DBRepStats.last_sent_lsn,
        DBRepStats.last_sent_time,
        DBRepStats.last_received_lsn,
        DBRepStats.last_received_time,
        DBRepStats.last_hardened_lsn,
        DBRepStats.last_hardened_time
from sys.databases as db
    left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
    left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
    left outer join sys.availability_replicas as Rep on DBRepStats.group_id = Rep.group_id and DBRepStats.replica_id = Rep.replica_id
    left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4
go

If no luck with above steps, have a look at following sections

  "AlwaysOn_health" from Management -> Extended Events -> Sessions (via SSMS)
  SQL Error Log
  WSFC Log


InnoDB: Error: log sequence number is in the future!

I would try a full resync, i.e. SST, at this point.

Note that depending on your wsrep_sst_method (see description MariaDB knowledgebase) an SST may block the donor node (node1 in this case) while it's making the backup. The default wsrep_sst_method is rsync, which is blocking.

You should in theory be able to force an SST by removing the grastate.dat file from the datadir and then restart MariaDB, but I'm unsure if that works in your case. So if that doesn't work, then you could proceed as if you were adding a new node to the cluster. In this case, basically just wipe the datadir, run mysql_install_db, and try starting up the new node.


Unable to CREATE DATABASE: error while processing file dmanage.bsq

According to this thread, this error means I don't have enough space on my disk. I try the same request with lower size for my datafile and it works.

So, I have two solutions: adapt, in my request, the size of my datafiles or extend my logical volume.


Using another left join:

select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    list a
    left join msg b on a.dbname = b.dbname
    left join msg d on  b.dbname is null
                    and d.dbname = 'alldbs' ;

or a cross join (this requires an 'alldbs' row to be present, otherwise it will return 0 results.):

select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    msg d
    cross join list a
    left join msg b on a.dbname = b.dbname
where d.dbname = 'alldbs' ;

SQlfiddle

Both queries assume there is only one row with dbname = 'alldbs'. If not, use the apply version provided by AndriyM.


Updating TNS Entry across multiple terminals

You will do  it one time; but i suggest to use dns names entry in the tnsnames and if a change have to be made it will be on a centralized place.


It looks like SQL Server is not using that index by default because:

it's a filtered index, and
your query is parameterized

You can see this warning in the execution plan XML:
<UnmatchedIndexes>
  <Parameterization>
    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
  </Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />

SQL Server doesn't know what the parameter values are (because they're in variables), so it can't safely use the filtered index.
One solution is to use index hints (as you mentioned, this isn't ideal).
Another way to work around that is to use dynamic SQL, as described by Jeremiah Peschka here:
Filtered Indexes and Dynamic SQL
I don't know how the filtered index is...filtered.  You might be able to get away with embedding the literal on only one of the two values, to limit plan cache bloat.


Why does this trigger slow down updates so much

Your trigger performs two distinct actions.

It "end-dates" the old record in the tableA_records table.
It inserts a fresh copy of the new record into the tableA_records table.

Without seeing how the tableA_Records audit table is constructed, I can't be sure if one step is causing more problems than the other, but some questions you can ask and steps to try:

Is there an index on the tableA_records.tableA_id column? There should be.
What other indexes are on the tableA_records table? Are they necessary? Are they well designed?
Have you tried commenting out the update statement, then testing the trigger, then uncommenting that and commenting the insert statement and retesting? This will tell you if the update or the insert is slower.

I suspect you'll find that more of your delay is caused by the update than the insert, since an update requires scanning through either an index or a full-table scan to find the appropriate row. Make sure that column is properly indexed.

If other columns in tableA_records table are indexed, then look to see if that index is needed. Having too many indexes will slow down inserts.


How can I implement a viewed system for my website's posts?

What makes you think that there is a viewed table like yours on stackoverflow's database? Raw logs are very costly to maintain on a relational database, and on high-traffic websites, caching has a very important role, and it is almost impossible to conserve full logs of everything (they are summarized). For Wikipedia, for example, we maintain a complete different infrastructure for analytics (fed with Apache Kafka from Varnish and Mediawiki), and then an API is offered that can be called from Mediawiki itself: https://tools.wmflabs.org/pageviews/?project=en.wikipedia.org&platform=all-access&agent=user&range=latest-20&pages=Main_Page

If you had to implement viewed X times functionality within the datbase, you could denormalize the tables by maintaining an extra column as part of the main posts table:

// posts
+----+--------+----------+-----------+------------+------------+
| id | title  | content  | author_id | date_time  | view_count |
+----+--------+----------+-----------+------------+------------+
| 1  | title1 | content1 | 435       | 1468111492 | 3          |
| 2  | title2 | content2 | 657       | 1468113910 | 2          |
| 3  | title3 | content3 | 712       | 1468113791 | 2          |
+----+--------+----------+-----------+------------+------------+

For several reasons, in some cases a separate table may be preferred (e.g. to avoid making the main table hot; if there is a very normalized table such as post_properties; to save space if most posts have 0 views)- it all depends on external factors. In that case, you would have:

// posts_views
+---------+------------+
| post_id | view_count |
+---------+------------+
| 1       | 3          |
| 2       | 2          |
| 3       | 2          |
+---------+------------+

And then:

SELECT posts.*, post_views.view_count
FROM posts
LEFT JOIN post_views
ON posts.id = post_views.post_id
WHERE id = :id

(The LEFT JOIN is in case you have posts without view counts, if not, just use a regular JOIN)

In both cases, after a "view" is added, additionally (or alternatively) add 1 to the number of views in a SERIALIZED way.

Try to avoid logs on relational databases (specially MySQL). You can bring the summaries back after being analysed.


When you install a program in Linux it is most often configured to just work. So you would do service postgresql start to start it, service postgresql stop to stop, and service postgresql status to check if it's working etc.

You may need to set it up that it will start automatically on boot. How to do this depends on your system version, so you may need to Google it. On my system it is chkconfig postgresql on to enable, and chkconfig postgresql off to disable.

If you want to find where is data directory of a running postgres then you can use sudo su postgres -c "psql -c 'show data_directory'". You can find Postgres configuration files, logs and data there.


I believe you are assigning the Foreign key on Book to Loan. It should be just the opposite and be on Loan to Book. The Book doesn't need a comparative loan but every loan needs a comparative book.

Both of your foreign keys should exist on Loan.