AlwaysOn Availability Group Automatic Failover does not work

If I disconnect DEV-AWEB5

Define "disconnect", if you will.  My guess is you kept the box up but took SQL Server down.

  I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping

That's because the listener is just a virtual network name (VNN) within the WSFC cluster resource group for the represented availability group.  Your DEV_AWEB5 node still owns the cluster resource group, but it's just the AG cluster resource most likely that is in a failed state.  The VNN must still be online (expected behavior).  It's simply pointing to whatever node is owning that resource group (in this case, DEV-AWEB5).  In fact, if you had PowerShell remoting enabled, and you ran the following:

Invoke-Command -ComputerName "YourListenerName" -ScriptBlock { $env:computername }

Likewise, if you can RDP into DEV-AWEB5 (provided you have the capability and accessibility, etc.) then you'd be able to RDP using the listener name (mstsc /v:YourListenerName).  It's just a VNN.

The return of that would be the computer name of your owning node.

By all of your symptoms, I'd be willing to bet that you've reached your failover threshold.  The failover threshold determines how many times the cluster will attempt to failover your resource group in a specified time period.  The default of these values max failovers n - 1 (where n is the count of nodes) in a period of 6 hours.  You can see that through the following WSFC PowerShell command:

Get-ClusterGroup -Name "YourAgName" |
    Select-Object Name, FailoverThreshold, FailoverPeriod

That just gives you the settings (which you can modify if you so choose, of course).

The best way to prove that this is the case for you, you would need to generate the cluster log (the system event logs only go into detail as far as " has failed", or something like that).

Get-ClusterLog -Node "YourClusterNode" -TimeSpan <amount_of_minutes_since_failure>

That'll by default get put into the "C:\Windows\Cluster\Reports" folder, and the file is called "Cluster.log".

If you were to open up that cluster log, you should be able to find the following string in there, indicating exactly what happened and why it happened:

  Not failing over group [YourClusterGroupName], failoverCount [# of failovers], failover threshold [failover threshold value], nodeAvailCount [node available count].

The above message is simply WSFC telling you that it will not failover your group because it's happened too much (you hit the threshold).

Why does this happen?  Simply to prevent the Ping-Pong effect of cluster resources going back and forth too frequently between nodes.

Whereas this would be common to hit these thresholds in failover testing, in production it would typically point to a problem that should be investigated.


Database design - Projects with multiple Clients but only specific client contacts

You need to add another table: ProjectContact that will be an intersection between ProjectClient and ClientContact.  This will let you say which contacts apply to a particular project.

The issue that you're going to have is controlling the integrity of the data.  You want to make sure that all project contacts come from clients that are actually on the project.  This can be done with declarative referential integrity (DRI) or by application logic.  Based on your table design, you seem to be pretty firmly in the camp of "every table gets a surrogate key" which makes using the DRI approach a little harder.

To use DRI for this, you want the to use a compound PK for both ProjectContract and ClientContact and in each case it should contain ClientID.  The same is true for the new intersection table, ProjectContact.  This will ensure that ClientID will be the same and your data must make sense.

Here is an illustration of what I'm suggesting...


Exclusively access a table and prevent other queries, even read queries

You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a TOP() cluse will be able to set the flag on at most one row.  By using the OUTPUT cluse you can return this to the application automically.

I define a simple test table and populate it:

create table dbo.T(id int, IsProcessing bit default 0);

insert dbo.T(id) values (1),(2),(3),(4);

The output clause needs a table variable to receive the changed values:

declare @U table (id int);

A bit of debug code to make the "before" and "after" states obvious:

select 'Before' as B, * from dbo.T;

And the statement itself:

update top (1) dbo.T
set IsProcessing = 1
OUTPUT INSERTED.id
INTO @U(id)
where IsProcessing = 0;

And the results:

select 'After' as A, * from dbo.T;
select * from @U;

This is the output from the second execution of the above.

B       id  IsProcessing
Before  1   1
Before  2   0
Before  3   0
Before  4   0

A       id  IsProcessing
After   1   1
After   2   1
After   3   0
After   4   0

id
2

The row with id=2 had gone from IsProcessing=0 to IsProcessing=1 and that id is returned in the table variable.

With this trivial data the rows are processed in the sequence they were created.  In a more complex environment the optimizer may choose any row that matches the where clause.  If you have a requirement to process rows in, say, time sequence further qualification will be required.

I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.

It is of course possible that there are no rows with IsProcessing=0 at the time the statement runs. In this case the table variable will have zero rows.

To fully isolate each transaction from the other you could try sp_getapplock. This will add overhead and reduce concurrency.  You have to be careful to release the app lock as soon as you can in both success and failure scenarios.


Rolling count of total transactions over time

What you want is called the cumulative sum, you can do something like:

create table transactions (transactionid int, d date);
insert into transactions (transactionid, d)
    values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
         , (4, '2014-08-18'), (5,'2014-08-20');

select x.y, x.w,  count(1)
from (
   select distinct year(d) as y, week(d) as w
   from transactions
) as x
join transactions y
    on year(y.d) < x.y
    or ( year(y.d) = x.y
     and week(y.d) <= x.w )
group by x.y, x.w;

+------+------+----------+
| y    | w    | count(1) |
+------+------+----------+
| 2014 |   31 |        2 |
| 2014 |   33 |        5 |
+------+------+----------+

I did not see your additional request for 2 2 for 2014. You can do that by replacing:

select distinct year(d) as y, week(d) as w
from transactions

...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.


I would use SSIS to do this. Try these steps:

I would start out by creating an object variable to store your datasheets' names
I would then create a VB task to pull out the names of your spread sheets and put the results into the object variable
Then create a foreach loop that uses the object variable as the collection
create another variable and toss in the name of the current sheet
add a data flow task inside the loop that uses the the current sheet as your data source
add your access table as your destination.

Here is the VB script you can use to pull out the excel metadata. You need to input the variables from your foreach loop to get this to work.

   Public Overrides Sub CreateNewOutputRows()

    Dim excelFilePath As String = Me.Variables.ExcelFilePath.ToString()
    Dim strCn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" + excelFilePath + ";Extended Properties=Excel 8.0"
    Dim dtTables As DataTable
    Dim tableName As String

    Dim cn As OleDbConnection = New OleDbConnection(strCn)
    cn.Open()
    dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

    For Each row As DataRow In dtTables.Rows
        tableName = row("TABLE_NAME").ToString()

        OutputBuffer.AddRow()
        OutputBuffer.EXCELFILEPATH = excelFilePath
        OutputBuffer.TABLENAME = tableName

    Next

    cn.Close()
    OutputBuffer.SetEndOfRowset()
End Sub

It's a bit tricky but very do-able. Best of luck.


You should be able to use pg_standby.

I cannot gurantee it, though, as I haven't tried it.


Is there a better way of displaying 'Count' of records beside other columns in a select query?

What you used there should not cause a problem, because it is a scalar subquery, meaning its result would be automatically cached, and as it has only 1 distinct value, even if test1 has 5 million rows, the inner query would not be executed 5 million times.
But you can use the analytic version of count:
select c_num,
       c_first_name,
       c_last_name,
       count(*) over () as members_cnt
from test1;


In SQL Server, is parallelism per operator, or something else?

He says that in a parallel plan, every operator gets DOP threads.

No. This is at best misleading, but closer to being simply wrong.

In a serial plan, every operator 'gets' one thread, but that thread is the same thread for all operators. The principle is similar for parallel plans.

Each parallel operator is run by DOP threads, but those threads are not exclusive to a particular operator, they are shared among operators within the same parallel branch.

Branch boundaries are delimited by Parallelism operators (Demand, Repartition, and Gather Streams). The diagram below shows a parallel plan with three branches:

Reproduced from the article referenced at the end of this answer

  So if you have MAXDOP 8 and your query has 4 parallel operators it'll use 32 threads at once.

No. You can't just multiply DOP by the number of operators to get the number of threads. The number of threads reserved for parallel branches is the number of parallel branches (not operators) multiplied by DOP.

The number of threads that can be active at the same time for a single parallel query is limited to DOP in SQL Server 2005 and later. SQL Server achieves this by allocating threads to DOP schedulers.

  I also read that it might just be 8 for the whole query, which seems like too few.

At DOP = 8 this would be correct for a plan with a single parallel branch. There can be multiple parallel plan branches in a parallel plan. For a plan with n parallel branches, the thread reservation for parallel workers is n * DOP.

See Parallel Execution Plans – Branches and Threads by Paul White.

Note: The number of branches reported in execution plans is normally the number of branches that may possibly execute concurrently (due to blocking operators, threads in one branch may sometimes be safely recycled for a later branch).


You could use a Document-oriented database for this. You could then create a program in your preferred language to import the existing documents into the db, parsing the folder structure for the metadata (customer, job#, etc).


List column value for all databases that have a specific table and column

I really, really, really recommend against sp_MSforeachdb. It's easy, sure, but it is undocumented, unsupported, and buggy as all get out.

Here is a dynamic SQL solution:

CREATE TABLE #x([Database] sysname, ApplicationVersion varchar(32));

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  BEGIN TRY
    INSERT #x EXEC ' + QUOTENAME(name) +N'.sys.sp_executesql
    N''SELECT DB_NAME(), MAX(Version) FROM dbo.ApplicationVersion'';
  END TRY
  BEGIN CATCH
    PRINT CHAR(32);
  END CATCH;'
FROM sys.databases
WHERE state = 0; -- database is online

EXEC sys.sp_executesql @sql;

SELECT [Database], ApplicationVersion FROM #x;

DROP TABLE #x;


How to achieve using TRIM command on SELECT * From TABLE

If I understand you correctly, this PL/SQL will do the job for you:

DECLARE
   CURSOR c1 IS SELECT table_name FROM USER_TABLES;
   QUERY varchar(4000);
BEGIN
  FOR rec IN c1 LOOP
    SELECT 'SELECT '||RTRIM(EXTRACT(COLXML,'//COLUMN_NAME/text()'),',')||' FROM '||rec.table_name||';' COL_STRING INTO QUERY
    FROM (SELECT E.table_name,
      xmlelement("USER_TAB_COLUMNS",
      XMLAGG(xmlelement("COLUMN_NAME", case when data_type like '%CHAR%' then 'TRIM(' end ||
      column_name||
      case when data_type like '%CHAR%' then '),' else ',' end)
    ORDER BY COLUMN_NAME)) COLXML
    FROM USER_TAB_COLUMNS E WHERE TABLE_NAME=rec.table_name
    GROUP BY E.table_name );
  DBMS_OUTPUT.PUT_LINE(QUERY);
  END LOOP;
END;
/

Make sure you set serveroutput on to get the output from DBMS_OUTPUT.

Change the query in the CURSOR to alter the table list it generates queries for.

Note: Doesn't do anything for 'CLOB'/'BLOB' columns.

Example output:

SELECT A,TRIM(B),TRIM(C) FROM BLAH;
SELECT A,B FROM EDITIONTEST;
SELECT C,D FROM EDITIONTEST2;

PL/SQL procedure successfully completed.

Another thing to mention; You're going to be limited to the 4000 character limit, but will be OK unless you have tables with hundreds of columns.


MySQL difference between wait_timeout and connect_timeout

Right from the MySQL Documentation

wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it.
connect_timeout : The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake

EXAMPLES

If wait_timeout is 1800 (30 Minutes), the DB Connection will close in 30 minutes if the connection is idle (indicated by Sleep in the command column of the processlist).
If connect_timeout is 10, then mysqld will reject you if you cannot login (authenticate) in 10 seconds.


Never detach a Suspect database. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE with FOR ATTACH_REBUILD_LOG option?

These commands should have done the trick:

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;

I wrote a post for this situation:

SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

You asked about the difference between:

DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS) and
ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

The thing is that you can run both to rebuild the log file, but with CHECKDB you rebuild the log and check database for integrity errors as well.

Also the second (alter database) will not work if there were active transactions (not written to disk) when the log file was lost. At start-up or attach, SQL Server will want to perform recovery (rollback and rollforward) from the log file which is not there. It happens when a disk crashes or an unexpected shutdown of the server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.

DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on a database in emergency status checks the database for inconsistency errors, tries first to use the log file to recover from any inconsistencies. If this is missing, the transaction log is rebuilt.
ALTER DATABASE REBUILD LOG ON... is an undocumented procedure and requires a subsequent DBCC CHECKDB to fix any errors.


you can create a table with same schema in another database first and copy the data like this. Let say my new Database name is NewDataBase and I want to copy data from table Sales.SalesPerson of [AdventureWorks2012] database and both database are in same server.

CREATE TABLE [NewDataBase].[dbo].EmployeeSales
( BusinessEntityID   varchar(11) NOT NULL,
  SalesYTD money NOT NULL
);
GO

INSERT INTO [NewDataBase].[dbo].EmployeeSales
    SELECT BusinessEntityID, SalesYTD
    FROM [AdventureWorks2012].Sales.SalesPerson;
GO


What can I do about this MariaDB 10.0 error?

You may have moved around manually the tablespaces or improperly importing the tables physically. The error assertion is a detection by MySQL InnoDB that pages have internal values that do not correspond to their physical location, thus forcing the kill of the server to avoid further corruption.

I have very very rarely seen that as a problem due to InnoDB programming (at least on upstream and stable MySQL), and more usually due to a problem in datafile or configuration management by an operator. While it should be possible to go back to a consistent state, without knowing more about what kind of processes you have performed, I would start by recommending performing a bull backup of your system so no data is lost.


Understanding the use of Sql command parameters

Your stored procedure, itself, in a vacuum, as written now, does not appear vulnerable to SQL injection... but that is not the only consideration, and is not the final answer.

It's always possible that it could be updated later and become vulnerable, but there's yet something else to consider.

For a much bigger potential problem, take one step back and look at your calling convention.

If someone could manage to pass the following string value to your application as txt_f8.Text... it would be obediently concatenated into your MySqlCommand()...

stuff'); GRANT ALL PRIVILEGES ON *.* TO 'hacker'@'%' IDENTIFIED BY 'pwn3d'; SELECT ('1

...then what would be executed by your server?

Possibly, nothing.  Possibly, way too much.

This randomly-googled tutorial reinforces the point of not crafting queries with string concatenation, and using SqlParameter instead:

  This situation invites a hacker to replace that string with something
  malicious. In the worst case, you could give full control of your
  computer away.

  Instead of dynamically building a string, as shown in the bad example
  above, use parameters. Anything placed into a parameter will be
  treated as field data, not part of the SQL statement, which makes your
  application much more secure.

Certainly not a good trade-off for having to do less typing.

I'm a MySQL DBA and not a .NET person, but from what I can tell from a quick search it appears that declaring CommandType.StoredProcedure allows you to take better advantage of the capabilities of stored procedures, by making some of the parameters INOUT or OUT, and not just IN.  This might be useful, for example, if you wanted one of the parameters to be the ID of the newly-inserted user, which the SP could return to your code.

If you don't need return values, you don't have to do it that way... but feedback/confirmation isn't usually a bad thing.


How to know the current log sequence number in 5.0.X

I actually wrote about this back on August 27, 2012 : Proper tuning for 30GB InnoDB table on server with 48GB RAM. Here is an excerpt from my past answer (under the section entitled Log File Size):

EXCERPT

5MB is the default size for innodb_log_file_size. Percona's mysqperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out

SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

END OF EXCERPT

Where do you go from here?

Run these commands every hour for a week. When you  ascertain the highest number for  @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?

CONCLUSION

Since you can figure out the proper InnoDB Log File Size in this manner, there is no need to pool the show engine innodb status\G for anything, especially since you said it is not available in MySQL 5.0.

Keep in mind that my answer works for MySQL 5.1/5.5. So, how do you did this for MySQL 5.0?

It's a good thing the status variable Innodb_os_log_written exists in MySQL 5.0.

Looking back, here is your original question:

mysql> pager grep sequence
PAGER set to 'grep sequence'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

With MySQL 5.0 you have to do this:

mysql> show global status like 'Innodb_os_log_written'; select sleep(60); show global status like 'Innodb_os_log_written';

Then subtract and multiply like you did before:

mysql> show global status like 'Innodb_os_log_written'; select sleep(60); show global status like 'Innodb_os_log_written';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 500034464256 |
+-----------------------+--------------+
1 row in set (0.01 sec)

+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.01 sec)

