PostgreSQL multidimensional array - ok to use or misdesign in this case? I have a table called listings. For each listing, the user can upload 5 images. In the table listings, I have a multidimensional array images text[][]. Each multidimensional element will hold [ a base64 string, filename of image].

The only time I would do a search on the images[][] array is if the user removes a image, I would search for the filename of image in the images[][] array and remove it.

I would really like to avoid having a table just for images. No image will ever belong to more than a single listing I can not justify the cost of foreign key looks ups for what there is really no benefit.

I read in PostgreSQL documentation:

  Arrays are not sets; searching for specific array elements can be a sign of database misdesign.

Since the only time I am searching for a specific array element is when a user deletes a image... does this constitute a database misdesign? I was really hoping to use denormalization for the images of a listing.

If you want to avoid any overhead for a fixed maximum of images, and keep the disk footprint to a minimum, don't use an array, that adds 24 bytes of overhead for the array type, similar to a the overhead of a row.

Add 5 individual columns to the row. Some empty columns with NULL values cost almost nothing. That's even more awkward to search for (you'll have to look into each column), but you say that's irrelevant for the use case.

If the number of images is dynamic I would suggest a normalized design with a 1:n table images, though (which wouldn't be a bad idea anyway).

If you need to search occasionally after all, a bloom index would be an option. The manual:

  This type of index is most useful when a table has many attributes and
  queries test arbitrary combinations of them. A traditional btree index
  is faster than a bloom index, but it can require many btree indexes to
  support all possible queries where one needs only a single bloom
  index. Note however that bloom indexes only support equality queries,

Or a GIN index on an ARRAY expression covering the 5 columns.

Related answers concerning storage:

Calculating and saving space in PostgreSQL
Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
Configuring PostgreSQL for read performance


get duplicate "base" URLs with mySQL I can't quite seem to figure this out, and hoping someone can help. I was trying something like this, but doesn't seem to work:

SELECT *
FROM testtable
GROUP BY SUBSTRING_INDEX(urlTest,'/',3)
HAVING COUNT(*)>1

Here is a (simplified) table structure with what I am using:

CREATE TABLE `testtable` (
  `field1` int(11) NOT NULL,
  `field2` datetime NOT NULL,
  `urlTest` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `testtable` (`field1`, `field2`, `urlTest`) VALUES
(2, '2010-01-01', 'http://test1.com/somethingelse.php?id=1'),
(5, '2010-01-01', 'http://test1.com/'),
(6, '2012-02-02', 'http://test1.com/newscript/something'),
(7, '2013-02-02', 'http://test2.com/newscript/something'),
(8, '2014-02-02', 'http://test3.com/newscript/something'),
(9, '2015-02-02', 'http://test3.com/');

ALTER TABLE `testtable`
  ADD PRIMARY KEY (`field1`);

The output I want to get is to return any "base" urls that are identical with an 'identical' date, that I can manually inspect.

In other words, although "test1.com/somethingelse.php?id=1" and "test1.com" are "unique" url entries, the "base" url (i.e., "test1.com") is identical/a duplicate (because the dates -> 2010-01-01 <- are also identical). However, test1.com/newscript/something would not count as a 'duplicate' - because although the 'base' url is the same - it has a 'unique' date to the rest of the 'test1.com' urls (i.e., "2012-02-02")

So the output I'd like to get is:

2, 2010-01-01, 'http://test1.com/somethingelse.php?id=1'
5, 2010-01-01, 'http://test1.com/'

(because only test1.com has the same "base" unique URL (test1.com) as well as has the same timestamp. "test3.com" - while the same "base" url - since the actual timestamps are different, would be considered 'unique' urls)

How would I accomplish this?

Thanks!

Selecting all fields makes no sense - server will output one RANDOM field value from all values in the group. Use

SELECT testtable.*
FROM testtable
JOIN ( SELECT SUBSTRING_INDEX(urlTest,'/',3) AS domain,
              field2
       FROM testtable
       GROUP BY SUBSTRING_INDEX(urlTest,'/',3), field2
       HAVING COUNT(*) > 1 ) domains
WHERE SUBSTRING_INDEX(testtable.urlTest,'/',3) = domains.domain
  AND testtable.field2 = domains.field2

fiddle


Log files in Always on Disaster Recovery Situation In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node.
Two replicas in the same datacenter have sync replication for HA and the other two replicas in the other datacenter are async for DR purposes.
My company wants to perform a Disaster recovery test in the form of simulating one datacenter going down from Friday night to Sunday afternoon.
When the primary datacenter has gone down I can manually fail the AGs from the primary to the secondaries, we ave good latency so any data loss will be very negligable.
However the one thing that concerns me is how to handle the logs on replicas that are up and running in the DR site, which will grow and grow as the secondary replicas will be down and the log files cannot be backed up.
I had planned to just let the logs grow for the period of time (friday night to Sunday afternoon) and when the secondary replcias were brought back up let the databases sync up again and fail back.  There is a decent amount of space on the disc drive for the logfiles.
I am not sure if I can let this situation happen for this long amount of time, the only other option I can see is removing the secondaries that are offline and then recreating and reseeding the databases when the Primary site is back.  But I really dopn't want to have to do this as it will be a substantial amount of Database and will take quite a bit of time.
Is there any way I can handle it better than just letting the log files grow?

Is there any way I can handle it better than just letting the log files grow?

I believe you have the ideal options covered already. You either

Let the log files grow for the duration of the outage, or
Remove the replicas that are offline from the AG (and re-seed them when the primary datacenter is available)

I'd lean towards #1 if you have the disk space, and #2 if you become aware that there will be an extended outage (or disk space is low).


Script to automate enabling TDE on any unencypted user databases I'm looking for a way to automate the encryption of user databases.  Ideally this would be a job that can be scheduled to run every night.  The job would: find any user database that is not encrypted, create the DEK (if it doesn't exist), and enable TDE.  We use the same Certificate/PK for each database on every server (for better or worse).  This certificate has been backed up.

I attempted this by creating the following stored procedure.  However, it occasionally fails with the following error:

  Executed as user: TAC_NT********. Msg 33107, Level 16, State 1, Server *******, Line 2
  Cannot enable database encryption because it is already enabled.  Msg 5069, Level 16, State 1, Server *********, Line 2
  ALTER DATABASE statement failed.  Encypting Database: *********  Warning: The certificate used for encrypting the database encryption key has not been backed up.
  You should immediately back up the certificate and the private key associated with the certificate.
  If the certificate ever becomes unavailable or if you must restore or attach the database on another server,
  you must have backups of both the certificate and the private key or you will not be able to open the database.
  Encypting Database: *********.  Process Exit Code 1.  The step failed

The databases do get encrypted, but the job reports failure.

Here's the SP:

CREATE PROC [dbo].[sp_EncryptDBs] AS
BEGIN
    SET ANSI_WARNINGS OFF;
    SET XACT_ABORT, NOCOUNT ON;
/*  Get Databases that are online and have no DEK   */
    SELECT
        db_name(D.database_id) [Database]
    INTO #Databases
    FROM
        sys.databases D
    WHERE
        NOT EXISTS
            (
                SELECT *
                FROM
                    sys.dm_database_encryption_keys DEK
                WHERE
                    DEK.database_id = D.database_id
            )
        AND db_name(D.database_id) NOT IN
            (
                'Master',
                'model',
                'msdb'
            )
        AND D.state_desc = 'Online'
    WHILE (SELECT COUNT(*) FROM #Databases) > 0
    BEGIN
        DECLARE @Database NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases)
        DECLARE @Command1 NVARCHAR(MAX) = N'
            USE [' + @Database + ']
            CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
            ENCRYPTION BY SERVER CERTIFICATE ********
            '
        DECLARE @Command2 NVARCHAR(MAX) = N'
            ALTER DATABASE [' + @Database + ']
            SET ENCRYPTION ON
        '
        PRINT 'Encypting Database: ' + @Database;
        EXEC sp_executesql @Command1 ;
        EXEC sp_executesql @Command2 ;
        --PRINT @Command1
        --PRINT @Command2
        DELETE FROM #Databases WHERE [Database] = @Database;
    END
    DROP TABLE #Databases;

/*  Databases that have a DEK but are in an unencypted state    */
    SELECT db_name(database_id) [Database]
    INTO #Databases2
    FROM
        sys.dm_database_encryption_keys K
    WHERE
        K.encryption_state <> 3
    WHILE (SELECT COUNT(*) FROM #Databases2) > 0
        BEGIN
            DECLARE @Database2 NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases2)
            DECLARE @Command3 NVARCHAR(MAX) = N'
                ALTER DATABASE [' + @Database2 + ']
                SET ENCRYPTION ON
            '
            PRINT 'Encypting Database: ' + @Database2;
            --PRINT @Command3
            EXEC sp_executesql @Command3;
            DELETE FROM #Databases2 WHERE [Database] = @Database2
        END
        DROP TABLE #Databases2
END
GO

I was checking K.encryption_state <> 3 (encrypted) to test whether I should enable encryption.
i should have also checked for encrypted (K.encryption_state <> 2) when testing the status.


Updating Master table information based on Detail table status I have one master and one detail table as following

Master:-
   BatchID
   BatchStatus
Detail
   DetailID
   BatchID
   DetailRecordStatus (Possible values 1,2,3)

I am trying to write a single a query that can update the Batch Status in Master based on detailRecordStatus values equal to 2 or 3

Have you tried Update with join?

For example..

UPDATE TABLE1
JOIN TABLE2
ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID
WHERE TABLE2.BRANCH_ID IS NULL


Convert Text Column Web Service Data to Columns I have a database table which stores web transactions for a fundraising donation page. One of the columns in the table stores a bunch of concatenated data in the format 'data1=value1 data2=value2 data3=value3 moredata4=value3..."

The pairs represent fields/columns from an external database integrated thru a rest service. God knows why it's stores in this way, but I need to parse this column into multiple columns. I cannot figure out how to do this in an efficient and clean way.

Something to note: not all records/rows in the table have a value for every item in the combined column. So you might see 'data1=value1 data2= data3=data3...' also not all 'firlds/columns' in the concatenated pairs start with 'data', some might say 'tranamt=50 coupon=2bb2' for example. Also to note, the 'space' looking character between pairs is actually chr(10) not a space.

I'd really appreciate anyone's help on this. I'm stumped. I've researched this endlessly on stackexchange and Google, and not been able to find anything that helps me with this unique scenario. I've split delimited columns before, but not with a 'field/column' and value pair. I'm also new to oracle. This is in an oracle db.

Thanks!

dbfiddle

There surely are, a lot of, Oracle experts who can write more optimized code (in Oracle) than this fiddle....


mysqldump collation issues I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment.
The problem I am facing now is that our wp database after exporting, using the script, and then importing, using phpmyadmin, loses some special characters. Most noteably the € sign.
Phpmyadmin lists the database as latin1_swedish_ci and that is the collation setting I am selecting in the Import tab of phpmyadmin (iso-8859-1).
I am using the following script to export the database:

  mysqldump --user=user --password=password --default-character-set=latin1 --skip-set-charset databasename -r wp-db.sql

    tar -cpzf /home/backups/wp-backups/website-wp-$(date +"%d-%m-%Y--%H-%M").tar.gz wp-db.sql && rm wp-db.sql

My best guess is that there must be some issue with collation but I am not aware where some type of conversion happens in the steps that I am taking. At some point the € must be dropped.
When inspecting the wp-db.sql using file it is described as iso-8859-1, hence I wonder where conversion takes place.
I have since tried to test some things and discovered that I am not capable of converting a UTF-8file to iso-8859-1if it includes a € sign. At least not using

  iconv -f UTF-8 -t ISO_8859-1 test

From what I gather mysql runs in UTF-8 natively, might that be the issue?

If I do not bother with any of this it will completely ruin things. Pretty much everything that isnt wp files but stored in the database will be displayed incorrectly or will be completely missing. By adding these two arguments to my script I have eliminated most of the issues apart from special character problems.

"displayed incorrectly" -- That does not necessarily mean that the text is stored incorrectly.  How many steps do you have here?

You have data in a WP database?  Please provide SHOW CREATE TABLE for one of the tables so we can verify whether it is latin1 or utf8.  Also, do SELECT col, HEX(col) to see what you have in the table for €.  For utf8, the hex should be E282AC.  As you pointed out, it cannot be stored in latin1.  If you don't see that hex, stop; we need to debug things before this point.  Else continue...

Then you are doing a dump?  Let mysqldump specify the CHARACTER SET (not COLLATION).

Take a peek at the dump file.  Well, this may be useless, because it depends on what tool you use to peek at it.

Then you are reloading the data?  After reloading, what does the hex say?  Again, stop if it is not that same hex.

Now you are running WP and trying to display stuff.  On a web page?  Look at the HTML; there should be a <meta... > tag very near the start.  What does it say?  In particular, does it say charset=UTF-8?

One more thing to check is the SET NAMES utf8 (or equivalent) -- phpmyadmin establishes the CHARACTER SET one place; find it.  And find it for WP.


I need help understanding Postgres's archive cleanup functionality I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to  me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform recovery upon the slave server crashing (so basically everything except the last 1 or few segments, depending on how far behind my slave is running). I'd like to be able to perform emergency point in time recovery, in the unfortunate case of data corruption or accidental deletion, if possible. To do this, am I left with the following choices:

Keeping all WAL segments since the last base backup was created (in my case, this is the one that the slave server was created from)
Making base backups all the time or doing something with PGBarman, etc.?
Am I misunderstanding something completely?

If option 1 is used, I presume the pg archive cleanup command is rendered useless to me. If option 2 is used, is it true that I should use the cleanup command as intended (deleting all the extra WAL segments immediately after they're committed)? Is there something more that I'm missing (see option 3)?

Note: I'm using Postgres 9.2.4

You're correct. If you want PITR capability, you need to keep all archives between when the base backup was taken and when you want to be able to recover to.

For this reason it's often a good idea to do a fresh base backup (say) weekly, and rotate them out, so you keep a base backup plus a week of archives.

PgBarman should be able to help you with this.

Archive cleanup would not be desirable if you want PITR.


MySQL database column to Store Phone numberss What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table?

Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, AreaCode and PhoneNumber).

My application will primarily target an Indian Market and will grow to other markets as well. I prefer to have varchar columns for storing Phone Numbers.

But should I have 3 columns for Office Phone and 2 Columns for Mobile Phone? Or 3 Columns for all kinds of phone numbers with Area Code as Optional?

I have seen similar question here but no answers yet.

I would recommend the 3 column solution with area code optional. It will be able to handle cases of countries where mobile numbers do have area code (like the U. S.) and will be much easier to maintain and service.


CHECK Constraint to enforce upper case letter and pattern match For one of my columns I am trying to enforce a pattern. The first letter should be D upper case, and the remaining 3 characters should be digits. For example:
D678, D890, D000
I'm quite new with CHECK constraints  and things like regular expressions.
Below is what I've done so far, which (I think) enforces the general pattern. However when I try adding something like d900 it works, even though it's a lower case d. I expected this to fail.
Can someone please assist:
CREATE TABLE Systems(
SystemsID NVARCHAR(4),
Title NVARCHAR(30),
CONSTRAINT chk_SystemsID CHECK (SystemsID LIKE '[D][0-9][0-9][0-9]'));

It seems the database and column collations are case-insensitive so the LIKE expression is also case-insensitive.
One way to perform a case-sensitive compare in this scenario is by adding a COLLATE clause, specifying a case-sensitive collation. For example, if your database default collation is a case insensitive collation such as Latin1_General_CI_AS, the example below will override that collation with the case-sensitive version of the collation for the literal and perform the case-sensitive comparison you want:
CREATE TABLE Systems(
    SystemsID NVARCHAR(4),
    Title NVARCHAR(30),
    CONSTRAINT chk_SystemsID CHECK (SystemsID LIKE '[D][0-9][0-9][0-9]' COLLATE Latin1_General_CS_AS)
);

Below are related collation documentation pages for your perusal:

COLLATE
Collation Precedence


Do I gain read performance improvement by using zlib rather than snappy compression in MongoDB? My current storage engine is WiredTiger and its compression level is as default, snappy. I've come across MongoDB documentation and it's been mentioned that using zlib compress better but needs more CPU.

I want to know will zlib store more data in memory compared to snappy as it compress the data? I have a server with 16 CPU cores. As RAM is more expensive I'd rather to save on memory in case it keeps more data.

Is this correct? Can I blindly switch to zlib to cache more data and improve read performance?

NOTE: Our server is read intensive.

The documentation at https://docs.mongodb.com/manual/core/wiredtiger/#memory-use says that collection data is only compressed on disk, so uncompressed in memory. Indexes keep their prefix compression in memory, but not the block-level compression.

So changing compression algorithms is not going to directly reduce the amount of memory needed to keep a read-heavy workload efficient (i.e. to make sure its working set stays in RAM) unless you are already memory-starved in which case better compression may help by reducing IO as the storage system is thrashed because any data for the DB in the OS's buffers & cache will be compressed. The only way to tell for sure is to benchmark a realistic workload on production-like data in a test environment with each combination of options you are considering.

The "unless you are already memory-starved" is significant here though: once you are in that state the best the compression is likely to do is improve your performance from very very slow to just very slow.

One exception to the above would be any query that needs to read a data-set that is too large to fit into any practical amount of memory, in which case you may see significant improvements: all the data the query needs will need to be read from the IO subsystem anyway, and the compression is likely to help this. We'd need to know a lot more about your application's data and workloads to give specific hints as to whether this would have a noticeable effect in your case, and even then the only way to be sure is, again, to run benchmarks.

NOTE: this is not the case for all databases. For example with MS SQL Server's compression options the data in pages in RAM are compressed just as they are on disk. This reduces RAM use by the buffer pool at the expense of CPU time on every read of each page. When data is uncompressed in RAM the decompression CPU hit is only experienced as the data is loaded from disk so won't affect subsequent reads until that page/block/document is evicted because it hasn't been referred to recently.

  Can I blindly...

The short answer is: No.

A longer answer is: Nnnnnoooooooooooooooooooooooooooooooooooooooooooooooooo...

Less facetiously: blindly doing anything on a production database and/or application is dangerous so it is never a recommended course of action. Never do this if you value your users and your own sanity. Always test in a dev/test environment first no matter how much you trust any source of advice that suggests otherwise. It may feel like wasting time when you test and find no ill effects, but you will at some point in your career be very glad of having applied due diligence and saved yourself from a nasty and embarrassing event on a application in production! If you don't follow due diligence in this way, you may experience unplanned CV update events.


check for dependencies on other servers in SQL server 2000 I have an old server running SQL Server 2000.

I need to perform some maintenance on the server and one step is to check for dependencies on other servers.

The SQL Server 2000 machine has database A, while database B is running on a different server.  DB A has stored procedures that will modify some table fields on DB B and vice-versa.  I want to know what cross DB/Server references there are, what should I do?

I'm fairly sure that there isn't a reliable way to do this. I remember being very surprised when I dropped and recreated a table in SQL Server 2000 and lost all the dependencies to the stored procedures that refer to it.

You can try looking at sysremotelogins and sysservers, but I doubt that will cover the full list.


Postgres looped table (start sequence from 1 and rewrite old rows) I want table to contain logs and be looped, so when it came to last key in sequence it will start over from one and will rewrite old logs.

I can do it if i mark sequence  as cycle and do requests like that:

INSERT INTO logs (initiator_id, target_id, action, message) VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE SET target_id=$2, initiator_id=$1, action=$3, message=$4;

What i don't like, is that i need to repeat same inserts if there is conflict (line 2)

What i'am curios about, is it a good way to do, or there is other more practical ways for tables that you need clear lets say every 3 mounts?

Or maybe there is a way to generate unique id without using sequences?

For now i decided to make 2 columns primary key (date, initiator_id) as log is always 1 action at a time.

you could just use a monotonic sequence (always counting upwards) and delete records based on their age
you could do table partitioning by date and just drop the old partitions
Postgresql never replaces records, an update always creates a new record and marks the old one as unused. Autovacuum sooner or later will make the space used by the old one available to hold new records.  If you want something like RRDB your should probably be using somethign like RRDB instead of postgresql.


Consistent logical backup of databases that use MyISAM and InnoDB engines I have a question regarding the logical backup of MySQL databases
that use both MyISAM and InnoDB.

The mysqldump utility supports these two options:

--single-transaction - Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does)
[...]
Option automatically turns off --lock-tables.
-x, --lock-all-tables - Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off.

