For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out?

In very simple terms:

In asynchronous mode, if the replica can't keep up with its primary then the log send queue will just increase indefinitely until the load is reduced.

In perfmon:
SQLServer:Database Replica Log Send Queue

Bascially the primary will keep humming along but the replicas will just end up further and further behind.

In synchronous mode, if the replica can't catch up, then you will see an increase in transaction delay on the primary. In other words the time the primary spends waiting on replicas to commit / harden a transaction.

In perfmon:
SQL Server Database Replica :Transaction Delay

Unless ofcourse the replica is offline, then there is no delay.

For more detailed reading on what causes latency in synchronous mode: https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/

@scismon makes a good point. The logs will fill up in async mode if the replica(s) can't keep up.

select log_reuse_wait_desc, * from sys.databases

log_reuse_wait_desc shows up with AVAILABILITY_REPLICA


The trigger does something, it sets the ERRLOG_CREATEDON column to the current time.

In Oracle this should roughly be:

create or replace trigger errlog_trigger
  before insert on error_log
  for each row
begin
  :new.errlog_createdon := current_timestamp;
end;
/


You wanted inly explained how the syntax works.
But as complete Query

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)

INSERT INTO #TempListings VALUES(13, 5000, 2, 1)
INSERT INTO #TempListings VALUES(14, 6000, 2, 1)
INSERT INTO #TempListings VALUES(15, 7000, 2, 1)

INSERT INTO #TempListings VALUES(16, 8000, 2, 2)
INSERT INTO #TempListings VALUES(17, 9000, 2, 2)
INSERT INTO #TempListings VALUES(18, 10000, 2, 2)
GO

WITH #myselect
 AS (SELECT
         BedroomsAvailable
         ,ListingId
         ,UnitTypeId
         ,Price,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)
         OVER (PARTITION BY UnitTypeId) AS MedianCont
    FROM #TempListings)
SELECT BedroomsAvailable
    ,COUNT(listingid) AS Count
    ,MIN(price) AS MinPrice
    ,MAX(price) AS MaxPrice
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation
    , MIN(MedianCont) MedianCont
    ,UnitTypeId
FROM #myselect
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId
GO

BedroomsAvailable | Count |  MinPrice |   MaxPrice | AveragePrice | StandardDeviation | MedianCont | UnitTypeId
----------------: | ----: | --------: | ---------: | -----------: | ----------------: | ---------: | ---------:
                1 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |       2000 |          1
                2 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |       2000 |          1
                1 |     6 | 1000.0000 |  7000.0000 |    4000.0000 |  2366.43191323985 |       4000 |          2
                2 |     6 | 1000.0000 | 10000.0000 |    5500.0000 |  3937.00393700591 |       4000 |          2

db<>fiddle here


Don't think about "not calling the update statement." If you have to check the existing value in the table, you have to read the row, whether you're going to update it or not. Then, if you have to update it, you have to read the row again first. How this could possibly be more efficient than just trying to update the row is beyond me.

You can shorthand all that is null/is not null syntax if you can pick a string that can't possibly be a valid name:

UPDATE dbo.students
  SET [Name] = @Name
  WHERE StudentId = @StudentId
  AND ( COALESCE(Name, N'한') <> COALESCE(@Name, N'한') );
  -----------------------^ obscure character that can't be a valid name

Otherwise:

UPDATE dbo.students
  SET [Name] = @Name
  WHERE StudentId = @StudentId
  AND
  (
        Name <> @Name
        OR ( Name IS NOT NULL AND @Name IS NULL )
        OR ( Name IS NULL AND @Name IS NOT NULL )
  );

If it really is a hard requirement to not fire the UDPATE if it will affect 0 rows, then you need to take a less efficient path and locate the row twice when the update is going to affect a row.

BEGIN TRANSACTION;

IF EXISTS
(
  SELECT 1 FROM dbo.students WITH (UPDLOCK, HOLDLOCK)
  WHERE StudentId = @StudentId
    AND
    (
      Name <> @Name
      OR ( Name IS NOT NULL AND @Name IS NULL )
      OR ( Name IS NULL AND @Name IS NOT NULL )
    )
)
BEGIN
  UPDATE dbo.students
  SET Name = @Name
  WHERE StudentId = @StudentId
    AND
    (
      Name <> @Name
      OR ( Name IS NOT NULL AND @Name IS NULL )
      OR ( Name IS NULL AND @Name IS NOT NULL )
    );
END

COMMIT TRANSACTION;


How do I write this cross Join in MySQL?

