UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables

Your list of characters that must  be supported clearly indicates you need nothing more than plain ascii.

If you want to stored this as text, then this ascii is your most compact way. But here are a few clarifications:

VARCHAR(10) does not "need" 80 bits. It may need 80 bits, if all characters are used, under ascii character set. If you only store 3 characters (e.g. 'abc'), then it only needs 24 bits.
utf8 does not store more space than ascii when ascii characters are used. 'abc' on both utf8 and ascii encoding are 3 bytes long. That's why it's called utf-8: it attempts to only use 8 bits when possible.
However on temporary tables (vanilla MySQL; solved on Percona Server; I'm not sure about MariaDB) a utf8 character will take 3 bytes no matter what; same for MEMORY tables. So best use ascii if it fits your needs.
You could compress further. You can use the COMPRESS() function, for example, or encode via your own method (if you only need 64 different characters, this means you're using 6 bits. This means for every 3 bytes (24 bits) you use today, you could squeeze in another byte (using 2 buts from each of the 3 bytes). So you can certainly compress by 25%, and possibly more. But this leaves you with BINARY/VARBINAY types, which are not as easy to work with: you'll have to always compress/decompress, you will not be able to index the text (alphabetically, that is; you can certainly put indexes on the column).
The rest of the tools you mentioned are imho irrelevant; by the time your data reaches varnishd, your texts are uncompressed. Possibly so for PHP as well.


Can't restore SQL Server 2016 backup to SQL Server 2014

Install sql server 2016 developer edition, its free for dev purpose only. Restore the database on 2016 instance and then use this method to move your data back to older version.

Note: be mindful of enterprise features vs standard edition feature incompatiblity and limitations.


well i created a small program using java packages :

mysql-connector-java-5.1.18-bin.jar

and aoracle jdbc driver :

classes12.zip (this version for oracle 8i)

with mysql connector i can run a "select" statements an read from mysql databse, and then i build an "insert" statement to instert them in the oracle database.

this was the best solution i found


Tools and methodologies to keep to DBs aligned

keep all SQL DDL in "patch" files in source control.

add a "schema_version" table to your database and track the latest patch file you are on.

write a script that runs new patches against your dbs.


How to do incremental backup every hour in Postgres?

There is an existing tool that'll help you a great deal, WAL-E. It provides an archive_command and restore_command for PITR to S3.

There are no commands to do incremental or differential logical backups. pg_dump can't take an incremental or differential. The only way to do that is via log archiving.

In theory you could take a new full backup, do a binary diff between it and the last backup, and upload the diff. This strikes me as a fragile and inefficient way to do things, though, and I really wouldn't recommend it.

Additionally, PgBarman supports integration with S3 via hook scripts, and will automate much of the backup rotation and management for you. Again, this may not be an option on Windows.


From the manual:

  address

  Specifies the client machine addresses that this record matches. This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

  ...

  If a host name is specified (anything that is not an IP address or a special key word is processed as a potential host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match.

  ...

  A host name specification that starts with a dot (.) matches a suffix of the actual host name. So .example.com would match foo.example.com (but not just example.com).

In other words, use

  host all all .mydomain.com md5

or similar in pg_hba.conf, along with reverse DNS configured appropriately as above.


How do you monitor specific SQL statement of a specific user ?

You might want to look into AUDIT ALL STATEMENTS BY user. As per Oracle's documentation, audit all statements by ...  audits all top-level SQL statements executed. Top-level SQL statements are issued directly by a user. SQL statements run from within a PL/SQL procedure or function are not considered top-level statements. Therefore, this clause does not audit the statements executed within PL/SQL procedures or functions. However, the execution of the PL/SQL procedure or function itself is audited. This clause is useful if you want to audit all the statements in a specific environment, regardless of other auditing configurations that are system wide or user specific.


Postgres - Multiple commands on table at the same time

You can't run multiple DDL statements at the same time because each statement will request an exclusive lock. So if you add a primary or foreign key, the table is locked.

You can however create multiple indexes at the same time using the concurrently option.

Without the concurrently this will also block write access to the table while the index is created (read access is still possible in any case).


Is there a way to convert a custom format Pg dump file to a directory format?

No, that is not possible. But there is no advantage in a custom format dump over a directory format dump (except that the latter is not a single file), so you might just use the directory format dump. You'd gain parallel pg_dump in the bargain.


How Can I Monitor SQL service with Zabbix?

You can monitor status of any Windows service with a Windows-specific item key (when using Zabbix agent) service.info.

See Windows-specific agent key documentation for more detail.


Oracle Change Data Capture: Unsubscribed column results in change set record

I imagine you've enabled suplemental logging for the table on all columns, instead of just the ones that you require to be tracked.

eg:

ALTER TABLE philtest ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

vs:

ALTER TABLE philtest
ADD SUPPLEMENTAL LOG GROUP log_group_philtest
    (PHIL_COLA, PHIL_COLB) ALWAYS;


Does the fact a table is big can impact the overall performance of a PostgreSQL server?

No, this shouldn't affect the speed of queries for other tables.
One way it could indirectly affect the speed is, if you regularly run queries against that big table and retrieve many rows from there (either directly or indirectly e.g. because of a Seq Scan). This could then cause data from other (smaller) tables to be evicted from the cache.
Then a query using one of the other tables needs to retrieve the data again from the hard disk which of course is slower than from cache.


TimeZone problem with Oracle SQL Developer for Windows on MySQL server

I found the following solution on a forum (https://community.oracle.com/thread/4144569?start=0)

You have to just insert a string in the port value
PORT_NUM?SERVEROPTIONS&dummyparam= and leave the database field empty

if you need to specify the database name, you just have to use this other string (I suppose, not tested)
PORT_NUM/DB_NAME?SERVEROPTIONS&dummyparam=

Hope this is still relevant to you or maybe someone


Using Database Engine Tuning Advisor in production

Don't go to DTA.  It can have severe performance impacts in production and can really do more harm than good (I learned that lesson the hard way when I started out as a DBA).

The first step is to find out what exactly is causing your performance issues.  Is it a CPU issue? Memory? Improper indexes? Also, how do you know you are experiencing a performance issue?  Are users complaining of slowness? Are you seeing CPU spike high and stay there?  What about memory? Is Page Life Expectancy dropping often and you can't explain why?  These are the questions you need to ask.  You need to find out where it hurts first, then go from there.

So, to answer your question, no you shouldn't use DTA [in production, during business hours].

EDIT:

This answer may help you as well: Should I rely on Database Engine Tuning Advisor for creating indexes?

The important thing to realize is that just because DTA recommends something, there is still ALOT to be said for using your knowledge of the DBs and data in conjunction with it.  Same goes for the wonderful Missing Index Warnings.  Can DTA get you started? Absolutely, but there's much more to consider when making changes that it recommends and the performance impact of running it in production.

I, personally, have used it less than 5 times in my career.  I try to use DMVs/queries in conjunction with our 2 monitoring solutions to troubleshoot performance issues.


Does binary logging apply to ColumnStore?

Reading the official documentation at Overview of the Binary Log (MariaDB | Knowledge Base) the first paragraph states:

The binary log contains a record of all changes to the databases, both data and structure, as well as how long each statement took to execute. It consists of a set of binary log files and an index.

If you rummage through MariaDB's ColumnStore documentation you will eventually find the article labelled ColumnStore Storage Architecture which in the last paragraph Transaction Log mentions:

MariaDB ColumnStore supports logging committed transaction to the server's Binary Log.

Your Question

Is binary logging independent of the storage engine used?

Yes, following the documentation we have come full circle. Any changes to the data are logged; be it a normal storage engine or the new ColumnStore engine.


Why MySQL tends to use more and more swap while there is enough RAM?

Yes, you can make MySQL not use as much swap by making it use huge memory pages. Huge pages are unswappable.
In my.cnf, [mysqld] section:
large-pages = 1

Additional OS level configuration is required. For example:
groupadd -g 630 hugetlb
usermod -G hugetlb mysql
sysctl -w vm.nr_hugepages = 20480
sysctl -w vm.hugetlb_shm_group = 630

You may need to increase nr_hugepages a little, look at the logs while it is starting for clues on when it no longer needs to be increased.
Additionally, 10GB buffer pool chunk size is absurdly large. Comment it out. Also, optimal for buffer pool instances is between 1GB and 2GB per instance, so a setting of 40 would be more appropriate than 4 for a 40GB buffer pool.
And finally - if your PHP script can use upward of 40GB of RAM, I dare say you need to fix that. Unless you actually need to manipulate 40GB of data in memory at once, there is almost certainly a better way.


Can oracle use more memory than memory_max_target?

The output of free includes caches and buffers, and a lot of people fall into the trap of thinking that their memory is used while it is not. Visit this site for explanation: http://www.linuxatemyram.com/

On the other hand though, yes, Oracle can definitely use more memory than configured with the memory_max_target parameter. Parameters, such as memory_max_target, memory_max_size limit the size of the SGA only.
Prior to 12c, PGA could not be limited, even though there is the hidden parameter _pga_max_size. That parameter limits the PGA size per session, but it does not account for all kinds of allocations, thus even 1 "rouge" session can eat up all the memory of the database server.

Starting with 12c, there is the parameter pga_aggregate_limit, which is a real hard limit for the PGA.

References:

Using Automatic Memory Management (Oracle Docs 11g)
6.3 Using Automatic Memory Management (Oracle Docs 12c)
6.4.4 Using Automatic PGA Memory Management (Oracle Docs 12c)


Big size difference in ibdata file from live to staging

One possibility is that you have or had in the past innodb_file_per_table=0, and despite separating them at a later stage into separate files (changing it to 1+rebuilding the tables), the space, as you said, has not been reclaimed.

In most cases, ibdata1 growing too much is caused by extra temporary space needed in the UNDO area (which before 5.6, was always part of the ibdata1 file). The usual problem happens this way: A long transaction starts (a long running query, a non-closed transaction by mistake, etc.)- all subsequent edits cannot purge the old data because in repeatable read mode, a transaction after the first select goes into "snapshot mode". As a consequence, the UNDO space keeps growing and growing, and your queries start getting slower and slower. This is very typical in production hosts that have high write activity but also have to backup consistently the database or perform statistics-like queries.

There are other reasons why this could happen, but the long running transactions is the #1 cause (aside from former bad configuration). If the long running transactions are no longer active, know that data is available for new pages within the file, just not returned to the filesystem- so unless it happens again it may not grow again. As you said, either export+import is the only way to shrink it before MySQL 5.6.

You have more information on how and what ibdata1 is used for at: https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/ Many of the pains of tablespace handling were fixed on 5.6 (UNDO on an external file, importing/exporting .ibds, querying ibdata structure contents,...) so give at some point a chance to 5.6 testing, it may be worth in your case.


What to do when handed a server the first time?

I think sp_Blitz by good folks at Brent Ozar Unlimited is the best way to go with a new to me server.

https://www.brentozar.com/blitz/

https://www.brentozar.com/first-aid/sql-server-downloads/


database design for multiple and all pepople

Mail Master table

MailID (PK)
Mail content

Emp Master table

EmpID (PK)
EmpName

Mail-Employee Mapping table

Mail-Employee_ID (auto increament id PK)
MailID (FK)
EmployeeID (FK)

Group_Master table

GroupID (auto increament PK)
GroupName

Group_Employee_Mapping table

Group_Employee_ID (auto increament)
GroupID (FK)
EmployeeID (FK)
(GroupID+Employee are unique so Composite PK)

I think tables and their roles are self explanatory.

Some rule for Group Master:-

Group_Employee_Mapping is predefined in system :you have already mapped all employee and its group in this table.One employee can belong to many group .

If Group='All' don't store "All" employee in Group_Employee_Mapping Table.As this is unnecessary

If user select "All" select all employee from Employee Table ,send them mail and store each employee and mailid in "Mail-Employee Mapping" table

If user select any specific group then select those employee from Group_Employee_Mapping table,send them mail and store them in Mail-Employee Mapping


According to Paul Randal in his Pluralsight course "SQL Server: Logging, Recovery, and the Transaction Log" the LSN is composed of three parts:

1) VLF sequence number (4 bytes)
2) Log block number (4 bytes)
3) Log record number (2 bytes)


ODBC Timeout error with SQL server connection

I found a solution to the problem, but not sure why it works.  I added an inbound Windows Firewall rule on the server, for port 1433 and the users in question can connect.  Not sure why this affected a couple users and not 20 others, but at least it gets the spotlight off me!  Thanks for everyone's input.


Got it. See my code, there is an option when creating the publication

EXEC sp_addpublication @publication = N'db_name',
       ...
    @immediate_sync = N'true'
    ...
GO

That's the equivalent of checking on the publication creation wizard the checkbox Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

That's why when running it, tried to automatically create the snapshot. And as last snapshot is from couple of months, there is lot work to do in the middle. Ran with false value and now everything is ok, runnig smoothly and without issues.


Getting a proxy account to backup to a share using Ola Hallengrens backup script

To make this happen, you need to give the computer domain account on which you have SQL Server installed rights to write to the share [Folder] on [ComputerName].

Say you have a computer SRV-SQLSERVER, and SQL services you run with NT Service*, but in some situation you can't create a domain account for them.
But you need to store backups on other domain server \SRV-BACKUP.

To have this working, create an share on \\SRV-BACKUP\Folder and give write permission for SQL box domain account DOMAIN\SRV-SQLSERVER$.

Some related info can be found here:

How to grant network access to LocalSystem account?


How often does SQL Server refresh group policy?

SQL Server doesn't really interact directly with a domain controller, or even read group policies directly.   That is all handled by Windows.


How to insert values in junction table for many to many relationships (MySQL)?

Knowing a name, you can easily get the matching ID by filtering by the name. For the PROJECTS table, it would look like this:

SELECT
  project_id
FROM
  PROJECTS
WHERE
  name = @project_name
;

Same for UNDERGROUND:

SELECT
  underground_id
FROM
  UNDERGROUND
WHERE
  underground_name = @underground_name
;

Each query returns the result as a row set. The two row sets can be combined into a single row set if you cross-join them:

SELECT
  p.project_id,
  u.underground_id
FROM
  (
    SELECT
      project_id
    FROM
      PROJECTS
    WHERE
      name = @project_name
  ) AS p
CROSS JOIN
  (
    SELECT
      underground_id
    FROM
      UNDERGROUND
    WHERE
      underground_name = @underground_name
  ) AS u
;

And now you just need to insert the result returned by the above query into PROJECTS_UNDERGROUND:

INSERT INTO
  PROJECTS_UNDERGROUND (project_FK, underground_FK)
SELECT
  p.project_id,
  u.underground_id
FROM
  ... /* same query as previously */

It is also possible to rewrite the SELECT part like this:

SELECT
  p.project_id,
  u.underground_id
FROM
  PROJECTS AS p
CROSS JOIN
  UNDERGROUND AS u
WHERE
  name = @project_name
AND
  underground_name = @underground_name
;

Logically, this simpler looking query prescribes the server to first create a cartesian product of the two tables and then filter the result set by the condition in the WHERE clause. If that was indeed how the server would execute the query, it would probably not work very fast. However, it is likely that MySQL can figure out the possibility to push down the conditions in such a simple case, thus effectively changing the logical order of execution, and come up with an efficient execution plan. I invite you to test and compare both solutions.


Replicate MySQL INSERT to SQL Server table

This has been asked before and there are a couple of useful posts.  But the work is still being left in your hands.   You might check these posts:

richardtallent has a post: https://stackoverflow.com/questions/1455018/replication-from-mysql-to-ms-sql
AndrewSQL offered an outlined approach that got more votes at: Replicating a remote MySQL database to MS SQL Server 2008

The bottom line is that there are hints, techniques, and some tools that you might be able to marshal into an approach that suits you.  But the work is left in your hands.

In the same thread as AndrewSQL, there is a post from Gaius recommending GoldenGate for MySQL and MS SQL.  But I have no experience with it.


Error while restoring a Database from an SQL dump

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.

It doesn't sound like you're dealing with a mysqldump output file, there.  Try the file utility.

shell> file dumpfile.sql
dumpfile.sql: ASCII text

If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at all, or you're dealing with something that's been compressed (with gzip or bzip2, for example), which you'd need to uncompress before piping it into mysql.

If you see SQLite 3.x database then you definitely have your answer... it's a raw SQLite database, not a MySQL dump file.

Indeed, the first few bytes of a SQLite database are these:

53 51 4C 69 74 65 20 66  SQLite f
6F 72 6D 61 74 20 33 00  ormat 3^@

Note that the 16th octet here is 0x00, explaining the ERROR: ASCII '\0' appeared in the statement... message in this case.  The suggestion that --binary-mode is appropriate is a false alarm.

Windows users: the 'file' utility is a tool from Unix, but the Windows version can be found here.


restoring to new db putting old db into restore

Try this statement:

RESTORE DATABASE ETL WITH RECOVERY

Since this is your prod environment, be very careful in doing your due diligence to be sure you're doing no harm that cannot easily be fixed.

Also, check the Options tab to see if "Take a tail-log backup before restore" is checked. If it's checked, then un-check it. Starting in 2016 (I think), if you're restoring over an existing database, then SSMS defaults to taking a tail log backup. In my quick test just now (restoring to a new name from a backup of an existing database), SSMS still defaults to take a tail-log backup prior to restore. Get in the habit of always checking the Options tab.

Hope that helps.


Insert multiples rows/columns - PostgreSQL 9.2

If it's guaranteed (like you commented on your previous question) that each SELECT returns exactly 1 row, the simplest way to combine both would be to just nest one SELECT within the other:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id
    , (SELECT id
       FROM   public.ja_mobiusers
       WHERE  name_first LIKE 'Alisha%'
       AND    name_last LIKE 'Dson%') AS foo  -- alias irrelevant
FROM   junk.wm_260_billables2
WHERE  info ILIKE '%Alisha%';

If there can be 0 - n results per SELECT, it gets more complex. You would have to define what the result should look like for each possible combination.

Note that you get no row (nothing inserted) if the outer SELECT finds nothing, but a NULL value for the second column if the nested SELECT finds nothing.

Or you could just use two subqueries:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
VALUES (
  (SELECT billable_id
   FROM   junk.wm_260_billables2
   WHERE  info ILIKE '%Alisha%')
, (SELECT id
   FROM   public.ja_mobiusers
   WHERE  name_first LIKE 'Alisha%'
   AND    name_last LIKE 'Dson%')
   );

Now you get NULL for each column where nothing is found, and an error if more than one row is found.

I seriously doubt that an ILIKE expression can guarantee a single result row.

Aside: your constraint would be faster and cleaner this way - though more verbose:

ALTER TABLE dm.billables_links ADD CONSTRAINT cc_one_and_only_one_target
CHECK (customer_id IS NOT NULL AND role_id IS     NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS NOT NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS     NULL AND mobiuser_id IS NOT NULL)


I agree with Jithin R and ensuring the SQL Server "service" account (what you have set in the services.msc for the login as, and ensuring it has at least MODIFY or FULL permissions at the NTFS folder level where the MDF and LDF files are created when they code dynamically creates the DB).

It sure sounds like an NTFS permission issue with whatever security context is being used to created the LDF and MDF files does NOT have permission on the disk partition to create the DB files when initiated.

The security context will need at least LIST to traverse all folders and subfolders to the level of the folder where the DB files will be created, and where those files are created, it'll need MODIFY or FULL permissions there but MODIFY access should suffice.

I'm not sure why BitLocker being enabled or disabled on this disk would have an effect on this but perhaps BitLocker enabling in your configuration changes the NTFS permissions of the folders where the DB is being created.


Mysterious postgres process pegging CPU at 100%; no running queries

Most likely you've been hacked and are now mining cryptocurrency for the hacker.

Chances are, you have a weak password for your postgres superuser account (probably named "postgres") and you let anyone in the world connect and attempt to log in, which they do until they guess your weak password.  Or maybe you have no password at all.


Searching a text with space in mySql match against Query

FULLTEXT needs space to be space, not a "letter".  That is, you cannot have spaces in a "word".

I would write application code to ignore 'short' words (such as 'g') and end up with

WHERE MATCH(alternate_name, product_desc, keywords)
        AGAINST ('+moto +plus'; IN BOOLEAN MODE)
  AND ( alternate_name LIKE '%moto g plus%'
     OR product_desc   LIKE '%moto g plus%'
     OR keywords       LIKE '%moto g plus%' )

That would be efficient because the MATCH happens first, thereby limiting the number of rows significantly.  Then the costly OR and LIKE does not have to touch many rows.

(I left off the 'mobiles' for this discussion.)


Not a able to kill the DB session in Postgres RDS

I found the answer to this on stack overflow, basically you don't have rdsadmin privileges so you can only terminate sessions for your own username. https://stackoverflow.com/questions/22713375/permissions-for-pg-terminate-backend-on-amazon-rds


For PowerBI DirectQuery you'll need to write a single view that returns all the data.  Internally you might use a table-valued function if that is convenient.

If the execution of the queries against the view is too expensive, you'll have to cache the results, either by loading them into a table, or by switching from DirectQuery to Import mode.

David


What are the consequences of setting varchar(8000)?

Length is a constraint on the data (like CHECK, FK, NULL etc)
Performance when the row exceeds 8060 bytes
Can not have unique constraint or index (key column width must be < 900)
The default is SET ANSI PADDING ON = potential for lots of trailing spaces to be stored
SQL Server will assume average length is 4000 for sort operations, allocating memory based on this (need to find a link to back this up but rust me while I do :-)

Summary: don't do it.


This piece of SQL can be used as a template:

DECLARE @sql nvarchar(max);
DECLARE @query nvarchar(max);
DECLARE @params nvarchar(500);

SET @params = N'@SQL nvarchar(max), @db_name sysname';
SET @sql= N'SET @SQL=REPLACE(@SQL, ''db_name'', @db_name); print @sql;'
--SET @sql = N'SET @SQL=REPLACE(@SQL, ''db_name'', @db_name); EXEC sp_executesql @SQL;';
SET @query = N'
CREATE DATABASE [db_name]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N''db_name'', FILENAME = N''D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_name.mdf'' , SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N''db_name_log'', FILENAME = N''L:\MSSQL11.MSSQLSERVER\MSSQL\Log\@db_name_log.ldf'' , SIZE = 512KB , FILEGROWTH = 256KB )
GO
ALTER DATABASE @db_name SET COMPATIBILITY_LEVEL = 110
GO
'

