Need for backing up MySQL databases information_schema, performance_schema, mysql and phpmyadmin?

performance_schema: Not necessary. It may contain performance metrics that are only good for the time you were using the instrumentation. It would serve no purpose to load collected metrics from the past into another MySQL instance or into the same MySQL instance at a much later time.
mysql : BE VERY CAREFUL HERE !!! If you backup mysql, you can only reload it into the same version of MySQL it came from. In particular, the mysql.user is neither forward nor backward compatible. See my old posts:

Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
Dec 24, 2012 :Backup and restore "mysql" database
Apr 12, 2012 : Cannot GRANT privileges as root (I give suggestions on how to backup GRANTS as SQL text with passwords encrypted)
If you have the slow log or general log enabled to write to the tables mysql.slow_log or mysql.general_log (See my old post How to enable MySQL general log?), such a backup could be huge. The entries may also be too old to want to restore. If you wish back the mysql schema, you should mysqldump it separate from user-defined databases.

phpmyadmin : If you wish to reload a backup of phpmyadmin into the same MySQL instance it came from, that would be fine.


How can I modify a trigger at subscribers SQL Server 2005?

Yes, you can use the system stored procedure sp_addscriptexec to run an ad-hoc script on all the subscribers.


Teradata to drive responsive web application

as I said in my answer to this question, Teradata wouldn't be my first choice for a web backend:
it really shines on moving/elaborating large quantities of data, but the typical workload of an application (frequent small queries) is not the best scenario for it.

Obviously there are other voices (see here) but there is quite an agreement about some critical points:

  Teradata locking behavior is the primary challenge for an OLTP style
  application.

and:

  use as much cache as you can: the Teradata connection is a precious resource
  you'd better not use for information that doesn't need to be realtime

It is clearly not impossible to implement such a solution on Teradata, but it requires your application to adapt fully to Teradata peculiarities (e.g. you could have to create a non-trivial application data model, to strive for single-AMP queries)

An example of the challenges you're going to face:

in an application I had to update a flag field on a set of rows in a table, chosen with a condition that was not the Teradata "primary index". However, there was an index on the columns used in the condition.

Usually, I would have used a simple "update": however, in Teradata, this would have created a "table-level lock".
At the end it turned out it was "better" to select all the rows in the application and do all the updates (in batch) one by one by primary index, to use the "hash-level" locks.

=========================================================

Edited: after the additional info from OP

First the good news:

Mostly read-only
since it is a mostly "read only" application, you can setup an efficient caching system. You could also "pre-cache" some common content so that the hit of first querying it won't be apparent to the user.

Why that's important in this case:
 connections in Teradata can't be created over a specific number for appliance. That's a pool wìthat will be shared between users in the application, the ETL and the normal OLAP users.
 In addition, queries that are not single-AMP won't have a very good latency, and they'll be more subject to the instan load of the system

Fine-grained queries

It might be a good thing if in this way you're able to do mostly single-AMP queries.

Why that's important in this case:
single-AMP queries (the ones querying/insert/deleting using the primary key as criteria) have lower lock impact and can be managed without paying the cost of coordination between the AMPs. On the other side, having such a "chatty" protocol wil occupy more the connections.

Materialized tables

It's good that you are already thinking about materializing some data. You might even find out that it will be useful have the same data materialized in two or more tables with different primary indexes, to satisfy your user interface (e.g. if you have different selection criteria on the same entity). However this will have a cost in terms of ETL time and disk space.

Now, the bad news:

More than 1000 users
As the application is not "completely" read-only, this number of users will probably create access conflict in the cases when they have to write.

Why that's a problem:
Forget multi-versioning: here readers block writers and viceversa. Your select might stop the update of another user from completing. If you are not able to use primary index for every update you do, you'll be locking the full table, and even if you use primary index, you are not going to have a row-level lock (you have an "hash-level".. normally it works ok, but it can have some issues with batch inserts). There's a workaround this conflict betwen readers and writers (LOCK ROW FOR ACCESS) but it opens the door to dirty reads, and you should carefully think if you can accept this in your application

Web application and OLAP on the same machine

Independently from the underlying technology, it's always a challenge to accomodate these two different workload on the same platform.

Why that's a problem:
Teradata has some workload management rules that can help in this field, however they need a careful tuning from DBAs and, however, extraordinary load (e.g. end of month batches) will likely have an impact on the user experience of the application

Hourly ETL

The hourly ETL will be a stress point for the application, since you'll be loading the same tables that you are querying. Since you cannot afford to lock the readers, you'll probably resort to "dirty reads", but you'll have to find some policy to manage the fact that the application could have temporarily inconsistent data.

Why that's a problem:
Already said: table locks, dirty reads and the presence of two different workloads

Number of queries

