SELECT ... INTO variable results in NULL or idk

So, the ultimate answer to my own question is, as follows.

The stored procedures on MySQL version 5.6.13 don't like, when you use local variables and procedure parameters named exactly as the column names returned by queries used in the procedure's body - even though I used backticks in the queries. Documentation on the use of backticks in MySQL queries.

I have made some changes in the logic of the procedure, as it was not working as I expected. But after renaming the following variables the code did not throw any exceptions, at all.

user_id -> usr_id
points -> to_spend
to_spend -> cur_pts

The edited code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `user_spends_points`$$
CREATE DEFINER=`a30267_palivo`@`%` PROCEDURE `user_spends_points`(IN `usr_id` INT UNSIGNED, IN `to_spend` MEDIUMINT UNSIGNED)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE cur_pts MEDIUMINT UNSIGNED DEFAULT 0;
    DECLARE v_id INT UNSIGNED;
    DECLARE loc_points MEDIUMINT UNSIGNED;
    DECLARE loc_spent MEDIUMINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT `id`, `points`, `spent` FROM `smartfuel_activities` WHERE `user_id` = usr_id AND `expired` = 0 AND `points` <> `spent` ORDER BY `created_at` ASC;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLEXCEPTION");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLSTATE");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        CALL `log`("user_id", CAST(usr_id as CHAR(5)));
        CALL `log`("points", CAST(to_spend as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET done:= TRUE;
        CALL `log`("handler", "NOT FOUND");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        CALL `log`("user_id", CAST(usr_id as CHAR(5)));
        CALL `log`("points", CAST(to_spend as CHAR(5)));
    END;

    START TRANSACTION;
    SELECT `current_points` INTO cur_pts FROM `smartfuel_users` WHERE `id` = usr_id;

    IF cur_pts > to_spend THEN
        OPEN cur;

        userPointsLoop: LOOP
            FETCH cur INTO v_id, loc_points, loc_spent;
            IF done THEN
                LEAVE userPointsLoop;
            END IF;

            IF to_spend > 0 THEN
                IF (loc_points - loc_spent) > to_spend THEN
                    UPDATE `smartfuel_activities` SET `spent` = loc_spent + to_spend WHERE `id` = v_id;
                    SET to_spend = 0;
                    LEAVE userPointsLoop;
                ELSE
                    UPDATE `smartfuel_activities` SET `spent` = loc_points WHERE `id` = v_id;
                    SET to_spend = to_spend - (loc_points - loc_spent);
                END IF;
            ELSE
                LEAVE userPointsLoop;
            END IF;
        END LOOP userPointsLoop;

        CLOSE cur;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough points to complete expenditure', MYSQL_ERRNO = 1000;
    END IF;

    COMMIT;
END$$

DELIMITER ;


MySQL cannot reach outside itself to do anything with files.

Checking/cleansing incoming data is best done in your client language (PHP, Java, VB, ...)


Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?

OPTIMIZE is probably better because what it does is

CREATE TABLE ...
Copy all the existing rows over
RENAME ...

(Some time during or after step 2, the indexes are rebuilt.)

Dump and reload:

Read the entire table, write to disk
DROP TABLE or TRUNCATE TABLE and CREATE TABLE ...
Read the dump, inserting into the table

This is slower because of the extra I/O for the dump.

If disk space is tight, execute the dump (mysqldum/xtrabackup/etc) and restore (mysql) from a remote machine and have the dump file on that remote machine.  This may be the only way to do the cleanup without running out of disk space.

Even better...

Next time you need to do a big DELETE...

CREATE TABLE new LIKE real
Copy the rows you do not want to delete into new
RENAME TABLE real TO old, new TO real
DROP TABLE old

This 'eliminates' the effort to do the DELETEs.

Deleting big chunks is a popular topic; see my blog on the topic.

Still even better...

If this is a time series, and you are dropping "old" data, PARTITION on the date.  Then DROP PARTITION to jettison the old data 'instantly'.  Details.

Caveat

If you have only 20GB of spare disk, and the remaining data + index in the InnoDB table is more than 20GB, OPTIMIZE will run out of disk space and fail (without doing 'anything').  So will dump+reload unless you can use a different machine for the dump.  (The 'better' solutions need setup; can't do either this time.)

If the table is in ibdata1 (not innodb_file_per_table), no space will be given back to the OS.  It will be available for future growth in InnoDB tables.

Addenda

Their may be a difference is size or speed between the restore and the OPTIMIZE.  The rebuilding of the indexes may be different.

If you are doing any of this to recoup space on disk, start with the smallest table first and work your way up.  Keep in mind that InnoDB makes some attempt at cleaning up holes in its BTrees.  In theory, a well-kept BTree will average 31% empty space, even after lots of churn.  So, that's the most you can expect to recoup from OPTIMIZE (or equivalent).  However, a huge delete leaves free space in addition to the 31%.


What memory should be given to the variables of my.cnf ?

Here are some of my recommendations for InnoDB.  In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs.

http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
This is the buffer pool, where data and index are cached

innodb_buffer_pool_size=(In Mb, 80% of available RAM), for 2GB, I would put 1536.

Monitor your system carefully and be prepared to reduce this variable if you start to see swapping.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_additional_mem_pool_size
This is where metadata is stored.  If you had a lot of tables, it would be useful to increase this.

innodb_additional_mem_pool_size=16M

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_data_file_path
Here you can define the location of the ibdata file.  If you can put the ibdata file on a separate disk that uses a seperate controller, it will reduce contention.  Be careful with this option.  Make sure that you read up on this variable before you use it.  If your ibdata file needs more than 29G, MySQL will stop whatever its doing and wait for you to add space.

innodb_data_file_path = ibdata1:128M:autoextend:max:29G

This manages in what increments the ibdata file is increased.  So if you put a really small number like 10MB, that would mean that innodb would spend a lot of time increasing its ibdata file if you had a lot of inserts.

innodb_autoextend_increment = 128M

where to put the transaction logs.  If you can put the transaction logs on a different disk with a separate controller, this will also reduce contention

innodb_log_group_home_dir = /tlog/

What is the size of the log files

innodb_log_file_size = 500M

25% of the buffer pool size, allows large transactions to run without the need to write to the disk

innodb_log_buffer_size = 384M

I also define where the temp directory should go.  Defines where MySQL creates temporary files.  Yet another way to reduce contention.

I typically leave enough space to allow reindexing of the largest table on the server.

tmpdir=/opt/tmp/

And an error log is useful just in case something goes wrong and you need to trouble shoot.

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

Also, if you have more than 4 cpus, these variables can be increased
innodb_thread_concurrency = 8
innodb_file_io_threads=4

This has nothing to do with innodb.  There are other variables you can play with that have nothing to do with innodb.  I added them at the bottom, but they were taken directly from a server I use for testing.  Avoid just copying and pasting them.  Reading MySQL's documentation based on the version your using is the best way to make the right choices.

# Fine Tunning
key_buffer              = 512M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
join_buffer_size        = 64M
sort_buffer_size        = 16M
myisam_max_sort_file_size = 1024M
tmp_table_size = 256M
max_heap_table_size = 257M
myisam_sort_buffer_size=1024M

table_cache            = 1000
thread_concurrency     = 8
# * Query Cache Configuration
#
query_cache_limit       = 16M
query_cache_size        = 16M

skip-name-resolve


How is Index rebuild affected by IN_ROW_DATA vs LOB DATA

In Ola Hallengren's scripts he uses the MAX fragmentation and only considers the IN_ROW_DATA allocation unit type. See this excerpt from his scripts:

ALTER PROCEDURE [dbo].[IndexOptimize]
-- ...
-- Is the index fragmented?
-- ...
-- Note his use of the following below:
---- @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent)
---- WHERE alloc_unit_type_desc = ''IN_ROW_DATA''
  SET @CurrentCommand05 = @CurrentCommand05 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'

Without going further into the exact logic of the scripts it would appear that the index would not be reorganized because the page count is below the minimum of 1000.

Github scripts
link to script line above


What is the optimal set of database maintenance tasks for high volume of transactions

As far as the transaction log goes, I highly recommend you read this article: SQL Server Central: Managing Transaction Logs.  The biggest takeaway you should get from this is that you need to plan your transaction log backups according to:

The maximum amount of data that can be lost in the case of a disaster
The size to which the log can grow.

The above was quoted directly from the above article on SSC.

What #1 is indicating is that if the drive that your log file exists on is corrupted and you can't salvage your log file (which means you can't back up the tail of the log for the minimization of data loss), then you will have data loss since your last transaction log backup.

And #2 being obvious, the transaction log in full recovery mode will grow until backed up.


How can performance of queries containing keywords like IN, EXISTS, etc. be optimized?

Try to use equivalent query with JOIN and without IN or EXISTS and compare the runtimes:

select id
from table1 join table2 on table1.A = table2.B
where table2.C < 200
group by id

Look also at similar question here, where similar optimization is proposed for much more complex query: https://stackoverflow.com/q/17038193/684229. But note that the JOIN solution is not always faster; sometimes, surprisingly, the IN/EXISTS queries come out as faster. Depends on the optimizer. That's why I suggest to test both variants.


Database is happily available even during log growth?

I ended up solving my own question with the below code:

    --Using the AdventureWorks2012 database in Full mode with log shrunk to 64MB
    --setup test table
    USE AdventureWorks2012
    go

    IF OBJECT_ID('TestTbl') IS NOT NULL
        DROP TABLE TestTbl
    GO

    CREATE TABLE TestTbl
    (
          RowID          INT IDENTITY(100,100)
        , RightNow       DATETIME
        , SomeField      VARCHAR(255)
        , SomeOtherField VARCHAR(255)
        , CONSTRAINT PK_TESTTBL_ROWID_RIGHTNOW
            PRIMARY KEY CLUSTERED (RowID, RIGHTNOW)
            WITH (IGNORE_DUP_KEY = OFF)
    )
    GO

    --grow the log out to roughly 50GB and write to the test table in between each grow
    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 1','8193 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 8193)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 2','16386 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 16386)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 3','24579 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 24579)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 4','32772 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 32772)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 5','40965 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 40965)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 6','49158 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 49158)

    --in a separate session insert to the test table, i did this after the first 8GB was grown
    INSERT TestTbl
    VALUES (GETDATE(),'SomeValue'+CAST(DATEPART(MILLISECOND,GETDATE()) AS VARCHAR(25)), 'SomeOtherValue'+CAST(DATEPART(MICROSECOND,GETDATE()) AS VARCHAR(25)))
    GO 50000

    --monitor growth iterations
    SELECT * FROM TestTbl WITH (NOLOCK)
    WHERE SomeOtherField LIKE '%MB%'

    --monitor log
    SELECT DB_NAME(database_id) DatabaseName,name LogName,size*8/1024 SizeInMB
    FROM sys.master_files
    WHERE database_id = DB_ID('AdventureWorks2012')
        and type_desc = 'LOG'

    DBCC LOGINFO ('AdventureWorks2012') WITH NO_INFOMSGS

    USE AdventureWorks2012
    GO

    SELECT
     [Current LSN],
     [Transaction ID],
     [Operation],
     [Transaction Name],
     [Description],
     [CONTEXT],
     [AllocUnitName],
     [Page ID],
     [Slot ID],
     [Begin Time],
     [End Time],
     [Number of Locks],
     [Lock Information]
    FROM sys.fn_dblog(NULL,NULL)

    --modify the rowIDs to span a growth iteration and observe short pause and continue
    SELECT * FROM TestTbl
    WHERE RowID BETWEEN 239500 AND 241000