MySQL does not support WITH and does not use [] around identifiers (it uses backticks (`) instead). You will probably have to repeat the right SELECT ... UNION ... as a subquery for each join. Other than that Cross join is the default one if you do not specify any join conditions.

SELECT cr.num AS ControlRange,
       sb.num AS SensorBias,
       se.num AS SensorError,
       vd.num AS VDCurve,
       i_t.num AS INSHalfCurve,
       i_s.num AS INSSensCurve,
       exdex.num AS ExogDEXCurve,
       initg.num AS InitGlucose
FROM (SELECT 0 as num UNION ALL SELECT 1) cr
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2) initg
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) exdex
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) i_s
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) i_t
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) vd
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2) se
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) sb
ORDER BY ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose

http://sqlfiddle.com/#!9/9eecb7d/27829 (hope I did not do any mistake when rewriting it)

You could instead create tables Two, Three, Four and Five if you use it often, but there is soe overhead in actually storing it.


how to change the default date format in oracle

You generally don't want to rely on implicit conversions of strings to dates.  That only leads to pain and suffering since different users will have different date formats.  Either use ANSI date literals or use the to_date function with an explicit format mask

UPDATE bb_product
   SET salestart = date '2012-06-01',
       saleend   = date '2012-06-15',
       saleprice = 8
 WHERE idProduct = 6

or

UPDATE bb_product
   SET salestart = to_date('01-JUN-2012', 'DD-MON-YYYY'),
       saleend   = to_date('15-JUN-2012', 'DD-MON-YYYY'),
       saleprice = 8
 WHERE idProduct = 6


Next time please build an example like below.

create table dbo.Reviews
(
   Id int not null
  ,ReviewFromId int null
  ,ReviewToId int null
)
GO

insert into dbo.Reviews
(Id,ReviewFromId,ReviewToId)
values
 (1,null,2)
,(2,1,3)
,(3,2,null)
,(4,2,5)
,(5,4,6)
,(6,null,7)
,(7,6,8)
,(8,null,null)
GO

From start I can tell your problem is when two rows are reviewed from the same row (3 and 4)
so that row (2) can persist only one ReviewToId

Also you do needs two results, one for each column.

/* Wrong reviews from */
select r4.Id, r4.ReviewFromId, r2.Id, r2.ReviewToId
from dbo.Reviews r2
join dbo.Reviews r4 on r4.ReviewFromId = r2.Id
where r2.ReviewToId != r4.Id

/* Wrong reviews to */
select r5.Id,r5.ReviewToId,r6.Id,r6.ReviewFromId
from dbo.Reviews r5
join dbo.Reviews r6 on COALESCE(r5.ReviewToId,0) = r6.Id
where COALESCE(r6.ReviewFromId,0) != r5.Id

Results are:

Id          ReviewFromId Id          ReviewToId
----------- ------------ ----------- -----------
4           2            2           3

Id          ReviewToId  Id          ReviewFromId
----------- ----------- ----------- ------------
5           6           6           NULL
7           8           8           NULL


Keeping messages in a backup table after dequeue is completed

I see two options:

1) use DBMS_AQADM.ALTER_QUEUE to change the retention of the queue. This way, when you REMOVE a message, it still stays in the queue table with a status of PROCESSED. You can specify the retention period. The table remains fast even with these messages there. When you are done, you can either manually delete the rows or let the database clean it up when the retention period has expired.

2) I'm not sure I would recommend this, but I've seen people put a ROW-DELETE trigger on the queue table for this purpose.


How to know when Postgresql password is changed

You can't. PostgreSQL does not record the time at which user information was updated.

The internal table storing this info is pg_authid. The only timestamp column there is rolvaliduntil, which does not help you.

The best you can do is determine the transaction ID that changed that user the most recently. This might not be the same as the one that changed the password. You can get this with:

select xmin from pg_authid where rolname = 'theuser';

You can compare this to SELECT txid_current() to get a very rough idea of how long ago it might've been changed, in terms of the server's transaction rate. If your tx rate is very bursty this won't do you any good. It might also be cleared (set to FrozenXID) if transaction ID wrap-around has happened on a busy server. I do not recommend relying on this method for anything even remotely interesting.

You can't determine who performed the transaction that made the change.

I imagine that a patch to add a last-change timestamp to pg_authid would probably be accepted by the community if you were to submit one to PostgreSQL 9.5. Raise this on pgsql-hackers. You will need to know some C programming and be ready to read some documentation, or to pay a developer to write it for you.

For the problem you are working on it would be more typical to have a higher layer in the application keeping track of audit information like this, where users only change their passwords through the app.


create additional oracle 11g database in linux

Oracle Database XE Installation and Execution Restrictions

  Only one installation of Oracle Database XE can be performed on a
  single computer. This does not affect any existing installation or new
  installations of Oracle Database 11g Standard Edition, Oracle Database
  11g Standard Edition One, or Oracle Database 11g Enterprise Edition.
  In addition, users can run only one instance of the Oracle Database XE
  database on each individual computer. To run more than one Oracle
  Database server instance or install more than one copy of the database
  software, upgrade to Oracle Database 11g Standard Edition, Oracle
  Database 11g Standard Edition One, or Oracle Database 11g Enterprise
  Edition.


Slave_IO_Running: Connecting in Master-Slave Replication

I faced a similar error some days ago. I was able to solve that error based on some Google search results. Here are some of the suggestions that might hopefully help you too:

Check whether you created the replication user with required privileges. If not, create a new user on master with the following command:

Create user ‘repl’@’192.168.2.46’ identified by ‘replpwd’;
Grant replication slave on *.* to ‘repl’@’192.168.2.46’;

If you already created the required replication user, check the privileges of the replication user. You can check the privileges by using the following command:

show grants for repl;

Usually, this issue occurs because of the incorrect password. In that case you can reset the password on the slave by using the following command:

change master to master_password = 'rplpassword';

Also, check the for any mistakes in the given replication user and host IP address.

Since, you are able to ping the master server, it is most probably because of the above mentioned reason.


Dump all binary columns from a table as files

To save bytea to the disk server-side, it is necessary to be a database superuser. Normal users are not allowed to write to the filesystem.

Assuming superuser rights, the simplest way is to implement it as a function in one of the "untrusted" languages.

Example in pl/perlu:

CREATE FUNCTION bytea_to_file(bytea,text) RETURNS void AS $$
  open(my $fd, ">".$_[1]) or die $!;
  binmode($fd);
  print $fd decode_bytea($_[0]);
  close($fd);
$$ language plperlu;

Usage:

select bytea_to_file(bytea_column, concat('/path/to/destination/', id_column))
from tablename where...

If such languages are not available in your postgres environment, a  non-efficient version can be made in pl/pgsql. It's non efficient because it has to create a temporary large object and copy the entire data into it before exporting it as a file, and then purge the large object.

pl/pgsql version (inefficient)

CREATE FUNCTION bytea_to_file_with_lo(bytea,text) RETURNS void AS $$
declare
 o oid;
 fd integer;
 INV_WRITE int := 131072;
begin
 o:=lo_create(-1);
 fd:=lo_open(o, INV_WRITE);
 if (fd<0) then
   raise exception 'Failed to open large object %', o;
 end if;

 perform lowrite(fd, $1);
 if (lo_close(fd)<>0) then
   raise exception 'Failed to close large object %', o;
 end if;

 perform lo_export(o, $2);
 perform lo_unlink(o);
end;
$$ language plpgsql;

Usage: the same as pl/perlu version. You still need to be superuser, unless the superuser privileges for this function only are granted to other users (which in the present case would be only OK if 100% trusting the users, otherwise it's disastrous):

alter FUNCTION bytea_to_file_with_lo(bytea,text) SECURITY DEFINER;

psql client-side version (mildly inefficient)

When all you have at your disposal is a client-side psql interpreter, binary contents cannot be extracted directly (it boils down to psql being adverse to the '\0' byte), but an intermediate representation in hex or base64 can easily be obtained and post-processed outside of psql.

Example in shell, assuming existence of the base64 command from GNU coreutils:

$ psql -Atc "select encode(bytea_column,'base64') from tablename" | \
   base64 -d >/path/to/destination/filename

This outputs only one line and column at a time. Extracting another column at the same time (like an ID) would be significantly more difficult for the post-processing, so this simple example assumes some kind of outer loop going through the IDs that would have been obtained previously.


How can I enable logging for a single function in PostgreSQL?

I tried the approach you described and it did not work for me, too.  As log_min_duration_statement can be set only by a superuser, I also tried defining the function with SECURITY DEFINER, logged in as a superuser - to no avail.

My only idea left (for pl/pgsql functions only) is to save clock_timestamp() into a variable as the very first statement in the BEGIN block, then do a RAISE LOG as the last one (before the RETURN statement, if there is any), with the difference of the current clock_timestsmp() and the saved one.

To my understanding, the result of this may be somewhat smaller than the time logged by the normal logging - the time spent on entering and exiting the function will not be taken into account for sure.

(Note that with some helper functions you could also do this in SQL functions.)


What does MBRContains and MBRWithin test? What does "contains" and "within" mean?

These are DE-91M (Wikipedia) terms, and they were standardized with Simple Features

but, Wikipedia probably covers it best, with two definitions,

  a contains b: geometry b lies in a, and the interiors intersect.
  a contains b iff no points of b lie in the exterior of a, and at least one point of the interior of b lies in the interior of a

The within relationship is the inverse of contains, with one catch "polygons do not contain their boundary." Other than that, within(a,b) = contains(b,a). Though interestingly, linestrings do contain all the points along the linestrings, except the terminal points.

For more information see the Wikipedia,

https://en.wikipedia.org/wiki/DE-9IM


Set permissions on all objects for SQL users

Single line to give permissions at the schema level to a role

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

And a second line to add users to the role

EXEC sp_addrolemember 'SomeRole', 'whatever user'

And do this in model so all new databases inherit.

Reasons, you should set permissions once only:

A schema is a container for objects.
New objects inherit permissions from the schema
A role is container for users
New users are added to a role and inherit

As you've found, migrating or restoring a database can lose object permissions when assigned directly to users. So why put yourself in that position?

You can also CREATE LOGIN with a SID so it is the same on all your servers and you don't get orphaned users either.

If you'd asked the correct question we could have saved you some coding...

Personally and finally, I'd say this is bad practice on "need to have", not "blanket do anything"


Optimization: Moving variable declarations to the top of your procedure

No.
This either used to be true a long time ago (and no longer is, at least since SQL Server 2000), or it never was true and your DBA just confused his recommendation with the following one:

It is important to group together all DDL statements (like creating indexes) for temporary tables at the start of a stored procedure. By placing these DDL statements together unnecessary compilations due to schema change can be avoided.

You can find another explanation of the reasoning behind this recommendation on this page.
If we take a look at this Microsoft KB, we see that the cause of a stored procedure recompile can be one of the following (SQL Server 2005+):

Schema changed.
Statistics changed.
Recompile DNR.
Set option changed.
Temp table changed.
Remote rowset changed.
For browse perms changed.
Query notification environment changed.
MPI view changed.
Cursor options changed.
With recompile option.

Declaring a variable -- even a table variable (i.e. @table_variable) -- cannot trigger any of these events, obviously, because declaring a variable doesn't count as DDL. A variable (even a table variable) is a temporary object used exclusively for your T-SQL programming. That's why table variables get no statistics and are not bound by transactions.  Declaring a variable (table or not) cannot trigger a proc recompile.
Creating a temp table (i.e. #temp_table) or an index, however, is DDL that affects the physical definition of the database. Temp tables and indexes are "real" objects with statistics and transactional control, therefore creating them could fire any of events 1, 2, or 5 in the list above and thus trigger a proc recompile.


The Dark Theme is still under active development and the work-around to enable it should be used for testing only. If you are testing it, then check Microsoft Connect for the issues you find, if they have not been raised feel free to raise the issue yourself. You should expect problems with it until Microsoft enable it by default and as such I would recommend not using it until that point.


Split Backup of MSSQL VS Stripe Backup of Sybase ASE

Any speedup in a striped backup will come from using multiple IO channels.  If all the files are in the same storage location it won't be any faster.

Using multiple CPU cores to process a backup probably won't help, as backup is extremely simple.  And a single CPU core can saturate the storage subsystem with backup pages.

You can see this for yourself with any big database, by writing the backups to the nul device:

set statistics time on
go

print '

single backup'

backup database AdventureWorks
to disk = 'nul'
go

print '

striped backup'

backup database AdventureWorks
to disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul'

outputs

single backup

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Processed 651384 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
BACKUP DATABASE successfully processed 651386 pages in 2.837 seconds (1793.779 MB/sec).

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 2877 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

striped backup

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Processed 651384 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
BACKUP DATABASE successfully processed 651386 pages in 4.023 seconds (1264.964 MB/sec).

 SQL Server Execution Times:
   CPU time = 499 ms,  elapsed time = 4123 ms.


SQL Server 2005 Express in VMware causing very high CPU load

I found an article that might be of help.

Edit: The link above is now dead, but the Wayback Machine has a copy here.

The blog post states that

  Finally we have identified the root cause of the issue is due to the activity on the database causing heavy usage of MARS [Multiple Active Result Sets]

which was a feature introduced in SQL Server 2005 (ref: here).


Your function had a number of problems. Try this revised version:

CREATE OR REPLACE FUNCTION add_column(
  full_name   regclass
 ,column_name text
 ,data_type   regtype)
  RETURNS BOOLEAN AS
$func$
BEGIN

IF EXISTS (
      SELECT 1
      FROM   pg_attribute
      WHERE  attrelid = full_name
      AND    attname = column_name
      AND    attisdropped = FALSE) THEN

   RAISE NOTICE 'Column "%" % already exists in %.'
                ,column_name, data_type, full_name;
   RETURN FALSE;
END IF;

-- For Postgres 9.1+:
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s;'
                ,full_name, column_name, data_type);

-- For Postgres 8.4-:
-- EXECUTE 'ALTER TABLE '|| full_name
--      || ' ADD COLUMN ' || quote_ident(column_name) || ' ' || data_type;

RAISE NOTICE 'Column "%" % added to %.'
             ,column_name, data_type, full_name;
RETURN TRUE;

END
$func$ LANGUAGE plpgsql;

Tested with PostgreSQL 9.1.7.

You do not need dynamic SQL at all to check the catalog table(s) for existence of the column. Simplify that to a plain EXISTS expression.
It's regularly faster by two orders of magnitude to use the table pg_catalog.pg_attribute instead of the bloated view information_schema.columns.

information_schema is only good for SQL standard compliance and portability of code. Since you are writing 100 % Postgres-specific code in a plpgsql function, neither is relevant here. And the basic layout of pg_attribute is not going to change across major Postgres versions either.
format() was introduced with Postgres 9.1. Use this convenient function in modern Postgres. For version 8.4 and older use the commented alternative in the code.
Do not quote the language name plpgsql at the end! It's an identifier, not a value. Single quotes Will make the command fail when using upper case letters.
Generally, it is essential to know the difference between identifiers and values.
I replaced the two parameters schema_name text, table_name text with fullname regclass. This has a number of advantages and no disadvantage AFAICS.

Shorter code and simpler call.
It's simple to supply the schema name where needed and omit it where not.
Cast to regclass checks for existence of the table immediately and raises an exception if not. Also prevents SQLi effectively.
If you don't provide a schema-qualified name, the search_path is used automatically. If the table name is unambiguous given the current search_path the schema name is not printed either when converted to text.
When (automatically) converted to text, schema and / or table name are automatically double-quoted where needed. Voids the need for quote_ident(), escaping as identifier in format() or adding quotes in messages.

Similarly I use regtype for the data_type. Similar advantages. Read more about object identifier types in the manual.
Simpler RAISE syntax.
The concatenation operator in Postgres (and standard SQL) is ||. + is an extension of the standard in SQL Server that does not work with most other RDBMS.


MySQL multiple index columns have a full cardinality?

As @Johnakahot2use mentioned, indexes on fields with really low uniqueness (low cardinality) is not a bad thing. It's just fields with high uniqueness are generally more efficient when indexed and stored in a B-Tree.
That being said, you don't normally write queries around the cardinality of the fields in your table (usually that's not even a luxury), you write queries for the data that's needed to complete the objective at hand, and you try to index (and design your schema) in a way that offers best performance to those use cases.
In other words, if one of your use cases is to get all ip_address_str in a specific month and year, then it makes sense to create an index on month and year (in the order of which ever field is more unique). If your use case will always require filtering on item_id then adding that to the index would certainly help it to be more performant but it's not a requirement for the index to be of help either.
In conclusion, create indexes and design the schema appropriate for your common use cases, not the queries around your data's cardinality (unless it so happens that works for your use cases).


mysqldump -h source.machine db_name  |  mysql -h target.machine

Add -u and -p as appropriate to both.

If you need to change the db name, add the suitable arg to mysqldump to avoid including DROP DATABASE and CREATE DATABASE; plus add the new db_name on the mysql command.

If you need to copy "routines", more args are needed.


That blog is a bit old, so I don't know if everything is still accurate. In the Azure world things work very fast and information changes often.

Info from the main Azure site:

  The prices are per database per month!

  Database Size Price Per Database Per Month    Database Units (DU) that
  will appear on your Bill Greater than 10 GB to 50 GB  $45.954 for
  first 10 GB, $1.996 for each additional GB

  4.6 DU for first 10 GB
  0.2 DU for each additional GB

That means that if you have a single database that's under 50 GB (and higher than the previous category of 10 gb), you'll pay 125$ per month while the database is online.


How is it possible that this "sub-calculation" of the "main calculation" actually gives a HIGHER value?

Imagine you keep track of all your purchases, from chocolate bars to houses and cars, over your entire lifetime. Maybe you buy 5,000 chocolate bars at prices varying between $1 each and $2 each.  For an average price of, for the sake of this example, $1.47.  Then you buy a car, priced at $40,000.  Now, the average price of the chocolate bars hasn't changed, but if you add in the car to that set, and recalculate the average you'll be at an average of something like $2.26.  That skew of outsized members of a set is why statisticians occasionally employ standard deviation to get rid of outliers.  In this example, if you remove any purchase from the set that has a price higher than 3 standard deviations from the mean, you return the average of the set back down to $1.47.
Your question shows that have two different sets of numbers that you're combining into a single larger set, eating in a restaurant, and getting delivery.  Imagine this is the set of purchases made in a restaurant:
╔═════╗
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 189 ║
║ 134 ║
║ 190 ║
║ 144 ║
║ 157 ║
║ 102 ║
║ 186 ║
╚═════╝
The average for that set of purchases is $140.58
Now, lets take the set of purchases for delivery meals:
╔═════╗
║ 289 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 190 ║
║ 232 ║
║ 287 ║
║ 200 ║
║ 270 ║
║ 186 ║
╚═════╝
The average for that set is $239.27.
If, however, you combine the sets together, as in:
╔═════╗
║ 289 ║
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 134 ║
║ 190 ║
║ 232 ║
║ 144 ║
║ 157 ║
║ 287 ║
║ 102 ║
║ 200 ║
║ 270 ║
║ 186 ║
║ 289 ║
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 134 ║
║ 190 ║
║ 232 ║
║ 144 ║
║ 157 ║
║ 287 ║
║ 102 ║
║ 200 ║
║ 270 ║
║ 186 ║
╚═════╝
The average is $187.70, which as you can see is less than the average for eating out, and higher than the average for eating in a restaurant.


Skip running trigger during cascade delete?

Short of disabling the trigger before executing your DELETE statement (which would affect all sessions and would only be appropriate if you are doing an off-hours bulk delete), setting a package variable (or defining your own context and setting a context variable) that your trigger reads is likely the cleanest option without reworking the existing application.

When you have an action that generally needs to be done when a row in Y is deleted but not when Y is deleted because of the cascade delete, that is probably not an action that should be in a trigger in the first place.  It ought to be part of the stored procedure (or other API) that allows you do do "normal" deletes on Y.  Of course, that sort of refactoring may not be possible with a legacy application so you may be stuck with some of the less elegant workarounds.


In order to restore a differential backup, you need a database that is in "restoring" state. Which is to say that recovery can't have run yet. This is accomplished by adding the "norecovery" clause to your full backup restore. So, it goes a little something like this:

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE, NORECOVERY

--now you restore your differential backup


Should I place my Quartz Scheduler tables in a separate database?

My input is that it should be within the application database since it is part of the application. I would put them in a different schema however to keep them separate from the business tables but with only a couple tables, the table prefix should be sufficient too.


SQL Server 2000-2014 migration using 2008 Express as intermediary

You could use Express, but it does not have all features that the full versions do and cannot handle databases larger than 10GB each. Please note the 10 GB restriction applies to the total size of data files only (so, for example, log files do not count toward the limit).

If that does not fit your needs, you could use a trial version of SQL Server 2008 R2 (ISO Image, or VHD) which is good for 180 days. As Aaron has researched, the EULA for the trial does cover this activity. The EULA states,

  You may install and use any number of copies of the software on your devices. You may use the software only to demonstrate and internally evaluate it.
  You may not use the software in a live operating environment unless Microsoft permits you to do so under another agreement.

Which does apparently include the use of an evaluation copy as a stepping stone.

Either way, you should  use SQL Server 2008 R2 and not SQL Server 2008.


Yes, that advice remains valid.

A low level snapshot of a volume that does atomic snapshots is much like a plug-pull or server crash. When restored from the snapshot, PostgreSQL just does normal recovery where it replays the transaction logs.

It's a perfectly sensible way to take a backup, though I recommend also taking periodic dumps. Snapshot backups won't help you in the face of undetected filesystem corruption etc.


The datediff function resulted in an overflow. But should not

First: the expression convert(datetime,convert(char(19), getdate(),126)) is redundant (except for the GETDATE(), of course). So, just replace that with GETDATE().

We need something to test with. The error message is clear, you clearly have a value for @hbdatetime which is too old, so that the number of seconds compared to GETDATE() overflows an int.

You can calculate the approx earliest allowable date compared to GETDATE() using:

  SELECT DATEADD(SECOND, -2147483647, GETDATE())

When I run this, I get: 1950-09-17 07:36:00.107.

So, you want to search for rows in your HBData table where HBDateTime is earlier than that.

Alternatively, you can use DATEDIFF_BIG() instead of DATEDIFF(). It returns the number of seconds as bigint instead of int. It was introduced in SQL Server 2016.


As long as you recognize the tradeoffs you're making, there is nothing inherently "wrong" about introducing a MyISAM table into a primarily InnoDB database for the purposes of supporting full-text searches.

Tradeoffs:

No enforcement of referential integrity on MyISAM tables. Referential integrity may be enforced at the application level instead if this is critically important.
Database has a mix of InnoDB and MyISAM storage engines which may go unnoticed or cause confusion for future developers and DBAs. At worst, an enterprising dev or DBA may try to "improve" the schema by changing the MyISAM table to InnoDB, thereby breaking full-text searching the application.
MyISAM full text search requires MySQL-specific SQL syntax which will reduce the portability of your application.


Refresh materialize View fast on commit multiple table

Just use DBMS_MVIEW.EXPLAIN_MVIEW + MV_CAPABILITIES_TABLE to find why fast refresh is not possible on your mview.

Fast refresh is possible without a join condition.

Create the table for storing explain results:

SQL> @?/rdbms/admin/utlxmv

Table created.

Create tables + mview:

create table t1 (c1 number primary key, c2 varchar2(20 char));
create table t2 (c3 number primary key, c4 varchar2(20 char));
create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv1
refresh fast on commit
as select t1.c1, t1.c2, t2.c3, t2.c4,
t1.rowid as rt1, t2.rowid as rt2 from t1, t2;

Explain mview and see fast refresh capabilities:

SQL> exec dbms_mview.explain_mview('MV1');

PL/SQL procedure successfully completed.

SQL> select capability_name, possible, msgtxt from mv_capabilities_table
     where capability_name like 'REFRESH_FAST%'

CAPABILITY_NAME                          P MSGTXT
---------------------------------------- - ----------------------------------------
REFRESH_FAST                             Y
REFRESH_FAST_AFTER_INSERT                Y
REFRESH_FAST_AFTER_ONETAB_DML            Y
REFRESH_FAST_AFTER_ANY_DML               Y
REFRESH_FAST_PCT                         N PCT is not possible on any of the detail
                                            tables in the materialized view

PCT fast refresh is not possible because there are no partitions in this example.

Verify fast refresh:

SQL> select * from mv1;

no rows selected

SQL> insert into t1 select rownum, 'A' from dual connect by level <= 2;

2 rows created.

SQL> insert into t2 select rownum, 'A' from dual connect by level <= 2;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from mv1;

        C1 C2                           C3 C4                   RT1                RT2
---------- -------------------- ---------- -------------------- ------------------ ------------------
         1 A                             1 A                    AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAA
         1 A                             2 A                    AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAB
         2 A                             1 A                    AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAA
         2 A                             2 A                    AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAB

SQL> select last_refresh_type from dba_mviews where mview_name = 'MV1';

LAST_REF
--------
FAST


What is a connection in MySQL Workbench?

Are you familiar with "server versus client"?  Your program (Workbench, PHP, magento, etc) is the "client".  MySQL's program (mysqld) is the server.

If both are on the same computer, the connection can be made via a "socket" and you use "localhost", but no "port".

If they are on separate computers, the client needs to specify where the server is (via hostname or IP address) and port number (or use the default of 3306).

Database products other than MySQL have similar connection protocols.

More

The typical beginner installs WAMP or LAMP.  They contain Apache, MySQL/Workbench/phpmyadmin, and PHP/Perl/Python on Windows or Linux.  Everything you need for building and publishing, say, web pages.  In that context, everything is on the same computer.  Under the hood, there are a lot of moving parts.

As far as connecting, you simply follow the "socket" or "localhost" path.

Meanwhile, the "big guys" have thousands of servers -- some with MySQL, some with web servers (Apache+PHP or whatever), some with "clients" (with WordPress or whatever) and they talk over the network (hostname+port).  But you are not a "big guy" yet, so don't worry about this detail.


How to recover previous values in Mysql Table?

unless you have a delayed replica, the only way to recover your old values is to restore the table from a backup.  sorry.


What happens if an index build is cut half way through?

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size.

SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of data (rows, indexes) to the database before it will need to grow the file size again since that space is unused.

You can right click and select properties of the database in question in Management Studio to see how much space is free of its total size:

If you must shrink, you can read up on it at the MSDN article on how to Shrink a File.


Does postgres have any configuration similar to mysql innodb_thread_concurrency?

PostgreSQL doesn't control execution of its child processes explicitly. If I borrow your words, PostgreSQL delegates that task to the OS.

PostgreSQL allows multiple clients to connect simultaneously. Each client connects a child process (referred to as a backend process), and each running backend  process handles all queries issued by the connected client. It is a very simple and classical server-client type multi-process architecture.

(I add more information.)

PostgreSQL can control the maximum number of connected clients by the configuration parameter max_connections (in the same way as MySQL),
but does not control the activities of the running backend processes depending on the server activity.

In addition, some helper processes, such as autovacuum related processes and checkpointer,  can be controlled their activities by themselves depending on the server activity.


Is database partitioning for performance reasons meaningful in a virtualized environment?

Partition-elimination is an awefully good reason to still partition even in a virtual environment, if your queries are such that they can use take advantage of it.


How to implement a unique key for a long VARCHAR column in MySQL

Checking directly for uniqueness of a 5000-character string is going to be pretty expensive, no matter how you implement it.

I'd suggest computing a hash, storing that hash, and computing uniqueness on that hash. This is almost the same thing, but does introduce the opportunity for a hash collision, where two different values result in the same hash.

The likelihood of a collision will vary with the hashing algorithm: MD5 has a 1 in 2128 chance in a collision. SHA1, and SHA256 each provide better protection against collision, but are slower/more expensive.

For example: You could create a computed/virtual column with the MD5() function, then create a unique index on that column. This won't guarantee perfect uniqueness, but is likely unique enough for most purposes where you have a 5k chunk of text that you want to keep unique.

With hashing, if two different, unique strings have a hash collision, this would prevent that second unique value from being inserted. However, two identical strings will produce the same hash. So the problem with a weak hashing algorithm is that it would be too aggressive in preventing dupes when it shouldn't.


There are many articles already on performance tuning of INSERT or `BULK INSERT' queries. Main points:

Optimize hardware: RAID/SSD/Fusion IO etc.
Tune source file system and network: copy files locally, use JUMBO packets if reading over network
Find optimal batch size, minimum logging
Insert into table with no indexes, triggers, constraints etc.
Apply all calculations, lookups, indexes, compression etc after load or in chunks
After all data is ready AND main table is stored on same disks, then switch partition - usually it will be almost instant as it will be a metadata only operation. However there cases when it is not.


What is the recommended way to autostart a Postgres cluster using a nonstandard data directory?

Are You using the official packages[1], supported by the PGDG?

You can do 2 things:

edit the startup script file (or make a simbolic link) pointing to the new directory.
Edit the /etc/postgresql/[version]/[cluster]/pg_ctl.conf to point to the new directory;

Please, take a look a documentation from debian website[2] to see more details.

EDIT: Looks like what you are looking for it's the pg_createcluster[3] utility.

Hope it helps!

References:

http://apt.postgresql.org
https://wiki.debian.org/PostgreSql
http://manpages.ubuntu.com/manpages/trusty/en/man8/pg_createcluster.8.html


Trouble recovering data from corrupted PostgreSQL tables

The very first thing you should do when you encounter database corruption is to capture a filesystem copy of the data directory tree while PostgreSQL is not running and save it somewhere safe.

Once you have done that, if you do not have a backup which you can restore you might want to try pg_resetxlog.  Note that the documentation has this recommendation:

  You should immediately dump your data, run initdb, and reload. After
  reload, check for inconsistencies and repair as needed.

Read over the documentation page for pg_resetxlog until you understand the options and which might be best for your situation.

Finally, make sure you understand how the database was corrupted.

Usually this is due to bad hardware or drivers.
Sometimes it is due to usage of non-default PostgreSQL settings which allow DBAs to reduce reliability to improve performance.  Some shops with redundant servers find this a worthwhile trade-off, because losing one server in a large redundant server farm is no big deal.  You may hear this sort of configuration referred to as "DBAs running with scissors" and consists primarily of turning off fsync, full_page_writes, and synchronous_commit -- all of which are needed to ensure persistence of committed transactions.  The first two are required to prevent database corruption on a crash of the OS or hardware.  You should not turn these off unless it is OK to lose the entire PostgreSQL instance.
Rarely, people delete some integral part of their database, such as the pg_xlog or pg_clog subdirectory.
Even more rarely, there is a PostgreSQL bug which caused the corruption.  Because any report of such a bug is treated very seriously, they tend to get fixed very quickly and are generally encountered these days by people running outdated versions which are out of support.  Without knowing the exact version (the output from SELECT version();), it's hard to know whether you are vulnerable to a known, fixed bug.  Possibly relevant to this particular error message is whether you used pg_upgrade and exactly which version of that you used.


mongodb equivlant query to this sql group by

First, 2.3 is a development branch that was turned into 2.4, you should not be using it any longer - hopefully you mean 2.2, which is still supported and developed (though it too will soon be end of life as of writing this answer).

The group method you mention runs server side javascript and is not going to be fast, especially in 2.2 which used the old spidermonkey engine and takes an exclusive javascript lock.  Generally I would not recommend using it, especially with a sharded cluster, where it is unsupported.

Instead you should use the aggregation framework, which was added in 2.2, is improved in 2.4 and will be even better in 2.6.  The framework includes a $group operator, and and many others that you can use in a "pipeline" to achieve the results you want.  If that seems odd, then I recommend the pipeline explanation docs here - for anyone familiar with the Linux/Unix shell, the concept should be very familiar.

To do a group on your field you would do something like this (this would be more specific and easier to be specific if you had actually included a sample document and desired output):

db.mytbl.aggregate(
  [
    { $group : { _id : {date:"$date_col"} , count : { $sum : 1 } } }
  ]
)

You can find some more general examples here:

http://docs.mongodb.org/manual/applications/aggregation/


If you have the option of moving the data into another database I would begin looking at using a star schema.  Here is a website that might help you begin research

http://www.ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx

Star schemas are built specificly for the fast retrieval of large amounts of data and are very good at aggregating that data over time.  Plus having the data seperated from your OLTP database means that there are not as many draw backs to adding indexes.  What's more having your data layed out in a Star Schema sets you up to be for creating data cubes with SSAS in the future; although, you do not need a cube to interface with the data.


How to show number of tables for each database in Postgres?

You can use something like the following which will list every table (with their row counts) for every schema in the database:
select n.nspname as table_schema,
       c.relname as table_name,
       c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
      and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;

I believe you'll need to use dynamic SQL in order to run it in every database. Here's a walkthrough.


Handling Bus Error in Mongo Service

As per MongoDB documentation here It would also be helpful to run smartctl (part of smartmontools) to check for SMART hardware errors:

sudo  smartctl -a /dev/sdb

Even you can run the Linux fsck utility is used to check and repair Linux filesystems (ext2, ext3, ext4, etc.).

Depending on when was the last time a file system was checked, the system runs the fsck during boot time to check whether the filesystem is in consistent state. System administrator could also run it manually when there is a problem with the filesystems.

Make sure to execute the fsck on an unmounted file systems to avoid any data corruption issues.

For your further ref here and here


ORA-12541 error while connecting to oracle express edition 11g

TNS-12541: TNS:no listener

Error message is self-explanatory, the listener is not running, so start the listener with "lsnrctl start".
Since you are on Windows, it runs as a service, so you can start the service itself.


Deleting "child" records on delete (reverse foreign key constraint?)

I would use a delete trigger to remove the records from table C when a delete occurs on table a or b.

There is a question from SO where the answer gives you a nice example:

https://stackoverflow.com/questions/11818191/mysql-trigger-delete-from-table-after-delete

But to give you the rough idea from that answer:

CREATE TRIGGER table_c_delete AFTER DELETE on table_a
FOR EACH ROW
BEGIN
DELETE FROM table_c
    WHERE table_c.parent_id = old.id;
END


If you want to know who was an employee's manager back in 2015, you need a separate table for that; employee_id, employee_id_of_manager, start_date, end_date.

The relationship may be more complex; what if a manager takes paternity leave?  Or job-shares, so that two or more people are sitting at that desk in rotation?

Having said that, a separate table of relationship history AND a column of employee_id_of_manager in the employee table may be a useful design.  For the job share or paternity leave, it may be better that the "manager_id" doesn't represent one person, but, for instance, the id is "John" but the database also knows that "John" is on leave and "Andy" is the replacement for "John".  Or "Peter and Paul" is the replacement, a composite person whose distinct parts are "Peter" and "Paul".

If we forget about employees and managers, and make the question about generally tracking changes to data, then it does become just a generic question about preserving an audit trail and history of data.  Of course, in a Microsoft SQL database for instance, when you update the manager_id then any previous value is destroyed and can't be recovered (except by the FBI or CIA who have been tapping all your data anyway ever since "Operation Paperclip").  The data is gone, unless you have in place any of the systems you refer to, or you have enabled the other feature in SQL Server 2016 which does the same thing but I've forgotten what it's called.  "Backup"?  No, it wasn't that, but that works too......

You could run an overnight job which looks for data which has changed, and copies it to a history table then - actually, that sounds like "Replication".

Some data you'd want to protect this way, some you wouldn't, so, you could split the table into two tables, one of which gets the audit treatment.  And gets the "Everlasting Transaction Log" or "Infinite Undos" facility applied to it.

I would prefer to design the historic record keeping specifically, but, if your SQL Server has an automatic tool for it (and we've covered several, with less or more "automatic" involved), then you may as well use that... maybe.

Another condition though that may apply to holding people's personal data - you may be under a legal obligation to -not- keep data that is old and no longer relevant.  To destroy data that, in fact, you have no good reason to keep.  This won't apply if you ARE the FBI or the CIA, of course.

Otherwise, this is awkward if your data retention plan consists of a 50 year cellar of backup tapes, and the law says that you need Giant Magnetic Bridget to pay you a visit.  (Please look up Giant Magnetic Bridget on Google.  She's an interesting subject that I learned about recently.)

So... maybe plan for necessary selective deleting of old records.

You could encrypt, and throw away the key to make certain data unusable - but then someone cracks the encryption, inevitably.  So the data can be read again.
 There is another interesting subject called a "One Time Pad", but the authentic thing is expensive and impractical - although simple: you use a file of random bytes the same size as your database, and every data byte that you store is first transformed by "Exclusive Or" (xor) operation with the corresponding random byte.  To read the data out, perform the "Exclusive Or" again.  To "destroy" the stored data, destroy the random data bytes.  That code can't be broken... but your system may have other points of leakage in it.  But in order to "delete" your copy of the data and all its backups, it'll do.

Although, in Microsoft SQL Server, the bytes in the file that represent one employee's data aren't fixed in place - so you'd need to invent a way to do that.


If this is not needed to perform on each and every day but sometimes, I suggest the following procedure:

Open the source server in Management Studio. Right click the database. Choose "Tasks" and "Generate Scripts". In the wizard choose either "entire database" or "specific objects" (and choose your tables).
Click Next.
Click Advanced.
At the property "Check for existence" choose "TRUE".
At the property "Types of data to script" choose "SCHEMA AND DATA".
At the property "Script Drop and Create" choose "Script Drop and Create" (this (re-)creates the (all|selected) tables with the current structure).
Click OK.
Decide if you want a file created or just the script opened in a new window.
Click OK / OK.
Run the script in your destination server and you are done.


Disable autocommit for SQL Server connection in Oracle SQL Developer

SQL Server uses autocommit mode by default. This cannot be changed permanently.

There are two ways implicit transactions (non-autocommit) can be turned on:

At the server level such that new sessions use it by default, using sp_configure 'user options' -- this may or may not work depending on how SQL Developer was implemented.
For a session, using SET IMPLICIT_TRANSACTIONS ON.


Two-column index (pretty unequal columns) and database optimisation

You're right. The more selective column should come first unless it happens to be in a range condition.

Having type in the index will provide absolutely no benefits on the performance when you query for type='a'. It will however put some extra overhead on your indexes which may result in actual degradation of performance.

If id is always in the query with this cardinality I wouldn't bother having type in your index at all. Especially if id is unique.


Problem in MySQL cluster when using two clusters

I'm assuming from the diagram that this is not in fact MySQL Cluster (NDB) but rather the informal sense of the word "cluster."

This should be fine as long as you don't intend to write to M2 or S2, or if you do, that the writes are exclusively to schemas and tables that do not exist on M1.

There are three probable causes:

You didn't enable log_slave_updates on both M1 and M2.  Without this option, incoming events don't propagate outward.
You haven't given each server a unique server_id.
You're doing replication filtering (rarely a good idea) and its configuration is being interpreted differently than you expect.


Is possible do an online snapshot backup , without archive logs at Oracle Database?

Yes, you can do an online backup without archivelogs if you have a storage system or backup tool that can take a point in time snapshot that will be consistent across all your datafiles and control files.  It sounds like you have such a tool and are using it for cold backups.  If that is the case, your documentation will still need to change.  If there is a problem with the system and you need to restore the backup, starting up the restored database will be as though the power plug was pulled a the time of the backup, therefore crash recovery will likely be necessary.

Given that your documentation needs to change anyway and that you want zero downtime backups, you should probably re-think your resistance to archive logs.  It limits your recovery possibilities greatly.  For example, if due to a disk error you get a bad block in one of your datafiles, and you have older consistent copies of that datafile, you can recover it using archive logs.  With snapshots taken online you would probably end up doing lengthy extracts from multiple snapshots of the system (assuming you can tolerate the downtime) and would probably still loose data.


What happens when one of the filegroup is full within SQL server

Every table or index has at least one partition.  Each partition is stored on a single filegroup*.  So when that filegroup is full and none of its files can be expanded, any operation that needs to allocate additional space for a partition that is stored on that filegroup will fail.

*Except that a table can be configured to store its blob data on a separate database filegroup, or filesream filegroup.


Can I read a .bak file without restoring it?

After some more searching and a helpful link or two, I finally found that what I was looking for is Red Gate's SQL Virtual Restore.  Problem is, it apparently isn't around anymore. I found an informational link about it in Red Gate's site here. Great write up by Brant Fritchey on it from June 2011 here

One link I found had a comment that similar functionality is in SQL Compare Pro.  If it is, the advertising page for that product, doesn't mention that feature.


You've pretty much hit the nail on the head.

If the use command results in a change of default database, then the MySQL client branches into build_completion_hash().

Provided that the client was not started with the -A option, that function:

hashes all SQL commands;
executes show databases and hashes the results;
executes show tables and hashes the results; and
for each table, calls mysql_list_fields() and hashes the results.

So, in your case, it is making 582 separate database requests (one for each of steps 2 and 3; and 580 for step 4), looping over the results of each one.  Ouch.

Of course, it'd have been imminently more sensible to do (or at least first attempt to do) a single SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() but ours is not to ponder why…


How to daily copy selected data from MySQL and PostgreSQL servers to another PostgreSQL server

What you are looking for is called ETL (extract transform load) tool
https://en.wikipedia.org/wiki/Extract%2C_transform%2C_load

There is no FREE/open source pre-built tool that exist to do what you want when moving data from different databases  that I am aware of.  I have used the paid for ETL tools they just speed up the process coding/typing time.  The computer generated scripts  always require tweaking to get to work and speed them up.

here is list  i found... (no idea how valid this review is,  i do not use such tools)
https://suyati.com/blog/top-10-etl-tools-salesforce-data-migration/

What you have to do is write scripts that update the tables in sequence you want.

This script can be python, perl, or some other script that runs and moves the data from one server to another.

Another option is use FDW on the PG 11 server to copy the data from the MySQL and PG 9.6 server.  Using FDW means it can be done in pure SQL using simple Updates and Inserts.
https://www.postgresql.org/docs/current/postgres-fdw.html
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

To execute the  SQL script use PG Agent for Perl/Pytho/other script language use cron Job.


SQL Server: Can you join implicitly using key relationships?

You always have to specify the columns you are joining on, regardless of any foreign key relationships.

Why? Because otherwise you couldn't do a full outer join if a foreign key existed between the tables, which would lead to identical queries producing different results for non-obvious reasons.

Also, what if you explicitly join on a different field? Would SQL behave as if you had included both fields in the join statement? Would you have to somehow tell it to ignore the foreign-key relationship for this one query?

FYI, the term "implicit join" in SQL actually refers to something entirely different, a SELECT statement made using a comma instead of a JOIN keyword:

 SELECT *
 FROM table_A, table_B
 WHERE table_A.key = table_B.key

vs an "Explicit" join:

 SELECT *
 FROM table_A JOIN table_B
 ON table_A.key = table_B.key

(This "old style" of implicit joins still works, but is not recommended.)


MariaDB does not have a datatype of JSON.  Instead it gives you LONGTEXT.

Be aware that there are a lot of differences between MySQL and MariaDB in the area of JSON.  Furthermore, different versions of each have different levels of functions, etc.


Better way to select products that are related to ALL of the categories?

This might work for you, should perform better.

WITH CTE_ProductCount AS
(
    SELECT PC.ProductID
        , COUNT(PC.CategoryID) AS ProductCount
    FROM ProductCategoryRelation PC
    WHERE PC.CategoryID IN (1,2,3)
    GROUP BY PC.ProductID
    HAVING COUNT(PC.CategoryID) = 3
)
SELECT *
FROM Product
    INNER JOIN CTE_ProductCount PC ON PC.ProductID = Product.ProductID;


I am designing a database for a game and the only option I can see a multiple table solution. Is there an option that I'm not seeing?

You want to store an undefinied number of items in a list, for this you need a new table or cleaner two tables.

you already have the first table called items, each has an item_id.

Then one table holds all the references to the collections, so best case only a new collection_id for every of the collection.

And the important one is the MxN relation table between items and collections, let's call it collection_items, holding in each row one item_id and one collection_id.

If you want to have multiple same items in one collection, and only want to know how many, add a count field.

But if the relations need to be distinguishable, add a row for every relation and an extra collection_item_id.


What does the base64 BINLOG statements in mysqlbinlog output mean?

From the MySQL Documentation

  •--base64-output[=value]   This option determines when events should
  be displayed encoded as base-64 strings using BINLOG statements. The
  option has these permissible values (not case sensitive):

  ■ AUTO ("automatic") or UNSPEC ("unspecified") displays BINLOG
  statements automatically when necessary (that is, for format
  description events and row events). If no --base64-output option is
  given, the effect is the same as --base64-output=AUTO.

  Note

  Automatic BINLOG display is the only safe behavior if you intend to
  use the output of mysqlbinlog to re-execute binary log file contents.
  The other option values are intended only for debugging or testing
  purposes because they may produce output that does not include all
  events in executable form.

  ■ ALWAYS displays BINLOG statements whenever possible. If the
  --base64-output option is given without a value, the effect is the same as --base64-output=ALWAYS.

  Note

  Changes to replication in MySQL 5.6 make output generated by
  this option unusable, so ALWAYS is deprecated as of MySQL 5.5.8 and
  will be an invalid value in MySQL 5.6

  ■ NEVER causes BINLOG statements not to be displayed. mysqlbinlog
  exits with an error if a row event is found that must be displayed
  using BINLOG.

  ■ DECODE-ROWS specifies to mysqlbinlog that you intend for row events
  to be decoded and displayed as commented SQL statements by also
  specifying the --verbose option. Like NEVER, DECODE-ROWS suppresses
  display of BINLOG statements, but unlike NEVER, it does not exit with
  an error if a row event is found.

  For examples that show the effect of --base64-output and --verbose on
  row event output, see Section 4.6.7.2, “mysqlbinlog Row Event
  Display”.

CAVEAT

Please keep in mind that binary logs are not backward compatible.

In ServerFault. someone thought that a master's binary log was corrupt, not realizing that binary logs have different start points. I addressed by explaining the following:

An empty binary log's filesize is

for MySQL 5.5, 107
for MySQL 5.1, 106
pre-MySQL 5.1, 98

As you already pointed out, Replication works from a 5.0 Master to a 5.5 Slave, not the other way around.


SQL Failover Clustering with Availability Groups: Single or Multiple Databases

Are arrangements below, only possible if 1 database is on the server, or could I have multiple databases below in arrangement?

You can have as many databases as your hardware will support which depends on workload, server size, and other general infrastructure items such as latency and bandwidth of the connections.

I've witnessed AGs that are ~20TB in size across 4 databases, AGs that had over 1,100 databases, and all values in between. Completely depends on your workload, hardware, and what you'd like to accomplish.


Possible Sign That A Database Is Corrupt?

Normally a few events of 823 and 824 are warnings that there is possible corruption on the database, also if you have backups of this database that are failing and or jobs on it that fail to complete.


mysql error connecting to the server did not ask for password during the installation process

It happens to me several times, if you want to solve this download the full package installer. The second installer from the link you posted. I think that will save you from unnecessary headache.
I hope I’m able to help?


Setting NLS_LENGTH_SEMANTICS for session via trigger

It is possible.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 29 08:29:35 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             343933032 bytes
Database Buffers          721420288 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> create user sapient identified by sapient;

User created.

SQL> grant create session to sapient;

Grant succeeded.

SQL> show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE

SQL> create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
    IF ( user like 'SAPIENT%' ) THEN
        execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
    END IF;
END;  2    3    4    5    6    7
  8  /

Trigger created.

SQL> grant select_catalog_role to sapient;

Grant succeeded.
SQL> connect sapient/sapient
Connected.
SQL> show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL>


A numeric column without a value is null:

select *
from the_table
where column_numeric is null;

There is no equivalent concept of an "empty string" for numbers.


MySQL Shell dump utility - maximum possible chunk size?

The max value is innodb_buffer_pool_size / innodb_buffer_pool_instances or some integral fraction of that.
But why bother changing the setting?  It should not make much difference.
Reference, and more discussion:  https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size


How to handle divide by zero in GENERATED columns in MySQL

There are three ways to handle DIVIDE BY ZERO error in your case :

1) USING IF() :

ALTER TABLE report_user
modify avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/if(trans_out_count,0,1))) STORED;

2) USING NULLIF() :