+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 500047017984 |
+-----------------------+--------------+
1 row in set (0.01 sec)

mysql> select (500047017984 - 500034464256) / 1024 / 1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 11.97216797 |
+-------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


MySQL: bind-address 0.0.0.0 in my.cnf does not work?

The configuration option bind-address tells MySQL what interfaces to listen on.  By receiving the error Access denied for user 'root'@'mguru.lnx.gr' we can see that is actually working fine.

What you will need to do is modify MySQL's privilege system so that you are able to connect from 'mguru.lnx.gr'.  The potentially confusing part of privileges in MySQL is that it is the combination of a username + the host.

This is covered in the MySQL manual here.


"pg_wal" cleans itself up. You should almost never touch pg_wal by hand. If it is not cleaning itself up, you need to figure out why and fix the underlying issue.
One possible reason is that you have a replication slot which is holding it back.  Either a replica is using a slot and is unable to keep up. Or you have a slot which has no replica attached, for example you destroyed the replica but didn't drop the slop it used to occupy.  You can see what slots you have by querying pg_replication_slots, and if necessary drop one with pg_drop_replication_slot, both run on the master.  You would look for the slot with the oldest non-NULL value of "restart_lsn".
Another reason is that you have "archive_mode" turned on, but your "archive_command" is constantly failing or can't keep up.  You will see warnings about this in your server log file if it is failing.
"pg_archivecleanup" is used to clean up a WAL archive.  "pg_wal" is not the archive, it is the live WAL files. You are lucky you didn't destroy your database by monkeying around in there.


You might want to consider abstracting product into a SKU for the order detail.  Instead of having order details show which specific pills were sold, show more generically which medicine was sold.  The reason for this is that you may sell, for example, 100 pills of a certain type.  Perhaps you only have 40 left of one batch and so you fill the order with those plus 60 from another batch.  That would show as two order details on a sales receipt, which would be less than ideal, since the customer doesn't want or need to know this detail.

Another advantage of abstracting the product list from the actual product batches is that you can get a simple list of products without repetition.  This is just good database normalization.  The name of the product and the batch number of the product have different functional dependencies, so they don't belong in the same table under Third Normal Form (3NF).

You can and should have inventory movement records that record the specific stock used to fill each order.

You should store the amount actually paid with the order details.  This is because the MSRP price can change over time so failing to store the price actually paid could lose information.  Similarly, you may decide to sell for a promotional price, which would be lost information if you only store the regular price (or MSRP).

I recommend against having inventory ins and outs in separate tables.  All inventory movements should be in one table with either two quantity columns (in/out) as per old-style accounting methods or preferably, a single quantity column with positive and negative numbers for receipts and sales, as per newer computerized accounting methods.  Keeping inventory in a single table reduces your code complexity considerably.


SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined

I haven't done any comparative testing of the two (yet) nor seen any articles on the topic. There is an Optimizing MERGE Statement Performance article on Technet but this doesn't include any comparisons with the update/insert syntax.

I can however suggest an improvement over your original syntax which eliminates the IF EXISTS lookup:

UPDATE
    dbo.tblCustomer
SET
    CustomerName = @CustomerName