Database design: self referencing vs separate tables

but there is another suggestion that i should separate each type to their own tables, so i will need debit_payment, refund_payment, regist_payment, and so on

You should use two tables because in this case, the "payment" is a transaction. And though the transaction includes a ledger entry for credit and debit those are two separate things. Further there isn't anything below (more atomic or subordinate to) the ledger entry.

A transaction is a batch of ledger entries in which the sum of those entries is 0. It requires no other information.
A ledger entry is attached to an transaction and one account, and exists only inside a transaction.

Here is an example,

CREATE TABLE transaction ( id int PRIMARY KEY );

CREATE TABLE ledger (
  ledger_id int PRIMARY KEY
  txn_id    int REFERENCES transaction,
  account   int REFERENCES account,
  amount    numeric(16,2)
);


Shouldn't mysql be using both indexes in a join?

I think this is due to the partial index. i.e. the index has only 8 char of hashPrevOut

It estimated that the number of matching first 8 char will lead to a second read from the table to compare the full value from first table with the full value in the other table, so the optimizer decided to skip the index, and deal with the table directly.


Dev/Test Environment For Databases With Cross-Database Joins

I would tend to suggest using multiple publish.xml files as described here Jamie Thompson blog

The database names (or a prefix) could then be stored as SQLCMD variables.

Each publish file would then be built and published as a build artifact - you'd need to code a custom powershell or batch job to pick the correct file based on the server name. (You could use the server name as a switch here)

You would need to set up the publish file for each environment once, but once that job is done, it won't need doing again.


Unable to get SQL Server 2016 CTP3 service to start on Windows 10

Interesting. Glad you got the SQL service to start. It should be okay to leave it as NT AUTHORITY\LOCALSERVICE for testing CTP3 on your workstation. In general for production servers on a domain it is best to provision and use domain service accounts for the SQL Server and Agent services - see https://www.simple-talk.com/sql/database-administration/provisioning-a-new-sql-server-instance-%E2%80%93-part-one/ .


What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type

SELECT * in a grouping query will not work in most (if not all) SQL implementations.

UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type
)

This query is deterministic and should work with most SQL dialects.

Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;


SQLPlus "no listener" only with a remote connection from Windows

The listener works as expected. It is configured to listen at localhost (127.0.0.1) and so it cannot accept connection from another host.

I assume that the listener runs on host with IP 192.168.1.130 .
So in the listener configfile change  localhost to 192.168.1.130 and restart the listener.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle


Postgresql ERROR: duplicate key value violates unique constraint when trigger insert

You're calling this

INSERT INTO organization VALUES ('Tivoli11','Tivoli organization',NULL,'F',NULL,NULL)

Which is triggering this.

INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)

Your primary key, organization_pkey, is on abbreviation. Here you have two Tivoli1 abbreviations. That triggers the exceptions which causes the trigger to fail, and the commit to abandon.

I have no idea what you want to do, but perhaps you need a consultant to audit your schema and workflow. It seems like you may be in over your head.

Re: "there is no Tivoli11"

Right,

You tried to insert it
That triggered a trigger trg_backup_row which you did not show in the question (please do)
trg_backup_row attempted another insert.
That attempted insert violated the unique constraint of the primary key.
That violation threw an exception.
That exception caused the transaction and the initial commit to rollback.

Transactions, even AFTER INSERT that throw exceptions cause the insert to rollback.


Creating a New Database Across a Network with SQL Server 2008 R2

What account is SQL Server running as on VAIOE? That will be the one that needs permission to access the share on VAION. And if SQL Server isn't running as a domain account, that might be tricky to get it to authenticate correctly.


I have seen such problems when other VS is installed\updated after installing SQL server. Easy step will be to run SQL repair - Steps here.

Alternative solution is to install SQL Native client and can be downloaded from here under install instructions.

Again, above solution will only solve current error. It will be great if you could share more information like when it got corrupt and what was installed to provide you better solution.


What is the Difference between "Internal" and "External" Source Systems in Data Warehousing?

You could get data from data providers - for example, geoip data from Maxmind, rating data from Moody's, device data from BlueCava. Those are all external systems. Systems used by the organization are internal systems.


Would SQL Server in a Cluster failover if it is running out of RAM

Starting with SQL Server 2012 the cluster will have a more comprehensive health check which allows for a possible resource based health condition using the SP_Server_diagnostics procedure. https://msdn.microsoft.com/en-us/library/ff878233.aspx

More on that failover policy can be read about here: https://msdn.microsoft.com/en-us/library/ff878664.aspx#monitor

Unfortunately though SQL Server 2008R2 and earlier doesn't have much of a sophisticated cluster health check for the SQL Service. Simply the health check will just issue the following command "SELECT @@Servername" and if it returns in a timely fashion the health check passes.

I would look instead though at why is the server under memory pressure and setting the max memory setting in the SQL Server instance putting a cap on that memory foot print.


DBA is a small acronym but a large role. At various times I have seen a DBA look after

storage
network
VMs
compute nodes
installation and configuration of the above
backups, and testing restores
DR strategy
enterprise data integrity
ETL
data security
data modelling, normalisation and database design
data tier programing
performance tuning
operational availability
replication
failovers
on-call support

.. and others besides. I cannot see any of these disappearing just because Oracle/ SQL Server/ PostgreSQL is replaced by MongoDB/ Cassandra/ CouchDB.

"Oh," but you say, "There's no schema. I don't have to do that modelling & design thing."

"You're deluded," I would reply. If the system is for, say, order processsing (and not order processing and chocolate cake recipies and cataloging selfies etc.), and The Business distinguishes Orders by a 7-digit integer Order Number, and each Order consists of one or more Order Lines (referencing one Product each), then you have a schema. Just because it's enforced in the application layer and not in the DBMS does not make it disappear. And somebody had better be writting those rules down, and confirming them with the users, and communicating them to the test team, and teaching newly-hired employees what they are, and getting them into the end-user documentation, and ensuring old data can still be read by the just-changed application. Just because the rules can be changed in application code alone, rather than application code and DBMS script, does not make any of that stuff any less important.

So, for my two cents' worth, despite its many, many benefits I don't believe this class of software removes any significant task traditionally done by a DBA in the RDBMS world. It may redistribute them onto people holding other job titles, and the tasks may become easier to perform or less intrusive into the SDLC.

As for adding tasks, there is the issue of maintaining compatibility with old data written according to a previous understanding of the business rules. An RDBMS would not have this problem as there is only one acceptable schema at any point in time.


Oracle "new or old reference not allowed in table level trigger"

You need a row before level trigger for that.

And you don't need an update, just assign the new value:

create or replace trigger updshe
  BEFORE update of Shift_Start on Pharmacists
  FOR EACH ROW
begin
  :new.shift_end := to_number(:new.Shift_Start, '99') + 8;
end;
/


Looks like you left part of your command off.  To be clear, you'll need to use the mysql client for the import.  But, in general that should work.  Another option is to just connect to the client (same command as before, but with out the the

Once in you can run

mysql> source /path/to/outfile.sql


How can I select data from two differents servers?

Federated storage engine can help you.

In mysql, you can achieve this by using federated storage engine. It allows you to access tables from different server. Once you reference the table from specific server, you can use it. You can get more details about
federated storage engine. Now you can join two tables from different servers. how to use federated engine

ETL tools can help you like Talend data integration

Talend is open source tool which is ETL tool uses in data ware housing. It have nice UI, you can learn it in short duration. Here you can create many Mysql or database connections  and use the different connections for different tables, can join whatever as you need.


