Azure blob storage container vs VM managed disk for SQL on Azure VM database files

The biggest advantage of putting the database files on page blobs is for very large databases is you can use File-Snapshot Backups for Database Files in Azure.  But it's a more advanced and somewhat more tricky feature than using Azure Disks, which are very simple and user-friendly.

So the basic guidance is to use Azure Disks, unless you have a scenario where you really need to avoid creating a copy of the data for backups, or restoring as a size-of-data operation.  Note that for HA/DR using regular backup to URL or managed backup to Azure Blob storage is also much more useful than file snapshot backups, as you get Lifecycle Management, multi-region support, soft delete, and all the other features of blob storage of your backups.

And Azure Disks support things like Azure Backup and Azure Site Recovery, so they are compatible with enterprise-wide BC/DR solutions.  If you store your databases directly on blobs, or in Azure Premium Files the data is not automatically associated with your VM from a infrastructure, management, and monitoring POV.


Where can I find example databases for MySQL, SQL Server and Oracle?

Oracle provides several sample schema, but as with Rolando's answer, you would have to convert these for the other databases.

My answer in a nutshell is Don't use identical scripts.

The problem with using scripts that are as similar as possible for each database is that you will be using least common denominator functionality.  Using a database requires an investment in infrastructure, installation, support and development that will not reach it's full potential if the advantages of each platform are not utilized.

To truly do a comparison of these platforms, I suggest you determine what your requirements are and then implement those requirements in the best way possible for each database.  This will of course require significantly more knowledge of the platforms, but any other comparison would only be academic.

Here are two examples.

Requirement: Show all days in a given date range with sales data for the day regardless of whether any sales were made that day or not.
Solutions: In Oracle you might do a cross join with a hierarchical query on dual returning all the days in the range.  In SQL-Server you might use a CTE to generate the days.  In MySQL you might use something else entirely.1

Requirement: Be able to run a query at any time to retrieve the data for any table as it existed at any point in time within the last four hours.
Solution: In Oracle you would only need to ensure that the undo retention was guaranteed and set to at least four hours.  In other databases the implementation would be dramatically different.2


How can I get rows after a specific row, from a resultset of a specific sort order?

Plan A. You need the entire set of columns that you are ordering by.  Let's say you left off at a=4 AND b=11 AND c=2
If you are going ascending, then
WHERE (a, b, c) > (4, 11, 2)

For all DESC, change to <
Caveat:  "row constructors" like that were not optimized until 5.7.
For a mixture, please provide a 2-column example without UUIDs.  Provide CREATE TABLE and sample data.  Preferrably ready to run in a 'fiddle'.
Plan B  This is like the row_number(), which is not available before 8.0.
Create a table with a new AUTO_INCREMENT.  If all the work is in a single connection, use a TEMPORARY TABLE.  Then lean on that new id for "remembering where you left off".


Job for mysql.service failed because the control process exited with error code

I don't see anyone else answering this but I had the same issue and this could help someone.
The first error should be about network, binding by default an address such as 127.0.0.1 for localhost could trigger that and you should try to connect using the binding address a not 'localhost'.
Now, after entering safe mode I ran into a similar error! Even when the process of mysql is not "Active" there where still running processes after I stopped the safe-mode session and that was preventing the service to start. I was able to locate does processes using this command: sudo service mysql stop && pkill -f mysql receiving this alert:
pkill: killing pid 8912 failed: Operation not permitted
pkill: killing pid 8913 failed: Operation not permitted
pkill: killing pid 9273 failed: Operation not permitted

after that I manually kill the processes using:
sudo kill 8912
sudo kill 8913
sudo kill 9273

and ran again the command: sudo service mysql stop && pkill -f mysql
Finally I started the service again service mysql start and was running normally. I entered safe-mode to reset the password of the root user.


Why is there such a huge performance difference in these two join statements?

The OR operator in your first statement is making the output of both queries totally different.  If you put brackets around the two items that are separated by the OR statement you will see the results are the same.

SELECT ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id)
FROM category c
    INNER JOIN product p on (p.indID = c.id or p.catid = c.id)
        and p.deleted = 1
        and p.statusid = 1
GROUP BY c.name

OR is evaluated after AND...


SQLCMD: "Login failed for user" on SQL Azure, but it works with SSMS

The error was some encoding issue with Powershell. I suppose the password's special characters were not read correctly by Powershell.

In cmd.exe, it worked flawlessly.


Get top 10 records from two identical tables

Assuming you don't care which result you get in ties, you can use ORDER BY / LIMIT twice, once in each subquery and then a third time in the main query.

This will be more efficient than a normal UNION query from two tables and then LIMIt, as it can use indexes on (Date) on both tables:

    ( SELECT ID, UserID, Date
      FROM table1
      ORDER BY Date
      LIMIT 4
    )
UNION ALL
    ( SELECT ID, UserID, Date
      FROM table2
      ORDER BY Date
      LIMIT 4
    )
ORDER BY Date
LIMIT 4 ;


Difference between Clustered Columnstore Index Table in Azure SQL and Table in Azure Data Warehouse

(IMHO) Azure SQL Data Warehouse is a Massively Parallel Processing (MPP) engine with a shared nothing architecture designed to handle hundreds of terabytes of data.  It does this by dividing your data (either by a distribution key you specify or round robin algorithm) across 60 distributions split across n nodes, depending on the performance tier you are at.  It's really a big data platform and I wouldn't bother using if you have less than say 1-10TB, or your long-term data growth exceeds 4TB.  A recent update (known as Gen 2) adds new service tier levels, increased concurrency and improved caching amongst other things.  I believe they've just announced a cheaper tier at Ignite; details to follow.  SQL DW has a reduced T-SQL surface area and is kind of expensive to keep running 24x7 so you might make use of its pause feature.

Azure SQL DB certainly a powerful database with tier-based service levels but it's capped at 4TB making it a different proposition to SQL DW.  It's targeted at smaller OLTP or BI dbs or warehouses that won't exceed 4TB.  Microsoft have just announced Azure SQL DB Hyperscale in public preview allowing up to 100TB.

Other than the distribution element, I'm not aware of any columnstore-specific differences between the two products.  Bear in mind the massive compression you get with columnstore when estimating the size of your data.

HTH


Check status of SQL restore with NORECOVERY before RECOVERY

The easiest would be simply to capture and review the output of the restore scripts, that provide the full detail of each restore, including any that encountered problems. How exactly to capture this will depend on where you are performing the restore - you can send the output from a SQL agent job step to a text file, for example.

If you haven't done that, I believe the SQL errorlog will capture the status of each restore as they finish.


Should I keep pg_xlog on the same disk as data if using disk snapshotting?

Having the transaction log (pg_xlog) on separate storage can increase write performance on most RDBMS platforms. If yours is a (concurrent) write heavy application, you may see a performance benefit.

If the snapshots are not synchronous however, you could lose data from uncomitted transactions in the event of an unclean restart. I suppose in certain situations a pg_resetxlog could be required after a failure.

You could look into WAL archiving, or even techniques similar to ZFS snapshotting if EBS snapshots have the same effective purpose.


How to improve query count execution with mySql replicate?

Give this a try:

Add this composite index to user_activity:  INDEX(user_id, action).  Then

SELECT SUM(x.ct), u.user_type, x.action
    FROM (
        SELECT user_id, action, COUNT(*) AS ct
            FROM user_activity
            GROUP BY user_id, action
         ) x
    JOIN user u  ON x.user_id = u.id
    GROUP BY x.action, u.user_type;


mongodb v3.4 - wiredtiger - compact does not work

@mils, The compact administration commands Rewrites and defragments all data and indexes in a collection. On WiredTiger databases, this command will release unneeded disk space to the operating system.

compact has the following form:

{ compact: <collection name> }

In you case after the db.runCommand(command) , you have got the message such as

{
    "ok" : 1.0
}

it means your command has successfully executed.

On WiredTiger, compact will rewrite the collection and indexes to minimize disk space by releasing unused disk space to the system. This is useful if you have removed a large amount of data from the collection, and do not plan to replace it.

I am also attaching the db.runCommand(command) picture from mongo shell command,for proper format understanding of that command.

WARNING:-
Always have an up-to-date backup before performing server maintenance such as the compact operation.

How to check the storage engine from the mongo command shell

  db.serverStatus().storageEngine

Note : -
As per MongoDB BOL  the WiredTiger Storage Engine From the MongoDB version 3.2: The WiredTiger storage engine is the default storage engine starting in MongoDB 3.2. For existing deployments, if you do not specify the --storageEngine or the storage.engine setting, MongoDB 3.2 can automatically determine the storage engine used to create the data files in the --dbpath or storage.dbPath.

For further your ref compact


Bad luck. If you have a hosted database, and the monitoring view is broken, there's nothing you can do.

To speed up autovacuum as much as possible, increase maintenance_work_mem and lower autovacuum_vacuum_cost_delay. But that won't have an effect on an already running autovacuum worker.


Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

I would also test this rewriting (aasuming that sID is the primary key of docSVsys):

SELECT COUNT(*)
FROM [docSVsys] AS d
WHERE EXISTS
      ( SELECT *
        FROM [docSVtext] AS sv
        WHERE sv.[sID] = d.[sID]
          AND sv.[value] = 'doug'
      )
   OR EXISTS
      ( SELECT *
        FROM [docMVtext] AS mv
        WHERE mv.[sID] = d.[sID]
          AND mv.[value] = 'doug'
      ) ;


How can I setup replication with the DNS name instead of the Server Name

I was able to get this working, but with a fairly unsatisfying workaround - so if anyone else can contribute a better solution, please do. I am concerned that this solution will have unknown side-effects.

The broad steps are:

