Syncing updates to master in master/slave setup [semi-sync replication]

You are almost there. I guess you have missed the auto_increment_increment & auto_increment_offset variables.

Set auto increment increment to the total number of servers.

Set auto_increment_offset to a number between 1 and auto increment increment for each server.

example:

Server 1: auto increment increment = 2, auto_increment_offset = 1

Server 2: auto increment increment = 2, auto_increment_offset = 2


is the only way to create a user for an azure database, to use SQL query?

Thats correct. Only via the Create User query. The GUI is not supported to create user and permissions for Azure sql database.
You can do that via any client (ssms) or the query editor in portal


PostgreSQL not using index scan when a second ORDER BY column is present

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one.

LIMIT will always operate after sorting is taken care of, so when sorting without an index is necessary, it will sort the entire recordset prior to processing it through LIMIT.

I wonder what is it you want to achieve? Applying LIMIT over a primary indexed order, then including all rows matching the sorted field(s) in the limited result, and finally sorting the limited results by any other fields and limit again. This is the way to go in this case:

SELECT *
FROM tbl
WHERE row_time = (
  SELECT row_time
  FROM tbl
  ORDER BY row_time DESC
  LIMIT 1)
ORDER BY row_id DESC
LIMIT 1;

Whereas for LIMIT n where n > 1:

SELECT *
FROM tbl
WHERE row_time IN (
  SELECT row_time
  FROM tbl
  ORDER BY row_time DESC
  LIMIT n)
ORDER BY row_id DESC
LIMIT n;


The query is slow because first, this query has an OR condition which cannot be optimized with a single index (while conditions with only AND can) and second, because besides the emails, it has to check the isActive=1 condition. So it prefers to do a table scan. The other possible path would be to use both email indexes with the Index Merge Union Access Algorithm but for some reason the optimizer does not choose this plan.

One solution would be to alter the two indexes you have, appending the isActive column at the end:

ALTER TABLE user
  DROP INDEX the_name_of_the existing_index,
  DROP INDEX the_name_of_the_other_index,
  ADD INDEX emailAddress_isActive_IX
    (emailAddress, isActive),
  ADD INDEX emailAddress2_isActive_IX
    (emailAddress2, isActive) ;

If it still slow (and/or does not do an index merge but prefers the table scan), you could try to rewrite the query with UNION:

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress = 'user@domain.com'

UNION

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress2 = 'user@domain.com' ;

Another option - without necessarily adding/altering indexes - is to nest the query into 2 layers, keeping only the email conditions in the internal layer, essentially forcing MySQL to consider using index-merge with the two available indexes:

SELECT userId
FROM
    ( SELECT userId, isActive
      FROM user AS u
      WHERE emailAddress = 'user@domain.com'
         OR emailAddress2 = 'user@domain.com'
    ) x
WHERE isActive=1 ;


Couple of additions to Rolando's suggestion.

If you're clearing out the old table, as well as populating new you could use the OUTPUT clause. Be mindful of the potential for log growth, consider a loop/batch approach if this may be a problem.

DELETE
    OldDatabase.dbo.MyTable
OUTPUT
    DELETED.col1
    , DELETED.col2
    , DELETED.col3
INTO
    NewDatabase.dbo.MyTable

BCP is a handy alternative to be aware of. Note this is using SQLCMD syntax.

:setvar SourceServer OldServer
:setvar SourceDatabase OldDatabase
:setvar DestinationServer NewServer
:setvar DestinationDatabase NewDatabase
:setvar BCPFilePath "C:\"

!!bcp "$(SourceDatabase).dbo.MyTable" FORMAT nul -S "$(SourceServer)" -T -n -q -f "$(BCPFilePath)MyTable.fmt"
!!bcp "SELECT * FROM $(SourceDatabase).dbo.MyTable WHERE col1=x AND col2=y" queryout "$(BCPFilePath)MyTable.dat" -S "$(SourceServer)" -T -q -f "$(BCPFilePath)MyTable.fmt" -> "$(BCPFilePath)MyTable.txt"
!!bcp "$(DestinationDatabase).dbo.MyTable" in $(BCPFilePath)MyTable.dat -S $(DestinationServer) -T -E -q -b 2500 -h "TABLOCK" -f $(BCPFilePath)MyTable.fmt


Raise application error in a stored procedure when inputs contain keywords like 'delete' , 'insert','update'

I would use regex_like:
select column_value
     , case
           when regexp_like(' '||column_value||' ','\s(drop|execute|truncate|create|insert|update|delete|merge)\s', 'i')
               then 'Yes'
       end as illegal
from   table(ora_mining_varchar2_nt
             ('drop table customers', 'just dropping by', 'undroppable'));

COLUMN_VALUE              ILLEGAL
------------------------- -------
drop table customers      Yes
just dropping by
undroppable

I've added merge to the list as I assume you are trying to prevent SQL injection. If so, there are still risks even if you filter for keywords. execute isn't a SQL keyword, though. Did you mean call?
Your procedure would become:
create or replace procedure app_error_test
    ( query1 nvarchar2
    , query2 nvarchar2
    , query3 nvarchar2
    , query4 nvarchar2
    , query5 nvarchar2
    , query6 nvarchar2 )
as
    queryconcat nvarchar2(30000) :=
        query1||' '||query2||' '||query3||' '||query4||' '||query5||' '||query6;
begin
    if regexp_like(' ' || queryconcat || ' ', '\s(drop|call|truncate|create|insert|update|delete|merge)\s', 'i')
    then
        raise_application_error(-20032, 'Illegal content in supplied SQL: '||queryconcat);
    end if;
end;


This is quite normal in database design. If you look at this guide about cardinality notations you will see that one option is zero to one (optional) (one campsite has zero to one campgrounds).

In SQL you do it by making a FOREIGN KEY constraint from campsite to campground and not declaring the foreign key id in campsite as NOT NULL.


Troubles with creating initial PostgreSQL base

As the root user, edit /etc/sysctl.conf and add the following lines:

kernel.shmmax=2147483648
kernel.shmall=2097152

Then run:

sysctl -p

.. to reload the kernel settings.

That'll set the maximum shared memory segment size to 2Gb & should solve your issue.


For the Nested Loop, there is nothing wrong with that.. You have less than five rows you're returning and you do it in one loop.
I'm not sure what you mean as batch. You'll have to show your other queries. Why are you running a UNION ALL a bunch of times to begin with?

Try something like this for your batch,

SELECT
  test_ipv6.ip,
  test_ipv6.data,
  test_cidr_ipv6.cidr,
  test_cidr_ipv6.data,
  test_ipv6.start,
  test_ipv6.end,
  test_cidr_ipv6.start,
  test_cidr_ipv6.end
FROM test_ipv6
INNER JOIN test_cidr_ipv6
  ON (test_ipv6.ip <<= cidr )
WHERE
  test_ipv6.ip = any(ARRAY[ip6('2001:DB8::1'),ip6('2001:DB8::1')]::ip6[])
  AND '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
  AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end;

You may have better luck with a CTE or inlining something like this...

WITH t AS (
  SELECT ipv6(iptext) AS ip
  FROM (VALUES
   ('fddf:c4c1:2573::/48') ,
   ('fd8d:d482:3c08::/48')
  ) AS v(iptext)
)
SELECT
  test_ipv6.ip,
  test_ipv6.data,
  test_cidr_ipv6.cidr,
  test_cidr_ipv6.data,
  test_ipv6.start,
  test_ipv6.end,
  test_cidr_ipv6.start,
  test_cidr_ipv6.end
FROM test_ipv6
INNER JOIN test_cidr_ipv6
  ON (test_ipv6.ip <<= cidr)
INNER JOIN t
  USING (ip)
WHERE
  '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
  AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end;


db2_install do not have write permission on the directory or file

if you install on mounted fs ensure it's not mounted with the nosuid and noexec parameters.
db2 binaries require suid file permissions.
remove v10.5 directory if you created it manually
and set chmod 755 on parent directory


Transaction Time Tables in MSSQL and Constraints

The trigger is pretty slow on larger tables.

You don't have any indexes, and you aren't limiting the check to the rows affected by the statement using the INSERTED virtual table.  See: Use the inserted and deleted tables

many operations like updates require more than one operation

In scenarios like this you're better-off managing the updates through a stored procedure, rather than trying to handle everything with a trigger.  For instance if you insert an adjoining interval, you'd probably want to coalesce them.


Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