How do I import updates to unique data in MySQL

The opposite for INSERT IGNORE is INSERT ON DUPLICATE KEY UPDATE

Refer to the chapter 14.2.5.3 of MySQL reference for further details.


How to remove options from mariadb tables?

Solution:

  alter table materiales row_format=default,transactional=default, engine=InnoDB;


Insert Into table ommitting columns that have default values

Is there a way of doing this without having to list all columns that don't have a default value?

No you either insert all the columns using the * expansion,

INSERT INTO foo
  SELECT * FROM bar;

Or, you insert just the listed columns

INSERT INTO foo (col2,col3)
  SELECT col2, col3
  FROM bar;

You can also use DEFAULT to be explicit, but no one does that. It's in the spec however, Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none. Being explicit looks like this.

INSERT INTO foo (col1,col2,col3)
  SELECT DEFAULT, col2, col3
  FROM bar;

DEFAULT COLUMNS sets all columns to their respective default. From the docs on DEFAULT COLUMNS,

  DEFAULT VALUES, All columns will be filled with their default values.

CREATE TABLE foo (id serial, foo serial, bar serial);
INSERT INTO foo (id,foo,bar) VALUES (DEFAULT, DEFAULT, DEFAULT);
INSERT INTO foo DEFAULT VALUES;
INSERT INTO foo(id,foo,bar) VALUES (42,42,DEFAULT);

TABLE foo;
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
 42 |  42 |   3
(3 rows)


MongoDB: Should I combine fields for performance in limited circumstances

Without combining the fields in your data set you could just create a compound index using both.  No changes to your data necessary.  To do so, just create the index like so:

db.collection.ensureIndex({"user" : 1, "domain" : 1})

Docs are here:

http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeys

Once you have created such a compound key it essentially makes an index on the leftmost element (user in my example above) redundant, and so an index on user (if it exists) could be removed.

Don't forget that the query optimizer only runs every ~1000 queries, so you will have to hint() the index to make sure it is used if you are testing it out.


How to print empty string as a result from xml node creation using oracle in a procedure

You use REPLACE in your code, with 1 more REPLACE:

SELECT REPLACE(XMLElement("TableData",
          XMLAGG(XMLElement("rt",XMLAttributes(1 as file_data_type, '' as Author)))
       ).getStringVal(),'></rt>','/>')
  as xml
FROM dual;

XML
--------------------------------------------------------------------------------
<TableData><rt FILE_DATA_TYPE="1"/></TableData>

SELECT REPLACE(XMLElement("TableData",
          XMLAGG(XMLElement("rt",XMLAttributes(1 as file_data_type, 'dummy' as Author)))
       ).getStringVal(),'></rt>','/>')
  as xml
FROM dual;

XML
--------------------------------------------------------------------------------
<TableData><rt FILE_DATA_TYPE="1" AUTHOR="dummy"/></TableData>

SELECT REPLACE(REPLACE(XMLElement("TableData",
          XMLAGG(XMLElement("rt",XMLAttributes(1 as file_data_type, 'dummy' as Author)))
        ).getStringVal(),'></rt>','/>'), 'dummy')
  as xml
FROM dual;

XML
--------------------------------------------------------------------------------
<TableData><rt FILE_DATA_TYPE="1" AUTHOR=""/></TableData>


What causes 'Unknown token received from SQL Server'?

The error message means client TDS* processing received a token it didn't expect.

This might be a bug, but if so, it is most likely an old one that has long been fixed. You should ensure that the client connectivity components (e.g. MDAC, SQL Server client) are patched up to date (as far as possible, given the old technology).

You should also check that your SQL Server 2005 is running at least Service Pack 4 (though 2005 is out of mainstream support now too).

*tabular data steam; SQL Server's communication protocol


Guid vs INT - Which is better as a primary key?

This has been asked in Stack Overflow here and here.
Jeff's post explains a lot about pros and cons of using GUID.

###GUID Pros

Unique across every table, every database and every server
Allows easy merging of records from different databases
Allows easy distribution of databases across  multiple servers
You can generate IDs anywhere, instead of having to  roundtrip to the database, unless partial sequentiality is needed (i.e. with newsequentialid())
Most replication scenarios require GUID  columns anyway

###GUID Cons

It is a whopping 4 times larger than the traditional 4-byte index
value; this can have serious
performance and storage implications
if you're not careful
Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
The generated GUIDs should be partially sequential for best
performance (eg, newsequentialid() on
SQL Server 2005+) and to enable use of
clustered indexes

If you are certain about performance and you are not planning to replicate or merge records, then use int, and set it auto increment (identity seed in SQL Server).


Passing a table name to a stored procedure

As @Shaulinator stated in his answer; the only way to accomplish this is to use dynamic SQL.

Having said that, I would caution against applying the dont-repeat-yourself mantra too heavily to database code where it doesn't necessarily belong.  I realize your question is about Sybase ASE (now SAP ASE); however since SQL Server and ASE share a very common language I would point you to the dynamic SQL "bible" by Erland Sommarskog, The Curse and Blessing of Dynamic SQL

I would recommend templating your permissions code, such that you do a search-and-replace on the object name whenever you add a new object.  Retain the modified template code in your version control system; most likely in the same place as the object code itself.


Query to select 'Customer_Num' with digit characters using regular expression

Neither is correct because both only check for a single digit that can appear anywhere in the string.
A regex that only selects digits in the complete string would be:
where REGEXP_LIKE(customer_num, '^[0-9]+$')

^ anchors the expression at the beginning of the string (to avoid leading non-digits)
[0-9]+ matches multiple digits but at least one
$ anchors the expression at the end of the string (to avoid trailing non-digits)

There is no difference between using [0-9] and using [[:digit:]]


Reporting services - High availability setup - SQL server 2012

Solutions that provide HA will serve you well - both a failover cluster instance or AOAG. Technet articles are great to help you with this and understand it deeper.

For instance, if you use a FCI (SQL Server failover cluster instance), while installing you provide a network name which identifies the instance. Your SSRS only needs to know this networkname\instancename. Then, when the primary node failsover, another node will assume the work and keep serving your SSRS with the databases. Do notice that this solution undergoes a small downtime - the time for the cluster to disconnect the disks from the prior primary node to the "new to be" primary node, while transfering the cluster groups.

If you use AOAG, your SSRS will have to work with the VNN (virtual network name), and, when a failover occurs, a secondary node will assume the work and keep providing data. As in this case the node has its own disks (providing a second copy of the data, while on FCI you just had one copy of the data), the failover doesn't face the "transfer the disks" downtime.

As for the report server itself, taking into account your comment, this pages will provide you great guidance to scale-out:

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

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

You may want to consider setting up a NLB to take advantage of having more than one SSRS server. This should be ocnfigured first, and you can read about it here:

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

And, just in case, some aditional information for Reporting Services with AlwaysOn Availability Groups:

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


MySQL:- Fetch records from 31 to 60 days

WHERE   date_created >= CURDATE() - INTERVAL 60 DAY
  AND   date_created  < CURDATE() - INTERVAL 30 DAY

WHERE   date_created >= CURDATE() - INTERVAL 90 DAY
  AND   date_created  < CURDATE() - INTERVAL 60 DAY

No, don't use 31 and 61; use the inequality.

CURDATE() will give you boundaries at midnight; NOW() will be based on the current time.  (This matters if date_created is a DATETIME instead of a DATE.)

Don't use DATEDIFF, it would let you use any index involving date_created for efficiency.


sql agent job notification email without configuring database mail

You can easily invoke a Powershell script:
(Change the FromEmailAddress, ToEmailAddress and SmtpClient information)

$computerName = gc env:computername
$emailFrom = $computerName + “<EmailFromAddress>.com”
$emailTo = "<EmailToAddress>.com"
$subject = "Testing email"
$emailBody = "This is an example of sending email via Powershell"
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
$message.Priority = "HIGH"
$smtpServer = "<YourSmtpServer>.com"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)


SQL Server Agent Jobs and Availability Groups

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

All this does is pull the current role of the local replica, and if it's in the PRIMARY role, you can do whatever it is that your job needs to do if it is the primary replica.  The ELSE block is optional, but it's to handle possible logic if your local replica isn't primary.

Of course, change 'YourAvailabilityGroupName' in the above query to your actual availability group name.

Don't confuse availability groups with failover cluster instances.  Whether the instance is the primary or secondary replica for a given availability group doesn't affect server-level objects, like SQL Server Agent jobs and so on.


How to efficiently clone database records together with all child elements

a new version of that page is created in a new record for auditing reasons.

How do you know which "version" is the latest, you're going to process on version and select the last one? That'll work, but I think you're barking up a pretty steep tree. Essentially you're building your own revision-control system in a database. Not saying everyone hasn't tackled that task before, but issues likely to come up are

Merging (other users)
Rollbacks (without which, why would you keep old versions)
Diff-views
Delta stores (why store the whole element when you only change one character)
etc, etc.

This gets super complex very fast. If you're wanting an out of the box solution, checkout gitlab. If you have speced to just stop here and not feature creep -- I think this will work just fine.


SQL Server update query on linked server causing remote scan

A friend just had a similar problem and rewriting the query solved it.

I know this is an old question, and likely you have worked it out, but for anyone else you could try this and report back...

UPDATE kg
SET
   IdGarment = IdGarment
   FROM Products p
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID
   WHERE log.ActionType = 'I' ;