For InnoDB, we need --single-transaction
for MyISAM, we need - lock-tables or lock-all-tables (in case we need cross-database consistency).

So, how is a hybrid database (a database that uses both MyISAM and InnoDB engines) supposed to be backed up?

Edit:

Just to clarify, the question could be reformulated like this:

Do the lock-[all-]tables options guarantee a consistent backup of InnoDB tables?

With mysqldump you can only safely use --single-transaction if all your tables are InnoDB, otherwise your backup is inconsistent.

If you have the requirement for a hybrid backup, then you need the lock-tables on all tables in the backup (default), which will be safe for all engines.  It's also worth mentioning that the default options will make sure your backup is safe, you don't need to turn any special flag on.

Note: If you do have a hybrid mix, perhaps look at xtrabackup.  It will only be locking during the MyISAM phase of the backup.


Define Field as a Foreign Key? I am receiving data from an external source in batches that I need to import to my PostgreSQL database. I have 2 main tables: clients and providers. Ideally, they should have a 1:1 relationship, however, this isn't always going to be true with the data I receive. There are cases where a client will be sent in a batch and it's provider counterpart will only be sent in a later batch (or vice versa).

My question is what is the best way to work around this issue? Currently, I have the two tables that are unlinked but have the potential to be if the "keys" are present in both tables but something tells me this isn't the best way to go about things.

