If you're using SQLPlus, you can use a variable & it will prompt you.

delete FROM activ_princ where id in
    (select ap.id FROM activ_princ ap
        inner join sect e on e.id=ap.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=&1);
delete FROM activ_sec where id in
    (select asec.id FROM activ_sec asec
        inner join sect e on e.id=asec.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=&1);
delete FROM sect where id in
    (select e.id FROM sect e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=&1);

See the documentation here.


mongodb wt file corrupt issue, can not query few documents

Ok we "fixed" the issue or atleast solved the problem of Mongo crashing

The wt collection file was corrupt and was not readable.
We run ddrescue and got rid of the bad sections in the file

ddrescue -n -e1 /database/mongocluster/shard2/b0/collection-72--3831537583791950739.wt RecoveredFiles/collection-72--3831537583791950739.wt RecoveredFiles/collection-72--3831537583791950739.wt.log

And then run wt to salvage the file

./wt  -v -h /database/mongocluster/shard2/b0 -C "extensions=[./ext/compressors/snappy/.libs/libwiredtiger_snappy.so]" -R salvage collection-72--3831537583791950739.wt

Thats it!!. The bad documents could not be recovered, but never mind I can regenerate that data from my app. Most importantly the mongo server does not crash. Hopefully mongodb will improve the logic in the newer versions. Crashing the db on any single I/O error is not very helpful


Postgres - Regroup rows with at least one common attribute

This was tricky but fun. Using a recursive query:

with recursive
  v(l, f) as (
    -- select Letter, Figure from table
    values ('A', 1), ('B', 1), ('B', 2), ('C', 2), ('C', 3),  ('D', 3), ('E', 4)
    ),
  r as (
    select *
    from (
      select array[l] as l, array[l] as al, array[f] as f, array[f] as af, 1 as g, 1 as n
      from v order by l, f limit 1
      ) a
    union all
    select CASE WHEN g.newgrp THEN g.l ELSE r.l || g.l END, r.al || g.l,
           CASE WHEN g.newgrp THEN g.f ELSE r.f || g.f END, r.af || g.f,
           CASE WHEN g.newgrp THEN g + 1 ELSE g END, n+1
    from r
    join lateral (
      select CASE WHEN newgrp THEN ARRAY(SELECT l FROM v WHERE l<>ALL(r.al || g.l) AND f<>ALL(r.af || g.f) ORDER BY l, f LIMIT 1) ELSE l END AS l,
             CASE WHEN newgrp THEN ARRAY(SELECT f FROM v WHERE l<>ALL(r.al || g.l) AND f<>ALL(r.af || g.f) ORDER BY l, f LIMIT 1) ELSE f END AS f,
             newgrp
      from (
        select l, f, g.l = '{}' AND g.f = '{}' as newgrp
        from (
          select COALESCE(ARRAY(SELECT l FROM v WHERE f =ANY(r.f) AND l <>ALL(r.l)), '{}') as l,
                 COALESCE(ARRAY(SELECT f FROM v WHERE l =ANY(r.l) AND f <>ALL(r.f)), '{}') as f
          ) g
        ) g
      ) g on (true)
    where (not g.newgrp or (g.newgrp and g.l <> '{}'))
    ),
  g as (
    select distinct on (g) l, f, g
    from r
    order by g, n desc
    )
select v.l as Letter, v.f as Figure, g.g AS Groupment
from v
join g on (v.l =any(g.l) or v.f =any(g.f))
order by 3, 1, 2


Calculating Median Value within a Group By

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- replace WITH (nolock)

SELECT [Time]
       ,AVG(NULLIF(Duration,0)) AS [Mean Response Time]
       ,AVG(CASE WHEN Rn in (Round(CN/2.0,0),Round((CN+1)/2.0,0))
                 THEN Duration END) [Median]
FROM
(
  SELECT [Time], Duration,
         Rn = Row_Number() over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
                                         Time
                                 order by Duration),
         Cn = Count(1) over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
                                         Time)
  FROM (
    SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time],
           Duration
    FROM [Application].[Exchange]
    WHERE [Started] >= '20121024' AND [Started] < '20121025'
  ) X
) Y
GROUP BY [Time]
ORDER BY [Time];


This term relates to the process of identifying the significant events in the transaction life-cycle, including error-processing, auditing, save-points, nested transactions, and distribution paths for distributed transactions.

I think you may find more results with the synonymous term Transaction Life-cycle Analysis.


You just need AVG(m.price) in your select list.


Postgresql Upgrade from 9.1.9 to 9.4.5 pg_stat_activity.procpid causes failure

Apparently, you have a view, blocking_procs on top of pg_stat_activity.  You will need to drop that view and reinstall it after the upgrade.

DROP VIEW blocking_procs;


Add --master-data to the mysqldump. That way the change of slave position to where you did the mysqldump gets executed when you load it.


How to determine if open_table_cache is set properly?

You have not mentioned but looks like you are using MySQL 5.6 version.
Opened_tables is a counter variable which stores the value of all tables opened by queries since start of the MySQL service.

What does 'very large' mean?

This Value can be grown up to few millions.

What does 'too rapidly' mean?

To check whether this value is growing rapidly or not, just run the SHOW GLOBAL STATUS LIKE 'Opened_tables'; query at the interval of each second. You will be able to see how many tables are being opened per second?

There is not a single variable or factor which is responsible for open_table_cache or table_open_cache.
(1) open_files_limit: MySQL has clearly mentioned in its documentation about it - Increasing this value increases the number of file descriptors that mysqld requires.
As per the storage engine, MySQL maintains file descriptors for each table. So, you must need to check system variables open_files_limit. This link provide you importance about this variable. Luckily, this variable can be increased or decreased in MysQL5.6 on Windows server. At the start of the service, MySQL automatically adjust this value <= specified value of open_files_limit.

(2) Query Execution Plan: What is your query and how many tables are getting used? How efficient indexes are getting utilized during query execution.

(3) Table Size: Number of records in a table. How big or small it is?

(4) RAM: RAM is the physical memory and cache size depends upon the RAM size. In performance of and application as well as speed up the query execution, it plays a vital role.

This is not the exact answer but a helpful suggestion.


shutdown immediate will end idle sessions, but my understanding is that it will not kill of any statements which are in the middle of processing. But once issued, it will stop any new sessions from starting (thus you cannot connect any more).

So you can either wait for the operations to finish, or try shutdown abort.


Setting up AG Groups with Current Failover Cluster

For your first question, yes you can use the same environment (Windows: 2012 R2 Standard and SQL 2014 Enterprise Edition) but the configuration should be changed. During failover clustering, primary and secondary servers shares the same resources or drives, this didn’t work for Always on configuration, and both primary and secondary replicas has to have their own drives.

For your second question, it depends on your preference and available resources, if you think that it’s easy just to reset drives on both servers and change configuration you could do that. If you have sufficient resources, space in SAN and feels more confident to start a fresh install you might chose the second option.

Concerning about the licensing. Assuming you are planning to use secondary node only for disaster recovery or short maintenance time, you need licensing only for a primary node. However, if you are planning to query from secondary node, you are also required a license for the secondary node too.


Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

The db_recovery_file_dest includes a variety of files, including online AND archived redo log files; take a look at the Oracle Backup and Recovery User's Guide chapter 5 to find out the best solution; disabling db_recovery_file_dest is not a good idea.  You can try

RMAN> report obsolete;

RMAN command to see what can be deleted. Then do a delete obsolete to delete them. Do you have any guaranteed restore points defined?

RMAN> list restore point all;

If you see any results with type GUARANTEED, if they are no longer needed, remove them:

SQL> DROP RESTORE POINT before_app_upgrade;

Is flashback database enabled?


What is Diagnostic.data file in MongoDb data folder?

The diagnostic.data file contains the result of db.serverStatus() command in binary format. This will be used by the MongoDB engineers to analyze the behavior of the server if any error happens.

It is not necessary to take a backup of this file.


Unable to deny remote access to Postgresql even if there is no entry in pg_hba.conf

The IP address should be followed by either a slash and a mask length (like /32) with no whitespace, or by whitespace and a netmask (like 255.255.255.255).  What you seem to have here is whitespace and mask length.  So you get a mask length being interpreted as a netmask.  I am surprised that that doesn't throw an error upon parsing, but in any event it doesn't do what you want.


Query notifications and connection options

The explanation to this problem turned out to be an issue with another part of the application explicitly opening read uncommitted connections. These read uncommitted connections were then left in the connection pool and reused by service broker, which can't operate using this isolation level.

The solution was simple: Make sure service broker is connecting with a different connection string, thereby using a different connection pool.


Time addition based on previous column partition by different object


Table Partitioning. What is the correct process for deleting .ndf and .ldf files?

You can't delete the LDF file: it's essential.

For the NDF, you need to show that it's not used to SQL Server. You'd use DBCC SHRINKFILE with EMPTYFILE. Substitute xxx based on sys.database_files

DBCC SHRINKFILE (xx, EMPTYFILE)


Postgres Error: must appear in the GROUP BY clause or be used in an aggregate function

Have a look at Postgres docs about GROUP BY:

  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

According to this you cannot reference a field on the SELECT statement if it doesn't appear on the GROUP BY clause or without using an aggregated function.

In your example, you're referencing p_search.id but it doesn't appear on the GROUP BY clause. You could try to change it by kia.image_id


Why use an intent lock at a higher level in the hierarchy, why not just a regular lock?

I think I have worked out the answer (Please correct this if my understanding is wrong)
After a bit more reading, I found a simple matrix here:

It appears that the X lock is less compatible with other locks than the the IX lock.
If we were to put an X lock on a table due to reading one row, we couldn't get an IS lock on that table but if we have an IX lock on the table we can get a IS lock.
If I understand correctly then, If we were to lock the table with an X lock to update one row, we couldn't read a different row because the read couldn't place an IS on the table. However, if we use an IX, it can get the IS lock it needs.
The following test seems to prove this is the case:
Update the single row:
USE AdventureWorks2016;