Nothing is wrong with your table definition.
(Except hat I would use jos_content_id or something instead of the non-descriptive column name id.
And I probably would use text instead of varchar(50).

Your INSERT statement is the problem.

With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value from the associated sequence.

Provide an explicit list of target columns (which is almost always a good idea for persisted INSERT statements) and omit serial columns completely.

INSERT INTO jos_content(asset_id, some_column, ...)
VALUES (36,'About',...);

If you need the value(s) of automatically generated column(s) immediately, use the RETURNING clause:

INSERT ...
RETURNING id;  -- possibly more

More details in this related answer on SO:

Increment value of a table ID for each INSERT

If you have manual entries in serial columns that might conflict later, set your sequence to the current maximum id to fix this once:

SELECT setval('jos_content_id_seq', max(id))
FROM   jos_content;

Where jos_content_id_seq is the default name for a sequence owned by jos_content.id, which you already found in the column default. Seems to be xhzt8_content_id_seq in your case;

Update: A similar issue popped up on SO and I came up with a new solution:

Make Postgres choose the next minimal available id


Is there an alternative to "Create or replace procedure" in MySQL?

This is the syntax to drop it if it exists

DROP PROCEDURE IF EXISTS db.sp_tmp_90days;


Moving mysql database to new partition with different partition "type"

You could omit partitioning /dev/sdb and just put a filesystem on it.

mkfs.xfs -L MYSQLDATA  /dev/sdb

(other filesystems like ext3/4 will also work).

And in /etc/fstab

LABEL=MYSQLDATA /var/lib/mysql  xfs    defaults 1 2

To migrate:

systemctl stop mysql.server
mount /dev/sdb /mnt
chown mysql: /mnt
mv /var/lib/mysql/* /mnt/
umount /mnt

Then use:

mount /var/lib/mysql
restorecon -Rv /var/lib/mysql # if you have selinux
systemctl start mysql.service


Oracle Database 19.3 on Windows: Howto impdp or expdp from a UNC path in Windows?

You may find the below interesting:

http://christian-gohmann.de/2019/05/19/symbolic-links-in-directory-objects-not-permitted-with-oracle-18c-19c/

Long story short, I had the same issue, then after setting the parameter below and restarting the database, Data Pump was able to use the network share.

ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;


MS SQL - Find Stored Procedure (Dynamic) Related to Table in Database

For dynamic SQLs, the only way to me is to search the definition of the stored procedure, like the following

use [your db]
select * from sys.sql_modules
where definition like '%Employee%'

-- if you know the pattern, you may use more restrictive way like
select * from sys.sql_modules
where definition like '% from Employee%'

But I have to say this may not be accurate. For example, if 'select * from Employee' is commented out in your SP, it will still be reported using this method.

Hope this helps.


pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace

I have completed the migration with no problems.

Creating the dump is easy:

sudo -u postgres pg_dump --verbose --no-tablespaces --format=directory --file=/backup/path old_database_name

Restoring on a new instance: first, create a new tablespace, and a target database in that tablespace. Then import your dump like this:

sudo -u postgres pg_restore --verbose --dbname=new_database_name --jobs=8 /backup/path/

You can adjust number of concurrent jobs dependent on Your hardware - processor cores, I/O subsystem performance, etc.

Now we have all the data in another database, with different name, in a dedicated tablespace, on a new server running newer PostgreSQL.


MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online

You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping security boundaries.

Setting a database online

As part of your admin account, you have access to set databases offline, but bringing a database back online requires sysadmin (which you don't have). To work around this permission restriction, AWS provides a stored procedure to bring databases online. You'll need to use the rds_set_database_online procedure in the rdsadmin database

EXEC rdsadmin.dbo.rds_set_database_online 'MyOfflineDatabase';

This procedure is fully documented by AWS here.

Reading the error log

Similarly, you  cannot use xp_readerrorlog on RDS SQL Server because it requires sysadmin. Instead, AWS provides THE stored procedure rds_read_error_log

EXEC rdsadmin.dbo.rds_read_error_log;

This procedure is fully documented by AWS here.


For SQL Authentication logins, this is no different in SQL Server 2014 than in previous versions. I just did this in 2008:

CREATE LOGIN dbowner WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO dbowner;
GO
DROP LOGIN dbowner;
GO

  Msg 15174, Level 16, State 1
  Login 'dbowner' owns one or more database(s). Change the owner of the database(s) before dropping the login.

I can't drop the database until I do this:

ALTER AUTHORIZATION ON DATABASE::mydb TO sa;

For Windows Authentication logins, As Dan pointed out, prior to SQL Server 2014, this check wasn't performed for Windows logins, so you could leave databases in a weird state by dropping their owner. Your second option sounds best to me, and it only means adding the above line to your script. Another option is to just not make a Windows login the owner (I'm not sure why you're making them owners instead of just giving them appropriate rights in the database), or stop dropping the logins as part of the process, or both.


What happens when you delete a Clustered index and Non Clustered Index once created

From MSDN:

  When a clustered index is dropped, the data rows that were stored in
  the leaf level of the clustered index are stored in an unordered table
  (heap). Dropping a clustered index can take time because in addition
  to dropping the clustered index, all nonclustered indexes on the table
  must be rebuilt to replace the clustered index keys with row pointers
  to the heap. When you drop all indexes on a table, drop the
  nonclustered indexes first and the clustered index last. That way, no
  indexes have to be rebuilt.

https://technet.microsoft.com/en-us/library/ms190691(v=sql.105).aspx

As for the nonclustered index drops, they aren't DELETING any data, it's a metadata operation and the index pages are simply deallocated. Think about TRUNCATE versus DELETE, TRUNCATE just deallocates the pages and you're done.  Dropping a nonclustered index is very similar, hence why dropping one is lightning fast when you do it (unless you are blocked).


What I would suggest to you before putting a lot of work into designing a system, is to take a leaf from the book of the great Issac Newton - "If I have seen further it is by standing on the shoulders of giants".

By this I mean take a long hard look at what's been done already - there are systems that could be of interest to you in this domain - take a look at this for example. Of course, in one sense, a taxi is just a resource to be scheduled - so you could look further afield at open source software for scheduling generally for ideas and inspiration.

That's not to say that you can't have your own ideas, but just take a look at some of the contenders in the open source world out there - borrow what you want and need from them and disregard the rest.

With respect to your particular question, I would go to the trouble of maintaining the details of day_off, booked, ill, unavailable... that way you can reward drivers who are never ill, rarely unavailable (i.e. when not actually booked) &c. The more information you can put into the database, the more you can do with it later. Just IMHO.


Impact of partition scan on large and small data

Think of it as different queries. How much it cost to navigate a B-Tree to find zero rows? That is what happens for those "extra partitions". Those that don't contain any data for your range and which are skipped with your second query alternative. If you don't have too many partitions, then this extra cost will be very low. But as number of partitions increase, so will the overhead (especially relative overhead if the selectivity for the range is high).


SQL Server crash with Reason 0x00000006 after a while on linux

Partial Solution:

Downgrading to SQL Server 2008 solves the issue.

PS.:

Posting this as an answer just because, depending on the project needs, this may be enough.

But a solution to the SQL Server 2017 version is still needed though...


invalid record length at 1A2/8F240298: wanted 24, got 0

You are looking at the wrong message.  The "invalid record length" is an ordinary way for WAL to end.  It does not necessarily indicate that there is a problem.  The  "FATAL: could not fsync" however definitely should not be happening.  You have a hardware problem, or a corrupt file system, and those need to be dealt with outside the database.

It is possible you can fix it with something like fsck, and then repeat the automatic recovery.  But you can never really be sure you don't already have permanent corruption that crash recovery can't fix.  So your best bet may be to restore from a backup, if you have one, onto different hardware.


This depends on how your log backups are set up.  If you are using Maintenance Plans, then look up the specific plan under Management->Maintenance Plans in your SSMS Object Browser.  If you are using a SQL Agent Job, then you'll need to look at the schedule attached to that specific job by opening the job and looking at the schedule on the schedule page.

You can also query the msdb.dbo.backuphistory table to see when your log backups have been taken with the following query:

select
    database_name
    ,backup_start_date
    ,backup_finish_date
from msdb.dbo.backupset
where type = 'L'
order by database_name,backup_start_date desc


Cartesian Product SQL / Inserts with multiple Values Clauses

On SQL Server you can write

select Ad,MasaAdi from
(
 select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
 from (
 select newid() as ID,Ad,MasaAdi
 from #Personel
 cross join #Masa
 )t
 ) t2 where Sira = 1 order by Ad

This is the best syntax to emphasize that it is a cross join.

But on SQL Server 2008 your query gives the same result .

EDIT:

Your problem is not the select statement, but the inserts. Inserts with multiple value clauses is a new feature of SQL Server 2008. For 2005 you have to write:

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')


Execute a query which returns uniformly at maximum 100 records

You could assign a row number to your results, then show every Xth record from that result.  You do this by calculating the total number of records, and dividing that by your desired number of intervals.  If the modulo is 0, spit out that record.

In my example below, I want to 10 records (approx) from the sysobjects table, sorted alphabetically by name.  The result is 10 records.

This was a lot harder to explain than to write.

;with SummaryTable as
(
select row_number() over (order by name) as RowNum,
       name,
       (select count(*) from sys.objects) as 'TotalCount'
 from sys.objects
)
select *
  from SummaryTable
 where RowNum % (TotalCount / 10)  = 0


SQL Server 2016 capped at specific CPU usage

Standard Edition is limited to 4 sockets or 24 cores


Why doesn’t IMPLICIT_TRANSACTIONS increment @@TRANCOUNT for my IF statement?

I suspect that what you're seeing is because some statements get transformed to SELECT statements behind the scenes and others do not. Consider your final DECLARE statement:

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

That is just shorthand for this:

DECLARE @y INT;
SET @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

Which can also be written like this:

DECLARE @y INT;
SELECT @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

In which case you'd expect to see @@TRANCOUNT incremented. However, can you make the same statement about the IF statement? If I run your original code with actual execution plans turned on I see the following:

I don't see a SELECT in the plan for the first query. I only see COND WITH QUERY. So it seems consistent with the documentation.


Which version of xtrabackup to use with MySQL 5.5?

Percona XtraBackup 2.3 should be backward compatible, so you should be able to use PXB 2.3 with MySQL/Percona Server 5.5. There are some edge cases where you will need to use PXB 2.2 or even 2.1 but it is rarely needed if PXB 2.3 works.

PXB 2.4 is recommended to be used on MySQL/Percona Server 5.7.

In any case, you should always test your backups so backup using PXB 2.3 and restore it on another instance to see if everything works.


What would be the better schema to represent this data?

Without knowing complete and exact requirements, we cannot design a complete data model. Based on my understanding from question, the following rules can be derived.

Each folder must have a folder type
Each folder type may contain more than one folder
Each folder must belongs to an owner
Each owner may contain more than one folder
Each owner must belongs to an owner type (either individual or team)
Each record must belongs to a folder
Each folder may contain more than one records

Here, I am not clear about about few things, such as:

Whether a record can be a part of more than one folder
Whether a folder belongs to both individual and team
Whether folder type other meaning such as archival or sales or something else
Whether is there any chance of adding more folder type or owner type (group, external, vendor, client, etc)

I am designed the below model, hoping future changes are adoptable without much changes in the design.

My suggestion is to start from conceptual data model, regardless of the size of the project. More questioned you answered on this process, more better the application will be.
I hope this answer will be helpful. Thanks!


Best to isolate the database in multiple instances?

the problem you are facing can be likened to the problem of multitenancy as you are talking about multiple applications (multiple blog) and one or more tables, schemas, databases, instances. A good starting point can be the following article from MSDN: Multi-Tenant Data Architecture. The article is focused on Microsoft SQL Server but it can be easily generalized.

Regards
Giovanni


Same MySQL query is so slower on ubuntu than Windows

For 99% I bet it's a CPU TOPOLOGY (both HW and VM may have the same) vs OS LICENSING.
While LINUX KERNEL utilizes all CPU's the WINDOWS limit those number to valid license type. e.g. for W10  PRO AND STANDARD EDITION it is a 2.
So in short - your COMPUTING POWER is limited on WINDOWS OS
while ON LINUX KERNEL IT MIGHT BE ONLY A GOVERNOR to tune the speed of OP's.
Other thing to consider is the utilization of raw or shared mem under those OSes or setting for hyperthreading memory interleaving etc.
Generally all other OS implementation that tries to utilize all those "advanced hw speedup techs" to run OPs.
I have encountered such a situation myself - running a 64 core HP server on a VM with a badly copied topology under unlicensed Windows 10 queries results took very long time (about 5 minutes) when normally under full licensed (64cores) Windows server it took 10 seconds. it all depends on the operating software (use of MT, HT, governors, thread locking etc).


Mysql replication from existing slave to new slave

I missed one very important option:

log_slave_updates

Now the binlogs are growing!

ref: MySQL Slave Relay Logging but not logging Binary Log

http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates


Can you control what tags are used in an XML column?

You could define a trigger in a suitable language to examine the data and block storing if validation errors occur. At least Postgres users could take this route.

For DB2 I found an example on how to use XML Schema for validation of XML columns. There should not a big difference to call XMLVALIDATE from within a check constraint or a trigger.
The example basically proposes the following steps:

-- Import the XML schema
register xmlschema 'http://sample' from 'file:///sample.xsd' as sample_db_scheme.sample_xml_schema;
-- Put schema to repository
complete xmlschema sample_db_scheme.sample_xml_schema;

Once the schema was imported, you can use it to validate the XML:

-- Place this call in a trigger or constraint
XMLVALIDATE(? ACCORDING TO XMLSCHEMA ID sample_db_scheme.sample_xml_schema)


mongodb - enableSharding for database takes five to six minutes to complete

The enableSharding command is basically a meta data operation that changes one document in the databases collection, and that happens on the config servers (at least it was the last time I checked it in detail in 2.6). If this is slow, it's because you have contention there (at the config servers), likely due to activity from the other collections being sharded and balanced.

Each initial collection sharding, chunk migration, balancer lock will hit various tables on the config servers, and potentially contend with your enable sharding command (the meta data operations are special cases too, with 2 phase commit etc.). You can look at scaling up the config databases (will only work if they are struggling with resources based on your current load, not if you are hitting locking contention), or if this is only a once-off and not indicative of ongoing load, then checking for completion of other operations, balanced collections etc. will make your initial configuration run slower but less prone to timeout and error. The other option would be to alter the timeout in the code to wait longer for the command to complete, of course.


How do I determine the spid of the session that is making a query on a linked server?

You would have to check running queries on the remote server. You could isolate linked server queries by filtering on your linked server name with the following dmv query.

    SELECT [s].[session_id] AS                          [spid]
       ,[s].[status]
       ,[s].[login_name] AS                         [loginName]
       ,[s].host_name AS                            [hostName]
       ,COALESCE(DB_NAME([r].[database_id]), '') AS [dbName]
       ,[r].[command]
       ,[wt].[wait_type] AS                         [waitType]
       ,[s].[login_time] AS                         [loginTime]
       ,[s].[last_request_end_time] AS              [lastBatch]
       ,[s].[program_name] AS                       [programName]
       ,[t].text AS                                 [lastSQLText]
FROM [sys].[dm_exec_sessions] AS [s]
     LEFT JOIN [sys].[dm_exec_requests] AS [r]
     ON [r].[session_id] = [s].[session_id]
     LEFT JOIN [sys].[dm_exec_connections] AS [c]
     ON [c].[session_id] = [s].[session_id]
     LEFT OUTER JOIN [sys].[dm_os_waiting_tasks] AS [wt]
     ON [s].[session_id] = [wt].[session_id]
     CROSS APPLY [sys].[dm_exec_sql_text]([c].[most_recent_sql_handle])AS [t]
WHERE [s].[status] = 'Running'
  AND [t].[text] like '%LinkedServerName%';


Incorrect EST Timezone instead of EDT in Oracle

I did not investigate any further for specific database and timezone file versions, but I guess you use and old database version with an outdated timezone file.

10.2.0.5.0:

SQL> select VERSION from v$timezone_file;

   VERSION
----------
         4

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EST

11.2.0.4.180417:

SQL> select VERSION from v$timezone_file;

   VERSION
----------
        14

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

It's time to upgrade.

This is a good starting point (requires a valid Oracle Support contract which you would need anyway for downloading the patch as well): Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)


sqlplus startup - failure in processing system parameters

Set the ORACLE_SID  and ORACLE_HOME  variables properly.  They must be in upper case letters.

Second, share the content of directory. ORACLE_HOME/dbs


Replace

select portfoliono into mportfoliono from sipholder where accno=:new.accno;

with

mportfoliono := new.portfoliono;

no need to use a SELECT for values of the updated row.


How should I be backing up Postgres for this scenario?

If you want to restore individual databases, you should probably be using pg_dump, not pg_dumpall.  And if you don't intend to restore over the top of an existing populated database, you shouldn't be using --clean.

Backing up your entire cluster and migrating/cloning one database that is in it are different tasks, you probably want different commands to do them.

But you have the dilemma that if you specify --create to pg_dump it will want to restore into a database of the same name as it dumped from, and if you don't specify --create then it is up to you to create a new database with the name you want, but also with the correct ENCODING and LOCALE.


The command is SHOW MEASUREMENTS.  You left off the 'S' at the end.

Hope that helps!


MYSQL: Merge data from one column to another

You need to update your table not insert

update my_jobs set city=substring_index(location, ", ",1) ,
state=SUBSTRING(location, LENGTH(location)-2, LENGTH(location))

If you want more explanation add a comment


How to start LaunchPad service of SQL Server on Ubuntu?

Linux Support Roadmap for SQL Server Machine Learning Services:

"Machine learning using R or Python in-database is not currently supported in SQL Server on Linux. Look for announcements in a later release.

However, on Linux you can perform native scoring using the T-SQL PREDICT function. Native scoring lets you score from a pretrained model very fast, without calling or even requiring an R runtime. This means you can use SQL Server on Linux to generate predictions very fast, to serve client applications."

The stand-alone Machine Learning Server is already supported on Linux.


changing from inner joins to left joins to include null values?

As I don't have the table definition and values Inside them, here's the code I used to do something "similar".

You can use it as inspiration... You may be able to adapt it to your situation.

 /*------------------------------------------------------------------ */
--create table tservicehours ([hours] float,jobcode int, reportno int);
--create table tServiceJobCodes (Servicejobcode int);
--create table tServiceReports (ReportNo int, [date] date, employee int);

--insert into tServiceReports values (1, '2017-01-03',10204), (2, '2017-01-04',10203), (3, '2017-01-05',10203), (4, '2017-01-02',10203), (5, '2017-01-03',10203), (6, '2017-01-05',10203), (7, '2017-01-04',10203);
--insert into tServiceJobCodes values (82),(86),(210),(215);
--insert into tservicehours values (3,82,1),(3.5,82,2),(3,82,3),(8,86,4),(5,210,5),(5,210,6),(4.5,215,7);

create table #date ([date] date);
insert into #date values ('2017-01-01'),('2017-01-02'),('2017-01-03'),('2017-01-04'),('2017-01-05');

select distinct #date.[date],tServiceJobCodes.Servicejobcode, tServiceReports.employee  into #temp from #date cross apply tServiceJobCodes cross apply tServiceReports;

with datas as (
select  date, [hours],  jobcode, employee
from tServiceReports r
join tservicehours h on h.reportno = r.reportno)

select distinct #temp.employee, #temp.[date], #temp.servicejobcode, datas.[hours] from #temp
left join datas on jobcode = #temp.Servicejobcode and datas.[date] = #temp.[date] and #temp.employee=datas.employee
where #temp.[date] between '2017-01-01' and '2017-01-05'
order by employee, servicejobcode;

drop table #date;
drop table #temp;
/* ----------------------------------------------------------------- */

The general idea here is to create a temp table containing each day for every jobcode/employee and then use that table to join on the actual data.

I hope it will help.


If you're really only looking to learn about the development side and have no interest in the administration or installation side at the moment, a quicker route might be to download a prebuilt developer VM image for Virtualbox. That can get you up and running very quickly, and you can connect to the DB running inside the VM from outside, so you can continue to do your development in your current environment.

The overview echos what others have said about licensing kicking in once you ship an application (but Oracle licensing is a quagmire, you'll need to talk to Oracle about it if/when you get past playing around):

  Learning your way around a new software stack is challenging enough
  without having to spend multiple cycles on the install process.
  Instead, we have packaged such stacks into pre-built Oracle VM
  VirtualBox appliances that you can download, install, and experience
  as a single unit. Just downloaded/assemble the files, import into
  VirtualBox (available for free), import, and go (but not for
  production use or redistribution)!

For experimenting it's ideal, in my experience. Of course, it's unsupported, but as you won't have a support contract anyway that's a bit of a moot point.

As David said, PL/SQL is integral to the database, not a separate component, so it is available in the VM image. You get some tools too, including SQL Developer, but you might have less friction running that natively and connecting it to the DB in the VM.


what is the most simple and easiest way to backup and restore on oracle 11g?

Shut down the database instance.
Copy all files to backup location.
Start up the database instance.


MYSQL binary log file not capturing in slave machine

If you are not writing directly on your slave, no binary logs will get generated by default.

To capture the binary log events from the master into the slave's binary log, you need two things:

enable log-bin
enable log_slave_updates


Does SSMS follow the same support lifecycle as SQL Server?

In the past, the releases lined up - each new SQL Server was accompanied by a new Management Studio.

However, in June of 2015 (concerning SQL Server 2016 and onwards) MS announced separate life cycles:

  With the release of SQL Server 2016 Community Technology Preview (CTP)
  2.1, customers can for the first time experience the rapid preview model for their on-premises SQL Server 2016 development and test
  environments and can gain a faster time to production.

  In addition, we are delighted to announce our first “preview” release
  of SQL Server Management Studio!  This is our first effort to release
  SQL Server Management Studio (SSMS) in a mechanism outside of the SQL
  Engine releases.  Our goal is to update this frequently with new
  features, fixes and support for the newest SQL Server features in SQL
  Server Engine and Azure SQL Database.

From the MSDN Blog on SQL Server Releases.

Based on your edit to be about support life cycles, I'm not sure that question has an answer since this new direction has been implemented (concerning future releases). As for past SSMS releases, I don't think I know of any updates or support to SSMS after the corresponding SQL Server version has been phased out - so I'd say it's safe to assume they align.


How to store international phone numbers in PostgreSQL?

Generally speaking, the presentation layer is where formatting should occur.  If spaces are only useful for presentation to the user, then reformat the phone number in the client app, and remove the spaces prior to storing them, since that will save space in the database.

Some of the questions about how to store phone numbers have been asked and answered here already.  See this and this.


What is the purpose of a user's default database?

Well Management Studio, for example, by default, only asks for (and only requires) server name and credentials. You can override these if you bother going into Options, but most people don't.

Do you really want all of your users connecting to master? In a lot of shops this can cause quite a bit of headache, especially with loose security. If users have the permission to create things in master, if they connect to master by default, they inevitably will create things there. And this can only mean cleanup will be needed later.


Permissions needed for dbms_metadata.get_ddl in a function

Based on Raj's comment, the following works:

create or replace function utilities.getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
authid current_user
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>


Backup database using logical backup device in T-SQL

To use a backup device the proper T-SQL would be:
BACKUP DATABASE test_student_db
TO WEEKLY_EXPORTS;

Check the Syntax section of the Backup doc to better understand the backup options.

It seems you're trying to develop a backup solution from scratch, so I recommend you take a look at Ola Hallengren's solution as it might save you a lot of work.


Disabling MariaDB cluster multi master mode

Yes, of course. Standard replication configuration is master and single or multiple slaves. If you use MariaDB Galera cluster you can point your applications to a single node and the other two serves as slaves.


SQL Server 2017 Express faster than Standard Edition

Wait statistics

In the execution plan of the query that performs worse we find the following wait stats:

  <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="280" WaitCount="27" />
        </WaitStats>
        <QueryTimeStats CpuTime="14" ElapsedTime="295" />

For the 295ms that the query executed, 280ms was waiting on ASYNC_NETWORK_IO.

As a result, 14ms cpu time & 15ms elapsed time without these wait stats seem reasonable to me.

These details in the actual execution plan mean that the issue is not directly related to the performance of the instance itself, but to the processing of these rows.

ASYNC_NETWORK_IO

Some parts of a previous answer on why ASYNC_NETWORK_IO occurs by Tom V

Which refers to a post by Thomas LaRock

  The ASYNC_NETWORK_IO wait indicates that one of two scenarios are
  happening. The first scenario is that the session (i.e., SPID) is
  waiting for the client application to process the result set and send
  a signal back to SQL Server that it is ready to process more data. The
  second is that there may be a network performance issue.

Most of the time I see ASYNC_NETWORK_IO happening it is due to the application not processing the rows fast enough (application design / app server under heavy load / stress).

After validating the application, the network would be the next suspect.


Is it possible to shrink an .MDF file on a drive with low free space?

To summarize the answers here and to inject my own advice:

1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.

2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.

3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.

4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.

If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.


Convert row into concatenated string with column names included

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server Management Studio.  Once you get the hang of how this works, it can become something you use almost every day.

"Drag-and-drop" the list of columns from the Object Explorer window onto a Query Window, as shown:

I'm using the dbo.spt_fallback_usg table as an example.  After I drag-and-drop them into the query window, the columns are:

xserver_name, xdttm_ins, xdttm_last_ins_upd, xfallback_vstart, dbid, segmap, lstart, sizepg, vstart

Hit [CTRL + H] to open the "Search and Replace" dialog box:

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box.  Pressing the "Replace All" button will put each column name on a new line.  Next, modify the "Search and Replace" items like this:

This will turn each individual column name into a CASE WHEN... statement.

Now replace the "newlines" with +, as in:

This results in the following code in the query window:

CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END +
CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END +
CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END +
CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END +
CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END +
CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END +
CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END +
CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END +
CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END +

This can then be manually wrapped into a SELECT statement like:

SELECT
    CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END +
    CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END +
    CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END +
    CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END +
    CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END +
    CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END +
    CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END +
    CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END +
    CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END
FROM dbo.spt_fallback_usg


How to join the updated result set of multiple servers

Something like this should do it:

create table #Tmp
(
  Drive char(1),
  MBFree int,
  ServerName varchar(50)
)

insert into #Tmp(Drive, MBFree) exec Server1.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server1' where ServerName is null

insert into #Tmp(Drive, MBFree) exec Server2.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server2' where ServerName is null

select *
from #Tmp
order by ServerName, Drive

drop table #Tmp

Result:

Drive MBFree      ServerName
----- ----------- --------------------------------------------------
C     57859       Server1
D     384636      Server1
F     82353       Server1
C     4963        Server2
E     10605       Server2

Server1 and Server2 has to be Linked Servers in the server where you are executing the above statements.


Inner Join or direct data save in SQL Server

I believe you are going to want to have things split out into multiple tables as you've described. Everything in the user table, and don't name it user lest you want to spend your application's lifetime writing [user], is going to result in every access to the database to hit one table. Imagine it's the lunch hour and everyone logs in to check their SumanSpace social network site. .5M users all reading that user table at once and then you have people creating new accounts as well as updating their existing data. The less wide you can make that base table, the better as you're going to be able to stuff more entries in an 8k page.

Plus, things like updates to Education or Location won't need to lock the main user table which could cause contention. Those locks would be on, what I assume, are less frequently accessed tables.

Specific to your tables. Education - I would look to design it as 2 - 3 tables. EducationType would be a lookup table describing the type of institution (grade school, middle school, junior high, high school, community college, military college, college, university, etc). EducationLocation to associate an institute of learning with a physical space. Something to consider here are universities with multiple locations like DeVry-is there business value in building out a location hierarchy with the parent in Downer's Grove and then all the satellite campuses listed? EducationObtained could be another lookup table, indicating the type of education obtained. For things less than the collegiate level, it'd have a value of graduated but the university level could have Bachelors, Masters, PhD, JD, Divinity (however abreviated) in Sciences, Arts, Fine Arts, etc. And of course, a cross reference table tying a user to their tenure (start and stop dates) at an institute.

Finally, you won't want an INNER JOIN as people may not have or may not choose to record their education or location information.

Those are my top thoughts before I go and get cleaned up. I'm sure @DennyCherry has some insight into the matter based on his time at MySpace.


What is the data type of the ‘ctid’ system column in Postgres?

tid

See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as tid.

  A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

You might find this similar Question interesting: How do I decompose ctid into page and row numbers?

By the way, if you are interested in this topic of ctid & tid, you might likely be interested in two new features of Postgres 12: (a) OIDs demoted to normal columns, and (b) the pluggable table storage / Access Methods feature new in Postgres 12 and later. See this, this, this, this, and this.


Question about cost optimization formula for SELECT

Assuming "first level" means the leaf level of a nonclustered (secondary) index, the b_li/2 part accounts for the cost of scanning (using sequential I/O) through half those index blocks.

Assuming "file records" means the base table, the r/2 component represents the cost of looking up additional data not present in the secondary index. Since this is a per-row cost that is expected to result in a random I/O pattern, it makes sense to express this in terms of r.


MYSQL Server shows it is master server in replication. How to turn off this?

I added below segement at the end of the my.cnf file.
It solved the problem.
[mysqld]
skip-log-bin

Currently my my.cnf file looks like:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
skip-log-bin


Openldap Replication don't run after configuring

The directives updatedn and updateref do not make sense at the provider (master).

You probably also want type=refreshAndPersist for continous replication. Setting interval= to one second would not work well with many entries in the DB.

Make sure that attributes entryUUID and entryCSN are indexed at the provider.

Also note that OpenLDAP 2.4.21 is really ancient release. During the last 9 years many things were fixed in OpenLDAP, especially related to replication.

I'd recommend to use the RPMs built by LDAP Toolbox project. They provide YUM repos also for RHEL/CentOS 6.

See also: OpenLDAP Admin Guide -- Replication


Global command to destroy all disconnected data files in Oracle 11g

Based on your description of this behaviour and your other comment, I guess your database runs on Windows.

The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation.

Before dropping tablespaces or datafiles, take them offline:

alter tablespace ... offline;

or

alter database datafile ... offline drop;

And drop the datafile/tablespace after the above:

drop tablespace ... including contents and datafiles;

or

alter tablespace ... drop datafile ...;

If they still remain there, just simply delete them at OS level. And no, there is no such command thats deletes datafiles from the database that the database does not know of.

Reference:

Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted (Doc ID 389467.1)


How to select second last row from table?

You can use this:

SELECT *
FROM table_name
ORDER BY column_name DESC
LIMIT n - 1, 1

The query just returns the first row after n-1 row(s)

For example, if you want to get the second most expensive product (n = 2) in the products table, you just use the following query:

SELECT productCode, productName, buyPrice
FROM products
ORDER BY buyPrice desc
LIMIT 1, 1

Try this. Could test here and it's ok.

Found this site helpfull


Why does ORACLE cache recovery create uncommited blocks during instance recovery?

How can a redo log file generate uncommitted data since all its information are recorded after COMMIT ?

LGWR process flushes redo logs from redo log buffer cache to online redo log files when-

A user fire commit explicitly.
One third of space in redo log buffer is filled.
Three seconds of time has passed since last redo flush.
DBWn process writes dirty buffer to disk.

These all triggers redo flush which may include uncommitted transaction.

BUT how can undo blocks can be used to rollback uncommitted changes (what are those changes anyway...) introduced by replaying redo logs during cache recovery ?

Let me give you an example，suppose you have a data block which contains one row with single column with value 5. You simply try to update it using update tbl set col1=6. During this process undo is generated to reproduce it's original value which is 5 and redo is generated to reply the transaction, means to reproduce the latest value which is 6.
Next, if this transaction is uncommitted and unfortunately instance crashes, during the next startup, Oracle(Specifically SMON process) takes the blocks which need to be recovered into the buffer cache and re-apply the changes using redo logs and if someone tries to read the row that you had modified before, Oracle simply rolled back the data block containing the row to its original value which is 5 using undo.
Update:
The following alert log excerpt is extracted from this blog.
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 80458 KB redo, 1721 data blocks need recovery
Started redo application at
Thread 1: logseq 490, block 52626
Recovery of Online Redo Log: Thread 1 Group 1 Seq 490 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 491 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 492 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 15.26MB
Completed crash recovery at
Thread 1: logseq 492, block 31015, scn 6082434
1721 data blocks read, 1721 data blocks written, 80458 redo k-bytes read
Sun Jan 09 00:50:27 2011

This clearly shows that block number 1721 needs recovery. And in order to do that the block is loaded into the memory(buffer cache) and redo is applied. The block is written back to data file after the recovery.
This is also explained in(Chapter-9 Redo and Undo, How Redo and Undo Work Together, Page ) Expert Oracle Architecture Third Edition book by Thomas Kyte.
From ASKTOM

Hi Tom,

To do the crash recovery , Database needs datablocks(needed recovery) to be brought into SGA. May i know which process will bring them?.

Thanks
Followup
smon will read them and apply redo to them during the recovery phase.

Instance/Crash Recovery


SSAS - what could I monitor in SSAS to fine tune its memory settings?

When trying to fine-tune your memory settings there are two things you'd want to watch:

MSOLAP Memory counters for your server in Performance monitor

Specific DMV queries over discover_object_memory_usage rowset and discover_object_activity rowset.

You can always use a third-party tool to monitor your resources. Also, you might want to check out this article – it gives a detailed description on how to achieve exactly what you are looking for.


Since dynamic sql is not allowed in the trigger body,you can use a statement which runs through all the columns and builds the IF statements

SELECT CONCAT('IF(OLD.',COLUMN_NAME, '=NEW.',COLUMN_NAME,' THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.' ,COLUMN_NAME,', NEW.',COLUMN_NAME,', CURRENT_TIMESTAMP(), NEW.company_id; END IF;')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='yourTable' AND column_key!='PRI';

You can narrow the results even more, take a look at COLUMNS table if you want to filter based on column type or remove columns with keys and so on.I removed the primary key columns in the last condition for example.


Mysql slave status is completely perfect but data is not reflecting

Do you use a maria-DB 10 Server with version below 10.0.18? Do your relay-log files contain any transactions? There was a bug which could lead to sich problems.


Why would Vertica let me start a transaction if I can't roll it back?

Vertica (actually, I think most if not all databases) behaves in this manner because the part that processes the BEGIN TRANSACTION SQL statement, namely the query parsing and execution engine, is not aware of the client AUTOCOMMIT setting. On the other hand, the client is not aware of the meaning of the string 'BEGIN TRANSACTION'.

The flow of control looks something like this, assuming the command line client vsql is used to run the example:

User starts vsql and connects to the database.
User enters \set AUTOCOMMIT on. After that moment the client (vsql) knows to issue an implicit COMMIT statement after each successful statement.
User enters BEGIN TRANSACTION. vsql does not recognize that string as one of its internal commands and sends the string to the server for processing.
Server recognizes the string as a valid SQL statement, compiles it, starts a transaction, and returns a successful result code to the client. Since autocommit is a client-side setting, the server has no idea about it.
Client, upon receiving the successful result code, issues COMMIT to the server.
Server complies and commits the transaction it has just started.

etc.


Mongodb what is the cost of array queries?

what is the cost of the find query (big o)?

Unindexed queries are going to involve a collection & document scan, so a search complexity of O(N*K) to iterate all documents and array field values.

Indexed queries use a B-tree search which would have a search complexity of O(log K) for a simple query and appropriate index (where K is the number of elements). MongoDB automatically flags an index as multikey if any indexed field is an array. The multikey flag is used to determine correctness of query bounds when an array field is used  in a query with multiple predicates.

  db.inventory.find( { "instock": { warehouse: "A" } } )

This query would have no results for the given example data because it is strictly matching the embedded document { warehouse: "A" } (which would not match embedded documents with additional fields like qty).

To achieve the outcome you described, the query should instead use dot notation to search the embedded warehouse values:

db.inventory.find( { "instock.warehouse": "A" } )

  Are there any indexing optimizations going on here

You can explain query results with execution stats to view the general work that goes into a given query:

db.inventory.find( { "instock.warehouse": "A" } ).explain('executionStats')

High level metrics include keysExamined (index key comparisons) and docsExamined (documents fetched). There are many other metrics that might be of interest including query stages and index bounds.

Adding an appropriate index to support your query will illustrate the difference:

db.inventory.createIndex({"instock.warehouse": 1})

The examples here are simple array queries. For details on more complex queries involving multikey indexes, see Multikey Index Bounds in the MongoDB manual. Where possible, internal optimisations attempt to minimise the range of values that need to be scanned when a multikey index is used.


sqloledb vs sqlncli - Performance difference or just feature set?

I talked to Erland Sommarskog about this and here is his response.

I attended a presentation as PASS 2005 about SQL Native Client. The
then-PM for SQL Server Client said that for ODBC they had achieved a
20% perf improvement, but for OLE DB, it was about even.

But since SQLNCLI provides more features, that is probably the provider
to use. Then again, with old ADO a lot of the features are unavailable
anyway.

HTH


PostgreSQL - maximum of sum over different months with ties over multiple years

For a given month:
SELECT tumour_count, illness
FROM (
   SELECT count(*) AS tumour_count, illness
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- given month: 2007-01
   AND    created_at  <  '2017-02-01'  -- optimized for index lookup
   GROUP  BY illness
   ) sub
WHERE  rnk = 1;

There should be an index on (created_at), or maybe even (created_at, illness) to allow index-only scans.
Subqueries are a bit faster than CTEs in Postgres. So only use CTEs where you need them, or when performance is not important.
Related:

How to optimise window queries in postgres
Count rows with max per group and additional condition

For any given period of time
As requested in the comment:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, illness
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
ORDER  BY mon, illness;

Careful if you have leading or dangling partial months, the counts may be misleading.
This is functionally equivalent to what ypercube already provided. Just some simplifications to be a bit shorter / faster. And the added filter for a given period of time.
Index support becomes less important with the growing share of rows read from the table - and stops being useful at all for more than roughly 5 %. (Exceptions apply, like for index-only scans.)
You can still have 1 row per month, by aggregating tying peers. Like:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, string_agg(illness, ' | ')
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
GROUP  BY mon, tumour_count
ORDER  BY mon;

db<>fiddle here


Verify mysqldump backup is corruption free

There are two things you want to consider

BACKUP PROCESS

There really is no substitute of doing a restore. This is just a replay of a mysqldump. If you need to do PITR, you may need to run mysqlbinlog against applicable binlogs and play the events up to the date and time needed.

You should do this periodically in case the disk where the mysqldump is located goes bad or if the mysqldump contains a sequence of characters in a BLOB making it impossible to restore.

BACKUP INTEGRITY

You should be able to start mysqld without a problem. Once you have mysqld running, you need to test each table's usability. You can do that by running this query:

SELECT CONCAT('CHECK TABLE ',dbtb,';') FROM
(SELECT CONCAT(table_schema,'.',table_name) dbtb FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

This will create a CHECK TABLE command for every MyISAM, InnoDB, ARCHIVE and CSV table to check their integrity. You can output this to a script and execute the script. The CHECK TABLE command can also allow you to check all the tables as a single command. You can change the query to collect the table names as a comma-separated list and prepend:

SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM
(SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

WARNING: The GROUP_CONCAT() function has a default limit of 1024 characters. If you have a modest number of tables, you will not list all the tables, and the last table listed will be cut off, causing an error.  You can preface your command with SET SESSION group_concat_max_len = 10000; to increase the length of the return.

Here is a sample from MySQL 5.6.16 (Windows) on my laptop:

mysql> SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM
    -> (SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('information_schema','performance_schema','mysql')) A;
+----------------------------------------------------------------------------+
| CONCAT('CHECK TABLE ',dbtblist,';')                                        |
+----------------------------------------------------------------------------+
| CHECK TABLE ayman.articles,ayman.topics,test.nuoji,test.prod,test.prodcat; |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Then, store that output to a variable and execute from the command line. Note that we have temporarily increased the group_concat_max_len before running the command.

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET SESSION group_concat_max_len = 1048576;"
SQL="${SQL} SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM"
SQL="${SQL} (SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM"
SQL="${SQL} information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')) A"
mysql ${MYSQL_CONN} -ANe"${SQL}" | mysql ${MYSQL_CONN}

Give it a Try !!!


Cost Threshold for Parallelism with Maxdop = 1

To say MAXDOP=1 is always beneficial is totally incorrect statement. MAXDOP value should be set according to SQL Server CPU core configuration followed by a good round of testing. Few applications I know that woks good with MAXDOP 1 is DynamicsAX and Sharepoint.

Yes ghost cleanup is single threaded as mentioned by Paul in this article but just because system process is single threaded IT DOES NOT means that MAXDOP=1 is going to be beneficial for you. This is what I gauge from your question. You can start with cost threshold of parallelism value =50. The value 5 mentioned is utter useless and it was for old days. With systems running on many cores now I recommend to set cost threshold of parallelism value to 50 and MAXDOP value which you can get from link shared below. See how this works for you.

We already have SE thread What is good way to calculate MAXDOP value I suggest you get MAXDOP value from queries mentioned in this link and see if that helps your environment.

Also I am not saying that you completely ignore Vendors recommendation but vendor supplies you application they cannot change SQL Server query engine behavior.


Insert rows that match a condition, and return rows that don't

You might store the records of the expensive query into a tempdb table and you can use that with your condition on the tempdb, this way you won't need to run your expensive query twice.

select * into tempdb..expensive_query from Userdb.dbo.expensive_query

Afterwards, you may use your conditional statement as below:

1st query will look like:

Insert into MyTable SELECT from tempdb..expensive_query
WHERE MyCondition;

2nd query will be:

SELECT from tempdb..expensive_query
WHERE Not MyCondition;

You may use # or ##table also instead of tempdb.dbo.table_name, this depends on whether you are doing that in the same session or multiple database sessions are required however tempdbb.dbo.table_name is safer.

You might use CTE also.

Hope above helps.


Is it possible to restore this to a different file group

No.

  possibly even on a different database)

No.


MySQL says incorrect parameter count for function I've created even tho it's right

The problem is that there was a native function distance() added in MySQL 5.7.5, which takes 2 arguments:

Distance() was added in MySQL 5.7.5.
Distance() is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. Use ST_Distance() instead.

It will be removed in a later release, but unfortunately you would need to rename your function. Had you not dropped it, you could have changed the function call in the app to include the schema:

The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions:

If you have already created a user-defined function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF and CREATE FUNCTION to re-create the UDF with a different nonconflicting name. Then modify any affected code to use the new name.

If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a nonconflicting name, or change calls to the function so that they use a schema qualifier (that is, use schema_name.func_name() syntax). In either case, modify any affected code accordingly.

[source]


Ola Hallengren Index Optimize - Skip "unused" Indexes

I don't think there is such an option, and you could submit a feature request (or a pull request) to Ola, but you can however submit a list of indexes that you want to exclude.

For example if you pass ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1  into the @indexes parameter the script will skip the index Idx1 on the object Schema1.Tbl1 in the database Db1.

The @indexes parameter is nvarchar(max) so you should be able to pass as many indexes as you like.

I realize that this may become very tedious to maintain, but you should be able to generate the list from the query you use to determine unused indexes.

This approach would however allow for a more fine-grained control over which indexes you skip and this way you could avoid skipping indexes that are only used every once in a while or you could also create different jobs so you skip large unused indexes in your normal job but still defragment some of them when you have a larger maintenance window etc.


User with dba privs can't login to APEX, but can from SQL*Plus

The APEX administrator accounts are not linked to a database schema. Reciprocally, creating a database schema won't create an APEX account.

You'll have to create the administrator account via the web interface.

Connect to the administration page:

  If your setup uses the embedded PL/SQL gateway, go to:

http://hostname:port/apex/apex_admin

  If your setup uses Apache and mod_plsql, go to:

http://hostname:port/pls/apex/apex_admin

Then On the Login page:

  In Username, enter admin.
  In Password, enter the Oracle Application Express administrator account password you specified when you installed Oracle Application Express.

There are three levels of administration, decide which type you want to create:

  Developers create and edit applications.
  Workspace administrators are developers who also perform administrator tasks specific to their workspace such as managing user accounts, monitoring workspace activity, and viewing log files. See "Workspace and Application Administration".
  Oracle Application Express administrators are superusers that manage the entire hosted instance using the Oracle Application Express Administration Services application.

I suppose you want to create an APEX administrator account. They are workspace administrator of the special workspace "Administration" that you can create following the standard APEX account creation.


Once you drop cluster for version 9.5, you may upgrade your 9.3 to 9.5 with this command:

sudo pg_upgradecluster -v 9.5 9.3 main

If you omit -v 9.5 it will upgrade the cluster to the latest postgresql version you have installed. For more information, type man pg_upgradecluster.

Now, since you already upgraded to 10, you have to stop the new cluster (version 10) and start the 9.3 again. You may do this with commands:

sudo pg_ctlcluster 10 main stop
sudo pg_ctlcluster 9.3 main start


Will DB backup time and size be decreased after truncate large tables

While it's been some time since I dealt with SQL Server backups that weren't compressed by SQL itself, SQL backs ups your data, not your free space.

As stated here

  The data contained in a full backup is not necessarily all of the
  contents of all data files. The backup will only contain the allocated
  pages from the data files.

TRUNCATE deallocates data pages. If the space consumed by your data drops from 700GB to 350GB, then the size of the backups should be about half what it was as well, and the time to take the backup should indeed be similarly reduced.


List with all visible meta data in AFTER UPDATE trigger

You could start with ORIGINAL_LOGIN(), SUSER_SNAME(), HOST_NAME() and 'APP_NAME()'.

However as @JeroenB said you may have better luck creating a trace or extended events session.  You can get the above information and more that way.  Make sure to restrict down the trace/EE session to just the one table and you shouldn't see a lot of overhead.  You could certainly stick with the trigger though and see if that gets you the information you need.  The main thing you could get out of the other options (that I would want to see) is the actual command being used.  I haven't found a way to get that out using a trigger I'm afraid.


After page compressing a huge table, the disk space didn't change. Do I need to shrink in any kind?

In order to reclaim disk space, you would have to use SHRINKFILE.  The usual caveats about incurred data fragmentation and the need to rebuild indexes to resolve that fragmentation apply.

Here is a sample query to determine which partitions are on which filegroups.
(Borrowed from Determining Filegroup for a Table)

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
    ,t.NAME AS table_name
    ,i.index_id
    ,i.NAME AS index_name
    ,p.partition_number
    ,fg.NAME AS filegroup_name
    ,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
        AND i.index_id = p.index_id
LEFT OUTER JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
    ON ps.data_space_id = dds.partition_scheme_id
        AND p.partition_number = dds.destination_id
INNER JOIN sys.filegroups fg
    ON COALESCE(dds.data_space_id, i.data_space_id) = fg.data_space_id
ORDER BY p.partition_number


Install Developer Edition ASE in Vagrant/Virtual Box

Installing Sybase within a virtual environment purely for development purposes is possible and I have used it that way for years with Windows, Linux and Solaris x86. I'm not sure about Vagrant, but with Virtual Box it works.

You must ensure your virtualised host has sufficient memory to run the database and have made the relevant additional settings.

You'll need to set the maximum amount of shared memory to a suitable value and there is probably a dependency on libaio1.

For example, add the following to /etc/sysctl.conf. If 1GB is too large you can use a smaller value. You'll need to consult the Sybase documentation for the version you're installing for a minimum value.

# Set the kernel shared memory to 1GB
kernel.shmmax=1073741824


postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client

As far as I'm aware what you're trying to do, by issuing a commit inside a procedure, is use an autonomous transaction. Postgres does not support them inside stored procedures/functions, and the behaviour you are seeing is correct - the (implicit) transaction does not complete until the procedure completes.

This page discusses the issue in detail along with potential workarounds.


Factoring out subselect (with clause) ends in ORA-00904

There seems to be an issue with the mixture of ANSI and Oracle style joins as well as the referencing of a cross joined table in a subquery.  Converting/removing these constructs, you should end up with something like this:

WITH spr0 AS (
      SELECT id FROM tka WHERE name = ?
   )
SELECT tka.id, tka.name, txt.text AS langname, einh.text AS einheit, tka.typ, vgd.a_wert,
   vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
FROM vgd vgd
CROSS JOIN spr0
JOIN vgs ON vgd.vgs_id = vgs.id AND vgs.schluessel = ?
JOIN tka tka ON tka.id = vgd.tka_id
   AND tka.txt_id_einheit = einh.id
JOIN mtdb mtdb ON tka.name = mtdb.tedabattribut
   AND mtdb.tedabattribut IS NOT NULL
   AND mtdb.baugruppe = ?
LEFT JOIN txt ON txt.spr_id = spr0.id ON tka.txt_id_langname = txt.id
LEFT JOIN txt einh ON einh0.spr_id = spr.id AND spr.name = ?
WHERE tka.status = 2 AND tka.felddimension = 0
AND NOT EXISTS
   (
      SELECT 1 FROM vgd vgd2
      WHERE vgd2.vgs_id = vgd.vgs_id AND vgd2.tka_id = vgd.tka_id AND vgd2.ai_id > vgd.ai_id
   );


A1.  You don't have to do that, it's pretty arbitrary.  The column alias just renames the id column.

A2. CONCAT() and GROUP_CONCAT are very different -- CONCAT('a','b','c') is just a function that would evaluate to 'abc'  GROUP_CONCAT() is an aggregate function that works with GROUP BY;  for each group of rows that you "roll up" into a group, GROUP_CONCAT() will create a string based on the value for each row in that group.

A3. JOIN is just shorthand for INNER JOIN

A4. ON defines how the tables are joined.  Each set of possible combination rows are examined, and each combination where the ON clause evaluates to true, then you get a match.  Visual Join Explanation

A5. I can't find your actual question here.

A6. No, a view doesn't sound like the right approach.  If you're searching for the same thing in many tables, then all that stuff should probably be in one tables.  But that's a generalization, you haven't described nearly enough about your actual project to give a clear answer.

A7.  This question is answered other places.


how I can create check sum to validate no changes at int string pairs on table

You could just use the CHECKSUM function as it returns an INT.  Just put in the columns that you want to checksum against for those tables and it'll output the checksum value for those columns.  Here's an example from AdventureWorks2012 but this function has been around for quite a while.  In this case I've got columns from two tables as part of the single checksum function call.

select loginID, checksum(LoginID, JobTitle, ShiftID)
from HumanResources.employee
join HumanResources.EmployeeDepartmentHistory on employee.BusinessEntityID = EmployeeDepartmentHistory.BusinessEntityID
and EndDate is null


Is it safe to leave/exit the stored procedure without commit or rollback on a transaction?

I would argue that transaction control does not belong to stored procedures at all, apart from some very particular circumstances (e.g. where you want to log audit records regardless of what happens to the "outer" transaction -- i.e. "autonomous transaction"). The procedure has no way of knowing if it is part of a larger unit of work and therefore, if it commits or rolls back, it might violate consistency rules of a larger transaction.
Commit or roll back at the level where business rules are implemented.
In other words, yes, in the majority of cases it's the right thing to do "to leave/exit the stored procedure without commit or rollback" and let the caller decide (provided that you don't hide the exception if it occurs).


What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I provided links to tutorials.  Just keep mind that on Ubuntu, the my.cnf file is in /etc/mysql/my.cnf and not in /etc/my.cnf like in the howtoforge tutorial.  In my setup, I didn't use FLUSH TABLES WITH READ LOCK; on the master.  If your master server has a lot of write activity, you may need to lock your tables by running that command before backing up.  If you use FLUSH TABLES WITH READ LOCK;, then after your backup, you will want to run UNLOCK TABLES.   If you run into any problems, let me know.

Here is the tutorial that I found on howto forge, made for Redhat/CentOS :
http://www.howtoforge.com/mysql_database_replication

Another tutorial that looked ok for Ubuntu
http://www.srcnix.com/2010/10/14/simple-mysql-replication-with-ubuntu-master-to-slave/

Here is the configuration I used :

On the MASTER Server

Configure the master server:

vi /etc/mysql/my.cnf

[mysqld]

# bind-address = 127.0.0.1 (comment this out)
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
max_binlog_size     = 100M
expire_logs_days    = 1

Restart MySQL:

/etc/init.d/mysql restart

Connect to mysql's console: mysql -u root -ppassword

Create and grant permissions to replication user.

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'ipaddressofslave' IDENTIFIED BY 'replicationuserpassword';

Make sure to copy this information somewhere or leave it visible

SHOW MASTER STATUS \G;
mysql> show master status \G;
            File: mysql-bin.000001
        Position: 100
    Binlog_Do_DB:
Binlog_Ignore_DB:

mysql> quit

Dump the database to a file:

mysqldump -u root -p databasename > /tmp/databasename-backup.sql

Copy the database dump to the slave server using scp or use ftp if you like:

scp /tmp/databasename-backup.sql root@ipaddressofslave:/tmp/

On the SLAVE Server

Edit the mysql configuration:

vi /etc/mysql/my.cnf
[mysqld]

# slave server configuration
server_id           = 2

# this is optional, but I find it useful to specify where the relay logs go to control.
# Don't forget to create the /var/log/mysql directory and give mysql rights to it.
# chown mysql:mysql -R /var/log/mysql
# disk space
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
relay_log_space_limit = 2000M

Restart MySQL: /etc/init.d/mysql restart

Restore the backup:

mysql -u root -ppassword nameofthedatabase < /tmp/databasename-backup.sql

Connect to MySQL:

mysql -u root -ppassword

stop slave;

# master log file and master_log_pos taken from show master status above
CHANGE MASTER TO master_host='ipaddressmaster', master_port=3306, master_user='replication', master_password='replicationuserpassword', master_log_file='mysql-bin.000001', master_log_pos=100;

start slave;

Run SHOW SLAVE STATUS\G:

mysql> show slave status\G;
             Slave_IO_State: Waiting for master to send event
                Master_Host: ipaddressmaster
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.0000001
        Read_Master_Log_Pos: 100
             Relay_Log_File: mysql-relay-bin.000001
              Relay_Log_Pos: 1
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 17324288
            Relay_Log_Space: 17324425
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
          Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.02 sec)

Afterwards, keep in mind that replication can fail for various reasons.  On the slave, you can monitor the status by running the command SHOW SLAVE STATUS \G; Or setting up a cron job to monitor the status and send emails if it fails.  Get familar with the output from this command.  If replication is running correctly, you should see "Slave_IO_State: Waiting for master to send event".

Once you get this setup correctly, I can provide you with a script to monitor that replication.

Here is a script to monitor the error log in MySQL.  If you add the line

  [mysqld]

  log-error       = /var/log/mysql/mysql.err

restart mysql : /etc/init.d/mysql restart

Then you can use the following script to monitor the log file.  If the log changes in any way, you will receive an email notifying you that an error occured on the slave server.  If you want the error log checked on a regular basis, you will need to add this script to your crontab.

Here is a sample script : /somepath/monitor_mysql_log.sh

#! /bin/sh
MAIL_TO="addressemail@something.com"

# This is the log that will be monitored.
# If any changes occur to this, then take appropriate action.
MONITORED_LOG=/var/log/mysql/mysql.err

# We will need this log to see whether any changes occured to /tmp/goreb.log
TEMP_LOG=/tmp/.mysql.err.1

# This is a 1-time command i.e. create the log file if it does nto exist.
[ ! -f $TEMP_LOG ] && touch -r $MONITORED_LOG $TEMP_LOG

[ $MONITORED_LOG -nt $TEMP_LOG ] && echo "an error occurred in mysql" | mail -s "Error on MySQL" $MAILTO

# Update $TEMP_LOG with the new modified date of $MONITORED_LOG
touch -r $MONITORED_LOG $TEMP_LOG

To add to crontab.

Make the script executable:

chmod +x /somepath/monitor_mysql_log.sh

Update crontab:

crontab -e

* * * * * /somepath/monitor_mysql_log.sh

And the script will be run every minute.

The script I provided is a script that I just quickly put together.  Also, in order for your server to be able to send emails, you'd have to install something like postfix or sendmail.


How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character.

Windows and SQL Server started out using the UCS-2 encoding because it was available and UTF-16 hadn't been finalized yet. Fortunately, however, there was enough fore-thought put into the designs of UCS-2 and UTF-16 that the UCS-2 mappings are a complete subset of the UTF-16 mappings (meaning: the 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is UCS-2). AND, the 65536 - 1114111 (0x10000 - 0x10FFFF) range of UTF-16 is constructed from two Code Points in the UCS-2 range (ranges 0xD800 – 0xDBFF and 0xDC00 – 0xDFFF, specifically) that were reserved for this purpose and otherwise have no meaning. This combination of two Code Points is known as a Surrogate Pair, and Surrogate Pairs represent characters beyond the UCS-2 range which are known as Supplementary Characters.

All of that information explains two aspects of NVARCHAR / Unicode data in SQL Server:

Several built-in functions (not just NCHAR()) don't handle Surrogate Pairs / Supplementary Characters when not using a Supplementary Character-Aware Collation (SCA; i.e. one with _SC, or _140_ but not _BIN* in the name) because the non-SCA Collations (especially the SQL_ Collations) were originally implemented prior to UTF-16 being completed (sometime in 2000, I believe). The non-SQL_ Collations that have _90_ or _100_ in their names but not _SC have minimal support for Supplementary Characters in terms of comparison and sorting.
The full Unicode / UTF-16 character set can be stored, without any data loss, in the NVARCHAR / NCHAR / XML / NTEXT datatypes because UCS-2 and UTF-16 are the exact same byte sequences. The only difference is that UTF-16 makes use of the surrogate code points to construct Surrogate Pairs, and UCS-2 simply can't map them to any characters, hence they appear to the built-in functions as two unknown characters.

With that background info in mind, we can now go through the specific questions:

  I would like SELECT NCHAR(128512); to return the same as this: SELECT N'';

That can only happen if the current database — where the query is being executed — has a default Collation that is Supplementary Character-Aware, and those were introduced in SQL Server 2012. Built-in functions that have string input parameters can have the Collation provided inline via the COLLATE clause (i.e. LEN(N'string' COLLATE Some_Collation_SC) ) and don't need to be executed within a Database that has an SCA default Collation. However, built-in functions such as NCHAR() accept an INT input parameter and the COLLATE clause isn't valid in that context (which is why NCHAR() only supports Supplementary Characters when the current database has a default collation that is Supplementary Character-Aware; but this is an unnecessary inconvenience that can be changed, so please vote for my suggestion: NCHAR() function should always return Supplementary Character for values 0x10000 - 0x10FFFF regardless of active database's default collation) .

  Is there an explanation for why, regardless of collation, SQL Server can understand and deal with the extended characters except from the perspective of NCHAR?

How SQL Server can store and retrieve Supplementary Characters without data loss was explained in the top section of this answer. But, it is not true that NCHAR is the only built-in function that has issues with Supplementary Characters (when not using an SCA Collation). For example, LEN(N'' COLLATE SQL_Latin1_General_CP1_CI_AS) returns a value of 2 while LEN(N'' COLLATE Latin1_General_100_CI_AS_SC) returns a value of 1.

If you go to the second link posted in the Question (i.e. "Microsoft's Supplementary Characters Collation Information") and scroll down just a little, you will see a chart of the built-in functions and how they behave based on the effective Collation.

  How do I find a collation which has the "supplementary character" flag?

In a version of SQL Server prior to 2012 you can't. But, starting with SQL Server 2012, you can use the following query:

SELECT col.*
FROM   sys.fn_helpcollations() col
WHERE  col.[name] LIKE N'%[_]SC'
OR     col.[name] LIKE N'%[_]SC[_]%'
OR     (COLLATIONPROPERTY(col.[name], 'Version') = 3
      AND col.[name] NOT LIKE N'%[_]BIN%');

Your query was close, but the pattern started with SQL and the SQL Server Collations (i.e. those starting with SQL_ ) have been deprecated for a while in favor of the Windows Collations (those not starting with SQL_ ). So, the SQL_ Collations are not being updated and hence have no newer versions that would include the _SC option (and starting in SQL Server 2017, all new collations automatically support Supplementary Characters and do not need, or have, the _SC flag; and yes, the query shown immediately above accounts for that as well as picking up the _UTF8 collations added in SQL Server 2019).

  Can you install collations on older instances?

No, you cannot install Collations into a previous version of SQL Server.

  How can I set a Unicode string variable (e.g. nvarchar) to a Supplementary Character using code (without using the actual Supplementary Character) in a database where the collation "does not contain the supplementary character (SC) flag"?
  ...
  Although the server is SQL Server 2008 R2, I'm also curious about any solutions for later versions.

When not using an SCA Collation, you can inject Code Points above 65535 / U+FFFF in two ways:

Specify the Surrogate Pair in terms of two calls to the NCHAR() function, each with one part of the pair
Specify the Surrogate Pair in terms of converting the VARBINARY form of the Little Endian (i.e. reversed) byte sequence.

These two methods of inserting Supplementary Characters / Surrogate Pairs will work even if the effective Collation is Supplementary Character-Aware, and should work just the same across all versions of SQL Server, at least as far back as 2005 (though probably would also work in SQL Server 2000 as well).

Example:

Character:

Name:                Pile of Poo
Decimal:            128169
Code Point:       U+1F4A9
Surrogate Pair: U+D83D & U+DF21

SELECT N'', --
       UNICODE(N'' COLLATE Latin1_General_100_CI_AS), -- 55357
       UNICODE(N'' COLLATE Latin1_General_100_CI_AS_SC), -- 128169
       NCHAR(128169), --  in DB with _SC Collation, else NULL
       NCHAR(0x1F4A9), --  in DB with _SC Collation, else NULL
       CONVERT(VARBINARY(4), 128169), -- 0x0001F4A9
       CONVERT(VARBINARY(4), N''), -- 0x3DD8A9DC
       CONVERT(NVARCHAR(10), 0x3DD8A9DC), --  (regardless of DB Collation)
       NCHAR(0xD83D) + NCHAR(0xDCA9) --  (regardless of DB Collation)

UPDATE

You can use the following iTVF to get the Surrogate Pair values (in both INT and BINARY form) from any Code Point between 65536 - 1114111 (0x010000 - 0x10FFFF). And, while the input parameter is of type INT, you can pass in the binary / hex form of the Code Point and it will implicitly convert to the correct integer value.

CREATE FUNCTION dbo.GetSupplementaryCharacterInfo(@CodePoint INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

WITH calc AS
(
  SELECT 55232 + (@CodePoint / 1024) AS [HighSurrogateINT],
         56320 + (@CodePoint % 1024) AS [LowSurrogateINT]
  WHERE  @CodePoint BETWEEN  65536 AND 1114111
)
SELECT @CodePoint AS [CodePointINT],
       HighSurrogateINT,
       LowSurrogateINT,
       CONVERT(VARBINARY(3), @CodePoint) AS [CodePointBIN],
       CONVERT(BINARY(2), HighSurrogateINT) AS [HighSurrogateBIN],
       CONVERT(BINARY(2), LowSurrogateINT) AS [LowSurrogateBIN],
       CONVERT(binary(4), NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT)) AS [UTF-16LE],
       NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT) AS [Character]
FROM   calc;
GO

Using the above function, the following two queries:

SELECT * FROM dbo.GetSupplementaryCharacterInfo(128169);

SELECT * FROM dbo.GetSupplementaryCharacterInfo(0x01F4A9);

both return the following:

CodePoint  HighSurrogate  LowSurrgate  CodePoint  HighSurrgate  LowSurrgate  UTF-16LE   Char
INT        INT            INT          BIN        BIN           BIN                     actr
128169     55357          56489        0x01F4A9   0xD83D        0xDCA9       0x3DD8A9DC

UPDATE 2: An Even Better Update!

I have adapted the iTVF shown above to now return 188,657 code points so you don't need to fit it any particular value. Of course, being a TVF, you can add a WHERE clause to filter on a particular code point, or range of code points, or "similar characters", etc. And, it includes additional columns with pre-formatted escape sequences to construct each code point (both BMP and Supplementary Characters) in T-SQL (without requiring an "_SC" or "_140_" collation), HTML (and XML), the style common to many app languages ("\uHHHH" ; used for C++ / C# / F# / Java / JavaScript / Julia / etc), and finally the slightly newer, other common style that handles all code points, not just BMP ("\UHHHHHHHH" ; used for C / C++ / C# / F# / Julia / etc).

Read all about it here:

SSMS Tip #3: Easily Access/Research ALL Unicode Characters (Yes, Including Emojis 😸)


Best database choice for a highly scalable web ticketing system?

Scalability is factor for every business that expects large growth. Ultimately only you and your team can answer the question with whatever is right for your pocketbook and time considerations. The best we can do is offer some options.

You could use a master MySQL database for the writes and then implement replication to create multiple, read-only slaves.

Or MySQL for the write database and then create a custom ETL application to convert the transactional data into read-only and place it into a NoSQL database like Cassandra. I've found Cassandra to be optimal for tons and tons of reads (which is great if people are performing searches on your content), but it will require you to be pretty savvy in the database realm.


SQL Server Audit not tracking changes at the database level

I think the main problem here - and please don't take offense - is that you're pointing and clicking in a GUI but not really sure what you're pointing and clicking at.

Here is an example that creates a server-level audit, then adds a database-level audit specification to track multiple operations on any object in the dbo schema.

USE master;
GO

-- create aserver audit
CREATE SERVER AUDIT Test_Server_Audit
  TO FILE ( FILEPATH = 'C:\temp\' ); -- you may need to change that'
GO

-- turn it on
ALTER SERVER AUDIT Test_Server_Audit WITH (STATE = ON);
GO

-- create a demo database
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.blat(x INT);
GO

-- create a database audit specification that monitors for activity
-- against any dbo object:
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT, UPDATE, DELETE, INSERT, EXECUTE ON SCHEMA::dbo BY PUBLIC)
    WITH (STATE = ON);
GO

-- do a couple of things:
SELECT * FROM dbo.blat;
DELETE dbo.blat;
GO

-- you should see those couple of things in the audit file:
SELECT * FROM sys.fn_get_audit_file('C:\temp\*.sqlaudit', NULL, NULL);
GO

Now, clean up:

ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE floob;
GO

ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

I'd start at perhaps a higher conceptual level before pressing a bunch of buttons and hoping that they work.