I don't know why this helped, but using the UPDATE "alias" form of the statement meant that the whole UPDATE query was remoted, rather than pulling the data back to the local instance to perform the JOINs.

If anyone has any ideas why this behaviour was seen, I would love to know.


Is it worth it to run VACUUM on a table that only receives INSERTs?

tl;dr: The first process that reads data after it is committed will set hint bits. That will dirty the page, creating write activity. The other thing VACUUM (but not other commands) does is marks the page as all-visible, if appropriate. VACUUM will eventually have to hit the table to freeze the tuples.

The work that needs to be done after an insert isn't really cleanup, at least not in the sense of the other work VACUUM normally does. Before I go into the details, note that this answer is based on current (unreleased) 9.6 code and I'm ignoring the effects of streaming replication, even though it can have an affect on visibility.

Because of MVCC, every time Postgres evaluates whether a tuple should be visible to a query, it must consider whether the transaction that created the tuple (recorded in the xmin hidden field) committed, along with some other criteria. That check is expensive, so as soon as it is known that a transaction is visible to all currently open transactions a "hint bit" is set on the tuple header indicating that. The setting of that bit dirties the page, which means it will have to be written to disk. This can be very confusing if the next command to read the data is a SELECT that is suddenly creating a lot of write traffic. Running a VACUUM after the insert commits will avoid that. Another important distinction is that VACUUM will ALWAYS hint tuples on a page (as long as it got the cleanup lock on the page), but most other commands will only hint if the inserting transaction committed before the command started.

An important point about writing all these hint bits is that VACUUM can be throttled (and autovacuum is throttled by default). Other commands are not throttled and will generate dirty data as rapidly as possible.

VACUUM is the only method for marking pages as all-visible, which is an important performance consideration for some operations (notably, index only scans). If you do a large insert it's very likely that there are many pages with nothing but newly inserted tuples. VACUUM can potentially mark those pages as all-visible, but only if the oldest running transaction when VACUUM started was newer than the transaction that inserted the data.

Because of how MVCC works, tuples that were inserted more than ~2 billion transactions ago must be marked as "frozen". By default autovacuum will kick in to do that every 200M transactions. Running a manual vacuum with vacuum_freeze_min_age set to 0 after a bulk insert can help reduce the impact of that. More aggressively, you could run VACUUM FREEZE on the table after inserting. That would "reset the clock" on when the next freeze scan would happen.

If you want to know the specific details, take a look at the HEAPTUPLE_LIVE case after the call to HeapTupleSatisfiesVacuum() inside lazy_scan_heap(). See also HeapTupleSatisfiesVacuum() itself, and compare it to HeapTupleSatisfiesMVCC().

There are two other presentations of mine that might be interesting.  The first video is available from http://www.pgcon.org/2015/schedule/events/829.en.html, while the second one (which I think was a bit better) at https://www.youtube.com/watch?v=L8nErzxPJjQ


If the mysql service is up right now, shut it down

C:\> net stop mysql

You need to create my.ini

Go to  C:\Program Files\MySQL\MySQL Server 5.5

You should see a bin folder and the data folder you have in your question

Create my.ini in C:\Program Files\MySQL\MySQL Server 5.5

I noticed you have

ibdata1
3 innodb log files
binary logs that start with mysql-bin

Make sure you have these options in my.ini

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=3
innodb_log_file_size=256M
innodb_buffer_pool_size=768M
log-bin=mysql-bin

You need to start the service manually

C:\> cd "C:\Program Files\MySQL\MySQL Server 5.5\bin"
C:\> net start mysqld

By default, when starting up mysqld like this, mysqld.exe expects the my.ini to initially be in its parent directory.

If mysql starts successfully, you should see mysqld.exe in the processlilst of the Task Manager and the file clarinet.err should have today's date. You can shut it down using:

C:\> cd "C:\Program Files\MySQL\MySQL Server 5.5\bin"
C:\> mysqladmin -uroot shutdown

Now configure mysql service so that it uses C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe


SQL Server 2012 Crashes and service doesn't restart

You are running into a known bug - There is a fix for it but update to SP2 rather than installing the hotfix.

The errorlog contains SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1). and you are using availability groups. The memory leak  is in the MEMORYCLERK_SQLLOGPOOL which is used for transaction log activity.


How do you select "array"-type columns via the DBLINK function?

The solution I found was via casting:

Specify the column in DBLINK SQL as TEXT
Cast to text in the DBLINK SQL statement
Cast from TEXT to POINT[] outside of DBLINK

PostgreSQL: What is the best way to cast from from text to point[]?


Dump based backups (logical backups) and recommended for small-medium databases, however, you could speed up logical backup by using enhanced logical backup tools like mysqlpump or mydumper.

Raw backups are recommended for large databases and there are two major tools to explore: mysqlbackup (Enterprise backup) and Percona xtrabackup (free - only Linux based environments).


Alter of stored procedure and execution performance

Had a situation where a production environment had DB schema updated
The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why.

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query.
From BOL - Recompiling Execution Plans :


Postgresql: Sortable index on array values

If your use case is to retrieve all array elements of all rows, an index is not going to help with that. Postgres indexes can only hold a single index entry for a single table row. And a GIN index would not support the sort.

The best option I see would be a MATERIALIZED VIEW - which needs a refresh after any write operations on the underlying table that might influence the result. So probably only useful for read-only (or mostly) tables.

CREATE MATERIALIZED VIEW foo_elements AS
SELECT unnest(bar) AS u, id
FROM   foo
ORDER  BY u;


Select last non-null value for given partition - Postgres 10

You can try to use MAX function with coalesce to make it.

Schema (PostgreSQL v9.6)

CREATE TABLE T(
   date date,
   city_id int,
   country_id int,
   free int,
   occupied int
);

insert into T values ('2017-01-01',2,3,null, 2);
insert into T values ('2017-01-02',2,3,4, null);
insert into T values ('2017-01-02',2,3,null, 5);
insert into T values ('2017-01-04',3,4,2, null);

Query #1

SELECT
  date,
  city_id,
  country_id,
  coalesce(MAX(free),0) free  ,
  coalesce(MAX(occupied),0) occupied
FROM T
GROUP BY date,city_id,country_id
order by date;

| date                     | city_id | country_id | free | occupied |
| ------------------------ | ------- | ---------- | ---- | -------- |
| 2017-01-01T00:00:00.000Z | 2       | 3          | 0    | 2        |
| 2017-01-02T00:00:00.000Z | 2       | 3          | 4    | 5        |
| 2017-01-04T00:00:00.000Z | 3       | 4          | 2    | 0        |

View on DB Fiddle


What are the real life cases for using Oracle advanced queuing?

I am not as familiar with Oracle as I am with Postgres.  Nonetheless, I will tell you where I see queuing approaches like this as ideal (as the author of pg_message_queue):  allowing database transactions to have non-transactional side-effects with minimal complexity cost.  A simple example is "when we commit a transaction saying we have shipped a part, let's send an email out notifying the customer."

You could do this without Oracle AQ, but Oracle AQ will likely make this simpler.  The key factor is that this allows you to enqueue the message such that it is visible on commit, and have the message which will be the basis of an email once the database transaction commits and not before.  If you try to send the message from inside the transaction, you get a number of nasty failure cases:

If the message fails to send should we abort the transaction?
If the message sends and the transaction later is rolled back, how do we un-send the email?  We can't.

Another example may be loosely coupled applications, integrated over a message queue.  Each application can largely run unaware of the other, but when transactions are committed, messages are sent to the other.  If they can't be delivered immediately, no problem.  We will process them when we can.

So the basic thing is that there are many cases where you want to centralize logic around the database where you can't really do everything properly in a single transaction.  Being able to send messages to other components on db commit is really helpful.

And sure you could build everything yourself without AQ.  But this has it already built for you.

Edit: I have re-read the Oracle docs here and they are hopelessly confusing, so I don't blame you for your confusion here.  If I wasn't already somewhat familiar with the sorts of things they were talking about, I am not sure I would have been able to follow them.  I am now 100% sure that my answer is on-target though.


First of all, thanks to @Dan Guzman for his inputs, somehow it sparked something in my head. :)

I found out the reason for the break in the LSN chain!  As I mentioned, the chain breaks after a backup is made to 'NUL', and I just came to know that 'NUL' is a sort-of file system, where data is written to it but then deleted afterwards. So obviously, if a transaction log is written to 'NUL' it creates a valid LSN but then since it gets deleted it cannot be restored to the secondary database and therefore breaking the LSN chain.

Now, i have this quote from this white paper,

  If you select “Truncate logs” inside the SQL tab then Veeam will perform a log backup to the file name “nul” after the snapshot was produced. It will do this for all databases that are in full or bulk logged recovery model. This will render your own log backups performed after this useless.

So it is a third-party backup software, VEEAM, that is causing the issue.  Right now, what I did was I still let VEEAM manage its own backups of the primary database, but disable VEEAM from truncating logs and let it just copy the transaction logs instead.  Problem solved, now my log shipping is running without a hitch. :)


4 tables, find department with the highest average salary

First query:

select
    e.employee_name as employee
    ,e.street
    ,e.city
    ,mgr.employee_name as manager
    ,mgr.street
    ,mgr.city
from
    employee e
    join manages mgs on mgs.employee_name = e.employee_name
    join employee mgr on mgr.employee_name = mgs.manager_name
where
    e.street = mgr.street
    and e.city = mgr.city

Second query:

select
    d.department_name
    ,avg(salary) as AvgSalary
from
   department d
   join works w on w.department_name = d.department_name
group by
   d.department_name