ALTER TABLE report_user
modify avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/nullif(trans_out_count,1))) STORED;

3) USING GREATEST() :

ALTER TABLE report_user
MODIFY avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/greatest(trans_out_count,1))) STORED;


Is there an efficient way to see the cause for "String or binary data would be truncated"?

There has been no change. SQL Server 2017 still offers up the same vague error message and does not provide any mechanism to discover the offending row/column.

This Connect item had over 1,600 votes when Connect was retired:

Please fix the "String or binary data would be truncated" message to give the column name

The latest comment there, from Microsoft, was this:

  Latest update - the developer working on it understands the challenges involved in creating a full fix. It may be tricky to plumb the information about columns needed to generate a full error message down to the actual conversion function in such a way that won't impact insert or update performance. We may implement something cheap in the short term such as logging the type and length of the data being truncated. It's still too early to know when such a fix would reach a publicly visible release.

The best I can suggest is to vote for that item (and comment on it, describing the business value this would add for you).


SSIS - Data Import & export wizard - existing tables

The Import/Export wizard generally makes pretty basic 2-step packages.  For instance, I quickly made one that migrates two tables from databaseA over to databaseB, and the Control Flow of the generated package looks as follows:

The CREATE TABLE statements are all performed together in the Preparation SQL Task 1 which means it should be the only place you need to make a change. In support of the advice provided by @RobertCarnegie in the comments, I also recommend putting your table creation/truncation logic within a stored procedure which will be called from the Preparation SQL Task 1 step in the package.