BEGIN TRAN;
UPDATE  Person.Person
SET     LastName = 'Smith'
WHERE   BusinessEntityID = 1;

in another session, SELECT a different row to the one being updated
SELECT * FROM Person.Person WHERE BusinessEntityID = 2

this row returns without being blocked
However, if I was to gain an X lock:
USE AdventureWorks2016;

BEGIN TRAN;
UPDATE  Person.Person WITH (TABLOCKX)
SET     LastName = 'Smith'
WHERE   BusinessEntityID = 1;

this query is now blocked:
SELECT * FROM Person.Person WHERE BusinessEntityID = 2


Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)?

FILETABLE data is built upon the FILESTREAM feature of SQL Server, and uses the Windows Server filesystem to store VARBINARY(MAX) data in discrete NTFS files on the filesystem of the server itself.  This data is not stored in the database, therefore the database file size will not include these 10GB+ files.  Database backups will, by default, include the data.  You can prevent SQL Server backups of the filestream data by using partial backups to exclude the filestream filegroups.  Filestream data is not cached using SQL Server buffers, the operating systems file cache is used instead.

Microsoft recommends AGAINST using FILESTREAM to store files that are on average less than 1MB since this negates the efficiency gained by using the Win32 file APIs to modify the data involved.  The maximum file size for a FILESTREAM file is only limited by the operating system maximum file size, which for current versions of NTFS is for all practical purposes unlimited (16 Exabytes).  There are, by design, no limitations placed on the size of a FILESTREAM object by SQL Server.

If the meta data you intend to store in SQL Server must be transactionally consistent with the object data stored in the FILESTREAM you should use FILETABLE/FILESTREAM.  If the object data stored in these 10GB+ files is not logically related to meta data stored in SQL Server, I would expect using FILESTREAM might be counterproductive.

In short, it depends on your exact use-case.  Add more details to your question about why you want to use a FILETABLE.

FILESTREAM docs at Microsoft:  http://technet.microsoft.com/en-us/library/gg471497.aspx

[Edit]

In light of your requirements to store binary X-Ray data along with machine parameters used to capture the x-ray data, it sounds like the precise use-case for a FILESTREAM.  However, instead of archiving to TAPE, I'd consider using DataCore SANSymphony-V Software SAN with multiple storage tiers.  SANSymphony-V allows you to easily and reliably expand your SAN with inexpensive commodity Windows-based servers using 15k RPM Enterprise drives (with an SSD tier, possibly) for the main database along with a tier configured with standard 7200 RPM SATA drives for storage of the x-ray data.  SANSymphony-V is SQL Server certified and would provide a relatively inexpensive way of maintaining access to both the new data and the older data without any complicated data-management system.  If Disaster Recovery and High Availability is a concern, the entire SANSymphony-V SAN can be mirrored, either on-site, or off-site at distances of up to around 100km.

Just for clarity, I am not in any way associated with DataCore - I simply love their design.


I think that you are confusing the presentation layer with the database layer.

In the presentation layer, repetitive information would be removed or reduced, perhaps a group header indicating the user and then a details section indicating the pages associated with that user.  This is the way that the information would best be presented to a person, in any case.

However, the dataset that underlies that presentation would look as you describe with the user information repeated for each page.  You get the same repetition of data if you reverse the joins.

SELECT P.page, U.user FROM Pages P INNER JOIN Users U ON U.user_id = P.user_id.

is equivalent to

SELECT P.page, U.user FROM Users U INNER JOIN Pages P ON P.user_id = U.user_id

So yes, it is a little illegible but it's meant to be fast and accurate.  Formatting the data relies on a deeper understanding of what the data actually means and desired presentation.


Can I query Microsoft GP database and save the result as MDF file?

The way to handle this is to create a new database with a different name (for example, MyDB_Export), then use the Export functionality in Sql Server Management Studio - right-click on the database in SSMS, and click Tasks / Export Data... on the pop-up menus. The only thing you will do is to specify a query to select the data to export to the new DB.

Its pretty intuitive to walk through the steps, so you should be able to work out how to complete this easily once you are in the Import/Export Wizard. If not, just add a comment below and I'll expand on this.


DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

The timing you see is given by pgAdmin (but it could be any other client) - that means it displays the time it needed to get and render the output. As you know the time needed for the database to produce the data (by using EXPLAIN ANALYZE), the difference you see must come from the transfer and/or rendering. You show less columns in the first query, that might be a reason, for example.

If you want to have an idea about how much time is consumed during the transfer, you can time the execution of the query from an application.  If you only fetch the data but don't process it in any way (not to mention render it), you get a good approximation on the time needed for transferring the data.  Just take the time needed for fetching the data and subtract the (already known) execution time of the database.

This way you will have an idea about the rendering time of pgAdmin, too, by comparing the above numbers to the one it gives you.


Since your data types are already decimal types, perhaps you need to remove NULL values with a WHERE clause instead of coalescing them to 0, which would result in the "divide by zero" error.

Something like:

SELECT ((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0)
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor
FROM  <...>
WHERE PO.ActualQty IS NOT NULL
    AND P.QuantityPO IS NOT NULL


Shared memory error while connecting to Oracle

Looks like there are some hoops with fire you need to jump thru for installing Oracle XE on Ubuntu. You may want to try following the article here: http://meandmyubuntulinux.blogspot.com/2012/05/installing-oracle-11g-r2-express.html

Oracle is very finicky when it comes to installing on particular OS's. It's very specific as to what they support. However, that doesn't mean you can't install it on any OS you want. You'll just need to jump thru a few hoops... Some with fire.

Hope it helps!


Startup Parameters greyed out in SSCM for new instance

As stated in the comment, please check your privilege. This change you are trying to make will write startup parameters to the registry and need privilege beyond member of sysadmin role in SQL Server.

Ref: SCM Services - Configure Server Startup Options

Limitations and Restrictions

  SQL Server Configuration Manager writes startup parameters to the
  registry. They take effect upon the next startup of the Database
  Engine.

  On a cluster, changes must be made on the active server when SQL
  Server is online, and will take effect when the Database Engine is
  restarted. The registry update of the startup options on the other
  node will occur upon the next failover.

Security

Permissions

Configuring server startup options is restricted to users who can
change the related entries in the registry. This includes the
following users.

Members of the local administrators group.
The domain account that is used by SQL Server, if the Database Engine
is configured to run under a domain account.


I've been having the exact same issues on my OpenVZ VPS's recently. Both running Debian 7 (official OpenVZ template), both with MySQL 5.6.23 from the Dotdeb repository. Random InnoDB corrupted tables, with the dump in the error log showing strange data that is definitely not part of any of my own databases, nor any files stored in my container.

Both started only recently (within the past month or so), one server was running fine for over a year prior, the other was a brand new VPS with fresh install of Debian.

My KVM VPS's running the exact same configurations have been fine thus far.

I'm sorry I can't offer a solution, but now I at least know that this is most likely not something that I caused. It has been extremely frustrating trying to figure out what is happening.

I'll let my host know that you have been having the exact same problems. Perhaps with this info they can figure out what the heck is going on. If they and I can get to the bottom of this I'll definitely let you know.


Realtime Update MySQL on every Update occurred on PostgreSQL

Two basic approaches.

1) Connect the servers via ForeignDataWrapper, DatabaseLink, Linked Server, External Table, or whatever your source system calls their technology.

2) Replication

Replication changes can be collected by triggers, or puled from database logs.  High throughput systems should use a log based solution, although those usually cost money (Oracle GoldenGate, Sybase RepServer, IBM IIDR, etc).  You may want to check out https://www.symmetricds.org as an open source alternative.

The downside to #1 is that your transaction success may (or will?) be dependent on BOTH systems being available.  Also, you will take a performance hit to update both systems at once.


Maintenance plan to delete backup and restore history not working

In the Maintenance plan job, create an output file to see what exactly is going on during the job run. Mostly there is something missing.
Drop and recreate the plan and test.


What happens when a file size limit is reached?

No, the two are unrelated.

TempDB is used for explicit temp tables, but also for sorting and database snapshots and lots of other internal things. Was there some other very large transaction going on when the TempDB filled up?

If a transaction needs more data space (or log space, or TempDB space) but the file is set to not grow (or there is insufficient space on disk), the transaction will fail and roll back. So no new data will be added.


One table with 2 foreign key mapping same primary key

Try this

SELECT
    status,
    schedule_switch.Switch_Employee_ID,
    schedule_switch.Requesting_Employee_ID,
    empl_info1.FirstName,
    empl_info2.FirstName, //added the modified join select column
    DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate,
    DATE_FORMAT(time_slot.date, '%m-%d-%Y') AS date,
    start_time,
    end_time
FROM work_schedule
    JOIN time_slot ON work_schedule.Slot_ID = time_slot.Slot_ID
    JOIN manager_info ON manager_info.Manager_ID = time_slot.Manager_ID
    JOIN schedule_switch ON work_schedule.Schedule_ID = schedule_switch.Schedule_ID
    JOIN employee_info empl_info1 ON schedule_switch.switch_Employee_ID = empl_info1.Employee_ID
    // modified your join
    JOIN employee_info empl_info2 ON schedule_switch.requesting_Employee_ID = empl_info2.Employee_ID
WHERE status IS NULL
ORDER BY switchdate;


How to efficiently combine user authentication with token authentication?

Apparently this use case is fairly specific and thus it is quite a challenge to write an "upsert" statement what covers all the steps as described in the question. Therefor I see the following procedure as the only way out.
create or replace function user_auth(p_user text, p_pass text)
returns token_response as
$$
declare
    v_user_id bigint;
    v_token_id bigint;
    v_updated_on timestamp with time zone;
    v_access_token text;
    v_refresh_token text;