order by
   AvgSalary desc


Automation of backups of Large Number of Database

At my previous job I did exactly this. We were in the 500 tenant range, but once you get above 10 or 20 the automation part makes the actual number much less important.

Assuming that your hardware can handle backing up 10,000 databases in a sufficient time, and that all 10,000 aren't having significant enough activity to overwhelm your server with or without backups, the next step is to automate the process in general. The nice thing about this model is that if 10,000 databases end up overwhelming your server, it is much easier to move half of them to a second server and point your applications at the right instance depending on the tenant, than it is to try and rip half of the data out of a single database. Do you have some that are in FULL and some in SIMPLE, or are they all in the same recovery model?

We had priorities. So a table to hold the tenant information (in my implementation there were many more columns, but this should be the basics):

CREATE TABLE dbo.TenantBackups
(
  TenantID      INT PRIMARY KEY,
  RecoveryModel VARCHAR(6),
  Priority      TINYINT
);

And then a history table (again, more to it in the actual system, but for the purposes of the question):

CREATE TABLE dbo.TenantBackupHistory
(
  TenantID   INT,          -- FOREIGN KEY,
  BackupType TINYINT,      -- lookup for FULL, LOG, RESTORE_TEST
  StartTime  DATETIME,
  EndTime    DATETIME,
  Status     NVARCHAR(1000) -- almost always NULL but held true "exceptions"
);

So now the job(s) could run through the tenants, ordered by priority, and perform backups. We actually had separate jobs for priority 1s vs. priority 2-5.

We tested priority 1 tenants nightly by restoring their full backups on a backup server. We also picked 20 random databases from the other priorities each night to test their restores as well. There were alerts etc. configured of course so that failures were immediately known, and if the exception was outside of our normal retry/error handling the Status column would contain information.

We also used SQL Sentry to chain jobs so that we didn't have to worry about scheduling a buffer and trying to predict when the backup jobs would finish. For priority 1 databases we didn't bother to wait for the backups to finish as a whole; we had a background thread that would wait for each individual full backup to complete and then would immediately pick it up off the queue and start the copy/restore test processes. For priority 1 tenants we also used the restored copy as a reporting offload - for any reports that were interested in data before today (which was most reports), they could run off the copy, because the only thing it was missing was today.

For full recovery tenants (not all customers required point-in-time), the log backup jobs could easily check the history table to see if a backup of any kind was already in progress. But of course error handling and retry logic was implemented all around.

I know these are very generic descriptions but I hope they point you in the right direction. Let me know if you'd like me to expand on any details.


Why did my transaction log grow so much?

It sounds like your database is in full recovery mode. Under this recovery model your transaction log will continue to grow until it is backed up.  Because you're not backing up your transaction log you aren't able to reuse any portion of it.  This is all for point-in-time recovery. After you back up your transaction log, the reserved file space for the t-log file will not shrink. You will need to explicitly shrink that file with DBCC SHRINKFIKE. Note: file shrinking should not be a routine operation, but only to do a one-time on demand shrink when you know that file space will not be needed.

Which brings me to my next point. If you need this point-in-time recovery then you need to schedule transaction log backups routinely in order to prevent t-log growth (as you're currently seeing it). If for some chance you only require a recovery point objective that full backups and differential backups can deliver, then you should consider the simple recovery model.  Consideration to move to that recovery model needs to be a sound one, as you are losing a certain and import aspect of disaster recovery that most enterprise production databases require.

See this BOL reference on transaction logs and their truncation: http://msdn.microsoft.com/en-us/library/ms190925.aspx#Truncation


Show cassandra help documentation in terminal instead of browser

From cqlsh reference documentation:

  cqlsh help displays in the terminal. CQL help is only available online in HTML.

You can download offline documentation in PDF & HTML formats from DataStax documentation site.


Unable to insert a data in a table in mysql

That number 9619362665 (9,619,362,665) is too big

Click This Link to See the Max Integer Types.

The biggest INT is 2147483647 (2,147,483,647).

If you want to store numbers larger than 2147483647, change the column as follows

ALTER TABLE USER MODIFY COLUMN MOBILE_NUMBER BIGINT;

Then, the INSERT should work.


Using REGEX_LIKE to Implement a Check Constraint

This isn't really a database administration question, but...

Firstly, your pattern does not prevent entries like this: XXXINT123abogus, because it does not say that "INT" or "NINT" should be at the beginning of the string, neither does it say that the lowercase letter, if it's there, should be the last symbol of the string.

You may want to try something like:

 +----------------------------- begins with
 |+---------------------------- optional "N"
 ||  +------------------------- then "INT"
 ||  |  +---------------------- then digits
 ||  |  | +-------------------- minimum 2
 ||  |  | | +------------------ maximum 4
 ||  |  | | |   +-------------- then a lowercase letter
 ||  |  | | |   |  +----------- which is optional
 ||  |  | | |   |  |+---------- and nothing more
 ||  |  | | |   |  ||
 ||  |  | | |   |  ||
'^N?INT\d{2,4}[a-z]?$'


Two identical MySQL tables, one not using indexes

Finally I've been able to figure out the problem, yet I still don't know why it happenned!

I've found this answer.

Doing ALTER TABLE acs_spectotechnologies_com_enerconcept_0005.measuresHistory ENABLE KEYS; fixed the usage of keys for this table, now every requests are lightning fast!


Postgresql-12 replication status in recovery mode

You can ask primary server about replication status of standby server(s) by using pg_stat_replication which is a system statistics view.
You can use WAL Archiving to setup a replication instead of / in addition to streaming replication


JDBC pooling Oracle driver Socket read timed out

Since the client is at the same server as the database you could try the IPC protocol instead of TCP as a workaround.
It is not only independent of the TCP traffic conditions but also MUCH quicker than TCP.

Your connection string would be something like

jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY = LISTENER)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

As you are using connection pooling, your only verification should be dead connection (SQLNET.EXPIRE_TIME).
Oracle has several notes explaining it's functionality but it basically says it's deppendent of the TCP network layout:

Dead Connection Detection (DCD) Explained - Doc ID 151972.1.
Common Questions About Dead Connection Detection (DCD) - Doc ID 1018160.6

It could be a profile configuration problem (the user connected is not allowed to stay inactive for more than x seconds, the IDLE_TIME limit).
You could verify your profiles with:

set linesize 1000
set pagesize 1000
select *
from dba_profiles
order by profile;

Also get your network checked.


DBMS_ID is a subtype in DBMS_STANDARD starting with version 12c.

DBMS_ID does not exist in 11g.

You have a messed up environment with 12c scripts and a 11g database.

The code that fails is in $ORACLE_HOME/rdbms/admin/reenable_indexes.sql, which is called by $ORACLE_HOME/rdbms/admin/utlprp.sql, which is called by $ORACLE_HOME/rdbms/admin/utlrp.sql.

This is an environmental issue but we do not know what you have done so far. In a properly created and configured environment this would not happen.

If you want to use 11g, look for a 11g image. There must have been someone desperate enough to create one. Like this one: docker-oracle-ee-11g. No, I haven't tested it.


Help understanding database design logic for a secondhand bookstore project in MySQL

You don't sell to users, you sell to parties (companies or individuals). Some might play the role of users.
There are many parties involved in a sales/purchase order:

the vendor (you if you sell)
the buyer (you if you buy)
a party selling something on consignment
sales staff taking commission
the party that placed the order
the party that took the order (a clerk)
the party that is paying for the order
the parties to whom to ship the order items

Do yourself a favour and get a data model pattern book and don't reinvent the wheel :)

Ready-to-Use Database models example


Dropping historic Partitions that are no longer required while in P2P replication

So, no answers from here, but after a certain amount of discussion and thought, I came up with a plan some months ago.

I will make this answer concise for this forum (you may not agree that I have!), to try to assist anyone that needs to perform a similar task in the future, feel free to ask questions if I miss anything out - though the method is straight-forward.

So, the primary concern is to remove the data without significant impact on Production traffic at the nodes we are replicating to/from. The easiest way to do this is to segregate a node that you want to work on, removing the data from that node whilst leaving all others unaffected (including the Reporting nodes).