Basically, after this change the Execute Script Task will look as follows:

Taking this approach allows the Data Flow tasks to remain unchanged, and now you should be able to reuse the SSIS package without much more work.

If you're finding it annoying to create a stored procedure that controls the creation/truncation of 144 separate tables, there are various tricks you can employ.  If you're not opposed to using the UI, you can multi-click items in the Object Explorer Details window and script out actions, so for instance if I want to generate out a bunch of drop/create scripts for a lot of tables, it's as easy as clicking on the table folder for a given database, multi-selecting the tables I want to drop/recreate in the Object Explorer Details window, and then right-click on them and select the option I want:

You will need to remove the GO statements if you want to nest this into a SP, but you can hopefully see that it's not nearly as difficult to bulk-generate scripts with this approach.

If you want to generate TRUNCATE statements (which will be easier to do), script out DROP table statements and then do a search/replace on DROP to replace it with TRUNCATE.


1) Although I prefer tcpdump myself I would ask what is the reason not to enable slow logs because it is not that big of an overhead. With reasonable thresholds it's actually very useful.

If you want to be super cautious you can safely enable it with a conservative long_query_time and slowly dropping it. The variable is dynamic so you can change it on the fly if you experience issues. Also depending on the flavor of your server (Percona for example) you may be able to set log_slow_rate_limit which was designed for exactly this purpose.