Looking forward to hearing your thoughts!

Many thanks

There are two kinds of foreign keys:  declared foreign keys and undeclared foreign keys.  Most people are familiar with declared foreign keys, but some don't realize that there is such a thing as an undeclared foreign key.

There are several advantages to declaring a foreign key.  The big advantage is rejecting invalid data at input time.  For interactive data entry, this is very valuable.

In your case, however, it's a detriment.  You probably want to store all the data you are given, even if some of it appears to be dirty data.  Later, you can go through some kind of cleansing operation where you detect certain kinds of errors, like orphaned references, and flag them for human intervention.

You would use this field in join conditions precisely the way you use foreign keys.  This is what is meant by an undeclared foreign key.  If there are any orphaned references, they will drop out of the join, and you need to program accordingly.

In a more formal situation, like a data warehouse, incoming data is loaded into a staging table at first, and only added to the warehouse data after cleansing.  That's probably overkill in your case.


Limit the degree of parallelism (DOP) available to any query On Oracle Exadata (11gR2), we have a relatively beefy database.

cpu_count is 24
parallel_server_instances is 2
parallel_threads_per_cpu is 2

We noted, through observation in Oracle Enterprise Manager (OEM), that performance was terrible due to queries being executed serially. To resolve this, all tables, materialized views and indexes were altered to take advantage of parallelism. eg:

ALTER TABLE SOME_TABLE PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);

The system was altered to turn on parallelisation:

ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = 'AUTO';

This resulted in better performance but we occasionally observed in OEM that a single query would tie up a DOP of 96 (all of the available resource). This resulted in subsequent queries being downgraded to a DOP of 1 (no parallelisation). Resulting in poor performance until the hogging query completed.

To resolve this we tried to limit the DOP available to any query with:

ALTER SYSTEM SET PARALLEL_DEGREE_LIMIT = 24;

This had no effect. We frequently observe queries that will use more than the limit (generally 48 or 96, but no real pattern).

How do we prevent any single query from hogging all of the available resource?

Parallel Server Sets:  PARALLEL_DEGREE_LIMIT limits the degree of parallelism, but if your query is sorting or grouping the number of parallel processes can be twice as much (two server sets to enable inter-process parallelism).  That explains why you will see 48 parallel processes even with a limit of 24.  This also happens if you use resource manager to limit the DOP.

Parallel hints:  PARALLEL_DEGREE_LIMIT only applies to statements that use the auto degree of parallelism.  Any statements that use a hard-coded degree, or even any type of object-level parallel hint, will ignore the limit.  If you have those hints, that could explain why you see 96 some times.

Calibrate IO:  Maybe automatic DOP is not being used, and thus the limit is not being followed, because the IO was not calibrated.  This query will tell you if the IO was calibrated:

select * from V$IO_CALIBRATION_STATUS;

I've seen this cause problems before, but my current system is not calibrated and automatic DOP seems to work fine.  You can tell if this is really an issue by looking at the Notes section of the explain plan.  If you see something like - automatic DOP: Computed Degree of Parallelism is 2 you're fine, but you don't want to see automatic DOP: skipped because of IO calibrate statistics are missing.

Increase PARALLEL_MAX_SERVERS:  Instead of worrying about running out of parallel servers, I would recommend that you significantly increase PARALLEL_MAX_SERVERS.
You should at least try to go back to the default value, PARALLEL_THREADS_PER_CPU x CPU_COUNT x concurrent_parallel_users x 5, between 240 and 960 depending on your memory settings.

Those high numbers sound ridiculous to many DBAs, but they actually make a lot of sense for the following reasons:

Oracle parallel servers are more light weight than most people assume.  (And hardly anybody ever tests it, they just find one situation where a large DOP causes
a problem and assume that high DOP is always bad.)
It's common to run an adhoc query in a GUI tool that only retrieves the first 50 rows, but still use dozens of parallel servers.  These queries are NOT consuming any significant resources, unless PARALLEL_MAX_SERVERS is too low.  Then people get yelled at for running
perfectly reasonable queries, which can lead to some ugly situations.
A very large DOP for a single query is not always bad.  Everyone assumes that if you keep increasing the DOP, the overhead will get too high and the performance will drop significantly.  But on many systems I've found that even a ridiculously high DOP will lead to better performance, although there are definitely diminishing returns, and it can be very unfair to other sessions.  But don't just guess, test it; take a query
and run it with all kinds of DOPs, up to a 1000.  You may be surprised.
Yes, too much parallelism can be bad.  But what's worse for the system, having slightly more than the optimum number of sessions, or forcing
a query to serial and basically killing an important job?  You should monitor the system before introducing arbitrary limits.


select for update gives error on indexed column here is my code:

mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ($row[0]+$k)";
$r=mysql_query($q);
}
mysql_query("commit");

if i run code with 2 browser if id column is not indexed code run correctly-  second session wait until first session commit -.

but if id column is indexed i get this error "mysql_fetch_array() expects parameter 1 to be resource, boolean given " on second browser - waiting session -.

when i use mysql_error() before mysql_fetch_array() i get this error "Deadlock found when trying to get lock; try restarting transaction". why?

SELECT FOR UPDATE tends to do row level locking in a deep way we tend to take for granted: Locking the corresponding index entries as well.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data at the index level.

You also have special cases of SELECT where you can lock specific rows on demand.

Look at this issue from another angle, "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT?" The answer to that can be Yes under a certain condition: If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.


Lock in mysql database My schema 'test' has 3 tables: 'user', 'post' and 'revision'.

CREATE TABLE IF NOT EXISTS `test`.`user` (
  `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
   PRIMARY KEY (`iduser`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`post` (
  `idpost` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `iduser` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
  `revision_count` INT NOT NULL DEFAULT 0,
   PRIMARY KEY (`idpost`),
   INDEX `fk_post_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_post_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost` ASC, `idrevision` ASC),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `fk_revision_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_revision_post`
     FOREIGN KEY (`idpost`)
     REFERENCES `test`.`post` (`idpost`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION,
   CONSTRAINT `fk_revision_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

And there is the script creates data:

INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (1, 'Ann');
INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (2, 'Bob');

INSERT INTO `test`.`post` (`idpost`, `iduser`, `content`, `revision_count`) VALUES (1, 1, 'this is first post', 1);

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, `content`) VALUES (1, 1, 1, 'this is old content of first post');