WHERE
    CustomerID = @CustomerID;

IF (@@ROWCOUNT = 1)
BEGIN
    SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
    INSERT
        dbo.tblCustomer
        (Taalnaam)
    VALUES
        (@CustomerName);

    SELECT @New_ID = SCOPE_IDENTITY();
END

You may also be interested in Mythbusting: Concurrent Update/Insert Solutions, which includes some examples of MERGE usage.


Representing an IS A relationship in a SQL database

I can see how colloquial English (or ESL) might indicate that a person indeed "is" a job role. However, you're dealing with a system of possibilities and ownership rather than the fact that, at least Americans, like to reduce a person's existence to their career role.

"She is a police officer" - works during standard conversation.

"He has a role of police officer" - reflects the possibility of no, one, or more roles that a person may own. You're dealing with composition not subtyping.

CREATE TABLE is one of the most basic aspects of SQL... you're only hurting yourself if you don't take the time to actually learn the language. Here's one good starting resource. Your next step will likely be to add a Foreign Key constraint.


How to grant Unmask permission to a user at table level and column level in sql server?

Unfortunately, there is no granularity for the UNMASK permission in SQL Server, users either see all masked data as masked or unmasked depending on whether they have UNMASK granted.

You could create a low-privilege user that has SELECT and UNMASK and create a stored procedure to retrieve only the columns you're after into a temporary table while impersonating this user. These results can then be joined back to your masked table after reverting the impersonation so that the columns that should be masked are and those that should be unmasked are retrieved from the temp table.


How large should my pool size be? And should I use pgbouncer?

There really isn't enough to go on here.  10 concurrent connections isn't a lot for most setups.

The best advice I can give is start simple and when you start to run into cases where performance is starting to become an issue then look at more complex solutions unless you know it will be an issue (say 1000 concurrent users and millions of queries from the web every day).  Otherwise you build in complexity you may never need.  However, by all means monitor and keep an eye out for scaling out solutions that work for you (I recommend keeping an eye on Slony and Postgres-XC in this regard in addition to other solutions). They are at the upper end of complexity but if you need something that can do anything you need it to, they are the solutions.

As it is though, generally for the number of concurrent connections you are talking about, there is no need to consider something complicated until you start to need it.  Then you will have a better idea of what you need in a solution.

Premature optimization is the root of all evil.


Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)

I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.

IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
  BEGIN
    RAISERROR('%s is secondary', 11, 1, @@servername );
  END

Configure this step to quit the job on failure.  This is better than trying to run something that fails because it's hitting a secondary.


Adding nullable column to table costs more than 10 minutes

There are a couple of misunderstandings here:

The null bitmap is not part of the heap tuple header. Per documentation:

  There is a fixed-size header (occupying 23 bytes on most machines),
  followed by an optional null bitmap ...

Your 32 nullable columns are unsuspicious for two reasons:

The null bitmap is added per row, and only if there is at least one actual NULL value in the row. Nullable columns have no direct impact, only actual NULL values do. If the null bitmap is allocated, it's always allocated completely (all or nothing). The actual size of the null bitmap is 1 bit per column, rounded up to the next byte. Per current souce code:

#define BITMAPLEN(NATTS) (((int)(NATTS) + 7) / 8)

The null bitmap is allocated after the heap tuple header and followed by an optional OID and then row data. The start of an OID or row data is indicated by t_hoff in the header. Per comment source code:

  Note that t_hoff must be a multiple of MAXALIGN.

There is one free byte after the heap tuple header, which occupies 23 bytes. So the null bitmap for rows up to 8 columns effectively comes at no additional cost. With the 9th column in the table, t_hoff is advanced another MAXALIGN (typically 8) bytes to provide for another 64 columns. So the next border would be at 72 columns.

To display control information of a PostgreSQL database cluster (incl. MAXALIGN), example for a typical installation of Postgres 9.3 on a Debian machine:

    sudo /usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/main

I updated instructions in the related answer you quoted.

All that aside, even if your ALTER TABLE statement triggers a whole table rewrite (which it probably does, changing a data type), 250K are really not that much and would be a matter of seconds on any halfway decent machine (unless rows are unusually big). 10 minutes or more indicate a completely different problem. Your statement is waiting to get a lock on the table, most likely.

The growing number of entries in pg_stat_activity means more open transactions - indicates concurrent access on the table (most likely) that has to wait for the operation to finish.

A few shots in the dark

Check for possible table bloat, try a gentle VACUUM mytable or a more aggressive VACUUM FULL mytable - which might encounter the same concurrency issues, since this form also acquires an exclusive lock. You could try pg_repack instead ...

I would start by inspecting possible issues with indexes, triggers, foreign key or other constraints, especially those involving the column. Especially a corrupted index might be involved? Try REINDEX TABLE mytable; or DROP all of them and re-add them after ALTER TABLE in the same transaction.

Try running the command in the night or whenever there is not much load.

A brute-force method would be to stop access to the server, then try again:

Force drop db while others may be connected in postgresql

Without being able to pin it down, upgrading to the current version or the upcoming 9.4 in particular might help. There have been several improvements for big tables and for locking details. But if there is something broken in your DB, you should probably figure that out first.


As we suspected, you are making the changes on 4306 and connecting on default port which is the server running on 3306. use these :

mysql -u user -p --port 4306


How to identify queries that cause high Logical I/O?

Not enough rep to comment, so posting here.  Below query should get you started:
SELECT
      qs.total_logical_reads
    , qs.total_logical_writes
    , qs.total_physical_reads
    , qs.last_logical_reads
    , qs.last_logical_writes
    , ISNULL(OBJECT_NAME(st.objectid), 'Ad-hoc Query') [Proc Name]
    , st.text
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
ORDER BY (qs.total_logical_reads + qs.total_logical_writes) DESC

Note - the totals are cumulative over time, so careful of the "gotchas" of queries which have been using little IO each execution but have been executed frequently over longer period of time versus queries which just started using more IO and/or executing more frequently. Check out more info about sys.dm_exec_query_stats and other potentially useful queries on the MS doc.


SQL Server 2000 - Stripping time from datetime column results using DATEADD and DATEDIFF is returning result with time still?

A datetime column always includes a time part. If you set a datetime column to '2017-05-10', it'll show up as 2017-05-10 00:00:00.

When you want to compare "dates only" with datetime columns, you set the time part to midnight on both dates. This is generally referred to as "removing" the time part.

If you want to eliminate the time part from your output, there are various options. You can build a date in whatever format you wish by pulling the various fields out with DATEPART. Most commonly, however, I've seen people convert the date to a string and drop the time portion:

SELECT CONVERT(varchar(10), GETDATE(), 120);

Note that CONVERT is being used here for formatting the date value for output, not for changing it for comparison purposes. I'd still do that as you did in your code.


Why does this MySQL 5.6 stored procedure not compile with MySQL Workbench?

You have to put DELIMITER $$ at the beginning of MySQL script file.

DELIMITER $$
CREATE PROCEDURE spDataLocationSetActive
(
          IN driveLetter5  varchar(128),
          IN active         tinyint(1)
)
BEGIN
DECLARE J INT;
Select count(*) INTO J
                        From    DataLocations dl
                        Where   dl.DriveLetter = '/'  LIMIT 1;

IF (J IS NULL) THEN

        SELECT dl.Enabled FROM DataLocations dl;
ELSE
        SELECT dl.Enabled FROM DataLocations dl;
END IF;

END


You cannot use a subselect in a check constraint; you can only reference columns from the same table.

However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.


Have x number of rows returned for one record

You can use CROSS APPLY and a CASE statement in your WHERE clause to produce the results you're after.

Setup:

CREATE TABLE DateTable
(
 ID INT,
 DateValue DATETIME,
 Weekly INT,
 Biweekly INT,
 Monthly INT
)

;WITH CTE AS
(
  SELECT 1 AS ID,
    CAST('2020-01-01' AS DATETIME) AS DateValue,
    1 AS Weekly,
    0 AS Biweekly,
    0 AS Monthly
  UNION ALL
  SELECT ID + 1 AS ID,
    DATEADD(DAY, 1, DateValue) AS DateValue,
    CASE WHEN (ID + 1)%2 <> 0 AND (ID + 1)%3 <> 0 THEN 1 ELSE 0 END AS Weekly,
    CASE WHEN (ID + 1)%2 = 0 THEN 1 ELSE 0 END AS Biweekly,
    CASE WHEN (ID + 1)%3 = 0 AND (ID + 1)%2 <> 0 THEN 1 ELSE 0 END AS Monthly
  FROM CTE
  WHERE DATEADD(DAY, 1, DateValue) < DATEADD(YEAR, 1, '2020-01-01')
)

INSERT INTO DateTable
SELECT ID, DateValue, Weekly, Biweekly, Monthly
FROM CTE
OPTION (MAXRECURSION 400)

Query:

SELECT d1.ID, d2.DateValue, d1.Weekly, d1.Biweekly, d1.Monthly
FROM DateTable d1
CROSS APPLY
(
  SELECT *
  FROM DateTable
) d2
WHERE d1.ID IN (1, 2, 3)
  AND d2.DateValue >= d1.DateValue
  AND d2.DateValue <=
    CASE
      WHEN d1.Weekly = 1 THEN DATEADD(DAY, 6, d1.DateValue)
      WHEN d1.Biweekly = 1 THEN DATEADD(DAY, 13, d1.DateValue)
      WHEN d1.Monthly = 1 THEN DATEADD(MONTH, 1, d1.DateValue)
     END

You can see it working in this db<>fiddle example.


In SQL*Plus, how do I run @https://here.is.url/script.sql behind a proxy?

Set the environment variable HTTP_PROXY.

HTTP_PROXY=http://username:password@proxyhost


Get count for different columns with different conditions from same table into one result table?