Best way to do this (remember you can't drop partitions and any/most operations are replicated and therefore create a large amount of traffic and a large amount of row changes), is to create a new SP and setup a Publication around this one SP. It should therefore be available at all nodes. The important bit is to set Replication to replicate the Execution of the SP - NOT the result (i.e. replicate the EXEC Sp_delete call NOT the delete WHERE ID = 1, delete WHERE ID = 2 - row level changes). This is set in Right click your new Publication (before setting up the other nodes in the Topology) > Properties > Articles > Click the SP_Delete you have setup > Article Properties button > Set Properties of Highlighted Stored Procedure Article > Replicate line = EXECUTION of the stored procedure. Complete your P2P topology.

BUT MHSQLDBA, you might be saying, that's just going to separately delete the rows at every node via the SP. - This is why you set the SP to only do the deletions:

IF @@SERVERNAME='The current server you want to affect'

Follow it with your delete procedure.

Thus, when this EXEC call is picked up at the server(s) that you don't want to perform the Deletes, it will be ignored as @@Servername will not equal the Server you have selected.

You may think - why not just create an SP at only the Server you are interested in and run that? - this is because if you do this, replication will break down the changes into how they affect the Article (table) rows and replicate the actual changes - you have to replicate the SP so that you can specify that the EXEC of the SP be replicated rather than the resultant changes.

This is the suggested order of events in my opinion/experience:

Create SP with delete code that specifies that it will only run the delete code if @@SERVERNAME = your desired server
Setup a new publication that Replicates this 1 SP with the Replicate = Execution of Stored Procedure within Article Properties
Run the SP at your desired server and be happy that you haven't brought down the whole estate with thousands of replicated DELETE commands

Points of Note:

This is still a laborious task. By using this method you have diminished your effect on all servers apart from the one you are working on. You have not diminished the workload for you, in fact you have made it worse - you are going to have to run this same SP at each node (with the IF line changed to the server you are targeting), effectively increasing the work you have to do, by the number of servers you have to affect. It is massively safer though as you will have minimal effect on all other nodes (I'm presuming you've failed traffic away from the node  you're working on of course!)
By using this method, you have created inconsistency between your nodes - you really need to be sure that the data you're removing IS NOT going to change before you can  finish performing the same operation on all nodes that require work. If a row you have deleted at 1 node is changed within the rest of the Estate you are going to end up with consistency errors.
You are likely going to put your normal replication expected SLA's behind by the amount of time it takes to perform the deletes at the node you're working on (I highly recommend you read up on batching the deletes) - therefore you need to be aware that once the operation completes, you won't have the node back in action until normal replication has caught back up after your Delete operation's locks have released. If you are replicating over high latency lines, I seriously suggest you check out using PULL agents instead of PUSH - it makes a humongous difference.

There is probably a better way of moving the data away in the SP than using delete - maybe moving it to another table that is not involved in replication and then dropping the 'new' table - or the reverse, if the data you want to keep  is less than the amount to delete, move the data you want to keep to a new table, drop the old one then rename your new table - there is lots of advice out there from these perspectives - I work in an environment where it was easier to fight for deletion than promote a concept that some personnel won't understand, so I'm describing the painful but basic way.

DISCLAIMER: All of the above is dangerous. If done hastily without appropriate forethought, you can seriously mess up a replication topology, your Company's data and probably your employment. Please take the above method and develop your own battleplan - create a test environment to prove the concept, test test and retest, do not take this task lightly. With enough consideration you will achieve your task - but it's not worth doing on Friday afternoon after a couple of lunchtime beers. Do it right, do it once (for real-test as much as you can), do it properly.

I hope this helps someone else. - I'm adding this bit as it is what I would have searched for if I wanted this answer:

Delete large amount of data from a Peer-to-Peer replication topology


Restore Database in SQL Server 2014 with different name

Perform a RESTORE FILELISTONLY first, so you understand the logical and physical names of the files.

RESTORE FILELISTONLY FROM DISK = 'c:\temp\foo.bak';

From there, you just need to fill in the pertinent pieces here:

RESTORE DATABASE foo_copy FROM DISK = 'c:\temp\foo.bak'
  WITH REPLACE, RECOVERY,
  MOVE N'foo_data' TO 'c:\...\foo_copy.mdf',
  MOVE N'foo_log' TO 'c:\...\foo_copy.ldf';

In case it isn't clear: don't use the UI for this. Pointing and clicking is not something you can easily reproduce, document, or store in source control. It can be a useful way to generate the commands in some cases (using the handy Script button), but in the long run you're much better off knowing the commands the UI would have generated anyway.


Oracle Newbie Question - How can one run selects from different tables but show output on separate rows

Is possible to merge/add the result of two or more different queries using:

UNION ALL

But count and names of columns must match on each query.

Of course you can add null or fixed values as needed.

SELECT COL1 AS ID, COL2 AS NAME, NULL AS INFO
FROM   TABLE1
UNION ALL
SELECT F1 AS ID, F2 AS NAME, INFO
FROM.  TABLE2
UNION ALL
SELECT 0 AS ID, NAME, INFO
FROM   TABLE3


Limit of Generating Script for Schema Only from SQL Server 2012 to SQL Server 2008R2

Schema generation will totally depend on the amount of objects (tables, SPs, triggers, indexes, etc) in database.

Schema is just a skeleton of the database without data.

If you are concerned about using SSMS GUI, you can use PowerShell - which will definitely scale better - and can be automated easily as compared to SSMS GUI.

I have tried scripting out schema of 1TB data warehouse database without any issues (as I said previously, the more objects you have, the bigger script it will be - and slower too (better to use PowerShell)).


Comparing binary 0x and 0x00 turns out to be equal on SQL Server

I couldn't find this comparison behaviour specified anywhere in BOL.

But the Connect Item Invalid equality comparison for varbinary data with right padded zeros states that

  Basically, the standard leaves it up to implementation to treat
  strings that differ only by [trailing] 00 as equal or less. We treat it as equal.

The Connect Item also states that the presence of trailing zeroes is the only case in which SQL Server differs from byte-by-byte comparison behavior.

In order to distinguish between two binary values in SQL Server that differ only by trailing 0x00 characters you can also add DATALENGTH into the comparison as indicated in your question.

The reason for preferring DATALENGTH rather than LEN generally here is because the latter gives an implicit cast to varchar and then you get the problem with trailing spaces.

+-------------+--------------------+
| LEN(0x2020) | DATALENGTH(0x2020) |
+-------------+--------------------+
|           0 |                  2 |
+-------------+--------------------+

Though either would work in your use case.


How to prevent redundant data that is hard to detect, e.g. John Smith

Perhaps you will solve this problem with little help from your UI person. Check for the presence of the name in the DB before making a new entry. If similar name is found in the DB, show some kind of warning in the UI that the user should be able to override.

Otherwise, as you perhaps realize, you can either have a unique constraint in the DB or not.


As count() counts only the non-NULL values, this is a simple GROUP BY query:

SELECT week, count(col1), ..., count(col10)
  FROM your_table
 GROUP BY week;


Prevent the collision data because duplicate id from imported database MariaDB

A few options: Allocate different ID ranges for each user, use composite primary keys, use UUIDs or use some of the mechanism that enables the auto_increment feature to be used in master-master replication:

You can specify a different AUTO_INCREMENT position when you create the tables for each individual employee's database so that there is a range of IDs allocated for each employee. E.g.

CREATE TABLE electronic_parts (
  id INT USIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  electronic_part_name VARCHAR(40),
  kind_of_electronic_part INT UNSIGNED
) AUTO_INCREMENT=1000;

This is probably not a good idea as there is nothing to prevent over-zealous employees from creating so many records that they end up in someone else's range. (Though I suppose you could build a constraint into your application to avoid such collisions.)

Re-design the primary keys so that they become composite keys with one auto-incrementing key as you have already and one key that is unique for each employee. E.g..:

CREATE TABLE electronic_parts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  electronic_part_name VARCHAR(40),
  kind_of_electronic_part INT UNSIGNED,
  PRIMARY KEY (id, employee_id)
);

You application then has to populate the employee_id with a unique value for each employee. E.g.:

INSERT INTO electronic_parts (
  employee_id, electronic_part_name, kind_of_electronic_part
)
VALUES (156901010, 'switch', 1);

Thanks to the auto-incrementing id component of the primary key, you don't have to worry about populating that.

Once it's time to merge all the databases, all will be fine because the primary keys are composites with one component unique for each employee, and one component unique within each individual database, so together this makes each primary key value unique across all databases.

I've tested this successfully in MariaDB 10.2.

Use the UUID() or UUID_SHORT() functions to generate globally unique primary keys instead of auto incrementing primary keys. Your application then needs to either explicitly call one of these functions when creating the records, or you could use a trigger. (I personally think it's bad design to use triggers this way, but it does work.)
Assuming you know a fixed upper limit to how many users there will be (n): You can use auto incrementing primary keys and set auto_increment_increment to n, and then set auto_increment_offset to 0 for the first user, 1 for the second user, 2 for the third user, and so on. This way you will avoid collisions in the same way that auto_increment is used in Galera clusters and other master-master replication configurations.


How to deny drop permission for a table?

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;

http://msdn.microsoft.com/en-us/library/ms173724.aspx


Query with different time period in same query ( WHERE )

Well first factor our the seller_id IN (1,3) and use USING

SELECT
  COUNT(DISTINCT order_id)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date)                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date)                     AS total_amount_until_now,
  COUNT(DISTINCT order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date) AS total_amount_until_a_month_ago
FROM order_items a
INNER JOIN orders b
  USING (order_id)
WHERE b.seller_id IN (1, 3)

You can use GROUPING to further clean this up (I guess) if you can accept a different format..

SELECT
  CASE
    WHEN CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date THEN 'until_a_month_ago'
    WHEN CURRENT_DATE >= b.order_creation_date THEN 'until_now'
  END,
  COUNT(DISTINCT order_id) AS total_orders,
  SUM(invoice_amount) AS total_amount
FROM order_items a
INNER JOIN orders b
  USING (order_id)
WHERE b.seller_id IN (1, 3)
GROUP BY ( CASE
  WHEN CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date THEN 'until_a_month_ago'
  WHEN CURRENT_DATE >= b.order_creation_date THEN 'until_now'
END )


Strange error in trace file for NSAn standby process

At first glance, in MOS there's a related known bug with that message in 11.2.0.1 on Solaris (Bug 10331312)  and 11.2.0.2 on Linux x86-64 (Bug 14016842 , this one refilled for review) both referenced by Doc Id 1308804.1.

However it doesn't provides any source of cause. As workaround they recommend to disable the log_archive_trace parameter to = 0 (default), as those messages can be ignored.


Found: another MySQL config is /usr/my.cnf and it contains:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

so, setting sql_mode="" did the thing I need.

Debian stretch. Percona server was installed after removing installed MySQL-5.6 community.