Another way to do it would be to use tcpdump which you can do on the server, on a shadow server (if your networking setup supports it) or on the application server as well. Then you can use pt-query-digest to extract the queries from it:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

This is a very versatile solution so it's worth to get familiar with.

More info can be found on https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

2) This is just an assumption but there are many-many ways to execute/replay queries so I think that's the reason for the dropped support.

3) If you have your queries you can also use percona playback, mysqlslap, sysbench or any other tools to execute them as you wish. If you want to write your own stress tool I have a web request playback code example which can give you some ideas.


Improve speed of stored proc with lots of inserts [Azure-SQL]

You should definitely separate it into multiple smaller procedures. Using just one procedure will cause a lot of locking and blocking, and generally degrade performance.

As for optimizing:

The first statement is update that updates data based on a @Code provided. Unless if its a clustered index created on it, you should create a nonclustered index on a Code column so you could prevent the whole table scan just to find the code.

Same logic could be applied for deleting. delete from prices where MainObjectid = @id; Having an index on MainObjectID, and generally on any Foreign Key column would provide you decent performance benefits (Whether you delete, or join the table, or even update records in parent table).

As you are inserting some data and deleting data based on ID of that insert, you could create a procedure that will do the first part of the code (Update or Insert if condition is met) and then return you the value of that ID(How to create objects with output parameters), which you could store in some int var in your c# app. Once you get a value you could execute the 2nd part of the procedure ,which includes deleting and some value inserting. Still it wont give you a best solution as you are inserting and deleting data in the same transaction - meaning it will most likely escalate into object lock, making you unable ( depending on transaction level) to make other select queries on those tables, but it will overall cut the % time of that update/insert initial statement.