You can use filtered aggregation:
select count(*) filter (where is_jump) as is_jump_count,
       count(*) filter (where is_throw) as is_throw_count,
       count(*) filter (where is_punch) as is_punch_count
from the_table;


SORT_SEQUENCE is wrong in as400 library indexes. Can this cause performance issues?

Solution: Sort Sequence

In short the problm occured from incorrect client odbc setup, making the indexes not *HEX but langauage specific , rendering the index ussage useless fromn server point of view.


Organizing and assigning privileges to multiple objects in MySQL

MySQL does not support multiple schema in Database, you must create separate database and grant permissions to roles as per your requirements.


Alternatives to LIKE and FTS for mid name search

There are only two ways to search text data in SQL Server and you've just eliminated both of them.  I assume you have a name field which has first name and last name in it and you want to be able to search for everyone with the name John?  Can you provide some examples of what isn't working correctly?


Authorization error running db2 command line with db2inst1

update dbm cfg is run by your user in the sh, not the user you connected as. You can attach to the instance as a particular user:
db2 attach to instance db2inst1 user db2inst1

or:
db2 attach to instance db2inst1 user db2inst1 using <password>

and then:
db2 update dbm cfg using INTRA_PARALLEL YES

Documentation can be founds at ATTACH
In addition to that, I find it much easier to run db2 directly from the sh (as I did above) instead of starting db2 clp separately. You can use the sh as a host language and for example do loops over a resultset:
#> db2 connect to mydb
#> for t in $(db2 -x "select ..."); do
       echo "The table is ${t}"
   done

Of course just a silly example, but it is a powerful mechanism for running db2 from a sh script


How to restore a pg_dumpall dump without CREATE INDEX?

I can see one workaround for this, by using pg_dumpall in two steps:

pg_dumpall --schema-only ....

Then edit the file and extract the index definitions into a second file. You also need to extract the foreign keys, because you have to run them manually after the import (probably together with the index creation script)

Then run that script (without the indexes) to create the (empty) tables. You

pg_dumpall --data-only ....

Then run that script to import the data into the new database. After that run the FK and index creation scripts.


How to interpret Azure SQL QueryStore Query Performance Insight

It’s possible that CPU spike was short, let’s say for 5 minutes reached 90%. Each bar represents a query that runs for 1 hour.
So, in this case, you will see, one bar taking 5/60 * 90% ~ 7.5%

QPI will show you most significant queries in the observed interval, and time grain is one hour.  If query spiked and its significance is not big enough to get into TOP 20, it won’t be shown.

You can change the granularity of monitoring chart (DB Blade, Monitoring Chart, "Edit chart") to be 1 minutes and check if the spike was short.

Hope it helps,
Andrejs


(sequel CHAR)
CONCAT(@sequel)

@variables are totally independent of non-@ variables.

Two fixes needed:

SET @SQL = CONCAT(@sequel); --> SET @SQL = sequel;
(sequel CHAR) --> (sequel VARCHAR(999))


How to handle lock timeout error in MySQL?

This is not a MySQL configuration problem, it is an application problem.

You apparently have one connection hanging onto something for more than 50 seconds (the default for lock_wait_timeout).  I consider even 5 seconds to be much too long for an interactive system to hang onto something.

Find the other piece of code and rethink its logic.


Executing delayed inserts with MySQL event scheduler

I think you need to start over in the processing of your data.  I don't think TRIGGERs are an efficient way to achieve the goals.

Here's a thought:

Put information to be INSERTED/DELETED into a couple of tables.  Periodically, batch process the entire tables to do the calculations and do the inserts and deletes.  Batch processing is a lot faster than one-row-at-a-time.

Furthermore, I would set up pairs of such tables.  Various clients would be inserting into one set, while another job is doing the processing described above.  When finished, the processed tables would be cleared, then swapped with the tables being inserted into.

This discusses the details for doing the "inserts", together with associated extra work ('normalization', in the blog).  Deletes would work similarly.


MongoDB moveChunk (chunk migration) chooses secondary with larger ping time for writeconcern majority

Actually mongodb don't "choose" neither of those secondaries. Write concern majority is fulfilled when first secondary has replicated that change successfully and reported that information back to the primary. In this situation, somehow your "local" secondary was lagging with opLog replication, reason for that may or may not be found if you analyze that nodes mongod.log file.


How to print the information of all tables using Oracle?

If you're a student and can't install SQL Developer locally, maybe you could use SQuirreL SQL, a more lightweight (but still excellent) JDBC database client. I've not used the graphical stuff which I believe they have, but you can just simply navigate the tree like structures through the tables and easily get any info you may want, table structure, row count and data.


Would it be possible to mount additional drives on the target SQL server, restore the data files to the new drives and then simply change the location of the data files in the DB once the restore is complete?

Yes, if the two databases on the target server have the same file layout.  A simpler and safer procedure is to restore the database with a different name, and then rename the databases to swap.
If you can avoid detaching a databse, setting it offline or shutting down SQL Server you have extra protection against losing data.


Postgres 9.1 statistics in pg_stat_database

It seems that pg_stat_database is thoroughly described in the manual.

As to the increasing xact_commit. You might be have AUTOCOMMIT=on. PostgreSQL will always run all your queries in a transaction. In case AUTOCOMMIT=on, COMMIT will issued after each statement executed.

In your case you have 2 statements:

previous SELECT * FROM pg_stat_database;
your test statement, like SELECT 1

You might try the following scenario:

open 2 sessions, S1 and S2;
S1: SET autocommit TO off;
S1: SELECT * FROM pg_stat_database;
S1: SELECT * FROM pg_stat_database;
S2: SELECT 1;
S1: SELECT * FROM pg_stat_database;
S2: SET autocommit TO off;
S2: SELECT 1;
S1: SELECT * FROM pg_stat_database;

You can continue playing around yourself.


Adding a surrogate id will not help in this case.  You already have a good PRIMARY KEY(name), correct?

The 4 columns look good for the purposes mentioned.  You probably need a composite INDEX(group_id, name) to facilitate finding all the related queries.

What do you do about nicknames that apply to two standard names?

What do you do about someone with a non-"standard" name as their 'official' name?


What is a suitable,stable, high performing database for Large Financial Time Series Data?

In my opinion you must be, or you need, a DBA.
Not a single rdbms can solve problems with big data out of the box.

In your case partitions can be useful for example. So data compression, etc...

I can just suggest 3 alternatives:

SQL Server Enterprise Edition
Oracle Enterprise Edition
Postgresql

And the skills to manage data.


what could be the fastest way to delete all data from a partition of a partitioned table?

SQL Server 2014 unfortunately doesn't support TRUNCATE on a partition. Either drop and recreate it or switch it out.

See longer discussion here.

SQL Server 2016 does support truncating partitions. If you're on that version, that's definitely your fastest option.


Can we reuse a .dmp file(expdp) to perform import using data pump utility

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


How to enforce uniqueness of compound primary key made up of two foreign keys to the same table?

In light of your comment,

  For my use case, user_one_id represents the initiator of the friendship, and user_two_id represents the recipient of the friendship. So I can't just use the lowest value as user_one_id.

Well, you can still do it. Your use case just excludes a row-constraint to ensure that. What you want is to use a table-constraint, something like this.

CREATE TABLE friendship (
  user_one_id int NOT NULL,
  user_two_id int NOT NULL,
  CHECK (user_one_id != user_two_id ),
  PRIMARY KEY (user_one_id, user_two_id)
);
-- you can do least first if you want. doesn't matter.
CREATE UNIQUE INDEX ON friendship (
  greatest(user_one_id, user_two_id),
  least(user_one_id, user_two_id)
);

We have a lot going on here.. We make sure.

Both are NOT NULL
Both are not equal to each other
Both are UNIQUE (user_one_id, user_two_id)

That leaves one remaining problem of commutative uniqueness we solve that with a custom unique table-constraint implemented with an index.

Proof in the pudding

INSERT INTO friendship VALUES ( 1,2 );
INSERT 0 1
test=# INSERT INTO friendship VALUES ( 2,1 );
ERROR:  duplicate key value violates unique constraint friendship_greatest_least_idx"
DETAIL:  Key ((GREATEST(user_one_id, user_two_id)), (LEAST(user_one_id, user_two_id)))=(2, 1) already exists.

As an important friendly note your names are all kinds of silly. The relationship is fine. In production, please give them better names..

friendship
----------
request_initiator
request_target
other_fields


Security and Performance implications of "View Server State"

There are no significant performance issues that I can think of from granting this permission. From a security perspective, you run the risk of letting a user see what you most details about your weak spots, so for example, a malicious user could view your most common wait stats are, which could help them target a DoS attack against your server.

Is this possible? Definitely.
Is this likely? I'm compelled to say No, but remember that it is estimated that 90 percent of attacks against companies are from internal attackers.


pgAgent Job's "last result" is continuously "running"

This was a bug in pgagent, it has been fixed in http://git.postgresql.org/gitweb/?p=pgagent.git;a=commitdiff;h=f9bf1ccb27ebcfce00e7a6d467bc0e1b5ee9555e.


Execute ALTER SYSTEM command in oracle scheduler

Create procedure to run your SQL statements then call that procedure in job_action.

SQL> create or replace procedure my_proc
as
begin
execute immediate 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=DEFAULT_MAINTENANCE_PLAN';
end;
/

Procedure created.

SQL> EXEC dbms_scheduler.drop_job('job1');

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name          =>  'job1',
   job_type          =>  'PLSQL_BLOCK',
   job_action        =>  'begin my_proc(); end;',
   start_date        =>  SYSDATE,
   repeat_interval   =>  'FREQ = DAILY; INTERVAL = 1');
END;
/

PL/SQL procedure successfully completed.

SQL> EXEC dbms_scheduler.run_job('job1');

