Startup NOMOUNT and Startup MOUNT on dataguard oracle

From the documentation:

  startup_clauses

  The startup_clauses let you mount and open the database so that it is accessible to users.

  MOUNT Clause

  Use the MOUNT clause to mount the database. Do not use this clause when the database is already mounted.

  MOUNT STANDBY DATABASE

  You can specify MOUNT STANDBY DATABASE to mount a physical standby database. The keywords STANDBY DATABASE are optional, because Oracle Database determines automatically whether the database to be mounted is a primary or standby database. As soon as this statement executes, the standby instance can receive redo data from the primary instance.

startup mount is the same as startup nomount; alter database mount.

So there is no practical difference between the two approaches you showed, if the commands are run consecutively (i.e. you are not doing anything else while it is started but not mounted). However, including STANDBY DATABASE in the command clearly shows intent, so I think it's generally better to include it, particularly if you're scripting this as part of a switchover process.


To know  the current compatibility_level of your database you can run:

SELECT name,compatibility_level
FROM sys.databases
WHERE name = 'DatabaseName';

Result for my TEST database:

name    compatibility_level
TEST    140

If the compatibility level changes, for example with the command:

ALTER DATABASE TEST SET COMPATIBILITY_LEVEL = 120;

You could find the date and time that happened in the sql server error log:

Or reading the (current) error log file with xp_readerrorlog:

EXEC sp_readerrorlog 0, 1, 'COMPATIBILITY_LEVEL'

Result

Replace the first parameter of xp_readerrorlog to 1 to read the second error log file, 2 for the third file, ... .


Does restoring database clear buffer cache and stored proc cache

Yes. The buffer and plan cache associated with the database before restore are not related to the database post-restore, so they will be cleared. To all intents and purposes the restored database is a completely separate, unrelated entity. There is nothing in cache that can be reused.


Is there a "standard way" to log network messages (events) into a database?

It seems that the very same question has been asked (and answered) on stackoveflow:

https://stackoverflow.com/questions/2797592/best-practice-logging-events-general-and-changes-database


The MySQL documentation Encryption and Compression Functions page has various functions that can be used for hashing passwords. It also clearly states that:

  Note

  The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA2() instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

As for the other question, you can test at SQL-Fiddle that PASSWORD() (at least in versions 5.1, 5.5 and 5.6 that we can test at SQL-Fiddle) is implemented as applying SHA1() twice on the provided string:

SELECT
  SHA1(UNHEX(SHA1('password'))),
  PASSWORD('password');

Thanks should go to Rene Cannao who has digged into the source code and provided the answer in the blog post: PalominoDB.com : Hashing Algorithm in MySQL PASSWORD()

There is also this, older answer on StackOverflow which is relevant: Simulating MySql's password() encryption using .NET or MS SQL

If you want to confim with Linux CLI, just tested at Ubuntu:

ypercube@Zeus:~$    echo -n "password" | sha1sum |
      perl -ne 's/([0-9a-f]{2})/print chr hex $1/gie' | sha1sum
2470c0c06dee42fd1618bb99005adca2ec9d1e19  -


Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

I guess the implicit conversion causes index scan, if that is the case, You can use plan guides and force index seek without changing the source code.


This is probably done by your log rotation software. On my Ubuntu machine its defined in /etc/logrotate.d/mysql-server.

logrotate is run daily by a cron job. Take a look in /etc/crontab. In my case I have the following:

25 6    * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )

So daily jobs get run at 6:25AM.

I also have a file /etc/cron.daily/logrotate which configures log rotate as a daily job.

Most Linux installations would have something similar. If you're running on Windows I'm afraid I have no idea.


How can I check if the keys are enabled on a MyISAM table?

Just run an EXPLAIN on a SELECT statement that would normally use a key. If the keys are disabled, then the columns possible_keys, key, key_len, ref will return null.


Copy SQL table to other server identity becomes int

If you want to copy a DB from A to B, use the backup and restore commands. This might be easier for what you need to do.

If you want to export the tables: during the export process at the Select Source Tables and Views screen, click on the Edit Mappings button, then check the Enable identity insert check box.


You are doing a sum() which means you "lose" the original values -- they are aggregated.

By applying a where clause, you filter rows before the aggregation phase.

I suspect what you want is a having clause, something like:

select sourceIP, sum(sourceBytes)
from flows
group by sourceIP
having sum(sourceBytes) > 50
order by sum(sourceBytes) desc;

Having is like a where but applied to the aggregated results.

Note that you can also use a subquery and a where instead of having as follows:

select * from (
  select sourceIP, sum(sourceBytes) as sum_sourceBytes
  from flows
  group by sourceIP
) a
where sum_sourceBytes > 50
order by sum_sourceBytes desc;


Warning for missing statistics in execution plan

The warning does not always correspond to missing single-column statistics.

There is no easy way to precisely determine the exact statistic the optimizer went looking for and did not find in all cases, but it is almost always a multi-column statistic that would provide some correlation information across multiple equality predicates in the query.

Perhaps in your case it corresponds exactly with the predicates in the Filter following the scan where the warning appears.

It would be nice if SQL Server reported all columns for the 'missing statistic' warning, but that is not how it works today, unfortunately.

A certain amount of trial-and-error may be necessary to identify the multi-column statistic you need to create to remove the warning. Note that multi-column statistics cannot be created automatically. Despite the warning, you may or may not find the quality of the estimate improves even if the missing statistic is provided.


Remote connection showing up as from localhost on MariaDB 10.2

I would expect a connection to show as coming from localhost if a remote user was querying a table that has the federated, federatedX, connect or spider storage engines (or possible other similar storage engines), and the table was configured to connect as the root user to the local MariaDB instance rather than a remote one.

You can find the storage engine used for a particular table with:

SHOW TABLE STATUS WHERE Name='your_table_name';

You can also find tables using a particular storage engine with:

SHOW TABLE STATUS WHERE Engine='name_of_engine';

You could also do something like:

SHOW TABLE STATUS WHERE Engine IN ('FEDERATED', 'FEDERATEDX', 'CONNECT', 'SPIDER');


What differences are there with SQL Server 2017's packaging of `Binn` on Linux and Windows?

All tests done with a full install on Windows (except Machine Learning, and PolyBase) and a full install on Linux. So for starters on Windows you'll find these,

112K fdhost.exe
244K sqlceip.exe
2.7M DQSInstaller.exe
 32K DatabaseMail.exe
 40K BackupToUrl.exe
476K sqlservr.exe
 56K StretchCodeGen.exe
 60K fdlauncher.exe
 88K xpadsi.exe

While on Linux you'll find

1.1M launchpad.exe
120K bcp.exe
120K reg.exe
140K SqlDumper.exe
 16K calchash.exe
200K mtlogreader.exe
208K rep.exe
232K DTAEngine.exe
232K ldfreader.exe
244K sqlceip.exe
244K sqlcmd.exe
 28K rgs.exe
 32K DatabaseMail.exe
 36K PalProcessLauncher.exe
476K sqlservr.exe
 48K useraccounttool.exe
 72K XdbPackageLauncher.exe
 76K OSQL.exe

A quick overview,

sqlservr.exe is the actual database
sqlceip.exe is the spyware -- both installs get that of course

  Sql Server Telemetry Client

calchash.exe this is Microsofts copy of sha1sum.exe takes a filename.

  Microsoft Trust Calculate hash utility (US/Canada Only, Not for Export)

OSQL.exe was deprecated and removed on the Windows Install
bcp.exe is the Bulk Copy Program but only the Linux version has -G Azure Active Directory Authentication
rep.exe - think grep on XML.

  Rep  -  a fast, file-oriented search and replace program.

DTAEngine.exe seems to be a component of SMSS that is available on Linux without using SMSS.
reg.exe and rgs.exe both seem to be some kind of registry modifier. I assume reg.exe is the system, and rgs.exe is the hive file.
Though pe32+ XdbPackageLauncher.exe just crashes when I run them in Windows 10.
PalProcessLauncher.exe, and launchpad.exe are likely specific to the Drawbridge abstraction.
ldfreader.exe seems interesting and potentially useful,

LDF Reader version 14.0.3008.27 - Dump Microsoft SQL Server Transaction Log

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\ldfreader.exe <file> [options]

Options:

        -minVlf <int>           Min VLF to dump. Skips all prior VLFs. (short form: -m)
        -maxVlf <int>           Max VLF to dump. Skips all following VLFs. (short form: -M)
        -verbosity <int>        Verbosity level. Default is 1. (short form: -v)
        -summary                Summary mode. Only prints overall structure of LDF without individual records. (short form: -s)
        -lopFilter <string>     Dump only records with these LOP. (short form: -l)
        -force                  Force dumping in presence of corruption. (short form: -f)
        -dumpPageLogs           Dump Page Log Records? (short form: -p)
        -instructionFile        Treat file as instruction file. (short form: -i)
        -targetruntime <int>    The target run time in seconds (see below). (short form: -t)
        -certInfo <string>      Certificate information file for encrypted logs. (short form: -c)
        -vlfSize <int>          Fixed VLF Size in MB for Socrates Log (short form: -z)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing vlf information
extracted at dump time, containing fileID;offset;size followed by a
separator line and then mappings: fileID;full log file path

The -t parameter is an indication of how long to run the tool
when used without -i it is treated as a hard timeout on log iteration
when used with -i it is used as a guide for hueristics, a hard timeout of
five times the requested value will be used

launchpad.exe

Invalid arguments. The supported arguments are:
        -launcher:          Define the launcher dll's full path.
        -logPath:           Define the launchpad's base log path.
        -satelliteDllPath:  Define the sql satellite dll path for the satellites.
        -workingDir:        Define the launchpad and satellite process base working directory.
        -cleanupLog:        Whether to cleanup the log directory after every execution [0|1].
        -cleanupWorkingDir: Whether to cleanup the working directory after every execution [0|1].
        -pipeName:          Define the launchpad's name pipe's name.
        -timeout:           Define the default timeout in ms.
        -SqlInstanceName:   Define the SqlInstanceName as in MSSQLSERVER or blank for default or an instance name.
        -logFilesCount -    Tells the max number of launchpad error log file to keep across launchpad restarts.