Intrecting thing: log doesn't mention this config for reading during server's startup...


IP address to use with FCI, Always On AG and Always On (AG) FCI

For Failover Clustering: ... Am I right to assume that the clients should use the above cluster IP address to access SQL server?And that the Cluster IP address will decide where to direct the request to?

When dealing with a SQL Server FCI, you'll want to use the virtual name provided in the SQL Server setup as the point of connectivity. Do not use the "Cluster Name" as that points to the node owning the cluster resources and not necessarily the SQL Server resource group.

  Always on Availability groups: ... We configure the Listener and give it a DNS/IP address.

Correct, you use the listener.

  So what is the purpose of having the cluster DNS/IP address also?

This is the administrative endpoint for the cluster. You'd use this to deal with cluster settings, since you're just looking at SQL Server there are high chances you'll never actually use it. It doesn't mean it isn't used, just you aren't actively using it with SQL Server as a client.

  Combine the above (FCI + AG): ... Here also I assume that client apps should be connecting to the listener IP address and not the cluster IP address?

Correct.


Tempdb is recreated whenever the instance is restarted.  At startup tempdb is created at the size specified in the system catalog.  This means if you define tempdb to be 10GB, and it grows over time to 100GB, then you restart the server, tempdb will now be 10GB again.

The question about shrinking is one loaded with bad connotations.  For a discussion around that, refer to this question.


Do I understand this relationship correctly?

To your specific question:

If a ValueType can be applied to several Attributes, but each Attribute can have only one ValueType, then there is a one-to-many relationship between ValueType (the "one" table) and Attribute (the "many" table); one ValueType row can be attached to many Attribute rows. So, the relationship should go the opposite direction, and the Attribute table would include ValueTypeID, as a foreign key to ValueType.

On an unrelated note: AttributeValue would need to be a table in your diagram. Each Character has many Attributes, and and each Attribute may be associated with many Characters. AttributeValue would need CharacterID, AttributeID, and the actual AttributeValue.

One last note: If there are a fair number of attributes that won't apply to all characters, then this design makes sense. However, if almost all attributes would apply to all characters (in the RPGs I'm familiar with, attributes like strength, charisma, mana, hit points, etc. apply to all characters), then it would probably make more sense to have the attributes directly in the character table. If there are just a few attributes that don't apply to all characters, then it may make sense to include them in the character table, and set them to NULL.

Using the approach you've chosen can also wind up taking more disk space; for each attribute used, you've got an additional copy of the character ID and the attribute ID to be stored. Of course, this can go the other way: if there are 100 attributes, and most characters only use 6, then the space to store the NULL values in each of the other 94 columns could easily be more than the space to store 6 extra character and attribute IDs.

Finally, how you use the data affects things as well. If you frequently need to bring up just one attribute for a character and work with that, your queries may be faster and cheaper because you don't have to pull up the character row, just the specific AttributeValue row. If you need to work with several attributes at once, or need other data that's in the character table, then going with including the attributes in the character table might be better.


Grabbing data from a query with specific criteria, then the data around it

First a query without rank. You may get more than 11 rows in the result, if there people with same number of Kills as your player:

  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills > k.Kills
    ORDER BY a.Kills ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills = k.Kills
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills < k.Kills
    ORDER BY a.Kills DESC
      LIMIT 5
  )
ORDER BY Kills DESC ;

This will show no more than 11 rows, even if there hundreds of players with same number of Kills, by applying an order on the two columns: Kills, Name:

  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills, Name  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills = k.Kills AND a.Name > k.Name
      OR a.Kills > k.Kills
    ORDER BY a.Kills ASC, a.Name ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
    WHERE Name = 'VoidWhisperer'
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a
      JOIN
      ( SELECT Kills, Name  FROM AgStats
        WHERE Name = 'VoidWhisperer'
          LIMIT 1
      ) AS k
      ON a.Kills = k.Kills AND a.Name < k.Name
      OR a.Kills < k.Kills
    ORDER BY a.Kills DESC, a.Name DESC
      LIMIT 5
  )
ORDER BY Kills DESC, Name DESC ;

It will be good if you have a an index on (Kills, Name) and a (unique) index on (Name).

Tested at SQL-Fiddle


You can limit the number and size of SQL Server error log files to avoid excessive space. This can be done from SSMS Object Explorer by right-clicking on the SQL Server Logs folder and selecting Configure. Alternatively, use a T-SQL script like the below example that limits the number of error logs to 10 files of up to 100MB each for your default instance:
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'ErrorLogSizeInKb'
    , REG_DWORD
    , 100;
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'NumErrorLogs'
    , REG_DWORD
    , 10;

Examine the log to identify and remediate the root cause for the excessive size. The reason could be repeated login failures due to an attack, misconfigured application, repeated stack dumps, etc.
To view the current log, double-click on it from SSMS Object Explorer under Management-->SQL Server Logs or with the query below. In either case, you may need to stop the query due to the very large log size but the sampled records should be enough to identify the cause.
EXEC xp_readerrorlog 0, 1;


how to edit a sql server profile template using a text editor like notepad or ssms?

Set up the trace for one user , start it and then on SQL Profiler File Menu

File --> Export --> Script Trace Definition

Though it is now recommended to use extended events instead of traces.


Creating backups on Alwayson without differential backups

. I don't plan to create any differential backups for the AAG group. Can you please let me know if enabling this option should work.

Yes, it'll work.

  Is it true that for new databases, only full backup happens and not the log backup?

I don't understand the question. If it's specific to Ola's script then I do not know, if it's a SQL Server question then you must take at least one full backup and one log backup before adding the database to the AG, so the answer would be no it isn't true.

  What can I do to resolve the issue?

I currently do not see an issue. It's a valid backup strategy, now where or not it meets your recovery SLA in a disaster is another question.


Postgresql query plan differs with limit value making the query slower for lower limits

Generally this kind of problem occurs because the query planner thinks it will find the one row you're looking within the first 1% of the table, but it doesn't.  One solution for this can be to increase the stats collection on those columns, with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 5000.

However, in this particular case, I think simply changing the table_2_id index to a partial index will cause the query planner to favor it:

CREATE INDEX table_2_not_deleted ON table_1 ( table_2_id ) WHERE deleted_at IS NULL;

The query planner tends to favor partial indexes which match portions of the WHERE clause exactly.  If that doesn't work, I'd try creating an index which covers everything:

CREATE INDEX problem_solver_dex
ON table_1 ( table_2_id, id )
WHERE deleted_at IS NULL


Linked-server query against remote DB does NOT fail when DB is in SINGLE_USER mode

Upon re-trying and attempting to reproduce this problem, it seems I cannot.  I think Nic said it best so far: "being in single-user mode doesn't implicitly prevent connections".  At the time, the linked-server query must have been the first and only thing connecting to that DB.

Thanks also to Aaron Bertrand for suggesting the restricted_user mode for future cases.


sum of double precision gives weird results

This is a common problem with floating point numbers everywhere.

Floating point numbers stored in computer systems should only ever be considered approximations because there are numbers easy to represent in decimal that come out longer than the available precision (sometimes they are in fact never ending) when converted to binary. See ypercube's links and https://stackoverflow.com/questions/588004/is-floating-point-math-broken amongst many other references, for more detail.

The most common example given (as seen in that StackOverflow link) is 0.1+0.2 not resulting in exactly 0.3. You have to apply extra rounding or flexible bounds checking (in both cases reducing the effective precision) to get the behaviour you are expecting.

As your data has a fixed number of decimal places (or a fixed maximum) at 2, you would be much better of using the fixed place decimal/numeric types or similar. These are actually stored and processed as scaled integers avoiding the need for any floating point representation internally so avoiding the approximation problem (all integers can be represented accurately in both base 10 and base 2, assuming you have enough digits/bits). For instance 0.1 and 0.2 might be stored as 1000 and 2000, so the addition results in 3000 which when converted to a string for display becomes 0.3 not the 0.3000000004 you might get out of a floating point calculation, and of course compares accurately to 3 in the same type which would be scaled to 3000.

  Is there a way to select all records containing more than two numbers after the period?

You could convert to string and count the characters after the decimal point (or comma depending on your system local). In TSQL this would find values that are have ended up with more than two decimal places after being stored in a binary float format then translated back to decimal:

SELECT *
FROM   myTable
WHERE  CHARINDEX('.', REVERSE(CAST(floatValue AS NVARCHAR(MAX)))>3

(you may need to tweak function names and related syntax for postgres).


This was a weird question. I say was because I addressed this in the past

Jun 17, 2011 : What are your use-cases for HandlerSocket?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?

Basically, the HANDLER syntax is kind of handy if you want to read table data from table while bypassing locking and transactional isolation levels (Yes, this means you should be able to bypass InnoDB's MVCC protocols).


Synchronous data transfer from SQL Server to Oracle

One option with SQL Server is Heterogeneous Database Replication. This works with SQL Server as a source and destination being either of:

  The following non-SQL Server databases are supported as Subscribers to snapshot and transactional publications:

  Oracle for all platforms that Oracle supports.
  IBM DB2 for AS400, MVS, Unix, Linux, and Windows.

Snapshot Replication is best if:

  Using snapshot replication by itself is most appropriate when one or more of the following is true:

  Data changes infrequently.
  It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
  Replicating small volumes of data.
  A large volume of changes occurs over a short period of time.

Therefore it seems that Transactional Replication is your best option:

  Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

  You want incremental changes to be propagated to Subscribers as they occur.
  The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
  The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
  The Publisher has a very high volume of insert, update, and delete activity.
  The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

You could also look down the Linked Server road and update it with a trigger. I think it could be a lot easier to setup if you have only a single table to sync.

However I will only insert some links as I have never tried it and can't try it right now:

Microsoft How to set up and troubleshoot a linked server to an Oracle database in SQL Server
MSDN Creating a Linked Server for Oracle in 64bit SQL Server
MSDN How to get up and running with Oracle and Linked Servers
Technet Oracle Provider for OLE DB

Be careful with the 32/64-bit providers and choose the right one.


Mirrored local accounts to connect to database on different server and domain

That is not a sql server question but a windows question.
If you have multiple computers, login on one with a local account and try to connect to another computer it will try to use the username and check it against its own local account database. If the passwords match the challenge/respone authentication will succeed.
As far as I remember the users have to be local accounts - on both sides.


I had an extra space in my collation in the latin1.xml file. Removing that, fixed it!


InnoDB Architecture

Please keep in mind what goes into the InnoDB Buffer Pool

16KB Data Pages for Tables that have been accessed
16KB Index Pages for Indexes that have been accessed
Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment)

MySQL's idea of a JOIN
Believe it or not, whether you have one big table and process a WHERE clause or you have joins on multiples, the query processing has the same mechanical behavior. (I wrote about this a year ago : Is there an execution difference between a JOIN condition and a WHERE condition?) You can also find this described in the MySQL Documentation.
You said you fears JOINs. MySQL treats every query like a JOIN. The book I quoted in my post calls a SELECT on a single table a degenerate join. Multiple tables just uses a little extra resources (file handles, temp tables).
Effects on the InnoDB Buffer Pool
A single table with all needed fields would be loaded into the buffer pool and may produce, at most, 1 temp table for gathering data satisfying a where clause. On the flipside of things, having a single table would mean denormalized data and introducing redundant data into the buffer pool as well as the physical table storage.
Doing joins may actually result in creating multiple tables. This would also load data and associated indexes into the buffer pool. Any subsequent accessing of data might actually take place in the temp tables. Accessing the Buffer Pool would no longer be necessary.
Tuning
While a large buffer pool helps in the initial processing of a query, tuning the per-connection settings would be more of a help. The parameters I have in mind are

join_buffer_size
sort_buffer_size (if you are using ORDER BY and/or GROUP BY)

Any query that causes need more space than what there setting are may trigger a paging of that buffer to disk. There is where a slowdown of a query would become manifest and felt.
For the InnoDB Buffer Pool, your main objective should be to have the data and index pages present as often as possible. Avoid have a single denormalized table or you may bloat the buffer pool.
For the query, you may have to run smaller queries if you fear joins. Conversely, you could tune your queries and setup better indexes.
MySQL 5.6
If you are using MySQL 5.6, I would further suggest flushing the map of the InnoDB buffer pool to disk by running this
SET GLOBAL innodb_buffer_pool_dump_now = 1;

every minute.
Add this to my.cnf
[mysqld]
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1

and restart mysql.
Doing this will have all data and index pages availableon each restart of mysql. This gives every query you have the luxury of having data and indexes in RAM and up-to-date.


How to parse XHTML node value with TSQL?

You can extract the node text with XML methods as long as the value is well-formed XML.
If the XHTML value is a parameter or variable:
DECLARE @XHTML xml = N'My Node Value'
SELECT @XHTML.value('.','varchar(100)');

If the XHTML value is in a table column:
SELECT YourColumn.value('.','varchar(100)')
FROM dbo.YourTable;

The node paths can be adjusted if the SPAN element is not the root node.


SQL Agent PowerShell task never finishes

The problem you are experiencing is with the PowerShell subsystem in SQL Server Agent. It is a bit flakely with using other modules becuase you are put in the context of the SQL Server PowerShell Provider (SQLPS.exe). So it works the same way as if you opened up sqlps.exe and then try to execute your code.

One thing to keep in mind with dbatools module is that it will conflict with both SQLPS and the sqlserver module that MS now maintains separate for SQL Server. Last I checked the main thing that it conflicted against was TEPP that we have in the module now, it just can't load that code. [Caveat: I'm a major contributor to this module.]

The dbatools module has custom types and styles built in so when you run the scripts under PowerShell host that also has SQLPS or the SQLServer module imported your results will vary.

To utilize dbatools in a SQL Agent step make sure you only use the CmdExec subsystem (step type) and then call PowerShell host to execute your code. If you do not want to maintain a file for each script you can put your code in a SQL Agent CmdExec step in the manner illustrated below, but more complex script it is easier to maintain via files.

USE [msdb]
GO

/****** Object:  Job [dbatools_example]    Script Date: 2017-08-30 8:53:15 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2017-08-30 8:53:15 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'dbatools_example',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [dbatools_command]    Script Date: 2017-08-30 8:53:15 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbatools_command',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'powershell.exe -ExecutionPolicy Bypass -Command "Import-Module dbatools; $server = ''manatarms''; Get-DbaDatabaseSpace -SqlInstance $server -IncludeSystemDbs | Out-DbaDataTable | Write-DbaDataTable -SqlInstance $server -Database db1 -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable"',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Running the job above gives me this in my database and table db1.dbo.FreeSpaceOnDiskDrive


CDC on Sql Server 2017 with HA cluster stopped working

it seems that the problem is generated by the fact that the database is not primary.
In fact it is read-only state. Try to fail back the relative AG group.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15
About the cdc job you need to have it in every node that of the AG cluster.
Take a look at how to manage cdc in an alwayson environment:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-ver15


Can I check what the allocation unit size is on my drives via a query?

Maybe this is one way:

EXEC master..xp_cmdshell 'fsutil fsinfo ntfsinfo "C:"';

Line 9 shows, e.g.:

Bytes Per Cluster :               4096

There are obviously more powerful ways to do this using, e.g. PowerShell, but as I suggested in my comment, it's hard to understand exactly what you're going to do with the output in SSMS, and what kind of effort you're willing to go to in order to use these methods from within SSMS... I'd say it's much better to get these values externally (which gives you so many more options), then update a table you can query from within SSMS.


Count total unique rows for each date and group by date

SELECT COUNT(DISTINCT srctable.email), dates.date_added
FROM srctable
JOIN ( SELECT DISTINCT date_added
       FROM srctable ) dates ON srctable.date_added <= dates.date_added
GROUP BY dates.date_added


Insert with OUTPUT correlated to sub query table

I guess you could (ab)use MERGE for this. First create a (temporary) table:

CREATE TABLE tempIDs
( PersonId INT,
  FinancialInstitutionId INT
) ;

Then MERGE into Person (instead of INSERT), so you can use columns of the tables involved in the OUTPUT clause:

MERGE INTO Person
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (Street1, Number1, City1, ...)
  VALUES (fi.Street, fi.Number, fi.City, ...)
OUTPUT inserted.Id, fi.Id
  INTO tempIDs ;

Then use the temp table to UPDATE FinancialInstitution:

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

Test at: SQL-Fiddle


Copy data from multiple databases to a central database for reporting and analysis

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).
Table structures are the same in all databases.

REPLICATION : If the objects (tables) you are replicating are not undergoing frequent schema changes then you can use many-to-one replication using T-Rep (this is a very thorough answer that I wrote with scripts and end result), provided you can slightly modify your table schema. This will give you a near-to-realtime data transfer to your central server.
SSIS : If you dont need a near-to-realtime data transfer, then you can look into SSIS - Incremental load using Lookup transform as described in Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services. ALso, refer to this excellent series : Stairway to Integration Services for more details. Alternatively, you can test out  Change Data Capture (SSIS) (as @srutzky suggested).


How to run Mongo database db.currentOp(true) command using API

Try this instead:

db.command("currentOp({$all: true})");

I've tested the JS version of this (don't have Java set up to test atm) and it worked. If you are looking for an example of how to do this, you can see the actual methods (rather than helpers) in Java in this example, compare the default on line 43 with "all" on line 49


Expressing the largest floating point value in PostgreSQL?

There is no constant for that in PostgreSQL, but if your architecture uses IEEE floating precision values, the maximum should be 1.7976931348623158e308.

Maybe it would be a good idea to use a value that's larger than any reasonable value, but obviously artificial, like 1e300.


Update Table Query: Automatically handle "Enter Parameter Value" prompts?

You can't have 'missing fields' in a query without getting a prompt. Your June updates can not reference the phone column that does not exist and your July can not reference address. You need 2 different queries. If your business logic can't determine which query to run then you can use the Fields Collection of the TableDef object.


Mongo HDD disk grows to fast when insert / deleting data

Well. This depends on the below factors. Working Dataset, padding factor and fragmentation.

You will need to check which of the below things are not working for you.

1) Padding: As you said you implemented padding, can you let me know the value of padding factor of your mongo?

2) Fragmentation leaves some holes in your data files when they are moved around. MongoDB tries to fill holes whenever possible. Again it depends on your data files, if the new data files which are supposed to be accommodated in the existing holes are larger than the hole size, it can't be fit.
So, compact and usePowerOf2Sizes options come into our rescue.

Can you let me know the frag value of your collection?

db.(collection).stats().storageSize

It gives the sum of size of the collection + total index size

If you are on MMAPv1, you can switch to WiredTiger storage engine and test how fragmentation is working. You will need to upgrade to 3.0 to plugin to avail this storage engine.

If all the above options doesn't work, we are left with a tough option. Your application has to minimize fragmentation by pre-allocating the max size of a document and ensuring document size growth is managed correctly.