PL/SQL procedure successfully completed.
SQL> select log_date, status from dba_scheduler_job_run_details where job_name='JOB1';

LOG_DATE                                STATUS
-------------------------------------   --------------
10-MAY-17 06.36.43.906003 AM -04:00     SUCCEEDED

Starting from Oracle 12c, we have SQL_SCRIPT as job_type which specifies that the job is a SQL*Plus script.

DBMS_SCHEDULER: Oracle 12c

Moreover, if you just want to switch resource allocation plan, as Balaz Papp mentioned in the comment, you could use Scheduler Windows.

  You create windows to automatically start jobs or to change resource allocation among jobs during various time periods of the day

Oracle Scheduler Concepts: Window


ORA-01031: insufficient privileges connecting to standby

According to the output of the command ps -ef|grep pmon which has shows that the instance name of the standby database is teststan.

[oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon

But in the description of listener on standby database, SID_NAME is defined as 'test'.

The Oracle tries to authenticate sys user using password file(orapwtest) which has never existed.
So set the SID_NAME parameter on listener.ora file on standby site to 'teststan'.


Autovacuum is not running even after configuring it on

Did you contact AWS support? The version 9.6.8 aurora-postgresql has a metadata corruption bug that could lead to this symptom that you have described. You probably need to upgrade.

I have recently encountered this problem twice in production, and AWS support was helpful in each event.


SQL Server Timeout: Gathering server side information

Perhaps (at least some of) these "random timeouts" might be network related?

You should definitely consider performance monitor.  You can view statistics in real time, collect statistics to generate a baseline, and configure triggers to send alerts.

PS:
Here's a good link to get you started:

Free Microsoft Tools to Help Setup and Maintain PerfMon for SQL Server


Using IF ... PREPARE ... EXECUTE ... DEALLOC to create a temporary block?

NOT EXISTS is more readable and does not need to go through to get a full count.
BEGIN
    IF (NOT EXISTS(SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'foo'
              AND COLUMN_NAME = 'bar' ))
    THEN
        ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;
    END IF;
END $$


database setup for posts table with multiple types

You may want to consider using a single JSON field on your posts table to store all the miscellaneous details in a JSON object. You can have as many as you want, including arrays and nested objects.

At this time MySQL is not great at indexing and accessing/manipulating JSON data, but it does provide a few functions like JSON_OBJECT, JSON_ARRAY, JSON_SET, JSON_INSERT, JSON_REMOVE, JSON_REPLACE, JSON_MERGE, and JSON_EXTRACT, and surely future version will have even greater support, such as indexing.

MariaDB has also Dynamic Colums and also good (possibly better) support for JSON data than MySQL.


Pentaho Data Integration - Kettle- Update Identity Column in Microsoft SQL Server

SQL Server does not allow updates the IDENTITY column value regardless of application technology.

One way to perform such a migration is to switch data into a table with identical schema (except for the IDENTITY property), perform the update, and then SWITCH back into the main table.

CREATE TABLE dbo.TableWithIdentity(
      IdentityColumn int IDENTITY NOT NULL
        CONSTRAINT PK_TableWithIdentity PRIMARY KEY
    , OtherData varchar(20) NULL
    );
CREATE INDEX idx_TableWithIdentity_OtherData
    ON dbo.TableWithIdentity(OtherData);
INSERT INTO dbo.TableWithIdentity VALUES
      ('Sample data one')
    , ('Sample data two')
    , ('Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    --create staging table with same schema, indexes, and constraints but without IDENTITY
    CREATE TABLE dbo.TableWithoutIdentity(
          IdentityColumn int NOT NULL
            CONSTRAINT PK_TableWithoutIdentity PRIMARY KEY
        , OtherData varchar(20) NULL
        );
    CREATE INDEX idx_TableWithoutIdentity_OtherData
        ON dbo.TableWithoutIdentity(OtherData);

    --SWITCH data into staging table
    ALTER TABLE dbo.TableWithIdentity
        SWITCH TO dbo.TableWithoutIdentity;

    --example update to change identity value
    UPDATE TableWithoutIdentity
    SET IdentityColumn = IdentityColumn + 100;

    --SWITCH data back into main table
    ALTER TABLE dbo.TableWithoutIdentity
        SWITCH TO dbo.TableWithIdentity;

    DBCC CHECKIDENT(N'dbo.TableWithIdentity');

    DROP TABLE dbo.TableWithoutIdentity;

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO


How to define custom STATIC Global Variables/Constants in MySQL

Since MySQL 5.5 it's not possible to set a global user-defined variable.

A work-around might be to create a stored procedure that would return what you need.

DROP PROCEDURE IF EXISTS HOUSE_SMALL_TYPE;
DELIMITER //
CREATE PROCEDURE HOUSE_SMALL_TYPE ()
BEGIN
SELECT 0;
END//
DELIMITER ;

and then call it.

CALL HOUSE_SMALL_TYPE();
The DROP statement is required in order to be able to modify it.

Credit to shock_one on SO for the above answer

I was also thinking another option may be setting your constant values in a seperate table that all of your stored procedures could access.


MySQL slave not able to execute long running update query

You need to stop replication, make the Slave have the same specs as the Master, then start replication.

Make sure the Slave has no incoming connections. Otherwise, that will make the SQL thread on the Slave compete with incoming connections that are running SELECT queries against the same table you are running DELETE.

If you cannot reroute the incoming connections, you will have to rerun the DELETE in chunks (perhaps 5000 rows at a time) on the Slave locally.

As a last resort, rebuild the Slave (after scaling up the Slave's hardware and configs).


Get actual Job Name from Program Name column of the results while running sp_whoisactive or sp_who2

You can do this easily with sp_WhoIsActive.
EXEC dbo.sp_WhoIsActive
    @get_additional_info = 1,
    @get_task_info = 2;

If you click on the additional_info column, you'll see this:

You don't explicitly need the @get_task_info parameter to retrieve the extra information, it's just a very useful general combination.


making a TSQL statement that has variables to be dynamic

Your dates must be strings too, but in a format that is unambiguous (e.g. date format 112):

DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE), 112) --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE), 112) --The 12th month from the current month

You will need to convert your GETDATE() as well, where you have it in the string. Watch out for quotation marks, and casting back to DATE as well. Here's a proposed solution:

DECLARE @query AS VARCHAR(MAX);
DECLARE @schemaName AS VARCHAR(5) = 'wl03'; -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 11, 0) AS DATE), 112); --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 12, 0) AS DATE), 112); --The 12th month from the current month
DROP TABLE IF EXISTS #12thMonthExpectedActivity;

-- Add rows for existing month into a temporary table
SET @query
    = 'SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('''
      + CONVERT(CHAR(8), GETDATE(), 112)
      + ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName
      + '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')';

PRINT @query;

Read more about CAST and CONVERT with Books Online.


How can I get a list of tables contained in a database diagram?

Although this question is a year old, if anyone stumble over this, I've posted a solution to make that possible here:

http://www.codeproject.com/Messages/4914888/Search-through-the-binary-for-all-contained-texts.aspx

The trick is to extract all ASCII characters in the diagram binary, you'll see that all table names are in there as simple text. That seems to be the reason why a diagram is also destroyed if you rename a table with a script - because it doesn't use a table ID to identify the table but the table name. If it's no longer there, the diagram is not usable anymore.

With this method you can identify which diagrams contains a specific table.


When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql would reveal that on Master1.

You would be better off doing the cutover of the IP as follows:

On Master1

FLUSH HOSTS;
service mysql stop

Change the IP in the App
On all web servers, service httpd restart

In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.

For example:

Master1 is 10.1.2.30
Master2 is 10.1.2.40
Use 10.1.2.70 as the DBVIP

Here is what you can setup

On Master1, run ip addr add 10.1.2.70/24 dev eth1
Use 10.1.2.70 in your app

Then, when it is time to cutover, do the following

On Master1

FLUSH HOSTS;
service mysql stop
ip addr del 10.1.2.70/24 dev eth1

On Master2

ip addr add 10.1.2.70/24 dev eth1
SHOW SLAVE STATUS\G and make sure all final SQL statements from Master1 executed

On all web servers, service httpd restart

That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.


How come an INNER JOIN returns zero rows when I use coalesce?

The column named importe is numeric but the JOIN conditions are not being fulfilled so it returns a blank value instead of a NULL mark.
How can I set this value to 0 instead of a blank value, I tried with COALESCE but it's not working.

If the join conditions are not being fulfilled you have 0-rows. An INNER JOIN on false returns 0-rows: not one row with a null value, not one row with a zero.
SELECT x,y
FROM ( VALUES (1) ) AS t(x)
JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y
---+---
(0 rows)

Returns nothing. What you want is an outer join.
SELECT x,y
FROM ( VALUES (1) ) AS t(x)
LEFT OUTER JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y
---+---
 1 |
(1 row)

Now that you have a row, you can use COALESCE
SELECT x, coalesce(y,0) AS y
FROM ( VALUES (1) ) AS t(x)
LEFT OUTER JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y
---+---
 1 | 0
(1 row)


Finding rows with same id but different date

Use an AGGREGATE function like MIN or MAX.

If you only want one record per ID, you have to choose which date you want.
The first (MIN) or the latest (MAX).

https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-2017

^^Above information is not what was requested.

Edited per clarification in comments:  Just use the HAVING to identify which ID values have more than one date, then apply that to a new SELECT.

SELECT id, Date
FROM tbl
WHERE id IN
    (
        select id
        from tbl
        group by id
        having count(distinct date)>1
    )


How to resolve "logical replication target relation schema.table is missing some replicated columns" error in postgresql logical replication?

Simply add the column again at the subscribed database and wait until replication has caught up before dropping the column again.


How to determine if the error was a deadlock?