In summary keep in mind that smaller and faster transactions are much more effective than larger and complex transactions, for performance and concurrency standpoint.


You need to create new parameter file,search for file alert_orcl.log ,open that file copy all parameters(only lines with "=" signs) after the line "System parameters with non-default values:" to a new file orcl.ora in any location e.g C:\orcl.ora

Next start the database

  startup pfile='C:\orcl.ora'; -- if successful go to next step
  create spfile from memory;    --create spfile
  create pfile='C:\oracle12c\initORCL.ora' from memory --backup pfile

Always use rman to backup spfile and controlfiles.

Note:-You'll find alert_orcl.log in ORACLE_BASE\diag\rdbms\orcl\orcl\trace(start from diag folder in oracle base directory)


Include logical key on each table vs. join

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence."

If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints.

Having independent keys is good for quickly getting a record specific to a table, but it seems you're not going to get a lot of the benefits of constraints by trying to 'roll your own'.


How do I archive and restore selected tables using SQL Server?

You have to use a 3rd party tool such as Red Gate SQL backup pro to restore. Or restore as a different DB and copy across.

Selective restore makes sense, occasionally, but selective backup usually wouldn't. You-d have to roll your own selective backup with SMO for example


Help configuring MySQL install for remote connections / root accounts understanding