Remove everything to do with replication from the Publisher/Distributor, up to and including the distribution database.
Rename the SQL instance using the code sample pasted below. The new name should be the Machine FQDN (not the DNS FQDN). This is important so that it matches the name of the SSL certificate. Reboot the server (apparently just restarting the SQL instance isn't enough)
Redeploy the replication setup.
Change the EncryptionLevel setting to = 2 for all Distribution Agents.
Check all Distribution Agents are working fine.

Now, when I get to the sp_adddistributor command, it works fine if I use the Machine FQDN, for example:

exec sp_adddistributor @distributor = N'SERVER1.mywindowsdomain.com\DEV', @password = N'12345'

And indeed, EncryptionLevel can be set to 2 successfully for the Distribution Agents.

Known side-effects:

Replication Monitor no longer connects automatically to the Publisher when it is launched from SSMS. I should note that it does work fine when being launched on the Publisher/Distributor server using Windows Auth, but does not connect when I launch it from our Jump Host and try to connect as 'sa'. To make it connect in this situation I have to add the Publisher, and then subsequently edit the connection properties.  I suspect that this issue may be somehow complicated by the fact our servers are all Case-Sensitive, but I have no evidence to support this suspicion.
When using Distributor View in Replication Monitor, the right-panel list of Publications is always blank. The Publications do however show up in the tree view on the left. There is no problem using the Publishers View.

Code to rename server:

sp_dropserver 'SERVER1\DEV'
GO
sp_addserver 'SERVER1.mywindowsdomain.com\DEV', local
GO


Why do large images stored in database hurt performance

There's more to life than performance, like data integrity. If you only care about performance you should be writing all your code in Assembly.

There is a higher risk of screwing things up by using the file system:

If you keep files in the file system, you now have two things to backup instead of one.

Other stuff: dealing with orphaned files. Dealing with file path security issues (path traversal). Preventing users from accidentally deleting files. You need to replicate your db security to the file system now.

If you're dealing with pictures of cats, go ahead and use the file system. If you're dealing with x-rays, use the database.

If you're so concerned about performance, use the filesystem as a caching layer. Ex: GET /files/12234 -> if not on disk then fetch from db and put on disk -> read from disk.

Use incremental backup for your database.


Return distinct rows based on matching data in a column in another table in PosgreSQL

Each row of e_v2 will be returned at most once by your query.  Any duplicate values you are getting are duplicates that already exist in e_v2.  You can remove them by adding a "DISTINCT" before your select-list.

SELECT DISTINCT * FROM e_v2
WHERE EXISTS (SELECT 1 FROM etraining_v1 WHERE "POINT_KEY" = etraining_v1.key)
ORDER BY
"POINT_KEY",
"INTERVAL_READING"

Or you an clean up e_v2 so that it doesn't contain duplicates.


How do you handle adding nullable columns to each other in a query?

That's pretty much what you have to do. Since NULL + anything else is NULL, you have to wrap each column in its own isnull(), coalesce() or CASE.


How can I get more information about which lookup failed in an SSIS lookup?

There's nothing I've encountered that provides out of the box capability for this and I'll be delighted if someone proves me wrong.

Therefore, what I do is a controlled failure.

My lookup against DimEmployee should always yield a match. Data was loaded to that table before this dependent package executed. There's no opportunity for source data to change betwixt that load and but I was still running into situations where the lookup yields no match. And no, it's not a late arriving dimension, just they had a poor understanding of what the requirements were.

At any rate, what I do is set no match to "Redirect Rows to No Match Output". For those of you on 2005, you'll have to make do with the "Redirect Rows to Error Output"

I then count how many rows flow out of the failed lookup because if there's one failure, there might be more. That's the downside to even if the lookup could capture this row failed - it's only going to show you the first row and I generally want to know all the failures.

Controlled failure

I use a script task for this and as I type this up, I can see how this could be made into a re-usable component... My Script Task acts as a Transformation but I could have just as easily specified a Destination. I map in the columns that I used in the lookup and yielded no match. For this example, I have an EmployeeID and their EffectiveDT

What I'm going to do in this component is fire a Warning event for every row that comes through. The default for 2012 projects run in the SSISDB will capture warning events. Once all the rows have gone through, in my PostExecute method, I'll raise the Error event which will cause the DataFlow as a whole to fail.

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    /// <summary>
    /// Variable identifying whether we had any rows
    /// </summary>
    bool Found;

    /// <summary>
    /// Initialize our variable
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        this.Found = false;
    }

    /// <summary>
    /// Error out the entire task if we found any rows
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();

        if (this.Found)
        {
            bool cancel = true;
            ComponentMetaData.FireError(0, "SCR Lookup Employee", "Unmatched Employees found. See warning for more context", string.Empty, 0, out cancel);
        }
    }

    /// <summary>
    /// Emit warnings for all the bad rows and then flag the entire operation as having bad rows
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        this.Found = true;
        string msg = string.Format("Research->{0}:{1}", Row.EmployeeID, string.Format("{0:yyyy-MM-dd}", Row.EffectiveDT));

        ComponentMetaData.FireWarning(0, "Unmatched Employees", msg, string.Empty, 0);
    }
}

I then run my package on the server and observe the execution/operation id. If you go into the All Executions report in SSMS, this is the value in the first column, ID. In this case, I see 938 so I then run the following query in the SSISDB

USE SSISDB;
SET NOCOUNT ON;

DECLARE
    @operation_id bigint = 938;

WITH SRC AS
(
    SELECT
        OM.message
    ,   CHARINDEX('->', OM.message) AS arrow
    ,   CHARINDEX(':', OM.message, CHARINDEX('->', OM.message)) AS colon
    ,   LEN(OM.message) AS length
    ,   RIGHT(OM.message, LEN(OM.message) - CHARINDEX('->', OM.message) -1) AS elements
    FROM
        catalog.operation_messages AS OM
    WHERE
        OM.message_type= 110
        AND OM.message_source_type = 60
        AND OM.message LIKE '%research%'
        AND OM.operation_id = @operation_id
)
, PARSED AS
(
    SELECT
        SRC.message
    ,   CHARINDEX(':', SRC.elements) AS colon
    ,   LEN(SRC.elements) AS length
    ,   SRC.elements AS RelevantText
    ,   LEFT(SRC.elements, CHARINDEX(':', SRC.elements) -1) AS EmployeeID
    ,   RIGHT(SRC.elements, LEN(SRC.elements) - CHARINDEX(':', SRC.elements)) AS EventDate
    FROM
        SRC
)
SELECT
    P.message
,   P.RelevantText
,   P.EmployeeID
,   P.EventDate
FROM
    PARSED AS P;

That's going to give me all the relevant bits I'd see in the "All Messages" report but with the advantages of I can click and select things and I've already parsed out what I need.

message RelevantText    EmployeeID  EventDate
DFT Load FactPayroll:Warning: Research->90132693:2011-05-25 90132693:2011-05-25 90132693    2011-05-25
DFT Load FactPayroll:Warning: Research->900432371:2011-05-25    900432371:2011-05-25    100432371   2011-05-25
DFT Load FactPayroll:Warning: Research->900443209:2011-05-25    900443209:2011-05-25    100443209   2011-05-25
DFT Load FactPayroll:Warning: Research->900443418:2011-05-25    900443418:2011-05-25    100443418   2011-05-25

Running in interactive mode

If you're running this from the context of Visual Studio/SSDT/BIDS, I wouldn't even worry about the script's output, just add a Data Viewer and then you don't have to worry about crawling through logs.

Alternative to modifying your package (2012+)

The 2012 release of SSIS gave us a new tool for troubleshooting already deployed packages: Data Taps. These are a per-execution mechanism that allows you to generate a CSV (only) extract at a particular point in a Data Flow. This is amazingly helpful in highly regulated environments (SOX/SAS 70/HIPPA/PCI) where you can't just deploy an updated package without signoff from everyone on planet Earth. Just run the package with a special bit set and it'll generate an CSV in a well known location.

For the referenced screenshot above, if I wanted to see all the source data that was going to hit "LKP DimEmployee", I'd identify my PackagePath: \Package\DFT Load FactJobAction and my IdentificationString: Paths[ALL DateSK.Union All Output 1] and then use the script in Josh's article

USE SSISDB;

DECLARE @ExecutionID  BIGINT

/*****************************************************************************
--  First create an execution instance.  Data taps are valid for the specified
--  execution instance only
*****************************************************************************/
EXEC catalog.create_execution
        N'DW Folder',  --Folder name in SSISDB
        N'DW Project', --Project name in SSISDB
        N'FactJobAction', --Package name in SSISDB
        NULL, --optional parameter to hold reference ID for later use
        0, --optional  parameter set to 1 if 32-bit runtime required
        @ExecutionID OUTPUT;

DECLARE @DataTapID BIGINT;

/******************************************************************************
--  Next create the actual data tap.  The parameters specified below deterimine
--  at which point in a specific package the data tap will be added.
******************************************************************************/
EXEC catalog.add_data_tap
        @ExecutionID, --output from catalog.create_execution
        N'\Package\DFT Load FactJobAction', --PackagePath property value from data flow task in SSDT
        N'Paths[ALL DateSK.Union All Output 1]', --IdentificationString property value from data flow task in SSDT
        N'File.csv', --Desired Output file name
        NULL, --optional paramter to specify number of rows to log. NULL for all rows
        @DataTapID OUTPUT; --output ID

/******************************************************************************
--  This final block of code executes the package.  The data tap file output
--  will be found in the %SSISRoot%\DataTaps directory upon completion
******************************************************************************/
EXEC catalog.start_execution
        @ExecutionID;  --output from catalog.create_execution


~/.my.cnf is .my.cnf file in your home directory. The meaning of tilde ~ in unix is current user's home directory.

The values from different configurations are overwritten. So /etc/my.cnf will overwrite was what defined in /etc/mysql/my.cnf, and ~/.my.cnf will overwrite what was in previous files.


Transform a varchar field with #-delimiters into multiple rows

Here is one way to do it:
SELECT zz.`idx`
  FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('#3#9#15#', '#', num.`id`), '#', -1) AS SIGNED) as `idx`
          FROM (SELECT (h*10+t+1) as `id`
                  FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                       (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b) num
                 WHERE num.`id` > 0) zz
 WHERE zz.`idx` > 0
 ORDER BY zz.`idx`;

You can pass up to 100 #-separated IDs into the string and get what you need split out as an UNSIGNED integer.


MySQL slave seems confused as to just how far out of date it is

It means the thread has processed all events in the relay log files, and is now waiting for the I/O thread to write new events to the relay log.

Wait and watch whether size decreases as it seems to be updating because initially size was 46.9G and now it is 46.7G


Find fulltext indexes in SQL Server 2008

To find a list of tables that have a FULLTEXT INDEX:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS FTCatalogName ,
    f.name AS FileGroupName,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName
FROM
    sys.tables t
INNER JOIN
    sys.fulltext_indexes fi
ON
    t.[object_id] = fi.[object_id]
INNER JOIN
    sys.fulltext_index_columns ic
ON
    ic.[object_id] = t.[object_id]
INNER JOIN
    sys.columns cl
ON
    ic.column_id = cl.column_id
    AND ic.[object_id] = cl.[object_id]
INNER JOIN
    sys.fulltext_catalogs c
ON
    fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
    sys.filegroups f
ON
    fi.data_space_id = f.data_space_id
INNER JOIN
    sys.indexes i
ON
    fi.unique_index_id = i.index_id
    AND fi.[object_id] = i.[object_id];

To enable and disable a FULLTEXT INDEX refer msdn

sp_fulltext_table
   [ @tabname= ] 'qualified_table_name'
   , [ @action= ] 'action'
   [
   , [ @ftcat= ] 'fulltext_catalog_name'
   , [ @keyname= ] 'unique_index_name'
   ]

The preferred method is to use ALTER FULLTEXT INDEX [myFTindex] {ENABLE/DISABLE} instead of the deprecated sp_fulltext_table.


The use case of "purge after 30 days" is an excellent use of PARTITIONing.  (It is one of only 4 use cases that I know of.)

PRIMARY KEY(id, date)
...
PARTITION BY RANGE (TO_DAYS(date))
(...)

Then every day do

ALTER TABLE .. DROP PARTITION ...
ALTER TABLE .. REORGANIZE PARTITION future INTO
           PARTITION ...,  -- preparing for tomorrow
           PARTITION future ...;  -- this partition should stay empty.

Partition Details -- this is a follow-on to @jkavalik's "Mandatory reading", as @mootmoot provided.

Note:  No subpartition.  No partition by id.  No partition by account_id.  The big advantage is making the big nightly DELETE run instantly via DROP PARTITION.

You probably need some kind of index on account_id.  Consider a composite index.

If you need to discuss this further, please provide SHOW CREATE TABLE (with or without partitioning) and the main queries.


Index performance for CHAR vs VARCHAR (Postgres)

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.

However, there is one difference that can make a difference in performance: a char column is always padded to the defined length. So if you define a column as char(100) and one as varchar(100) but only store 10 characters in each, the char(100) column uses 100 characters for each value (the 10 characters you stored, plus 90 spaces), whereas the varchar column only stores 10 characters.

Comparing 100 character with 100 characters is going to be slower than comparing 10 characters with 10 characters - although I doubt you can actually measure this difference in a SQL query.

If you declare both with the length of 10 characters and always store exactly 10 characters in them, then there is absolutely no difference whatsoever (this is true for Oracle and Postgres)

So the only difference is the padding that is done for the char data type.

  Also keep in mind that there's often a very big difference between a CHAR and a VARCHAR when doing index comparisons

The above quote is only true if (and only if) the char column is defined too wide (i.e. you are wasting space due to padding). If the length of the char column is always used completely (so no padding occurs), then the above quote is wrong (at least for Postgres and Oracle)

From my point of view, the char data type does not really have any real-word use. Just use varchar (or text in Postgres) and forget that char exists.


Error 1290 despite of fixing my.ini file and having FILE permissions

In windows, you need to give double slash "\" in the path.

   load data infile "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\Book1.csv" into table sample fields terminated by ',';

You can give reverse slash "/" as below

   load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Book1.csv" into table sample fields terminated by ',';

Give it a try.


See vceron's post on https://discourse.snowplowanalytics.com/t/redshift-maintenance-best-practices/1124/2

When doing a Deep Copy in a highly unsorted table Redshift needs to sort this table before inserting into the new one.
This sort operation will be taken place in a intermediate temporary table, which at first be placed in memory, but as the data set is too big, will eventually spillover to disk.
However, those temporary tables are not compressed, and Redshift will allocate temporary disk space for that operation which results in disk full error if there is not sufficient space for the temporary data.


How to, efficiently, search through many sql dump files of really big size

ApexSQL Restore will mount the backup as virtual Database, and you can query it using SSMS.

I also published a PowerShell script that will let you analyze your backups in a given folder. I wrote it for analyzing size but you could easily adjust this to include properties like the date taken and such to find which backup you need to mount. You can find the script and article around it here.

I have not played with the Apex tool extensively but do not believe it will search all backups in a folder for you to find the one with the specific data you need. You would have to mount at most the full and then all the logs to get it to the date you need.

I have never seen one that does exactly what you are looking for, but the above should help I think.


Convert Oracle Query for Date to MySQL queries

Your second query can be converted to MySQL in two ways.

First one is simple query, that adds interval of days to compared date, depending on which dayofweek it is. It's not the most elegant solution, and different days of week require different values (or selects).
Code below uses CURDATE as a starting value, query for each column can be used separately:

SELECT
 DATE(CURDATE()) AS now_date,
 DATE_ADD(DATE(CURDATE()), INTERVAL (9 - IF(DAYOFWEEK(CURDATE()) < 2, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_monday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (10 - IF(DAYOFWEEK(CURDATE()) < 3, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_tuesday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (11 - IF(DAYOFWEEK(CURDATE()) < 4, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_wednesday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (12 - IF(DAYOFWEEK(CURDATE()) < 5, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_thursday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (13 - IF(DAYOFWEEK(CURDATE()) < 6, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_friday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (14 - IF(DAYOFWEEK(CURDATE()) < 7, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_saturday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (8 - DAYOFWEEK(CURDATE())) DAY) AS next_sunday;

Code found and copied from old GizmoLA post's comment. Similar one, just for Sunday, was explained on StackOverflow.

Second one is more universal, but requires creating function. In this case it's code could look like this one, found on SQLines post:

CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
 RETURNS DATETIME
BEGIN
 DECLARE start DATETIME;
 DECLARE i INT;

 -- Select the next date
 SET start = ADDDATE(start_date, 1);
 SET i = 1;

 days: LOOP
       -- Compare the day names
    IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN
     LEAVE days;
    END IF;

    -- Select the next date
    SET start = ADDDATE(start, 1);
    SET i = i + 1;

    -- Not valid weekday specified
    IF i > 7 THEN
     SET start = NULL;
     LEAVE days;
    END IF;

 END LOOP days;

 RETURN start;
END;

Usage (show next sunday):

SELECT NEXT_DAY(NOW(), 'Sun');

You can also use NEXT_DAY and SUBDATE functions to get the previous date:

SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7);


CREATE TABLE IF ONLY NOT EMPTY RESULT SET

You can create your tables in PL/pgSQL DO blocks (or functions if it makes sense). Check if a table is empty after creation and throw an exception if it is. Catch that exception (and do nothing). That rolls back all the changes the block (or function) made, i.e. the creation of the table, but none of the outer transaction.
For example, if you do
DO
$$
BEGIN
  CREATE TABLE elbat_1
  AS
  SELECT *
         FROM (VALUES ('a'),
                      ('b'),
                      ('c')) v (nmuloc_1)
         WHERE 1 <> 1;

  IF NOT EXISTS (SELECT *
                        FROM elbat_1) THEN
    RAISE EXCEPTION no_data_found;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
END;
$$
LANGUAGE plpgsql;

DO
$$
BEGIN
  CREATE TABLE elbat_2
  AS
  SELECT *
         FROM (VALUES ('a'),
                      ('b'),
                      ('c')) v (nmuloc_1)
         WHERE 1 = 1;

  IF NOT EXISTS (SELECT *
                        FROM elbat_2) THEN
    RAISE EXCEPTION no_data_found;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
END;
$$
LANGUAGE plpgsql;

elbat_1 will never be seen anywhere outside of the respective block. But elbat_2 will.
If you have another layer around that grants you transaction control, like you're writing some application, and can split each table creation into its own transaction, you can also simplify that and just create the table, check for it to be not empty and commit or otherwise rollback the transaction. Other transactions won't ever see the table if it's empty (unless it gets somehow emptied in another, committed transaction other than this one). You won't need the PL/pgSQL blocks then. Something along the lines of:
-- let your application start a transaction
BEGIN TRANSACTION; -- or do that by the means of transaction control functions of the adapter library
-- then create the (one) table
CREATE TABLE elbat
             AS ...;
-- return the result of
SELECT EXISTS (SELECT *
                      FROM elbat);
-- to your application and if it is true
COMMIT; -- or do that by the means of transaction control functions of the adapter library
-- otherwise
ROLLBACK; -- or do that by the means of transaction control functions of the adapter library


Caveat:  This is untested code.

CREATE TABLE Categories (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    category VARCHAR(...) NOT NULL,
    PRIMARY KEY(id),
    INDEX(category)
) ENGINE = InnoDB;

-- Create mapping table
CREATE TABLE Categories_Items (
    category_id SMALLINT UNSIGNED NOT NULL,
    item_id ...,
    PRIMARY KEY(category_id, item_id),
    INDEX(item_id, category_id)
) ENGINE = InnoDB;

-- Populate Categories table and generate ids
INSERT INTO Categories (category)
    SELECT DISTINCT category FROM items;

-- Start to convert Items.category to Items.category_id
ALTER TABLE Items
    ADD COLUMN category_id SMALLINT UNSIGNED NOT NULL;

-- Populate mapping table
INSERT INTO Categories_Items
   (category_id, item_id)
    SELECT c.id, i.id
        FROM Categories AS c
        JOIN Items AS i  ON i.category = c.category;

-- Finish conversion
ALTER TABLE Items
    DROP COLUMN category;


Linux Error: 111: Connection refused LSNRCTL

To me it looks like you did not start the listener but it is started. Your problem seems to be the tcp connection and not the ipc. So the listener is running, you can not reach it using tcp. Test this using telnet gaurav 1521

If the telnet does not give a connection, a firewall is blocking you. Stop/edit the firewall.


copy data from Previous non null rows and if first row is null copy next non null value

For that you need an ORDER and another column that helps get the last number
the cte #help1 get you the order with Row# needed for the following tables
ChangeIndicator is need t make a group , s that you can get the corect masked value
In #help2 the groupy are established, so that you can get the first vaule of the group, which has the value, you want fr that you need also the order built in #help1.

CREATE TABLE table1
    ([Masked] varchar(19))
;

INSERT INTO table1
    ([Masked])
VALUES
    ('5574-12XX-XXXX-1338'),
    ('6674-13XX-XXXX-0855'),
    ('7784-14XX-XXXX-5237'),
    (NULL),
    (NULL),
    (NULL),
    ('9984-11XX-XXXX-6196'),
    ('3384-11XX-XXXX-0615'),
    (NULL),
    (NULL)
;
GO

WITH #help1 as (
select *
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Row#
, case when [Masked] is null then 0 else 1 end as ChangeIndicator
from [dbo].[table1]
)
, #help2 as (
select *, Sum(ChangeIndicator) over (ORDER BY Row#) RowGroup from #help1
)
select
case when [Masked] is not null then [Masked]
else first_value([Masked]) over (partition by RowGroup ORDER BY Row#)
end UnitsBalanceFillDown
from #help2

GO

| UnitsBalanceFillDown |
| :------------------- |
| 5574-12XX-XXXX-1338  |
| 6674-13XX-XXXX-0855  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 9984-11XX-XXXX-6196  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |

db<>fiddle here


Best way to define related resources db model

This is definitely a workable solution. The one thing that this prevents is the use of built-in referential integrity options that your DBMS may provide.

As an example, resource_id and related_id cannot be foreign keys to another table - because both could reference multiple other tables.

So, maintenance of the relationship between a "document" resource_id and the document table must be handled in your code. You need to be sure that (for example) when you delete a document row, you remove rows from your resource_link table with that "document" resource_id or related_id.

In general, it's easiest to allow the database to maintain such information appropriately by itself. It's much less likely that you'll inadvertently allow a document row to be deleted without cleaning up connections elsewhere.

To avoid this, you'd need a solution where either:

you have 2 bridge tables for each possible pair of resource types (document_media, document_event, media_document, media_event, event_document, event_media, etc.; possibly even document_document, media_media and event_event); or
each resource type would need a bridge table with a column for each possible related resource (e.g., document_bridge would have the columns document_id, media_id, event_id, related_document_id, etc.), with a constraint to ensure one and only one of the related IDs is set.

Each of your options has its pros and cons. If you're confident that you'll be able to enforce data referential integrity "manually", your solution may be the cleanest, and the easiest for another person to follow. If the available resource type are expected to grow dramatically, then the other solutions (both of which would require adding or modifying multiple tables) will become more and more awkward to work with over time.

On the other hand, if the resource types are expected to be static, and there aren't more than a couple more, using one of the other solutions would ensure referential integrity (if properly set up with foreign keys). Also note that, with either of the other two solutions, it would be possible to create a view matching your proposed single table; if simply having a single place to get a simple list of what's related to something is considered a plus, you can have that with any option.

The queries to retrieve all related resources will be a little messy regardless of which option you take.

In terms of performance, the more indirection you have, the more work the DB engine will have to do - technically, at least. In practice, I suspect the performance difference between what you propose and (say) the "resource X resource" version (specific tables for each possible relationship, for both possible directions) would be small enough to be ignorable.

So, I don't think there is a single "best" way to do this. Ultimately, the comfort level of the development and support teams with the solution is likely to be the most critical determining factor. If your development team has experience with manually maintaining data referential integrity, your solution is perfectly reasonable, and might well be the easiest for someone else to follow.


In which server does the snapshot agent run?

The Snapshot Agent (snapshot.exe) runs on the Distributor.

From Books Online:

Replication Agents Overview

  Snapshot Agent

  The Snapshot Agent is typically used with all types of replication. It
  prepares schema and initial data files of published tables and other
  objects, stores the snapshot files, and records information about
  synchronization in the distribution database. The Snapshot Agent
  runs at the Distributor. For more information, see Replication
  Snapshot Agent.

However, the stored procedure sp_startpublication_snapshot is executed at the Publisher on the publication database and is used to start the Snapshot Agent job that generates the initial snapshot for a publication.

When you configured replication and created the publication, you specified the accounts in which the Snapshot Agent will run under at the Distributor and the Publisher.  This account information is saved.  This is covered in Snapshot Agent Security.

Even though you execute sp_startpublication_snapshot from the Publisher, it already has all of the necessary information to connect to the Distributor and start the Snapshot Agent.


Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I would say that learning how to understand explain plans is a vital skill in helping you to optimise SQL statements. I've found Christian Antognini's book, Troubleshooting Oracle Performance, very useful in detailing how these work, as well as explaining how to approach database optimization. While a few years old, you'll still learn a lot that's still relevant from it.

If you get more advanced you could look at Jonathan Lewis' books, but these are more in-depth so probably not a good starting point. Cost-based Oracle Fundamentals is quite old now, but much of it is still relevant. I've not read Oracle Core: Essential Internals for Troubleshooting yet, but it's received good reviews from the Oracle community.

As you're on 11g, if you have queries that take more than a few seconds I'd definitely recommend looking at the real-time SQL monitor (assuming you're appropriately licensed). As the name suggests, it shows the progress of an SQL statement in real-time, breaking down how long each operation has taken with details of rows fetched so far. It also keeps details of recently executed queries for a short while so can see how your changes affect a statement.

Oracle SQL Monitoring documentation: http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94543

Learning how to tune queries is something that will take time and practice. A few things I've learned:

Write queries to fetch as few rows as possible as soon as possible (e.g. you don't want to full scan a 10 million row table if you only need 100 rows from it)
Verify that number of rows expected in each step of an explain (expected) plan match those returned in the actual execution plan. When these are orders of magnitude different it's likely the optimizer isn't choosing the "best" plan.
Understand the principles of good indexing: how they work and when they should/shouldn't be used when executing a query (Richard Foote has a very in-depth blog discussing indexes in Oracle)

Mostly you'll learn by writing queries, looking at the (expected) explain plans and comparing these to the actual execution plans (either via tracing the query or using the SQL monitor). Then re-write the query, add/remove indexes, etc. and see how it affects the plans and execution times


Is it possible to log MySQL variable changes?

You can log changes that are made on-the-fly using the audit plugin.  You can get that from here https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html - variable changes are logged as SET commands.

The max_allowed_packet variable is set on both the client and the server - so in the [client] section and the [mysqld] section of the ini files (my.cnf for linux or my.ini for windows).

The variable may also be getting set in your application API, so you would need to check and change that too.

Finally there is an observation in the book High Performance MySQL that max_allowed_packet also controls the maximum size of a user-defined variable. These can be truncated or set to NULL if that max value is exceeded.


Need to create join on multiple ids on same cell

WARNING, this violates some basic database design principles. Like @a_horse_with_no_name notes, you want separate values (user names, user IDs) in separate rows / columns, not aggregated in a single comma-separated field. IMHO this is the most important reason you're not succeeding in writing this query on your own; otherwise, some simple JOINs would suffice.

But, if you insist, it can be done. Here's an example, using the STRING_SPLIT function from SQL Server 2016 to split comma separated strings and this trick to join them again:

SELECT file_name, LEFT(ca.value, LEN(ca.value) - 1), assign_users_id
FROM files AS extern
CROSS APPLY (
  SELECT user_name + ','
    FROM files AS intern
    CROSS APPLY STRING_SPLIT(assign_users_id, ',')
    INNER JOIN users
      ON users.user_id = value
    WHERE intern.file_name = extern.file_name
    FOR XML PATH('')
) ca(value)
GROUP BY file_name, ca.value, assign_users_id

SQLFiddle


Error occur during Oracle and Sql Server Integration

Solved the question.Select the check box 'use 32 bit runtime' in Execution options tab as shown below


I usually see this when the resultset is large, and has already started to be sent to the client.  The client (apparently, I have not dug into the source code of this) still needs to read everything that was in the pipeline to be sent to it but not yet received, and this can take a long time, especially if the client is in the midst of a swap storm.

Are you using any third party or little-used extensions?

If these aren't the case for you, you will need to provide a reproducer or do some investigation of your own.  While you wait for the cancel to take effect, what is busy on your system?  Just the psql process itself, just the postgres backend it was connected to, or both?  Can you run perf on the busy process and see what it is doing?  Or attach gdb to it and get some backtraces at random times?


Try using, The key here is dot notation.

sh.shardCollection("finalNBA.list", {"teams.name":1}})

Make sure you have already created an index for test.name field.


How to find documents that is equal or includes today in its date range?

Just use ISODate(...) (which is an alias for new Date(...))

You can skip $and, if you like:

{
   "startDate": { $lte: ISODate("2020-01-01T00:00:00.000+0000") } ,
   "endDate": { $gte: ISODate("2020-01-01T00:00:00.000+0000") }
}

A more dynamic and easier way would be to use moment.js library:

{
   startDate: { $lte: moment().startOf('day').toDate() },
   endDate: { $gte: moment().startOf('day').toDate() }
}


There are many things that can go wrong in Master-Master.  Use Master-Master, but do not write to both masters.  Pick one for all writes; leave the other one for failover.


How do I explicitly define data types for a MongoDB collection?

Use the mongoDB document validator. It was introduced in version 3.2 and allow you to validate documents during updates and insertions. It need to be defined when the collection is being created.

You can define the data types for a collection like this:

db.createCollection( "log",
   { validator: { $and:
      [
         { capped: { $type: "bool" } },
         { size: { $type: "int" } },
         { max: { $type: "int" } }
      ]
   }
} )

Document validator support types:

https://docs.mongodb.com/manual/reference/operator/query/type/

Official info about document validator:

https://docs.mongodb.com/manual/core/document-validation/


Convert OLTP database to OLAP Datawarehouse

First in this case I suggest you to adopt a star schema. the  transaction_data table obviously will be you reference to construct your fact table because it contains 'measures' like quantity,sales_value and other numeric attributes , the fact table also will contain substitution-keys of dimensions tables like Time, Product, Customer,demographic, etc.


Using GROUPing or ROLLUP rather than SUMing every column

If I understand correctly, the ideal solution would need GROUP BY GROUPING SETS which is not available in MySQL.

Your two options are to use

WITH ROLLUP:

SELECT date, type, currency,
       SUM(orders) AS orders, SUM(qty) AS qty,
       SUM(shipped_orders) AS shipped_orders,
       SUM(shipped_qty) AS shipped_qty,
       SUM(return_qty) AS return_qty,
       SUM(shipped_minus_returns_units) AS shipped_minus_returns_units
FROM daily_report
WHERE date >= '2015-07-01' AND date <= '2015-07-29'
GROUP BY date, type, currency
  WITH ROLLUP ;

two queries, one simple and one with the aggregations needed and UNION ALL:

SELECT date, type, currency,
       orders, qty, shipped_orders,
       shipped_qty, return_qty, shipped_minus_returns_units
FROM daily_report
WHERE date >= '2015-07-01' AND date <= '2015-07-29'

UNION ALL

SELECT date, type, NULL,
       SUM(orders), SUM(qty), SUM(shipped_orders),
       SUM(shipped_qty), SUM(return_qty), SUM(shipped_minus_returns_units)
FROM daily_report
WHERE date >= '2015-07-01' AND date <= '2015-07-29'
GROUP BY date, type

ORDER BY date, type, currency ;

The first option will have more groupings than you need (per date only and a single total in the end).


Find jobs that succeeded, but had steps that failed

You need to analyze data in the jobstephistory table.

You can use following query, it is slightly modified script from Analyzing SQL Agent Job and Job Step History in SQL Server by Atif Shehzad:

--Script # 1: To generate steps history of all jobs USE msdb GO

SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as StepExecutionStatus,
case jobsts.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as JobExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id and h.step_id !=0
inner join sysjobhistory jobsts
on j.job_id = jobsts.job_id and jobsts.step_id =0
WHERE
jobsts.run_status = 1
and h.run_status != 1
ORDER BY j.name, h.run_date, h.run_time
GO


Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

It is terrible, that you learned it that way (sorry!).

READ UNCOMMITTED let's you read every row, yes. Even those who are currently used in an INSERT, UPDATE, DELETE operation. This is very useful if you need to take a quick look at some Data or in mission critical SELECT-Statements where a block would be very harmful.

In fact, you risk your integrity. It may occur that you read a row, which is currently used to be deleted or in a change. It can also appear that you read a wrong value. This may be really uncommon, but it can happen. What do I mean with that? Well, think of a row which is very broad (it has many columns with many long nvarchar-columns). An update occur on this row and sets new values. In rare cases it can happen to you, that you read only a half row. Another thing can happen for example, if a user changes his login values. He changes his mail + password. The mail is already set, but the password isn't. This way you have a inconsistent state.

I would suggest to forget about READ UNCOMMITTED. Just use it where it's really needed.

Another alternative for you can be to enable the READ_COMMITTED_SNAPSHOT database option - therefor you can use READ COMMITTED SNAPSHOT due to the enabled row versioning in your tempdb. This way you just read another (older) version of a row. This won't block your Queries. But it may occur that you read an old value too, but an consistent old value.

Another idea can be WITH(READPAST) instead of WITH(NOLOCK). You will read the old state of the table (a bit like in the SNAPSHOT ISOLATION), but you'll skip all currently locked rows instead.


Failed attempt to fetch logical page - tempdb

Repeated corruption = faulty IO subsystem. Something, somewhere in the IO stack (driver, cache, controller, disk, etc)

I'd suggest that you get it onto a different IO subsystem to start with and investigate from there.


sp_msforeach db - Why do we need to use the USE keyword

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;';
-- 3,380 total rows on my system

You will also get a number of resultsets that all show the objects from foodb. You have to issue the command this way in order to get the command to execute in the context of each individual database:

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;';
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

One reason it works this way is that you might be executing from the context of the current database because it has a static object you want to use in relation to all of the databases. So, imagine you are in foodb and you create this table:

CREATE TABLE dbo.ObjectNameBlacklist
(
  name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name
  FROM dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM ?.sys.objects WHERE name = onb.name
  );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.


In the simplest case, when you restore a database, you tell RMAN to recover it to the most recent point in time by applying all the redo (archived and online) that is available.  That will re-apply any statements that were executed during normal database operations including things like dropping tables and deleting data.  If you want to restore a database to a point in time before something bad happened, you'd need to do a point-in-time recovery with something like

RUN
{
    SET UNTIL SCN 1000;
  # Alternatives:
  # SET UNTIL TIME 'Nov 15 2004 09:00:00';
  # SET UNTIL SEQUENCE 9923;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Normally, you'd specify an UNTIL TIME or UNTIL SCN that was just before the first erroneous command.

In general, though, you would try to avoid using RMAN to recover from human errors.  Generally, doing a full database restore is a last resort and generally there are other transactions going on that you don't want to lose.  If a DBA inadvertently drops a table, for example, you don't want to restore the database and lose all the transactions that other people had made after the table was dropped.  You would generally do a FLASHBACK DROP

FLASHBACK TABLE table_I_should_not_have_dropped
       TO BEFORE DROP

to restore the table from the recycle bin (which is almost instantaneous).  If you deleted


PostgreSQL maintains a list of reserved and non-reserved terms in the appendix. ALIAS is absent from that list. You can verify PostgreSQL does not use ALIAS by checking out the YACC grammar. Even as far back as Postgres95 ALIAS was not a reserved word (the first version in the migration from QUEL to SQL)

 MySQL does NOT reserve ALIAS
 MariaDB does NOT reserve ALIAS
 Oracle does NOT reserve ALIAS
 SQL Server does (in T-SQL) reserve ALIAS (as a potential future keyword on their "reserved keyword" list)

SQL Standard

In SQL-92, ALIAS was marked as a <reserved word>; but, there was no use assigned for that <reserved word>.
In SQL-99 ALIAS was marked as an "Additional Reserved Word", and added to the list of <reserved word>; but, there was no use assigned for that <reserved word>. Perhaps they reserved the term with the intent to define meaning later, and then withdrew it at a different point. Or, perhaps they reserved the term for vendor defined implementation. PostgreSQL reflected the spec's reservation in the docs, and then removed that reservation with the spec.
In SQL-2011, ALIAS is no where to be found and the word "alias" only appears in reference to 'Feature T053, “Explicit aliases for all-fields reference”'

ℹ There is no digitized copy of SQL-86, or SQL-89


32GB RAM needed to work with 200.000.000 rows in MongoDB?

As MongoDB documentation here With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the filesystem cache.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of (RAM - 1 GB), or
256 MB.

  For example, on a system with a total of 4GB of RAM the WiredTiger cache will use 1.5GB of RAM (0.5 * (4 GB - 1 GB) = 1.5 GB).
  Conversely, a system with a total of 1.25 GB of RAM will allocate 256
  MB to the WiredTiger cache because that is more than half of the total
  RAM minus one gigabyte (0.5 * (1.25 GB - 1 GB) = 128 MB < 256 MB).

Via the filesystem cache, MongoDB automatically uses all free memory that is not used by the WiredTiger cache or by other processes.

Note: The  storage.wiredTiger.engineConfig.cacheSizeGB limits the size of the WiredTiger internal cache. The operating system will use the available free memory for filesystem cache, which allows the compressed MongoDB data files to stay in memory. In addition, the operating system will use any free RAM to buffer file system blocks and file system cache.

To accommodate the additional consumers of RAM, you may have to decrease WiredTiger internal cache size.

The default WiredTiger internal cache size value assumes that there is a single mongod instance per machine. If a single machine contains multiple MongoDB instances, then you should decrease the setting to accommodate the other mongod instances.

To more about the serverstatus then run the command such as

db.runCommand( { serverStatus: 1 } )

Monitoring applications can run this command at a regular interval to collect statistics about the instance.

  Note: The value (i.e. 1 above) does not affect the operation of the command.

The following example includes all repl information in the output:

db.runCommand( { serverStatus: 1,  repl: 1 } )

The output fields vary depending on the version of MongoDB, underlying operating system platform, the storage engine, and the kind of node, including mongos, mongod or replica set member.

For the serverStatus output specific to the version of your MongoDB, refer to the appropriate version of the MongoDB Manual.

For your further ref here , here , here and here


How can I pass object to procedure from trigger?

I found this: SOURCE

  There is great reason why you should never call stored procedures from
  within triggers.

  Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB,
  you will experience a proportional volume of shared row locks and
  annoying intermittency from exclusive row locks. Such would be the
  case if triggers were manipulating tables with INSERTs and UPDATEs
  being stagnated to perform heavy duty MVCC inside each call to a
  trigger.

  Don't forget that Triggers require overhead. In fact, According to MySQL Stored Procedure Programming, page 256 under the head "Trigger
  Overhead" says the following:

  It is important to remember that, by necessity, triggers add overhead
  to the DML statement to which they apply. the actual amount of
  overhead will depend upon the nature of the trigger, but --- as all
  MySQL triggers execute FOR EACH ROW --- the overhead can rapidly
  accumulate for statements that process large numbers of rows. You
  should therefore avoid placing any expensive SQL statements or
  procedural code in triggers. An expanded explanation of trigger
  overhead is given on pages 529-531. The concluding point from that
  section states the following:

  The lesson here is this: since the trigger code will execute once for
  every row affected by a DML statement, the trigger can easily become
  the most significant factor in DML performance. Code inside the
  trigger body needs to be as lightweight as possible and -- in
  particular -- any SQL statements in the trigger should be supported by
  indexes whenever possible.

Check RolandoMySQLDBA answer.


How to simulate production workloads on a dummy database?

The fastest, easiest way is to rebuild an index on a large table.

That pushes a lot of write activity through, and it’s consistent, easily repeatable, and easily timed.

It’s not your workload (or anything near it) but helps you get a fast idea of the overhead right away.


Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered

Use try_cast() instead.

select *
from (
  select *
  from myNormalTable AA
  inner join #TEMP BB on try_cast(BB.NVARCHARCOL as bigint) = AA.MYBIGINTCOL
  --where try_cast(BB.NVARCHARCOL as bigint) is not null /* not neccessary for inner join */
 ) ZZ
where ZZ.MYBIGINTCOL = 1234

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

try_convert(datatype,val)
try_cast(val as datatype)
try_parse(val as datatype [using culture])


"Could NOT find Curses" error when installing MySQL from source

The clue is in the error message (which I also encountered today):

install the correct Curses library. If you're on Debian/Ubuntu then install libncurses5-dev ( use sudo apt-get install libncurses5-dev )
then remove CMakeCache.txt and rerun cmake. i.e. just do rm -f CMakeCache.txt.
Re-run cmake .


(Ola Hallengren) Deleting transaction log backups

I just noticed that you are only providing information on the differential and transaction log backup steps of the backup procedure. What is with the FULL backup?

Ola has implemented a fail-safe mechanism which will delete transaction log backups only if a FULL and/or DIFF backup is available.

  DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.

Reference: SQL Server Backup (from ola.hallengren.com)

This means that if you don't have a recent FULL or DIFF backup, then the @CleanupTime parameter will have no effect. All the Transaction Log backups will be kept on disk, even though the CleanupTime has passed.

You might want to check if your FULL, DIFF and TLOG backups are consistent and available on disk. It is important to have all files available. sequence available.

Example of Files on Disk

Assuming it is Friday 3.30pm (what a coincidence) you should find the following files in your backup folder if everything is set up correctly:
(I have put a separator between the FULL and DIFF backups)

SA | 1900 | FULL
----------------
SU | 1800 | DIFF
----------------
MO | 1800 | DIFF
----------------
TU | 1800 | DIFF
----------------
WE | 1800 | DIFF
----------------
WE | 1800 | TLOG
WE | 1900 | TLOG
WE | 2000 | TLOG
WE | 2100 | TLOG
WE | 2200 | TLOG
WE | 2300 | TLOG
WE | 2400 | TLOG
TH | 0100 | TLOG
TH | 0200 | TLOG
TH | 0300 | TLOG
TH | 0400 | TLOG
TH | 0500 | TLOG
TH | 0600 | TLOG
TH | 0700 | TLOG
TH | 0800 | TLOG
TH | 0900 | TLOG
TH | 1000 | TLOG
TH | 1100 | TLOG
TH | 1200 | TLOG
TH | 1300 | TLOG
TH | 1400 | TLOG
TH | 1500 | TLOG
TH | 1600 | TLOG
TH | 1700 | TLOG
----------------
TH | 1800 | DIFF
----------------
TH | 1800 | TLOG
TH | 1900 | TLOG
TH | 2000 | TLOG
TH | 2100 | TLOG
TH | 2200 | TLOG
TH | 2300 | TLOG
TH | 2400 | TLOG
FR | 0100 | TLOG
FR | 0200 | TLOG
FR | 0300 | TLOG
FR | 0400 | TLOG
FR | 0500 | TLOG
FR | 0600 | TLOG
FR | 0700 | TLOG
FR | 0800 | TLOG
FR | 0900 | TLOG
FR | 1000 | TLOG
FR | 1100 | TLOG
FR | 1200 | TLOG
FR | 1300 | TLOG
FR | 1400 | TLOG
FR | 1500 | TLOG

Possible Reasons TLOG Files Aren't Deleted

Ola's Failsafe Mechanism

If a DIFF backup is missing (for example the DIFF from WE and TH) then no TLOG backups will be deleted from disk up until the last DIFF or FULL backup. In this example you would have TLOG backup files up until the DIFF backup on TUesday at 6pm (1800).

Solution

Ensure you have FULL and DIFF backups on disk.

"SQL Server Agent" Service Account Insufficient Privileges

If the SQL Server Agent (INSTANCE) Service Account doesn't have enough privileges on the directory it is deleting files from, then it will be unable to delete the TLOG backup files.

Solution

Ensure the Windows Account of the SQL Server Agent Service has access to the network drive.
Check that the Run As setting in the job step is set to the SQL Server Agent Service Account.

SQL Server Agent Jobs Not Being Logged in Msdb

There are cases then the job steps of jobs executing are unable to enter the details into the msdb job tables due to contention (locks, blocks, long running transactions). The tables required by the backup job are being locked by another process (another maintenance plan / other 3-rd party tools / cleanup jobs / a long running transaction) and might be determined as the victim of a deadlock and rolled back. The data is missing in the msdb database and because no FULL or DIFF backups exist, nothing gets deleted.

Ola checks this with the following part in the DatabaseBackup stored procedures:

    IF @CurrentBackupType = 'LOG' AND (@CleanupTime IS NOT NULL OR @MirrorCleanupTime IS NOT NULL)
    BEGIN
      SELECT @CurrentLatestBackup = MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE ([type] IN('D','I')
      OR database_backup_lsn < @CurrentDifferentialBaseLSN)
      AND is_damaged = 0
      AND database_name = @CurrentDatabaseName
    END

This means that if the record for the previous DIFF or FULL backup was unable to be recorded in the msdb database, then the TLOG files will never be deleted.

Special Solution

Check that the FULL and/or DIFF backups of the database in question are recorded in the msdb database and verify that you don't have any long running transactions on the msdb database.
Reschedule (the/other) jobs to run at a slightly different times if you have multiple jobs running at the same time.

Good luck.


How can I group together rankings by number of users?

Your data is not enough to give the query a good test, but this FIDDLE seems to do it.

code of dbfille added:

SELECT y.journey, RANK() OVER (ORDER BY cnt DESC) as ranking
FROM
(
    SELECT x.journey, count(*) as cnt
    from
    (
        SELECT ID, STRING_AGG(RTRIM(evar),',') as journey
        FROM journey
        GROUP BY ID
    ) x
    GROUP BY journey
) y;

output:

journey,     ranking
content,support,     1
landing,content,homepage,     1
support,content,     1

when I add an extra journey like this:

INSERT INTO journey VALUES
(4    ,'landing'   ,'2019-01-01',      1   ),
(4    ,'content'   ,'2019-01-02',      2   ),
(4    ,'homepage'  ,'2019-01-03',      3   );

output:

**journey,     ranking**
landing,content,homepage,     1
support,content,     2
content,support,     2


The problem is with the erp.table, you are defining primary keys multiple times.

Try the following code:

CREATE TABLE [erp].[table](
    [SysRevID] [timestamp] NOT NULL,
    [SysRowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),(0))+CONVERT([binary](6),getutcdate(),(0)),(0))),
    [Company] [nvarchar] (8) NOT NULL DEFAULT (''),
    --THE ABOVE ARE DEFAULT FIELDS.  LEAVE THEM ALONE.  ADD FIELDS BELOW
    [AffilationNum] [int] NOT NULL DEFAULT ((0)),
    [VendorNum] [int] NOT NULL ,
    [CreationClass]   [nvarchar] (10),
    FOREIGN KEY ( VendorNum ) REFERENCES Vendor (VendorNum),
CONSTRAINT [idxEvAffiliation] PRIMARY KEY CLUSTERED
(   [Company] ASC,
    [AffilationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

Refer to Primary Key Constraints and Composite Primary Keys.


MsMerge_genhistory has alot of rows with pubid = null

I have found out that since i use anonymous subscribers the publication will take ownership of all changes and it will not set a pubid for its changes. the only row that the subscriber adds is a row with artnick = 0. So if the subscriber adds changes the subscriber will only have one row and it will have a valid guid for the publication but the row does not point to any changes. instead the publication will add the changes as if the originated there.

The problem with the metadata cleanup was not related to the null values, it was orphaned subscriptions. If an anonymous subscription is created from one of our handheld devices and is not synced, it caused the cleanup to exclude generations. This is added with sp2 in sql2012 and the cleanup creates a list of excluded generations that are "active" the problem is that if a subscription in sysmergesubscriptions has null in last_local_sentgen it will add all generations between 1 and sentgen to the exclude-list and the metadata does not get cleaned. I have tried to remove these subscriptions in my test environment and now i can see the cleaning is being run properly. I can also see that this can also happen if a subscription-creation does timeout. The subscription can be created but the database in the handheld is not downloaded the subscription becomes orphaned and never synced.

Hope this helps someone.


ERROR 1130 (HY000) not allowed to connect to this MariaDB server facing issue in granting permission

The GRANT statement doesn't work because the user@host combination root@'%' doesn't exist (yet). You can create it and then re-run the GRANT:

CREATE USER root@'%' IDENTIFIED BY 'your_secret_password_here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The WITH GRANT OPTION part gives the user the power to GRANT privileges to other users.

Note that in order to be able to connect from the other container, you may also have to remove or comment out any restrictions in your configuration (i.e. files like /etc/my.cnf, /etc/my.cnf.d/server.cnf, /etc/mysql/*.cnf) such as any bind-address settings:

bind-address  = 127.0.0.1

Comment out with a '#':

# bind-address  = 127.0.0.1

... and then restart MariaDB.


Why SQL function always returning empty result set?

XQuery is case-sensitive, as are things generally when dealing with XML.  Correct your function to have an upper-case Listing element and it will work.  I note you also pass in @ID but don't use it and have accidentally swapped around your Value1 and Value2 columns. You should also always use the text() accessor when reading untyped XML for a performance boost, as in my example below.

This worked for me:

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN

    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT
        l.v.value('(Value1/text())[1]','INT'),
        l.v.value('(Value2/text())[1]','INT')
    FROM @XML_Details.nodes('/Listings/Listing')l(v)

    RETURN
END
GO

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)

DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT *
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result

My results:

Just as an aside, your function is simple enough to be an inline table-valued function rather than a multi-line one.  Inline TVFs generally perform better.  Post back if you want to know more about that.

HTH


How to resolve too many connections and fatal error in mysql running on vps

First you need to do is run this query:

SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';

This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:

Run CHANGE MASTER TO for controlling replication coordinates
KILL or mysqladmin kill to kill threads belonging to other accounts
PURGE BINARY LOGS to systemically delete binary logs
Make configuration changes using SET GLOBAL to modify global system variables
mysqladmin debug command
enabling or disabling logging
performing updates even if the *read_only* system variable is enabled
starting and stopping replication on slave servers
specification of any account in the DEFINER attribute of stored programs and views
HERE IS THE MOST IMPORTANT ONE FOR YOUR PROBLEM: : Enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

You will need to login as root@localhost and revoke SUPER privilege as follows:

UPDATE mysql.user SET super_priv='N'
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
FLUSH PRIVILEGES;

Once you do this, whenever all users flood mysql connections, only root@localhost can login. After all, if everybody and his grandmother had SUPER privilege, this would bar root@localhost from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:

mysql> SET GLOBAL max_connections = 300;

That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.

CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf

[mysqld]
max_connections=300

You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:

cd
wget mysqltuner.pl
perl mysqltuner.pl

The 3rd line under Performance Metrics has this

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 20h 46m 22s (8M q [10.711 qps], 129K conn, TX: 90B, RX: 19B)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 2.1G global + 5.4M per thread (2000 max threads)
[OK] Maximum possible memory usage: 12.6G (80% of installed RAM)

See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.

In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.


postgresql bgworker SIGTERM handler called twice

This is because pkill sends the signal to all processes that match the given name, and Postgres is multi-threaded.

Use regular kill with the process ID of the background process that you wish to nuke.


`t2.t1_id IN (select t1.id from t1 ...)` is slower then `t2.t1_id IN (1,2,3,...,150)`

This should be best:

SELECT  COUNT(*)
    FROM  t1
    JOIN  `t2`  ON t2.t1_id = t1.id
    WHERE  `t1`.`site_id` = 5978)
      AND  (t2.created_at >= '2016-02-13 08:00:00')
      AND  (t2.created_at  < '2016-02-13 08:00:00' + INTERVAL 24 HOUR);

with these indexes, with the columns in the order given:

t1: INDEX(site_id, id)
t2: INDEX(created_at, t1_id)

Caveat:  If t2.t1_id = t1.id is "many:1", you will get the right answer.  If it is "many:many", you will get an inflated COUNT.

This formulation may also be efficient:

SELECT  COUNT(*)
    FROM  `t2`
    WHERE  EXISTS   ( SELECT  *
              FROM  t1
              WHERE  `id` = t2.t1_id
                AND  `site_id` = 5978
                    )
      AND  (t2.created_at >= '2016-02-13 08:00:00')
      AND  (t2.created_at  < '2016-02-13 08:00:00' + INTERVAL 24 HOUR);

with these index, with the columns in the order given:

t2: INDEX(created_at, t1_id)


How to normalize a table with multiple cells that have multiple values?

Normalisation is to remove information from tables, that are repe4ated by many times and ids as int are smaller than any text.
The bridge Tables you need, because you have a m:n relationship between film and users(cast, director, musician...)
Occupation is in my opionion a attributs of the relationship between film and user
Film (idfilm,Titel, plot,Wiki_Page, year,... )

Film2user (idfilm,iduser,idtype)

type  (idtype,occupation)

user (iduser, Name, Birth,...)

genre (idgenre,name)

Film2genre(idfilm, idgenre)

as you develop further, you can add more attributes or tables if you find more such redundant information


Update mysql DB when data has a single quote with Dynamic values

NOTE: Several questions/comments that wouldn't fit into a comment block ...

What language is your linux script written in? bash? ksh? perl? something else?

Where is the CN value coming from and how are you handling it, eg, is the CN value initially stored in a variable which is then used to build the actual query?

Have you tried passing in the CN value by way of a variable (either sqlcmd/-v or direct OS variable reference like "update ... CN = '${CN}'...")? [I'm guessing it still fails but want to confirm this.]

Could you pre-process the CN value, replacing any occurrences of a single quote with either a) two single quotes ('') or b) a backslash plus single quote (\'); then feed this resulting value into your dynamically created query? [Depending on your scripting language, and how you're handling the CN value, this may be doable with littler overhead.]

While your example demonstrates the issue, it doesn't show us how you're building the query itself ... so I'm wondering if there could be a (relatively) easy solution that, in effect, allows us to escape the single quote while building the query ... ?


Should I set up a password for mariadb.sys@localhost?

Here's a great article that explains the purpose of that account.
This section of the article in particular explains why MySQLTuner is complaining:

It uses an invalid password (see the output of SHOW CREATE USER
above). This ensures that should the account get unlocked by mistake,
it is still impossible to login. It is thus recommended not to reset
the password.


Duplicate delete query in binary log of MySQL Master

slave-skip-errors can hide even serious errors; don't do it.

Turn on the "general log" briefly.  This should capture a little more info about where the duplicate DELETE is coming from.  One of two things will happen:

There will be two DELETEs in the general log, too.  This will point at the application for issuing the delete twice.
There will be one in the general log but two in the binlog.  In this case, move on to the next step.

Do you have the binlog for replication?  If so, turn on the general log on a Slave to see if the duplicate stuff in the binlog really represents two DELETEs, not just some kind of stuttering in mysqlbinlog.

In any case file a bug report at bugs.mysql.com; the different paths give you different ways to complain.


How to grant access for multiple tables to a user in MySQL DB?

First create the user

GRANT USAGE ON *.* TO test@'localhost' IDENTIFIED BY 'whateverpassword';

To grant only SELECT privilege to the siteindex table in the greatstat database

GRANT SELECT ON greatstat.siteindex TO test@'localhost';

To grant only SELECT privilege to the whois1 table in the greatstat database

GRANT SELECT ON greatstat.whois1 TO test@'localhost';

To grant all privileges to every table in the greatstat database

GRANT ALL PRIVILEGES ON greatstat.* TO test@'localhost';

Please read the MySQL Documentation on the GRANT command.


Msg 102, Level 15, State 1, Server DESKTOP-U368A1B, Line 1 Incorrect syntax near '1'

You should remove the parentheses from around the parameters for your stored procedure, and enclose the call in double quotation marks. In a normal command line, this would look like:

sqlcmd -S DESKTOP-U368A1B  -Q "tranKonto 1,2,100,1"

Since this is in perl, you will need to escape the quotation marks:

system("sqlcmd -S DESKTOP-U368A1B  -Q \"tranKonto 1,2,100,1\"");


Dual site active-active highly available database

Availability Groups provide you the ability to scale out reads via secondary replicas (end user reads, like reporting workloads, as well as things like backup and DBCC). They do not offer anything new that lets you scale out writes.

Your options there are still things like merge and peer-to-peer replication, or manual sharding or distributed partitioning (where the app knows which database to write to and read from). I don't know to what extent (if at all) any of those solutions will support seamless failover. That might be better achieved with manual partitioning and Availability Groups or even mirroring - but again your app would have to understand that there is more than one database and they are distinct and separate. Availability Groups themselves don't provide anything to distribute the writes automatically.


How can I query the SQL Server Compatibility Level into a variable?

You could query the system table sys.databases:

-- for SQL 2005+
declare @level tinyint;
select @level = [compatibility_level]
from sys.databases
where name = 'AdventureWorks'

Select @level
GO

-- for SQL 2000+
declare @level tinyint;
select @level = [cmptlevel]
from sysdatabases
where name = 'AdventureWorks'

Select @level
GO


What firewall ports should be opened for central management server

When you attempt to enlist the Target (TSX) to the Master (MSX), the step that is failing is "Make sure the Agent service for '{MSX Server}' is running." This step simply checks to see that the SQL Server Agent is running on the MSX Server.

You can instead try to use msdb.dbo.sp_msx_enlist (documented here), which is run on the TSX to enlist it on the MSX. This may provide a more helpful error message, or it may even succeed.

If it succeeds, you can query msdb.dbo.systargetservers on the MSX to confirm that the server has been successfully enrolled, and is polling for instructions to download. Next, you can poll msdb.dbo.sysdownloadlist to see those instructions, and see if they are being consumed correctly by the TSX. There is an error_message column which should include download errors. Note, errors being logged here would be dependent on the TSX successfully connecting to the MSX--so your TSX would need to be able to connect to the MSX's DB Engine port (1433 by default) in order to even write the error messages.


Read mysql config (my.cnf) using mysql console

Depending on how your access was implemented, you may be able to look at my.cnf with the \! command.

mysql> pager less
mysql> \! cat /etc/mysql/my.cnf
....
mysql> nopager

If you want to know what variables are actually running on the server, look at SHOW GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES;


How to best handle MAX within a query/view

I think (not very clear) that you want something either:

For a VIEW solution - use something like this

SELECT v.visitor_id AS vid, v.visitor_name
FROM visitor v
INNER JOIN vw_max_visit_movement vmw ON
v.visitor_id = vmw.visitor_id;

Personally, I might want to use a CTE (unless I had millions of visitors), as per here. The reason I mentioned that you might want to avoid this approach for large tables is best explained here.

WITH visitor_max AS
(
  SELECT visitor_id, MAX(visit_movement_id)
  FROM visit_movement
  GROUP BY visitor_id
)
SELECT v.visitor_id AS "Vistor No.", v.visitor_name AS "Visitor name"
FROM visitor v
INNER JOIN visitor_max vmax ON
v.visitor_id = vmax.visitor_id;

In any case, the results are the same (not sure if this is what you're asking - if not, please expand the question)

Vistor No.  Visitor name
         2        Paulie
         3           Jim
         4          Bill
         5          Fred

===  DDL and DML for the tables used =====

CREATE TABLE visit_movement
(
  visit_movement_id INTEGER,   -- possibly a timestamp?
  visitor_id INTEGER
);

INSERT INTO visit_movement
VALUES
(34, 2),
(35, 2),
(34, 2),
(37, 3),
(38, 3),
(39, 3),
(40, 3),
(41, 4),
(42, 4),
(43, 5);

CREATE TABLE visitor
(
  visitor_id INTEGER,
  visitor_name VARCHAR (20)
);

INSERT INTO visitor
VALUES
(1, 'Mary'),
(2, 'Paulie'),
(3, 'Jim'),
(4, 'Bill'),
(5, 'Fred'),
(6, 'Xavier');

CREATE VIEW vw_max_visit_movement AS
SELECT visitor_id, MAX(visit_movement_id)
FROM visit_movement
GROUP BY visitor_id;


Convert character varying(255)[] to JSONB

You can not convert the array of strings to the single JSON object (directly at least). If you need to convert it to the array of JSONs use

select yourvalue::jsonb[];

If you need to convert it to the JSON array use

select jsonb_agg(unnest::jsonb) from unnest(yourvalue);

or

select to_jsonb(yourvalue::jsonb[]);

where yourvalue::jsonb[] ensures that all values in the array is really valid JSON and to_jsonb(...) converts it to the JSONB array.

Example:

with t(x) as (values(array['["a","b","c"]','{"d":"e"}','1']))
select to_jsonb(x::jsonb[]) from t;

Output:

[["a", "b", "c"], {"d": "e"}, 1]


Need MySQL configuration options like 'first time'

Encrypted passwords are stored in a table.  You apparently kept the data while updating the software?  So the pwd was still there.

You could change the password to nothing.
You could start mysql "skipping the grant tables.
You could live with the brief inconvenience.
You should consider moving forward to 5.5, 5.6, 5.7.  (With the same pwd issues.)


Error 71501, 71561 while encrypting columns using always encrypted feature of SQL Server 2016

Regarding your first question, which version of SSMS, are you using? If it's older than 17.0, I recommend trying the newest version (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms).

Based on your description, I suspect you are using SSMS 16.x. The likely root cause of this problem is that the objects in your database have external dependencies (on objects in other databases) or have broken dependencies. In SSMS 16.x, the Always Encrypted wizard does a comprehensive validation of all dependencies in the entire database, including objects not dependent on that tables that you are trying to encrypt. We have fixed this problem in SSMS 17, by limiting the validation only to the tables containing the columns you are encrypting and the wizard does not error out anymore when encountering a validation error. Instead, it completes encryption and produces a warning, allowing you to investigate/address the issue later.

Regarding your second question - you need to designate a primary key column for each table.


Log Shipping or Always on as DR for SQL Failover CLuster

My experience is that an AlwaysOn high availability group, HAG, is easier to maintain than log shipping. Lets set some variables for our scenario.

1.) Your version and edition of SQL Server is SQL Server 2016 Enterprise Edition. We are not on the latest SQL Server patch level. We need to apply a newly released service pack.

2.) The SQL Server database environment exists at a typical small to medium sized company (The active passive AlwaysOn build that you described in your question leads me to this conclusion). This company has a nightly or weekly maintenance window but like most companies the service level agreement is either four or five nines of up time (The database must be available either 99.99% or 99.999% of the time).

Lets look at how each feature allows you, the DBA, to keep the I.T. manager happy by maintaining those up time numbers.

An AlwaysOn HAG gives you the flexibility of being able to fail over back and forth between your AlwaysOn replicas. My assumption is that the primary and secondary replicas are not in close physical proximity to each other. Perhaps they are in different data centers or different racks within a data center. Regardless, If I need to perform maintenance on the primary I can set the availability mode from asynchronous to synchronous, fail over, perform my maintenance, fail back over, and set the availability mode back to asynchronous (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups).
  With a properly configured AlwaysOn HAG listener the end user will experience little to no interruption and you'll get to easily maintain that SLA.

Now lets take a look at Log Shipping. Without doing a cut and paste from the Microsoft books online there are five steps that have to be manually performed before you can failover. They can be found here... https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server .

Considering that most incidents and or maintenance windows occur after hours would you rather have an AlwaysOn high availability group that you can easily failover back and forth between replicas or try and get the primary and secondary databases into sync by applying transaction log backups?


How do I add keyboard shortcuts in SSMS 2012 to run a specific query?

The option is still there (although this is from SQL Server 2014):

You don't normally have to restart SSMS, just open a new query window.


No, no flush is needed.  The response given to the client is intentionally vague, to avoid disclosing information to a presumed attacker.  Look in the database server's log file for a more complete error message.


Safe to Shrink Database in this Scenario?

This should answer your question : http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Quoted:

  So what if you do need to run a shrink? For instance, if you’ve
  deleted a large proportion of a very large database and the database
  isn’t likely to grow, or you need to empty a file before removing it?

  The method I like to recommend is as follows:

Create a new filegroup
Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the

  tables and remove fragmentation from them at the same time
      Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

  Basically you need to provision some more space before you can shrink
  the old files, but it’s a much cleaner mechanism.

  If you absolutely have no choice and have to run a data file shrink
  operation, be aware that you’re going to cause index fragmentation and
  you should take steps to remove it afterwards if it’s going to cause
  performance problems. The only way to remove index fragmentation
  without causing data file growth again is to use DBCC INDEXDEFRAG or
  ALTER INDEX … REORGANIZE. These commands only require a single 8KB
  page of extra space, instead of needing to build a whole new index in
  the case of an index rebuild operation.

So if you have multiple filegroups you can use this technique, otherwise you can make a shrink and then do a rebuild of your indexes.


What is a proper way to handle a "sequence number" like field?

In Access, this is more about code than design, unless you decide instead on an autonumber, which provides the required unique field and is much easier and safer. Otherwise, in https://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1/11950647#11950647, you will find the following notes:

This requires a reference to the Microsoft ActiveX Data Objects x.x Library

Sequential Numbers

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String

    lngResult = 0  'assume fail

    strCon = "Provider=" ''Connection to back-end
    cn.Open strCon

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseServer

    ''Where BEInfo is a single line table
    strSQL = "SELECT ASeqNumber FROM BEInfo"

    rs.Open strSQL, cn, , , adCmdText

    'Note this is ADO, so no rs.Edit
    rs!ASeqNumber = rs!ASeqNumber + 1
    rs.Update

    lngResult = rs!ASeqNumber

    ''This should not happen, but just to be sure
    If DCount("ASeqNumber", "Table", "ASeqNumber=" & lngResult) > 0 Then
        lngResult = 0
    End If


Try rewriting the sub-query as a join:

SELECT YEAR(b1.checkin) as year
     , MONTH(b1.checkin) as monthnum,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON YEAR(b2.checkin) = YEAR(b1.checkin)
   AND MONTH(b2.checkin) <= MONTH(b1.monthnum)
GROUP BY YEAR(b1.checkin),MONTH(b1.checkin)
ORDER BY YEAR(b2.checkin) ASC, MONTH(b2.checkin) ASC

I also removed CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month, I don't think that will matter but it might be beneficial to do this once, and add it to the report in the application-layer.

You don't mention version of MySQL, but later versions have generated columns. You can try generating columns for YEAR and MONTH:

CREATE TABLE IF NOT EXISTS bookings
( DBID int unsigned NOT NULL AUTO_INCREMENT
, checkin date NOT NULL
, checkout date NOT NULL
, total int NOT NULL

, checkin_year SMALLINT as (YEAR(checkin))
, checkin_month SMALLINT as (MONTH(checkin))

,    PRIMARY KEY (DBID, checkin)
,    KEY total (total)

,    KEY (checkin_year, checkin_month)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

and then use those columns in the query:

SELECT b1.checkin_year
     , b1.checkin_month,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON b2.checkin_year = b1.checkin_year
   AND b2.checkin_month <= b1.checkin_month
GROUP BY b1.checkin_year, b1.checkin_month
ORDER BY b1.checkin_year, b1.checkin_month

BTW, what is the purpose of the index on TOTAL?


SqlPackage does not pick up variables from profile

Look at the references in your database solution. You will probably see them there. You can use the SQLPackage Script action to see the variables actually included in your dacpac.


invalid memory alloc request size on simple query with array_agg on Postgres 9.3

In your first query, you do the aggregate on the whole table, then limit the single row result to 200 rows.  This is because the LIMIT is applied on the result set of its containing query.

If you did an EXPLAIN select array_agg(st.search_token) from search_token st limit 200;, it would return something like

Limit [...]
   -> [...]Aggregate [...]

Basically, the closer one node in the plan is to the bottom, the earlier it is executed (this is more complicated than that, but in this context it is true).

The good news is that you found the solution, too :)


Understanding of SQL Server active/active cluster

You've essentially got it right.  Active/Active is really just a multi-instance SQL cluster.  When you install the Failover Cluster Instance (FCI) I1 on N1 of the Windows Server Failover Cluster (WSFC) you then have to run Add Node on N2 of the WSFC for I1, and vice versa for Instance I2. The SQL Server and Agent services are installed on both nodes, but will only be actively running on the node that actively has control of the SQL Instance.


How to export data from a SQL Server database with hundreds of tables and timestamp columns?

If the account that the SQL Service is running under has access to any network shares, then you can use database backup to a shared folder, and restore from there as well.

BACKUP DATABASE [dbname] TO DISK = '\\servername\sharedfoldername\dbname.bak'
RESTORE DATABASE [dbname] FROM DISK = '\\servername\sharedfoldername\dbname.bak'

Or you can use bcp.exe. Use SSMS Object Explorer to script out all the tables, and execute the script on the desired database
Use TSQL to build out all the bcp.exe commands, something like this:

select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' out "\\server\someshare\' + name + '.bcp" -n -S ' + @@servername + ' -d "sourceDb" -T '  from sys.tables order by name;
select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' in "\\server\someshare\' + name + '.bcp" -S "DestinationInstance" -d "destinationDb" -T '  from sys.tables order by name;


Big Data vs Relational Database on making structure of Data-ware house for telecom data analysis

Rule of Thumb: Does your workload fit with "traditional" approaches? Like oracle? Oracle can handle "00's of thousand of data of call" so does other databases like MySQL and SQL Server. You start looking into "Big Data Tools" when these solutions start to break. It's a pretty old video but it has some good insights: http://www.youtube.com/watch?feature=player_embedded&v=5keh0UU0mNo

Also I like the answer by Jeff Hammerbacher asnwer to similar question. Start at 27:01 http://www.youtube.com/watch?v=SVxc9lhMA0M

You could also consider reading the book "Planning for Big data" (free)
http://parasdoshi.com/2012/05/18/want-to-learn-about-bigdata-read-oreillys-book-planning-for-bigdata/


Setting up HA and a synced up reporting box all together using SQL Server 2014 Standard Edition

I would investigate the following scenario:  Setup a failover cluster to take care of the HA requirement and set up a third box with database mirroring to offload the reporting part.
Take a look at these articles for more details:
https://msdn.microsoft.com/en-us/library/ms191309(v=sql.120).aspx  and  https://msdn.microsoft.com/en-us/library/ms175511(v=sql.120).aspx


Is there any way to use "like" operator during oracle table partitioning

Virtual column partitioning:

create table table_name (
    uri varchar2(20),
    part as (
      case
        when uri like 'model://%' then 'mod'
        when uri like 'list://%' then 'list'
        else 'def'
      end
    )
)
partition by list (part) (
  partition part_mod values ('mod'),
  partition part_list values ('list'),
  partition part_def values ('def')
);

Seems to work:

insert into table_name (uri) values ('model://abc');
insert into table_name (uri) values ('list://abc');
insert into table_name (uri) values ('test://abc');

select * from table_name partition (part_mod);
select * from table_name partition (part_list);
select * from table_name partition (part_def);

Inserted rows are returned from correct partitions.