I guess the ex.getErroCode() has returned ORA-00060: deadlock detected while waiting for resource which is deadlock error. Add ORA in front of the returned error code then search online or use Oracle supplied tool oerr as demonstrated below to determine the error details.

  An error code. This is an integer value identifying the error that caused the SQLException instance to be thrown. Its value and meaning are implementation-specific and might be the actual error code returned by the underlying data source. Retrieve the error by calling the method SQLException.getErrorCode.More...

  [oracle@orcl trace]$ oerr ora 60

  00060, 00000, "deadlock detected while waiting for resource"

  Cause:  Transactions deadlocked one another while waiting for resources.

  Action: Look at the trace file to see the transactions and resources
           involved. Retry if necessary.


In the past I built a "fuzzy-search" in a .Net CLR function. This function gets called the same way a user-defined function gets called.

For example,

select id, name
from customers
where dbo.CompareStrings("newCustomerName", customers.name) > .8

would only return customers with a name that was 80% similar to the input name.

The % match is based on the number of changes needed to convert one value to another, not the number of characters that are different. We use it to compare addresses, and found this was more effective because of the numerous street abbreviations that are used.

Here's the code I used to compare strings. I did this so long ago that I can't remember how to deploy it, although a quick search will show you many articles on how to create SQL CLR functions

' Checks two strings against each other and returns a decimal between 0 (doesn't match at all) and 1 (100% match)
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function CompareStrings(ByVal input1 As SqlChars, ByVal input2 As SqlChars) _
As <SqlFacet(Precision:=10, Scale:=4)> SqlDecimal

    If IsNothing(input1) And IsNothing(input2) Then
        Return New SqlDecimal(1.0)
    ElseIf IsNothing(input1) Or IsNothing(input2) Then
        Return New SqlDecimal(0.0)
    End If

    Dim s1 As String = New String(input1.Value)
    Dim s2 As String = New String(input2.Value)

    If s1.Length = 0 Or s2.Length = 0 Then
        Return New SqlDecimal(1.0)
    Else
        Dim re As New Regex("[^A-Za-z0-9 ]", RegexOptions.IgnorePatternWhitespace Xor RegexOptions.Singleline)
        s1 = re.Replace(s1, "( )\1*", "$1")
        s2 = re.Replace(s2, "( )\1*", "$1")
        s1 = UCase(re.Replace(s1, ""))
        s2 = UCase(re.Replace(s2.ToString, ""))

        Dim dif As Integer = GetStringSimilarity(s1, s2)
        Dim max As Integer = s1.Length
        If s2.Length > max Then max = s2.Length

        Return New SqlDecimal(1.0 - (dif / max))
    End If
End Function

' Compares two strings using the relationship in patterns of letters
Private Shared Function GetStringSimilarity(ByVal s1 As String, ByVal s2 As String) As Integer
    Dim n As Integer = s1.Length
    Dim m As Integer = s2.Length
    Dim distance(n + 1, m + 1) As Integer

    Dim cost As Integer = 0
    If n = 0 Then Return m
    If m = 0 Then Return n

    For i As Integer = 0 To n
        distance(i, 0) = i
    Next
    For j As Integer = 0 To m
        distance(0, j) = j
    Next

    For i As Integer = 1 To n
        For j As Integer = 1 To m
            If Mid(s2, j, 1) = Mid(s1, i, 1) Then cost = 0 Else cost = 1
            distance(i, j) = Min3(distance(i - 1, j) + 1, distance(i, j - 1) + 1, distance(i - 1, j - 1) + cost)
        Next
    Next
    Return distance(n, m)
End Function

' Returns the min of 3 values
Private Shared Function Min3(ByVal x As Integer, ByVal y As Integer, ByVal z As Integer) As Integer
    Dim min As Integer = x
    If y < min Then min = y
    If z < min Then min = z
    Return min
End Function


What you see typically happens when the non-privileged user doesn't have schema level privileges.  In this case, they cannot list the objects inside the given schema - with the command you tried.  See the below example:

-- as a superuser, for example
CREATE DATABASE test;
\c test
CREATE TABLE public.blah (id integer);
REVOKE ALL ON SCHEMA public FROM PUBLIC;

CREATE USER u WITH PASSWORD 'u'; -- don't do this in production

-- connect as u
\c test u
\dt
No relations found.

-- but:
\dt public.*
       List of relations
 Schema │ Name │ Type  │ Owner
────────┼──────┼───────┼────────
 public │ blah │ table │ dezso

See what the documentation says:

  USAGE

  For schemas, allows access to objects contained in the specified
  schema (assuming that the objects' own privilege requirements are also
  met). Essentially this allows the grantee to "look up" objects within
  the schema. Without this permission, it is still possible to see the
  object names, e.g. by querying the system tables. [...]

\dt does the latter, namely queries pg_class and pg_namespace to find matching objects.  Apparently, when the schema name is not specified, the system wants to hide objects from view.


Dataguard parameter changed after DB bounce

The initialization parameters are not supposed to change while bouncing the database except in one case, that is if the modification was only for currently running instance(SCOPE=MEMORY).

The following are the steps to modify initialization parameters using Enterprise Manager.

  To modify initialization parameters for the currently running instance, and also record the modifications in the server parameter file that will persist when the database is restarted, complete the following steps:
  On the Current subpage, in the Value column, enter new values for the initialization parameters.
  Select Apply changes in current running instance(s) mode to SPFile.
  (Optional) In the Comments column, enter text explaining the reasons for the changes.
  Click Apply.
  A confirmation message appears.

Viewing and Modifying Initialization Parameters


Install last CU for SP2 without installing SP1 in sql server

You can just install SP2 and then CU10. No need to install CU for SP1.

You can have a list of all SP + CU here

More relevant info :

If you have a large estate that needs to be patched, you can use dbatools - Update-DbaInstance

Also, dbatools has a really nice - build reference page  that will tell you what the latest SP/CU is and End of support dates as well.


This is an interesting read for How MySQL uses memory
We have,
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size, query_cache_size
and
Per Thread Buffers: read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, thread_stack, max_allowed_packet, net_buffer_length
To calculate present status you can download procedure here and just run it from MySQL.
I guess we can formulate number of Threads (nT) = ( R - GB  ) / PTB

Where,
R - Ram
GB - Global Buffers
PTB - Per Thread Buffers


How could we start Oracle database in Oracle Exadata which's celldisk has proactive failure?

Exadata uses NORMAL or HIGH redundancy diskgroups. It is possible to use EXTERNAL redundancy diskgroups as well, but that is not supported.

Even if you drop a griddisk, and one of the ASM failgroups becomes incomplete, you can still mount the ASM diskgroup and use the mirror side(s). But this will not happen automatically, because the usual MOUNT command throws an error in such a case, and srvctl will not start the database because of that.

You should be able to mount the diskgroup manually with the FORCE option. Log in to the ASM instance as sysasm:

sqlplus / as sysasm

And mount the diskgroup (on all nodes):

alter diskgroup data_dm01 mount force;

Once the diskgroup is mounted, you should be able to start the database.

For the faulty physicaldisk, you should open an SR with Oracle (given that you have valid support contract) for a replacement drive (this usually automatically happens with ASR (Auto Service Request)). The last time I did that, the replacement drive was available in 1-2 days, and their engineer delivered and replaced it in the customers data center.


Surprisingly, that's not gibberish.

That indeed appears at the top of binlogs whenever you do mysqlbinlog to a binary log generated using MySQL 5.1 and MySQL 5.5. You will not see that gibberish in binary logs for MySQL 5.0 and back.

This is why the start point for replication from an empty binary log is

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

This is good to remember if you do MySQL Replication where the Master if MySQL 5.1 and the slave is MySQL 5.0. This could present a really big headache.

Replication from Master using 5.0 and Slave using 5.1 works fine, not the other way around.(According to MySQL Documentation, it is generally not supported for 3 reasons: 1) Binary Log Format, 2) Row-based Replication, 3) SQL Incompatibility).

Anyway, do a mysqlbinlog on the offending binary log on the master. If the resulting dump produces gibberish in the middle of the dump (which I have seen a couple of times in my DBA career) you may have to skip to position 98 (MySQL 5.0) or 106 (MySQL 5.1) or 107 (MySQL 5.5) of the master's next binary log and start replicating from there (SOB :( you may need to use MAATKIT tools mk-table-checksum and mk-table-sync to reload master changes not on the slave [if you want to be a hero]; even worse, mysqldump the master and reload the slave and start replication totally over [if you don't want to be a hero])

If the mysqlbinlog of the master is completely readable after the top gibberish you saw, it is possible the master's binary log is fine but the relay log on the slave is corrupt (due to transmission/CRC errors). If that's the case, just reload the relay logs by issuing the CHANGE MASTER TO command as follows:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='< master-host ip or DNS >',
MASTER_PORT=3306,
MASTER_USER='< usernmae >',
MASTER_PASSWORD='< password >',
MASTER_LOG_FILE='< MMMM >',
MASTER_LOG_POS=< PPPP >;
START SLAVE;

Where

MMMM is the last file used from the Master that was last processed on the Slave
PPPP is the last position used from the Master that was last processed on the Slave

You can get MMMM and PPPP by doing SHOW SLAVE STATUS\G and using

Relay_Master_Log_File for MMMM
Exec_Master_Log_Pos for PPPP

Try it out and let me know !!!

BTW running CHANGE MASTER TO command erases the slave's current relay logs and starts fresh.


How can I find port running Oracle Enterprise Manager

The port configuration is documented in the following file:

$ORACLE_HOME/install/portlist.ini

If Enterprise Manager has been installed then setting off the following command will show the status of the Enterprise Manager:

./emctl status dbconsole

If you are receiving the following message:

./emctl:  not found.

...then you might not have Enterprise Management Console installed.

The Management Console is not required to have a fully functional Oracle RDBMS instance up and running.