I create 2 sessions work concurrently. Session 1 works with query:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES
  ((SELECT max_idrevision + 1
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1),
   1, 2, 'abc xyz');

Now session 2 works:

BEGIN;
UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;
-- will hang by lock

-- but with this update
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;
-- not hang

I don't know what's lock mysql used with query insert in session 1? Is it row lock or table lock or anything else ? If it's row lock, why does second update query in session 2 not hang ?

Simplified scenario:

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost`, `idrevision`),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `user1_idx` (`iduser`));

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, content`) VALUES (1, 1, 1, 'this is old content of first post');

Session1:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES
  ((SELECT max_idrevision + 1
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1),
   1, 2, 'abc xyz');

select max(idrevision) AS max_idrevision from revision where idpost=1;

The above query will fetch the value from the secondary index 'index1'.
Because idpost and idrevision is covered by index1 itself. So index1 is a covering index for this query.
It will take S lock on the row(1,1) in the index 'index1' due to repeatable read isolation level.
(Read about repeatable read isolation level).

Session2:

BEGIN;
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;

Above query will change the clustered index alone, it won't change any secondary index like 'index1' because 'content' is not part of any secondary index.

UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;

But in this case, idrevision has been changed. So it will try to update the key part of 'index1' ( because idrevision is a part of key in index1).

Why it leads to hang:

As I mentioned earlier in session 1, S row lock taken for the row (1,1) in the index1.
Now session 2 tries to take X row lock to update the row(1,1) in the same index1.
If you look lock type compatibility matrix, S and X locks are Conflicts.

Note:

During hanging, you can open one more session and give the following query to find out where it is hanging.

mysql> select * from information_schema.innodb_locks;
--------------
select * from information_schema.innodb_locks
--------------

+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id    | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 1826:8:4:2 | 1826        | X         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
| 1817:8:4:2 | 1817        | S         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+

The above analysis is based on mysql-5.6


Is there a maximum IOPS for MySQL asynchronous replication on the slave server? I have been struggling with this issue for a while now. I have a simple Master-Slave setup with MySQL 8.0.15 installed on both systems. In day-to-day operations the slave keeps up the Master with no issue, but unfortunately with our network, the connection between the two server will sometimes be down for a couple hours or more. When this happens and the connection finally comes back up, it appears the relay logs are written on the slave at the expected speed (network speed), but the application of these logs into the MySQL database is not reaching the full potential of the physical disks.

The RAM and CPU allotted are not anywhere near capped either. When replication is catching up on the slave, the disks only reach around 600KB/s, but in typical operations (Windows operations) these SSDs can reach > 500MB/s. I know these are more 'random' writes, but I was not expecting this much of a performance drop off. This is actually the same speed as a similar setup we have that use only spinning disks spec'ed at far less IOPS.

The hardware/software is the same on the master and slave: The data partition is setup in a separate RAID-10 of SSDs with the OS and application on a separate RAID 1, 64GB RAM, 88 'CPU's, both are running Windows 2016 with MySQL 8.0.15, and they are using similar configuration files. Here are the startup configs I am currently using:

[client]
port=3306
default-character-set=utf8
[mysql]
no-beep
default-character-set=utf8
[mysqld]
port=3306
datadir="W:/Data"
character-set-server=utf8
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="S:/general.log"
slow-query-log=1
slow_query_log_file="S:/slow.log"
long_query_time=10
log-bin="S:/bin"
log-error="S:/error.log"
server-id=10
lower_case_table_names=1
secure-file-priv=""
loose-local-infile=1
max_connections=500
table_open_cache=2000
tmp_table_size=7G
thread_cache_size=48
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=12G
key_buffer_size=11M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_data_home_dir="W:/Data"
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_log_buffer_size=120M
innodb_buffer_pool_size=50G
innodb_log_file_size=15G
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=1M
max_allowed_packet=1G
slave_max_allowed_packet=1G
max_connect_errors=2147483648
open_files_limit=4161
sort_buffer_size=2M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060

REPLICATION

replicate-same-server-id=0
sync_binlog=1
gtid-mode=ON
enforce-gtid-consistency=true

Slave optimizations

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=88
slave-compressed-protocol=ON
log-slave-updates=0
binlog-group-commit-sync-delay=4000
binlog-group-commit-sync-no-delay-count=5
binlog-row-image=MINIMAL
binlog-format=STATEMENT

All of our databases use solely innodb tables. I have attempted various optimizations from my research online. I have tried adjusting slave-parallel settings and various innodb settings, none of which seem to have an affect on disk throughput when the slave is catching up. Does anyone see anything here I need to adjust or any recommendations on how to go about troubleshooting this? Or even if you think I should be looking at hardware vs. software? Thank you!

Please let me know if you need any more information.

The immediate fix for the Slave

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

This should speed up the slave a little.

Go to my.cnf on the Slave and add

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT

and restart mysqld.

Please read my old post Dynamic change to innodb_flush_log_at_trx_commit for more clarification.


Error: CImageHelper::Init () Failed Load of symbol file with path Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment.
Getting error:

CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL Server\MSSQL14..... Error Code 0xc0000004

There is nothing before or after this error and all I have to go by is a SPID. there is no set time, its happened once at 12:30AM and another time at 10:1 5AM.
I did some googling and there was one recommendation of reducing the MAXSIZE of the server memory. We have it set to 433,600 with 500,000 available.
How can I solve the problem and get rid of this error?
Additional information

Total RAM on the server is 500GB
Forced encryption is not enabled

Responses to comments

Anything in windows event log - app or system? Any mini dumps generated?

Event viewer has nothing that correlates with those time frames but something I will have to review further because I do see some errors.

For mini dumps - just check select * from sys.dm_server_memory_dumps

Nothing came back.

I resolved this by navigating to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn on the SQL server and modifying two ini files within the Binn folder. The first is "sqlctr" and the second is "sqlagtctr." Upon opening the files you will see that both contain "symbolfile=[SYMBOLINSTALLPATH]\sqlagtctr.h" or "symbolfile=[SYMBOLINSTALLPATH]\qlctr.h" nested under [info].

By replacing [SYMBOLINSTALLPATH] with the fully qualified path of C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn and restarting the SQL service I was able to get the issue resolved. For example, the beginning file content should now look similar to the following:

[info]
drivername=[DRIVERNAME]
symbolfile=C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlagtctr.h


Record doesn't show if selecting earlier date interval i encountered a very strange behaviour on mysql database.

I have this complicated query:

SELECT
    category, subcategory, sum(hours), count(hours),
    group_concat(concat(user,': ', comment) separator ' ') as comments
FROM
    duomenys, workers
WHERE
    duomenys.user=workers.full_name
    AND date BETWEEN '2017-05-23' AND '2017-05-25'
    AND user='USERNAME'
    AND departament='Some departament'
GROUP BY
    category, subcategory
ORDER BY
    id ASC;

In short it selects records from time interval (concats identical comments and sums hours).

For example:

Category   Subcategory   Hours  Count  Comments
Category1  Subcategory1  2:30   3      Username: User comment.

The problem is, if i increase time interval BETWEEN '2017-05-22' AND '2017-05-25', one record from 2017-05-24 is not displayed. Not sure if it's only this one, a user spotted it missing. I can select any date, or even interval of one day date, it shows ok. But if i select 22 or earlier as starting date, it dissapears from results.

The record is normal, same as others, the query is the same, i cannot spot anything unusual.
Same behaviour happens in phpmyadmin if i use this query directly on the database.

Table structures are very simple, no external keys, etc.:

CREATE TABLE IF NOT EXISTS `duomenys` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Ivedimas` datetime NOT NULL,
`Data` date NOT NULL,
`Category` varchar(255) NOT NULL,
`Subcategory` varchar(255) NOT NULL,
`Hours` int(11) NOT NULL,
`Comment` text NOT NULL,
`User` varchar(64) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18871 ;

CREATE TABLE IF NOT EXISTS `workers` (
`name` varchar(20) NOT NULL,
`full_name` varchar(80) NOT NULL,
`skyrius` varchar(80) NOT NULL,
`departament` varchar(80) NOT NULL,
PRIMARY KEY (`vardas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm sorry if names don't match somewhere, i tried to translate fields to english, for easier reading.

EDIT:
I thought i found the answer. After carefully examing that record, i found this quotation mark used in the record " instead of “„ like on every other records. But changing the quotation mark didn't help. And it shouldn't, because i use mysql_real_escape_string before inserting into databse.
That record looks like this:

SCR-4.1.1-2017 „Scrambled ciklo, scrambled ir daliųjų medžiagų vežimo sertifikatų išdavimo taisyklės“ projekto peržiūra (pakartotinai).

I think the problem with you query is the use of aggregated functions: sum and count. Using group by category, subcategory  shows only rows with different category and subcategory values: only one row for each category and subcategory.

You can add comments column on your group by clause. Another option is to remove group by clause, sum and count functions, but this second option generates a result without aggregates values.

I hope this helps