root@localhost allows a root user to connect to mysql locally from the DB server using the mysql socket file.

root@127.0.0.1 allows a root user to connect to mysql locally from the DB server using the TCP/IP protocol. (Trust me, you will be needing this one. MySQL has a nasty bug in it. The bug sometimes causes the mysql socket file to disappear rendering root@localhost useless. You will need to connect using root@127.0.0.1 if this ever happens to you)

root@server.domain.lan allows a root user to connect to mysql from server.domain.lan using DNS (Dynamic Naming Services)

You could create root@'%' but I highly recommend you do not.

I would also recommend using a different password for each root user.


You need to query the CTE and return * to see the changed data.

   with updated as (update bippy set id = 100 where id = 1 returning *)
   select id, ts from updated;

Or if you want all rows returned (updated and non-updated):

   with updated as (update bippy set id = 100 where id = 1 returning id, ts)
   select id, ts from updated
   UNION ALL
   select id, ts from bippy where id <> 1;

From the documentation:

7.8.2. Data-Modifying Statements in WITH

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query. An example of this is that in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of the UPDATE, while in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.


SSIS execute task running wscript.exe hangs when started from SQL Server Agent

Without some detail of error messages it is hard to know for sure what's going wrong, however it sounds like it may well be either a permissions issue or a problem running the script on that host.

When an agent job runs, it will run under the agent service account or the credential used to create the proxy that the step runs as.

This means that the account will need permissions on wscript.exe and the script that wscript.exe runs.  I think my next step would be to do the following test:

create a new credential using your login
use this credential to create a proxy
run the step of the agent job as this proxy

If this doesn't work, try running the script directly on the host where SQL Server is running - the outcome of this would indicate if there is an issue with the script being able to run from that host


Total number of DML and DDL operations performed in a table in oracle

If your database has been running in ARCHIVELOG mode and supplemental logging is enabled, you can historically mine the archive logs using Log Miner.

There's a nice demo on this page.

May I ask why you want the total number of operations? The numbers by themselves are meaningless.


Fetch Data from same table name across schema

The best way is writing your schema names explicitly like below;

select menu_id, menu_name, cost from hotel_1.menus
union all
select menu_id, menu_name, cost from hotel_2.menus
union all
select menu_id, menu_name, cost from hotel_3.menus

On the other hand, you can write a set returning function (or table returning function) and use execute
to run dynamic query. Here is a sample set returning function for your case;

CREATE OR REPLACE FUNCTION public.get_all_menus (
  out menu_id integer,
  out menu_name TEXT,
  out cost DECIMAL(10,2)
)
RETURNS SETOF RECORD AS
$$
DECLARE
  l_query TEXT;
BEGIN
  -- Create dynamic query
  SELECT
    array_to_string(array_agg(format('select menu_id, menu_name, cost from %I.%I', n.nspname, c.relname)) , E'\nunion all\n')
  INTO
    l_query
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE
    c.relkind = 'r' AND
    n.nspname NOT IN ('pg_catalog', 'information_schema') AND
    n.nspname NOT LIKE '^pg_toast' AND
    c.relname = 'menus';  --the table name which is common for all schemas

  --run the query by using execute.
  RETURN QUERY EXECUTE l_query;

END;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

You can use this function as if its a table. The usage is;

select * from get_all_menus();


In my opinion, this is one of those operations that would definitely benefit from PowerShell.  All you'd need to do is gather the list of job steps that have your old string (in this case, your old location) and replace it with the new string (read: new location).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$OldLocation = "<YourOldLocation>"
$NewLocation = "<YourNewLocation>"

$SqlServerName = "localhost"    # by default this will look at the local default instance
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

# loop through all of the SQL Server Agent Jobs on the instance
foreach ($Job in $SqlServer.JobServer.Jobs) {

    # loop through each Job Step on the current Job
    foreach ($JobStep in $Job.JobSteps) {

        # test to see if the Job Step Command text contains your original string
        if ($JobStep.Command.Contains($OldLocation)) {
            $JobStep |
                Select-Object Parent, Name, Command

            # the below two lines would make the change to the new string and apply them
            #$JobStep.Command = $JobStep.Command.Replace($OldLocation, $NewLocation)
            #$JobStep.Alter()
        }
    }
}

NOTE:  This is sample code and should thoroughly be tested in a non-production environment.  Ensure that your previous data is backed up, and that you are positive the change is the desirable outcome.

All this code does it loops through all your jobs on a specific instance.  If the job step command contains a certain string, then it will show you what job it belongs to, the name of the step, and the command containing the string.  I have deliberately commented out the command modification lines so that the code only actually identifies which steps contain your string.

You can also narrow your search by adding additional conditional clauses to the if block.  For instance, if you just want to check CmdExec jobs, add this with an -and conditional check:

$JobStep.SubSystem -eq [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::CmdExec

This is simply one way to do this.  I will stress my point again: make sure you test out this theory and execution far away from production, and ensure that you can revert changes with the necessary backup.


Does "Table Valued Function" force the plan to get into cache?

does this behaviour is shared with table value function or not?

the short answer is:
It is not, because if you check what execution plans you have in the cache these are the objects you can find there:

cacheobjtype    nvarchar(34)    Type of object in the cache.
The value can be one of the following:

Compiled Plan
Compiled Plan Stub
Parse Tree
Extended Proc
CLR Compiled Func
CLR Compiled Proc

You may be looking to consider:

Plan cache and optimizing for adhoc workloads

check what your plan cache is made up of:

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count],
    SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
        ELSE 0
        END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
    SUM(CASE WHEN usecounts = 1 THEN 1
        ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO

Minimize plan cache bloat with the optimize for ad hoc workloads configuration option

  SQL Server 2008 introduced the ‘optimize for ad-hoc workloads’
  configuration option which, when enabled, causes SQL Server to create
  a plan stub instead of a full plan the first time a statement is
  executed. If the plan is used a second time, then SQL Server will
  store the full plan. This option was introduced to help minimize the
  performance impact of plan cache bloat.

and another thing I would like to mention is..

High compiles (not recompiles):

High Compiles and Multi-Statement Table Valued Functions


SQL query with Like operator with Leading Wildcards

What first comes to mind, is that if you are searching for some symbols inside the licenseplate that will always start, for example at 4th symbol (example data 123as43bvxxx ), and it will be always be fixed, you can consider adding computed persisted column to your table

alter table [table1]
add LicenseplateComputed as substring(licenseplate,4,50) persisted

And then create nonclustered index on your newly added computed column "LicenseplateComputed"

After this you will be able to query your table

select top (30000) * from table1 where LicenseplateComputed like 'as43bv%'

and it will perform efficient Index Seek


Here are a few starting points (and I don't have answers to everything).
Disclosure: I suggest a couple tools that were written by myself (and of course used in production).

Consider this login audit plugin (open sourced, find in GitHub). I developed & compiled in on Linux, you will have to find your way on Windows. This plugin will log any login attempt (successful or failed) onto your server.

We use the above to audit any login on any of our servers -- using logstash to aggregate all our logs, and kibana to view/query it. Likewise, you can query this log and send alerts to graphite / query by Nagios -- I don not know what bests suits Windows.

Consider common_schema's security_audit. This will generate a report on bad security settings (duplicate passwords, excessive privileges etc.). It does not do any steganography.
Check out GreenSQL - I never tried it myself. It's a proxy sittting between your clients and your MySQL server, that can analyse and block queries on the fly.
Check out MariaDB security enhancements, e.g. role management. Available for Windows.


MyISAM with Innodb in the same Wordpress database reduce performace?

Having InnoDB won't make MyISAM slower and vice versa. However you have to be more careful in memory allocation for InnoDB and MyISAM. Because they store data and indexes in different buffers.
MyISAM:

Indexes in the key buffer
Data in file system cache

InnoDB:

Data and indexes (well, in InnoDB everything is indexes) in the buffer pool

So if you allocate more than necessary to InnoDB then MyISAM will have to access disk more often (and vice versa)


Restore script in SQL server doesn't work in W7

You don't need rights to access the backup file, the SQL Service does. Running SSMS as administrator will not help.

Check that the account you have SQL running under is able to read from that directory.


It depends on the person who is managing the server. you can make decision based on following points:

If you are familiar with commands based administrative tasks, then Ubuntu Server edition is a better choice, as finding and installing required tools is easier compared to other distros. Best part is reduced overhead of Licencing and GUI.
If you prefer GUI based administration, then windows is good choice.


Does MySQL limit the number of users (not connections)

User accounts are again stored in tables in terms of rows and columns and also its respected permissions under mysql database wherein it has primary key as host and username. So, I don't think so you have any limitations. But you have something called max_connections that has to be set to a value, say it has 100000 as max that you can set. Then keeping each user connection in mind then we can go upto 99999 users connected to it parallely.

My question to you is.

Why do you need all different users to be connected to the mysql connection pool?
It is highly impossible to make a note of users permissions and its passwords.
If you need those many users for application purpose, you may try doing it in regular innodb tables and have list of users under one group and tag them to a role. Just grant multiple roles to the users who needs the access.


Deleting expired SQL server database backup file on data domain using PowerShell

Something like this:

$FileExe = "C:\..\bin\ddbmexptool.exe"
$dbtype = "mssql"
$dduser = "DDBOOST_USER=dduser"
$ddhostpath = "DEVICE_PATH=DDHOSTMSSQL"
$devicehostname = "DEVICE_HOST=ddhost.domain.com"
$servers = "server1","server2","server3"

foreach ($server in $servers)
{
  $clientname = "CLIENT=$server.domain.com"
  & $FileExe -n $dbtype -a $dduser -a $ddhostpath -a $ddhostpath -a $devicehostname -a $clientname -b "6 weeks ago" -e "2 weeks ago"
}


Find which numbers in [1, 161] are not in the result set?

To find gaps in a number range:

Test table and data:

mysql> CREATE TABLE wp_blogs
    -> (
    ->   blog_id INTEGER
    -> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);

Query:

mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
    ->     FROM wp_blogs AS a, wp_blogs AS b
    ->     WHERE a.blog_id < b.blog_id
    ->     GROUP BY a.blog_id
    ->     HAVING start < MIN(b.blog_id);
+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+


How to get First in and Last out with multiple records?

Look for

with
cte1 as ( select *,
                 coalesce(lag(verify_state) over (partition by user_id order by verify_date asc),1) prev_state,
                 coalesce(lead(verify_state) over (partition by user_id order by verify_date asc),0) next_state
          from tbl_excel_attendance
),
cte2 as ( select *,
                 lead(verify_date) over (partition by user_id order by verify_date asc) next_date
          from cte1
          where (prev_state,verify_state,next_state) in ( (1,0,0), (1,0,1), (0,1,0) )
)
select *
from cte2
where verify_state = 0
  and next_date is not null
order by 2,3

fiddle


Will SSIS 2012 work with SQL Server 2008R2 (not integrated)

There will be no issues as SSIS 2012 will work with previous versions of SQL Server. I recently upgraded from 2008R2 to 2012 with 45 SSIS packages and all are working fine. As Always Test all your packages before hand on 2012.

from this link :

As you may already know, the SQL Native Client OLE DB provider is being deprecated. This doesn't affect other OLE DB providers or the OLE DB API. Also, the SSIS OLE DB components are not being deprecated.
In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from the packages.


Python SQLite3 horizontal partitioning by string column

This can be achieved as follows according to the official SQLite documentation.

For the bye table you could use the following:

CREATE TABLE bye AS
SELECT a,
       b,
       c,
       d,
FROM all_entries
WHERE a = 'bye'

...and for the hi table:

CREATE TABLE bye AS
SELECT a,
       b,
       c,
       d,
FROM all_entries
WHERE a = 'hi'

Reference: CREATE TABLE (SQL As Understood By SQLite)

If this is just a temporary solution for something you wish to achieve then add the TEMPORARY keyword:

CREATE TEMPORARY TABLE bye AS...

There are some limitations with this approach which are described in the referenced article.

  A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. The default collation sequence for each column of the new table is BINARY.

  Tables created using CREATE TABLE AS are initially populated with the rows of data returned by the SELECT statement. Rows are assigned contiguously ascending rowid values, starting with 1, in the order that they are returned by the SELECT statement.

And you would possibly have to post a question on Stack Overflow for a Python solution.


Managing a SQL Server database with over one terabyte of data

Get more space.   If you have a business requirement to retain that much data, they have to come up with the money.
Turn on page compression for the biggest tables (and indexes) (test test test!), Log data compresses extremely well, but compressing that amount of data is going to take some time.
See point 1


Oracle High-water Mark: how relevant it is when using ASSM tablespace for tables?

ASSM is not about reducing high water mark of the table. The main benefit of ASSM is it automates the task of freelist management in order to handle the concurrent DML on the table. Freelists are used to allocate new blocks when data is added to the table. You will be able to see the performance benefits of ASSM when multiple sessions try to populate the data concurrently into a table.
As for the issue of deleted blocks and its performance impact, the answer is it depends. ASSM or any other oracle feature does not automatically remove blocks from table when data is deleted. If you delete large amount of data from a big table and do not explicitly shrink the table and if your queries mostly do full table can, then you will not see any performance benefit by deleting the table data. Even if you decide to shrink the table to get immediate performance benefit, you will need to think about how quickly new data will be added to the table and the table will grow back, basically a cost-benefit analysis.
Again, a better explanation of this can be found in oracle documentation and in books like Expert Oracle Database Architecture authored by Tom Kyte.


Select all rows that have all of the following column values

This is one way to accomplish that, though there might be a simpler way:
WITH CTE_DirectorFilms_Distinct AS
(
    SELECT DISTINCT D.DID, F.GID
    FROM Directors D
    INNER JOIN Films F
        ON D.DID = F.DID
),
CTE_DirectorFilm_Matches AS
(
    SELECT DF2.DID, COUNT(DF1.GID) OVER (PARTITION BY DF1.DID) AS DF1_GID_COUNT, COUNT(DF2.GID) OVER (PARTITION BY DF2.DID) AS DF2_GID_COUNT
    FROM CTE_DirectorFilms_Distinct DF1
    LEFT JOIN CTE_DirectorFilms_Distinct DF2
        ON DF1.DID <> DF2.DID
        AND DF1.GID = DF2.GID
    WHERE DF1.DID = IdOfFirstDirectorGoesHere -- Or can filter on First Name and Last Name if you prefer
),
CTE_Director_Matches AS
(
    SELECT DISTINCT DID
    FROM CTE_DirectorFilm_Matches
    WHERE DF1_GID_COUNT = DF2_GID_COUNT
)

SELECT D.DID, D.First_Name, D.Last_Name
FROM Directors D
INNER JOIN CTE_Director_Matches DM
    ON D.DID = DM.DID

This first gets a distinct list of all Directors and the Genres they've created a film for. Then using a left outer self join it gets all Directors with their unique Genre counts and the Genre count for the Director you're interested in (this is based on that Director's DID but you can do First_Name and Last_Name instead if you prefer). Then it filters out all Directors who don't have a matching count of unique Genres to Director of the previous CTE. Finally it gets all the fields from the Directors table for those matching Directors.


glogin.sql supress error when Instance Idle

This is what I use in my login.sql. Tom Kytes book Expert One on One has this script. No sign of ORA-01034 when instance is down. My prompt only shows idle>

set term off

-- a lot of stuff

REM here is how I set my signature prompt in sqlplus to
REM username@database> I use the NEW_VALUE concept to format
REM a nice prompt string that defaults to IDLE (useful for those
REM of you that use sqlplus to start up their databases - the
REM prompt will default to idle> if your database isn't started)
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr(global_name, 1, decode(dot, 0, length(global_name), dot-1)) global_name
from (select global_name, instr(global_name, '.') dot
from global_name);
set sqlprompt '&gname> '

-- a lot of more stuff

set term on


Kerberos Configuration Manager - Does it work with SQL Server 2016?

This currently is not available yet for SQL Server 2016 unfortunately. You will have to manually add your SPN's using

 setspn –S MSSQLSvc/<server name>:1433 domain\account

EDIT: The Kerberos Configuration manager is now available for SQL Server 2016
https://www.microsoft.com/en-us/download/details.aspx?id=39046