Really big LOG backups on a DB with Image field

If you have a high volume of changes (inserts, updates or deletes) to those image BLOBs, then you would be best served to NOT store them within the database in the long run.  Assuming your not having performance issues, which some features like FileStreaming and FileTables can help with, your problem is going to be the "velocity" of change to those BLOBs of data, which will manifest a single byte change within the BLOB into a full recreation of the entire BLOB in the transaction.

One possible "work-around" if you MUST keep them in the database AND you can tolerate some data lose of the binaries is instead store them in a separate database on the same instance.  Set that database's recovery model to SIMPLE, and just do some extra differential or multi-day full backups.  Then to query the data, utilize cross-database joins and/or views that do the same.  Not an ideal situation, but at least a way to minimize data lose if your recovery needs can vary by individual data item (as opposed to the entire database).


How do I normalize a database with lots of many-to-many relationships?

It is very clear but you should probably first read some "introduction to relational databases" before you proceed, because from the way you are asking I feel it would be beneficial for you to get some basics first. No offence, it may save you lot of time later.

To give you few fast rules of a thumb

You never ever connect more then two tables with one link. Try to connect tables step by step. Always take a pair of them and try to join them. Do not try to solve the whole thing at once.
you only need the link table if there is so called many to many relationships, i.e. if a client can have many programs and at same time one program can have many clients. It seems to me that it might not be needed in your case.
In all other cases you just place "anothertableID" to one of the tables. In your example it would be good to start with putting ProgramID to ReportTable, ClientTable, PartnerTable. Maybe it is what you need.

To give an more elaborated answer you should give more information about structure of your problem.


Why would a file generated by mysqldump be substantially larger than the data directory

this is normal.

with a version 5.5.60 default settings for innodb innodb_file_per_table = 0
it means - data stored not in files inside the database folder, but in global innodb file.

You can check it in /var/lib/mysql
file - ibdata1

after version 5.6 default value for innodb_file_per_table settings is 1

ibdata1 is still extremely important, but data stored in separate files similar for myisam.


How to fix all duplicate key value violates unique constraint

The code you display is not related to "reindexing". (Compare: REINDEX). It gets a sequence behind a serial column (or IDENTITY column in Postgres 10 or later) back in sync - by setting it to the maximum existing ID. Consider this query instead:

SELECT setval('tablename_id_seq', max(id)) FROM tablename;

No subselect needed, no off-by-one error to fix (the next value returned from the sequence is incremented by default).
Or, better yet, you don't have to provide the name of the sequence:

SELECT setval(pg_get_serial_sequence('tablename', 'id'), max(id)) FROM tablename;

Details:

How to reset postgres' primary key sequence when it falls out of sync?


Query "last" records in a table with non-sequential key fields

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data transferred between databases.

I would suggest a following procedure:

Find the current Oracle SCN (System Change Number)

select dbms_flashback.get_system_change_number from dual;
Get rows that have been changed since last update

select * from [source_table] where ora_rowscn > [last scn] and ora_rowscn <= [current scn]
Resolve possible duplicates in your target database
Store the [current scn] to target database so it can be used as [last scn] during the next iteration


Why the MySQL query for where clause is very slow?

You will need to define an index on the voteup_count column for the query to be performant. Without this index, this query will be scanning all the rows until it reaches the first value of the Range condition.

Assuming that the data tree is in such a way that rows with low voteup_count values come first (basically, in ascending order). In that scenario, MySQL does not need to scan a lot of rows, and stops as soon as the LIMIT is achieved.

To make the query fast in all scenarios, define the following index:

ALTER TABLE zhihu.zhihu_answer_meta ADD INDEX voteup_count_idx (voteup_count) ;

Also, note that LIMIT wihout ORDER BY is non-deterministic, as data is basically stored as an unordered set, so you can get random set of rows.

Also, another optimization tip is to avoid using SELECT *, and instead specify the column name(s) explicitly. Do read: Why is SELECT * considered harmful?


Inclusion of ORDER BY on query that returns no rows drastically affects performance

I agree with Martin Smith's answer, but the problem is not simply one of statistics, exactly.  The statistics for the foreignId column (assuming automatic statistics are enabled) accurately show that no rows exist for a value of 3 (there's just one, with a value of 7):

DBCC SHOW_STATISTICS (tinytable, foreignId) WITH HISTOGRAM

SQL Server knows that things might have changed since the statistics were captured, so there might be a row for value 3 when the plan is executed.  In addition, any amount of time might elapse between plan compilation and execution (plans are cached for reuse, after all).  As Martin says, SQL Server contains logic to detect when sufficient modifications have been made to justify recompiling any cached plan for optimality reasons.

None of this ultimately matters, however.  With one edge-case exception, the optimizer will never estimate the number of rows produced by a table operation to be zero.  If it can statically determine that the output must always be zero rows, the operation is redundant and will be removed completely.

The optimizer's model instead estimates a minimum of one row.  Employing this heuristic tends to produce better plans on average than would be the case if a lower estimate was possible.  A plan that produces a zero-row estimate at some stage would be useless from that point on in the processing stream, since there would be no basis to make cost-based decisions (zero rows is zero rows no matter what).  If the estimate turns out to be wrong, the plan shape above the zero row estimate stands almost no chance of being reasonable.

The second factor is another modelling assumption called the Containment Assumption.  This essentially says that if a query joins a range of values with another range of values, it is because the ranges overlap.  Another way to put this is to say that the join is being specified because rows are expected to be returned.  Without this reasoning, costs would be generally underestimated, resulting in poor plans for a broad range of common queries.

Essentially, what you have here is a query that doesn't fit the optimizer's model.  There's nothing we can do to 'improve' estimates with multi-column or filtered indexes; there's no way to get an estimate lower than 1 row here.  A real database might have foreign keys to ensure that this situation could not arise, but assuming that is not applicable here, we are left with using hints to correct the out-of-model condition.  Any number of different hint approaches will work with this query.  OPTION (FORCE ORDER) is one that happens to work well with the query as written.


You don't want #>>, but instead #>, from the docs

  #>   Get JSON object at specified path
  #>>  Get JSON object at specified path as text

Also, you should consider normalizing that. Storing json-arrays, of json-objects and expecting a query on those objects to be fast is insane.


Upload multiple csv file to multiple tables sqlServer

I you want to use powershell to upload these CSV files, without having to code/script, take a look dbatools.io. Look for the cmdlet Import-DbaCsv

All the documentation is available on the site. Just download the tools from github or the powershell gallery.

You can use Get-ChildItem cmdlet to get all the CSV files in a folder and use the pipeline to send the files to Import-DbaCsv (optionally specifying the -AutoCreateTable flag). For example:

Get-ChildItem -Path "C:\Path\To\your\CSVs" | ForEach-Object {
    Import-DbaCsv -Csv $_.FullName -SQLInstance "localhost" -Database "yourdb" -AutoCreateTable
}


Is my subquery inneficient. It is extremely slow

Your query is slow because of the sub-select.  It is what is known as a correlated sub query.  It will be executed once per row in the outer query.

This question answers a similar problem.  It's looking for the minimum, you for the maximum; it's using one table, you're using one.  The translation shouldn't be too hard, though.


You can do swapsies by specifying both tables in your RENAME statement.

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

MySQL documentation on RENAME TABLE says:

  The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one...


What is the best design for entities in order to represent dynamic organizations?

There are two different approaches to achieve this.

Solution 1: The hierarchy is in the employees data

Add a reportsto field to your employees table

Each employee has an ID. This can be a unique identifier or an employee number that is given to each employee when she/he starts at the company. Each employee will have to report to another person (e.g. manager, team manager, ...) Here is some example data for such an EMPLOYEE table with a REPORTSTO column

Example EMPLOYEE table:

 UID   | REPORTSTO | NAME      | DEPT
-------+-----------+-----------+---------------
 UID1  |           | Smith     | CEO
 UID2  | UID1      | Vernon    | CFO
 UID10 | UID2      | White     | CFO1
 UID23 | UID2      | Rutzky    | IT1
 UID22 | UID2      | V.        | MN1
 UID6  | UID23     | M.        | IT12
 UID9  | UID23     | Nets      | IT22

Pros and Cons

The benefit of implementing this solution is that the departments and sub-departments are irrelevant. Everybody has to report to somebody. You can now query the hierarchy with a script to return a lovely tree view. The disadvantage is, that you have to change the reportsto each time somebody leaves the company.

Solution 2: The hierarchy is in a separate table.

Separate the hierarchy from the employees data

The employee has a link to a hierarchy/department table. The hierarchy is not stored together with the employee data, but in a separate table

Example of EMPLOYEE table:

 UID   | HIER_ID   | NAME      |
-------+-----------+-----------+---------------
 UID1  | 1         | Smith     |
 UID2  | 2         | Vernon    |
 UID10 | 7         | White     |
 UID23 | 14        | Rutzky    |
 UID22 | 17        | V.        |
 UID6  | 210       | M.        |
 UID9  | 213       | Nets      |

Example of HIERARCHY table:

 HIER_ID    | BELONGSTO    | DEPT_SHORT     | DEPT_DESC
------------+--------------+----------------+-------------------------
 1          |              | CEO            | Chief Executive Office
 2          | 1            | CFO            | Chief Financial Officer Office
 7          | 2            | CFO1           | Assistant CFO
 14         | 2            | IT1            | Head of IT
 17         | 2            | MN1            | Manufacturing Lead Manager
 210        | 14           | IT12           | Assistant Head of IT
 213        | 14           | IT22           | Database Administration

Pros and Cons

The benefit of implementing this solution is that the hierarchy is not linked to the employees, but rather the employee is linked to a hierarchy. Likewise you can have an infinite number of sub-departments / sub-structures and don't have to think about limitations. The disadvantage is if the structure changes inside the company (e.g. IT1 (14) doesn't belong to CFO (2) but reports directly to the CEO (1)), because it requires a separate user interface to modify the hierarchy.