Miquel Boada


Mnesia: advantages and differences What are the advantages of Mnesia over major SQL database implementations and how it differs to them?

Can I use the database to hold really huge amounts of data without noticeable performance degradation ?

Sorry for being late to the party. :)
Here's my answer, based on having used Mnesia since 1996 and various other database technologies since 1988.

Mnesia and MySQL are indeed different beasts, and which one is the best depends very much on how you intend to use it.

If your application is written in Erlang, Mnesia allows you to store the data in the same memory space as your application, which means you can fetch a single data object as quickly as a few microseconds. This is not possible in MySQL, since your application and the database will be separated in memory. The reason why Mnesia can do this and still be robust, is that Erlang implements memory 'protection' at the language level.

Overall, SQL databases tend to favor throughput over latency, and when it comes to latency, Mnesia+Erlang are generally outstanding. You need to decide which one is most important to you. As it says in the docs (above), Mnesia's target applications were telecom switching applications, where response time requirements for e.g. a call setup were around 20 ms. This essentially meant that you could read from the database only if the data was in shared memory, but would avoid writing to persistent storage on a per-call-setup basis. OTOH, these applications have practically no need for ad-hoc query support, and do not use very large data sets. Some work has been done to extend the suitability of Mnesia for other domains, but it is not a priority for the Erlang/OTP development team. Mnesia is what it is, and is likely to stay that way.

In the link above where Mnesia and MySQL are compared for speed, one needs to remember that it's in eJabberd, which runs against a single server if it's MySQL and runs a fully replicated database if it's Mnesia - and large eJabberd clusters can have as much as 10 or more erlang nodes (and thus, 10 or more Mnesia replicas). From a redundancy standpoint, this is fairly ridiculous and costly, and Mnesia by no means forces you to do so. It obviously gives wicked-fast reads on each node, but writes will be very expensive. Several comparisons I've read have ended up comparing distributed Mnesia with a single-node MySQL; if redundancy is not needed for MySQL, it shouldn't be required for Mnesia either. Mnesia is quite flexible in letting you choose replication patterns, and data location is transparent to the application.

Mnesia is also not limited to 2 GB per table (although a particular storage option is). The largest Mnesia database I know of has ca 600 GB of data in (64-bit) RAM+disk - although I do not recommend this. Anything up to 10-20 GB should be perfectly fine with modern hardware though, but skip disc_only_copies entirely and use disc_copies - buy more RAM if you have to. I'd think twice before using the sharding support (mnesia_frag) - it works, but is rarely worth the trouble.

Perhaps the biggest difference between Mnesia and MySQL is SQL itself: Mnesia doesn't really have comparable functionality; QLC offers some support for ad-hoc queries, but it is not in the same league as SQL, and neither is the level of query optimization. In tooling and provisioning, MySQL is also superior, and if you need analytics, there's no question which one you should choose (i.e. NOT Mnesia).

The best way to view Mnesia is as an extension to the Erlang language. It puts data right at your fingertips, and is excellent for small data sets where the data structure and access patterns are well known. For this purpose, using MySQL is about as uncomfortable as is using Mnesia for the things where MySQL works best.

Most applications fall somewhere in between, and this is where it becomes a judgement call. You may well end up using both...


Ola Hallengren's index rebuiuld script - how to exclude more than one object? I'm using Ola's script to rebuild indexes. But after problems with script blocking particular objects that are used by SOLR, I wanted to exclude them and run script for them in another schedule. So, in main job I've set parameter @indexes like this:

@Indexes = 'ALL_INDEXES, -database.schema.object_1, -database.schema.object_2, -database.schema.object_3'

Is this a correct settings for this parameter? Can I add here multiple objects? Also, in second job I've set only this objects to be rebuild (without ALL_INDEXES).

Yes that looks like the correct syntax (except you have two '--' for your last index name? :-) )

Also, when you are writing your script for these indexes only, you do not need the '-' before the index name.

more info here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


Calculating count on same table based on condition So I need to calculate the amount of Clients who's LastName start with the same letter, then use that count as part of an Reference number.

I.e. something like this:

declare @AmountOfSimilarSurnames int;
set @AmountOfSimilarSurnames = (...)

declare @Ref nvarchar(100);
set @Ref = 'CLT00' + @AmountOfSimilarSurnames

I need help setting that AmountOfSimilarSurnames

So far I got this but it is working:

select COUNT(*) FROM Client c1
join Client c2 on RIGHT(c1.Client_LName,1) = RIGHT(c2.Client_LName,1)

Below is an example using RIGHT (starting character) and EXISTS (so that matching names are counted only once).

DECLARE @Ref nvarchar(100) = 'CLT00'
    + CAST(
        (SELECT COUNT(*)
        FROM Client c1
        WHERE EXISTS(
            SELECT 1
            FROM Client c2
            WHERE
                LEFT(c1.Client_LName,1) = LEFT(c2.Client_LName,1)
            )
        ) AS varchar(10));


Get-ClusterParameter Not Returning Expected Results I have been trying to get the parameter value for RegisterAllProvidersIP in my AG environment. However, the command is not fetching me all results. The resultset has just 4 parameters, and I'm wondering how to get all. I want to check this to investigate the ongoing connectivity issue. This is a multi-subnet AlwaysOn configuration.

I am using the below command:

Get-Clusterresource -Name AG_Name | Get-ClusterParameter

AG_Name being the listener name.

Please help in identifying what I am doing wrong or is it just some permission issue?

The command you are running is returning all of the parameters for an availability group resource.  There are not many, as most of the config is stored in SQL Server and not as cluster resource parameters.

The RegisterAllProvidersIP parameter is not a parameter of the availability group resource.  RegisterAllProvidersIP is a parameter of network name resources.  You can see all of the RegisterAllProvidersIP settings on all network name resources on the cluster with the following command:

Get-ClusterResource | where-object {$_.ResourceType.name -eq "Network Name"} |
    Get-ClusterParameter RegisterAllProvidersIP

I use the following command on my new cluster builds to disable RegisterAllProvidersIP on all cluster network name resources except for the Cluster Name resource:

Get-ClusterResource | where-object {$_.ResourceType.name -eq "Network Name"} |
    where-object {$_.name -ne "Cluster Name"} |
    set-ClusterParameter RegisterAllProvidersIP 0

Please keep in mind that this command changes that setting on all cluster name resources except for the Cluster Name resource, so if you have other applications set up on the cluster you may not want to use this.


Nasty Lookups in Query plan I've been trying to get rid of expensive lookups in my query plan but cannot seem to get my head around it.
I understand the idea of creating a covering index to eliminate the need for lookups but I don't know how to approach it with a complicated plan like this.

Any ideas on how to approach this would really be appreciated.

Query plan can be found here:
https://www.brentozar.com/pastetheplan/?id=HyBbQ0eRr

Thanks

So, a covering index is an index that has all the columns that are needed for your query.
A key lookup happens when the optimizer uses your index to get data, but is lacking one or more columns, so it fetches those from the clustered index.

As for example you have an index seek on Table_22 with the index table_22_promoId_Index.
Afterwards in the plan you see that the key lookup fetched a whole bunch of columns.

If you add all the columns from your output list of the key lookup, into your Table_22_PromoId_Index as included columns, you'll stop having a key lookup, cause now it'll just fetch everything from that index, without needing extra information from your clustered index
Then you can repeat that for the others

  I don't know how to approach it with a complicated plan like this.

Take it one step at a time, pick one lookup, and try and fix that, then move to the next one.
Also since you're selecting many columns, try and remember that making huge indexes also has downsides on updates/inserts and takes up storage, so be careful not too have too many indexes (All depends on how your specific case though.)

This is also why people advice you to only select columns you actually need instead of everything, since that makes your indexes more narrow and easier to tune.

A useful read about this topic :
Expensive key lookups

  What confuses me is that the Key Lookup contains something called a "Predicate" and a "Seek Predicate". I haven't seen this before and I'm a little confused by the explanation in the article.

So a seek predicate and a predicate in a key lookup is the same as in your Index seek. What happens is that he'll fetch the data from his clustered index (in the case of a key index) using the Seek predicate. Afterwards if he has to filter that data more, he'll use the predicate for extra filters.

There's a good answer from Joe Obbish explaning it really well:
Difference between seek predicate and predicate


Duplicate check constraint on multiple tables? I have an integer value for a data to be included into two or three tables, Say it is part no. The integer value of part no must be between 1 to 8. I want to add that constraint into the tables.

What method should I follow? Should I create constraint on each of the tables or just create one table with the values for part no and reference the table from others?

I would create another table.

This allows for several possibilities:

that one day some other part number such as 9 would be acceptable. You can quickly and transparently add it to the table, rather than changing a bunch of constraints on different tables.
that you might want to store some extra data alongside each part number, for example the name of the part. You can add a column to the table which you already have.

The only situation where I would not do it this way, was if 1 to 8 is something which could never ever change. For example, you might be sure that there will only ever by 7 days of the week (but who knows?).

In this case I would create a TYPE with this constraint. Then you can use the type in multiple tables. If the days of the week (or whatever) DOES change, you can change the TYPE.


SQL Import text files I have a directory that will regularly have text files dumped in it that I'm trying to get imported into a single SQL table.
Basic format of the text files is as such:

[DATA]
Test: 12345
Test type: Random Test Information - Not Important
Test date: 01/10/2010
Test time: 12:00:00