Example:
Launchpad.exe  -launcher commonlauncher.dll -logPath C:\sqlServerBinaries\log -satelliteDllPath C:\sqlServerBinaries\binn\sqlsatellite.dll      -workingDir C:\sqlServerBinaries\data\ExtensibilityData -cleanupLog 0 -cleanupWorkingDir 0 -pipeName sqlsatellitelaunch -timeout 600000 -SqlInstanceName MSSQLSERVER

mtlogreader.exe, some utility for Hekaton logs

MtLog Reader - Dump Hekaton MtLog files

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\mtlogreader.exe <file> [options]

Options:

        -verbose <int>                 Verbosity (0-4) Default 1. (short form: -v)
        -buffio <bool>                 Buffer stdout (true/false) Default true (short form: -b)
        -keySecret1 <string>           The first secret key blob used for crypto keys (short form: -k1)
        -keySecret2 <string>           The Second secret key blob used for crypto keys (short form: -k2)
        -instructionFile               Treat file as instructionfile. (short form: -i)
        -targetruntimeinseconds <int>  The target run time in seconds (only valid with -i). (short form: -t)
        -skipchksum                    Skip checksum validation. (short form: -sc)
        -maxbsn <uint64>               Max BSN to dump. Default 0 (dump whole file). (short form: -m)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing checkpoint file
information extracted at dump time, containing the root file name followed by a
list of container directories (one per line).
 the -t parameter is an indication of how long to run the tool
when used with -i it is used to limit the processing of checkpoint files

useraccounttool.exe, I believe it's the use R account tool, as in the statistical language for Machine Learning that Microsoft brought

useraccounttool <command> [options]
Available Commands:
  /install                - Create the credentials required for R on SQL Server.
  /uninstall              - Cleanup the credentials required for R on SQL Server
Available Options:
  /instance:<name>        - [Optional (default: MSSQLSERVER)] The instance to use
  /sqlbinnpath:<path>     - [Required] The path to the sql binn directory.
  /userpoolsize:<size>    - [Optional (default: 20)] The number of user accounts to create.
[useraccounttool] <12/15/2017 5:23:26 PM> #3: Program exited with exit code 1


Total billions of rows and daily millions of row insert: What database system suits best

I've worked with a 30+ billion row monthly partitioned table with page compression and 10 years of history.  The table schema was fairly simple with a datetime2(2) clustered index and 3 non-clustered indexes on varchar columns and a couple of non-indexed columns.  Storage was about 2TB and it performed reasonably well.  SqlBulkCopy was used to insert about 15M rows continuously throughout the day as data was needed in near real time.

Based on this anecdote, I'm confident SQL Server could handle your expected volume with adequately sized hardware.  That being said, I completely agree with @DamianoVerzulli that your application is an excellent candidate for a less costly NoSQL solution due to your tolerance for delay.


Why is a GiST index used for filtering on non-leading column?

an index can only be used when we have predicates for the leading (or all) columns.

In Postgres, this rule of thumb is only somewhat applicable to (default) B-tree indexes. See:

Working of indexes in PostgreSQL

But mostly wrong for GiST indexes. The manual:

A multicolumn GiST index can be used with query conditions that
involve any subset of the index's columns. Conditions on additional
columns restrict the entries returned by the index, but the condition
on the first column is the most important one for determining how much
of the index needs to be scanned. A GiST index will be relatively
ineffective if its first column has only a few distinct values, even
if there are many distinct values in additional columns.

So, generally, you put columns (or expressions) with the most distinct values first in a GiST index.
Related:

PostgreSQL exclusion constraints in a bitemporal setting?

It's different for other index types. For (also very common) GIN indexes:

A multicolumn GIN index can be used with query conditions that involve
any subset of the index's columns. Unlike B-tree or GiST, index search
effectiveness is the same regardless of which index column(s) the
query conditions use.

The whole chapter of the manual is recommended reading.


MySQL auto increment problem with deleting rows / archive table

You can check the AUTO_INCREMENT value from INFORMATION_SCHEMA like this:

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='x';

you should also see the AUTO_INCREMENT with

SHOW CREATE TABLE mydb.x\G

SUGGESTION

If the table has barely 1000 rows, you could manually compress it and force to have MAX(id) each time. Suppose the table looks something like this:

USE mydb
CREATE TABLE x
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
);

Try doing OPTIMIZE TABLE manually as follows:

InnoDB or MyISAM tables with no non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

MyISAM tables with non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
ALTER TABLE x_new DISABLE KEYS;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x_new ENABLE KEYS;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

This should preserve all id's and assign AUTO_INCREMENT appropriately.

It should work just fine with MySQL Replication.

CAVEAT

If the ids are different for any reason between Master and Slave, blame MySQL (I mean blame Oracle) : http://dev.mysql.com/doc/refman/5.5/en/replication-features-auto-increment.html

If the aforementioned suggestion does not rectify this, there is only one thing left to do and it is guaranteed to work. What is it?

Run this on the Master Only:

mysqldump -uroot -p... --triggers mydb x > mydb_x.sql
mysql -uroot -p... -Dmydb < mydb_x.sql

With a table of 1000 rows, this should

go pretty quickly
replicate and produce an exact copy on the slave
restore auto_increment behavior to Master and Slave

Give it a Try !!!

UPDATE 2013-02-11 19:30 EDT