How to fill GROUP_CONCAT() with 0's when JOIN data missing

Both date and stat may be absent. So you must generate not date array, but (date, stat) pairs array:

WITH RECURSIVE dates (date) AS
(
  SELECT '2019-01-24'
  UNION ALL
  SELECT  date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB('2019-01-30', INTERVAL 1 DAY)
),
stats (stat) AS (
  SELECT DISTINCT stat
  FROM daily_stats
  WHERE stat IN(132, 120, 111)
),
dates_stats (date, stat) AS
(
  SELECT date, stat
  FROM dates, stats
)
SELECT
    dates_stats.date AS label,
    GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(dates_stats.stat, 132, 120, 111)) AS value
FROM dates_stats
LEFT JOIN daily_stats ON daily_stats.stat IN(132, 120, 111)
                     AND daily_stats.date = dates_stats.date
                     AND daily_stats.stat = dates_stats.stat
GROUP BY label;

fiddle

Or maybe ever

stats (stat) AS ( SELECT 132 stat
                  UNION ALL
                  SELECT 120
                  UNION ALL
                  SELECT 111
),


How do you partition an events table by a time based off the datediff(Lag,time)?

You can look at the following:  (Notice I changed your sample date formats for this example)
This uses a CTE to get your dataset with the 'LAG'ged value for [Activity_Start_Time].  The LAG is performed as partitioned by User and ordered by the date

Table:

Create Table #events
(
SessionID Int,
[User] Int,
Video Int,
Activity_Start_Time SmallDateTime,
[Event] VarChar(20)
)
Insert Into #events Values
(10,1,2,'2019-02-12 12:05:05','play'),
(10,1,2,'2019-02-12 13:05:05','stop'),
(10,1,2,'2019-02-14 12:05:05','play'),
(10,1,2,'2019-02-14 13:05:05','Watching'),
(10,1,2,'2019-02-16 14:10:10','Stop')

Query:

With cte As
(
Select
       Activity_Start = Lag(Activity_Start_Time,1,0)
                        Over (Partition By [User] Order By Activity_Start_Time),
       Case When IsNull(Lag(Activity_Start_Time,1,0)
                        Over (Partition By [User] Order By Activity_Start_Time),0) = 0
            Then 0
            Else DateDiff(Hour,Lag(Activity_Start_Time,1,0)
                        Over (Partition By [User] Order By Activity_Start_Time),Activity_Start_Time)
       End As Diff_Hours,
       *
From
       #events
)
Select
      SessionID,
      [User],
      Video,
      Diff_Hours,
      Activity_Start,
      Activity_Start_Time As Activity_End
From cte
Where Diff_Hours > 2

Drop Table #events

Result:

SessionID   User    Video   Diff_Hours  Activity_Start      Activity_End
10          1       2       47          2019-02-12 13:05:00 2019-02-14 12:05:00
10          1       2       49          2019-02-14 13:05:00 2019-02-16 14:10:00


copy a sqlite3 table preserving custom _id

Just copy the ID values over:

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (_id, key_remote_jid)
    SELECT _id, key_remote_jid
    FROM android.messages;

Please read the SQLite Autoincrement documentation. New ID values will not conflict with the manually inserted values. (And it is likely that you do not need to specifiy the AUTOINCREMENT keyword.)


Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema?

No, members of the Sysadmin role or the database owner role bypass the permission checking algorithm. So no matter what permission are granted or denied , they are able to always access the objects.


Assuming those objects are stored procedures or views (and not tables), you'll need to:

script the objects in question
drop them from the database (or just remove the schema-binding)
change the database collation
recreate the objects by running the scripts you created in the first step.

As an aside, you didn't need to sp_msforeachtable "ALTER TABLE..."


Using queries instead of loops to modify database principals

You are going to have to use dynamic SQL.  Even if there were a way to access a system table that has all the information on on server, you would need dynamic SQL for the four-part naming on other servers.

By the way, you can resolve variables inside queries.  However, a variable does not work for a server, database, table, or column name.  In short, variables replace constants.

Here is a suggestion on how to do it in your case.


Are the ALL_OBJECTS and DBA_OBJECTS different when you have DBA Role?

First let's define ALL_OBJECTS and DBA_OBJECTS in oracle:

ALL_OBJECTS describes all objects accessible to the current user.
DBA_OBJECTS describes all objects in the database

As result if you select everything they are different because there are multiple levels of DBA privileges on Oracle. For example if you have SysDBA privileges then you have much more access than a normal DBA.


Index Maintenance with Ola starting from day x (7 or 9) till the end of month

I wouldn't recommend making changes directly to Ola's script for your situation.  Any customization you make to his scripts will be lost when you download a newer version sometime in the future.

Instead, create a SQL Server Agent job to run daily and put your conditional execution logic in the job step

if datepart(day,getdate()) > 7
    EXECUTE dbo.IndexOptimize


We cracked this a while back by replacing the RAID controller on the server. The disks and server configuration were fine but it appears that the RAID controller couldn't deal with the IO.

We are now in the good place of reads ~ 2ms and Writes at <= 5ms


Full backup is not showing up because it has already been restored and no new latest info is there in msdb to reflect the same.
Moreover, once msdb is updated with the backup info, it lets you avail with such info and in a manner of chain as followed in as LSN.

However, you can do this manually by running below script on a specific database to retrieve backup information for. The process will then find the latest Full backup and all transaction log backups that have occurred after this full backup.

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
DECLARE @max_family int

-- set database to be used
SET @databaseName = 'enterDatabaseNameHere'

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM  msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM  msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

select  @max_family=max(family_sequence_number)
FROM    msdb.dbo.backupset b,
    msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
AND     b.database_name = @databaseName
AND     b.backup_set_id = @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT  backup_set_id, family_sequence_number,
    case mf.family_sequence_number
        when 1 then 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
        else '                         , DISK = '''
    end
        + mf.physical_device_name +
    case mf.family_sequence_number
        when @max_family then ''' WITH NORECOVERY'
        else ''''
    end
FROM    msdb.dbo.backupset b,
    msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
AND     b.database_name = @databaseName
AND     b.backup_set_id = @backup_set_id_start
UNION
SELECT  backup_set_id, 1,
    'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
    msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
AND     b.database_name = @databaseName
AND     b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
AND     b.type = 'L'
UNION
SELECT  999999999 AS backup_set_id, 1,
    'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id, 2

Also, you can go through the link here for more option to get the same.


Article table has a lot of null. What is the best way to prevent null in MySQL

If you are short of storage and database is big enough you can reduce the size ot table but the price is significantly higher CPU and RAM consumption. Just split the table

PERSON
+----+------+------+-------------+
| ID | name | attr | description |
+----+------+------+-------------+

into the two tables:

PERSON
+----+------+------+
| ID | name | attr |
+----+------+------+

P_DESCR
+------+-------------+
| P_ID | description |
+------+-------------+

Each time you need the full info do a JOIN..ON

SELECT *
  FROM      person  AS w
  LEFT JOIN p_descr AS z ON z.p_id = w.id
 WHERE w.id = 1234
;

LEFT JOIN fill description column by NULL if no according row in the P_DESCR table for certain PERSON

Both approaches - with NULLs and JOINs are valid but you can choose what is more suitable for your needs.


I have a working theory (LaForge would say to Capt Picard)...
Since you are using MySQL 5.1, you have access to the table INFORMATION_SCHEMA.PROCESSLIST.
You also have access to the ID of the current process the trigger is running on. The function to get that process ID is CONNECTION_ID.
You could try to fish out the query like this:
SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();

Keep in mind that the info is LONGTEXT:
mysql> show create table information_schema.processlist\G
*************************** 1. row ***************************
       Table: PROCESSLIST
Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint(4) NOT NULL DEFAULT '0',
  `USER` varchar(16) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Within the trigger, you could use a local variable to hold the query
DECLARE original_query VARCHAR(1024);

and then capture the query
SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();

Give it a Try !!!
If it works, then
Make it so (Capt Picard would quip back to LaForge)


SQL Server 2019 System.Data.SqlClient namespace query

possible to call a T-SQL script rather than include it in the main body of the PowerShell script

Of course.  Just read the file contents with get-content and pass that to the function:
function SqlQuery($server, $database, $query)
{
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
 $result = $command.ExecuteReader()
 $table = new-object “System.Data.DataTable”
 $table.Load($result)
 $connection.Close()
 return $table
}

$server = "localhost"
$database = "tempdb"
$sql = get-content "c:\test\foo.sql"

SqlQuery $Server $Database $sql

If the sql file contains multiple batches, you'll need to break it up yourself, or use use the Invoke-Sqlcmd cmdlet from the SqlServer module, which you can install with
install-module SqlServer


Why does this scalar function not raise an error?

Figured it out myself. I did not fully understand how the strict keyword works but the docs state:

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.


As it sounds, you are in a split brain situation.  The original master (01) was never stopped from being master, and after the promotion of 02, it became just another master.

Fixing such issues pre-9.5 is not so easy (at that version pg_rewind became an element of the PostgreSQL ecosystem) - you will need some manual cleanup, most probably.  What is certain is if you got writes to 01 after promotion of 02, they will be lost (or the writes on 02, depending what you choose to do).

I'd take a logical dump from both 01 and 02 to start (to check if there is anything that has to be manually replayed from 01 to 02), stop 01 altogether, remove the older timelines WAL segments from the archive (well, you can move them somewhere else just in case) and then try to build a slave based on 02 again.

You can also use pg_xlogdump to see which relations (tables, indexes, etc.) got writes since the split brain started.  (Note that from version 10 the utility name is pg_waldump.)