Channel number: 1
Rate: 12.345 kN/min
Start load: 12.345 kN
Stop load: 12 %
Area: 12345.678 mm²

[RESULTS]
END TEST STOP
Maximum load: 12.345 kN
Maximum strength: 1.234 MPa

[GRAPHINFO]
Axis0=6;0;60;0;0;0
Axis1=3;0.000;25.000;255;0;65535
Graph1=0;1

[GRAPH]
Time [sec]  Load [kN]
0.050   0.123
0.100   0.456
0.150   0.789
0.200   1.123
0.250   1.456
0.300   1.789
0.350   2.123
0.400   2.456
0.450   2.789
0.500   3.123
0.550   3.456

[CHECKSUM]
123ABC4D

The 'Graph' section can vary in length so the solution would need to know when to stop.

The data I'm wanting to import is

Test
Test Date
Test Time
Load Rate
Start Load
Stop Load
Area
Maximum Load
Maximum Strength
The Graph data
Checksum

My first table looks like below:

----------------------------------------------------------------------------------------
|TestNo|TestDate|TestTime|LoadRate|StartLoad|StopLoad|Area|MaxLoad|MaxStrength|Checksum|

My second table looks like below:

------------------
|TestNo|Time|Load|

TestNo will be my primary key to keep the data in check.

Update
Now I've got the following

--Import Test Results File into temp TextImport table
DROP TABLE #TextImport
CREATE TABLE #TextImport ( line VARCHAR(8000) )

INSERT  INTO #TextImport
        ( line )
        EXECUTE MASTER..xp_cmdShell 'Type "\\FilePath\Example.txt"'

--Creates TestNo temp table to parse out data within
DROP TABLE #TestNo
CREATE TABLE #TestNo (data VARCHAR(8000), [row] VARCHAR(8000))

--Inserts TestNo into TestData temp table
INSERT INTO #TestNo(data, [row])
SELECT stuff(line, 1, 5+charindex('Test: ', line), ''),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ROW] FROM #TextImport

--Inserts TestNo from above into Results table
    --Assumes TestNo is always on Line 2
INSERT INTO [dbo].[Results] (TestNo)
SELECT
data
FROM
#TestNo
WHERE [ROW] = 2

--Creates TestDate temp table
DROP TABLE #TestDate
CREATE TABLE #TestDate (data VARCHAR(8000), [row] VARCHAR(8000))

--Inserts TestDate into TestData temp table
INSERT INTO #TestDate(data, [row])
SELECT stuff(line, 1, 10+charindex('Test date: ', line), ''),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ROW] FROM #TextImport

--Inserts TestDate from above into Results table
    --Assumes TestDate is always on Line 4
----Need to pull TestNo from above rather than hard-coding it
UPDATE [dbo].[Results]
SET TestDate = (
                SELECT
                data
                FROM
                #TestDate
                WHERE [ROW] = 4
                )
WHERE TestNo = 12345

So now the big questions are

Can I get the TestNo from elsewhere so I don't have to hardcode it?

I feel like I could pull it from #TestNo somehow??

How can I make this now loop through a folder instead of hardcoded and start pulling through files, processing them, then moving onto the next in the folder?
How am I going to deal with Graph data? Time/Load and splitting them apart and pulling them into their table?

That shouldn't really be done on a single table, it will cause you all sorts of grief. Do it over two tables. Have your first table however you want, but without the "graph" values (time and load). Instead have simply a "graph_id" value or something similar. e.g. (based on your example):

------------------------
|Heading         |     |
|Test No#        |     |
|Test Date       |     |
|Test Time       |     |
|Maximum Load    |     |
|Maximum Strength|     |
|graph_id        |     |
|Checksum        |     |

Then have another table that stores your graph details (time and load):

------------------------
|graph_id        |     |
|time            |     |
|load            |     |

Access the data via a simple join.


Innodb: Operating System Error number 5 in MySQL I am trying to retrieve the data from MySQL ibd file but facing following error.

140601 17:00:45 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140601 17:00:45 [Note] Plugin 'FEDERATED' is disabled.
140601 17:00:45 InnoDB: The InnoDB memory heap is disabled
140601 17:00:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140601 17:00:45 InnoDB: Compressed tables use zlib 1.2.3.4
140601 17:00:45 InnoDB: Initializing buffer pool, size = 8.0M
140601 17:00:45 InnoDB: Completed initialization of buffer pool
140601 17:00:45 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140601 17:00:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Error: tried to read 16384 bytes at offset 136 2748317696.
InnoDB: Was only able to read -1.
140601 17:00:45  InnoDB: Operating system error number 5 in a file operation.
InnoDB: Error number 5 means 'Input/output error'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.

Please help me out from this issue.

Thanks

Given the log indication of starting recovery, then getting a failed read, this sounds consistent with a hard drive failure. You can see the same output with that as the diagnosis at http://forum.parallels.com/pda/index.php/t-70181.html and https://serverfault.com/questions/315664/mysql-wont-start, with the first answer in that second link showing a way to recover.


Identify mysqldump activity in MySQL log file? I have a database that is being regularly backed up by mysqldump by... something. It's not a script that I wrote, not sure whose it is. I'm trying to find out more about these dumps, such as what database user they are being executed as. Is there a way to identify an execution of mysqldump in the regular mysql logs?

The database being backed up is hosted on a mysql server, version 5.5.49.

I can't provide the options used to call mysqldump because mysqldump is not called from any script of mine. I am trying to find the script (it does not appear to be in any normal cron job - I think it is someone's custom script), and I think that if I can determine what user it runs as, I can probably find the person who wrote it.

SUGGESTION #1
The quickest way to locate any dump activity is the enable the slow query log
You should find queries that resemble something like the following
SELECT /* SQL_NO_CACHE */ from ...

Any large table running a long dump has to show up in the slow log.
If all the tables are small, then lower long_query_time with
SET GLOBAL long_query_time = 0.2;

Let it run for a few hours. Then change it back to 10.
SET GLOBAL long_query_time = 10;

If you don't change it back, the slow log will grow big very quickly.
SUGGESTION #2
Another way would be to check the process list. Please see my 5-year-old post (How to detect a running mysqldump?)
In your case, just run the following query
SELECT * FROM information_schema.processlist
WHERE info like 'SELECT%'
AND   info like '%SQL_NO_CACHE%';

This query will tell you the Process ID, MySQL username, and host doing the mysqldump.
GIVE IT A TRY !!!


Syntax Error In Postgresql Function Why am I getting:

ERROR: syntax error at or near "'SELECT '"

In:

CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[])
RETURNS TABLE(
  schema VARCHAR(128),
  id BIGINT,
  product_name VARCHAR(128),
  product_type SMALLINT,
  product_subtype SMALLINT,
  product_units SMALLINT,
  product_info JSONB,
  product_image VARCHAR(256),
  is_featured BOOLEAN,
  is_special BOOLEAN,
  is_alert BOOLEAN,
  distributor_name VARCHAR(128),
  distributor_image VARCHAR(256)
) AS $$
DECLARE
  sch name;
  n text;
  t text;