begin
    select id
    into v_user_id
    from users
    where username = p_user
        and password = validate_password_hash(p_pass, password)
    limit 1;

    if (v_user_id is not null) then
        select id, updated_on, access_token, refresh_token
        into v_token_id, v_updated_on, v_access_token, v_refresh_token
        from tokens
        where user_id = v_user_id
        order by updated_on desc
        limit 1;

        if (v_token_id is null) then
            insert into tokens (user_id)
            values (v_user_id)
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;

        if (v_updated_on < current_timestamp - interval '1 day') then
            update tokens
            set access_token = generate_random_token(),
                refresh_token = generate_random_token(),
                updated_on = current_timestamp
            where id = v_token_id
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;
    end if;

    return (v_access_token, v_refresh_token)::token_response;
end;
$$ language plpgsql;

Here is a working DB fiddle


Conditionally restore based on which job is running on the principle

If I understand you correctly, I would think that the primary mirror should be the one doing the restoring. If this is the case I would modify your job(s) like so:

if ((select mirroring_role from sys.database_mirroring where db_name(<database id>) = '<database name>') = 1)
    Restore Database XYZ from disk = 'C:\XYZ.bak'

Not knowing your full setup, I "would think" both servers could run this job and it wouldn't matter which job got there first or the timing of the jobs.

Note:

A mirroring_role of 1 signifies the principle whereas a mirroring role of 2 signifies the secondary role.

Edit based on your comment:

If you'd rather keep your jobs "as is", even though one of them sounds redundant, just stack them together in one job with two job steps. If the first step fails, execute the second step. But if the first job step succeeds, don't run the second job step.

I still think conditionally restoring is your best bet, but I'd have to see your job code on both servers to know exactly what your doing. Having both jobs compete to restore the same database doesn't sound like the very best of plans to me.


Possible reasons for irregularly high query execution time?

That spike is probably caused by dirty data pages being flushed to disk. Raise the checkpoint_segments parameter in the postgresql.conf file.

http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

http://www.postgresql.org/docs/current/static/wal-configuration.html


Overlapping ranges is an especially difficult problem to optimize.  However, here is a technique that provides significant performance, but it requires major change to the schema.

Add another table; let's call it Prefixes.  It contains two columns:

prefix DECIMAL(4,0) NOT NULL,
range_id INT,   -- for JOINing to `ranges`
PRIMARY KEY(prefix)