Let's assume that x and xhistory have identical layouts. Let's also add some columns:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT,
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE xhistory
(
    xid int(10) unsigned NOT NULL DEFAULT '0',
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Perhaps you could just use the REPLACE command. It mechanically operates as either an INSERT or UPDATE via DELETE and INSERT.

REPLACE INTO xhistory SELECT * FROM x;
TRUNCATE TABLE x;

Previously existing rows get deleted from xhistory and then inserted from x into xhistory. New rows are simply inserted.


Why not create ColumnStore index on every single table?

In SQL Server 2016, if you have & processing over million of rows, the only thing that would not allow you to use Columnstore Indexes will be the current technology limitations, such as Replication on CCI, CLR, CDC or CT over CCI and so on ...
Otherwise this should be a default way to approach any modern Data Warehouse.


How to take a database dump for selective set of values in few tables?

I posted an answer similar to this back on Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query?

You will have to employ the use of --lock-tables

From that post, the OP wanted all data connected with this query

select table1.id, table1.level, table2.name, table2.level
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

I recommended doing these mysqldumps

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql
mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql
mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

You then have to load them in a target DB server in reverse order:

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

Now, let's look at your queries

select * from foo where user_id = 1;
select * from bar where id in (select id from foo where user_id = 1)
select * from baz where id in (for all the above foos)

You would dump as follows:

dump foo where user_id=1
dump bar where foo_id in (select id from foo where user_id = 1)
dump baz where bar_id in (select id from bar where foo_id in (select id from foo where user_id = 1))

Here the dumps

WHERE_CLAUSE="user_id=1"
mysqldump -u... -p... --where="${WHERE_CLAUSE}" mydb foo > foo_subset.sql
WHERE_CLAUSE="foo_id in (select id from foo where user_id = 1)"
mysqldump -u... -p... --lock-all-tables --where="${WHERE_CLAUSE}" mydb bar > bar_subset.sql
WHERE_CLAUSE="bar_id in (select id from bar where foo_id in"
WHERE_CLAUSE="${WHERE_CLAUSE} (select id from foo where user_id = 1))"
mysqldump -u... -p... --lock-all-tables --where="${WHERE_CLAUSE}" mydb baz > baz_subset.sql

Here is the reload order

mysql -u... -p... -D newdb < baz_subset.sql
mysql -u... -p... -D newdb < bar_subset.sql
mysql -u... -p... -D newdb < foo_subset.sql

Give it a Try !!!


TokuDB table stats row count decreases to 1

We looked into it for a while and it looks like this is a regression in Percona. I wasn't yet able to reproduce it outside our own environment, but it started when we upgraded our servers from 5.6.22 to 5.6.28, persisted through upgrading to 5.6.30, and disappeared when we returned to 5.6.22.

The stats decreate was present on both master and slave servers and apparently depended on traffic the database served. I was not able to replicate this on any smaller data nor with any specific queries.


Data Flow Task Produces No Rows with Declare Statement

Well, even though I don't actually know the reason behind this behavior, I remember reading about this some time ago, and what to do to fix it. To make this work, you just need to add SET NOCOUNT ON as the first line of your sql command:

SET NOCOUNT ON
DECLARE @CurrentBlahDate DATETIME
SET @CurrentBlahDate = ( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' ))
SELECT
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(@CurrentBlahDate AS DATE)


Assuming current Postgres 9.4.

Since you are dealing with just ~ 20 names of products, it's largely irrelevant how you provide those to the query. An index would be a waste of time, Postgres is going to use a sequential scan anyway (judging from what you disclosed in the question).

You can use a temporary table, which lives for the duration of the session, not just transaction like you seem to assume. And you can even create indexes on temp tables, it just wouldn't help in this case.
Be sure to run ANALYZE on temp tables manually:

Are regular VACUUM ANALYZE still recommended under 9.1?

Or you can unnest a provided array literal just as well. But don't rely on the outdated trick of unnesting two arrays in the SELECT list in parallel. This is error prone. Move it to the from clause. I'd suggest:

SELECT
...
LEFT JOIN unnest('{name1,name2,name3}'::text[]) WITH ORDINALITY
  product_names (name, type_id) USING (type_id)
...

More:

Order of returned record with IN statement postgres

Assuming the column name type_id is unambiguous in the tables to the left.

If you don't actually have ascending type_id starting with 1 like your example suggests, consider the new variant of unnest() taking multiple array parameters:

SELECT
...
LEFT JOIN unnest('{4,7,9}'::int[], '{name1,name2,name3}'::text[])
  product_names (type_id, name) USING (type_id)
...

More:

Passing arrays to stored procedures in Postgres


There are 4 kinds of memory clerks in SQL Server.

Generic
Cache Store
Object Store
User store

memoryclerk_sqlgeneral is a general memory clerk responsible for a variety of non-specific sql activities whereas the memoryclerk_sqlbufferpool is the clerk dedicated solely to the buffer pool, since the buffer pool is a busy process.

Links:

SQLServer-DBA memory

MSDN Memory Clerks


Changing data type from mediumint to int without downtime

While Rolando has my vote, because he is right in your case, I want to answer your original question (for you in the future and for others) regarding percona toolkit:

pt-table-sync is not what you want. Think about pt-online-schema-change. While there are some hard corners, it works on RDS with the right configuration or minimal changes on the script. pt-table-sync will also fix the issues with rds in the next release. There are other alternatives for online schema changes, oak-toolkit and Facebook ones.

As an alternative, you need a spare slave that can suffer the lag for the time the ALTER is running and then doing a controlled switchover.


I'd answer with a question: Why do you need a table for a single row?
Regarding the design of the table, any table should have a primary key. I won't discuss that as it's the baseline of any design book. Regarding the way of designing that primary key, there are two schools of thought:

one that makes the PK as a separate column, usually self generated, like a GUID or and auto increment INT (in your case a separate uniqueidentifier column);
one that makes the PK as a column (or set of columns) internal to the table (in your case would be a username or email or SSN, whatever makes that user unique) that uniquely identify a record.

To what line you adhere, it's only a matter of taste.


Can text fields be not loaded for join memory purposes in MySQL?

Maybe.  That is, there is no setting to achieve such; however, rewriting the query can achieve such.
Please show us the query that is causing such.  Meanwhile, here are some possible examples:

SELECT * ... loads all the text columns.  If you don't need all the columns, spell out what you need instead of saying *.

SELECT big_text FROM t WHERE ... ORDER BY ... LIMIT 10 -- In some cases, the optimizer can focus on the 10 rows desired, and load big_text for only those.  For other cases, it loads big_text for 'all' the rows before discovering which 10 you needed.  This can usually be worked around.  But I need your SQL to talk you through it.

JOIN is another case where the previous item may or may not show its ugly head.


Difference between return next and return record

While RETURN NEXT is valid plpgsql syntax, RETURN RECORD is non-existent as such. It corresponds to how you declare the return type in the function header. Read the manual about how to return from a function.

In a function declaring RETURNS int you can:

RETURN 1;

For RETURNS SETOF int you can:

RETURN NEXT 1;

If you declare OUT parameters, you can just:

RETURN;
-- or do nothing: OUT params are returned at the end of the function anyway

Or if you combine OUT parameters with RETURNS SETOF record - effectively the same as RETURNS TABLE (...):

RETURN NEXT;

The current state of all OUT parameters is returned as next row.

Finally, to return a whole set of rows at once:

RETURN QUERY ...

record is a special data type. It's meaning depends on where you use it. Like I posted in my answer to your previous question:

Declare variable of table type in PL/pgSQL

The manual about the record type in PL/pgSQL.


Count Rows that exist before and not after and after but not before

In general you should include the scripts that generate your Table schemas and populates them with data. (Also for performance questions, you should include the execution plan too.) I couldn't do a lot of testing without real data, but I believe this query should improve the performance you're seeing:
DECLARE @my DATE = '2019-12-01'
DECLARE @nextMonthYear DATE = DATEADD(MONTH, 1, @my)
DECLARE @prevMonthYear DATE = DATEADD(MONTH, -1, @my)

SELECT CurrentMonth.Brand, SUM(CASE WHEN NextMonth.ID IS NULL THEN 1 ELSE 0 END) AS Churn, SUM(CASE WHEN PreviousMonth.ID IS NULL THEN 1 ELSE 0 END) AS New, CurrentMonth.MonthYear
FROM Table1 AS CurrentMonth
LEFT JOIN Table1 AS NextMonth
    ON NextMonth.MonthYear = @nextMonthYear
    AND CurrentMonth.ID = NextMonth.ID
LEFT JOIN Table1 AS PreviousMonth
    ON PreviousMonth.MonthYear = @prevMonthYear
    AND CurrentMonth.ID = PreviousMonth.ID
WHERE CurrentMonth.MonthYear = @my
GROUP BY CurrentMonth.Brand, CurrentMonth.MonthYear

This also is a lot simpler (both for the developer to read and for SQL Server to parse) then a bunch of sub-queries. You're likely right that the unneeded additional joining to the sub-queries is a source of your performance issues (but I can't verify that without seeing the execution plan).
What the above query does is use a LEFT JOIN to Table1 on itself twice (once for the PreviousMonth and once for the NextMonth) by ID to get all the records that match and don't match to the CurrentMonth. Then using the SUM() aggregate function with a CASE statement inside of it, we can sum up only the NULLs (cases where the ID of the CurrentMonth did not exist in the PreviousMonth and NextMonth) to get the counts for the New and Churn, grouped by the Brand and MonthYear of the CurrentMonth.


How to design a table for reconciliation

It depends on amount of data and how it will be used. You can have one table for with flag for intraday/COB datasets, then queries that would filter on those flags. After that you'll probably use EXCEPT to get unprocessed transactions. Then you need to keep this data for  2-5-7 years. There may be more requirements. It is OK if you have enough capacity and the only use for this table is one-off reconciliation and then storing data.

However, if you need to run reports on COB data, join it to dimensions, do more reconciliation, enrich with more attributes, send to downstream systems, then you would go for a separate table.


SQL Server Database backup regressed to historical date

Short answer: add ", INIT" to the end of your WITH statement to re-initialize the backup file on every backup. Per Microsoft:

INIT Specifies that all backup sets should be overwritten...NOINIT is the default.

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

I modified your code below to include the new change:

use My_database
go
checkpoint
go
use [My_database]
go
DBCC SHRINKFILE (N'my_db_log', 500)
go
backup database My_database
to disk = 'W:\...\My_database._dmp1.bak'
, disk = 'W:\...\My_database._dmp2.bak'
... ... ...
, disk = 'W:\...\My_database._dmp30.bak'
with stats = 10, INIT
go

p.s. I'd get rid of your SHRINKFILE in production because it fragments the database and it can cause interruptions in service because SHRINKFILE is usually slow. Any index rebuild/reorganize will probably bloat your database back to a similar size anyway.

And you could get rid of checkpoint as well as SQL Server issues an automatic checkpoint when a backup is taken to insure that the current state matches the log.


How to grant permissions for User defined type in Oracle

I think you need to either qualify type with schema when using client account (CO_ADMIN.DOCPARAMETERTYPE_TABLE, not just DOCPARAMETERTYPE_TABLE ) or create a public synonym for the type.


You are just not catching it at the right times, there are lots of operations that will use the transaction log.

When do you do maintenance if any (defragment indexes, CHECKDB, etc.)?  These all use the transaction log.
Do you have any jobs that load/modify data at night?  These operations can use the transaction log as well.

As Jon Siegel suggested, use the disk usage report to get the times when it's filling up and correlate that with other activity.


Cluster design: if I expect to insert data into several tables every week, is it a bad idea to cluster them?

I have yet to see a real world user scenario where the benefit (saving a bit of disk or I/O or block access) of using a cluster instead of just regular tables (or IOTs) with joins is so significant that it worths the hassle of dealing with it.
5-20 records per week: that is nothing. Paper and pencil can do that.
FYI: The data dictionary tables use a few clusters for identifiers. These identifiers never change. They are inserted, deleted, but never updated. In some environments, 5-20 records are inserted/deleted in a matter of seconds or minutes (due to dynamically creating and dropping objects) without causing any problem. So 5-20 records per week will not be a problem. The question is: do you really want to use something that is almost never used, that may not even improve performance noticeably (or even make it worse), but requires extra attention.


All I did was change

SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` =
NEW.`QuestionID`);

to

SELECT `Bounty` INTO bountyvar FROM `Questions` WHERE `QuestionID` = NEW.`QuestionID`;

and it started working... Why though xD

Some light can be shed on that from this question: apparently some versions of MySQL just don't like some variations of syntax. Oh well. I'm glad this is over. I'm tired.


Splitting Comma Separated string into columns by using REGEXP_SUBSTR

select
  regexp_substr(pq, '[^,]+', 1, 1) as po,
  regexp_substr(pq, '[^,]+', 1, 2) as qty
from
(
  select
    regexp_substr('po1,qty1,po2,qty2','[^,]+,[^,]+', 1, level) as pq
  from dual
    connect by regexp_substr('po1,qty1,po2,qty2','[^,]+,[^,]+', 1, level) is not null
);

PO         QTY
---------- ----------
po1        qty1
po2        qty2


Percona XtraDB Cluster (5.6.28-76.1-56-log) - random OOM events

Rate Per Second = RPS

Suggestions to consider for your AWS Parameters group

max_connections=300  # from 400 because max_used_connections was 197
innodb_flush_neighbors=2  # from 1 to reduce 95,455 innodb_buffer_pool_pages_dirty faster
innodb_io_capacity=900  # from 200 to use more of your IOPS available
read_buffer_size=262144  # from 2M to reduce handler_read_next RPS of 2,416,022
read_rnd_buffer_size=196608  # from 16M to reduce handler_read_rnd_next RPS of 965,300

to reduce MySQL RAM footprint and reduce CPU busy with performance improvement.

Please view my profile, Network profile for free downloadable Utility Scripts to improve performance and contact info.


SQL Server 2017 Distributed Availability Groups

After DC 1 is down, since your secondary availability group [distributedag] is asychronous and is in manual failover mode, it is still in secondary role, so you will need to connect to [distributedag] listener and execute command:

USE master
ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS

This will force your secondary AG at DC2 to become primary, and bring it into a read-write state


Uniquifier on Non-Unique Clustered Index vs Unique Clustered Index

One of the most important considerations for a clustered index in terms of performance is that it be ever increasing (or decreasing) and not something that will be changed (except possibly very rarely).  The clustered index represents the physical order of your table.  If you are constantly inserting into the middle of the index, or modifying the values of your clustered index then you will get bad page splits where SQL has to move part of the data from one page and move it into another one in order to make room for the new data.  These moves take time and cause fragmentation that degrades performance.

That being said the size of the clustered index, while important, is not your biggest concern.  I did some experiments with using date columns (with a uniqueifier) vs an int column and found that if my queries were date based I still got a big bump in performance.

My suggestion to you is that if ID1, ID2 and ID3 are ever increasing (and rarely change) then use that as your clustered index.  If not, and you still want to enforce uniqueness then either make them a non-clustered primary key or a non-clustered unique key.  If they are not ever increasing then you can consider your date column or just create a surrogate key for the table.  Use an int data type and make it an identity column.

If you do create a surrogate key then you can create a non-clustered index on ID1 to improve the performance on those queries assuming you didn't already create a unique index for it.  If you frequently need to return, say ID2 and DTM then you could INCLUDE those in your index to additionally improve your performance.


Azure SQL Database "Login failed for user" in application, but works fine in SSMS

We found that with contained databases / contained users you must specify:

GRANT CONNECT TO [YOUR_USER]

Otherwise CONNECT seems to be revoked by default. Once we made the above change, we could access the database.


SQL Server Agent - Service Failed to Start (Access is Denied)

I hope you are using Configuration Manager to change the Service account.Using the service.msc applet does not grant required permission

Refer the link : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/975c86b9-3622-418a-8e19-6720a51d7549/access-denied-starting-sql-server-agent?forum=sqlgetstarted

Other way could be to add the Service account in SQL Server as sysadmin and then configure it to run under the required service account.


Your query will give all unique combinations of product-id and name in the table, so if for example product id 123 has name "Pencil" in English and "Crayon" in French, you will get:

123 | Pencil
123 | Crayon

The results are deterministic in that all unique combinations that exist in your table, will be returned in that query. There isn't an option of which row will be picked, as the list of columns in the query determines what you want the results to be "unique" by. For example if you have product ID 123 with "Pencil" in both British English and American English, the query will return just the single one 123 - Pencil.

To get a list for language 1 (let's say I want it in English and that is language number 567):

select distinct product-id, name from mytable where lang-no = 567

Please clarify how the "fallback" language should work - do you have a specific language in which you know you will always have ALL products? Once you clarify that we can edit the answer accordingly.


Define referential integrity in your database, so that a bag cannot be removed so long an item exists, idem for the accounts and bags.  Then don't do the selects but provide exception handling on the deletes.  Your database will do the selects itself, to enforce the integrity.


db schema for a object with multiple foreign key tables

Simply include in your model a choice table for each type of choice that your
Your field choice_type_id is essentially a kind of partitioning attribute.  It indicates what type of child table is applicable.

So far you have three tables: MULTIPLE_CHOICE, BINARY_CHOICE, and FREE_TEXT (although you might consider binary choice to be a special case of multiple choice).

Each of these tables is a child of your QUESTION table, i.e. they all have a question_id foreign key.  The multiple choice table will have multiple records pointing to QUESTION whereas the other tables will have one - depending on how you model binary choices.

Note that this physical arrangement could be flattened out using a view to look like the one big table with sparse columns that you want to avoid.  The one big flat (sparse) table might be handy for reporting, but since you're worried about scale you might want to avoid a sparse table for physical storage.

Your user answer tables would be children of the various choice tables that intersect the USER and the appropriate choice, since each question is answered by multiple users.

For more about this approach, see my answers to this question and this question here on DBA.SE.


Right way to restart Oracle 12c Server with pluggable databases

I want to propose my findings as an answer. I have tested these procedures and they seem to work fine.

NOTE: The whole experiment was done on Windows Server 2012 R2, however based on my readings I would say most of it applies to Linux too.

Shutdown

Shutdown the Oracle Database Instance to ensure that everything close cleanly
https://docs.oracle.com/database/121/ADMQS/instance.htm#ADMQS052
http://www.dummies.com/how-to/content/how-to-stop-an-oracle-12c-database.html
Shutdown Windows Server

Restart

Start Windows Server
Start the Listener
https://docs.oracle.com/database/121/ADMQS/network.htm#ADMQS044
Start the Database instance
https://docs.oracle.com/database/121/ADMQS/instance.htm#ADMQS052

Remarks

Restarting the Windows Server should do everything automatically but there are a couple of remarks.

Starting the Oracle Services on windows is not the same as starting the database instance. However they are configured by default to behave that way. Oracle Administration Assistant for Windows can be used to determine what exactly happens with the database instance when the Oracle Services are restarted.
https://docs.oracle.com/database/121/NTQRF/admin.htm#i1006074
Linux can also be configured to start the Oracle Database automatically using scripts. Oracle Restart was recommend in previous versions to accomplish this but is now deprecated.
https://docs.oracle.com/database/121/UNXAR/strt_stp.htm#UNXAR150
http://docs.oracle.com/database/121/ADMIN/restart.htm#ADMIN12708
This step is no longer necessary, see below: If a pluggable database was configured during installation it should be manually started after restarting the oracle services.
https://web.archive.org/web/20180911225755/http://blog.contractoracle.com:80/2013/06/oracle-12c-need-to-start-container-and.html

Do note: The 12.1.0.2 patchset introduced the ability to preserve the startup state of PDBs through a CDB restart. This is done using the ALTER PLUGGABLE DATABASE command with the OPEN and SAVE STATE options.
http://oracle-base.com/articles/12c/multitenant-startup-and-shutdown-cdb-and-pdb-12cr1.php


Do SSDs reduce the usefulness of Databases

There are some things in a database that should be tweaked when you use SSDs. For instance, speaking for PostgreSQL you can adjust effective_io_concurrency, and random_page_cost. However, faster reads and faster random access isn't what a database does. It ensures

ACID (Atomicity, Consistency, Isolation, Durability)
Some form of concurrency control, MVCC (Multiversion concurrency control)
Standardized access for libraries (XQuery, or SQL)

He's just wrong about indexes. If the whole table can be read into ram, an index is still useful. Don't believe me? Let's do a thought experiment,

Imagine you have a table with one indexed column.

CREATE TABLE foobar ( id text PRIMARY KEY );

Imagine that there are 500 million rows in that table.
Imagine all 500 million rows are concatenated together into a file.

What's faster,

grep 'keyword' file
SELECT * FROM foobar WHERE id = 'keyword'

It's not just about where data is at, it's about how you order it and what operations you can do it. PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN and BRIN indexes (and Bloom through an extension). You'd be foolish to think that all of that math and functionality goes away because you have faster random access.


To use FILESTREAM, it has to be configured at the OS and instance level. At the OS level, FILESTREAM is enabled either during the installation of SQL Server 2008 or by running SQL Server Configuration Manager. Once FILESTREAM has been setup, you have to create a FILESTREAM filegroup. You then have to create a data container in the FILESTREAM filegroup. Once all these steps are done, you can create a table in your DB to use FILESTREAM.

(From your link) Here are the steps to configure FILESTREAM at the OS level, if not done during installation:

On the Start menu, point to All Programs, point to Microsoft SQL
Server 2008, point to Configuration Tools, and then click SQL Server
Configuration Manager.
In the list of services, right-click SQL Server Services, and then
click Open.
In the SQL Server Configuration Manager snap-in, locate the instance
of SQL Server on which you want to enable FILESTREAM.
Right-click the instance and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQLAccess check box.
If you want to read and write FILESTREAM data from Windows, click
Enable FILESTREAM for file I/O streaming access.
Enter the name of the Windows share in the Windows Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data. Click Apply.

Next configure the SQL instance for FILESTREAM access. Run the following query on the instance (SSMS):

EXEC sp_configure filestream_access_level, 2; -- Options are 0/1/2
GO
RECONFIGURE;
GO

Now add the FILESTREAM filegroup to your DB:

ALTER DATABASE Production ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO

ALTER DATABASE Production ADD FILE (
       NAME = FSGroup1File,
       FILENAME = 'F:\Production\FSDATA') -- Location for your data container
TO FILEGROUP FileStreamGroup1;
GO

You can now use FILESTREAM in your tables.


Moving SQL Server default instance's folders to another path on the same server

From View or Change the Default Locations for Data and Log Files:

  In Object Explorer, right-click a server and click Properties.
  In the left panel, click the Database settings page.
  In Database default locations, view the current default locations for new data files and new log files.
  To change a default location, enter a new default pathname in the Data or Log field, or click the browse button to find and select a pathname.

Moving system databases is a little more intense, and harder for some databases (like master) than others (like tempdb). I'd start here, and consider whether you really need to move master/model/msdb:

Move System Databases

I don't know that there's a supported way to move folders like Binn, FTData, etc. If you find ways to move those that seem to work, I'm pretty confident they'll break at some point. So if the object is really to remove all traces of SQL Server from the installation drive, it will be much cleaner to backup your databases, uninstall SQL Server, and start over. If the object is just to keep Binn and Data separate, I'd have to question the value in going through a lot of trouble for that.


SQL Server 2005 Replication Stops after a minute without an error

SQL Server Replication is a notorious error hider.

Usually if replication just stops, a problem occurred and there is on place where you can see what that problem is. All other places you might look at show no sign of trouble.

Places to include when hunting for errors:

The replication monitor
The job history of each job involved (Look at the history. Even the SQL Agent Monitor hides a replication problem every once in a while.)
The SQL Server error log
The windows event log

3 and 4 are rarely needed. 2 is usually all you need to look at to find the problem if 1 did not help.

There is a lot more information about potential errors and how to identify them in my replication stairway articles. Particularly in Level 10.


How can I update a single field in an array of embedded documents?

The answer from Kalhara is correct if you know the position of the embedded document in your array, however there is an alternative approach that can be used to update the first matching array element without knowing the position.

Assuming you update with a single array in your query criteria, the positional operator ($) can be used to refer to the matching array element:

db.students.update(
    // Match criteria
    {
        first_name: 'AA',
        last_name: 'BB',
        'cours_reussis.name_school': 'EPFC'
    },

    // Update first matching array element using the positional operator ($)
    {
        $set: {
            'cours_reussis.$.name_school': 'ENSA',
        }
    }
)


How can I reuse a table name after deletion (in DynamoDB)?

Answering my own question:

I waited about ten minutes and then the error went away. It appears that the name is in a cache which is periodically refreshed.


Postgresql: How to take incremental backup with wal-e

If you understand the base backup and WAL archives in Postgresql, then you would have understood the WAL-E.

Base Backup:

Its also called as the Hot backup, this will not interrupt any other transaction inside your PostgreSQL. Once you initiated the basebackup it'll wait for a checkpoint. If the checkpoint is entered, then it'll copy the entire data directory to a file. And these backups are the starting points for replication or POINT IN TIME recovery. Generally, basebackups are not consistent and may have corrupted data. Here the WAL files have come into the picture.

WAL:

WAL files will get all data changes which are happening inside the database. These changes are made in the WAL files first and then actual database after that. Thats why its called as Write Ahead Log.

What is consistent backup:

  consistent backup = basebackup + WAL Files.

Once the bacebackup may or may not have the committed data. So WAL files are will redo or undo the particular transaction to make the data consistent. WAL files must be applied after restoring the basebackup. This WAL files should be mentioned in restore_command in the recovery file.

WAL-E:

Its an OpenSource PostgreSQL database backup tool which will upload your backups to S3, GCP storage and Azure (+ some other places).
Here also it'll perform the basebackup first.

I'll explain this functions with S3.

backup-push:

This will initiate the basebackup and upload the backup file to s3.

backup-fetch:

Restore the basebackup from the S3. LATEST will restore the most recent backup.

wal-push:

It'll get the WAL file using the archive_command and store into the S3.

wal-fetch:

It'll restore your WAL files from S3.

Recovery:

If your primary database is corrupted and you want to restore it from WAL-E,

envdir /etc/wal-e.d/fetch-env wal-e               \
--s3-prefix=s3://some-bucket/directory/or/whatever  \
backup-fetch /var/lib/my/database LATEST

Now use recovery.conf file to restore your WAL files.

restore_command = 'envdir /etc/wal-e.d/env wal-e wal-fetch %f %p'
standby_mode = on

Or, if you want to stop the restore on a particular time, use

recovery_target_time = '2017-02-01 19:58:55'

Then start the PostgreSQL and it'll restore your databases to a consistent state.

How to backup using WAL-E:

Schedule a cronjob for this. This blog post explains this clearly.

Don't wrongly understand the WAL-E with Replication:

WAL-E will push the log files when Postgresql kicks out the WAL logs from the pg_XLOG folder. This will archive based on keep_wal_segments. So this will not restore upto the last commit, but it'll so upto last archived WAL file. Its mainly used for DR not for HA.


Will inserting a row keep it in the buffer cache

Since partitions have partition keys, when you insert the row, the partition key will need to be updated as well.  This works well with range, range-hash partitions from my experience.  So if your primary key is part of the partition, I would say it's not worth the effort to try to load the current day partition into memory, from my experience.  I'm not quite sure why you would want to have an entire partition in memory.  It seems that you would get far better performance, simply by just focusing on partition pruning and partition-wise joining, and parallelism.  Inserts are much faster on partition tables than heap tables anyways, and the buffer cache will be used as long was it has to search for the key.  If you want to test this out, run your process and monitor the X$BH view which gives you an object-by-object view of what's being located in the cache.


Named Parameters in MySQL Workbench Queries?

SET @given_id = 1;
SELECT name FROM table1 WHERE id = @given_id ;


restoring a table from sql file in postgresql through Windows Command prompt

If your dump file contains COPY statements, then it's a SQL dump which is restored using psql as explained in the manual.

psql dbname < your_dump_file.sql

More examples are in the manual.


How can I identify correlated subqueries that can not be rewritten as JOINs without using DISTINCT?

My question is how can one know if an SQL statement written using subqueries will have an equivalent one written using joins. I am quite comfortable, if in the answers, someone likes to use relational algebra notation.

Good question.

First Example Given

So let's look at the first query she gives..

SELECT GPA
FROM Student
WHERE sID in (SELECT sID from Apply where major = 'CS');

And, then let's look at a similar query with joins,

SELECT *
FROM student
JOIN apply ON (student.sid = apply.sid AND major = 'CS');

I've changed the columns in  in the first and second intentionally. "GPA" here, is specifically student.GPA.

If the following, then the correlated subquery can not be rewritten as a simple join,

the SELECT statement has aggregates WHERE gpa in (SELECT max(gpa)..) (if the join requires aggregation or grouping, you'll have to use a derived or virtual table: JOIN ( SELECT.. ) AS t.
if the JOIN condition is NOT UNIQUE in the above, we assume there is one student with a matching row having both a common sid and major=CS. The resulset can grow or shrink of this assumption fails.

You can easily test that..

SELECT sid, count(*)
FROM apply
WHERE major='CS'
GROUP BY sid
HAVING count(*) > 1;

This returns two rows, both sids would get duped in the join.

 sid | count
-----+-------
 123 |     2
 987 |     2

As an exercise in clarity, you could push the DISTINCT down yourself, and this can again be rewritten using a JOIN.

SELECT GPA
FROM student
JOIN (
  SELECT DISTINCT sid, major
  FROM apply
) AS apply
  ON (student.sid = apply.sid AND major = 'CS');

Or, even..

SELECT GPA
FROM student
JOIN (
  SELECT DISTINCT sid, major
  FROM apply
  WHERE major = 'CS'
) AS apply
  USING (sid);

Second Example Given

The second example,

SELECT sID, sName
FROM Student
WHERE sID IN (select sID from Apply where major = 'CS')
  AND sID NOT IN (select sID from Apply where major = 'EE');

Falls victim to the first example exactly. However, the NOT IN,

Doesn't matter for UNIQUE-ness because we're excluding results.
Moreover, rewriting the NOT IN as an anti-join (LEFT OUTER JOIN ... ON NULL) is likely faster.
It will handle any condition where apply.sid IS NULL more effectively, including the case where there are no major = 'EE'

Here is an example,

SELECT student.sID, sName
FROM Student
LEFT OUTER JOIN apply ON (student.sid = apply.sid AND major = 'EE')
WHERE student.sID IN (select sID from Apply where major = 'CS')
  AND apply.sid IS NULL;

In summary

Joins (including INNER JOINS) can increase or decrease the amount of rows in the result set.

If the JOIN condition is never true, the result on an INNER JOIN is pruned.
If the result is true more than once for the same row, the result is that the amount of rows returned increases.

But, joins join two tables. On t1 JOIN t2,

if the t1, and t2 have no duplicate rows then SELECT t1.*, t2.* should have no dupes. Something should be different.
If either t1 or t2 have duplicate rows then a JOIN that includes those row will result in duplicate output rows.
If you SELECT a subset of t1, or a subset of t2 (as you did above when you ONLY selected GPA) then it's possible to have something that appears to be a dupe, though it's not. Had you have selected student.GPA, apply.* you would have seen the reason for the duping.


MySql not displaying all columns in the result

Kind of looks like there's a CR (carriage return, ASCII 13) after the C in Trans_Type. Try specifying the column names, and put that first; it would disappear, but the other columns would be visible.

You can explicitly check for non-printing characters like this using the ORD() function. Let's say Trans_Type is a CHAR(3) (or NCHAR(3)):

SELECT ORD(SUBSTR(TransType,1,1)) as FirstChar
      ,ORD(SUBSTR(TransType,2,1)) as SecondChar
      ,ORD(SUBSTR(TransType,3,1)) as ThirdChar
  FROM Transactions
 WHERE cust_no=2 && Trans_type LIKE '%C%';

would show you the character codes for each character; check the appropriate code page to see what the characters actually are (as noted, for ASCII, a 13 represents a carriage return, which would move the cursor to the start of the current line in Windows at least).

If this is the problem, you should be able to use REPLACE(Trans_Type, @BadChar, '') to remove the offending character from your results. For ASCII and the carriage return character, @BadChar would actually be CHAR(13 using ascii). So, your query would be:

SELECT `cust_no`, `date`, `Category`, `Subcategory`, `Amount`
      ,REPLACE(Trans_Type, CHAR(13 USING ascii), '') as Trans_Type
      ,CASE WHEN Trans_type LIKE '%C%'
         THEN 'Credit Card'
         ELSE NULL
       END AS type
  FROM Transactions
 WHERE cust_no=2 && Trans_type LIKE '%C%';

Note: mostly untested, as I don't have your data.


Problem Installing 11.2.0 on RHEL 7 64 Bit

Cause

The security limits requirements were not set according the recommended values

The message
[client(15674)]CRS-10001:CRS-6021: No msg for has:crs-6021 [n][65536]
makes think about the value recommended for oracle hard nofile 65536 in /etc/security/limits.conf

Then these messages makes think that probably the other parameters in /etc/security/limits.conf were not set
[client(15669)]CRS-10001:CRS-6021: No msg for has:crs-6021 [l][unlimited]
[client(15672)]CRS-10001:CRS-6021: No msg for has:crs-6021 [c][unlimited]

Solution

Using root user make sure that /etc/security/limits.conf are set as follows
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240


SSDT Publish failing with an error without even running any statements

Turns out the GO statement segregated the successful code from the remainder, which indeed contains an erroneous statement (as per the error message), and so the error was thrown during syntax validation.

By taking the line number of the GO statement and adding the line number shown in the error message (which is really the offset in the batch), I found a bad MERGE statement, since double-clicking the error message in the SSDT publish results takes me to a random spot in the publish script.

So it was caused by statement batching, with syntax validation finally running on a deployment script (that is excluded from building with the SQL project).


Designing a data model for a survey; building the relationships

Table 1 - Questions

Table 2 - Users

Table 3 - UserResponses

If your answers could be more than just yes or no 1 or 0, you might also want a question_answers as a 4th table.  With this you could then use multiple choice with more than 2 choices.

UserResponses will be a many to many relationship or a junction table because a user might answer more than one question and a question will be answered by more than one user.

The UserResponses table will reference the Users table primary key and the Questions table primary key as foreign keys.
In the case where you want multiple choice, not just yes/no, the UserResponses table will also have a foreign key relationship to the Question_Answers table.


Can you grant an Active Directory Group rights on a linked server?

You can do by creating a role for the AD Group user and assigning permissions to it.

Create your Active Directory Group as a login in SQL Server as below :

USE master;
GO
CREATE LOGIN [COMPANYDOMAIN\ADGroup] FROM WINDOWS;
GO

Then create a user from the login in the database

USE DatabaseName;
GO
CREATE USER [COMPANYDOMAIN\ADGroup] FROM LOGIN [COMPANYDOMAIN\ADGroup];
GO

Now Grant appropriate permissions to the user

Note: Granting a server permission to a database role is not possible

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER to [COMPANYDOMAIN\ADGroup];


Getting column information from the MySQL command line

Add -e option:

  ·   --execute=statement, -e statement

  Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using Options
  on the Command
             Line”, for some examples. With this option, mysql does not use the history file.

mysql -uroot -pPassword1 -e "select column_name from information_schema.columns where table_schema = 'dbName' and table_name = 'tableName';"

Test:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;"
+--------------+
| column_name  |
+--------------+
| AnswerID     |
| QuestionID   |
| ReplyContent |
| id           |
| emp_id       |
| call_start   |
| call_end     |
| call_type    |
| idEmployee   |
| eName        |
+--------------+
root@onare:/home/onare#

EDIT 2:

If you want to avoid the query header (column_name), use --skip-column-name:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;" --skip-column-names
+--------------+
|     AnswerID |
|   QuestionID |
| ReplyContent |
|           id |
|       emp_id |
|   call_start |
|     call_end |
|    call_type |
|   idEmployee |
|        eName |
+--------------+
root@onare:/home/onare#


AlwaysON AG synchronous commit mode disconnected transaction log effect and mitigation

Can someone please confirm if this is correct?

Yes, you are 100% correct!

  Is there any way we can mitigate this, ie. ensuring there's no index maintenance during this window or switching to aynschronous commit mode?

The mitigation is not with the mode that the replicas run it, it's removing that replica from the AG.

  Does this pertain to my situation as described above and if so does this mean that the potential for the transaction to fill is mitigated by design?

Yes, that's what will happen if you change nothing. No, the transaction log will still fill up. Regardless of synchronous or asynchronous modes the primary must keep all log blocks that are required for the replica that is the furthest behind.

In your scenario, there are only two replicas a primary and a secondary. The secondary will be offline for a while. You've already correctly stated what will happen, the send queue will get larger as the replica is offline and stays that way. This happens no matter the mode.

The mitigation is to remove the replica that will be down for the extended period of time. This allows the primary to properly truncate the log with proper transaction log backups.

Once the secondary is back up, since it was kicked out of the AG all of the databases participating in that AG will be in a restoring state. To get back to a good steady state and bring the replica back into the AG, take the log backups that were run during the period the secondary replica was offline and apply them to the databases, leaving them in a restoring state (with no recovery). Once you get close, pause all transaction log backups on the primary and join the replica back into the AG. Resume the log backups on the primary.


MySQL error 1241: Operand should contain 1 column, INTERSECT two subqueries

You probably want to replace:

WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID
...
  ) ;

with:

WHERE cTitle='Database Management Systems'
  AND (S.StuName, S.StuID) IN (                -- this changed
SELECT S2.StuName, S2.StuID
...
  ) ;


SMS Suggesting covering all columns in an index?

SMS suggested adding a covered index to one of my tables that indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table.

The suggestions shown in SSMS are actually generated by the query optimizer while it searches for an efficient physical execution plan for the query. The suggestions are opportunistic and based only on a superficial analysis of the single query.

  To me this seems like it is moving the entire table into the index.

Copying rather than moving, but yes, an index that covers every column in the base table (either as a key or included column) is essentially a copy of the table's data sorted in a different way. Conceptually, this is the same as having multiple clustered indexes on a table.

  More generally is there any reason to cover every single column in a table?

It is quite unusual to do this, but there will always be very specific scenarios where it can make sense. An index that is a copy of the table data sorted in a different way will need to be maintained for every change to the base table, will consume more space on disk and in memory, and will generate more transaction log. For most applications, the costs outweigh the benefits.

  Adding the index results in times going from 70ms average to 5.8 ms including all columns and 7ms with just index and no covered columns (5 trials; Client statistics). So it seems covering all is faster.

A fully covering index means all the data needed for the query can come from that single b-tree structure. If a nonclustered index does not fully cover the query, the optimizer can choose to locate rows using the index then look up the remaining column data in the base table using the row locator stored in the nonclustered index. The extra step has a cost associated with it, which is proportional to the number of rows qualified by the index key search.

In most cases, the performance improvement offered by creating the nonclustered index on FK_FileID alone would be considered the best trade-off among the various considerations. That said, you may have very specific requirements that mean you are happy to go with the fully-covering option.


mysqldbcompare giving Compare table checksum FAIL error

It means that the simple approach (table checksum) failed, but that the rows are identical, which is a pass.  Since the row checksum takes a lot longer than the table checksum, you want to have table checksums pass as much as possible to avoid subsequent row checksums.


Changing the "default database" opton in login

You can use sys.server_principals to find default database and alter login to change it.

SELECT name,
       default_database_name,
       'alter login ['+name+'] with default_database=master;'
FROM sys.server_principals
WHERE default_database_name <> 'master';


There are a couple of ways you could model this database.  There is nothing wrong with the way you have it right now.  However, you could normalize the table structure something like:

CREATE TABLE Player (
   facebookId BIGINT NOT NULL,
   XP int NULL,
   PRIMARY KEY (facebookId)
)

CREATE TABLE Score (
    facebookId BIGINT NOT NULL FOREIGN KEY REFERENCES Player(facebookId),
    score INT NOT NULL,
    scoreDate2 DATETIME NOT NULL,
    PRIMARY KEY (score)
)

CREATE TABLE MedalType (
    medalType INT NOT NULL IDENTITY(1,1),
    medalName VARHCAR(10) NOT NULL,
    PRIMARY KEY (medalType)
)

CREATE TABLE PlayerMedal {
    medalId BIGINT NOT NULL IDENTITY(1,1),
    facebookId BIGINT NOT NULL FOREIGN KEY REFERENCES Player(facebookId),
    medalType INT NOT NULL FOREIGN KEY REFERENCES MedalType(medalType),
    PRIMARY KEY (medalId)
}

This type of structure would be a lot more flexible. To add your "platinum" medeal, you would add a row to the MedalType table.  You could then query the number of medals of that type that the player has from the PlayerMedal table.

For the All-Time High Score, use the Score table with the MAX() function to get their highest score.

Two things that I didn't quite model properly here are:

XP -- didn't know what this was, so wasn't sure where to place it.
Using facebookId as the primary key - Wouldn't do this in case you decide your system isn't going to be solely based on Facebook. Would use an unique key, with facebookId as another column in the table.


Database size is limited in the express edition only. In other editions the size is limited only to your storage subsystem restrictions. If it is not express edition, ask your client if it is the full transaction log file issue which he is interpreting as a database size limitation. In that case you either need to back up the log files or increase log file size or truncate the log file with dbcc, depending on your database environment, storage subsystem and recovery model.


Get the job scripts out of a MSDB Backup

Perhap simplest would be to do the following:

Install a new instance of SQL Server.
Shut down the SQL Server, delete the msdb data and log files.
Copy the old msdb data and log files to the same location as the files you deleted.
Bring up the new instance of SQL Server and script out the jobs.  (You can use SSMS Object Explorer Details to select all jobs and create one massive script, or do them one at a time.)
Uninstall the new instance.

There are also scripts, including this powershell script, but they use methods that talk to msdb:
http://www.johnsansom.com/script-sql-server-agent-jobs-using-powershell/


SSMS returns results immediately; ADO takes longer

As Pablo Montilla mentioned in a comment on the question, I should have added a code sample to indicate that I was using Dapper. (I have since added the sample.)

The solution was to pass an argument to Dapper's Query method to tell it not to buffer all of the results before letting me iterate over them. The default behavior of Dapper is to buffer the results first.

foreach (var result in connection.Query("SELECT * FROM dbo.my_view", buffered: false))
{
    // process each result in here
}


If you have a read-intensive query running on one NUMA node (in this case, 0), then it can experience a lower page life expectancy relative to other NUMA nodes.

That's totally normal.

To see what queries are running right now, you can use Adam Machanic's excellent sp_WhoIsActive. It's totally free. Some folks even run it every X minutes and log the data into a table so they can go back to see what was running at the time PLE nosedived.


How to exclude non-distinct rows in a query

You could use GROUP BY, HAVING and a Common Table Expression (CTE)  to obtain the data.
The GROUP BY and HAVING provides all those PbMIDs that only impacted a single company. If you need PbMIDs that impacted n companies you could change the HAVING to HAVING =n  where n is the required number of companies.
SELECT
    PbMID
FROM
    ChildTable
GROUP BY PbMId
HAVING COUNT(Company) =1;

This can then be combined with a CTE to produce the final query below.
WITH CTE_SingleInstance (PbMIDSinglInstance)
AS
(
    SELECT
        PbMID
    FROM
        ChildTable
    GROUP BY PbMId
    HAVING COUNT(Company) =1
)
SELECT
    ChildTable.ID,
    ChildTable.PbMId ,
    ChildTable.Company
FROM
    ChildTable CT
JOIN
    CTE_SingleInstance  CTES
ON
    CT.PbMId  = CTES.PbMIDSinglInstance;


Query to get yesterdays data only for a specific time interval

DECLARE @yesterday SMALLDATETIME;
SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE())));

SELECT ...
WHERE X.stage = 'Approval'
AND X.T_ExitTime >= DATEADD(HOUR, 6, @yesterday)
AND X.T_ExitTime < DATEADD(HOUR, 17, @yesterday)
ORDER BY x.T_ExitTime;

Don't think you want BETWEEN for datetime ranges, unless you really want to include 5:00 PM and not 5:00:00.0000001 PM.

What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries


SQLite is suitable for storing data locally in user's Machine.

On Database size limit, maximum database size of 140 terabytes.

On reading data SQLite support index so you can think of having indexes based on your select query.

you can read more about it from below link:

https://www.sqlite.org/limits.html

https://www.tutorialspoint.com/sqlite/sqlite_indexes.htm


I found a solution to this problem.
Create the following files:

RemoveSubscriptions.bat:

@echo OFF
SET Server=http://localhost/ReportServer_MSSQL_2008
SET ReportUrl=/test/reports/testreport
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe" -i RemoveSubscriptions.rss -s %Server% -e Mgmt2010 -v reportUrl=%ReportUrl% > %date%.log

RemoveSubscriptions.rss:

Public Sub Main()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    Dim subscriptions as Subscription() = rs.ListSubscriptions(reportUrl)
    For Each item As Subscription in subscriptions
        'Delete all subscriptions that are older then one month
        If (item.LastExecutedSpecified AndAlso item.LastExecuted.AddMonths(1) < Date.Now) Then
            Console.WriteLine("Removing subscription with id {0} from {1}", item.SubscriptionID, reportUrl)
            rs.DeleteSubscription(item.SubscriptionID)
            Console.WriteLine("{0}, {1}, {2}, {3}", item.SubscriptionID, item.Active, item.LastExecuted, item.LastExecutedSpecified)
        End If
    Next
End Sub

Now if you change the variables in the bat file and run the bat file it should remove all subscriptions that are older then 1 month this includes the actual sql job.

Warning: This will remove jobs that run more then once so if you have a report scheduled to run once a year it will be lost!


Get start time and end time of a trip in SQL Server


WHERE (    t.sevnum IS NOT NULL
       AND t.premnum = c.premnum
       AND t.sevnum = c.sevnum)
/* OR (t.sevnum IS NULL) */


This might be better placed on stack overflow.  This isn't something you'll accomplish with MySQL.  What you're talking about is referred to as 'stemming' in search.  Similar to matching different conjugations of a regular word e.g. run => runs ,ran.

I don't know of any such applications for proper names off hand but when you find one that will sit alongside your primary application to "normalize" the name before inserting the record into your database.  Mysql, sqlserver, mongo, whatever.  The DB technology is irrelevant as your task is out side the scope of storing data/documents.

Lucene would be a better tool for your task.  But I couldn't speak to it's prepackaged ability to stem names like you want.

Edit

After thinking about it I think I misspoke when I said Lucene would be a "better" approach in of itself for what you want.  My understanding is stemmers exist outside of core lucene and then proxy a search for "bob" into ("bob" or "robert") to feed into the lucene engine.


Is there a way to pass to execute immediate only once a variable but use it several times?

I don't know of any built-in way to accomplish this but you could use a derived table or a Common Table Expression (CTE or as Oracle likes to call them: subquery_factoring_clause), if your aim is to pass the parameters only once in the USING:

-- derived table
OPEN myCursor FOR
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3
    FROM (SELECT :myId AS id, :myPk AS pk FROM dual) my
      CROSS JOIN
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId

-- CTE
OPEN myCursor FOR
    WITH my AS
        (SELECT :myId AS id, :myPk AS pk FROM dual)
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3
    FROM my
      CROSS JOIN
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId


MySQL Enums have non-intuitive behaviour, why?

The SELECT in your example should be consistent with the INSERT with the way it uses quotes. The following works:

select b,b='1',b='0' from t;

Here's proof.

MySQL's enum documentation explains that an enum is an index of strings - every entry has both a numeric index and a string value. The SELECT query in your question is using the numeric index, and the one I've provided is using the strings as you inserted them.

Also noted in the documentation are that the first value always has an index of 1, and that the index 0 always resolves to an empty string (''). An enum of ('0','1','3','Banana','2') would therefore look like this:

Index = 'Value'
    0 = ''
    1 = '0'
    2 = '1'
    3 = '3'
    4 = 'Banana'
    5 = '2'

In the above example - as well as your original example - select b,b=1,b=0 from t; will look up the indexes 0 and 1, which resolve to the values '' and 0 respectively.

You will almost never need to use the indexes when working with enum data - make sure you use the string values, and put quotes around them every time.

As a general rule, don't use enum types to store numbers. The risk of confusion is extremely high, and if you really want to restrict the values, there are other appropriate tools, such as lookup tables with foreign keys.


Can you optimize a view with many UNION ALLs to external databases?

Using select * from vManyUnions where p = 1 will already avoid having to access the other tables, because of the contradiction optimisation.

If you look at the actual execution plan, you should see that only one of the external tables is actually accessed. Any others would have the NumberOfExecutions property set to zero.

You can see me demonstrate this in a talk that was recorded in 2009 for SQLBits, at http://bit.ly/Simplification


Creating a clustered index and want to add log files

If we add another log file while this is taking place, does the alter
  database add log file block command applications?

No, this won't be a "blocking" operation, but there may be a write pause while the space intended for the log file is zeroed out. Instant File Initialization doesn't apply to log files. If you're going to create one, don't make it very large.

The other thing is that log files aren't written to in parallel, the way multiple data files can be. There's no guarantee it'll be used immediately, unless the other log file is completely full.

Hope this helps!


64 Availability groups max on SQL STD : Failure when attempting to create a new NetBIOS interface while bringing a resource online

The Microsoft EnableNetBIOS documentation states:

  There is a limit of 64 NetBIOS devices in the cluster.

You should be able to just ignore this--disabling NetBIOS on clusters is recommended and SQL Server doesn't need it.  See Speeding Up Failover Tips-n-Tricks.


Locking a table and disabling the indexes hangs mysql - 100% CPU

I switched over to Mariadb and successfully ran the query.


The opposite of ordering by a column in SQL

Assuming that we have an equal amount of each value that you want to sort (in "maximum distance" style), you could do the following:
[1] Create a "mapping table", that holds all distinct values that need sorting.
[2] Select the values from the mapping table in the order (ascending/descending) as often needed for representing the stored values.

Proof of concept code:

create table t_ (
  id_ serial ,
  num_ integer
);

-- Stick some grouped values into the table.
do $$
begin
  for outer_ in 128 .. 256 loop
    for inner_ in 1 .. 10 loop
      insert into t_ (num_) values (outer_);
    end loop;
  end loop;
end$$;  -- 1290 rows

When you run a select,

select * from t_ order by id_;

you will see that the values in num_ are grouped together.
Then, create a "mapping" table, populate it with DISTINCT values - in (ascending) order - from the original table.

create table map_ (
  id_ serial ,
  num_ integer
);

insert into map_ (num_)
select distinct num_ from t_ order by num_;
-- 129 rows

select * from map_;

Then, JOIN them, and use MOD() for getting the correct values in the output.
The last select has a column for values in the original order, and another column for the new ("maximum distance") order.

select t_.id_ t_id_, map_.num_ "new ordering", t_.num_ "old ordering"
from map_ join t_
  on ( map_.id_ = mod(t_.id_ , ( select count(*) from map_ )))
order by t_.id_;

I know that this does not solve the problem if there are unequal amounts of "original" values.  However, I think it is maybe a step in the right direction.


Target/total memory is higher than max_server_memory

The max server memory setting does not represent how much memory you are willing to let the SQL engine use. From the Memory Management Architecture Guide:

Starting with SQL Server 2012 (11.x), SQL Server might allocate more
memory than the value specified in the max server memory setting. This
behavior may occur when the Total Server Memory (KB) value has already
reached the Target Server Memory (KB) setting (as specified by max
server memory). If there is insufficient contiguous free memory to
meet the demand of multi-page memory requests (more than 8 KB) because
of memory fragmentation, SQL Server can perform over-commitment
instead of rejecting the memory request.
As soon as this allocation is performed, the Resource Monitor
background task starts to signal all memory consumers to release the
allocated memory, and tries to bring the Total Server Memory (KB)
value below the Target Server Memory (KB) specification. Therefore,
SQL Server memory usage could briefly exceed the max server memory
setting. In this situation, the Total Server Memory (KB) performance
counter reading will exceed the max server memory and Target Server
Memory (KB) settings.
This behavior is typically observed during the following operations:

Large Columnstore index queries.

Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.

Backup operations that require large memory buffers.

Tracing operations that have to store large input parameters.


That's less that a gigabyte?  MySQL can handle a terabyte or more.  MySQL could handle 10 blobs in each of 10 million rows.  Or more.  "Blob" is not the issue.

As for "performance issues", that's a question of what indexes you have, what queries you have, etc.  Please provide more details:  SHOW CREATE TABLE; some sample queries; what type of data is involved (eg images); etc.


Update a column to same value for all rows in a group of the group by

Something like this, may be
with u (key, uuid) as (
select distinct on (key) key, gen_random_uuid() as uuid from test
)
update test t1 set uuid = (select u.uuid
  from u
  where u.key = t1.key)


SQL Server 2008 Full Text Index Never Seems to Complete

I finally found the cause of my problem!

I tried for months to track down the issue, but finally gave up, disabled automatic change tracking, just manually initiated incremental population and moved on with my life.

Meanwhile, there had been another nagging error that I was having trouble tracking down.  Periodically the website would throw a DB connection error:

  Cannot open database "XXXX" requested by the login. The login failed. Login failed for user 'XXXX'.

It turned out that both of these problems had the same solution.  All I had to do is turn off a database setting called Auto Close.  To do this, just right-click the database and click properties.  In the properties window select Options and set "Auto Close" to false.

As soon as I disabled Auto Close, my DB login issues went away and the automatic change tracking worked perfectly.

Thanks again for everyone's help.  I appreciate it!


SQL Server Profiler is available for SQL Server 2008 Dev Edition?

Yes, it is available in the installer for development edition. Without firing it up, I'm not sure if the installer has changed between 2008 and 2008R2 but in 2008 there are two options for installing client tools.

Management Tools (Basic) which installs SSMS, SQLCMD and Powershell support
Management Tools (Complete) adds Profiler, Database Tuning Advisor, Integration Services, Reporting Services.


Connect Error (2054) - mysql_old_password issue still not solved

PHP must be expecting to use the old MySQL authentication plugin algorithms. You do not have to restart mysql. Since old_passwords is a globally dynamic option, all you have to do is run the following:

SET GLOBAL old_passwords = 1;

In addition, please add this to /etc/my.cnf:

[mysqld]
old_passwords=1

to have future restarts retain this setting.

To further verify the need to do this, the next time you login to mysql, run this

SELECT LENGTH(password) password_length,COUNT(1) length_count
FROM mysql.user GROUP BY LENGTH(password);

If none of the passwords are of length 16, this may explain PHP's reluctance to login.

Sad to say, but the alternative would be to setup 16-character passwords, but you cannot reverse-engineer 41-character passwords. You would have to manually setup the 16-character passwords  using the original plain-text values.

For example, if root@localhost had 'helloworld' as the password, it would have convert it using the OLD_PASSWORD function. Here is a comparison:

mysql> select password('helloworld');
+-------------------------------------------+
| password('helloworld')                    |
+-------------------------------------------+
| *D35DB127DB631E6E27C6B75E8D376B04F64FAF83 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select old_password('helloworld');
+----------------------------+
| old_password('helloworld') |
+----------------------------+
| 6c755a9e66debe8a           |
+----------------------------+
1 row in set (0.00 sec)

mysql>

Once you activate old_passwords, to convert it you would have to do something like this:

UPDATE mysql.user
SET password = OLD_PASSWORD('helloworld')
WHERE password = PASSWORD('helloworld');
FLUSH PRIVILEGES;

UPDATE 2013-02-02 22:44 EDT

If changing the authentication style on the MySQL side has not worked for you at this point, I have some bad news for you. You stated earlier:

  I used php 5.2.9 and everything worked ok but lowering php is not a solution for me. I also have to mention that i dont have full access to DB's settings cause the client doesn't give me that full access.

You may need to downgrade PHP after all. If your hosting provider isn't in position or unwilling to adjust MySQL or give you a downgraded PHP, you will have to go with a hosting provider that will.

As an alternative you may want to look into Amazon EC2 when you can have full control of all software upgrades after running sudo -s. The links you mentioned before gave you what you needed to know. I gave you what you can do to accommodate the MySQL side. Do what you know has to be done to get this solved.


Can we configure local and azure servers in a WSFC for Always On Availability Group?

Yes, you can
I've had 2 local (on-premise) SQL Servers and 1 VM in Azure, configured in Availability Group of three servers


Why is the lease locked for a some full sql backups to Azure Blob using Ola Hallengren backup scripts?

Do you know what version of each you are using? How close are you watching this in real time? I'm hoping the below quotes and links with the questions above will help spur you towards the right direction here.

When backing up to or restoring from Windows Azure storage, SQL Server acquires an infinite lease in order to lock exclusive access to the blob. When the backup or restore process is successfully completed, the lease is released. If a backup or restore fails, the backup process attempts to clean up any invalid blob.

Reference

The Lease Blob operation establishes and manages a lock on a blob for write and delete operations. The lock duration can be 15 to 60 seconds, or can be infinite. In versions prior to 2012-02-12, the lock duration is 60 seconds.
Starting in version 2012-02-12, some behaviors of the Lease Blob operation differ from previous versions. For example, in previous versions of the Lease Blob operation you could renew a lease after releasing it. Starting in version 2012-02-12, this lease request will fail, while calls using older versions of Lease Blob still succeed. See the Changes to Lease Blob introduced in version 2012-02-12 section under Remarks for a list of changes to the behavior of this operation.

Reference


Database backup: NT AUTHORITY\\SYSTEM is not able to access the database

We had 2 instance and NB MSSQL Job was failing and we just added (NT AUTHORITY\SYSTEM) under sysadmin in security roles and restarted the Netbackup services. NB SQL Jobs Started working.


Remote Connection to SQL-Server for data extract

As @FutbolFan said, best approach is to use SSIS (which takes a bit more time) or SQL Server Import/Export Wizard (the feasible option, in your case).

Right Click on the Database in question ans Select Tasks -> Export Data (the wizard will open up)
Select The Database in question as your source (might have to switch between SQL 10.0 or 11.0 depending on your version.
Select Flat File as your destination.  You'll be able to customize your delimiters and text qualifiers here
Select the Object you want to export.  You can do this by using the Object selector, or by using a custom query.  Up to you.
Run the package for each table you want to export, verify your results.


How to call Oracle's stored procedure from DB2 stored procedure?

Depending on the DB2 platform, it may indeed be possible to call an Oracle (or other database) stored procedure from a DB2 stored procedure. With DB2 for Linux, Unix, and Windows the process of setting up communication with a foreign data source is called federation. You can read more about this in the manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.iis.fed.sysconfig.doc/topics/iiyfafsporc.html


How to insert a large amount of records to the database in a short time?

There are several options.  Here are the main ones that come to mind.

SSIS - SQL Server Integration Services: This is SQL Servers ETL tool and you can move data from pretty much any source to pretty much any destination and it can be quite fast.

BCP - Bulk Copy Program: This is a command line tool that comes with SQL Server.  It's very good at moving text files into SQL Server or you can export into a native format from one SQL Server and import using that same format into another SQL Server.  BCP is also very fast but a bit more limited than SSIS.

INSERT INTO tablename SELECT fieldlist FROM othertable: This assumes that you are moving from one SQL table to another on the same server.  This is the fastest but at 500k rows you are looking at a fairly big transaction or having to break it up like you said.

OPENROWSET: This is a SQL Server function that will let you pull data into a table from various formats.  It's also supposed to be fairly quick but I haven't worked with it much.

General note: Your primary key is mbr_id.  If this is something that is stored in your source data (as opposed to being an identity column) your insert will go MUCH faster if the source data is in mbr_id order.


MariaDB defragmenting with new option innodb-defragment=1

Myth-busters.

Defragmentation is a concept that mostly does not apply to InnoDB.  It is rarely worth the effort to use OPTIMIZE TABLE or other techniques to defrag.

There are several ways in which InnoDB fragments and/or has "free" space:

The rows in a block not filling up the block.
BTree blocks gravitate to 69% full.  If you will insert new rows at random spots, this is sort of the limit of the intra-block waste.
The blocks in an 'extent' not consuming the entire extent.
Undo/redo records temporarily consuming space.
Data and Indexes are each BTrees, with various 'free' space.

SHOW TABLE STATUS shows only part of the "free" space.  Furthermore, it will usually indicate 4/5/6/7MB as free for non-small tables because of reserving space in an 'extent' for future action.

Why defrag?

Defragmentation provides only a tiny benefit to performance; so this is rarely a valid reason.
If you are almost out of disk space, defragging may keep you out of trouble briefly.  But if the defrag action requires copying the table/tablespace, then you may not have room to do it.  Catch-22.  Well, OK, MariaDB's feature avoids the copy.  But will it shrink the size of the tablespace file, thereby giving space back to the OS?
InnoDB already does some defragmentation -- by combining adjacent somewhat-empty blocks automatically (under certain circumstances).

(Looking at TABLE STATUS provided)

It shows 0.9GB "data" but a huge 6.9GB "free".  (I assume that STATUS was taken after an OPTIMIZE?)  This can exist in multiple situations:

innodb_file_per_table = OFF, in which case, the "free" is how much free space is in ibdata1, and has nothing to do with the table in question.  (You say this was not your case.)
The table has a thousand PARTITIONs.  Each partition always shows some "free" space; it could add up to 6.9GB.  (One reason why PARTITIONing may not be wise to pursue.)
You recently DELETEd most of the table's rows.  This was probably a very slow process.  It would have been faster to create a new table, copy over the rows to keep, then rename to put the table in place.  And, guess what, this approach would have given you 'optimize' for free.

The defrag feature in MariaDB, according to the manual:

  Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.

So...

It does not take extra disk space while defragmenting.  This can be very important if you are too low on disk space.
It fails to give the space back to the OS, even for file_per_table.
It apparently does not prevent accesses to the table during the defragmenting.  (Or at worst does it in quick bursts.)


A connector table is the standard way to solve this sort of problem.

As you say in your question, if you have a TitleID column in the Authors table it restricts you to one Title per Author, unless you add a Title2ID, Title3ID, etc columns, which for someone like Stephen King might mean quite a few columns.....

Also, by having the  connector table you can deal with cases where a Title has more than one Author.


Lack of cached execution plans when using `IN` clause

1) Is there a recommended way to write these sorts of queries in order
  to bypass this limitation and take advantage of cached execution plans
  (other than using forced parameterization at a database level)?

Paul White explained it here with examples.

  2) How can I evaluate the impact that these queries are having on the
  overall performance of the database (e.g., how can I answer
  quantitatively whether or not it is worth my time to worry about the
  lack of cached execution plans)?

You need do some collection and aggregation for this one.

Once you compare the performance of your rewrite as explained in Paul's article you know the difference (in io, cpu) for one call + time for recompilation.
Multiply that by number of calls (of the changed query) in x period.
Add those numbers for all the query you rewrite.
Another approach is to benchmark you overall workload, make change to small number of queries and compare with baseline.
If moving in the right direction make more changes (provided workload did not change),otherwise go back to starting point. Evaluate one by one.


Hook to run code when Postgres DB launches

You have two methods of doing this,

Step back, your best bet is to use TEMPORARY TABLES

  TEMPORARY or TEMP
  If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

  The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated.

  Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated; see Compatibility.

Alternatively, you could rig it into your daemon that starts the server by,

Creating two pg_hba.confs,

One that denies external connections.
One that permits external conections.

Create two config files, one to each pg_hba.conf
Start the server with the config file that points to the pg_hba.conf that denies external connections.
Run the code you want.
Stop the server.
Run the server with the config file that points to the pg_hba.conf that permits external connections.

You could do a similar thing with postgresql.conf's listen_addresses parameter.