BEGIN

  n := to_char(lon,'999.999999999999999');
  t := to_char(lat,'999.999999999999999');

  FOREACH sch IN ARRAY schemas
  LOOP
    RETURN QUERY 'SELECT ''' || sch || ''', product_name, product_type, product_subtype, product_units, product_info, product_image, is_featured, is_special, is_alert, distributor_name, distributor_image FROM ' || quote_ident(sch) || '.listing WHERE ST_Contains(area, ST_SetSrid(ST_MakePoint(' || n || ', ' || t || '), 4326))';
  END LOOP;

  RETURN;

END;
$$ LANGUAGE plpgsql STRICT;

You want RETURN QUERY EXECUTE for dynamic SQL.

With RETURN QUERY you're trying to use a string literal as the start of a query. That's nonsensical and raises a syntax error.

Also, use format with the %I and %L specifiers for identifiers and literals, instead of using concatenation. Otherwise you have SQL injection issues in your function.


CPU Usage Incorrect in sys.dm_os_ring_buffers I'm running a cloud VPS with a SQL Server instance. Because it's for personal use, I'm using express edition (I can't use developer edition because I technically have production apps running on it and there's no way I could afford Standard+).

I'm trying to get baseline captures going using Brent Ozar's tutorial using sp_BlitzFirst. The problem I'm running into is that ProcessUtilization in sys.dm_os_ring_buffers always comes out as 100, no matter what the actual CPU usage is at the time.

VM Info

  @@version: Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)   May 15 2019 19:14:30    Copyright (C) 2017 Microsoft Corporation    Express Edition (64-bit) on Linux (Ubuntu
  18.04.2 LTS)

Host: 1 & 1 Ionos VPS

lscpu output

Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
CPU(s):              2
On-line CPU(s) list: 0,1
Thread(s) per core:  1
Core(s) per socket:  1
Socket(s):           2
NUMA node(s):        1
Vendor ID:           GenuineIntel
CPU family:          6
Model:               63
Model name:          Intel(R) Xeon(R) Gold 5120 CPU @ 2.20GHz
Stepping:            0
CPU MHz:             2194.844
BogoMIPS:            4389.68
Hypervisor vendor:   VMware
Virtualization type: full
L1d cache:           32K
L1i cache:           32K
L2 cache:            1024K
L3 cache:            19712K
NUMA node0 CPU(s):   0,1
Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 avx2 smep bmi2 invpcid xsaveopt arat flush_l1d arch_capabilities

Microsoft says that DMV (ring buffers) doesn't work on SQL Server 2017, only 2019:

  Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL
  Server by built-in tools as well as third party monitoring utilities.
  When SQL Server 2017 was released on Linux, unfortunately this DMV did
  not return correct CPU usage information by SQL Server process. SQL
  Server team is glad to announce that the starting with SQL Server 2019
  release candidate, the sys.dm_os_ring_buffers DMV returns SQL CPU
  utilization correctly.


SQL Server Setup failure - Cached MSI files are missing Trying to apply SQL Server 2008 SP4 to an old instance I now have to look after, but the installer is failing with
TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
The cached MSI file 'C:\Windows\Installer\5dd0fcab.msi' is missing.
Its original file is 'sql_engine_core_inst.msi' and it was installed for product 'Microsoft SQL Server 2008 Database Engine Services' from 'e:\a9aebd1f210f70e7096a\x86\setup\sql_engine_core_inst_msi', version '10.3.5500.0', language 'ENU'.
To resolve this problem, recover the missing file from the installation media and start setup again.
For more information about how to resolve this problem, see 'Steps to restore the missing Windows Installer cache files' (http://go.microsoft.com/fwlink/?LinkId=144387) in the Microsoft Knowledge Base.
Error code 0x84B20002.

BUTTONS:
OK
I have managed to find and fix a lot of missing MSP files, but cannot locate the source media for SQL Server 2008 SP3 (10.3.5500.0). Any ideas if this is available anywhere that I can download?
http://www.microsoft.com/download/en/details.aspx?id=27594#overview - no longer available
(Yes, I know I need to upgrade to a newer version - one step at a time!)

Did you find the tool that automates this ?
https://docs.microsoft.com/en-us/archive/blogs/psssql/sql-setup-toolsuite-introduction-1-fixmissingmsi


Why do the databases go into the "recovery pending" state when the share permissions are changed from "everyone"? Yesterday we had a power failure and after the power was back our financial databases on the SQL did fall into "Recovery Pending" state. After struggling a while I've remembered that a few days ago we did change the sharing permissions of the database directory to certain users from "everyone" and tried to revert the permissions back to "everyone" and everything was OK again in a few minutes.

What reminded me of this directory share permission change were these error messages in the event log:

  FileMgr::StartLogFiles: Operating system error 2(The system cannot
  find the file specified.) occurred while creating or opening file
  '\\SERVER\xxx.ldf'. Diagnose and correct the operating system error,
  and retry the operation.

  FCB::Open failed: Could not open file \\SERVER\xx._MS for file number
  1.  OS error: 5(Access is denied.).

Why did this happen?

It sounds like a simple permissions issue.

Why did this happen?

The Everyone permission literally allows anyone to connect to those network shares and access those files. When you remove those permissions, now no one is allowed to access them. What may be confusing is that you may think that SQL Server has access to those files and shares, when, in fact, it does not.

To allow specific access to those databases that run over SMB shares (starting with Microsoft SQL Server 2012), Microsoft recommends that the SQL Server service run as a <domain name>\<user name> and that this account have FULL CONTROL to the file share AND NTFS permissions to the underlying files and/or directories within that file share.

Alternately, Microsoft recommends that if you use a system account instead for the SQL Server service account then apply FULL CONTROL share AND NTFS permissions to the account <domain_name>\<computer_name>$--where computer name is the computer name of your SQL Server.

I'd retry removing those "everyone" permissions once more, in a test environment, to validate that this fixes your issue.

For more details see:

https://msdn.microsoft.com/en-us/library/hh759341.aspx


ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins I've a strange behaviour of Sql Developer with OS Authentication, 4.1.3 32/64bit, gives ORA-01017 while sqlplus is logging in without any problem.

This is the example answer sqlplus gives me:

~ sqlplus  /@XXXX

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 27 08:50:10 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

after that running SELECT user FROM DUAL; return my domain user.

While this is what i get from sql developer configured on Databases -> Advanced.

as it is shown it returns Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied

And when I try to connect from the Connection section I get a little more detailed error:

An error was encountered performing the requested operation:

ORA-01017: invalid username/password; logon denied
01017. 00000 -  "invalid username/password; logon denied"
*Cause:
*Action:
Vendor code 1017

Thank you in advance for any idea or help

The problem was that I had too many instant clients and Oracle clients on my machine (one oracle client 'x86').

The instant clients were too old.

The only working instant client for me is the '12.1.0.2.0'.

I downloaded:

instant client basic lite win64 12.1.0.2.0
instant client jdbc win64 12.1.0.2.0
instant client sqlplus win64 12.1.0.2.0

fixed the PATH variable with the path I extracted the instant client '12.1.0.2.0' and removed all others from it.

In the end I set up the environment variable TNS_ADMIN pointing to my sqlnet.ora and tnsnames.ora. I think this TNS_ADMIN is not necessary.


Listener does not connect to the correct instance I have an instance with Always ON and two replicas, one of primary and one of secondary. I have created another instance and enabled Always On, each group located in different instances has a different listener with IP and different port. For the first case the listener works well, that is, it connects to the primary replica, and when the failover is done it behaves well. For the case of the second listener, I do not know why to try to connect through this, I am connecting to the group-instance of the first listener, someone has any ideas ??. For the second case, the replicas are fine, synchronized, the problem is that the listener does not connect where it should.

Thanks

By default a standalone SQL Server instance (ie non Failover Cluster Instance), will listen on its port on All IP Addresses.  See  TCP/IP Properties (IP Addresses Tab).

And this means that the instance will accept TCP connections on its designated port on all current and future IP addresses.  So when the Cluster brings the AG Listener IP address online, it won't just be the SQL Instance that owns the AG that listens on that IP address.


How can I create such a query in MS access so that only customers with more than 3 bookings show up? I have two different tables, the "customers" tables, with "customer ID" as the primary field and a few more fields such as Name, Surname etc. (All in text format).

The second table is "Booking details", It has the primary key as "Booking ID" and then customer name, surname etc, (Again, all in text)

So, basically, in the Booking details table, each booking is given a unique Booking ID, and one customer can do several bookings, i want to create a query that sorts out only customer with more than 3 bookings ?

SELECT Customers.Customer_ID
FROM Customers
INNER JOIN Bookings
    ON Customers.Customer_ID = Bookings.Customer_ID
GROUP BY Customers.Customer_ID
HAVING COUNT(Customers.Customer_ID) > 3

Visually (some terms are approximate because I have no MS Access with English interface):

Add both tables to Query pane
Join them by proper fields
Select fields from Customers table which you want to output, do NOT add fields from Booking table
Press Totals knob on the ribbon
Add one more Customers.Customer_ID to the fields list
Change its Grouping function from Grouping by to Count
Set the criteria for this field to >3
Uncheck Show if you do not need in its value


how to retrieve mongodb admin password installed on AWS EC2 3rd party developers have installed MongoDB on AWS EC2 instance and later handed over code to me.

Now I don't know what's the password for admin user of MongoDB but I do have ssh keys to connect to that EC2 instance.

My query is how to reset password for MongoDB admin user ?

You can't retrieve existing passwords, but you can reset them assuming you have ssh access to the host and appropriate root or sudo permissions to edit the MongoDB configuration and restart the MongoDB service.

The exact steps may vary depending on your O/S and how you manage the MongoDB server, but the general process for resetting a password on a standalone MongoDB server would be:

Take a backup of the configuration file used by your used by your MongoDB service (typically /etc/mongod.conf for Linux installations).
Edit the current configuration file to set:

security.authorization to disabled
net.bindIp to localhost (you don't want to allow remote connections while access control is disabled)

Restart your MongoDB service using the relevant command for your O/S (typically sudo service mongod restart in Linux).
Login to mongod locally without auth using the mongo shell: mongo localhost/admin.
Reset the password for your admin user: db.changeUserPassword('myAdminUser','New&secur3').
Replace your MongoDB configuration file with the original that you backed up in the first step.
Restart the service: sudo service mongod restart.


Login failure when running a SSIS package from a SQL Server job I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection.

If I run the package directly on my machine, it runs successfully, however if I want to run it through a SQL Server job I have created on my local machine (where the destination database is), I'm getting a login failure error:

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login failed for user 'xxx\user1'.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot open database "MySybaseDatabase" requested by the login.

I know that the job runs as 'SQL Server Agent Service Account' that is in fact xxx\user1
 and it is different from a user has been set up in ODBC connection to access the source database, but don't know why the odbc connection login does not take the precedance and is used rather than the agent service account?

I read some posts in different sites, they were talking about setting up proxy, etc, but don't know if it is quite relevant to my issue.

The connection to the source database has been set up via ODBC, but as the step was failing, I added the userId and password to it to force SQL uses the userId I want rather than SQL Sever agant user, but it didn't help.

Do you know how we can fix this login issue?

Thank you.

I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.

EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".

Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.

The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>

Then save the package.

When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.


Backing Up an AG with Ola Hallengren I'm trying to understand the labyrinth that is the rules about AG backups.I've got Ola Hallengren's scripts installed on machines TST01/TST02.  02 is the preferred replica for backups. I have an AG with AdventureWorks in it and one database outside the AG - DBAutility.

I have the below in my SQL Agent job on TST01.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘User_Databases’, @CopyOnly = ‘Y’,@Directory = N’C:\MyDirectory\’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 168, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b

When I run the job on TST01 I get a backup of DBAUtility taken from TST01 and written to that machine.  I get no backup of AdventureWorks.  I'm taking this approach from a post from Brent Ozar.

Brent on Ola H and AG Backups

I know Ola has other parameters for AG backups, but I couldn't seem to get that to work as expected either.

I do this a lot with my Availability Group backups (using Ola's scripts). Since you have @CopyOnly = 'Y', and you have a Secondary server set as your preferred replica for backups, Ola's script will ignore this backup on your Primary and use the Secondary server for the FULL_COPY_ONLY backups (if the script is run there).

If you were to do away with the @CopyOnly = 'Y' parameter, the scripts would take a FULL backup on your Primary server (FULLs and DIFFs are not allowed on Secondaries).

Ola's scripts are smart when it comes to Availability Groups.  If you have a preferred secondary specified, his scripts will try to use it for any backups that don't HAVE to be done on the Primary.  Because of this, I wound up creating identical backup jobs across my Primary and Secondary servers, and each one runs whichever backups are appropriate based on the current status of each server.


oracle global index vs local index on a partitioned table I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this column. And can you explain why?

Thanks,

Sean Nguyen

Why would you want to index CREATED_TIME if you're already partitioning on CREATED_TIME?  If you are regularly querying the data looking for date ranges much smaller than your partition grain, that is, you're regularly querying for date ranges of a couple hours, you probably want to adjust your partitioning strategy to create partitions more frequently.  If you query recent data in smaller intervals than old data-- for example, you regularly aggregate by hour over the past day, aggregate by day over the past week, and aggregate by month over the past year-- you may want to merge smaller partitions together as they age (potentially in addition to compressing older partitions).

Additionally, are you sure that you would want to index CREATED_TIME rather than creating a materialized view that pre-aggregates the data by some smaller interval than the partition?  If you're aggregating data in various queries by hour, day, and month, for example, you'd generally be better served by creating a materialized view that pre-aggregated the data at the smallest grain (hour) and an Oracle dimension object that allowed query rewrite to use the materialized view to aggregate the hourly rows into daily or monthly results rather than trying to read all the data from the base table.


Can adding both Asc and Desc indexes improve performance? Was wondering if adding an ascending and descending index (on the same column) could allow SQL to search any faster by utilizing both indexes at the same time?

The leaf pages of the index form a doubly linked list with pointers to the next and previous pages. This means indexes can be scanned both forward and backwards.

There is a slight difference in that currently only scans in the forward direction can be parallelised but it is extremely unlikely to justify the cost of maintaining both just for that edge case.

  could it allow SQL to search any faster by utilizing both indexes at
  the same time?

No SQL Server won't split a single index access into a multiple index access in some sort of attempt to parallelise the work.


SQL Loop through Inserts and Update table I have a table (tblStagingHeader) containing the below two columns and example data.  It is a record of SQL table names that have been created as part of an SSIS\ETL load.

StageTable       Inserted
-------------------------
tblStage1          0
tblStage2          0
tblStage3          0

Using the below SQL task, I dynamically insert data from the staging tables (e.g. tblStage1 etc) into real tables.

My question is - after each successful insert, how do I loop through the tblStagingHeader and change the 'Inserted' field from a '0' to a '1' for each corresponding StageTable?

--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble]  as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd
--EXECUTE sp_executesql @cmd

I cannot take all credit - this is also linked to post:
SQL Insert via mappings table

You should be able to adjust the dynamic SQL to include the UPDATE for each staging table.

Also, based on your comment about the update only being executed when the insert is successful, I modified the code example to wrap each insert/update into a transaction which should be an all-or-nothing for that staging table.

UPDATE: Added TRY/CATCH logic

--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'BEGIN TRY BEGIN TRANSACTION;  insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble] + '; update StagingHeader set inserted = 1 where StageTable = ''' + [StageTble] + '' + '; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;'''  as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd

BEGIN TRY BEGIN TRANSACTION;  insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1; update StagingHeader set inserted = 1 where StageTable = 'tblStage1; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;';
BEGIN TRY BEGIN TRANSACTION;  insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2; update StagingHeader set inserted = 1 where StageTable = 'tblStage2; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;';


You can try to use the REPAIR TABLE myTable USE_FRM

"Probably" your .MYI file is missing or have corrupted headers

  This option tells MySQL not to trust the information in the .MYI file
  header and to re-create it using information from the .frm file

However before proceed check the documentation of your MySQL 5.1 version and make a backup copy of your .MYI,.frm, and .MYD file in order to avoid loss of data


sys.dm_exec_procedure_stats DMV contents contradict BOL I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached stored procedure plan, however there is one User Stored Procedure that appears twice in the table.

Both 'versions' of this stored procedure have wildly different execution times / counts.

Is this just due to different plans being cached and used? If so I didn't realise that sql server cached multiple plans for the same object_id, I thought it was sort of a one-in one-out deal? Plus if this is the case, it seems to contradict BOLS definition of the DMV?

Further to this, if there are two versions of the plan in use, how can I tell what plan is being used in what situations?

Anyone else encountered this?

Just working from Paul's comment. The key difference between the statement in Books Online and your interpretation:

  It says: ... one row per cached stored procedure plan.

  You read: ... one row per cached stored procedure.

You can check which plan attributes are different and thus leading to different copies of the plan by looking at the contents of the attributes DMV, ordering by attribute name. You should see at least one attribute where two rows for the same text have different values in the value column.

SELECT t.[text], pa.attribute, pa.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
WHERE LOWER(t.[text]) LIKE N'%create%procedure%procedure_name%'
  AND LOWER(t.[text]) NOT LIKE N'%sys.dm%'
  AND pa.is_cache_key = 1
ORDER BY t.[text], pa.attribute;

Typically, you will see differences in one of these two attributes:

set_options

This is due to runtime differences in settings like ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, etc. I talk about this in the following post:

Multiple Plans for an "Identical" Query

user_id

This isn't really the user running the query, but rather it is due to two different users having different default schemas (the value is actually schema_id), and at least one of them is calling the stored procedure without the schema prefix (SQL Server caches a different plan because the search path is different - it must check the default schema first). I talk about this in the above post as well as the following:

Bad habits to kick : avoiding the schema prefix

(Also, be sure you are looking at the overarching plan for the procedure, not two plans for two completely different statements within the stored procedure, e.g. WHERE p.objtype = N'Proc'.)


Best practices for connecting DBs that are in different geographical regions We are about to set up SQL Servers in different countries. We need to link them, but we don't have to have a direct link (as in linked server). In other words, they can be loosely coupled.

Is it better to connect them via a VPN and have them as linked servers, or use loose coupling via web services?

By "better", I am referring to stability.

VPNs are definitely more secure than web services.  As far as stability goes, as long as the VPN is set up correctly and the network doesn't go down in either location, VPNs are stable.


Firebird Configuration: firebird.conf Parameters Unit of Measurement Many of the parameters in the firebird.conf configuration file are integers and I'm unsure what unit of measurement (KB, Byte etc.) is being used. For example, if tweaking the TempCacheLimit param, is the value in kilobytes or bytes (or a different unit)?

The default firebird.conf is extensively documented in the file itself, and explicitly explains this, as an example, in Firebird 3:

# Please note, by default a number of the values are specified in **Bytes** (Not KB).
# You may add obvious abbreviations k, m and g in the end of a number to specify
# kilobytes, megabytes and gigabytes.

The support for k, m, g is new in Firebird 3, previous versions provided a conversion table.

And for values that expect a different unit, this is documented, for example:

# Maximum summary size of each user trace session's log files in MB.

Some values that expect bytes, repeat this in their specific documentation (but not all).

Not all numeric values are sizes in bytes, some represent a number of data pages, port numbers, seconds, booleans, or percentages. For each of these the unit is documented.


How to Join two tables with the join being regexp_replace Need your help, I have two tables “temp” and “temp 1”

The temp table I am deriving a column called numbers. I am
wondering how do you join table temp1 onto temp and joint on

SELECT regexp_replace(indicator,'[^0-9]') as Numbers
from temp

Select Number
From temp2

I tried the following however it’s not recognised

SELECT indicator,
       regexp_replace(indicator,'[^0-9]') as numbers
from temp
Left join temp2
    on temp.number= temp2.numbers

You can not directly use the alias in ON clause or in WHERE condition

So simply use your expression in ON clause:

SELECT t.indicator,
       regexp_replace(t.indicator,'[^0-9]') as numbers
from temp t
Left join temp2 t2
    on (regexp_replace(t.indicator,'[^0-9]') = t2.<Join column name>);

Cheers!!


Single row update using primary key affects many rows I'm doing a very simple datetime update in a table using its primary key:

update TABLE
set END_DATE = '2015-10-14 00:00:00'
where ID = 1165027;

I received the message (1 row(s) affected) 183 times!

When doing a select only one row is returned:

select * from TABLE where ID = 1165027;

What am I missing?

As answered by ypercube in a comment to the question

There are one or more trigger(s) on your table.

The multiple (1 row(s) affected) messages are generated not just by your single-row update statement, but also by other statements in the triggers fired from that original action.