Then, to look for the number 4432109..., first look up 4432 in Prefixes.  This will lead to one or morerange.id` values for checking in your existing table.

Note that, for your example, there will 100 entries in Prefixes for 'UK' and 10 entries for 'UK Vodafone'.  This implies extra code to maintain Prefixes.

A variant on this is to move the rest of the ranges columns into my new table, and get rid of the table ranges.  (Whether to do this depends on the number and nature of the columns, the hassle in the code, the size of the 'prefix', the size of the table(s), etc, etc.)


View locks acquired during query execution (SQL Server)

is it possible to view the locks, along with the type, acquired during the execution of a query?

Yes, for determining locks,

You can use beta_lockinfo by Erland Sommarskog

beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

Another method is to use sp_whoIsActive by Adam Machanic with @get_locks = 1
EXEC sp_WhoIsActive
@filter = '',
@filter_type = 'session',
@not_filter = '',
@not_filter_type = 'session',
@show_own_spid = 0,
@show_system_spids = 0,
@show_sleeping_spids = 1,
@get_full_inner_text = 0,
@get_plans = 1,
@get_outer_command = 1,
@get_transaction_info = 0,
@get_task_info = 1,
@get_locks = 1,   ----------> 1 = ON (get lock info); 0 = OFF
@get_avg_time = 0,
@get_additional_info = 0,
@find_block_leaders = 0,
@delta_interval = 0,
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
@sort_order = '[start_time] ASC',
@format_output = 1,
@destination_table = '',
@return_schema = 0,
@schema = NULL,
@help = 0


Oracle: How to create secondary index based on every word in a CLOB

You probably want to use a contextual index. Then when you search in the clob column you would do something like

myclob contains ( 'aword') > 0

This would then be a substitute for regexp. I have not used this
feature in a few years, but it does take some work to create and
maintain the indexes. You should look for specific examples that
people created and used the index.

Indexing with Oracle Text


Error 1064 - You have an error in your SQL syntax;

The syntax is not:

SELECT id INTO @x, data INTO @y ...

but it is:

SELECT id, data INTO @x, @y ...

Use:

SELECT SUM(avg_score), COUNT(Id)
  INTO averageScore, IdCount
FROM ... ;


Why might a table scan have a huge I/O Cost?

As per the comments:

See SQL Server “empty table” is slow after deleting all (12 million) records – Remus Rusanu

The answer to this question is: Ghosted Records, subsequently confirmed by the original poster. Posting here for completeness.


What is the performance cost of enabling "log_lock_waits" on Postgres?

This is essentially for free, and you should enable it in all databases unless the workload is designed to have long lock waits regularly (which would be a design bug).

These error messages are written by the deadlock detector, which is expensive to run, which is why PostgreSQL doesn't run it unless someone has to wait deadlock_timeout seconds for a lock. 1 second is very long, so this should not happen normally (database transactions should be way shorter).

But the deadlock detector has to run anyway, and these log messages are just a side effect you get for free.


What commands are available to visualize the data pages on SQL Server?

DBCC PAGE

Paul Randal also has lots of stuff about on-disk structures and articles "Anatomy of a (insert storage thing here)" (some overlap here of course)


Postgres dumping specific non-table objects

I could run a whole-database dump with -T options set to exclude all the tables I don't want.

So what you want is to automatically exclude all tables but the ones you specify? You could always do this with dynamic sql..

SELECT format(
  'pg_dump %s',
  string_agg(  format('-T ''%I.%I''', schemaname, tablename), ' '  )
)
FROM pg_tables
WHERE schemaname = CURRENT_SCHEMA -- whatever schema.
  AND tablename NOT IN ('myDesiredTableName');

Run that with psql -c and then run the output from it to dump the database.


If you used homebrew to install mysql, mysqld will keep on restarting unless you use following brew command:

brew services list

Get the name of service to stop and then run the following command:

brew services stop mysql@5.5

From: https://stackoverflow.com/a/57169447/2670370


MySQL master master replication, safe to delete rows on both masters?

Your question already has the key to your answer. It does depend on row based or statement based replication.
STATEMENT-BASED REPLICATION
If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, no big deal. Replication will just carry on. It will  just take you a lot longer to realize your data draft on the Slave (or in your case, either Master) if there were slight differences at all.
ROW-BASED REPLICATION
If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, that can break replication because the exact row info is embedded in the relay logs on slave. That row is expected to exist for the delete to happen. There is an error code for it:
sh-4.1# perror 1032
MySQL error code 1032 (ER_KEY_NOT_FOUND): Can't find record in '%-.192s'

You will likely see error log messages like these:

2014-02-27 22:03:00 4070 [ERROR] Slave SQL: Could not execute Update_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000166, end_log_pos 93114177, Error_code: 1032
2014-03-17 10:50:15 11596 [ERROR] Slave SQL: Could not execute Delete_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000337, end_log_pos 427881, Error_code: 1032

Here are other links that discuss this

Some MySQL slave errors?
MySQL Forums : Replication stops with Error 1032
MySQL Forums : Error 1032 - Can't find record in table
Bug #44873 : Replication failure error code:1032

For more insights, please read Advantages and Disadvantages of Statement-Based and Row-Based Replication from the MySQL Documentation.


sorting output by incrementing IP in mysql


What is the best practice for the msdb database owner?

Brent Ozar's sp_blitz check on this sums it up well, I think:

  ...there’s no short answer for best practice.

And he does reference Andreas' article as well, acknowledging that is an ideal state. But often times ideal states are not feasible or reasonable in practice.

I don't know that taking the best practice approach (in this case) is indeed reasonable or feasible when looking at many enterprise scale deployments or when managing any large number of instances. I'm often satisfied enough to just use sa personally.

A lot of the community's best practice checks default to looking for sa as a database owner in general, because having a commonly used one does indeed help from an administrative perspective. For example, the popular PowerShell module dbatools's functon Test-DbaDbOwner defaults to looking for sa (citing Dan Guzman as the best practice source to do so) as the owner of all databases (though you can certainly specify a custom one if desired) just like Brent's scripts do.

I think in this case whether or not to do it comes down to personal opinion/choice based on circumstance. If you can handle the additional burden, then by all means it is ideal to do so. But most of the time in my experience, I think the extra work, complexity, and potential for time consuming problems outweighs the narrow benefits.

Finally, when you ask:

  Can anyone provide any insight as to the possible effects of changing the owner of msdb?

I'm not sure what you mean, but I think Andreas covers the topic fairly well to show what the benefits/effects of changing the owner are.

Further Reading:

Is Execute As Owner in MSDB a security risk?


COUNT(), GROUP BY and GROUP_CONCAT() together on MySQL

To create the result you wanted, I used a subquery and used GROUP_CONCAT() from there.

create table test
(
  id int,
  event varchar(3)
  );
  insert into test
  values
  (22,'a'),(23,'bb'),(24,'bb'),(25,'ccc'),(26,'ccc'),(27,'ccc')

select group_concat(total)
from
(
SELECT COUNT(event) AS total FROM test GROUP BY event
  )a

+---------------------+
|group_concat(total)  |
+---------------------+
|1,2,3                |
+---------------------+

DB Fiddle


Can `listen_addresses` system configuration setting in Postgres stop pre-authentication exploits?

listen_addresses filters connections before postgres sees them

  ➥ Does this mean listen_addresses theoretically stops pre-authentication exploits?

Yes. If PostgreSQL's postmaster isn't listening on a given interface (as identified by its local address) then no remote host may connect to it via that interface. The operating system will report the TCP port as closed and send a TCP RST to the connecting host; PostgreSQL code is never reached, so PostgreSQL bugs cannot be exploited, even pre-auth ones.1.

listen_addresses blocks pre-auth exploits

  Is there any engagement between the Postgres server and the incoming connection which might possibly result in an exploit? Or will the forbidden incoming connection be blocked without any engagement?

No. listen_addresses configures the listening TCP socket at the operating system level, binding it only to the network interface(s) specified2. It filters on the connection target address specified by the remote host. The OS won't tell PostgreSQL about connections to other interfaces at all.

Note that the same is is not true of pg_hba.conf. pg_hba.conf controls remote-host source address filtering and authentication configuration. The PostgreSQL postmaster does handle any connection that comes in on a listened-on address and is then rejected by pg_hba.conf configuration.

You can do sender-address based filtering before PostgreSQL sees the connection by configuring operating system firewall rules. Those will prevent PostgreSQL pre-auth exploits by blocking the connection from ever reaching PostgreSQL.

So if you know that only the hosts in network 111.1.0.0/16 have any business connecting to your PostgreSQL it's a good idea to configure a firewall rule accordingly. You should set pg_hba.conf as a fallback, but the firewall rule should stop anyone even attempting to connect to postgres itself.

Understanding authentication flow

To connect to postgres you must pass a series of "gates" of sorts. Ignoring UNIX sockets for this explanation, we have:

OS network and TCP level, before you hit PostgreSQL code:

Any border router or firewall between you and postgres must permit the TCP connection
Any operating system firewall configured on the postgres host must permit the TCP connection
listen_addresses - postgres must be listening on the interface or the OS treats the TCP port as closed
Any network security extensions like TCP Wrappers (/etc/hosts.allow and /etc/hosts.deny) must permit the connection. (Assuming they're supported on your OS and postgres is compiled to use them)

Within PostgreSQL code:

A pg_hba.conf host, hostssl or hostnossl rule is found when matching by source-address, ssl mode, target dbname and requested username
The matched pg_hba.conf rule must not specify reject
If SSL client certificate checks are enabled, the SSL client certificate satisfies the configured CA cert.
The requested username and database name must both exist in the PostgreSQL catalogs
The requested username or a role it inherits must have the LOGIN option in the PostgreSQL catalogs
The requested user must have CONNECT rights to the requested database. (By default the public role all users are a member of has CONNECT rights, but you can REVOKE that and GRANT it only to specific users or roles/groups).

Yay, you've connected

If a connection is blocked at an earlier stage, it never interacts with later stages. I haven't checked the exact ordering of the dbname vs username privilege checks, but the rest is right.

I ignored pg_ident.conf and username mappings, client cert DN mappings, the details of things like SSPI/GSSAPI and low level auth methods, etc here.

Now, if you're using UNIX sockets (unix_socket_directories, and a libpq host address that's a path or omitted entirely), the PostgreSQL stage is the same except that pg_hba.conf matching doesn't check source-address and looks for local lines instead of host, hostssl or hostnossl lines. The peer auth mode is supported to require a unix username to postgres username match. Details in the manual.

Exposing PostgreSQL to the Internet

Let me note that pre-auth exploits for PostgreSQL are not wholly unheard of, but are rare. It's fairly routine to expose PostgreSQL on the Internet directly.

You should use hostssl in pg_hba.conf to enforce SSL connections to protect authentication exchanges and make casual scanning a bit harder. And you should adopt the same protective measures you use for any other Internet-exposed service: use fail2ban or similar, use an IDS, monitor logs, and have firewall rules to exclude anything that you know has no business connecting.

However, if you don't need to expose PostgreSQL to the Internet, don't do it. In particular, if you only need loopback connections, bind PostgreSQL to the loopback address(es) 127.0.0.1 and ::1. Or even better, use unix sockets.

See also

The "Client Authentication" chapter of the manual
listen_addresses
pg_hba.conf
For Ubuntu and (mostly) Debian:

Ubuntu docs for Ubuntu's PostgreSQL packages
Ubuntu community help wiki for PostgreSQL

1 The attacker could still exploit OS bugs in the network stack. Or (very unlikely) they might be able to use tricks like source-address spoofing to fool a buggy OS into letting it send an initial packet to PostgreSQL even when it's bound to a different interface. Any modern, sensibly configured OS will prevent that.

2 Internally each listen_addresses entry is used to make a separate listening TCP socket. For UNIX-like OSes it's passed to the bind(...) call on each socket. See postmaster.c around line 1012, if (ListenAddresses), and see the StreamServerPort adapter in src/backend/libpq/pqcomm.c around line 532.


Oracle delete technique for table's segment size (data compactness)

How do you define the "tablespace size"?  Are you interested in the total size of the data files on disk that comprise the tablespace?  Or are you interested in the total size of all the segments that are part of the tablespace?

Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under either definition.  Both the size of the table's segment and the size of the tablespace's data files will remain constant.  Of course, there will now be additional free space in many of the table's blocks that can be used by subsequent INSERT and UPDATE operations.

Issuing a TRUNCATE, on the other hand, will decrease the size of the table's segment.  That won't affect the size of the tablespace's data files.  But it will affect the total size of all the segments that are part of the tablespace.  So there may be a difference depending on your definition of the size of a tablespace.  A TRUNCATE, being DDL, will not be transactional so it cannot be rolled back.  Assuming that you are deleting a large fraction of the rows in the table, it will also tend to be much more efficient than issuing a DELETE because it generates much less UNDO and REDO.

If you are stating in your last paragraph that the size of the segment is increasing much faster than the rate at which new data is being added, assuming that the new rows are roughly the same size as the old rows and that the old rows are not growing over time due to updates, is it possible that the new rows are being added via direct-path inserts which will always go above the current high-water mark for the segment and will thus never reuse the space in blocks that is freed up by a DELETE?  If so, is that intentional?  If the table is small, you might see similar differences because of the granularity of extent allocation-- you might insert 100 rows without requiring a new extent, the 101st insert requires Oracle to allocate a new extent, and that new extent might be sufficient for thousands of new rows to be added, but you'll only see the size of the segment change after the 101st insert.  But that is less likely if this is a reasonably large table unless you've chosen a particularly large extent size.


This is talked about exhaustively on the wiki. You'll have to ask a more poignant question

https://wiki.postgresql.org/wiki/Priorities


Generally, SQL is declarative. That is, you say "what you want" not "how to do it". One consequence of this is that you don't control what order things are processed or evaluated in at the same logical processing step (WHERE clause or GROUP BY clause etc are these steps)

That is, SQL doesn't generally short circuit because the concept doesn't apply.

There are several cases where it does (SQL Server and CASE for example) of course, but in your example you'd need the 2nd option to guarantee it. Note SQL Server has an optimisation for this kind of query but I'd assume Sybase doesn't


Maybe not all the differences, but most important in my opinion :

enum vs text/varchar + check constraint.

enum values are internally stored as integer numbers, so different storage requirements
ordering : order of enum values corresponds order in which values were entering during creation type, so for enum ('small', 'big') small < big;  for text column  'big'<'small'

enum vs foreign key

performance-wise they should behave very similar, but you can bypass foreign key constraint by, for instance, disabling all triggers or changing session replication role
there is a bigger difference from data model perspective. If something has attributes, needs to be tracked, likely to be modified in either way, it should be stored as a separate entity in its own table. If it's just an attribute , e.g. color in many cases, then it's fine to store it as a enumerated type.

Triggers are just not the right tool to implement check constraints. They add complexity, hide logic, add maintenance overhead.  In some cases they are "necessary evil" , but they definitely shouldn't be used just to do what check constraint can do.


Shut Down MySQL during InnoDB rollback after crash

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background.

What you can do is kill mysql (again) and restart it skipping that step:

/etc/init.d/mysqld start --innodb_force_recovery = 3

Then drop the offending table and recover it from the backups, that may be faster than performing a huge rollback.

For the future, try to avoid huge changes on a single transaction precisely to avoid rollbacks, which can be several times more expensive that the actual commit. I can provide you with some tools to help you do this, if they are administrative changes. If the error was (by) a developer, I can only recommend you the option --i-am-a-dummy. :-)


Why doesn't more memory necessarily make Postgres more performant?

In fact, effective_cache_size coupled with shared_buffers could more appropriately be considered to be the quintessential memory settings. Keeping shared_buffers a bit lower (e.g., 25%) is useful because Postgres also relies on operating system caches as well, which may account for utilization of some of the other "6GB" of RAM in the OP.

According to the official Postgres "tuning" page, setting effective_cache_size to half of total memory is considered to be conservative. However, this isn't a memory allocation, rather a guideline to help Postgres plan its queries based on what's available to it.

Also note that understating resources to Postgres by a slight degree can be helpful, in allowing some breathing room for future scaling. Imagine your Postgres server was optimized to take 100% advantage of all the physical resources in your machine, and then you reached your server's limit. There would be little you could do at this point to stave off disaster (e.g., swapping, extreme performance degradation, etc.), so leaving a bit of wiggle room can come in handy when you need a week's time to upgrade your server.


There are many possibities!

PITR vs STANDBY

PITR & Standby are complementary solutions:

Standby is interesting to recover from:

physical problem: server crash, datacenter unavailabity
and offer quick switchover procedures

PITR will help you in case of data damage:

  If someone performs an operation that damages your data (DROP TABLE , UPDATE with a wrong WHERE clause ...). Then, you will be able to restore your database before this operation.

Implementation

PITR implementation

There are many tools that can help you to perform PITR, I have already used:

barman
wal-e

And there are also:

PgBackREst
...

This article lists and compares several LINUX backup tools.

My bast advices, regarding PITR implementation, are:

Use existing tools (and don't try to write a new one)
Test your backups
Monitor your backups (and see the monitoring point fails)!

standby implementation

As it's native, there's no need for additional tooling on the implementation-side.

But:

monitor it correctly
Write and test your switch procedures (and re-test them at regular intervals, to prevent that the procedure is not accurate when needed!)


The OLE DB provider "MSDASQL" for linked server "(null)" reported an error

I had same issue once, You may need to recreate the linked server with this set to false (or just change it in the linked server properties->server options):

EXEC master.dbo.sp_serveroption @server=N'servername',
    @optname=N'remote proc transaction promotion', @optvalue=N'false'

Additionally, try using OPENQUERY to run this against the link

syntax:

OPENQUERY ( linked_server ,'query' )

and example SQL:

SELECT * FROM OPENQUERY(server_name,'SELECT * FROM table_name');


Unable to start MongoDB with NUMA interleave

Your startup script doesn't actually use numactl command to start mongod.

Your startup command should be:

/usr/bin/numactl --interleave=all /usr/bin/mongod

See also a similar question at AskUbuntu: How should I start MongoDB on a NUMA machine?


You will get better performance using a recursive common table expression although the syntax is (some would say) a bit more complicated, fortunately I don't have to explain it as there is an excellent article by Itzik where he handles both breadth first and depth first hierarchy evaluation.
https://sqlperformance.com/2020/09/t-sql-queries/fundamentals-of-table-expressions-part-6-recursive-ctes


If you don't want to go for a full EAV solution, you could try something like this:

product_base
------------
  id
  product_group_id
  (other fields)

product_option_types
--------------------
  id
  description

product_options
---------------
  id
  product_id (fk to product_base.id)
  product_option_type_id (fk to product_option_types.id)
  description

This will let you have data that looks like this:

product_base
ID  |  name | product_group_id
------------|-----------------
1   |  Foo  | 1

product_option_types
ID  |  description
------------------
1   |  colour
2   |  size
3   |  model
4   |  sublabel

product_options
ID  | product_id | product_option_type_id  | description
---------------------------------------------------------
1   |  1         | 1                       | red
2   |  1         | 1                       | green
3   |  1         | 2                       | small
4   |  1         | 2                       | large
5   |  1         | 3                       | special edition
6   |  1         | 3                       | standard edition
7   |  1         | 4                       | Bar

So you end up with a "Foo" product that has 2 colours (red and green), two sizes (small and large), two models (standard and special) and a sublabel (Bar).

Note that in product_options, you'll need a unique constraint on (product_id, product_option_type_id, description).

you could try to go further and attempt to normalize the values of product_options.description to a separate table, but that might be more complicated than it's worth.


Updating a table with a single UPDATE statement vs several UPDATE statements

When update executed once it's guaranteed that all rows that satisfy conditions will be updated (or none in case of error). 4 separate updates may update different rows (something may change between updates, and you're using MyIsam, so transactions won't help;  in addition, your update depends on non-deterministic NOW()  ).

Another point is that each UPDATE locks set of rows , so 4 updates will lock rows 4 times even if the same rows are affected(in case of MyISAM)


How to update foreign keys in a large fact table using FORALL?

I think this will do the trick, unless I have missed something. It has the benefit of less and more maintainable code, and performance wise it will likely outperform PL/SQL versions.

MERGE INTO tbl_big_table big
USING (
  SELECT
    per_c_period_orig,
    per_c_period
  FROM tbl_catalog
  ORDER BY per_c_period
) small
ON (big.per_c_period_orig = small.per_c_period_orig)
WHEN MATCHED THEN
  UPDATE SET big.per_c_period = small.per_c_period;

COMMIT;


In which order does a SQL Replication write the data

I'm not sure if you mean Transactional Replication or Snapshot Replication in your current situation, but in either case the ordering of the data should be preserved. You can read more on how Transactional Replication works in that linked Brent Ozar article.
Transactional Replication uses the the Transaction Log to ensure Atomicity and Consistency of the ACID principals in SQL Server. Snapshot Replication is a literal copy (snapshot) of your data in a point in time and exactly preserved that way when distributed to the Subscriber database.
That being said, the data type TIMESTAMP is uniquely generated based on the rowversion by the server and therefore will not match between your Publisher and Subscriber database(s).
For Transactional Replication it should be in the same sequential ordering, for Snapshot Replication I don't know if it can be guaranteed and you'd have to test, but my guess is there's still a consistent row ordering by the underlying rowid of the table, if not the primary key, and therefore should also be consistent (despite being new values in the TIMESTAMP field.)


SQL Server - difference between instance name and instance id

By default, the instance name is used as the Instance ID. Instance ID is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances

(source: http://msdn.microsoft.com/en-us/library/ms143531(v=sql.120).aspx )

Install SQL 2008 r2 steps: http://blogs.msdn.com/b/petersad/archive/2009/12/17/sql-server-2008-r2-sysprep-step-by-step.aspx


How to insert numerous data into temporary table?

You can use VALUES (...), (...):

INSERT INTO table(colA, colN, ...) VALUES
    (col1A, col1B, ...)
    , ...
    , (colnA, colnB, ...)

With @In:

DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES
    ('value1')
    , ('value2')
    , ...
    , ('value1000')

It will insert X rows all at once. GO is not needed. Variables declared before GO don't exist anymore after GO.


How does recovery work when server is shutdown before a checkpoint?

In full durability, the default, the commit completes only when the data is physically written to the log file - it's only data pages that are later written to disk on checkpoint by the lazy writer.
There's a per-database setting called delayed durability that can override and allow the transaction to be committed but only written to the log file when the buffer is full, offering faster transactions but the risk of losing (as yet) unwritten committed transactions.
More info


How to make MySQL 5.1 connection ignore kills?

I might have solved my problem...

I was afraid that 2 different killer threads could: (1) show processlist, (2) choose the same connection to kill, (3) then both would kill it.

The first kill would trigger the execution of whatever I wanted, while the second kill could potentially blow things up.

The easiest, and nearly obvious, solution that I missed is to look at the other side of the problem: instead of making the worker thread unkillable, the killer threads must cooperate: the killer threads would (1) select get_lock('killer_thread'), then only after acquiring the lock they would (2) show processlist, (3) kill the chosen threads and finally (4) select release_lock('killer_thread').


Selecting the most specific match from one table

SELECT *
FROM dogs
ORDER BY CASE WHEN breed <=> @breed THEN @breed_weight ELSE 0 END
        +CASE WHEN color <=> @color THEN @color_weight ELSE 0 END
        +CASE WHEN skill <=> @skill THEN @skill_weight ELSE 0 END DESC LIMIT 1

The parameter weighting factors can be constant literals or varying query parameters.

find by ('American Bulldog',  'Black', 'Runs fast')
Returns id 5

@color_weight must be greater than @skill_weight. Backward record id=1 will be returned, and a random of them if weights are equal.

If some search condition is mandatory - move the comparison to WHERE clause and remove it from ORDER BY expression.

  Yeah the breed and color can basically be in the where clause, they will be mandatory.

SELECT *
FROM dogs
WHERE breed = @breed
  AND color = @color
ORDER BY (skill <=> @skill) DESC LIMIT 1


Drupal 7 Uses InnoDB, so do I need other configs?

The other group headers have no bearing on InnoDB whatsoever. [mysqld] is all you really need.
With regard to InnoDB itself, you will need to tune InnoDB in many aspects
System Tablespace / Individual Tablespaces

Sep 26, 2012 : How do I shrink the innodb file ibdata1 without dumping all databases?
Jul 05, 2012 : Move ibdata1, set innodb_data_file_path
Apr 19, 2012 : MySql - Clean ibdata1
Apr 01, 2012 : Is innodb_file_per_table advisable?

Buffer Pool

Oct 22, 2012 : How much memory do I need for innodb buffer pool?
Jun 13, 2012 : What to set innodb_buffer_pool and why..?

Transaction Logs

Jan 17, 2013 : MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?

Multiple CPU / Multiple Core Engagament

May 21, 2012 : MySQL - Installing InnoDB Plugin
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

Epilogue
There are so many other aspects I have no time to write about because I probably have written it already. Please see all my posts on InnoDB.
What is also worth noting is that you should be using the latest version of MySQL

MySQL 5.6.10 now supports FULLTEXT indexing. You need to test this fully.
InnoDB in MySQL 5.6 is faster than InnoDB in MySQL 5.5

On a side note, did you know the StackExchange has a Drupal Q&A site ? I have posted answers there as well.


How can I display a mysql bit(64) column as a string of ones and zeros?

You can also use BIN() function. You still have to LPAD() though, the higher order 0s are not displayed:

LPAD(BIN(hash), 64, '0')


What is the purpose of adding new file to the database?

You could

put huge archive data onto a big but slow drive
put structural information ("meta data") into its own file
put indexes etc. on a fast drive
put large readonly data (e.g. old legacy data) onto a SSD
set up a more intelligent backup strategy

I'm sure there's still more to add :-)


Why does my query run fast in Environment A, but slow in Environment B?

There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.

Server

Is the hardware across environments the same (disks, memory, CPU, etc.) ?

If VMs are being used, could noisy neighbors affect overall VM performance?
If in the cloud, do autoscaling and other configurations have parity?

Are environments mixed between physical/virtual/cloud?
Do the OS versions match?
Are environments in different datacenters?

Instance

Are the SQL Server versions the same?

A CU or SP can make a world of a difference even if the major versions are the same.

Is the active workload during the query execution(s) comparable?

Are the same volume of queries present in all environments?
Is the nature of the workload the same across all environments?

Do all environments participate in the same HA/DR setup?

Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.

Do the same maintenance jobs get run on the same schedules in all environments?
Are trace flags equivalent across all environments?
Are the same backup jobs running in all environments?

Impact from backups should be minimal, but often they are not run at all on lower environments.

Are the sys configurations the same?

Database

Are the schemas / indexes / statistics / objects all equal across environments?
Does the exact same data exist across environments?

Amount of data
Distribution of data
Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)

Are database level configs the same?
Are compatibility levels the same?

With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies between environments. When developing a new query, additional tuning is often needed as it moves towards production.

Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.

Final note: lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.

More Resources:

Different Plans for "Identical" Servers by Aaron Bertrand


You can use variables for this kind of thing:

select userId, count(*)
from (
    select @g:=case when userId=@lastuserId then @g else @g+1 end AS grp,
           @lastuserId:=userId AS userId
    from test1, (select@lastuserId:=0, @g:=0) vars
    order by transactionTime
) groups
group by grp, userId
order by grp


How I would I break a total count query into intervals?

You could potentially do this using ROW_NUMBER.

I'd just advise to steer away from this whole thing, as it's not particularly useful....

If you were to do this, make sure you replace the <some_id> with the Customer ID or similar...

SELECT status, count(*)
FROM
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY status ORDER BY <some_id> ASC) rnum,
    status
  FROM customers
) weirdness
GROUP BY status, (rnum-1)/500;


MySQL - Difference between using count(*) and information_schema.tables for counting rows

There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable where and group by clauses, etc.)

a) the normal way. Just count them.

select count(*) as table_rows from table_name ;

Accuracy: 100% accurate count at the time of the query is run.
Efficiency: Not good for big tables. (for MyISAM tables is spectacularly fast but no one is using MyISAM these days as it has so many disadvantages over InnoDB. The "spectacularly fast" also applies only when counting the rows of a whole MyISAM table - if the query has a WHERE condition, it still has to scan the table or an index.)
 For InnoDB tables it depends on the size of the table as the engine has to do either scan the whole table or a whole index to get the accurate count. The bigger the table, the slower it gets.
b) using SQL_CALC_FOUND_ROWS and FOUND_ROWS(). Can be used instead of the previous way, if we also want a small number of the rows as well (changing the LIMIT). I've seen it used for paging (to get some rows and at the same time know how many are int total and calculate the number of pgegs).

select sql_calc_found_rows * from table_name limit 0 ;
select found_rows() as table_rows ;

Accuracy: the same as the previous.
Efficiency: the same as the previous.
c) using the information_schema tables, as the linked question:

select  table_rows
from    information_schema.tables
where   table_schema = 'database_name'
  and   table_name = 'table_name' ;

Accuracy: Only an approximation. If the table is the target of frequent inserts and deletes, the result can be way off the actual count. This can be improved by running ANALYZE TABLE more often.
Efficiency: Very good, it doesn't touch the table at all.
d) storing the count in the database (in another, "counter" table) and update that value every single time the table has an insert, delete or truncate (this can be achieved with either triggers or by modifying the insert and delete procedures).
This will of course put an additional load in each insert and delete but will provide an accurate count.

Accuracy: 100% accurate count.
Efficiency: Very good, needs to read only a single row from another table.
   It puts however additional load to the database.
e) storing (caching) the count in the application layer - and using the 1st method (or a combination of the previous methods). Example: run the exact count query every 10 minutes. In the mean time between two counts, use the cached value.

Accuracy: approximation but not too bad in normal circumstances (unless for when thousands of rows are added or deleted).
Efficiency: Very good, the value is always available.


How to have more than 100 entries in case statement as a variable

Put the data into a table

CREATE TABLE AccountTranslate (wrong VARCHAR(50), translated(VARCHAR(50));

INSERT INTO AccountTranslate VALUES ('ADDICTION ADVICE%','ADDICTION ADVICE');
INSERT INTO AccountTranslate VALUES ('AIR BP%','AIR BP');
INSERT INTO AccountTranslate VALUES ('AIR NEW Z%', 'AIR NEW ZEALAND');

and join to it.

SELECT ...,COALESCE(AccountTranslate.translated, ac.accountName) AS accountName
FROM
....,
account_code ac left outer join
AccountTranslate at on ac.accountName LIKE AccountTranslate.wrong

That way you can avoid keeping the data up to date in multiple places.  Just use the COALESCE where you need it.  You can incorporate this into CTE or VIEWs as per the other suggestions.


pgAdmin is known to be slow and inefficient with large result sets, so this is not surprising. It is not built for mass data display.

If you want to visualize a sample, add a LIMIT clause to the query.

If you want to export the data, use the COPY statement.


At What Point Should I Split or Partition a Very Large but Simple Table

There's a reason that the general advice is that it depends on the table design and the queries on it. My answer to your other post on Stack Exchange says as much. Saying "queries which are simple count(*) operations based on an indexed ID field" doesn't give much information since it says nothing of the cardinality of the set of rows under consideration. Things you can do to mitigate the (as of now perceived) problems are:

Partitioning. Specifically, your data seems to be logging-type data. My guess is that you want to get stats by some unit of time (e.g. "widgets per day" or "whozits by hour"). Partition by your quantum (i.e. days or hours in the previous examples) and move partitions off to read-only file groups occasionally
On a related note, if the data is write-once, consider pre-aggregating the data once the time period is no longer active. That is, why do I need to keep counting how many events happened on a day from three years ago if that data is never going to change? Once the day is over, count everything in that day, store it somewhere else, and never count it again. In fact, if you never have need of the detailed data (i.e you only ever do aggregations against it), consider deleting it after you count it. If you implement this idea, you can get even more clever with filtered indexes that cover only the "active" period which will make your queries faster because they will not cover the vast majority of your data

But, as my advice in the other post suggests, the only way you're going to know for sure is to load it up with a reasonable amount of data and try it out. All we can do here is say what will probably work in the general case. Without the specifics of your hardware, your data, and your queries, all we can do is guess. And, you may find that once you run the test that I'm proposing that the answer is "there's nothing to do" because it works just fine as is.


Setting MySQL slow query to less than 1 second

Just set long_query_time to the value you want. If you want to log queries that take longer than 100ms, you would set it to 0.1.

The ability to set this option to a value less than 1 was added in MySQL 5.1.21.


How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation?

To fix that error just list the fields  you want to insert. This means, all of the fields but ID.

Solution 1:

Replace

--Insert into [Input] table; get an auto-incremented PK ID assigned to new row
INSERT INTO Input
SELECT * FROM inserted

With:

INSERT INTO Input (TS_Code, column2, column3,..., column25)
SELECT TS_Code, column2, column3,..., column25 FROM inserted;

Notice that in this case your trigger will delete records and you will have holes in your table:

ID      TS_Code       Column2 ....
----------------------------------
1       1001          valueX
3       1003          valueY
4       1002          valueZ   <--- your trigger found a repeated 1002, ID = 2 record is deleted.

If this is what you want, go ahead with Solution 1.
Also notice, that your trigger will work only for record by record insert. A bulk insert will call the trigger once and you will need to handle each record using cursors.

Solution2:

If you want to keep the records already found, you probably want to use the MERGE option:

first of all put all the records from C# into a Working table.
Lets say table C_Sharp that should look like this:

CREATE TABLE C_SHARP(
TS_Code INT,
Column2,
...
Column25
)

With that C_SHARP table having all the records, perform a MERGE sentence:

MERGE INTO Input I
   USING (
          SELECT TS_Code, Column2, ... , Column25
            FROM C_SHARP
         ) C
      ON I.TS_Code = C.TS_Code

WHEN MATCHED THEN
        UPDATE
           SET Column2 = C.Column2,
               ...,
               Column25 = C.Column25
WHEN NOT MATCHED THEN
    INSERT (TS_Code, Column2, ..., Column25)
    VALUES (C.TS_Code, C.Column2, ..., C.Column25)
END;

Good luck!


SELECT message.Id,
       message.message,
       message.category,
       person.Id personId,
       CASE WHEN personmessage.messageId IS NULL
            THEN 0
            ELSE 1
            END enabled
FROM message
CROSS JOIN person
LEFT JOIN personmessage ON personmessage.personId = person.Id
                       AND personmessage.messageId = message.Id
ORDER BY 4,1

fiddle


Pgbouncer getting auth failing when trying to connect using psql

To create an md5 password for PGBouncer (or PostgreSQL for that matter), the formula is:

"md5" + md5(password + username)

Here are 3 ways you can create one, where the username is "admin" and the password is "password123"...

Linux:

# echo -n "md5"; echo -n "password123admin" | md5sum | awk '{print $1}'
md53f84a3c26198d9b94054ca7a3839366d

MacOS:

➜ echo -n "md5"; md5 -qs "password123admin"
md53f84a3c26198d9b94054ca7a3839366d

Python 2:

>>> import hashlib
>>> print("md5" + hashlib.md5("password123" + "admin").hexdigest())
md53f84a3c26198d9b94054ca7a3839366d

Python 3:

As above, but use binary strings...

print("md5" + hashlib.md5(b"password123" + b"admin").hexdigest())

Config Files

The entry in your pgbouncer.ini should be:

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

and the relevant entry in /etc/pgbouncer/userlist.txt should be:

"admin" "md53f84a3c26198d9b94054ca7a3839366d"

You can then test this by connecting to pgbouncer

psql -h localhost -p 6432 -U admin

... and then typing the plaintext version of your password when prompted.


Explicit transaction left open after try/catch

There are a few types of errors that are not caught by the T-SQL TRY / CATCH structure (taken from the MSDN page for TRY...CATCH and slightly edited for clarity):

  Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  Attentions, such as client-interrupt requests or broken client connections.
  When the session is ended by a system administrator by using the KILL statement.
  Errors that occur at the same level of execution as the TRY…CATCH construct during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

For the example code provided in the Question (i.e. a single DML statement), there is a very simple fix: remove the explicit transaction. For a single DML statement an explicit transaction is unnecessary unless there is additional logic that does a check on some condition and might optionally decide to undo that change via a ROLLBACK done in the main TRY and not as part of the CATCH since it wasn't a database engine error. Outside of that particular scenario, I can't see a reason (a good one, at least) to use an explicit transaction for a single DML statement. But I would still keep the TRY / CATCH structure for general error handling.

When it comes to multiple DML statements (which is the true context of the Question as stated in the wording of it above the example code) then you obviously do need an explicit transaction, so here are some thoughts on that:

A comment by @usr on the Question suggests to skip the error handling at the database layer altogether and instead handle it at the app layer. While app code can handle the transactions and error handling, if you are not using an ORM like Entity Framework exclusively (meaning you have stored procedures that are called by SQL Agent jobs and/or by support people via SSMS), then those stored procedures need to include the transaction and error handling because the app layer isn't always the initiator of the execution, yet you still want transaction and error handling in those other situations. I discuss this more in the following answer: Are we required to handle Transaction in C# Code as well as in stored procedure
Uncommitted transactions are automatically rolled-back when the connection ends. This is from SQL Server's perspective, not the client's perspective. Meaning, the connection is no longer listed in sys.dm_exec_connections.
Connection pooling complicates the auto-rollback handling since it, by design, keeps the connection open, even after the client "closes" it. When the connection remains open so that another "connection" attempt can simply re-use it, the session itself is not "cleaned up" until a new connection is opened and a query batch is executed! Upon the first execution submitted by the app code on a "new" connection that is actually reusing a connection from the pool, an internal process labeled as sp_reset_connection is called that will, among other things, roll back an uncommitted transaction in that session that is now being reused. The problem here is when connection pooling is being used, and there is a termination of the process (a Command Timeout perhaps), and no new connection is being requested and no new query is being submitted. In this case the connection just sits in the pool and the session still exists and no cleanup operation is being requested. But this doesn't last forever (though still longer than you want in these cases). According to the MSDN page for SQL Server Connection Pooling (ADO.NET) (in the Removing Connections section):

  The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed.

Cancelling a stored procedure execution isn't just a matter of your app code getting a CommandTimeout or calling an explicit "cancel". SQL Server Management Studio (SSMS), or any IDE, is also client code. And it is possible cancel a running stored procedure in SSMS. And doing so will have the same effect of skipping the CATCH block. But cancelling an execution in SSMS still keeps you in the same session, so the transaction is still active until you manually call COMMIT or ROLLBACK, or until you close that query tab (at which point it will tell you that there is an uncommitted transaction and ask if you want to commit it or not; my testing shows that answering "no" will roll it back).

So what to do about app code that is using connection pooling? I am not a huge fan of using SET XACT_ABORT ON;. What I would try first is:

Connections can set the max size of the connection pool via the Max Pool Size connection string keyword. If the pool is very large, then it is less likely for any particular connection to be reused quickly. The default pool size appears to be 100. Setting the value to be slightly lower than it is currently (but not too low as to not make effective use of having connection pooling) will help ensure that the connections are reused more quickly will means the cleanup process will be called more quickly which will rollback any open transaction.
In your app code where you are executing the query / stored procedure, in the catch block you can call SqlConnection.ClearPool which should close the actual connection at the SQL Server level which should in turn allow the auto-rollback of uncommitted transactions to take place. You might even be able to be fairly targeted with this by checking the exception to see if it is a Command Timeout or one of the other few scenarios that would terminate the process while skipping the T-SQL CATCH block, and if so, then call ClearPool.
Technically you could simply disable connection pooling entirely by specifying Pooling=false; in the connection string. However, I do not believe this is necessary and do not recommend it unless either absolutely necessary OR having an application that doesn't generate a lot of connections in the first place.


sp_blitz will show you all non-default configuration settings and the details show what the default is.

Another option would be to use dbatools to export the config on a fresh install, using export-dbaspconfigure. Then import when you want to reset, using import-dbaspconfigure.

Export-DbaSpConfigure -ServerInstance . -path c:\temp\sp_configure.sql

This will create a file with all sp_configure options in this format

EXEC sp_configure 'show advanced options' , 1;  RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'recovery interval (min)' , 0;
EXEC sp_configure 'allow updates' , 0;
EXEC sp_configure 'user connections' , 0;
EXEC sp_configure 'locks' , 0;
EXEC sp_configure 'open objects' , 0;
EXEC sp_configure 'fill factor (%)' , 0;
EXEC sp_configure 'disallow results from triggers' , 0;

Then you can either run this file or the import-spdbaconfigure command, which will run the script for you

import-DbaSpConfigure -ServerInstance . -path c:\temp\sp_configure.sql


It's more readable if you use a derived table. No need for 4 SELECT clauses, just 2. Plus, it's proper SQL, not some fancy feature that MySQL or Access has added:

SELECT InvoiceNumber,
       CalcField1,
       CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage
FROM
    ( SELECT InvoiceNumber,
             Field1 + Field2 AS CalcField1,
             Field3 + Field4 AS CalcField2
      FROM ...
    ) AS x ;

The other option, is not to use a derived table at all but double the calculated expressions:

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       ((Field1 + Field2) - (Field3 + Field4)) / (Field3 + Field4)
         AS Percentage
FROM ... ;


Can't find compiled parameters values in the plan cache

sp_prepare doesn't include the compiled values for parameters, because it doesn't 'sniff' them. When a query is issued with it, cardinality estimates are made using the density vector rather than the statistics histogram.

I blogged sort of recently about that here:

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

If you read the blog post, some of the terms I've used in this answer are explained a bit more.


Without knowing your dataset size, or your usage (read or write heavy? queries per second?), here are some generalities:

InnoDB has improved drastically in even the last few years performance-wise. This, along with the (mostly) ACID compliance and row-level locking, is why it's now the default in 5.5+
If you're not using full text indexes in your MyISAM table, you should be fine switching to InnoDB
Make sure you know the changes to the mysql version you're going to on RDS. Performance might not be the only 'gotcha' on moving to InnoDB at RDS. If RDS is newer than your current application, make sure there's not any features that changed. You do this by:
Test it!! Don't make the move on your application until you've tested it out.

One method to test it is to turn on the general query log for a week or so (if the performance hit won't be an issue) and then run it against the RDS instance to see how it runs.

Disclaimer: I'm not an expert on RDS, so I don't know the best way to test against it.


Grouping records with with consecutive start and end dates -Postgresql

You could use a GROUPING AND WINDOW solution in this way:

create table tbl (vendor text, auth text, StartDate Date, EndDate Date);

✓

insert into tbl values
('Vendor 1', '#####', '20170101', '20170228'),
('Vendor 1', '#####', '20170301', '20170415'),
('Vendor 2', '#####', '20170416', '20170531'),
('Vendor 2', '#####', '20170601', '20170712'),
('Vendor 1', '#####', '20170703', '20170930');

5 rows affected

select vendor, min(startdate) as startdate, max(enddate) as enddate, grp
from (
      select vendor, auth, startdate, enddate,
             sum(rst) over (order by vendor, startdate) as grp
      from (
             select vendor, auth, startdate, enddate,
                    case when coalesce(lag(enddate) over (partition by vendor order by vendor, startdate), startdate) + 1 <> startdate then 1 end rst
             from   tbl
           ) t1
     ) t2
group by grp, vendor
order by startdate

vendor   | startdate  | enddate    | grp
:------- | :--------- | :--------- | --:
Vendor 1 | 2017-01-01 | 2017-04-15 |   1
Vendor 2 | 2017-04-16 | 2017-07-12 |   3
Vendor 1 | 2017-07-03 | 2017-09-30 |   2

db<>fiddle here


Do I need SQL_NO_CACHE for write intensive query?

No.

SQL_NO_CACHE is only relevant for SELECT statements. It is not an option for INSERT or LOAD DATA statements. "The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache"

Moreover, the query cache itself has to be enabled, then is invalidated when the underlying dataset has changed.

If you're performing a series of SELECT statements at the same time as the bulk load, then you will save a few microseconds by using SQL_NO_CACHE, but it is only an option as part of the SELECT.


I was wondering whether such useful query could also be used in standard ms sql server some how?

No, CREATE DATABASE .. COPY OF .. is ONLY applicable to  Azure SQL Database

As a side note,

  Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process.  You can monitor the copy process with the sys.dm_database_copies and sys.databases views. The sys.dm_operations_status view can be used as well as it returns the status of database operations including database copy.

In order to create a copy of current database in SQL Server, you have to backup the database and then restore it with a different name

e.g.

backup database OriginalDB
to disk = 'D:\backup\OriginalDB_full.bak'
with init, stats =10;

restore database new_db_name
from disk = 'D:\backup\OriginalDB_full.bak'
with stats =10, recovery,
move 'logical_Data_file' to 'D:\data\new_db_name.mdf',
move 'logical_log_file' to 'L:\log\new_db_name_log.ldf'


MySQL 8.0.17 on Windows - I forgot my root password, dont need to keep any previous DBs

It's possible re-initialize the data directory to produce an empty MySQL instance. Here is the documentation describing the process: https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html .

It tells the command to perform is:

bin\mysqld --initialize --console

or the following if you want to connect as root without password:

bin\mysqld --initialize-insecure --console

The data currently in the data dir would be overwritten. If there still is a chance it might be needed in future, you may find datadir param in my.cnf and backup the directory before proceeding to re-initialization.

Another possible option is to uninstall MySQL, remove or rename datadir as found in my.cnf and install again. It shouldn't ask for the old root password then.


Selecting the correct row given multiple input value combinations

Short and dirty answer. Not efficient, it will have to scan the whole table. Will give only 1 row as result, if there are more than one rows with same (maximal) number of matches ("wrong" data in your terminology), the choice is arbitrary:

SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC
LIMIT 1 ;

Slightly better than the above but not more efficient. If there are more than one rows with same (maximal) number of matches, the choice is not arbitrary but based on the assumption that a row with "item and type" is preferred over a row with "item and colour" which is preferred over a row with "type and colour":

SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC,
         (item='pen')+(type='gel') DESC,
         (item='pen') DESC
LIMIT 1 ;


MongoDB working set size on capped collection larger than capped collection size?

Check out https://jira.mongodb.org/browse/SERVER-5615

You might be running into a similar issue. Do you have several indexes on your data? Can you do a db.coll.stats()? And maybe show some output of mongostat while it's running slower?

I've heard similar complaints in the past, but haven't actually used capped collections myself.


Unable to Create new Maintenance plan in SSMS 2017

I was just searching for this error as it just occurred on my instance as well. Although it wasn't identical, I wanted to put this out there if anyone else runs into the same problem when searching.

Server 2019
SQL Server Standard 2017 (14.0.2027.2)
SSMS 2018 (v.18.5.1)

Went to create a new maintenance plan (the first one on this server and named instance) and received a very similar error out of the gate: Exception has been thrown by the target of an invocation. (mscorlib) Invalid Class String.
I checked all components, etc and my fix was to install SSMS 2017 and use it rather than SSMS 2018. Works just fine now.


MySQL Scheduled Event and server offline?

It should run just fine.

However, it is just like configuring Windows Updates. If you configure Windows Updates for 12:00 AM, shutting down the Windows Machine at 11:59 PM and starting it back at 12:01 AM prevents Windows from checking for Updates.

When such a shutdown is occasional, leaving everything alone will cause the DB server to bring up the MySQL Event at midnight so long as mysqld is running at midnight.


Drag and Drop table object explorer into query window issue Sql Server Management Studio 18.1

Follow this feedback item for updates on the issue:
SSMS Crash when dragging table from Object explorer into new query window

Microsoft SQL Server (Product Manager, Microsoft Azure) responded  ·  Jun 14, 2019
Issue has been identified and a fix is being made. It will be rolled out with the next update of SSMS 18.

In the meantime, you could revert to SSMS 17.9.


Reset Variable when row value is changed

select agentID,points,
case when (if(@prev_agent != agentID, @total := points, @total := @total + points)) is null then null
when (@prev_agent := agentID) is null then null
else @total end as running_total
, comment
from attendance, (select @total:= 0.0, @prev_agent:=null) v
order by agentID, incurredDate

see it working live in an sqlfiddle

In case you wonder, why I use this case when construct, please refer to this post.

P.S.: When using variables like this, you should have a order by clause. Group by in MySQL uses an implicit order by when none is specified. But remember using order by when you don't have a group by.

EDIT: Actually your group bydoesn't fit here, it seems to me. I changed it to a order by. And I changed your points column to datatype decimal(5,2), cause you have .5 in your sample data, which would become 1 when you continue to use int.

EDIT:

To get the total for each agent, just wrap above query in another query like this:

select agentID, MAX(running_total)
from (
    select agentID,points,
    case when (if(@prev_agent != agentID, @total := points, @total := @total + points)) is null then null
    when (@prev_agent := agentID) is null then null
    else @total end as running_total
    , comment
    from attendance, (select @total:= 0.0, @prev_agent:=null) v
    order by agentID, incurredDate
) sq
GROUP BY agentID

see it working live in an sqlfiddle


MySQL 8 roles - I'm probably missing something obvious

So after diving deeper into the documentation, and specifically the "Using Roles" page, I have found that:
"By default, granting a role to an account or naming it in the mandatory_roles system variable value does not automatically cause the role to become active within account sessions... To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE."


Azure SQL Database vs. error 8152 enhancement

You cannot enable traceflags in azure sql database (single database).

You can enable traceflags when using azure sql database managed instances:

DBCC TRACEON documentation

Since the azure sql database service is based on the latest stable sql server version

  It's (azure sql database) based on the latest stable version of the
  Microsoft SQL Server database engine.

Azure sql database service documentation

And  in your linked source it is stated that:

  ...Note that for now, even in SQL Server 2019 CTP 2.0 the same trace
  flag 460 needs to be enabled. In a future SQL Server 2019 release,
  message 2628 will replace message 8152 by default....

We should hope to see this more detailed error message enabled by default for the azure sql database service in the future, once that build is deemed stable.


Is it possible to create a multi-layer schema in SQL Server 2008 R2?

No, that is not possible.  You can wrap those names in square brackets, but that is not recommended, since it will likely cause a lot of confusion.  For instance:

CREATE SCHEMA [hr.report];

SELECT statements against objects in the schema would then look like:

SELECT * FROM [hr.report].SomeTable;


Normalization to Third Normal Form (3NF)

I feel that you could add an additional Entity in there, one that represents people as a part of this (school-like?) institution, call it "People" (yeah...)

So Tutors, Students, Teachers, Head of Departments, etc are all People.
That table will hold Attributes that the sub classes/entities will share (because they're all people, right?): Firstname, Lastname, DoB, etc.

You then refer to this table on your other entities. Eg the Teachers by using the FK PeopleId (or whatever you call it =)

A student is then able to become a tutor and even a Teacher or a Head of Department at some point without being added to simultaneous tables within the database.

To, then, define a relationship between your Course, your Students and your Tutors you'll use a junction table in between that could look like:

------------------
|  CourseTutors  |
------------------
| FK | CourseId  |
| FK | StudentId |
| FK | TutorId   |
------------------

Thats very granular, it will even allow you to have one tutor per student.


Syncing data between onprem and Azure SQL

No .. Azure data factory is not required.

You can set up transactional replication from on-prem to Azure. Do not use data-sync .. its buggy and have many issues.


How to calculate or track monthly database growth?

below query using DBA_HIST_SEG_STAT and base on awr SNAPSHOTS and their priod gives the database growth per snapshot. you can change it for monthly output.

    set serveroutput on
    execute dbms_output.enable(buffer_size => NULL);**

    Declare
          v_BaselineSize  number(20);
          v_CurrentSize   number(20);
          v_TotalGrowth   number(20);
          v_Space     number(20);
          cursor usageHist is
                  select a.snap_id,
                  SNAP_TIME,
                  sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
              from
                  (select SNAP_ID,
                      sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
                  from DBA_HIST_SEG_STAT
                  group by SNAP_ID
                  having sum(SPACE_ALLOCATED_TOTAL) <> 0
                  order by 1 ) a,
                  (select distinct SNAP_ID,
                      to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
                  from DBA_HIST_SNAPSHOT) b
              where a.snap_id=b.snap_id;
      Begin
          select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
          select sum(bytes) into v_CurrentSize from dba_segments;
          v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

          dbms_output.put_line('SNAP_TIME           Database Size(MB)');

          for row in usageHist loop
                  v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
              dbms_output.put_line(row.SNAP_TIME || '           ' || to_char(v_Space) );
          end loop;
      end;
    /


SQL Server 2012 x64 - cannot safely allocate more than 50% RAM

I'd check the perfmon metrics for per-cpu CPU busy, as well as the perfmon metrics instance-wide and per NUMA node for PLE, database pages, etc.
Two recent SQL Server KBs

FIX: SQL Server performance issues in NUMA environments
FIX: You may experience performance issues in SQL Server 2012

However, for some workloads, especially with higher core count, high concurrent query count, and lots of database disk IO, using trace flags 8015 (disable NUMA support at SQL Server level) and 8048 (remove per-socket query memory allocation botlleneck) will provide even better results than the fixes contained in SQL Server 2012 SP1 CU4. (I confirmed this on our test equipment with simulations of our workload - YMMV)

Trace flag 8015 deserves thorough evaluation before deployment.  Memory affinity and attending lower memory latency is sacrificed for managing a single large bpool.  It also results in one lazy writer instead of one per NUMA node, and the idea of affinitizing a connection endpoint within a single SQL Server instance to each NUMA node also disappears.  But for some workloads the benefits are undeniable.  Don't use 8015 without 8048.

I've never seen or heard of any measurable cost to adding trace flag 8048, and if CMEMTHREAD waits and associated spinlocks are triggered by query memory allocation - its the only reliable way to eliminate them.


You can use sp_rename to change the name of procedures, columns, tables, indexes, constraints. You'll have to do each object individually (although you can create a single script that renames the objects), and I would highly recommend running through a test environment first to ensure that you do not run into any problems.


I would like to launch a pg_dump command from a remote computer

Regarding the password, you have two choices:

An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html
A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html

I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any computer that can connect to the database. It is not a "server application".

But to run a program on a remote Windows computer you can use PsExec


SSMS Disk Usage report does not show auto grow events

The logic for the disk usage report is baked into SSMS and while we can't know what the RDL looks like (and if any filtering is done) I grabbed this query sent by SSMS 2016 using Profiler:

if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
begin
declare @curr_tracefilename varchar(500) ;
declare @base_tracefilename varchar(500) ;
declare @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;

    select  (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,       DatabaseName
,       Filename
,       (Duration/1000) as Duration
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as ChangeInSize
from ::fn_trace_gettable( @base_tracefilename, default )
left outer join sys.databases as d on (d.name = DB_NAME())
where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername   and DatabaseName = db_name()  and (d.create_date < EndTime)
order by StartTime desc ;

This isn't substantially different to Aaron's script and I don't see how it could return different results. It looks to me that even if the traces roll over, it will still be iterating all of them (and they wouldn't roll over 5x in the space of a few minutes or even hours of testing).


Why does "DB2 Recovery Expert" web console doesn't enable the Run and Next button?

The reason could be that the tool identified that there's some information missing. Still in my case everything was there. So, I've found that the solution for my issue was just because, even if you don't have an alternative path for logs or backups, you still have to specify them when running these operations.

Sometimes even doing so they might not enable instantly. You must go "Previous" and then "Next" to make the web console notice the changes.