I know I said that "finer grained queries" can be a good thing, but here we're talking of 100 queries per user for a page, and a minimum of 20 queries per user action. Now, if you execute them serially, you're increasing latency (and you're looking for sub-second response time); but if you parallelize them, you're occupying the equivalent of the maximum sessions of a Parsing Engine just for an user (I'm not considering caching here)

Disclaimer: I've been working last two years in more traditional Teradata projects,  but when OLTP and raw latency counted I don't remember figures to go a lot less than the 150ms you mention. Maybe 130ms. But that was  after optimizations and configuring the system to accommodate our workload, and just on the simpler queries/macros.

But, then, if you already have an instance, and you want to verify Teradata performance on this scenario,  I'd suggest you to ask Teradata Sales support for a "Proof of Concept" on your  environment. I think they'll gladly accept (the more load you have,  the more likely you'll need additional nodes).

The reasons I wanted to know so many things about your application is that,  at certain conditions (mostly read data,  few active users,  clearly defined batch and online windows), it is easy to create applications on Teradata (as on every other system).  When some of these constraints are lifted,  the lock architecture and the necessity to target single-AMP queries,  makes it really difficult (not impossible, but really difficult) to achieve satisfying performances.


Upgrade SQL Server 2014 Failover Cluster to SQL Server 2016 Availability Group on Windows Server 2016

To migrate from two Failover Clustered Instances (FCI) to an Availability Group (AG) that spans four Availability Replicas is fairly straight forward but somewhat of a lengthy process. Also, some downtime is required to point your application to the Availability Group Lister and manually fail over to one of the Secondary Replicas.

This is a high level overview of the process so use the links provided to plan:

Disable Database Mirroring
Completely uninstall the FCI and destroy Windows Cluster at DC2

Uninstalling a SQL Server Clustered Instance

Install Standalone SQL Instances on the two nodes at DC2
Join the two new stand-alone servers at DC2 to Cluster at DC1 and configure the AG

Combine a failover cluster instance and Availability Groups
Configure Availability Group on a multisubnet cluster

Take down time to fail over to to a Secondary Replica in DC2 and update Application to point to Listener
Remove FCI from AG

Remove a secondary replica from an Always On Availability Group

Completely uninstall the FCI at DC1
Repeat Step 3 at DC1
Add new stand-alone instances to AG

Add a secondary replica to an Always On Availability Group

All of this can be done on SQL Server 2014. There are some benefits to upgrading to 2016 but I would recommend you read this to help plan as there are a lot of similar steps to upgrade.


deduct 60 minutes/ 1 hour from punch time (Start time) of attendance

Something like this? If you share your table structure the answer could be more precise.

-- TEST TABLE
CREATE TABLE dbo.employees(id int identity(1,1),
                           StartTime datetime2);

-- TEST VALUE
INSERT INTO dbo.employees (StartTime)
VALUES(getdate());

--CHECK
SELECT * FROM dbo.employees;

-- ACTUAL UPDATE
UPDATE dbo.employees
SET StartTime = DATEADD(hour,-1,StartTime);

-- CHECK AFTER UPDATE
SELECT * FROM dbo.employees;


How can I speed up this slow simple query?

Try adding two indexes:

ALTER TABLE game
  ADD INDEX deleted_sportid_hometeam_date_IX          -- choose names
    (deleted, sportid, hometeam, date),
  ADD INDEX deleted_sportid_awayteam_date_IX          -- for the indexes
    (deleted, sportid, awayteam, date)  ;

and then running this version:

SELECT * FROM game
WHERE hometeam = 29
  AND sportid = 1
  AND date BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted = 0

UNION ALL

SELECT * FROM game
WHERE awayteam = 29
  AND sportid = 1
  AND date BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted = 0 ;

You can still try your original query after having added the 2 indexes and the efficiency will probably be improved (but not much, depends on the selectivity of the deleted - sportid combination.) But I don't think the optimizer is smart enough to understand that this (rewriting) is equivalent to that (original query) and that it could use both indexes, one for the 1st and the other for the 2nd part, so you will only have a small efficiency gain.

So, the "why is it faster this way" has mainly to do with the OR condition and with the limited ability of the optimizer to rewrite queries in equivalent forms. This rewriting for example would never be produced anyway by an automated optimizer (because I assumed that hometeam and awayteam are never the same, which the optimizer cannot guess.) If home team and away team could be the same, then we'd have to use UNION (not UNION ALL).

B-tree indexes are super-good for conditions with AND, only. When there are ORs or otherwise complicated conditions, they are not so good. Occasionally - like in this case - there is a way to rewrite a query so it has only ANDs. The last part of the execution (the UNION ALL) can be executed in MySQL by running the two subqueries one after the other and showing all results from both so there is no calculations or delay there.


I would suggest to try an index like this:

CREATE NONCLUSTERED INDEX NCI_ProcessState_ProcessStateDateLastModified_SleepUntil
ON [TABLE](ProcessState,ProcessStateDateLastModified,SleepUntil)

This way you can filter sharp on the state as all others will result in bigger result sets due to the <= filter.

You can check the index usage easily with this query:

SELECT obj.*,usage.*
FROM sys.dm_db_index_usage_stats AS usage
INNER JOIN sys.indexes as obj
        ON usage.object_id = obj.object_id
WHERE database_id = db_id()

Just filter it on your index and take a look a the search and update columns.


Is there a way to speed up restoring data from MySQL to PostgreSQL?

You should try pgloader : http://pgloader.io/howto/mysql.html It can load data from MySQL to PostgreSQL using COPY mode, which is faster than INSERT.


A date column in a table won't save the value after a correct date was entered

Community wiki answer:

There are loads of options/possibilities. See Datetime Format Models in the Database SQL Language Reference.

For example, if you have it set to DD-MON-RR, you should use 15-SEP-17.

RR lets you store 20th century dates in the 21st century using only two digits, as described in The RR Datetime Format Element:

  The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

  If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

  That is:

  If the specified two-digit year is 00 to 49, then

  If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

  If the specified two-digit year is 50 to 99, then

  If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
  If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.


Permission error when restoring MsSQL database with full text on Linux

Brian told me on the chat:
"@FrancescoMantovani Importing the database, dropping the catalog, then re-exporting looks to have resolved the issue. Thank you so much for your help, this was very valuable."
This was the root cause:
https://support.microsoft.com/en-us/help/923355/error-message-when-you-perform-a-full-backup-of-a-database-in-sql-serv
@Brian, please add info if you need


Re enable Windows Authetication in SQL Server

Follow the steps in:

Connect to SQL Server When System Administrators Are Locked Out.

Briefly:

Start server with the -m switch
Connect as  a local administrator
Enable the sa account (and reset its password if necessary)
Restart server normally


Selecting rows from same name table from multiple databases in single result set

Sure :

    select * from database_n.dbo.Table_1
    UNION ALL
    select * from database_n+1.dbo.Table_1
    UNION ALL
    select * from database_n+2.dbo.Table_1
    -- etc.


How to change SQL Server in a domain with a new server machine and latest SQL database?

What you are planning is called side-by-side upgrade. It means that wholly new system is installed. Its alternative is in-place upgrade. It means that only the Sql Server is upgraded on existing computer. As you are going to upgrade the operating system too, side-by-side is the only option.

Microsoft has released a white paper about how to upgrade Sql Server. The pdf has 400+ pages, but not all parts are likely to be relevant to your case.

Read the documentation and ask for clarifications about things you didn't quite catch.

As a side note: though migrating from Sql Server 2000 to 2008 is certainly an improvement, you really should migrate to more recent version. That is, Sql Server 2014 or 2016 should be your migration target. Be aware that Sql Server 2000 is so old that it cannot be migrated directly to Sql Server 2012. An intermediate step into Sql Server 2008 is needed.


What is the usage of sys.sysrowsets in SQL Server?

Its an underlying table for sys.partitions, which returns the same information as

SELECT * FROM sys.partitions

  But what is a partition?

Refer to Partitioned Tables and Indexes on MSDN.

idmajor is the column name that's commonly known as object_id

idminor is the index_id.

Lets do some testing now:  I have used AdventureWorks 2012 from CodePlex.

USE AdventureWorks2012_Data;
GO
DECLARE @MyID int;
SET @MyID = (SELECT OBJECT_ID('HumanResources.Employee',
'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);

Output:

name        object_id   type_desc

Employee    1237579447  USER_TABLE

Lets query the sys.partitions view now:

SELECT partion_id, object_id, index_id FROM sys.partitions
WHERE object_id = '1237579447'

Output:

partition_id      Object_id      index_id

72057594045136896   1237579447  1

72057594050510848   1237579447  2

72057594050576384   1237579447  3

72057594050641920   1237579447  5

72057594050707456   1237579447  6

72057594050772992   1237579447  7

If we look at the sys.indexes view:

SELECT object_id, name, index_id from sys.indexes where object_id = '1237579447'

Output:

Object_id           name                                index_id

1237579447  PK_Employee_BusinessEntityID                    1

1237579447  IX_Employee_OrganizationNode                    2

1237579447  IX_Employee_OrganizationLevel_OrganizationNode  3

1237579447  AK_Employee_LoginID                             5

1237579447  AK_Employee_NationalIDNumber                    6

1237579447  AK_Employee_rowguid                             7

In the above output, index_id is just an id for the indexes. 1 for Clustered index and the others (2-7) for other non clustered indexes.

  The object_id for all indexes under a table is same as the object_id
  for that table.

Lets take a look at the sys.sysrowsets table:

USE AdventureWorks2012_data
GO
SELECT rowsetid, idmajor, idminor from sys.sysrowsets
WHERE idmajor = '1237579447'

I used the object_id for the HumanResources.Employee table to filter data.

Output:

rowsetid               idmajor         idminor
-------------------- ----------- -----------
   72057594045136896  1237579447           1

   72057594050510848  1237579447           2

   72057594050576384  1237579447           3

   72057594050641920  1237579447           5

   72057594050707456  1237579447           6

   72057594050772992  1237579447           7

From this its clear that object_id or idmajor is the same for the table and all the indexes under it and idminor is nothing but the index_id for an index.


Query to find GUID's "not in" null containing subquery returns no results

Just to provide some additional explanation to billinkc's answer.

If null is a trump card you might be wondering why doesn't WHERE 2 IN (2,3, NULL) exhibit the same behavior?

That one works as expected because it evaluates to (2=2) OR (2=3) OR (2=NULL).

Under the rules of three valued logic for Or-ed conditions if any of them evaluate to true the expression is true. Otherwise if any of them evaluate to unknown the expression is unknown. The only other possibility is that all are false in which case the expression evaluates to false.

In order for a row to be returned in SQL the WHERE clause must evaluate to true rather than false or unknown. The above does that.

The expression 1 NOT IN (2,3, NULL) evaluates to (1 <> 2) AND (1 <> 3) AND (1 <> NULL). When conditions are AND-ed all of them must evaluate to true in order for the expression to evaluate to true.

The presence of the NULL in the list guarantees that there will be at least one UNKNOWN and that this will never be the case. Hence the reason for the " NULL pooches it all" behaviour in this context.

To give an analogy here as to why the NOT IN behaviour makes sense.

  Three friends Tom, Dick, and Harry are sitting in a railway carriage
  with a complete stranger whose name is unknown to them.

  If Tom is asked "Is your name different from everyone else's in this
  carriage?" then it is impossible for him to answer with any certainty.

  Even though he knows that Tom <> Dick and Tom <> Harry (so the
  statement might be true) the veracity of the statement overall hinges on
  the stranger's name and this is not known.

  This is analogous to the SQL 'Tom' NOT IN ('Dick', 'Harry', Null)


Exclude IP addresses of Vulnerability Scanner

sp_Blitz doesn't have parameters to skip IP addresses since SQL Server doesn't store IP addresses for most of the activities.

However, you can skip specific checks altogether if you want. To do that, create a table like this:

CREATE TABLE dbo.SkipChecks (ServerName NVARCHAR(128), DatabaseName NVARCHAR(128), CheckID INT);

Then, you can add rows in there for the checks you want to skip. In the far right column of sp_Blitz's output, the CheckID is listed. (For a comprehensive list of checks and IDs, see the documentation.)

ServerName = ‘MyServer, DatabaseName = NULL, CheckId = NULL – will not check anything on servername MyServer
ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on any server
ServerName = ‘MyServer’, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on MyServer
ServerName = NULL, DatabaseName = NULL, CheckId = 5 – will skip CheckId 5 on all databases
ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = 5 – will skip CheckId 5 on MyDB


SQL Server - dump schema into separate files keeping installation order

The best way to get this done is to use a tool to manage the process of getting stuff in and out of source control and then deploying it.

If you have an MSDN license, Visual Studio has a database project that can do this. It's free with the license and works pretty well. It has some limitations, especially when it comes to dealing with code that can lead to data loss. In those cases you may find manual intervention is necessary. Also, you'll have to do some of your database work within Visual Studio because there's no way to use Azure Data Studio or SQL Server Management Studio. However, this approach solves the dependency issue.

There are also third party solutions. I work for a vendor, Redgate, that makes a few of them. We solve the data loss problem and we work within SSMS (and soon ADS). There are other vendors as well (I just think we're better). These tools also solve the dependency problem while still letting you have individual files so that you can track all changes independently through your source control management tool.


You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema?

In that post I show these minimum privileges for a mysqldump

SELECT
SHOW VIEW (If any database has Views)
TRIGGER (If any table has one or more triggers)
LOCK TABLES (If you use an explicit --lock-tables)

You should run this command:

SHOW GRANTS FOR tungbt@192.168.12.197;

If SHOW VIEW is not there, that's the reason why.

UPDATE 2014-04-16 23:06 EDT

When you did this

mysqldump --all-databases --routines >> all.sql

I see you did not specify the user and password. That being the case, you were not logged in as root@localhost. You will have to be explicit in specifying the root user

mysqldump -uroot -p --all-databases --routines >> all.sql

You will see the password prompt. Enter the root@localhost password and you are off and running.

You could also specify the password too

mysqldump -uroot -ppassword --all-databases --routines >> all.sql

Give it a Try !!!

WILD SUGGESTIONS

If you are using .~/my.cnf and still getting an error, you might be hitting this situation in Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user '

If the config file is .~/my.cnf is really /root/.my.cnf, perhaps you are not logged in as Linux root. You may have to run sudo.

Please run this command

mysql -ANe"SELECT USER(),CURRENT_USER()"

If you do not see root@localhost twice, then you are not authenticating correctly.

In .my.cnf you need to make sure that user and password are under the [client] section

[client]
user=root
password=rootpassword

not under the [mysql] section.

UPDATE 2014-04-17 13:53 EDT

I cannot help look at that bug report and wonder the following: Since you have DEFINER=tungbt@192.168.12.197, it is possible that root@localhost is behaving like tungbt@192.168.12.197 ? I say this because according to the MySQL Documentation on CREATE VIEW: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition.

You could change the definer of the view to root@localhost and try the mysqldump again


How do I guarantee data is the same after upgrading SQL Server?

I'd buy Red Gate SQL Data Compare so 100% comparisons are possible.


Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if:

the column is declared as NOT NULL in the table definition, or
the column is protected from nulls by an active, trusted check constraint

Here's a simple test table:
CREATE TABLE dbo.Test
(
    Id int IDENTITY(1,1) NOT NULL,
    DeclareNotNull int NOT NULL,
    DeclaredNull int NULL,

    CONSTRAINT PK_Test PRIMARY KEY (Id),
    CONSTRAINT CK_DeclaredNull CHECK (DeclaredNull IS NOT NULL)
);
GO

INSERT INTO dbo.Test
    (DeclareNotNull, DeclaredNull)
SELECT
    v.[number],
    v.[number]
FROM master.dbo.spt_values v
WHERE
    v.[number] IS NOT NULL;
GO

It has two columns: one is declared to be NOT NULL, the other is declared NULL but has a check constraint.  Neither column has any rows with NULL values.
We can validate the check constraint like this:
SELECT
    cs.[name],
    cs.[type_desc],
    cs.is_disabled,
    cs.is_not_trusted
FROM sys.check_constraints cs
WHERE cs.parent_object_id = OBJECT_ID(N'dbo.Test');

We can then get estimated plans on these two queries:
SELECT * FROM dbo.Test WHERE DeclareNotNull IS NOT NULL;
SELECT * FROM dbo.Test WHERE DeclaredNull IS NOT NULL;

Notice there are no "Predicate" or "Seek Predicate" sections in the scan, or other filter operators in the execution plan.  The null check has been removed in both cases.
If we disable the check constraint:
ALTER TABLE dbo.Test
NOCHECK CONSTRAINT CK_DeclaredNull;
GO

SELECT
    cs.[name],
    cs.[type_desc],
    cs.is_disabled,
    cs.is_not_trusted
FROM sys.check_constraints cs
WHERE cs.parent_object_id = OBJECT_ID(N'dbo.Test');

And we get the estimated plan for that second query:

The clustered index scan operator now includes a "Predicate" section, since the check constraint is not trusted.


You are not moving instances, you are copying databases via backup restore. All you need is to get the syntax right, as per RESTORE spec:

<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
          [ ,...n ]

Each WITH option is separated from the preceding with a comma. What you're missing is the , and you need to move the files into the destination location:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE
 , MOVE 'Backup_Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test.mdf',
 , MOVE 'Backup_Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test_log.ldf'

Judging from your folder names (MSSQL10 vs. MSSQL10_50) your source server is SQL Server 2008 (not R2) and only the destination is SQL Server 2008 R2. Which means that the copy will upgrade the database and the database will never be possible to copy/restore on the original non-R2 instance.


Benefits of reindexing large bloated indexes in PostgreSQL

Given that I have other large indexes on other large tables, will Postgres work better given that the new index fits into shared buffers better?

There is probably little if any benefit.  If all the other indexes are rarely used and this one is intensively used, then maybe there will be.  The gold standard would be try it and see.  But unless all your scans are index-only scans, the index and the pages of the much larger table the index points at will be fighting over shared_buffers, as they certainly both won't fit.

Just because something will fit in the shared_buffers, doesn't mean it will get loaded into shared_buffers (you could force it with pg_prewarm) and then stay there forever (PostgreSQL doesn't provide a way to "nail" things into shared_buffers).  And the buffer eviction algorithm is not all that clever, so don't count on them staying in shared_buffers just because it seems obvious to you that they should.

  I think my current query times on the old index are good as-is.

So why worry about it then?


I don't think the database have any way of changing the current date. Its date is the computer date. You should probably check the date at operating system level and, possibly, correct it.


Trigger for multiplying two columns from different tables with a functions in mysql

Assume we have (schematically):

Products table

CREATE TABLE products (productID INT,
                       price DECIMAL);

Orders table

CREATE TABLE orders (orderID INT,
                     total_price DECIMAL);

Joining table

CREATE TABLE joining (productID INT,
                      orderID INT,
                      amount DECIMAL,
                      separate_price DECIMAL);

There is a lot of records in products table. We form new order and add new record into orders table. Now we begin to insert records into joining table, and we want to calculate both separate_price (for each separate product) and total (for whole order).

The trigger for to calculate separate price will be:

CREATE TRIGGER calculate_separate
BEFORE INSERT
ON joining
FOR EACH ROW
SET NEW.separate_price := (SELECT NEW.amount * products.price
                           FROM products
                           WHERE products.productID = NEW.productID);

The trigger for to calculate total price will be:

CREATE TRIGGER calculate_separate
AFTER INSERT
ON joining
FOR EACH ROW
UPDATE orders
    SET total_price = total_price + NEW.separate_price
    WHERE orderID = NEW.orderID;

If there is no separate_price field in joining table (and there is no trigger which calculates the value for that field) we will use

CREATE TRIGGER calculate_separate
AFTER INSERT
ON joining
FOR EACH ROW
UPDATE orders
    SET total_price = total_price + (SELECT NEW.amount * products.price
                                     FROM products
                                     WHERE products.productID = NEW.productID)
    WHERE orderID = NEW.orderID;

Pay attention: we calculate separate value (which is stored in the same table, in the record which creation fires a trigger) in BEFORE trigger (so the calculated value then will be inserted), whereas we re-calculate total value in AFTER trigger (and update a record in another table, and only in case when new record successfully inserted).

PS. Function mentioned in a question is not used - we do not need in it. We can use it, of course, but its usage is excess.

PPS. Each trigger above contains one single statement. So we do not need in BEGIN..END blocks ade DELIMITER reassign.


You have two errors:

apparently brand is defined as text, not varchar
set returning functions have to be used like a table in the FROM clause.

So you need to change your function definition to:

CREATE OR REPLACE FUNCTION getcars()
   RETURNS TABLE (car_name text, id_car INT)
AS $$
BEGIN
   RETURN QUERY
   SELECT brand,car_id
   FROM cars;
end;
$$
LANGUAGE plpgsql;

And then use it like this:

select *
from getcars();

Simple functions that just return a query are better defined as language sql:

CREATE OR REPLACE FUNCTION getcars()
   RETURNS TABLE (car_name text, id_car INT)
AS $$
   SELECT brand,car_id
   FROM cars;
$$
LANGUAGE sql
stable;


Exporting mysql query result to .txt file

You need to embrace the filepath in quotes. This works for me (tested on MariaDB 10.4, but should work on older versions as well):

DELIMITER //
CREATE PROCEDURE test(filepath text, _query text)
BEGIN
  DECLARE stmt TEXT;
  SET stmt := CONCAT(_query, ' into OUTFILE \'' , filepath, '\'');
  PREPARE s FROM stmt;
  EXECUTE s;
  DEALLOCATE PREPARE s;
END //
DELIMITER ;

By the way, it's not good practice to use user-defined variables inside stored programs, like @sql in your procedure, because they retain their values between calls. (I think it will be OK in your procedure, though, unless you use @sql elsewhere.) DECLARE your variables instead.


I would recommend backing up the transaction log more frequently than once a day, possibly every 15 minutes.

It may be that a single very large transaction has blown up its size. You can monitor how full the transaction log gets during the day (in between log backups) using the command

dbcc sqlperf(logspace)

And yes, you can shrink the log, using a command like this, for example, if you want to shrink it to 1000MB:-

use YourDatabaseName
go

dbcc shrinkfile(2,1000)

Weekly full database backups exposes you to data loss; I would also recommend backing your database more frequently than weekly.


One database with two schemas: cross-schema View permissions issue?

Ownership chains work down not up.  If you give the user rights to the view, they can query the view without needing rights to the objects under the view, in this case the table.  It doesn't work the other direction.  The user always needs rights to the object they are attempting to access.  All the objects under that object will come automatically.


Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?

Why in the documentation is said a optimization is working for CONTAINED IN clause, but actually you need to create such check constraint to use it?

The optimization is really occurring - even though the main table index scan appears in the execution plan, it is never executed because it's behind a filter with a startup expression predicate.

See below for a more detailed explanation.

  All clauses can return rows from both tables and one of them. Why this optimization is working only for the CONTAINED IN clause?

I don't think anyone but the SQL Server product team could answer that one, unfortunately.

The execution plan is a bit misleading here.  If I create a temporal table:

CREATE TABLE dbo.ThisIsJustATest
(
  [Id] int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
  , [Junk] nvarchar(2048) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ThisIsJustATestHistory));

And then load it with data:

INSERT INTO dbo.ThisIsJustATest (Junk)
SELECT m.[text] FROM sys.messages m
OPTION (MAXDOP 1);

And then update some rows (so that I have history rows to query):

UPDATE dbo.ThisIsJustATest
SET Junk = 'Busted'
WHERE Id % 100 = 0;

And run this query (that only returns history rows) with stats I/O on:

SET STATISTICS IO ON;

SELECT *
FROM dbo.ThisIsJustATest FOR SYSTEM_TIME CONTAINED IN ('2018-09-11 14:17:00.96', '2018-09-11 14:18:51.35');

Here's what I get:

Table 'ThisIsJustATest'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ThisIsJustATestHistory'. Scan count 5, logical reads 154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So according to stats I/O, it really did zero reads from the base table - it only read from the history table.  Here's the execution plan.

Notice that all the predicates on the base table's index scan operator are residual predicates.  So in theory, the whole table should get read (even though there are no matching rows).  So there is some trickery going on on.

It doesn't read anything from the base table because the predicate in the filter operator must always evaluate to false:

'2018-09-11 14:18:51.35'=sysmaxdatetime((2))

Note that the predicate is a Startup Expression, so the Clustered Index Scan below it does not get started when the expression evaluates to false. sysmaxdatetime is an intrinsic (internal) function that presumably returns the maximum date/time value at the given precision.

The "Number of Executions" on the Clustered Index Scan is 4, but this is simply an internal quirk related to the way parallel plans work - a thread does call Open() on the subtree to ensure all necessary threads get created and initialized, but no real work is done. In a serial version of the same plan, the number of executions would indeed be zero.

What about the CHECK constraint?

For what's worth, it appears that adding the check constraint is still useful as a performance tuning option, as it reduces logical reads on the history table by quite a bit.

Before the constraint:

Table 'ThisIsJustATest'. Scan count 0, logical reads 0
Table 'ThisIsJustATestHistory'. Scan count 5, logical reads 160

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 197 ms.

After the constraint:

Table 'ThisIsJustATestHistory'. Scan count 1, logical reads 53

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 197 ms.


How to quote a SQL*Plus identifier in a bash script?

[root@o73 ~]# SGA_TARGET=$(su - oracle -c "export ORACLE_SID=RYMIN19; export ORAENV_ASK=NO; . oraenv >/dev/null; echo \"set pages 0
select value from v\\\$parameter where name = 'sga_target';\" | sqlplus -s / as sysdba")
[root@o73 ~]# echo $SGA_TARGET
2147483648
[root@o73 ~]#


I changed a bit

What you want has to be a BEFORE INSERT TRIGGER

The weight i couldn't find in your trigger

Because every variable has to come from somewhere you can define them, get them from the new row, or by a selectl.

You can't access them by writing tabl1.abc or table2.xyz

DROP TRIGGER IF EXISTS calcEGFR;
delimiter //
CREATE TRIGGER calcEGFR BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
DECLARE _age_at_visit INTEGER;
DECLARE _sex VARCHAR(10);
    SELECT age_at_visit, sex INTO _age_at_visit, _sex FROM Table1 WHERE (Table1.patient_wpid = NEW.patient_wpid AND Table1.patient_id = NEW.patient_id);
    SET @creatinine_power := POWER(NEW.creatinine,-1.154);
    SET @age_power :=  POWER(_age_at_visit,-0.203);
    IF _sex = 'female' THEN
        SET @sex_addition := 0.742;
    END IF;
    IF _sex = 'male' THEN
        SET @sex_addition := 1;
    END IF;

    SET @eGFR_value := (175 * @creatinine_power * @age_power * @sex_addition);
    SET NEW.eGFR = @eGFR_value;
END;//
delimiter ;


Communication link failure for some queries to linked server

We disabled TCP Chimney Offload per the Symantec article and rebooted the servers and the issue appears to be resolved.

The problematic SPs are no longer throwing the communication link failure exception.

More info from Microsoft on TCP Offloading


Safely moving and creating new tempdb files

To move tempdb files, you simply need to do the following:

alter database tempdb
modify file
(
    name = tempdev,
    filename = 'C:\YourNewTempdbDir\tempdb.mdf'
)
go

alter database tempdb
modify file
(
    name = templog,
    filename = 'C:\YourNewTempdbDir\templog.ldf'
)
go

If you want to add a new file to tempdb, you simply need to do the following (provided you want to add it to the PRIMARY filegroup, or create your own):

alter database tempdb
add file
(
    name = tempdb2,
    filename = 'C:\YourNewTempdbDir\Tempdb2.ndf'
)
go

For these changes to take effect, you will need to restart the SQL Server service.  So as far as minimizing downtime goes, you are constrained to the amount of time it will take for the service restart.  You don't have to worry about moving the pre-existing tempdb database files, as SQL Server always recreates the files and the new locations/files will be created upon service startup.

As for the "1 tempdb data file per core", that is largely a myth.  The correct approach is to monitor tempdb file contention for the Page Free Space (PFS), Global Allocation Map (GAM), and the Shared Global Allocation Map (SGAM) pages.  Please reference this article to get a query (alternative link) that looks through the sys.dm_os_waiting_tasks DMV to see how much tempdb file contention there is.  Then you need to go off of this, instead of just blanketing tempdb with the same amount of files as there are cores.  It's the more advisable approach.


There are two stages to the lock of updated data.  The first is an update lock (U) and the second, provided there is data that needs to be modified, is an exclusive lock (X).  It's a two stage operation in the sense that there is data that needs to be searched in order to determine what/if data needs to be modified.  The update lock will exist for that (or attempt to be attained for the duration of the operation).  Then when data needs to be modified, the update lock will convert into an exclusive lock.

The reason behind this locking mechanism is to prevent deadlocking from the updating data scenario.  Please see Kalen Delaney's post on more specifics surrounding this.

Take the below for example:

Execute this for connection 1...

-- connection 1, leaves an open tran to prevent connection 2 from progressing

use AdventureWorks2012;
go

begin tran

    update HumanResources.Department
    set name = 'This is a new name'
    where DepartmentID = 11;

--commit tran

Then in another session, attempt the following update:

use AdventureWorks2012;
go

update HumanResources.Department
set name = 'This is a conflicting tran'
where DepartmentID = 11;

What we have here is an update lock that is waiting because there is already an exclusive lock on the resource that the update lock is trying to get (an update lock is not compatible with an exclusive lock).  The above scenario is used to "halt" the update lock conversion to exclusive lock.

You can see this by looking at the sys.dm_tran_locks DMV:

use AdventureWorks2012;
go

select
    resource_type,
    resource_description,
    resource_associated_entity_id,
    request_mode,
    request_status
from sys.dm_tran_locks
where resource_database_id = db_id();

You should have similar results for the output of the above query:


This one looks like the document you are after (although French, not English, and not free)
https://iteh.it/catalog/standards/iso/7c7fa915-f99f-4ce9-8aa4-0e896d89ed84/iso-iec-9075-1989
Or this one seems to have English and Russian versions:
https://gostperevod.com/st-iso-iec-9075-1989.html


Setting up Master - Slave replication: error connecting to master (Error_code: 2003)

So, I actually had several issues that may come in handy to walk through for others:
Error: 2003
I removed the bind-address from the master
Errno: 1045
Access denied for user
I explicitly set the IP address for the slave user:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'000.000.000.00' IDENTIFIED BY 'password';
instead of
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
Error 1236
Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet
It turns out the default max_allowed_packet is 1MB for <= 5.6.5 and 4MB for >= 5.6.6.
I set both of these manually to 16MB as per this article
Error 1236
Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event
I followed the instructions in this answer
That solved the last of my errors and the replication appears to be working as expected now!


Command line export and import CSV in SQL Server 2008 R2

Based on your need, I can suggest couple of options :

Use SSIS Import/Export; or
Use BCP OUT and BCP IN

Each method has its own benefits and it all depends on your requirement which one to choose.


Check the following if it's applicable to your case:
ECONNREFUSED (79) "Connection refused" message in the db2diag.log for the db2hmon process.

  Cause

  The DB2 Health Monitor periodically wakes up and checks for a
  connection to the DB2 administration server (DAS).

  Resolving The Problem

  This type of error is reported when the DAS (DB2 administration
  server) is not active. When the health monitor wakes up it checks for
  a connection to the DAS. In this case the connection is refused as
  either the DAS does not exist or it is not started.


How to include a where clause in a query from another column

If you implement the process as a stored procedure, it can generate the dynamic SQL based on the rules in your base tables and then execute that dynamic statement.

From the DB2 CLP, it's possible to write a query (probably containing one or more CTEs) that generates the SQL. The EXPORT utility can write the results of that query to a text file whose name is already in your master script's list of files to execute.

Another option is CLPPlus, which IBM based on SQLPlus from Oracle. It has some capabilities to iteratively construct dynamic SQL in a scratchpad-like area and then execute it.


VMWare Memory Sizing and SQL Server 2014

Wes. Those Perfmon counters are a good start to make sure that the VM is configured correctly (meaning, the host isn't wildly overcommitted on RAM.)

However, they don't help size the VM.

If you're having performance problems, you'll want to identify two things:

The SQL Server's top wait during that time, and
The queries causing that wait

To get the top wait, try running sp_BlitzFirst @SinceStartup =1. (Disclaimer: that's an open source script, and I'm one of the authors.) You can also use 3rd party monitoring tools like Idera SQL DM, Quest Spotlight, and SQL Sentry Performance Advisor. (Disclaimer: those cost money, and I didn't write any of those, but they're awesome.)

The @SinceStartup = 1 switch gives you waits since startup, which isn't as cool as monitoring software, but it's a free start.

If your top wait is PAGEIOLATCH, that means reading data pages from a data file. If that's the case, you want to find the queries reading the most data. For that, use sp_BlitzCache @SortOrder = 'reads' (again, disclaimer, open source script, and I'm one of the authors.)

That'll give you the top 10 queries ordered by how much data they're reading. They're usually candidates for index tuning or query tuning. I'd always rather tune those rather than add memory, but if PAGEIOLATCH is your top wait, and you're not allowed to tune those top 10 queries, nor tune their indexes, then the next fix is to add memory to the VM. (But only after going down this route of troubleshooting.)


Custom unit of measurement conversion problem

Your problem is not a unit of measurement problem.  It's a stock keeping unit (SKU) problem.  You can't convert, nor for any practical purposes would you ever want to convert from 8 packs of cans of beer A to 12 packs of bottles of beer B.

Your product ("Beer A") is a generic product header.  You don't sell "Beer A".  What you actually sell are the boxes of cans (or bottles) - of a particular size - of Beer A.  This box is what gets a record in your product/item table.  You may want to have a product header table that just lists "Beer A" without any actual quantity related to it.  That's up to you and depends on your system requirements.  What you really need to have though is a SKU table where the actual salable things are recorded.

Now if you want to be able to get some kind of apples to apples comparison between SKUs of similar products, you can track an actual, objective unit of measure for each SKU.  For example, if you're dealing with beer, then a liquid measure is appropriate.  Say you have 6 x 355ml cans of Beer A in your six pack SKU.  Then that SKU has 2,130ml of beer in it.  You could store that along with the other useful information that it's a six pack of 355ml cans.

If you want to have this apples to apples comparison, then you want to have a unit of measure conversion table that has columns such as:

...
  measure_type char(1) -- `D`ry or `L`iquid (or others as applicable)
, from_unit_of_measure varchar(10)
, to_unit_of_measure   varchar(10)
, conversion_factor    decimal(16,8)
...

If you have something based on these columns then you have to have all the combinations of from / to units of measure in both directions in order to look up a conversion factor.  You could also, alternatively, only have from non-default units of measure to the standard unit of measure for each measure_type.  This would mean less data in the table, but you'd have to have conditional logic to go from and / or to non-standard units of measure.  In the worst case scenario, you might have to convert from units in A (non-standard) to B (standard) and again from B (standard) to C (non-standard).  Which way you go depends on your relative tolerance for more data to maintain or more code complexity.


Seeing some comments saying not to use SSIS for this, but my vote would be for using SSIS based on the fact that you said that the data would be transferred between servers. If you used a stored proc, you would have to have a linked server setup.

And yes, using SSIS would allow you to run your extract and load on your tables in parallel. It would also give your more flexibility to do things like dump your extract to raw files to get that done quickly without putting as much pressure on your source system and then import the raw files on your destination server where it might take longer.

I also guess you could use replication, but if you don't have replication already installed and configured, that would be a bigger lift to get up and running then writing a simple SSIS package that could do all this for you, in my opinion.


Mongodb replica set primary and secondary node conf fail when one slave is down

In my env, I had created MongoDB master-slave conf for high
  availability so one server fails should be handled by other but, my
  slave's server fails due to disk utilization by this, my master node
  also fails to provide data to api's which create disrupts my whole
  system.

As per MongoDB documentation here to providing all the functionality of master-slave deployments, replica sets are also more robust for production use. Master-slave replication preceded replica sets and made it possible to have a large number of non-master (i.e. slave) nodes, as well as to restrict replicated operations to only a single database; however, master-slave replication provides less redundancy and does not automate failover.

Master instances store operations in an oplog which is a capped collection. As a result, if a slave falls too far behind the state of the master, it cannot “catchup” and must re-sync from scratch. Slave may become out of sync with a master if:

The slave falls far behind the data updates available from that
master.
The slave stops (i.e. shuts down) and restarts later after the master
has overwritten the relevant operations from the master.

When slaves are out of sync, replication stops. Administrators must intervene manually to restart replication. Use the resync command. Alternatively, the --autoresync allows a slave to restart replication automatically, after ten second pause, when the slave falls out of sync with the master. With --autoresync specified, the slave will only attempt to re-sync once in a ten minute period.

To prevent these situations you should specify a larger oplog when you start the master instance, by adding the --oplogSize option when starting mongod. If you do not specify --oplogSize, mongod will allocate 5% of available disk space on start up to the oplog, with a minimum of 1 GB for 64-bit machines and 50 MB for 32-bit machines.

  So, is this event occurs due to configuration fail or its mongo
  property? Or some conf other than master-slave creation is required to
  prevent this?

As MongoDB documented here from MongoDB 4.0 removes support for master-slave replication. Before you can upgrade to MongoDB 4.0, if your deployment uses master-slave replication, you must upgrade to a replica set.

Warning :
Deprecated since version 3.2: MongoDB 3.2 deprecates the use of
master-slave replication for components of sharded clusters.

Important:
Replica sets replace master-slave replication for most use cases. If possible, use replica sets rather than master-slave replication for all new production deployments. This documentation remains to support legacy deployments and for archival purposes only.

After modification the question of OP

As  here MongoDB provides two options for performing an initial sync:

Restart the mongod with an empty data directory and let MongoDB’s normal initial syncing feature restore the data. This is the more simple option but may take longer to replace the data.

See Automatically Sync a Member.

Restart the machine with a copy of a recent data directory from another member in the replica set. This procedure can replace the data more quickly but requires more manual steps.

See Sync by Copying Data Files from Another Member.

  WARNING: Avoid reconfiguring replica sets that contain members of different MongoDB versions as validation rules may differ across
  MongoDB versions.

Reconfigures an existing replica set, overwriting the existing replica set configuration. To run the method, you must connect to the primary of the replica set.

For Example

rs.reconfig(configuration, force)

To reconfigure an existing replica set, first retrieve the current configuration with  rs.conf(), modify the configuration document as needed, and then pass the modified document to rs.reconfig().

rs.reconfig() provides a wrapper around the replSetReconfig command.

The force parameter allows a reconfiguration command to be issued to a non-primary node.

For further your ref here, here and here


Alternative to using MIN() in a subquery?

This cannot be done without a subselect as you state.
There are two steps involved.
1.  For each name, find the record with the MIN(qty).
2.  Return the ID for that record.

There are other approaches but the two steps remain.

It appears the query you posted has some missing info. This should be the whole thing.

    SELECT id, name
FROM (
    SELECT name, MIN(qty) minqty
    FROM foo fs
    GROUP BY name
) AS a
    JOIN foo f
        ON f.name = fs.name
        AND f.qty = fs.minqty


Using the query from the SO answer Vincent Malgrat referenced, here is how you can get the transaction isolation level for the transaction in progress:

SELECT s.sid, s.serial#,
   CASE BITAND(t.flag, POWER(2, 28))
      WHEN 0 THEN 'READ COMMITTED'
      ELSE 'SERIALIZABLE'
   END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

If you are not already in a transaction you can start one with the following:

declare
   trans_id Varchar2(100);
begin
   trans_id := dbms_transaction.local_transaction_id( TRUE );
end;
/

It seems like there would be an easier way than this.  I don't know how to get the default isolation level for the session if that is what you are looking for.


is it safe to compress backups for databases with TDE enabled?

I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled.

You asked about safety so replying. Using backup compression with TDE is safe and I have been using it quite a lot. What doesn't works well, for version SQL Server 2014 and below is the backup compression mileage (the amount of compression) you would get for versions below SQL Server 2016 is not great, let me put that the backups are slightly compressed as compared to backup compression on non TDE enabled database. From the Docs which Andrew Sayer shared (Backup compression with TDE
Starting from SQL Server 2016

Starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. If MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.

Starting from SQL Server 2019

Starting with SQL Server 2019 (15.x) CU5, setting MAXTRANSFERSIZE is
no longer required to enable this optimized compression algorithm with
TDE. If the backup command is specified WITH COMPRESSION or the backup
compression default server configuration is set to 1, MAXTRANSFERSIZE
will automatically be increased to 128K to enable the optimized
algorithm

is anyone experiencing errors during restore with compressed backups for tde databases?

Show me the error, you must be restoring TDE enabled database without certificates. My best guess


Determine the currently executing statement in a long running PL/pgSQL stored procedure

One crude but effective method, available in any version, is to raise notices in your plpgsql function:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$func$
BEGIN
PERFORM ...
RAISE NOTICE 'step1';

INSERT ...
RAISE NOTICE 'step2';

DELETE ...
END
$func$ LANGUAGE plpgsql

Another possibility would be to use auto-explain with

SET auto_explain.log_nested_statements = ON;

The second method gives you a lot more than you asked for. Both are crutches and work-arounds and more or less intrusive.
The upcoming feature in 9.4 by Pavel that @Craig identified in his comment seems like a major improvement:

GET DIAGNOSTICS text_var = PG_CONTEXT;

It's already documented in the /devel manual.


How to match all words of one column against words in another column

For a quick and dirty solution - not 100% accurate due to some full-text search specifics but will get most of the matches - add a FULLTEXT key to A and then use a join with be with MATCH AGAINST as the join condition. Since MATCH AGAINST cannot use a a non-constant argument, you will have to simulate the join with a cursor in a stored procedure. Below is a fully-functional tested example:

create table a (id int not null primary key,
company_name text, fulltext key(company_name)) engine=myisam;
create table b (id int not null primary key, company_name1 text);
insert into a values(1,'dog kitten'),(2,'spoon fork'),
  (3,'fish crab'),(4,'dog mouse'),(5,'noise mouse'),
(6,'kitten dog'),(7,'noise one'),(8,'noise two'),
  (9,'noise three'),(10,'noise four'),(11,'noise five');
insert into b values(1,'dog mouse kitten'),
(2,'spoon knife fork'),(3,'fish sea crab');

drop procedure if exists ft_match;
delimiter //
create procedure ft_match()
language sql
deterministic
sql security definer
begin
    declare v_id int;
    declare v_company_name1 text;
    declare v_finished int;
  declare c cursor for select * from b;
    declare continue handler for not found set v_finished=1;
  delete from results;
  open c;
c_loop: loop
    fetch c into v_id,v_company_name1;
    if v_finished then
        leave c_loop;
    end if;
    insert into results select v_id,v_company_name1,a.id,a.company_name
     from a where match (a.company_name) against (v_company_name1 in boolean mode);
    end loop c_loop;
    close c;
    select * from results;
end//
delimiter ;

create table results (a_id int not null, a_company_name text,
b_id int not null, b_company_name text);
call ft_match();

More info on full text keys in the MySQL manual at https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

This is suitable for relatively small tables and in circumstances where high latency is acceptable. For better performance on large datasets  and perfect accuracy, you will need to implement some form of external full text indexing.


Just as a work around (since you said running the package manually works): you should be able to run the dtsx package using T-SQL via a job or make a stored proc to be called by a job, as this article explains.

For a package in the server:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'

SET @SQLQuery = 'DTExec /SQL ^"\DataTransfer^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

For a package in the file system:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'

SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO


Inserting filtered data from one table to another

You can insert the result of a SELECT using INSERT ... SELECT syntax. To get rows from one table that do not have corresponding rows in another you can use the A not in B LEFT JOIN MySQL idiom or a similar one with NOT EXIST subquery (though the latter is less efficient).

Please give your table details for more specific query.


What could cause a text comparison to fail?

This is occurring because something has changed in the way our data is being distributed. Some of our tables are not distributed in the same way as others - the records are on different nodes than the distribution algorithm says that they should be. So, when I run a query that looks for a specific value, the master sends that query only to the node that should hold that key value, and it doesn't find it. The same happens with a join - the segment node assumes that since both tables are distributed on the same join key, that the join can be performed locally. One of the tables is incorrectly distributed, so the join fails.

I don't yet know what has caused this, I will post an update if I find out. Any suggestions what it might be?

I have removed the postgresql tag as this problem turns out to be greenplum-specific.

Ok here's what happened. Our DBA was restoring from a backup, but it failed because the files in the backup didn't match the expected names. The restore wanted a file called something_0_2_something and _0_3_ and _0_4_ but the backup contained _0_22_ and _0_23_ and _0_24_ instead. So he raised a call with Pivotal, but because that was taking a while to get an answer, he just renamed them all subtracting 20 from the number and carried on with the restore. Seems like this has ended up with data being restored to the wrong nodes. We're still waiting for an answer from Pivotal about what went wrong with the file names and how we can fix it.

Further information: apparently the backup had backed up the mirror segments, not the primary segments, hence the different file names. We are working around it by taking down the primaries, restoring to the mirrors, bringing the primaries back up, and re-balancing. We don't know why it happened, the primary and mirror status looked fine. Pivotal have been looking at the system on webex and they say they've never seen this happen before so there's an achievement.


Are Postgres btree index keys compressed?

"Would the indexes be more efficient if the columns were altered down to VARCHAR(12)?"

No, and that is easy to test:

create table foo(val1 varchar(256), val2 varchar(12));
create index i_foo1 on foo(val1);
create index i_foo2 on foo(val2);

insert into foo(val1,val2)
select 'ABCDEFGHIJ','ABCDEFGHIJ'
from generate_series(1,10000);

vacuum full;

select relname, pg_size_pretty(pg_relation_size(c.oid))
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where nspname like 'fiddle%';

relname | pg_size_pretty
:------ | :-------------
foo     | 512 kB
i_foo1  | 392 kB
i_foo2  | 392 kB

dbfiddle here

  "Yes, the physical table storage for these columns is highly compressed…"

It isn't compressed (unless the string is long), it's just that for varchar (unlike char) the length specifier is merely a constraint that determines the maximum length:

  The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less.


Configuring SQL Server 2016 to allow remote connections

Figured it out!  Since the server is running on a remote VMWare ESXi instance, the subnets are completely different.  I changed:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

To:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = any profile = DOMAIN

And everything immediately started working.  Ugh, firewalls.


What does "ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"" mean?

This error message is new in postgres and is indeed related to the replication bug fixed in 9.3.3

As mentioned in the git commit from Mon, 17 Mar 2014:

  The recently-fixed bug in WAL replay could result in not finding a
  parent tuple for a heap-only tuple. The existing code would either
  Assert or generate an invalid index entry, neither of which is
  desirable.  Throw a regular error instead.

Since the parent tuple is an internal notion of postgres, the implication would be that the data structure for this relation is in a corrupted state, beyond troubleshooting at the SQL level.

The safest way out of this mess would be, if at all possible, to pg_dump the affected databases, then drop and reload them, and then recreate the constraints on this clean state.


UPDATE Table_1
SET EmailId = (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                 )
WHERE EXISTS (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                   AND Table_1.EmailId <> d.EmailId
                   AND d.EmailId IS NOT NULL
                 );


SSMS execution time queries besides SET STATISTICS

In the "Query" menu in SSMS there is an option "Include Client Statistics" that you can look at.

There is no TSQL command like SET STATISTICS that prints out cumulative stats per batch.

You could of course just do

DECLARE @StartTime DATETIME2 = SYSUTCDATETIME()

--Your code here

SELECT DATEDIFF(NANOSECOND, @StartTime, SYSUTCDATETIME())

But on my desktop the code runs far too quickly for the resolution of SYSUTCDATETIME() so most of the time it returns 0.

If you just want to know that the code runs "fast enough" to likely not need any performance tuning this may be sufficient.

For a higher resolution option you could get the value of cpu_ticks in sys.dm_os_sys_info before and after execution and try and translate this back to clock time (e.g. by getting the diff and dividing by 3,000 on a 3GHZ processor to get μs) but this may not be reliable.

You could also wrap the code to test into a temporary stored procedure and look at the stats collected for that

CREATE PROC #P AS
BEGIN
YourCodeHere:
END

GO

EXEC #P

SELECT last_execution_time,
       execution_count,
       total_worker_time  AS [total_worker_time μs],
       total_physical_reads,
       total_logical_writes,
       total_logical_reads,
       total_elapsed_time AS [total_worker_time μs]
FROM   tempdb.sys.dm_exec_procedure_stats
WHERE  object_id = OBJECT_ID('tempdb..#P')

But probably the easiest way of getting useful timings for the original code is simply to run it in a loop for a million times and time the overall duration of that - so that any inaccuracies caused by the resolution of SYSUTCDATETIME() average out to become negligible.


Do I need to install SQL Server service packs to continue receiving security patches

It's always been my belief that SQL Server needs to be updated to the latest service pack in a timely (and tested) manner to continue receiving security/bug patches.

You are correct. Some service packs will continue to have new CUs until their lifetime runs out.

  ... and that it would automatically recieve security patches any way.

They either didn't understand how this worked or flat out lied to you.

  Is this the case, or do you need to keep up-to-date (once tested) with the latest service pack?

You'll need the latest SP + Patches. For example, since you're on RTM... you're missing quite a few different security patches because they weren't made for the RTM branch after it went out of support*.

*Note that the links above show the same security updates released for supported service packs at the time. However, RTM was not one of them.


Optimizing for data that does not fit in memory

it is important to know, which queries you are running.
otherwise it is close to impossible to give you any real advise.

however, there are two things you should know:

UPDATE benefits nicely from a thing called FILLFACTOR. it can speed up things a lot.
the order of columns does have an impact on the size of the table (http://www.cybertec.at/shrinking-the-storage-footprint-of-data/). it can change things quite nicely

but as i said: i got to see your queries to really help.


Import csv file into sqlite3 table - ERROR "Insert failed: datatype mismatch"

I tried to import using your csv format

    CREATE TABLE Books1 (
    books_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    author TEXT
    );
    sqlite> .mode csv
    sqlite> .separator "-"
    sqlite> .import books1.csv books1
    books1.csv:1: unescaped " character
    books1.csv:2: unescaped " character
    books1.csv:2: unescaped " character
    books1.csv:3: unescaped " character
    books1.csv:1: expected 3 columns but found 1 - filling the rest with NULL
    books1.csv:1: INSERT failed: datatype mismatch

I fixed csv removing quotes for each record and comma that separates records

sqlite> .import books1.csv books1
sqlite> select * from books1;
337864-" Pride and Prejudice "-" Jane Austen"
783356-" Ivanhoe "-" Walter Scott"
987786-" Harry Potter and the Goblet of Fire "-" J.K. Rowling"

Did you notice hyphens after first two columns not neat.

I fixed csv file to correct format and this is the result

sqlite> select * from books;
337864,"Pride and Prejudice","Jane Austen"
783356," Ivanhoe","Walter Scott"
987786," Harry Potter and the Goblet of Fire","J.K. Rowling"


Querying pg_buffercache: relation "pg_buffercache" does not exist

You need to first connect to the database that you need to check the stats from, and in that do the create extension, after which the query should work ;)


Remote mysql only works from my computer, but no others

you must be have user with host % and grant level like root user
You can test telnet or remote mysql from cpanel server if success networking or firewall issue is solved because your mysql server can access from other client. if not you must be check from networking level and firewall level probably default port mysql is blocked.


Same query plan, different data set, very different query duration SQL Server 2012

Do the percentages and query cost even mean anything?

Not really--They have meaning, but they're unreliable.

  are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds?

Exactly--The cost is an estimate. If you look at the detail pop-up when you hover on an operator, you'll see that what you see as "Cost" in the graphical plan is actually "Estimated Operator Cost." It is based on the pre-execution estimate, and will be the same even when actual costs vary.

  Is SQL server lying to me and the actual difference in cost is the "nested loop" for the inner join that produces the final row count even though it lists that as 0% of the cost?

It's not quite lying, but it's not reality either. Again, since these are based on estimates, they can be wrong. In particular, sometimes you'll see Cost% add up to more than 100%--another indicator it's not based on real performance.

There's a better way

IMHO, SQL Sentry Plan Explorer is the easiest way to look at the actual execution performance data. It will give you actual CPU & IO information for each statement in your batch, which you can use to zero in on the most expensive parts of your batch. In this case, you can test the same statement for different customers and look at the actual execution results.

You might also want to try using SET STATISTICS IO,TIME ON and running the results through statisticsparser.com. That will give you a good sense of the IO/CPU differences between the two queries for different customers.


How to troubleshoot orm queries interfering with other applications

I would strongly suggest metadata in the connection in order to trace back to the application.  In the connection string, there is an Application Name.  There is also session data which can be used in the form of CONTEXT_INFO

https://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes

Of course all this requires application changes, but it is good for tracing and auditing in general, so baking it in from the beginning is really useful.


Delete millions of rows from a SQL table

The execution plan shows that it is reading rows from a nonclustered index in some order then performing seeks for each outer row read to evaluate the NOT EXISTS

You are deleting 7.2% of the table. 16,000,000 rows in 3,556 batches of 4,500

Assuming that the rows that qualify are evently distributed throughout the index then this means it will delete approx 1 row every 13.8 rows.

So iteration 1 will read 62,156 rows and perform that many index seeks before it finds 4,500 to delete.

iteration 2 will read 57,656 (62,156 - 4,500) rows that definitely won't qualify ignoring any concurrent updates (as they have already been processed)
and then another 62,156 rows to get 4,500 to delete.

iteration 3 will read (2 * 57,656) + 62,156 rows and so on until finally iteration 3,556 will read (3,555 * 57,656) + 62,156 rows and perform that many seeks.

So the number of index seeks performed across all batches is SUM(1, 2, ..., 3554, 3555) * 57,656 + (3556 * 62156)

Which is ((3555 * 3556 / 2) * 57656) + (3556 * 62156) - or 364,652,494,976

I would suggest that you materialise the rows to delete into a temp table first

INSERT INTO #MyTempTable
SELECT MySourceTable.PK,
       1 + ( ROW_NUMBER() OVER (ORDER BY MySourceTable.PK) / 4500 ) AS BatchNumber
FROM   MySourceTable
WHERE  NOT EXISTS (SELECT *
                   FROM   dbo.vendor AS v
                   WHERE  VendorId = v.Id)

And change the DELETE to delete WHERE PK IN (SELECT PK FROM #MyTempTable WHERE BatchNumber = @BatchNumber) You may still need to include a NOT EXISTS in the DELETE query itself to cater for updates since the temp table was populated but this should be much more efficient as it will only need to perform 4,500 seeks per batch.


What is the strategy of query evaluation in DBMS when Limit is used in query?

Simplifying down to the post table, for discussion.

Create a unique index on the post_id.

CREATE UNIQUE INDEX recent_post_id ON post(post_id)

Then query the data as such:

SELECT *
FROM post
WHERE user_id IN (your criteria)
ORDER BY post_id DESC
LIMIT 30

Of course, the most overhead may well be your nested collection of rows that you UNION together to get the criteria for recent posts.


In MySQL 8.0, there is a "straightforward" way using the windowing functions of RANK or DENSE_RANK.

If 5.7, there is no practical SQL way.  It would be better to pull the data into your app to compute the ranks.

I have a quibble with 1-2-2-2-3-... -- The guy with rank 3 is really 5th in the list.  The 3-guy will be disappointed when he drops to 4 or 5 tomorrow when the 2-guys' scores change a little.


What are the risks in running ALTER VIEW in a live production environment?

Modifying the underlying table for the view should not make an impact when running alter view. This is not true to users trying to access the view.

  If a view currently used is modified by using ALTER VIEW, the Database Engine takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, the Database Engine deletes all copies of the view from the procedure cache. Existing plans referencing the view remain in the cache but are recompiled when invoked.

Source

Exclusive locks:

  Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Schema locks:

  Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

What it does:

  During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

So it sounds like this:

    User 1 runs a query.

    User 2 alters the view.

    User 3 tries to query the view.

    User 1 finishes query, User 3 finishes query, User 2 now establishes a schema lock
preventing any other transactions until no active users are using the view.

I ran a test on my database with three concurrent windows. One would select * from the view 10,000 times, another would insert into the view 10,000 times, and the other would update the base table 1000 times. While these ran, I would run the alter view script. It behaved in a manner as I described with the scenario above.

None of these procedures were overly heavy or time consuming, basically thousands of micro transactions. The alter wedged itself in as fast as it could and altered the view between transactions when it could establish a lock. Due to this view and tables being small, I could not see the difference. Your environment will vary based on data sets.

I think your primary risk is raising the time it could take to run or complete transactions on the view. The other risk is modifying a view can break other things if they were not written to specify columns and were maintained with * instead. Other thoughts, you could be removing columns in the view in use elsewhere.


mySQL Master not getting data from slave

I think you what you are describing is a replication topology called a Star Topology

I wrote about that before

Apr 18, 2013 : Single slave - multiple master MySQL replication (Approach #1)
Apr 28, 2011 : MySQL in star topology (ServerFault)
Apr 26, 2011 : MySQL in star topology (Stack Overflow)

In that event, the Central Server must serve as a Slave in MySQL Replication paradigm until all changes in the binary logs of the Master (outside server) are pulled and executed in the Central Server.

If you pull the plug, replication breaks. It will lose its place.

MySQL does have a setting to control this in the CHANGE MASTER TO command. By default, the retry interval for MySQL 5.6 is 86400 seconds. That's 24 hours. Before MySQL 5.6, it was 60 seconds. To change the retry interval simply run,

STOP SLAVE;
CHANGE MASTER TO MASTER_CONNECT_RETRY = 60;
START SLAVE:

CAVEAT

Even if you pull the plug, it is possible for replication to lose its place and not reconnect because of incomplete transmission of binlog events. When that happens, you must reset the replication coordinates manually.

Here is how to fix replication manually in this regard:

Step 1: STOP SLAVE;

Step 2: SHOW SLAVE STATUS\G

Suppose the display looks like this

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000255
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Step 4: Record Relay_Master_Log_File and Exec_Master_Log_Pos

In this case, 'mysql-bin.000254' and 858190247

Step 5: Use Replication Coordinates from Step 4

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000254',MASTER_LOG_POS=858190247;

Step 6: Start Replication

START SLAVE;

Give it a Try !!!


Is it possible to duplicate a standby database on the fly?

Step 2: Mount or Open the Source Database Before beginning RMAN
duplication, mount or open the source database it if it is not already
mounted or open. If the source database is open, then archiving must
be enabled. If the source database is not open, and if it is not a
standby database, then it must have been shut down consistently.

Duplicating a Database

To create a standby database with the DUPLICATE command you must
connect as target to the primary database and specify the FOR STANDBY
option. You cannot connect to a standby database and create an
additional standby database.
Basic Concepts of Standby Creation with RMAN


What database technologies do big search engines use?

Pigeons.
The heart of Google's search technology is PigeonRank™, a system for ranking web pages developed by Google founders Larry Page and Sergey Brin at Stanford University:

Building upon the breakthrough work of B. F. Skinner, Page and Brin reasoned that low cost pigeon clusters (PCs) could be used to compute the relative value of web pages faster than human editors or machine-based algorithms. And while Google has dozens of engineers working to improve every aspect of our service on a daily basis, PigeonRank continues to provide the basis for all of our web search tools.
Why Google's patented PigeonRank™ works so well
PigeonRank's success relies primarily on the superior trainability of the domestic pigeon (Columba livia) and its unique capacity to recognize objects regardless of spatial orientation. The common gray pigeon can easily distinguish among items displaying only the minutest differences, an ability that enables it to select relevant web sites from among thousands of similar pages.
By collecting flocks of pigeons in dense clusters, Google is able to process search queries at speeds superior to traditional search engines, which typically rely on birds of prey, brooding hens or slow-moving waterfowl to do their relevance rankings.
When a search query is submitted to Google, it is routed to a data coop where monitors flash result pages at blazing speeds. When a relevant result is observed by one of the pigeons in the cluster, it strikes a rubber-coated steel bar with its beak, which assigns the page a PigeonRank value of one. For each peck, the PigeonRank increases. Those pages receiving the most pecks, are returned at the top of the user's results page with the other results displayed in pecking order.


MYSQL crashes on XAMPP while working with drupal

ok, i best solution which suited me well.
for being cautious in choosing the above solutions provided in my question above..
(cuz the solutions didn't guarantee 100% that my data would be intact), i made a backup for my drupal database folder just in case.
chose solution (2), which is adding the force_recovery..but no luck in fixing the problem..
so i went to the location of the corrupted file in "C:\xampp\mysql\data\bitnami_drupal7", and moved the corrupted file to another folder (NOTE: i did not delete the corrupted file).
then started mysql service, and viola! my drupal was back to life again.
and i was able to access phpmyadmin with sql queries.
(back then, mysql couldn't execute sql queries because it was in a state where its about to crash).
i went straight to the cache tables of drupal to clear them..
(back then, i wanted to clear drupals' cache since the error started, cuz ever since i knew that mysql crashes because of a table in drupal; but mysql couldn't have enough time to execute sql queries in phpmyadmin, again cuz of the the crash).
hence, after clearing all drupal cache at "bitnami_drupal7" with this code:

TRUNCATE TABLE cache;
TRUNCATE TABLE cache_block;
TRUNCATE TABLE cache_bootstrap;
TRUNCATE TABLE cache_field;
TRUNCATE TABLE cache_filter;
TRUNCATE TABLE cache_form;
TRUNCATE TABLE cache_image;
TRUNCATE TABLE cache_menu;
TRUNCATE TABLE cache_page;
TRUNCATE TABLE cache_path;
TRUNCATE TABLE cache_token;
TRUNCATE TABLE cache_update;

then, i replaced the corrupted file back again to try if it becomes recognized or not, and started mysql..and it worked!!
mysql never crashed again and complain about that file being corrupted anymore, and that saved me from deleting or affecting any data.


Contained User on View - Select permission denied on object 'table1', database 'Database1', schema 'test'

SELECT permission on a view doesn't grant SELECT permission on the referenced tables.  Instead if the view and the tables have the same owner, there is an "ownership chain" between them and permissions on the tables are not checked.
So ensure that the view and tables have the same owner.
Objects are owned by the owner of their schema by default.  It is possible (but unwise) to change the ownership of a single object.  Anyway you can list the objects and their owners like this:
select o.name, type_desc, user_name(coalesce(o.principal_id,s.principal_id)) owner
from sys.objects o
join sys.schemas s
  on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
  and type_desc in ('USER_TABLE','VIEW')
order by type_desc, name


Unable to create read-only user in PostgreSQL 9.5

The question doesn't specify what user executed the following statement:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

but based on the surrounding code, let's guess it's postgres. That's the reason why readonly gets a permission denied trying to read it: it is the future creator of the table, dbowner, that should execute this statement.
(to be sure, note that you should also mention to what database you're connected when running the mentioned commands).

As stated by the doc (emphasis mine) at https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html :

  You can change default privileges only for objects that will be
  created by yourself or by roles that you are a member of.


Postgresql split date range by business days then aggregate by month

Assuming the table has a primary (or unique) key, then you can calculate the "value per day" using this:

select to_char(dt, 'yyyy-mm') as month,
       value,
       value::numeric / count(*) over (partition by id) as value_per_day
from data
  left join generate_series(start, "end", interval '1 day') as t(dt) on true
where extract(dow from dt) not in (0,6) ;

generate_series() generates rows for each day between start and end, and the where clause removes the weekends.
id is the unique identifier for the original rows it is required to be able to count the number of days "per row". With that result we can now aggregates the final result:

select month, sum(value_per_day)
from (
  select to_char(dt, 'yyyy-mm') as month,
         value,
         value::numeric / count(*) over (partition by id) as value_per_day
  from data
    left join generate_series(start, "end", interval '1 day') as t(dt) on true
  where extract(dow from dt) not in (0,6)
) t
group by month
order by month;

Online example: https://rextester.com/KPGOFU25611


Both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON

From a previous answer to this:

  If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET
  TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will
  not get any of the benefits.

  If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to
  modify any code. MS SQL Server automatically applies snapshot
  isolation for that table.

Basically, with only ALLOW_SNAPSHOT_ISOLAION, you still have to specify the isolation level in the query/SP to make it use that isolation level.

With READ_COMMITTED_SNAPSHOT set to ON, the DB is automatically in that mode and there's no need to modify your queries.

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

In the answer I linked to above, there is a link to Kim Tripp's video on snapshot isolation.  It's pure gold.


Sure, you can query V$BACKUP_PIECE in target database or RC_BACKUP_PIECE in RMAN catalog:

SQL> select encrypted, tag from v$backup_piece;

ENC TAG
--- --------------------------------
NO  TAG20180926T211157
NO  TAG20180926T211159
YES TAG20180926T211224
YES TAG20180926T211227


Drop partition scheme with orphan indexes

but the index names are NULL (and index id is 0)

That's because they are heaps.  BOL Reference on sys.indexes

One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme.  Then once you have those tables off of that partition scheme you should be able to continue with removing it.

create clustered index IX_YourIndex
on dbo.YourTable (YourCiKeyCol)
on SomeOtherFilegroup;


MySQL allows you to create a temp table with a existing name because they don't have the same "scope". A temporary table is visible in the session only, and it is dropped at session ending. If you have the same name, MySQL "hide" the original table until you drop your temp table.

You can refer to the Temporary Tables section in the MySQL documentation

Max.


Migrating MySQL 8.0.20 database to AWS Aurora

The biggest potential problems with going backward from 8.0:

CTEs
Windowing functions
new COLLATIONs
Various minor syntax changes that are best found by trying the transfer.

If you are not using those, a mysqldump might produce a file that can be read by 5.6 or 5.7.  Try it.  Come back with specifics if that fails.


CTE: Get all parents and all children in one statement

If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple UNION:

WITH RECURSIVE
    -- descendants
    rec_d (id, name) AS
    (
      SELECT tree.id, tree.name FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id
    ),
    --  ancestors
    rec_a (id, name, parent_id) AS
    (
      SELECT tree.id, tree.name, tree.parent_id FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name, tree.parent_id FROM rec_a, tree WHERE tree.id = rec_a.parent_id
    )
SELECT id, name FROM rec_a
UNION
SELECT id, name FROM rec_d ;

If we haven't got any errors above, we can them improve it:

change the final UNION to UNION ALL by putting the starting node(s) in only one of the CTEs.
use JOIN .. ON instead of the implicit joins.
fix mismatches between SELECT and CTE column lists.

The query becomes:

WITH RECURSIVE
    -- starting node(s)
    starting (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t
      WHERE t.name = 'Father'          -- this can be arbitrary
    ),
    descendants (id, name, parent_id) AS
    (
      SELECT s.id, s.name, s.parent_id
      FROM starting AS s
      UNION ALL
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t JOIN descendants AS d ON t.parent_id = d.id
    ),
    ancestors (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t
      WHERE t.id IN (SELECT parent_id FROM starting)
      UNION ALL
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t JOIN ancestors AS a ON t.id = a.parent_id
    )
TABLE ancestors
UNION ALL
TABLE descendants ;