@sql is the piece of SQL that will be executed once for each db
@params is the parameter used by @sql
@query is a string with the piece of SQL use to create a new DB

While playing with @query, be really careful with single and double quotes.

First SET @sql... only prints the query. The second one --SET @sql... will execute it if uncommented.

Then this template can be followed by one more more of the below lines and the whole batch can be executed:

-- template
--
...
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'xxx';
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'yyy';
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'zzz';
...

If db_names are already in a table, this query will output all the EXEC lines:

SELECT N'EXEC sp_executesql @sql, @params, @query = @query, @db_name = '''+col_db_name+''';;'
FROM db_list;

You can then copy some or all of them after the template and execute the whole batch.

Or without a table, this can simply be used:

SELECT N'EXEC sp_executesql @sql, @params, @query = @query, @db_name = '''+col_db_name+''';'
FROM (values
    ('xxx')
    , ('yyy')
    , ('zzz')
    , (...)
    , ...
) as dblist(col_db_name);


Select top-most folder in a hierarchy by given criteria in MySQL

I think you want this. All the results from users table except those that have a parent in users:

SELECT u.user, u.child
FROM users AS u
WHERE NOT EXISTS
      ( SELECT 1
        FROM users AS u2
             JOIN folder AS f
             ON f.parent = u2.child
        WHERE f.child = u.child
          AND f.dist > 0         -- this could be: AND f.parent <> f.child
          AND u2.user = u.user
      ) ;


SSMS - Query variables from Table Design Mode > Column Description

I'd suggest to create either an actual table with the status and id itself and create a FK to the SalesOrderHeader.Status table and my code below will convince you :)

So below would be a formatted implementation of what you'll probably need, as you can see it's a long winding road just to join a column.You may modify below and put it inside a udf if you don't want to go to an actual table.

;WITH src
AS (
    --#1 get extended info description meta attribute
    SELECT sys.objects.name                AS TableName,
           sys.columns.name                AS ColumnName,
           ep.name                         AS PropertyName,
           CAST(ep.value AS NVARCHAR(255)) AS Description
    FROM sys.objects
        INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
        CROSS APPLY fn_listextendedproperty(
                                               DEFAULT,
                                               'SCHEMA',
                                               SCHEMA_NAME(schema_id),
                                               'TABLE',
                                               sys.objects.name,
                                               'COLUMN',
                                               sys.columns.name
                                           ) ep
    WHERE sys.objects.name = 'SalesOrderHeader' AND
          sys.columns.name = 'Status'
),

src2 AS (
   --#2 Retain only status description to be extracted later
   SELECT SUBSTRING(src.Description, CHARINDEX('.', src.Description) + 1, 255) descd
   FROM src
 )
   --#3 extract id and description
SELECT CAST(SUBSTRING(x.StatusDesc, 1, CHARINDEX('=', x.StatusDesc) - 1) AS TINYINT) AS StatusId,
       LTRIM(RTRIM(SUBSTRING(x.StatusDesc, CHARINDEX('=', x.StatusDesc) + 1, 255)))  AS StatusDesc
FROM
(
    SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS StatusDesc
    FROM
    (
        --#3.1 generate rows from the delimited data of status description
        SELECT CAST('<XMLRoot><RowData>' + REPLACE(src2.descd, ';', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM src2
    )                                           t
        CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
) x;


It depends on whether the relationship between property and address is 1:1.

Put another way: it depends whether two properties may reside at one address, or one property may have two addresses.

I can imagine, for example, a large worksite with multiple buildings. The address for the worksite might be 50 Smith St (one address). And I can imagine your property table having two rows linking to that address: one where property.buildingName = 'Workshed' and another where property.buildingName = 'Reception' (although I'm not sure you would rent a workshed separately to a reception, and if you did, they would probably be given separate addresses anyway).

If the above modeling of two properties at one address is possible with your business data, then I advise against merging your two tables.

On the other hand, if there is only ever 1 property per address, I think there is no real issue with merging your tables.

Other considerations:

If your database was managing millions of rental properties, there may be performance differences between the two designs. This would be an avenue to explore further.
If your application currently references two separate tables, there will be work converting your application to reference just the single table. This work might be trivial (if, say, currently, all references query a view and you only have to update your view) or monumental (if, say, you have 200 reports querying these tables and your data is synchronised with 5 other systems whereby you have to update your sync process and perform regression testing across all of those systems, even if they all belong to business clients). The prospective cost of the work to merge tables should also be taken into account.
If this is simply an exercise - for example, for a course on SQL - then either design might be fine, so long as you clarify the limitations of each (eg. only one property per address).

Here is a tutorial that introduces you to relational database normalisation - which is basically the process you need to go through in order to make your decision.


how to add a user to local sql server when one has only windows admin rights on the server

It's pretty easy to do if you are a member of the local Administrators group.
The below steps are taken from this answer on StackOverflow:
Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:
net stop mssqlserver

If you're running a named instance of SQL Server, the Service Name will be something similar to mssql$instancename.
Open SQL Server Configuration Manager from the Windows Start Menu.  Find the instance listed in the "SQL Server Services" window, and right-click it, then click "Properties".  Go to the "Service Tab", and make note of the "Binary Path" and CD into it as such:
CD "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn"

Run the following command to start SQL Server in single user mode.  As
SQLCMD is being specified, only one SQLCMD connection can be made (from another command prompt window).
sqlservr -m"SQLCMD"

Open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:
sqlcmd

And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode.  To add a login for your account, run:
CREATE LOGIN [DOMAIN\USERNAME] FROM WINDOWS;

Replace DOMAIN and USERNAME with appropriate values.
-- For older versions of SQL Server:
EXEC sys.sp_addsrvrolemember @loginame = N'DOMAIN\USERNAME'
     , @rolename = N'sysadmin';

-- For newer versions of SQL Server:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\USERNAME];
GO

Source.


You need a join or a correlated subquery (with `EXISTS), in order to compare rows:

SELECT
    SUM(c.available_supply * h.price) AS market_cap_usd_all
FROM coins AS c
    JOIN hist_all AS h
    ON c.id = h.coins_id
WHERE h.price <> 0
  AND ( h.second_coin = 'USD'
     OR
        h.second_coin = 'USDT'
        AND NOT EXISTS
            ( SELECT 1
              FROM hist_all AS hi
              WHERE hi.first_coin = h.first_coin
                AND hi.second_coin = 'USD'
            )
      ) ;


function checking type of input and reacting

The result of pg_typeof() is an oid, if you want to compare that with a string value, you need to cast it, e.g. if pg_typeof($1)::text = 'jsonb' then...

But once you checked if the input is a JSON you also need to verify if it's a JSON array, because {"foo": "bar"} will also be reported as json(b).

The way you iterate over the array is also quite inefficient as you are running the same select statement over and over again. You can check this with a single statement.

There is no ELIF in PL/pgSQL. As documented in the manual it needs to be ELSIF

So the function would look something like this:

CREATE OR REPLACE FUNCTION check_is_animal(anyelement)
  RETURNS boolean
AS $$
DECLARE
   arr text[];
   l_result boolean;
BEGIN
  -- check if the argument is a text array
  -- this could be expanded to check for character varying[] as well
  IF pg_typeof($1)::text = 'text[]' THEN
    arr := $1;
  ELSEIF pg_typeof($1)::text = 'jsonb' THEN
    IF jsonb_typeof($1) = 'array' THEN
      -- check if the array contains strings or objects:
      IF jsonb_typeof($1 -> 0) = 'object' THEN
        -- assuming the key is always name:
        arr := array(select jsonb_array_elements($1) ->> 'name');
      ELSE
        arr := array(select jsonb_array_elements_text($1::jsonb));
      END IF;
    ELSE
      return false;
    END IF;
  ELSEIF pg_typeof($1)::text = 'json' THEN
    IF json_typeof($1) = 'array' THEN
      arr := array(select jsonb_array_elements_text($1::jsonb));
    ELSE
      return false;
    END IF;
  ELSE
    -- no array whatsoever
    return false;
  END IF;

  select count(*) = 0
    into l_result
  from unnest(arr) as x(name)
  where not exists (select *
                    from animals a
                    where x.name = a.animal);

  return l_result;
END;
$$ LANGUAGE plpgsql;

If the JSON array contains unknown key names and you can't use the hardcoded name, you can use this instead:

    arr := array(select x.v
                 from jsonb_array_elements($1) as t(e)
                 cross join jsonb_each_Text(t.e) as x(k,v));

The final select statement checks if all elements in the array exist in the table. By using a NOT EXISTS condition, the statement can stop as soon as one non-existing animal is found.

Online example


What do the mysqlds do in my mysql database?

I see 18 mysqld's there.  I'll bet SHOW VARIABLES LIKE 'thread_cache_size'; gives you at least 18.  And, I'll bet that, at some point since startup, you had 18 clients running.

So why doesn't PROCESSLIST show 18 processes/threads?  Because wait_timeout killed off 17 of them.

Bottom line:  Nothing to worry about.


Moving data from one database to another database with auto incriment keys

Just follow this... steps

These are the tables for database1

    create table if not exists table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

There are the tables for  database2

    create table if not exists 2table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists 2table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists 2table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

Here we have temp data for database1

    insert into database1.table1 (data1)
        values('table_1_data_1'),
        ('table_1_data_2'),
        ('table_1_data_3');

    insert into database1.table2 (data2)
        values('table_2_data_1'),
        ('table_2_data_2'),
        ('table_2_data_3');

    insert into database1.table3 (data3,t1_id,t2_id)
        values('table_3_data_1',1,1),
        ('table_3_data_2',1,2),
        ('table_3_data_3',2,1),
        ('table_3_data_4',2,2),
        ('table_3_data_5',2,3);

temp data for database2

    insert into database2.2table1 (data1)
        values('2table_1_data_1'),
        ('2table_1_data_2'),
        ('2table_1_data_3');

    insert into database2.2table2 (data2)
        values('2table_2_data_1'),
        ('2table_2_data_2'),
        ('2table_2_data_3');

Now come to the point

Here we are copying data
from database1s table1 to database2s 2table1

and

from database1s table2 to database2s 2table2

    insert into database2.2table1 (data1)
        select data1 from database1.table1;
    insert into database2.2table2 (data2)
        select data2 from database1.table2;

Now we are going to insert data into database2s 2table3

    insert into database2.2table3 (t1_id,t2_id,data3) select
      (select 2t1.id from database2.2table1 2t1 where 2t1.data1 =t1.data1 ),
      (select 2t2.id from database2.2table2 2t2 where 2t2.data2 =t2.data2 ),
      t3.data3
    from database1.table3 t3
    left join database1.table1 as t1 on t1.id = t3.t1_id
    left join database1.table2 as t2 on t2.id = t3.t2_id;

I hope it helps...


Does the target database need to have the same structure of the source database before to use IMPDP?

The new database can have a different directory structure and even storage system, but you'd need some preparation before running the import. Especially, you'd need to create the tablespaces in advance with the correct (new) storage parameters (and of course the required size). Then impdp would not have to create them (an error might still be shown, but import should proceed).
Make sure to give the tablespaces the same names (or use the corresponding REMAP parameters on import). No need to apply quotas etc, that the import should take care of then (when creating the users).


With foreign keys automagically dropping out, can I set up an alert?

Foreign keys kept dropping out by themselves.

Unless there is a major bug (and that would be talked about a lot online) in the version you are running, then this should not happen. Something is asking for the keys to be dropped rather than the engine doing it automatically.

  Setting up an alert that will send an email when a foreign key is dropped.

You could use a DDL trigger (search the documentation for "DDL Trigger", on online more generally for examples) to log the exact time that a DROP statement takes action. I don't know if sending mail is possible/practical in Orcale but you can definitely log the events to a table that you can refer to later.

The trigger may give you access to the user account that is used, though this may not be useful if it is being caused by an application user rather than a more specific login (i.e. a human admin). Knowing the exact time will allow you to more easily work out what process (an errant index maintenance script, someone deploying updates that are incorrect and reset the table's keys to an older incorrect setup, and so on) is asking for the keys to be dropped.


SQL Server MDF still has old values after update

This is probably one of the silliest processes I've ever heard of, but anyway.

After you've updated your data to remove the offensive words, run a checkpoint, and then restarted SQL Server (just to make sure it's all committed), the only way to guarantee that the underlying disk has been overwritten, and the "offensive" data expunged, is to perform a shrink of the database file.

This effectively takes the data pages from the end of the file and inserts them into open spaces at the beginning of the file to fill up "empty" space. It's an horrific process for SQL Server, as it causes massive fragmentation, but it will "solve" your problem.

Once you've moved your data to the new server, you must run a full index rebuild to defragment the tables, but, hey, it should work.


Changing a certifcate+key algorithm by dropping & recreating with the same names

Just to feed back on this, the process worked fine and the data was re-encrypted successfully.

Therefore despite some concerns raised over symmetrical data encryption, (of which there are business rules to justify this), all worked well.

All stored procedures that reference the key continued to work fine without any modification being required.


Why is the Read-Only Routing not functioning in this Availability Group?

The human element was the problem, as usual. Turns out I had accidentally used the primary replica's server name in the Read-Only Routing URLs for all three replicas. There being only one character difference between each name made it difficult to spot, even though I'd looked at them dozens of times.

Thanks to @SeanGallardy for prompting me to pull the URLs, which caused me to notice the problem.

Thanks to @clifton_h for also making a good-faith effort to help.  Y'all good people!


Can't create triggers in MySQL, no errors but no triggers either

You have to use delimiter declared earlier to complete the statement:

DELIMITER // -- turn delimiter into "//" instead of ";"
CREATE TRIGGER `ZZZ`.`name` BEFORE INSERT ON `transactions`
FOR EACH ROW
BEGIN
   -- the code that will be stored for the future use,
   -- not the statements that will be executed right now
   SELECT something FROM ... ; -- inactive delimiter

END// -- redefined delimiter
DELIMITER ; -- again

Trigger declaration is the statement that contains the code. To tell the code from the statement the different delimiters are used. Redefined // for the containing statement and the default ; for the contained code.

That's how your queries should look like:

USE `wa3`;

CREATE TABLE `transactions` (
    `id` CHAR(36) NOT NULL,
    `account_number` INT(11) NOT NULL,
    `transactions` INT(11) NOT NULL,
    `debit` DECIMAL(16,2) NULL DEFAULT NULL,
    `credit` DECIMAL(16,2) NULL DEFAULT NULL,
    `balance` DECIMAL(16,2) NOT NULL,
    `created` DATETIME NULL DEFAULT NULL,
    `modified` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

DELIMITER //

CREATE TRIGGER `wa3`.`InsertCreditOrDebitNotNull` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL)
    THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END// -- first trigger

CREATE TRIGGER `wa3`.`UpdateCreditOrDebitNotNull` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL)
    THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END// -- second trigger

CREATE TRIGGER `wa3`.`InsertBothCreditAndDebit` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL)
    THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END// -- third trigger

CREATE TRIGGER `wa3`.`UpdateBothCreditAndDebit` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL)
    THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END// -- fourth trigger

DELIMITER ;

SHOW TRIGGERS;


How do uninitialized variables differ from NULL in Mysql?

There are two ways to set a variable from a query, and they behave differently, especially when no record is matched.

You can SELECT ... INTO the variable...

select first_name into @z from actor where actor_id = 999999;

Or you can SET it to the value returned by a scalar subquery.

set @z = (select first_name from actor where actor_id = 999999);

Illustrating the difference with the sample database...

mysql> use sakila;
Database changed

Our variable starts out NULL.

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT first_name INTO @z FROM actor WHERE actor_id = 1;
Query OK, 1 row affected (0.00 sec)

We matched a row and our variable is set.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

We try to select a record that doesn't exist:

mysql> select first_name into @z from actor where actor_id = 999999;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

So what's in our variable now?

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

I think this explains the confusion.  When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.

On the other hand, if we use the other construct, the variable is reset when no record is matched.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

mysql> set @z = (select first_name from actor where actor_id = 999999);
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>


What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts?

The purpose of the data warehouse is to serve as a single source of truth1 for your BI consumers. It provides you with the central place where the data and metadata for your reports and dashboards originate. If you deprive it of  this role and create a multitude of data marts populated by your operational data (via staging tables or not), inevitably each of these data marts will contain its own "source of truth", not necessarily correlating with what other data marts think is the truth, thus diminishing the value of business intelligence derived from these multiple truths.
Building a data warehouse is indeed a notoriously long and failure-prone process, but developing ETL procedures is not nearly the most time-consuming part of it; it is master data management and modelling that takes the most time and effort.
Also, the size of your organization is barely relevant; it's the value that you expect to derive from your BI and, indirectly, from the data warehouse that feeds it that should drive your decision to take shortcuts.

1 - Some say it's a single version of truth, because ultimately the source of those data is elsewhere.


Does freeing up disk space improve the performance of MySQL?

Can a lack of free disk space impact MySQL performance?

Certainly a lack of disk space at all can prevent writes from happening. Especially when storage is beginning to become full (>90%, as a minimal ballpark) and the disks are fragmented, it can take some time to search where writes can even occur when MySQL is trying to write to disk.

This would be true of any database platform or any application that writes to disk. This isn't so much a MySQL behavior as it is a storage behavior.

My suspicion is that you have different qualities of storage disks available to you filesystem(s) that contain the MySQL data. As your server became more full, you would be more frequently subject to the performance of your worst disks in terms of IO.

After purging your server of old binlogs you've likely been able to increase your storage-tier's performance with IO because it takes a shorter amount of time to find places to write. Shorter time to find places to write means faster transaction performance. Faster transaction performance means less timeouts and noticeable delays in application functionality.


SELECT Dynamic columns with values from other table

what is the logic behind above database design ?

Is TaskfieldName dynamic for particular taskid ?

In Sql Server,

    create table #t (TaskId int,TaskName varchar(50)
 ,TaskAuthor varchar(50) )
insert into #t VALUES(1,'Testing Task','Me')

create table #t1 (TaskFieldId int,TaskId int
 ,TaskFieldName varchar(50), TaskFieldValue varchar(50))
 insert into #t1 VALUES
(1 ,1,'Created_at'   ,'2017-31-05')
,(2 ,1,'TaskLocation' ,'Berlin')
,(3 ,2,'TaskLocation' ,'Berlin')
declare @TaskID varchar(10) =1
declare @Taskfield varchar(200)
declare @Sql varchar(max)=''
select top 1
@Taskfield=stuff((select ','+'['+TaskFieldName +']'
from #t1 t11 where taskid=t1.taskid for xml path('') ),1,1,'')
from #t1 t1
where t1.TaskId=@TaskID
--select @Taskfield
set @Sql=
'SELECT *
FROM (
    SELECT t.*
        ,t1.TaskFieldName
        ,t1.TaskFieldValue
    FROM #t t
    INNER JOIN #t1 t1 ON t.TaskId = t1.TaskId
    where t.taskid='+@TaskID+'
    ) AS src
pivot(max(TaskFieldValue) FOR TaskFieldName IN (
            '+@Taskfield+'
            )) pv;'

--print @Sql
EXEC (@Sql)
drop table #t
drop table #t1


Steps to reproduce:

create table house_t
(
POSTALCODE                  VARCHAR2(6),
IFNSFL                      VARCHAR2(4),
TERRIFNSFL                  VARCHAR2(4),
IFNSUL                      VARCHAR2(4),
TERRIFNSUL                  VARCHAR2(4),
OKATO                       VARCHAR2(11),
OKTMO                       VARCHAR2(11),
UPDATEDATE                  DATE,
HOUSENUM                    VARCHAR2(20),
ESTSTATUS                   NUMBER(5),
BUILDNUM                    VARCHAR2(10),
STRUCNUM                    VARCHAR2(10),
STRSTATUS                   NUMBER(5),
HOUSEID                     VARCHAR2(36),
HOUSEGUID                   VARCHAR2(36),
AOGUID                      VARCHAR2(36),
STARTDATE                   DATE,
ENDDATE                     DATE,
STATSTATUS                  NUMBER(5),
NORMDOC                     VARCHAR2(36),
COUNTER                     NUMBER(10),
CADNUM                      VARCHAR2(100),
DIVTYPE                     NUMBER(5)
);

Sample data:

insert into house_t (HOUSEID, HOUSEGUID, AOGUID)
with g as (select * from dual connect by level <= 1000)
select sys_guid(), sys_guid(), sys_guid() from g,g,g
where rownum <= 50000000;
commit;

SQL> select count(*) from house_t;

  COUNT(*)
----------
  50000000

CREATE UNIQUE INDEX HOUSE_T_IDX0 ON HOUSE_T(HOUSEID);
CREATE INDEX  HOUSE_T_IDX1 ON HOUSE_T (HOUSEGUID);
CREATE INDEX HOUSE_T_IDX2 ON HOUSE_T (AOGUID);

SQL> select segment_name, bytes/1024/1024 from user_segments;

SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
HOUSE_T                                   6336
HOUSE_T_IDX0                              2368
HOUSE_T_IDX1                              2432
HOUSE_T_IDX2                              2432

Now to simulate your script:

SQL> alter session set tracefile_identifier=BALAZS1;
SQL> exec dbms_session.session_trace_enable(true, false);

declare
  TYPE T_HOUSE_T_ARRAY IS TABLE OF VARCHAR2(36);
  L_DATA T_HOUSE_T_ARRAY;
  CURSOR c IS SELECT sys_guid() from dual
    connect by level <= 50000 order by dbms_random.value;
BEGIN
  for i in 1..70
  loop
    begin
      OPEN c;
      LOOP
      FETCH c BULK COLLECT INTO L_DATA;
      FORALL i IN 1..L_DATA.COUNT
      INSERT INTO house_t (HOUSEID, HOUSEGUID, AOGUID)
        VALUES (L_DATA(i), L_DATA(i), L_DATA(i));
      EXIT WHEN c%NOTFOUND;
      END LOOP;
      CLOSE c;
  commit;
end;
  end loop;
END;
 21  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:58.97
SQL> exec dbms_session.session_trace_disable;

And the tkprof output:

SQL ID: 4h4byjv3yd590 Plan Hash: 0

INSERT INTO HOUSE_T (HOUSEID, HOUSEGUID, AOGUID)
VALUES
 (:B1 , :B2 , :B3 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     70     82.17     105.82      23693     359178   43342331     3500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71     82.17     105.82      23693     359178   43342331     3500000

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     24243        0.01         11.82
  ...

And an enormous amount of leaf node splits:

select sn.name, ss.value from v$statname sn join v$sesstat ss
  on (sn.statistic# = ss.statistic#)
where sid = sys_context('userenv', 'sid')
  and name in ('leaf node splits', 'leaf node 90-10 splits',
    'branch node splits', 'root node splits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                      82981
leaf node 90-10 splits                                                   18
branch node splits                                                      363
root node splits                                                          0

1) I am using an SSD here, that is why my demo took much less time, because of the much lower disk response time. You should look at the amount of current blocks, not the time spent on this.

2) I am inserting data in randomized order, this causes leaf block splits. leaf node 90-10 splits are caused by inserting data to the rightmost part of the index, in a sequentially increasing order. leaf node 90-10 splits can be simply done by adding new index blocks to the "rightmost" part of the index, then copy the highest value into them. Sometimes it is unofficially referred as a 99-1 split instead of 90-10. leaf block splits means adding new blocks in the "middle" of the index, copying and rearranging data in several blocks, this is much more work.

3) Performance-wise I find it very a bad idea to use GUIDs as identifiers. They consume multiple times more storage space compared to simple NUMBER identifiers.

This is the same, but with NUMBER types:

EGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
HOUSE_T2                                  2432
HOUSE_T_IDX02                             1088
HOUSE_T_IDX12                             1088
HOUSE_T_IDX22                             1088

Also: Limitations of the Oracle Cost Based Optimizer (Doc ID 212809.1)

  Limitations of Histogram on Character Columns

  Histograms only store the first 32 characters of a character string (5
  characters pre 8.1.6 See Bug:598799 ). If histograms are gathered on
  character columns that have data that is longer than 31 characters and
  the first 31 characters are identical then column histograms may not
  reflect the cardinality of these columns correctly as these values
  will all be treated as if they are identical. There are also similar
  limits with numeric data which is normalised to 15 digits in histogram
  endpoints.

  As of 12c, the maximum possible number of characters considered for a
  histogram on a string column increases from thirty-two to sixty-four.

This has caused some seriously incorrect cardinality misestimates with histograms on GUID columns.

This is how this behaves when values are inserted in an increasing order (removed order by dbms_random.value):

INSERT INTO HOUSE_T (HOUSEID, HOUSEGUID, AOGUID)
VALUES
 (:B1 , :B2 , :B3 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     70     21.13      23.39       3266     167674    2496874     3500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71     21.13      23.39       3266     167674    2496874     3500000

As you can see, it is less than 1 current block per row.


LIKE Operator not returning desired result after using LOWER

Selecting a column as LOWER(NAME) doesn't automatically apply to your WHERE clause...  You need to have LOWER in WHERE clause, minimally.

SELECT
  COMPANY_ID, NAME, EMP_ID
FROM employee
WHERE emp_id = 4
  AND LOWER(NAME) LIKE '%steven%'

In actuality, they are two completely separate pieces of instructions to the query planner..  The WHERE clause instructs the planner on which records to consider, and the SELECT clause instructs the planner for how to present the data to you.

The following query is equally valid

SELECT
  COMPANY_ID, UPPER(NAME) as NAME, EMP_ID
FROM employee
WHERE emp_id = 4
  AND LOWER(NAME) LIKE '%steven%'


Why is a connection via an SSH tunnel presenting as 127.0.0.1 and not localhost?

All connections that were being made on the server to "localhost" were indeed using the Unix Domain Socket and authenticating with the test@localhost user.

I confirmed this with some tests:

mysql -utest -hlocalhost --protocol=SOCKET -P3306 -p  # worked (-P also redundant)
mysql -utest -hlocalhost --protocol=TCP -P3306 -p     # failed
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

The connection via the SSH tunnel was using TCP and as skip-name-resolve was ON was trying to login as test@127.0.0.1 rather than test@localhost.

Since my applications are running on the same server as the database and configured to connect to localhost/UDS, I see no benefit of turning skip-name-resolve ON as this just causes an additional problem when attempting to connect via the SSH tunnel to "localhost".


Why do we use Group by 1 and Group by 1,2,3 in SQL query?

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms.

The reasons people do it:

they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code.

The reasons it's bad:

it's not self-documenting - someone is going to have to go parse the SELECT list to figure out the grouping. It would actually be a little more clear in SQL Server, which doesn't support cowboy who-knows-what-will-happen grouping like MySQL does.
it's brittle - someone comes in and changes the SELECT list because the business users wanted a different report output, and now your output is a mess. If you had used column names in the GROUP BY, order in the SELECT list would be irrelevant.

SQL Server supports ORDER BY [ordinal]; here are some parallel arguments against its use:

https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal


Why does query parameter type affects mysql index choice?

Query processing takes the given SQL, applies a set of rules and heuristics and comes up with an execution plan. This states which indexes are used and how. The outcome is dependent on the input and how the rules have been written.

Given a slightly different start point a different end point may result. It's somewhat like dropping a ball in a landscape of river vallies. Drop it once and it will roll into a river. Drop it in a slightly different place and chances are it will roll down into the same river. Occasionally, if you're near a ridge, you drop the ball in a slightly different place and it rolls into the next river instead.


Can the number of tables and functions affect performance?

It's a gradual process and the effect is comparatively small. But of course, looking up entries in system tables gets slower with lots of rows. Those are just regular tables. Highly optimized, but regular tables. There are indexes to keep the effect small.

I have never actually noticed an effect in my biggest database with a couple of hundred tables and close to 1000 functions. Sequences, views, triggers .. everything. Your DB seems to have similar numbers - I'd guess you cannot report any observations yourself? Or you would surely have added that in your question.

The cost for other things like backup and VACUUM rises, too, obviously.


How the statement based replication works internal on secondary node of mongodb?

Mongodb do support only row based replication for the data durability among the master and slaves, when you hit a update command like below

db.collname.update({"empid":43123},{$set:{"age":{$inc:1}}})

the above statement search for the employee with employee id empid 43123 and increaments his age to 1, if his age is 26 it will make it as 27, this operation happens only in master, if you go and check the oplog for this statement the oplog entry will be like below

db.collname.update({"empid":43123},{$set:{"age":27}})

so mongodb replication always work on the basis of row which is updated.


I think this is better choice when student can choose multiple Major at a time.

STUDENT (StuID, StuName)
MAJOR (StuID, Major)

You Second Try will be a better choice when student can choose only one Major at a time

STUDENT (StuID, StuName, MajorID)
MAJOR (MajorID, Major)

Now one more step, I can think of here is, as Major  value is some fixed set of variable(limited in number), This will gives the option to add new Major and student can choose more than one Major at a time.

Student (StuID, StuName)
Major (StuID, MajorID)
MajorLookup (MajorID, Major)


One to Many Relationship: Describing the Tables

The typical nomenclature for this type of table design would be "class-subclass".  In your example of Books and Authors, Books is a subclass of the Author class.  Any book automatically has an author, since no book can ever be written without having an author write it.  Same goes for Sales and SalesItems; each SalesItem must have an associated Sales object.

However, "parent-child" may be employed where it makes sense.  As is so often typical, the answer is It Depends™.

Using nomenclature that makes sense for the specific domain in question, and is easily and readily understood by the stakeholders, ensures future comprehension.

One-to-many is a typical term used to describe the relationship between rows in each table.  The relationship might also be one-to-one or many-to-one or even many-to-many, depending on domain requirements.


Is there a way to track page splits on an InnoDB table?

I don't think there is that specific metric.  However, looking at

SHOW STATUS LIKE 'Innodb%';

Some interesting things:

Innodb_pages_written / Innodb_buffer_pool_write_requests --
    over, say, 15% is "too high"
Innodb_buffer_pool_pages_flushed / Uptime --
    I/O writes per second
Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests --
    "wait to get into buffer_pool" - don't want that to go over about 1%

To cut back on the physical I/O, be sure to have innodb_buffer_pool_size set to about 70% of available RAM.

If you have RAID or SSD, we should discuss further.

If you have less than 4GB of RAM, consider getting more.

Indexing UUIDs is evil, even when not the PRIMARY KEY.

Please provide SHOW CREATE TABLE; I want to see the row size and table size.

TEXT and BLOB can cause issues.  Please provide SHOW CREATE TABLE.


How to install the additional module pg_trgm

pg_trgm is an extension, so:

CREATE EXTENSION pg_trgm;

If you get the following error

  ERROR:  could not open extension control file ".../extension/pg_trgm.control":
  No such file or directory"

then you need to install the module for your operating system

Ubuntu/Debian:

sudo apt install postgresql-contrib

Redhat/Centos

sudo dnf install postgresql10-contrib

Fedora

sudo dnf install postgresql-contrib


DENY EXECUTE on one schema object hides other objects in same schema

Since you didn't specify any other permissions you gave or denied to the user group AD\Users, I'll assume that they are memebers of db_datareader and public and have no other specific permissions.

To perform my test, I used SQL Server 2008, not R2 and started out with a completely new user group, database, schema and stored procedures.

As sysadmin

Created a Windows Group called AD\TEST_USERS in Windows

Created a login from that Windows Group in SQL Server

Created a test database TEST

Added AD\TEST_USERS as a member of public and db_datareader on the TEST database

Created a schema called XTR

Added two stored procedures sp_1 and sp_2 to XTR

As a member of AD\TEST_USERS

Opened up SSMS

Opened up the TEST database and expanded stored procedures.  Nothing was visible

As sysadmin

GRANT EXECUTE ON OBJECT::XTR.sp_1 TO [AD\TEST_USERS]
GRANT EXECUTE ON OBJECT::XTR.sp_2 TO [AD\TEST_USERS]

As a member of AD\TEST_USERS

I refreshed the stored procedures and now see XTR.sp_1 and XTR.sp_2

As sysadmin

DENY EXECUTE ON OBJECT::XTR.sp_1 TO [AD\TEST_USERS]

As a member of AD\TEST_USERS

I refresh stored procedures in SSMS and no longer see XTR.sp_1 but I do see XTR.sp2

Doing the same steps as you on my SQL Server 2008, simply denying EXECUTE on one stored procedure did not make the other stored procedure invisible in the same schema to members of my test group.


Updating one table from another table where non-primary-key field is similar string

Without the primary key, fixing the data with an update statement is likely to get very messy.  You're most likely better off getting a fresh export of the original data with the PK included, run the process/es that you did to clean up the data, then run an update statement.  And then put constraints on the table to prevent the errors from occurring again.

The update statement should be fairly simple though.  I don't know the full syntax for Access, but here's the general SQL statement I'd use on SQL Server:

update TableA
set FieldA = source.FieldA
from TableA destination
inner join TableB source
on destination.PK_ID = source.PK_ID


You have replaced the previous column reference in the CASE expression with the same subquery that you are using earlier to calculate the value of previous. While that certainly resolves the issue of referencing a calculated column at the same nesting level, duplication of code is far from an ideal solution.

You can avoid the duplication by abandoning the CASE expression completely as unnecessary. All it does is producing a "yes"/"no" result based on the values of the current and previous calledstationid only to be compared to "yes" at the outer level. Instead of checking on the value of achou, your outer level can be checking on the values of calledstationid and previous directly, like this:

SELECT COUNT(achou) as total
FROM (
    SELECT
        h.calledstationid,
        (
            SELECT calledstationid
            FROM radius.radacct_historical as sub
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
              AND sub.acctstarttime < h.acctstarttime
            ORDER BY sub.acctstarttime DESC
            LIMIT 1
        ) as previous,
    FROM radius.radacct_historical  AS h
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
) as S WHERE S.calledstationid = 34 AND S.previous = 42;

As you can see, I have also removed other elements from the query, like most of the columns returned by the nested SELECT (acctstarttime, acctstoptime, radacctid, callingstationid – they neither participate in the output nor affect the count result) and the nested ORDER BY h.acctstarttime ASC (there is no need to sort rows before counting them).


MySql: does max_user_connection concern SUPER user?

No, it the limit counts for every connection independent of which kind of user connects

This limits the number of simultaneous connections that can be made by any given account

See manual


How to set 'Enable identity insert' for all the tables at once during importing data in SQL Server?

You can do all tables at once in the Import / Export wizard. On the Select Source Tables and Views Page you can select the tick box in the source bar, once selected you can select Edit Mappings and you'll see Enable Identity Insert at the bottom.


Perhaps try:

DECLARE @sql varchar(max);
SET @sql = 'ALTER TABLE table_name ADD ' + @column_name + ' varchar(250);';
EXEC (@sql);


Recreate all foreign keys in all tables as deferrable (batch)

You can do this by generating the necessary script.

select 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' drop constraint '||quote_ident(conname)||';'||chr(10)||
       'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' add constraint '||quote_ident(conname)||' '||
       pg_get_constraintdef(c.oid, true)||' deferrable initially immediate;' as ddl
from pg_constraint c
  join pg_class tb on tb.oid = c.conrelid
  join pg_namespace ns on ns.oid = tb.relnamespace
where ns.nspname in ('public')   --<<< adjust the schema name(s) here
 and c.contype = 'f';

Spool the output of the above statement into a file and then run that generated script.


In MongoDB, $setIsSubset does not make efficient use of index

An index is basically a mapping from a field value to a document.  In MongoDB each element of the array is listed in the index separately.
Using that example data, the index might look something like:
"geography" => s2
"english" => s1
"english" => sN
"history" => sN
"maths" => s2

This lends itself well to equality and inclusion searches.  In a query for{ $match: { "$class": "maths" } }, it would simply start at the first occurrence of  "maths", scan through the index entries until it reach a value that is not "maths", and pass the list of matching on to the next part of the query.  The same works for an $in search with a separate scanned range for each value being tested.
This can also work for $nin by scanning the ranges of the index that fall between each value in the query.
The query for {$setIsSubset: {"$class", [input]}} is essentially
${and:[
   {class:{$in:[input]}},
   {class:{$not:{$nin:[input]}}}
]}

In order to efficiently use an index with the curren structure, the MongoDB would need to scan all of the matching ranges to find documents that match, and scan all non-matching ranges for documents that do not match, sort one or both lists, and then compare the two lists to eliminate documents that have both matching and non-matching values.
I am not sure if that would perform better or worse than just scanning all of the documents.
It may be worth submitting a feature request to see if that can be implemented.
Also node that the following from the $expr documentation:

$expr does not support multikey indexes.

In MongoDB "multikey" means at least one of the fields in one of the indexed documents contains an array.
As a workaround, you might try doing separating that test into multiple aggregation stages, first test the array with $in which will use and index to reduce the number of documents examined, then test the with $setIsSubset will be able examine only the documents that have at least one matching value.
I would be very curious how this query performs compared with the two you have tried already:
db.students.explain('executionStats').aggregate([
  { $match: { class: { $in: [ "maths" ] }} },
  { $match: { $expr: { $setIsSubset:  [ "$class", [ "maths" ] ] } } }
])


MySQL vs SQL Server Express (Compare Free DBMS)

I will be totally honest with you. While MySQL works fine in Windows, I would not recommend it for any Enterprise use in Windows.

You should go with SQL Server Express because you will have a database that works great in Windows for Free. Should your company fully commit to Enterprise Database usage, you could simply upgrade to SQL Server (different advanced versions).

If you are going for moderate budget, go with MySQL, BUT SWITCH TO Linux. High Availability solutions fit Linux and Unix, not Windows.


how to Enable these Windows Firewall rules using powershell?

You would use the Enable-NetFirewallRule cmdlet:

Enable-NetFirewallRule -Name "WMI-WINMGMT-In-TCP", "WMI-RPCSS-In-TCP"

In order to find the relevant rule names (in order to enable it), you can search existing firewall rules by name by using the Get-NetFirewallRule cmdlet, for example:

Get-NetFirewallRule -Name "*WMI*"

This uses wildcards to final all the rules that contain "WMI" as an example.


Ordering of table is based on Clustered index or non clustered primary key?

Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier.

If there is a clustered index then the data pages are stored with the rows in this order, but that does not guarantee that they will be processed in exactly this order (it makes it likely if there are no joins or ordering clauses, but with no explicit ordering request the order rows will be output is officially "undefined").

Without a clustered index the rows are stored in data pages in an unordered manner (this is referred to as a "heap table"). This may initially be the same order that the data was inserted, but as soon as deletes and updates start to happen this becomes definitely not true.

If you want to read around a little theory lookup "set theory" upon which relational databases are based. Mathematical sets are by their nature unordered, which means anything in a relational database should be considered unordered by default.

Regarding non-clustered primary keys: these do not affect the ordering of stored row data at all. They have extra meaning to your data model but the don't physically do anything that a non-clustered index with the "unique" property set would do. Similarly a clustered primary key is just a clustered unique index internally with extra meaning for your data model.

tl;dr: if the data needs to be ordered a particular way, you must directly specify that order in an ORDER BY clause.


In most cases HAVING should only be used in a query with a GROUP BY. From BOL:

  Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

You don't have any aggregates so HAVING isn't the right choice. You probably want to continue to use your WHERE clause:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
AND Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';

Below is a similar query that can use HAVING (although it doesn't really make sense). Note the presence of the GROUP BY:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
GROUP BY P.Data, Nm.Nome, epp.PosicaoFinal
HAVING Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';

In terms of getting the right results, the query that you have doesn't have join clauses. You're doing a cross join between Prova, Piloto, and EquipaParticipaProva.

Perhaps this is closer to what you wanted:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM EquipaParticipaProva epp
INNER JOIN Prova P ON p.IdProva = epp.IdProva
INNER JOIN Piloto Nm ON Nm.IdPiloto = epp.IdPiloto
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
AND Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';


Does it make sense to create an index on foreign key (uuid) column in Postgres?

It's usually recommended to have an index on foreign key column.  It helps when master and detail tables are frequently joined or when delete/update happens on master table. In this case absence of index causes full scan of detail table to enforce foreign key.
If any of above is true for your system , it would be a good idea to add index.

Side note.  You mentioned ids store guid values, so probably you never search by range. Then hash index would be much better choice compared to "normal" b-tree index.

Indexing the foreign key column is also useful if the parent table receives deletes (or updates on the PK). For every row in the parent table that is deleted, the database has to check the referencing tables if they still have rows referencing the parent. That check is done by selecting from the child table with a where condition on the FK column. Obviously this is faster if the FK column is indexed (this is true for other DBMS like Firebird, Oracle, SQL Server or DB2 as well)


Reconnect "not connected" query windows in SSMS to original connection

In SSMS, if you run a query in a disconnected window, it will attempt to re-connect to the prior connection. You will get an error, then the window will reconnect.  If you run a query like SELECT 1;, it will reconnect without having to do any data access/manipulation.

However, I'm a bit timid about relying on this to remember what server I was previously connected to. While I have noticed this to be the behavior, it is not documented, so I do not fully trust that it is "as designed" vs "the way it works." You should always make sure you are connected to the correct server before running any scripts.


SQL Server on Windows Server (VMware) - online storage swap

Follow the process here: Move Database Files to move the databases to the new storage.  You can leave the insance online, but each database will need to be OFFLINE while its files are copied to the new location.

Alternatively you can present the new drives, shut down SQL Server, copy all the files to the new drives, unmount the old drives, mount the new drives in the same folders or drive letters as the old drives, and restart SQL Server.

In both cases take backups of your databases before you start DO NOT delete any files from the old drives until you have sucessfully migrated to the new drives and taken new backups of your databases.

  what I need to achieve now is to force one of the available HA technologies to create a copy of my DB set into the same SQL Server instance.

To do that, use Backup/Restore, not Transnational Replication.  Replication is more complicated, expensive, and does not support tables without a primary key, or all database objects.  So, for each database:

Place Database in Full recovery mode
Take a Full Backup and restore it with a new name and location without recovery
Take a Tail Log Backup of the source database (taking it offline).
Restore the Tail Log Backup on the new database with recovery.
Rename or drop the old database
Rename the new database (back online).
Restore the desired recovery mode of the new database.


Transaction Replication:Intialize from Backup

You don't have to take the publisher offline when you initialize from a backup.  Just like initializing from a snapshot, the initialization just needs to bring the subscriber database to a point-in-time within the Distribution Retention Period, and to configure the subscription with the LSN to start from.

Perhaps you're thinking of Manual Initialization which requires you to synchronize the subscriber before beginning repilication.


How to have remote standby PostgreSQL server in different location (AWS)?

There are two ways to run a replica. You can use either, or both together:

Streaming replication, where the replica makes a PostgreSQL protocol connection to the standby as configured with primary_conninfo in recovery.conf; or
WAL shipping, where the replica runs a restore_command (set in recovery.conf that fetches WAL archives to replay.

See recovery.conf.

Both require that standby_mode be set in recovery.conf.

WAL-E is designed to be used as an archive_command on the master, to store WAL into S3, then as a recovery_command on the replica to fetch WAL. There is no need for a streaming replication connection. You can add one anyway, and it will be used where available, and the system will fall back to WAL archive recovery if it can't connect over streaming replication. Or you can just use WAL-based archiving.

The main advantage of using streaming replication as well is that it's more timely; you don't have to wait until an entire WAL archive is filled and shipped before the replica sees the changes.

For more information see the manual.


For SQL-Server & Postgres:

WITH RN AS
(
    SELECT date, sales, rest,
           COALESCE(LAG(sales) OVER (ORDER BY date DESC), 0) LG

    FROM sales
)
SELECT   date, sales,
         SUM(LG + rest) OVER (ORDER BY date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rest
FROM     RN
ORDER BY date

date       | sales | rest
:--------- | ----: | ---:
2017-01-01 |    52 |  240
2017-01-02 |    30 |  210
2017-01-03 |    10 |  200

For MySql:

SELECT date, sales, rest
FROM (
      SELECT t.date,
             t.sales,
             @acm := @acm + t.rest + @last_sales AS rest,
             @last_sales := t.sales
      FROM   sales t,
             (SELECT @last_sales := 0, @acm := 0) r
      ORDER BY t.date desc
      ) X
ORDER BY date;

date       | sales | rest
:--------- | ----: | ---:
2017-01-01 |    52 |  240
2017-01-02 |    30 |  210
2017-01-03 |    10 |  200

SQL-Server dbfiddle here

MySQL dbfiddle here

Postgres dbfiddle here


HSQLDB get next value for IDENTITY column

You can get the last identity value already inserted, but not the next one.

Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,

  INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'

See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions


Enable MySQL to use more than 1 core (multicore)

MySQL's implementation won't allow you to use more than one core per Server Thread.

I recommend divide-and-conquer strategy: break up your query in multiple (hopefully smaller, independent) jobs and start again.


What is the best way to compare values of whole tables between many databases?

This may not be exactly what you are looking to do, but you could use the EXCEPT statement to get the rows that are different:

SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1
EXCEPT
SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2

If you do it this way though, make sure you also do the opposite EXCEPT so you know the rows that don't exist in the other database's table:

SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2
EXCEPT
SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1

Hopefully that helps you a little.


How to get information about the current cluster configurations with list of datacenters?

As far as i understand your question, you can run nodetool status which shows all the data center including all their belonging nodes.

$ nodetool status
Datacenter: DC1
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Tokens  Owns (effective)  Host ID                               Rack
UN  <IP>   1.7 TB     256     7.2%              <Host ID>  RAC1

Datacenter: DC2
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Tokens  Owns (effective)  Host ID                               Rack
UN  <IP>   1.7 TB     256     7.2%              <Host ID>  RAC1


What types of locks are held on global temporary tables?

I was curious for this question and wanted to try myself and see whether tempdb is a special database or just like another user database wherein default isolation "Read Committed" is applied or not.

Below are queries that I executed:

--Created two tables in tempdb, one named table and second one as gobal tempdb table

select * into tempdb.dbo.msgs from sys.messages

select * into ##msgs from sys.messages

Executed Update query for few records of these tables in next window

begin tran
go
update dbo.msgs set language_id = 1055 where message_id = 11242
go
update ##msgs set language_id = 1055 where message_id = 11242
go

Both the above statement updated 22 rows as expected.

Later on I ran select statement on these two tables in two different window and so far both are stuck:

One thing I noted that in the first query no record is coming at all on screen whereas in the second case, 8830 records are on screen and stuck afterwards( Not sure why?) however in both the case, query is stuck and running forever as they are waiting for update to complete. This is what was expected in the default behavior of MSSQL database.

I validated same using sp_whoisactive and could see there is database blocking as below:

Lock information for each of them is provided for details.

1st:

<Database name="tempdb">
  <Objects>
    <Object name="msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="S" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

2nd:

<Database name="tempdb">
  <Objects>
    <Object name="##msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="UIX" request_status="GRANT" request_count="22" />
        <Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="22" />
      </Locks>
    </Object>
    <Object name="msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="UIX" request_status="GRANT" request_count="22" />
        <Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="22" />
      </Locks>
    </Object>
  </Objects>
</Database>

3rd:

<Database name="tempdb">
  <Objects>
    <Object name="##msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="S" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Also cross-checked isolation level for each of these transaction using below command:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id in(63,66,67)

Result:

With all these, it could be stated that for tempdb also, isolation level is set as "Read Committed" and rules apply similar to other databases.

I have run these queries on MSSQL 2019 version.


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)

This appears to be a authentication scheme problem.  If my memory serves correctly, the appearance of the ANONYMOUS LOGON is indicative of NTLM being used instead of Kerberos.  If your SQL Server service is running under a domain credential, you will need to ensure there is a Service Principle Name (SPN) present for SQL Server.  The syntax of the SetSPN command can be found here:  SetSPN  In short, you must marry a protocol with a TCP Port and the service account.  I believe the command will look something like this:

setspn -s MSSQLSvc/Server.Domain:TCPPort Domain\ServiceUser

To verify which authentication scheme your existing connections are using, run the following code:

SELECT
    dec.session_id,
    dec.auth_scheme
FROM sys.dm_exec_connections AS dec

If configured properly for Kerberos, you will see Kerberos in the auth_scheme column.  If not, you will see NTLM.

Finally, if your setup requires multi-hop authentication, you will need to configure each subsequent hop before the SQL Server to allow delegation.  Inside Active Directory, go to the properties page of the computer or user account that will need to pass through authentication to the SQL Server, go to the Delegation Tab, select "Trust this user for Delegation on Kerberos only", and then select the service that this account will be passing authentication to (search for your SQL Server service account).

Hope this helps,

Matt


How to upgrade PostgreSQL 9.6 to 10 in Ubuntu 16.04?

Is there any simple apt-get version?

Yes. As described in the webpage you linked to, you can install PostgreSQL 10 using the official posgres apt repository (instead of the default Ubuntu repository for 16.04):

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql

You can remove the old version (9.6) beforehand, e.g. by

sudo apt-get remove postgresql-* pgadmin3* pgagent

But make sure you backup your database first so that you can restore it after the upgrade as pointed out in the comments.


Connect to a remote MySQL Server from a dynamic IP address

Do you have the possibility to connect to your office network using a VPN? Then you could receive a static IP address in the range of your internal network. For this one, you could easily grant the necessary privileges.


parser_customer_period_mo is a many-to-many relationship table?  It probably does not need id.  (Getting rid of unnecessary indexes helps at some level.)  (This may have somehow caused the lock wait timeout.)

200 similar tables -- bad design.  (But seems to be 'necessary' in this situation.)

FKs cost something, but probably that won't explain what you are seeing.

IN ( SELECT ... ) is an inefficient construct (especially in older versions of mysql), see if you can turn it into a JOIN.

OR inhibits the use of an index.  (Probably cannot improve on that.)

Change innodb_buffer_pool_size to be about 70% of available RAM.  This should cut back the I/O a lot.


Convert string numeric values with comma as decimal separator to NUMERIC(10, 2)

(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.)

You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then convert the comma into a period / decimal and you are done:

SELECT CONVERT(NUMERIC(10, 2),
               REPLACE(
                       REPLACE('7.000,45', '.', ''),
                       ',', '.'
                      )
              ) AS [Converted];

Returns:

7000.45

For the sake of completeness, I should mention that I also tried:

SET LANGUAGE Greek;
Looking at various format styles for CONVERT, but nothing applies here.
The FORMAT function, but the input type must be a numeric or date/time/datetime value (that and it was introduced in SQL Server 2012, so not applicable to SQL Server 2008 R2 or older).

And nothing else seemed to work. I was hoping to find something more elegant than two REPLACE calls, but so far no such luck.

Also, just to mention, while not a pure T-SQL solution, this can also be accomplished via SQLCLR. And, there is a pre-done function that does this in the SQL# library (that I wrote) named String_TryParseToDecimal. This function is available in the Free version, and works in every version of SQL Server starting with SQL Server 2005:

SELECT SQL#.String_TryParseToDecimal('7.000,45', 'el-GR');

Returns:

7000.45000000000000000000


Restoring a mysql database using --single-transaction

--single-transaction is used to ensure the dump is consistent when dumping, it has nothing to do with import/restore.

Loading a dump is not generally possible in a single transaction because many CREATE statements do an implicit commit.

It is often not even desirable because for a rollback to be possible, the transaction needs to keep undo logs and if you import many rows over many tables, the logs will grow too much, eat memory and disk space and slow down the import and all other operations.

If your issue is import speed, check that extended inserts are being used in the dump (that each insert statement inserts multiple rows at once) and you have proper buffer pool and log size configured for InnoDB.

If you want to import the data without issuing any locks as stated in your question then I am afraid that's not possible, because DML (inserts, updates, deletes) just have to lock to keep things ACID.

You should not restore from a dump a database of any service while it is running and restoring a specific database to a server instance hosting other databases should not issue any locks hindering these other services.


Get the last ID after INSERT the PostgreSQL database with PgBouncer

PostgreSQL creates a sequence by default per table. e.g actor_id_seq for the actor table on the column id.
So, this will return the last inserted id value:
select currval('actor_id_seq'::regclass)

The sequence generator operation is not transaction-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value. If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.


I found an answer in Redshift documentation soon after asking the question. The chapter is called Managing Concurrent Write Operations:

Amazon Redshift allows tables to be read while they are being incrementally loaded or modified.
In some traditional data warehousing and business intelligence applications, the database is available to users only when the nightly load is complete. In such cases, no updates are allowed during regular work hours, when analytic queries are run and reports are generated; however, an increasing number of applications remain live for long periods of the day or even all day, making the notion of a load window obsolete.
Amazon Redshift supports these types of applications by allowing tables to be read while they are being incrementally loaded or modified. Queries simply see the latest committed version, or snapshot, of the data, rather than waiting for the next version to be committed.

That corresponds to MVCC definition from Wikipedia:

MVCC [...] approach: each user connected to the database sees a snapshot of the database at a particular instant in time. Any changes made by a writer will not be seen by other users of the database until the changes have been completed (or, in database terms: until the transaction has been committed.)
When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead marks the old data as obsolete and adds the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else.

Connected with Redshift vacuuming description that I posted in the question, it makes me believe Amazon Redshift does support MVCC. Although I still was not able to find any mention of MVCC in official docs for Redshift.


SQL Server AlwaysON : start sync with secondary for selected databases

Yes, you will need to manually backup and restore the databases, with logs, to the new secondary and then join the databases to the AG.  This might help.