SQL Server 2012 Licensing on a VMware Infrastructure

Excerpts from  Microsoft Licensing Guide

How to License Individual Virtual Machines Using the Per Core Licensing Model
*Similar to the Per Core licensing model in physical OSEs, all virtual cores (v-cores) supporting virtual OSEs that
are running instances of SQL Server 2012 software must be licensed accordingly.
To license individual VMs using the Per Core model, customers must purchase a core license for each v-core (or virtual processor, virtual CPU, virtual thread) allocated to the VM, subject to a four core license minimum
per VM. For licensing purposes, a v-core maps to a hardware thread. When licensing individual VMs, core factors do not apply.
Note: Licensing individual VMs is the only licensing option available for SQL Server 2012 Standard Edition
customers who are running the software in a virtualized environment under the Per Core model.*

Licensing is tricky, I would encourage you contact Microsoft to get true answer

You should call 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist, and you can get more detail information from them. Outside North Ameriac you can use the Guide to Worldwide Microsoft Licensing Sites to find contact information in your regional MS location.


many Servers of SQL server 2012 needs mirroring

Automatic failover requires a witness. See Database Mirroring Witness:

  To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

Your application will also have to use a mirroring aware connection string, see Connect Clients to a Database Mirroring Session:

  "Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"


Take a look the queries in your question

/* Execution time 7.0095062E-5 */
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

/* Execution time 5.3167343E-5 */
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,77379,77381)

/* Execution time 9.2029572E-5 */
SELECT wp_posts.* FROM wp_posts WHERE ID IN (77381,77379,77375)

Look at the execution time above each query

7.0095062E-5 is .000070095062 seconds
5.3167343E-5 is .000053167343 seconds
9.2029572E-5 is .000092029572 seconds

The SQL queries themselves are fast.

Please read any release notes on WordPress 3.4.1 to see if WordPress upgrade installed any DB-related modules that need tuning or deactivation. Also, check for any data that was added during the upgrade process that may have bloated your tables.


I am presuming you are setting it to AMERICAN_AMERICA.* with your ALTER SESSION.

In my experience the language setting has to match between the client, server and nls_database_parameters.  It might be a big mission for your DBA to convert your character set to AMERICAN_AMERICA.UTF8 using the Database Migration tool, so would it be possible to make your change to NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1 on the server?

Another problem is that WE8ISO8859P1 is a subset of UTF8 so when your RDBMS does its single-pass (or more) conversions when transporting data between the layers, you can get lossy results and see strange characters returning to your client.  So you would be better off to set your client and server locale to WE8ISO8859P1 to match your nls_database_parameters on the database.


Function accepts only certain passwords in postgreSQL

That's because you used the wrong format specifier. %I is for identifiers, but the password is a string literal, so you should use %L (and not include the single quotes).
Compare these:
SELECT format('PASSWORD %L', 'gut'), format('PASSWORD ''%I''', 'gut');
     format     |     format
----------------+----------------
 PASSWORD 'gut' | PASSWORD 'gut'
(1 row)

test=> SELECT format('PASSWORD %L', 'Böse'), format('PASSWORD ''%I''', 'Böse');
     format      |      format
-----------------+-------------------
 PASSWORD 'Böse' | PASSWORD '"Böse"'
(1 row)

So there were unexpected double quotes included in the password.
But there are bigger problems:
SELECT format('ALTER USER me PASSWORD ''%I''', 'inject'' SUPERUSER --');
                     format
-------------------------------------------------
 ALTER USER me PASSWORD '"inject' SUPERUSER --"'
(1 row)

Ha! I can abuse your function to become superuser, because you didn't use proper quoting.


Restoring a MS SQL Server database on Linux

Try to edit the script (/opt/mssql/lib/mssql-conf/mssql-conf.py) using text editor, It is line 1: #!/usr/bin/python22, which may be wrong.

I changed it #!/usr/bin/python2
Plz try this


Storage accounts not showing up when creating Azure Storage Credential

I see the same thing.  I suspect that the SSMS Maintenance Plan UI in SSMS for generating an SQL Server Credential to use with Azure Storage is out-of-date, or simply broken.
I would use the built-in (and newer) Managed Backup to Azure feature instead of the Maintenance Plan in the first place.
If you do want to use the Maintenace Plan, you'll need to create the credential yourself, per the docs here.  The tricky part (and the bit SSMS was trying to save you from) is generating the Shared Access Signature, which needs to be done in Powershell, or with the Azure Storage Explorer (not in the Azure Portal).


How to connect an MSSQL database engine to a local network

the proper way - you need run Configuration Manager and check if tcp/ip support enabled for all necessary interfaces.


How can I optimize a mysqldump of a large database?

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways:

Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly flicking between the location being read from and the location being written to.
The output of mysqldump will be very compressible, so if you can not separate the output from the input as mentioned above pipe the output through gzip or similar. This will reduce the amount of writing being done (so reduce the overall IO load, and the amount of head movement) at the expense of some CPU time (which you may have a lot of spare at these times anyway).
Also, (as well or instead of compression) pass the output through a pipe utility (like pv) that supports large write buffers to group blocks written to the drives together more, again to reduce the effect of head-movement latency - this will make quite a difference if using the --quick option to reduce the RAM impact of backing up large tables).
Only run your backup process when IO load is otherwise low.

You may be fixing the wrong issue though: it might be easier to address the connection drops instead (though reducing the I/O load imposed by your backups will help reduce the effect you have on other users so is worth trying anyway). Could you run your manual backups through screen (or similar tools like tmux)? That way if your connection to the server drops you can just reconnect and reattach to the screen session without any processes getting interrupted.

If you are sending the data directly over the connection (i.e. you are running mysqldump on your local machine against a remote database, so the dump appears locally) you might be better off running the dump on the server first, compressing as needed, then transferring the data over the network using a tool (such as rsync) which supports partial transfers so you can resume the transfer (instead of restarting) if a connection drop interrupts it.

As part of your "reducing the size of the overall database to resolve this issue" I would guess that a large chunk of your data does not change. You might be able to move a large chunk of the 1.2Gb from that main table off into another and remove that from those that are copied by the mysqldump call. You don't need to backup this data every time if it never changes. Splitting data between tables and databases this way is usually referred to as data partitioning and can also allow you to spread the data and I/O load over multiple drives. High-end database have built in support for automatic partitioning, though in mysql you will probably have to do it manually and alter your data access layer to account for it.

Straying off-topic for this site (so you should probably nip over to ServerFault or SuperUser to ask if you need more detail): If you seem to be losing connections due to inactivity, check the options in your SSH server and SSH client to make sure keep-alive packets are enabled and being sent often enough. If seeing drops even if the connection is active you could also try using OpenVPN or similar to wrap the connection - it should handle a short drop, even a complete drop if your entire connection is down for a few seconds, such that the SSH client and server don't notice.


What permissions are necessary for truncating a table?

The best place to look for this information is in books online. The article on TRUNCATE TABLE here indicates:

  The minimum permission required is ALTER on table_name. TRUNCATE TABLE
  permissions default to the table owner, members of the sysadmin fixed
  server role, and the db_owner and db_ddladmin fixed database roles,
  and are not transferable. However, you can incorporate the TRUNCATE
  TABLE statement within a module, such as a stored procedure, and grant
  appropriate permissions to the module using the EXECUTE AS clause.

So ALTER is the minimum permissions required. You can get that as DB Owner, you can get that as DB_DDLAdmin. Or just grant alter.

If you think about what truncate does and how it works, this makes sense, it is a pretty "severe" command and empties the table of data and does it quickly.


SQL estimates are way off on DELETE statement with Triggers on huge tables

Assuming all the related tables have correct indexing for the delete paths, you could try:

DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

If that works, try to reduce it to the minimal number of hints.

These sorts of plans are very challenging for cardinality estimation, and the 'default' CE model often makes a mess.

Once you have a plan shape that works well, you should be able to force that shape using a plan guide etc. if necessary.


want to use logical replication with a postgresql10 cluster (that is not using logical replication)

Replicating a single database can only be done using logical replication.
You just need to change the wal_level from replica to logical.
The level logical includes all the information that replica includes, plus some more that's required for logical replication. So changing it, won't break your streaming (physical) replication.


Functional dependencies applies to whole database or to a particular relation?

A functional dependency is exactly what the term implies - the output of the function is always determined by the input.  If for example we have a function f(), and provide variable x, and we always receive output y, then y is functionally dependent on x.  You can think of this like a simple graphing function
2x + 1 = y
Plugging some sample values into the function we get:

X    Y
------
1    3
2    5
3    7

and so on.  Thus we know that for every value of x, there is value of y that will always be that value of y for that x.

Codd adapted this to data management with respect to determining if one data element's value always results in a known value of a second data element as if it were the input and output of a function.  So for example, if we find that each employee of a small company is given an employee number, and we create a simple list of employee numbers and names, we find that the name is functionally dependent on that employee number.  Every time we see the employee number 7 for example, we see the name "Jim Brown."  This represents in the database the proposition that is true in the real world - namely that employee number 7 is the person named "Jim Brown" that we can point to and say "that's him."

Functional dependency can be implemented as a unique constraint because there is a one to one relationship between the input to the function and the expected output.  In the algebra example, when we plug 2 into the equation 2x + 1 we will always get 5.  Applying that example to data management in the example above, each time we have the employee number 7 we need to have one and only one name and other characteristics that goes with it.  We can't have employee number 7 associated with "Jim Brown" and "Bob Jones" anymore than we could say that plugging 7 into 2x + 1 can yield 15 and 25.  This application of simple math to data management is the fundamental underpinning that allows the DBMS to be told how to protect data integrity without having to understand the semantics of the data.

Functional dependencies are by definition implemented when a unique constraint is placed on each table identifying each set of data elements that have a one to one relationship with another set of data elements and whose values always vary with that first set.  The notation is typically:

A,B ---> C

This means that the value of A and B determines the value of C.  Perhaps A is employee number and B is Dependent Number, and C is the dependent's name.  So in this sense they apply to each and every table in each and every schema in each and every database.

Some good references on relational theory are Fabian Pascal's Practical Database Foundation Series and Chris Date's book Relational Theory for Computer Professionals.


DATETIME2FROMPARTS alternatives for SQL Server 2008

You can make it a bit shorter.

dateadd(hour, datediff(hour, 0, datum), 0)

This calculates the number of hours between 1900-01-01 00:00:00.000  and datum and adds that value to 1900-01-01 00:00:00.000.


Multiply elements of equally sized postgresql array

Parallel Unnest

What you want is something like this with parallel-unnest. Here we use the ARRAY() constructor rather than array_agg()

SELECT ARRAY(
  SELECT a*b
  FROM unnest(
    ARRAY[1,2], -- ex1
    ARRAY[2,3]  -- ex2
  ) AS t(a,b)
);

Creating our own function

In functional programming, we call this zip, and you'd use that in a language with primitive functional support like Python, or the like. In languages with more advanced functional support, like Haskell, you'd use zipWith or Ramda.js R.zipWith()

This will return an array sized to the greater of ex1, or ex2. You can put this in a function like this,

CREATE FUNCTION array_zip_with_multiply( arr_q anyarray, arr_e anyarray )
RETURNS anyarray
AS $$
  SELECT ARRAY(
    SELECT a*b
    FROM unnest(
      arr_q, -- ex1
      arr_e  -- ex2
    ) AS t(a,b)
  );
$$ LANGUAGE sql
IMMUTABLE;

SELECT array_zip_with_multiply( ARRAY[1,2,9,4,5], ARRAY[5,8,4,0,12] );
 array_zip_with_multiply
-------------------------
 {5,16,36,0,60}
(1 row)

Note: this won't work if there isn't an operator * defined for the type.


Suggestions - Enabling EFS on FileStream Data for SQL Server

I can't speak to whether or not EFS should be applied per folder.  However, there is a security consideration that seems to be worth an answer here.

Check out this post from David Browne on the MSDN forums question Can FILESTREAM directory containers be encrypted with EFS?:

  Note that during a SQL Server backup of FILESTREAM data, the data is stored decrypted. A restore operation to a normal volume therefore will result in decrypted values.

So if you are using native SQL Server backups, you will need to keep this caveat in mind from a security / compliance standpoint.


Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle

You want to flag the function for the reason of CPU bottleneck, based on what?

You could say you found the sql_id, and checked v$sql, compared elapsed_time to plsql_exec_time and found that a huge portion of elapsed_time comes from plsql_exec_time and cpu_time is also high.

Verify this with:

select elapsed_time, plsql_exec_time, cpu_time from v$sql where sql_id = '...';

Or even better, you can check where the time is actually spent with:

alter session set statistics_level=all;

Run the statement:

update LIST_JOURNAL
   set STATUS       = '4',
       END_DATE     = :b1,
       END_TIME     = :b2,
       END_TYPE     = '1',
       USER_ID  = 'SYSTEM',
       ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2)
 where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and
       STATUS < '4');

Then view the actual statistics of this execution with:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

The above gives you exact execution count, timing, cardinality, buffer gets, disk reads, temp, memory, etc. information about each step of SQL execution. This works in any edition of the database and does not require any licensed option. There are various ways of achieving the above

There is no index on the LIST_JOURNAL table, but it has 3.7 million rows and the above output indicates that your statement performed a TABLE ACCESS FULL on LIST_JOURNAL with 100000 buffer gets but 0 reads because the table is in cache, but this step actually returned only 5 rows (A-Rows) and this step took 10 seconds, while the whole execution took 10.2 seconds? Sure, you are very likely to have a missing index problem. What index to create? We do not know, because the distribution of the data is unknown to us. You may need a composite index with all columns in the predicates or just 3 of them or a simple index on only the TERM_ID column may suffice.

Or the time spent in most steps is insignificant, but the time spent in the UPDATE step is high, combined with plsql_exec_time being relatively high compared to elapsed_time and a relatively low amount of affected rows? Sure, check the function.

The above is the absolute minimum that I would check, before even thinking about the datatypes used in the function. Collect the facts first before theorizing without the slightest idea where the problem originates from.

Some answers to the original questions:

Yes, it can be. Even with the proper datatypes.
Yes, it can. Depends on the data distribution, number of affected rows.
Depends. Typically yes, when storing dates.


Has anyone had luck sending dbmail via relay to an external server on an Azure VM (which appears to block SMTP outbound regardless of rules)?

Please read: Enhanced Azure Security for sending Emails – November 2017 Update which outlines the recommendations, options and restrictions for sending emails in Azure.

In particular:

  For Pay-As-You-Go or Microsoft Partner Network subscriptions created
  after November 15, 2017, there will be technical restrictions blocking
  e-mail sent directly from VMs in these subscriptions.  Customers that
  need the ability to send e-mail from Azure VMs directly to external
  e-mail providers (not using an authenticated SMTP relay) can make a
  request to remove the restriction.  Requests will be reviewed and
  approved at Microsoft’s discretion and will be only granted after
  additional anti-fraud checks are performed.  To make a request, open a
  support case with the issue type Technical --> Virtual Network -->
  Connectivity --> Cannot send e-mail (SMTP/Port 25).  Be sure to add
  details about why your deployment needs to send mail directly to mail
  providers instead of going through an authenticated relay.


Thanks  Akina - your message helped me get on the right track and sort this out:

  Copy this result to (temporary) table with additional autoincrement field (with the name rownum, for example, autoincrement field will (must) be PRIMARY KEY, so it will be filled with values automatically), then expose the data from this table and select first/next chunks by this field values (WHERE rownum BETWEEN 50*@chunknum-49 AND 50*@chunknum).

I ended up creating a new table using the previous resultset with the rownum autoincrement that you suggested


Oracle Hierarchical query: how to follow max on each level

This seems to solve the question asked. Not sure if there is a solution without aggregating first:

select lpad(' ',2*(level-1)) || child as result
from
  ( select parent, max(child) as child
    from test_connect_by
    group by parent
  ) t
start with parent is null
connect by prior child = parent
order by level ;


SQL query to identify missing hinted indexes

Personally, I would start with DBA_HIST_ACTIVE_SESS_HISTORY, and look at all statments that contain that type of hint.

From there, you can pull the index name coming from that hint, and then do a lookup on dba_indexes to see if the index exists, is valid, etc.

You should be able to do this via PL/SQL if you want to make it really fancy and do it all in one step, otherwise a few pieces of SQL and a spreadsheet application can be your friend.


START WITH and CONNECT BY condition in Oracle SQL

When you have CONNECT BY without START WITH, the root, starting points of the recursion are all the rows of the table. It is useful if you want to find all the hierarchies under all employees.

The condition in CONNECT BY is checked only for the next levels of recursion, so only for siblings.

You can modify START WITH to get everything except Cambrault:
(note that this way, an employee may appear multiple times, one as individual, one as a child of their manager, another as a child of their manager, another as a grandchild of their manager's manager, etc.
(so this is probably not what you want):

START WITH
      NOT (last_name = 'Cambrault' AND first_name = 'Gerald')

or all hierarchies under top level managers (employers without a manager). Everyone will appear once this way:

START WITH manager_id IS NULL

or all hierarchies under top level managers (employers without a manager) except Cambrault's.
Note that this will give you the same results as the previous START WITH with current data (but will differ when Cambrault is promoted to top level manager):

START WITH manager_id IS NULL
  AND NOT (last_name = 'Cambrault' AND first_name = 'Gerald')

Test in dbfiddle.uk.


Warning: #1292 Incorrect datetime error when using DATE_ADD()

You try to add to the string 'foo' 5 days.
Use backticks for column names
SELECT * FROM table WHERE DATE_ADD(`foo`, INTERVAL 5 DAY) < '2021-01-09 00:00:00'


N prefix before string in Transact-SQL query

The following articles have some good information on the question. The short answer is just that there's a type mismatch between the unicode column and non-unicode string literal you're using. From the KB article, it looks like omitting the N prefix might still work in some cases, but it would depend on the code page and collation settings of the database. That might explain the change in behavior, if you were previously having success with the no-prefix method.

https://support.microsoft.com/en-us/kb/239530


Change some of the 1048576's to 1048576.0.

SELECT distinct(volume_mount_point),
 total_bytes/1048576 as Size_in_MB,
 available_bytes/1048576.0 as Free_in_MB,
 (total_bytes/1048576)-(available_bytes/1048576.0) as Used_in_MB,
 (((total_bytes/1048576)-(available_bytes/1048576.0))/(total_bytes/1048576))*100 as Used_in_Percentage
FROM sys.master_files AS f CROSS APPLY
 sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
 available_bytes/1048576.0 order by 1

It's an awful query though.


Please explain what does "for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)')" do in this code

A common technique for aggregate string concatenation before SQL Server 2017 is using XML data type methods along with STUFF to remove the extra delimiter. In SQL Server 2017 and later, STRING_AGG makes this task much cleaner and easier. The STRING_AGG equivalent:

select @colsUnpivot = STRING_AGG(quotename (C.name),',')
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

The components of the XML expression technique for aggregate string concatenation are as follows.

This subquery will generate a result set with one row per column in the source table with a comma preceding each column name:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

Example result:

,[column1]
,[column2]
,[column3]

Adding the FOR XML PATH(''), TYPE converts these rows into a single strongly-typed XML text node with the concatenated rows, allowing XML methods like value to be used on the type:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE

Result (XML type):

,[column1],[column2],[column3]

Invoking the method value('.', 'NVARCHAR(MAX)') on that XML node converts the XML node to an nvarchar(MAX) string. The value XML data type method takes an XQuery expression with '.' representing the current node (only node here) and the second argument being the desired SQL data type to be returned.

SELECT
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)')

Result (nvarchar(MAX)):

,[column1],[column2],[column3]

Finally, the STUFF function removes the extraneous leading delimiter from the string. There is an error in your original example; the length should be 1 instead of 8 so that only the comma is removed:

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Final result (nvarchar(MAX)):

[column1],[column2],[column3]

I sometimes see folks omit the , TYPE).value('.', 'NVARCHAR(MAX)') when using this technique. The problem with that is that some characters must be escaped with XML entity references (e.g; quotes) so the resultant string will not be as expected in that case.

EDIT:

Incorporating Mister Magoo's recommendation to use (./text())[1] instead of just . as the node spec, the query below will improve performance with large data sets.

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'');


MySQL Cluster: cannot start the management node

I believe that you can ignore this error as the mysqld should still start up.

However, in trying to reproduce the problem, I've found a glitch in the quick start guide for Windows, a "-" (the standard dash, ASCII 45) has been substituted with a "–" (the long dash, ASCII 150), the correct options for the MySQL client should be "-h 127.0.0.1 -P5000 -u root".

Try this out (i.e. type out the option rather than cutting and pasting from the quick start guide).

With the wrong character, the MySQL client refuses to connect to the MySQL daemon which could give the impression that the MySQL daemon process (mysqld) wasn't running correctly.


Use SQL Developer to (create the table and) load the data into a table.

From there, use a JOIN.


If you would like to manually control this, here are some suggestions
Login to Window Command Line Shell as Administrator and run this
C:\> net stop mysql

When the prompt comes back, MySQL is down.
If you shutdown Windows, the only evidence for MySQL's shutdown would be in the error log.
EXAMPLE
On my laptop

I have MySQL 5.5.37 for Windows
It's been up since Dec 17, 2014
The error log filename says 12/17/2014  11:09 AM            83,010 ROLAED3573-NYTD.err
I just ran net stop mysql
The error log filename says 01/09/2015  02:02 PM            83,304 ROLAED3573-NYTD.err

From my actions, MySQL shutdown wrote 294 bytes at the end of the error log. What did it write ?
150109 14:02:33 [Note] MySQL: Normal shutdown

150109 14:02:33 [Note] Event Scheduler: Purging the queue. 0 events
150109 14:02:33  InnoDB: Starting shutdown...
150109 14:02:34  InnoDB: Shutdown completed; log sequence number 10001663889
150109 14:02:34 [Note] MySQL: Shutdown complete

Given this output, I would expect a shutdown to have these lines at the end of the error log.
UPDATE 2015-01-09 14:47 EST
I just ran this
C:\MySQL_5.5.37\data>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

C:\MySQL_5.5.37\data>

The error log says 01/09/2015  02:34 PM            84,289 ROLAED3573-NYTD.err
This landed at the bottom of the file
150109 14:34:48 [Note] Plugin 'FEDERATED' is disabled.
150109 14:34:48 InnoDB: The InnoDB memory heap is disabled
150109 14:34:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
150109 14:34:48 InnoDB: Compressed tables use zlib 1.2.3
150109 14:34:48 InnoDB: Initializing buffer pool, size = 1.0G
150109 14:34:48 InnoDB: Completed initialization of buffer pool
150109 14:34:48 InnoDB: highest supported file format is Barracuda.
150109 14:34:48  InnoDB: Waiting for the background threads to start
150109 14:34:49 InnoDB: 5.5.37 started; log sequence number 10001663889
150109 14:34:49 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150109 14:34:49 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150109 14:34:49 [Note] Server socket created on IP: '0.0.0.0'.
150109 14:34:50 [Note] Event Scheduler: Loaded 0 events
150109 14:34:50 [Note] MySQL: ready for connections.
Version: '5.5.37-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

I have shutdown my laptop
I booted up my laptop
The error log says 01/09/2015  02:40 PM            85,568 ROLAED3573-NYTD.err
What did it write this time ?
150109 14:39:13 [Note] MySQL: Normal shutdown

150109 14:39:13 [Note] Event Scheduler: Purging the queue. 0 events
150109 14:39:13  InnoDB: Starting shutdown...
150109 14:39:13  InnoDB: Shutdown completed; log sequence number 10001663889
150109 14:39:13 [Note] MySQL: Shutdown complete

150109 14:40:04 [Note] Plugin 'FEDERATED' is disabled.
150109 14:40:04 InnoDB: The InnoDB memory heap is disabled
150109 14:40:04 InnoDB: Mutexes and rw_locks use Windows interlocked functions
150109 14:40:04 InnoDB: Compressed tables use zlib 1.2.3
150109 14:40:04 InnoDB: Initializing buffer pool, size = 1.0G
150109 14:40:04 InnoDB: Completed initialization of buffer pool
150109 14:40:04 InnoDB: highest supported file format is Barracuda.
150109 14:40:05  InnoDB: Waiting for the background threads to start
150109 14:40:06 InnoDB: 5.5.37 started; log sequence number 10001663889
150109 14:40:06 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150109 14:40:06 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150109 14:40:06 [Note] Server socket created on IP: '0.0.0.0'.
150109 14:40:06 [Note] Event Scheduler: Loaded 0 events
150109 14:40:06 [Note] MySQL: ready for connections.
Version: '5.5.37-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

As shown from the end of the error log, it performed a clean shutdown at 2:39 PM. MySQL came back up when Windows was started.


Use a split string table valued function and CROSS APPLY.

--sample split string TVF
create FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

--demo setup
Declare @Table1 table (HIC_Number varchar(5), DateFrom date, DateTo date, PrimaryDx varchar(5), AdmitDX varchar(5), AdditionalDx varchar(100))
insert into @Table1 (HIC_Number,DateFrom,DateTo,PrimaryDx,AdmitDX,AdditionalDx) values
('A','1/1/2018','1/25/2018','E559','E459',';E559;E099;E1121;E784;E039;J449')

--the solution
SELECT HIC_Number
    ,DateFrom
    ,DateTo
    ,PrimaryDx
    ,AdmitDX
    ,Item
FROM @table1 a
CROSS APPLY dbo.SplitString(a.AdditionalDx, ';')
WHERE item <> ' '

| HIC_Number | DateFrom   | DateTo     | PrimaryDx | AdmitDX | Item  |
|------------|------------|------------|-----------|---------|-------|
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E559  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E099  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E1121 |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E784  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E039  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | J449  |


How to purge logs by using Flashback feature in Oracle Database

FRA will be purged automatically when free space is needed
Put the archived logs in FRA as well
Use RMAN to make the backups and use RMAN commands (REPORT OBSOLETE / DELETE OBSOLETE) to manage the purging of  backup pieces + archived logs from FRA

From docs: (http://docs.oracle.com/cd/B19306_01/backup.102/b14192/setup005.htm)

The Oracle Flashback Database feature, which provides an convenient alternative to point-in-time recovery, generates flashback logs, which are also considered transient files and must be stored in the flash recovery area. However, unlike other transient files, flashback logs cannot be backed up to other media. They are automatically deleted as space is needed for other files in the flash recovery area.

When Files are Eligible for Deletion from the Flash Recovery Area

There are relatively simple rules governing when files become eligible for deleteion from the flash recovery area:

Permanent files are never eligible for deletion.
Files that are obsolete under the configured retention policy are eligible for deletion.
Transient files that have been copied to tape are eligible for deletion.


You can use the UNION operator to combine your hydrats_survey and hydrants_measurement tables as one dataset, then you can use the ROW_NUMBER() window function to get the latest row per hydrant from that dataset with a query like the following:
SELECT hydrants.fid, hydrants.h_number, conditions.hydrant_condition_date as last_date, conditions.condition, conditions.st_pressure, conditions.dyn_pressure
FROM hydrants
INNER JOIN
(
    SELECT fid, hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure, ROW_NUMBER() OVER (PARTITION BY hydrants_fid ORDER BY hydrant_condition_date DESC) AS SortId
    FROM
    (
        SELECT fid, survey_date AS hydrant_condition_date, condition, hydrants_fid, CAST(NULL AS INT) AS st_pressure, CAST(NULL AS DECIMAL(10,2)) AS dyn_pressure
        FROM hydrats_survey

        UNION

        SELECT fid, measurement_date AS hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure
        FROM hydrants_measurement
    )
) conditions
    ON hydrants.fid = conditions.hydrants_id
WHERE conditions.SortId = 1 -- Filters out everything but the latest record per hydrant ordered by date

The only caveat is if you have two records with the same date that are different in other ways (such as different conditions, as nbk points out), this will choose one of those two at random because there is no distinct field combination to order on inside of the ROW_NUMBER() function.
One might try to solve this by using the fid column of the hydrats_survey and hydrants_measurement tables, but those two fields are independent of each other and could still end up creating a non-unique dataset when unioned together in the subquery. So you'd need to decide on a unique field to be the tie-breaker (e.g. if there's a bad and good condition on the same day, then take the record with the bad condition) or accept the results will be semi-random when there are two records on the same date.


DBMS recommandation for C# applications with provides triggers, procedures and if possible free

I think you have two that I would recommend:

The first is PostgreSQL, which runs on your local computer as a database server.  It has an impressive feature set and allows stored procedures to be written in a number of languages (including PL/PGSQL, PL/Perl, and PL/Python).  It is free and it is enterprise-grade.  PostgreSQL ideas have made their way into Informix and elsewhere.

The second is Firebird which is the open sourced version of Interbase.  It contains triggers, stored procedures, and more, and is free.  The feature set is not as large as it is with PostgreSQL in terms of programmability, but it has the additional feature of being able to be run as an embedded library in the process of your application.  PostgreSQL also has a larger community.


It looks like this bug has already been reported, and is listed as "Stalled" at the moment.

https://jira.mariadb.org/browse/MDEV-10463


You must either be the superuser or logged in as the same user who owns the session you wish to cancel.

So connect as user myuser and you'll be able to pg_cancel_backend, or, if that doesn't respond, pg_terminate_backend.


Selecting, inserting, updating and deleting in a single query - possibly?

Sure, you can use the OUTPUT clause in an UPDATE statement.

-- create some test tables

create table #sample(
    id int identity(1,1) not null primary key,
    serial varchar(50),
    reason varchar(50),
    movedTo varchar(50),
    region varchar(50),
    movedDate date
    );

go

create table #sample2(
    id int identity(1,1) not null primary key,
    serial varchar(50),
    reason varchar(50),
    destination varchar(50),
    );

go

-- add some sample data
insert #sample(serial, reason, movedTo, region, movedDate)
select top(10) RTRIM(row_number() over( order by @@SPID)), newid(), newid(), null, GETDATE()
from sys.columns a;

go

-- perform the update/insert in one
update #sample
set region = movedTo, movedTo = null, movedDate = null
output deleted.serial, deleted.reason, deleted.movedTo
into #sample2(serial, reason, destination)
where movedTo is not null;

go

-- show the result
select *
from #sample;

select *
from #sample2;


Replace consective numbers from a string

I used the following, which gets you part of the way there.

USE tempdb;
GO
CREATE TABLE Test
(
    uname VARCHAR(255)
    , num INT
);

INSERT INTO Test (uname, num) VALUES ('Max', 1);
INSERT INTO Test (uname, num) VALUES ('Max', 2);
INSERT INTO Test (uname, num) VALUES ('Max', 3);
INSERT INTO Test (uname, num) VALUES ('Max', 4);
INSERT INTO Test (uname, num) VALUES ('Max', 5);
INSERT INTO Test (uname, num) VALUES ('Max', 11);
INSERT INTO Test (uname, num) VALUES ('Max', 12);
INSERT INTO Test (uname, num) VALUES ('Max', 13);
INSERT INTO Test (uname, num) VALUES ('Max', 14);
INSERT INTO Test (uname, num) VALUES ('Max', 15);

INSERT INTO Test (uname, num) VALUES ('Jen', 22);
INSERT INTO Test (uname, num) VALUES ('Jen', 23);
INSERT INTO Test (uname, num) VALUES ('Jen', 24);
INSERT INTO Test (uname, num) VALUES ('Jen', 25);

SELECT uname
    , CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
FROM (
    SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
    FROM dbo.Test
    ) t
GROUP BY uname, groupnum;

Results:

Here is the sample code provided so kindly by @AaronBertrand

;WITH cte as (
    SELECT uname
        , min(num) As MinNum
        , CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
    FROM (
        SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
        FROM dbo.Test
        ) t
    GROUP BY uname, groupnum
    )
SELECT DISTINCT uname
    , STUFF((
        (
            SELECT ', ' + NumRange
            FROM cte as cte1
            WHERE cte.uname = cte1.uname
            ORDER BY cte1.MinNum
            FOR XML PATH, TYPE
        ).value('.[1]','VARCHAR(255)')
    ), 1, 2, '') as NumRange
FROM cte;

The results:

STUFF is used here to remove the leading , from the results.  It does that by replacing the first two leading characters in the concatenated NumRange with a zero-length string ''.


This is a rather nasty bug in MySQL 5.7 that I have experienced outside of RDS 2 months back. There is a strange mutexing behavior involving the act of purging binary logs.
My workaround involved manually deleting some of the binary logs in the OS as listed in bin-log.index and executing PURGE BINARY LOGS BEFORE CURDATE() + INTERVAL 2 HOUR; (Nightly purge every 2AM). It sped up it up some because it had less files in the OS to examine. When it came to the actual binlogs still in existence, running PURGE BINARY LOGS ... simply dragged. That's why I did manual smoke-and-mirrors stuff.
YOUR ACTUAL SITUATION
In your particular instance, you are using RDS. Since you cannot go to the OS, there is nothing else you can do to the binlogs because max_binlog_size cannot be changed in an RDS Parameter Group. You could change the max_binlog_cache_size if you have large or many transactions that need to cached ahead of its writing to the binlogs, but this may or may not help depending on how frequent you write in your app.
RECOMMENDATION
This issue does not exist in MySQL 5.6.
You may have to migrate your DB back to MySQL 5.6.27 RDS.
EPILOGUE
From my experience, I was victimized by this bug in MySQL 5.7.12. You informed me that the bug was addressed in MySQL 5.7.14. I wish I had upgraded like I wanted. Since your situation came up MySQL 5.7.11, I knew someone besides me noticed this problem. Go for your upgrade to 5.7.16 ASAP. Have a good day !!!


Table Value Constructor as Procedure Parameter

No, you must create a table variable, insert the records into that table variable, then pass the table variable to the procedure.

The way that people used to do what you are describing is with pipe delimited text strings. For example, passing '1|Tyler|Smith|2|Jack|Blade|3|Someother|Guy' and then having the stored procedure unpack it. But that methodology has completely fallen out of favor compared to using table valued parameters.


Generate CSV Email For Each UserID In Table

The following will perform an action similar to a FOREACH cycle:

CREATE TABLE #TableOne (
    UserId VARCHAR(5),
    SalesNo INT,
    DateSale DATE,
    MarkMet BIT);

INSERT INTO #TableOne (
    UserId,
    SalesNo,
    DateSale,
    MarkMet) VALUES
('ec12', 22, '2017-01-01', 1),
('ec12', 4,  '2017-01-02', 0),
('ec12', 3,  '2017-01-03', 0),
('er11', 30, '2017-01-01', 1),
('er11', 22, '2017-01-02', 1),
('er11', 33, '2017-01-03', 1),
('ss33', 40, '2017-01-02', 1),
('ss33', 5,  '2017-01-10', 0);

DECLARE @RowCount INT = (SELECT DISTINCT COUNT(*) UserId FROM #TableOne);
DECLARE @i INT = 1;
DECLARE @UserId VARCHAR(5);
DECLARE @Temp TABLE (id INT, userid VARCHAR(5));

INSERT INTO @Temp (id, userid)
SELECT
    ROW_NUMBER() OVER(ORDER BY t.UserId) AS Id
    ,t.UserId
FROM (
    SELECT DISTINCT
        UserId
    FROM #TableOne) t;

WHILE (@i <= @RowCount)
BEGIN
    SET @UserId = (SELECT t.userid FROM @Temp t WHERE t.id = @i)

    /************************************************/
    /* Build the logic to be iterated per each user */
    /* (generate CSV, Send Mail, etc)               */
    /*                                              */
    /*    eg. SELECT *                              */
    /*        FROM #TableOne                        */
    /*        WHERE UserId = @UserId                */
    /*                                              */
    /************************************************/

    SET @i = @i + 1;
END

First it will create a temp table where to store each unique UserId and will assign them a pseudo-uid based on the ROW_COUNT, then it will iterate through this newly created table to get the UserId's one by one, at which point you'll be able to execute an action per record/UserId.


Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window?

Creating a relationship through the relationship pane does one thing: it creates a relationship (obviously).

The lookup wizard, by contrast, can do multiple things:

Create a relationship (identical to one created through the relationship pane)
Add a field to the table, set that field to be a combo box based on the relationship storing the foreign key (you can do this manually in table design view as well, but it's not as intuitive)
Dangerous: make that field a multi-valued field so you can store multiple keys in a single row

If you use that last option, you will run into trouble when migrating, since other databases generally don't support multi-valued fields. Also, multi-valued fields are generally a mess to deal with further on, and I highly recommend not using them.

The second option creates somewhat of a mess as well, as the data that's displayed (data from the linked record) is not equal to the data that is stored (the foreign key). This can lead to unexpected behavior when trying to query/filter/export the data (since sometimes you operate on the displayed data, and other times on the stored data), but is not really a problem when migrating it (as it just migrates the foreign keys). You might need to reapply the combo box settings after migrating, though.


This is an updated copy of my answer on StackOverflow.

If I have a function public.foo(bar text) returns text...

I create another function like this:

create or replace function test_foo() returns void as $$
begin

  perform assert_equals('stuff', foo('thing'));
  perform assert_null(foo(null));
  ...

end $$ language plpgsql;

I have some assert functions like the following. I purposely used the same names and signatures as JUnit.

CREATE OR REPLACE FUNCTION assert_equals(expected text, actual text) RETURNS void AS $$
begin
        if expected = actual or (expected is null and actual is null) then
            --do nothing
        else
            raise exception 'Assertion Error. Expected <%> but was <%>', expected, actual;
        end if;

end $$ LANGUAGE plpgsql;

I also have a function to run all tests:

CREATE OR REPLACE FUNCTION run_all_tests() RETURNS void AS $$
declare
    skip constant name[] = '{run_all_tests}'; --a list of functions to skip
    test_prefix constant name = 'test_';
    proc pg_catalog.pg_proc%rowtype;
    started timestamptz;
begin

    raise notice 'Time(m)   Name';
    for proc in select * from pg_catalog.pg_proc where proname ilike test_prefix || '%' and not proname = any(skip) order by proname loop
        started = clock_timestamp();
        execute format('select %s();', proc.proname);
        raise notice '% %()', to_char(clock_timestamp() - started, 'MI:SS:MS'), proc.proname;
    end loop;

end $$ LANGUAGE plpgsql;

If you want to not commit data, start a transaction (BEGIN) then roll it back (ROLLBACK).

Looks like you can check code coverage with https://github.com/kputnam/piggly

Also look into:

http://pgtap.org/
http://en.dklab.ru/lib/dklab_pgunit/
http://www.epictest.org/


How DB index works internally at high level?

This is an example for Db2, so a number of details will differ if you use another DBMS, but in general, it will look pretty much the same. Lets create a sample table:

create table test
( x int not null generated always as identity
, y int not null
, z int not null
);

create unique index testix on test (x);
insert into test (y,z)
with t(n,m) as (
    values (0, rand())
    union all
    select n+1, rand()
    from t where n<100000
) select 10000*m, 1000*m from t;

So, we have a table with 100000 rows. Let's examine the unique index, before we add any data we will have just the root node:

select nlevels, nleaf from syscat.indexes where indname = 'TESTIX'

NLEVELS NLEAF
1       1

NLEVELS is the height of the tree, and NLEAF are the number of leaf-pages. After adding data:

NLEVELS NLEAF
2       206

So we have a tree that in ASCII-art looks something like:

1         R
2   ... / | \ ...

The 206 leaf-pages address all data in the table. If we look at the plan for a query like:

select * from test where x = 1234

it will look like:

                Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                 1
              FETCH
              (   2)
              13.637
                 2
           /----+-----\
          1           100001
       IXSCAN   TABLE:    LELLE
       (   3)          TEST
       6.82834          Q1
          1
         |
       100001
 INDEX:    LELLE
       TESTIX
         Q1

But what information did we touch to achieve this?

select num_executions, rows_read, pool_data_l_reads, pool_data_p_reads, pool_index_l_reads, pool_index_p_reads
from sysibmadm.snapdyn_sql
where stmt_text = 'select * from test where x = 1234'

NUM_EXECUTIONS       ROWS_READ            POOL_DATA_L_READS    POOL_DATA_P_READS    POOL_INDEX_L_READS   POOL_INDEX_P_READS
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                   1                   16                   35                   12                   66                   30

ROWS_READ (16) is the number of rows (data) that we accessed for this query
POOL_DATA_L_READS (35) is the number of pages read from memory for data
POOL_DATA_P_READS (12) is the number of pages that could not be found in memory and had to be read from disk. In any normal situation, 1 logical read results in 0 or 1 physical reads.
POOL_INDEX_L_READS (66) is the number of pages read from memory for index
POOL_INDEX_P_READS (30) is the number of pages that could not be found in memory and had to be read from disk

So, even with this minimal amount of data in the table we only touch a fraction of pages (66) compared with the total number of pages (206+intermediate pages) for indexes.

In general, a read from the index will be a walk down the tree choosing a path at each new level.


These are the sparse files checkdb makes while running: https://blogs.msdn.microsoft.com/jorgepc/2010/11/25/what-are-sparse-files-and-why-should-i-care-as-sql-server-dba/


SSMS Timeout when browsing for sql servers?

The most likely reason for this is port 1434 UDP being blocked on the new network.

An in-depth analysis of how SQLBrowser messaging works can be found here: https://www.mssqltips.com/sqlservertip/2057/why-listing-all-of-your-sql-servers-does-not-always-work/
That article also lists other reasons for this issue, like the SQLBrowser service not running on the servers, but based on your description of the problem, it looks like the new network you are working on is isolated by a firewall that blocks UDP traffic on port 1434.


How to query tabular data that has thousands of columns

25k columns? Hmm, that smells like some serious denormalisation. Do you have many sets of repeating, numbered columns, perhaps something like purchase_date_1, purchase_date_2, purchase_date_3 ,, purchase_date_99 or value_january, value_february .. value_december? Maybe there are recurring structured blocks such as residential_address, delivery_address, postal_address etc.

The way to handle these is to declare a separate table for each block and put each value in its own row. The above then becomes e.g.

TABLE Purchases
  CSV_row_key
  Purchase_date
  Ordinal      -- to capture the 1..99 if required; probably should be omitted

Transforming columns to rows is sometimes referred to as an unpivot.

Declare indexes on the keys and other predicate columns. Effective indexing is a separate subject for which we'd need the precise table design and the query.

It may be possible to pre-calculate aggregate values from these normalised tables. Running a batch job after loading and writing aggregates to a further set of tables is one approach. Materialized views is another.

1.75 billion values is large but not outrageous by current standards. Any of the usual RDBMSs on reasonable hardware should be able to handle it. Unfortunately the devil is often in the detail. It is possible to defeat the best software with contorted queries and convoluted schema. To avoid that we would need much more specific information.


How can I find the Database ID of a deleted database?

You could inspect the default trace to see the database id for a deleted database, assuming the database was deleted recently enough for the drop to be captured in the trace.

This T-SQL will show you the trace records:

DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;

IF (SELECT COALESCE(OBJECT_ID('tempdb..#trctemp'), 0)) <> 0
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

SELECT tt.DatabaseID
    , tt.DatabaseName
    , tt.StartTime
    , tt.HostName
    , tt.LoginName
    , tt.ApplicationName
FROM #trctemp tt
WHERE tt.eventclass = 47
    AND tt.objecttype = 16964
ORDER BY tt.StartTime DESC;


PostgreSQL Select statement produces errors in datagrip but not pgAdmin4

This ended up being really simple and a very silly mistake, but hopefully this will help someone else.

I simply needed to select the correct database. The default is postgres. The database I had my tables in was webadmin. You can change the database when you make your connection in datagrip. The screen where I made the change looked like this


Selecting records where date range is current year minus #years

This will delete anything three years older than January 1st of this year.

DELETE table_name
WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);


Joining two tables using one to group another

SELECT a.id1, a.id2, `time`, event
FROM a
LEFT JOIN b ON a.id1 = b.id1 AND a.id2 = b.id2
            AND a.`time` BETWEEN b.stime AND b.etime
Where not exists ( select null from a A1
                          Where a1.id1=a.id1 and a1.id2=a.id2
                          AND a1.`time` BETWEEN b.stime AND b.etime
                          And a1.`time` < a.`time` )

Cfr. http://sqlfiddle.com/#!5/284fb/4

Another way to obtain these results:

with e as (select rowid R, b.* from b)
select e.*,  a.* from (
 select min( a.`time` ) T, e.R  from a,  e
  where a.`time` between e.stime and e.etime
      and a.id1 = e.id1
      and a.id2 = e.id2
  group by e.R
 ) M, a, e
 where e.R = M.R
   and a.id1 = e.id1
   and a.id2 = e.id2
   and a.`time` = M.T;

With your current schema, that contains a serious flaw, there is no way to obtain your result in a timely fashion.  With some simple modifications, you can get it in 21 seconds.

Modify your tables as follows:

CREATE TABLE a (
 `time`        REAL       NOT NULL,
  idA number,
  id1      CHAR        NOT NULL,
  id2       CHAR        NOT NULL,
  event    REAL);
CREATE TABLE IF NOT EXISTS "b"(
  id1 TEXT,
  id2 TEXT,
  stime REAL,
  etime REAL,
  idB number);
CREATE UNIQUE INDEX pk_b on b(idB);
CREATE INDEX i_b1 on b(id1,id2);
CREATE INDEX i_a1 on a(idA);

I've given b a primary key.  a.idA contains a reference (foreign key) to b.  This update took quite some time.   Now the following query finishes in 21 seconds.

select b.*, a.event, a.`time`
  from b, a
  where a.idA = b.idB
    and not exists (select null from a a1
                     where a1.idA = b.idB
                       and a1.`time` < a.`time`)

--EQP-- 0,0,1,SCAN TABLE a
--EQP-- 0,1,0,SEARCH TABLE b USING INDEX pk_b (idB=?)
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE a AS a1 USING INDEX i_a1 (idA=?)
c2|d2|834.0|1395.0|5|49.407297300706|834.0
c1|d2|1750.0|5332.0|1|49.9163756294503|1750.0
c1|d1|2088.0|5330.0|2|49.6802055002045|2088.0
....
c2|d1|9994707.0|9998630.0|5986|49.4294519703533|9994707.0
c2|d2|9998164.0|10000000.0|5988|48.9248418596976|9998164.0
c1|d1|9998324.0|9999377.0|5990|17.4151947643984|9998324.0
c1|d2|9998791.0|10000000.0|5987|48.9222722695804|9998791.0
c2|d1|9999744.0|10000000.0|5989|49.8495713740755|9999744.0
Run Time: real 21.612 user 21.377030 sys 0.207799
sqlite> select count(*) from a;
11313047
sqlite> select count(*) from b;
5990


I'd try this (which I don't fully understand, but should be equivalent):

CREATE or REPLACE FUNCTION pointscanner
(
    IN _source integer,
    IN _target integer,
    IN _k integer,
    IN _directed boolean,
    IN _quant integer
)
RETURNS table(way geometry, name text, distance double precision, tags hstore, "operator" text,
        admin_level text, z_order integer) AS $$
BEGIN
    RETURN QUERY
    WITH rt AS
    (
        SELECT
            *
        FROM
            route_agg(_source, _target, _k, _directed)
    ),
    xt AS
    (
         SELECT
             ST_Extent(ST_Union(geom)) AS extent
         FROM
             rt
    )
    SELECT
        pt.way,
        pt.name,
        ST_Distance( rt.geom, ST_Transform(pt.way,4326) ) as distance,
        pt.tags,
        pt.operator,
        pt.admin_level,
        pt.z_order
    FROM
        planet_osm_point pt
        CROSS JOIN rt
        CROSS JOIN xt
    WHERE
        xt.extent && ST_Transform(pt.way, 4326)
    ORDER BY
        distance ASC
    LIMIT
        _quant;
END; $$
LANGUAGE 'plpgsql' VOLATILE;

NOTE: it would be helpful to have the definition of the planet_osm_point table, and of route_agg.


Partition Question

You could partition by area and area_zone. As a result your CREATE TABLE statement would look something like this.

create table LogEvent
(
    id bigint auto_increment,
    area smallint unsigned not null,
    area_zone tinyint unsigned not null,
    value1 float not null,
    value2 float not null,
    reason char(2) not null,
    primary key(id,area,area_zone)
)
PARTITION BY RANGE COLUMNS (area,area_zone)
(
  PARTITION p01 VALUES LESS THAN (10,20),
  PARTITION p02 VALUES LESS THAN (20,30),
  PARTITION p03 VALUES LESS THAN (30,40),
  PARTITION p04 VALUES LESS THAN (40,MAXVALUE)
);

You will notice that I modified your PRIMARY KEY definition as a PRIMARY KEY must include all columns in the table's partitionning function.  There is also some learning you will have to do to understand in what partition you're rows would be stored.  Here is a link that explains how and where rows would be inserted based on the way you define you're partitionning scheme.

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

The data types you choose for area and area_zone will depend on how unique both will be. As you said you are expecting millions of rows, the question is, will there be millions of different areas and area_zones. The number of unique areas and area_zones could not go past the limits defined by smallint and tinyint.

Also keep in mind that some partitions will be much larger than others. If you partitioned by ID, the partitions would eventually be about the same size.  Using area and area_zone, some may be much larger than others.

InnoDB versus MyISAM

Rolando answers your question about MyISAM versus InnoDB quite nicely in this post:

Choosing MyISAM over InnoDB for these project requirements; and long term options

What columns to Index

For question 3, knowing where to add an index depends on the cardinality/selectivity of the column you wish to index. If a column is very dense, in otherwords it has a large number of duplicate values, then it makes less sense to create an index for that column alone. In your situation, you have two columns, area and area_zone.  Area alone may not be selective enough as there may be many area_zones per area.  I think you could create a composite index on both columns to obtain a better cardinality and thus a more useful index.  Keep in mind that when you add an index, there may be an impact on performance and storage as MySQL must store the data in the index you created as well as in each column being indexed.  Here is a MySQL post on numeric data types to give you an idea of the amount of space each type takes.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

CREATE INDEX idx_area_area_zone ON LogEvent(area, area_zone);

Here is a great article on composite indexes that you may find useful:

http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/

Memory Attribution Question

To answer your RAM question, you could start with a relatively amount 2GB and a small InnoDB buffer pool size example (512Mb) since you are saying that SELECTs won't happen often.  Once your server is in production, you can calculate how much of the buffer pool MySQL is actually using.

To help you determine that value, I am quoting Rolando from one of his previous posts:

"What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server." Once your server is in production, you will be able to calculate what percent of your InnoDB buffer pool is actually in use. He provides formulas to help identify that percentage.

What to set innodb_buffer_pool and why..?

If you choose to use MyISAM for your LogEvent table, then you would give that memory to other variables such as key_buffer and join_buffer_size.  The ratio of interest as far as memory usage is concerned, would be your Key Cache Hit Ratio

  1 - (Key_reads/Key_read_requests)

Which should be as close as possible to 100%


Parameter sniffing = On with Parameterization = forced. Which takes precedence?

These do not conflict. Parameter sniffing is the process that uses the parameter values when building a plan. Forced parameterization turns literals into parameters in queries.

So having both on will turn literals into parameters, but not use those values when estimating cardinality.

More on parameter sniffing

More on forced parameterization


One problem with your trigger is that it is potentially updating all rows in the table, even if only one row was updated or inserted. If someone accidentally sets the values to NULL, they'll then get updated to the current time, the next time the trigger runs. Does this make sense for your application? I would think it makes more sense to leave past values alone (deal with those separately, though it seems doubtful any still exist), and only bother updating rows that are currently impacted directly by the trigger. You do that by joining to the inserted pseudo-table:

UPDATE sr SET saledate = GETDATE()
  FROM dbo.salerecords AS sr
  INNER JOIN inserted AS i
  ON sr.key_column = i.key_column -- you'll need to update this
  WHERE sr.saledate IS NULL;

That said, I agree with @SoleDBAGuy, who suggested a default constraint. Here is how you could implement that:

ALTER TABLE dbo.salerecords
  ADD CONSTRAINT df_saledate DEFAULT (GETDATE()) FOR saledate;
GO
ALTER TABLE dbo.salerecords
  ALTER COLUMN df_saledate datetime NOT NULL;

And then drop the trigger. Note that the second command may fail; if you have null values in there already, it's hard to justify just giving them "right now" as their sale date, when they could potentially be years old. So you can look at those individually, or just apply some date to them all, like:

UPDATE dbo.salerecords SET saledate = GETDATE();
-- or
UPDATE dbo.salerecords SET saledate = '19000101'; -- beware magic number

Finally, please always use the schema prefix.


How to make DDL changes to large tables and cause minimum server and object contention?

The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a SELECT...INTO of the index fields into a temp table with an ORDER BY of the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not fully identified to date).

In addition, if you can add an otherwise unused high-performance drive or drive array (SSD preferred) and create the index on a filegroup on that drive array, this can greatly improve the index creation performance.

For modifying an existing index, use the above tips to create a new index, then DROP the existing index after examining index usage on the new index for a time. (Why? I've modified some indexes in ways that should have increased performance, only to find overall performance degraded. When I create a new index, I can monitor if SQL Server wants to use it, and/or check performance by forcing the index.)

Of course, since you have SQL Server 2016 EE use the ONLINE=ON option. This minimizes locking when creating indexes, and is best for non-clustered index creation or alteration. Clustered indexes will reorganize the whole table, so it will take some time no matter what happens.


How to verify data integrity of data folders after data recovery in MySQL and PostgreSQL?

As far as I know, the only way in PostgreSQL is to run something like

pg_dumpall >/dev/null

which will scream if it encounters table corruption.

However, that won't test index integrity. For that, PostgreSQL v11 has the amcheck extension.


How to get the information of the optimizer of sql server?

You should check out the SQL Management Studio

It will let you see many things about your query. I know it will show you the cost of executing the query. It will also show you the cost of each step of the execution.

http://msdn.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms191194(v=sql.105).aspx


copy tables from one database to another

Consider the source database name is databaseA and destination  is databaseB.

Step-1. Take dump of tables to be loaded to new host:

mysqldump -uroot -pPASSWORD --routines --skip-drop-tables --single-transaction --master-data=2 databaseA > databaseA.sql

Notes:

--skip-drop-tables is used to avoid accidental override of tables from  databaseA to databaseB.
If there is no replication and active writes, you can ignore --single-transaction and --master-data options. (Harmless to keep them as well.)
If you  have only a few of the tables to be dumped them  you  can specify list of tables (space delimited) after database name in above command as follows:

mysqldump -uroot -pPASSWORD --routines --skip-drop-tables --single-transaction --master-data=2 databaseA table1 table2 ... > databaseA.sql

Step-2. Backup the destination database: (Just in case something goes wrong)

mysqldump -uroot -pPASSWORD --routines --databases databaseB > databaseB.sql

Step-3. Load the database:

mysql -uroot -p databaseB -hHOSTNAME < databaseA.sql

Notes:
- Here HOSTNAME is the hostname/ipaddress of the destination

Just to add, if your databases are on same machine, (which is not the case here, but still saying) you can use RENAME operations to move tables:

rename table databaseA.table1 to databaseB.table1;

You can generate these SQLs  from information_schema database and source it to mysql to quick action.


Use a Context namespace in a multi-tenant database?

You could encapsulate the logic into the packages themselves:

SQL> CREATE OR REPLACE PACKAGE pkg IS
  2     g_context VARCHAR2(30);
  3     FUNCTION  get_ctx(p_variable VARCHAR2) RETURN VARCHAR2;
  4     -- set_ctx may be private of course
  5     PROCEDURE set_ctx(p_variable VARCHAR2, p_value VARCHAR2);
  6  END;
  7  /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
  2
  3     FUNCTION  get_ctx(p_variable VARCHAR2) RETURN VARCHAR2 IS
  4     BEGIN
  5        RETURN sys_context(g_context, p_variable);
  6     END get_ctx;
  7
  8     PROCEDURE set_ctx(p_variable VARCHAR2, p_value VARCHAR2) IS
  9     BEGIN
 10        dbms_session.set_context(g_context, p_variable, p_value);
 11     END set_ctx;
 12
 13  -- set ctx dynamically
 14  BEGIN
 15     g_context := 'MYCTX_'||sys_context('userenv', 'current_schema');
 16  END;
 17  /

Package body created

Let's create the context and call it :

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'CREATE CONTEXT ' || pkg.g_context || ' USING PKG';
  3  END;
  4  /

PL/SQL procedure successfully completed

SQL> exec pkg.set_ctx ('a', '123');

PL/SQL procedure successfully completed

SQL> select pkg.get_ctx('a') from dual;

PKG.GET_CTX('A')
--------------------------------------------------------------------------------
123


Changing location of binlog prevents starting up

I finally figured it out: MariaDB keeps an "index" file, fortunately ASCII, that you need to edit: mariadb-bin.index located in the binlog directory (or wherever your log_bin_index configuration points at). This file lists all the binlog files with their full paths. This is unfortunate as the paths should be relative to the log_bin configuration setting. Example:

$ head mariadb-bin.index
/<binlog path>/mariadb-bin.000001
/<binlog path>/mariadb-bin.000002


It probably seems that following is in the stop words list.
you should check the stop words to see is that the case.


foreign key relationships and auto-incrementing in Sql Server

You can issue the command

SET IDENTITY_INSERT tablename ON

before inserting values into a table and call

SET IDENTITY_INSERT tablename OFF

afterwards.

According to the documentation for SET IDENTITY_INSERT (Transact-SQL):

  At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

and

  If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

To keep the foreign key relationship simply import the main table (Foo) before the details table (Bar).


Is it possible to replicate data changes of a database with triggers using binary logs?

It possible in theory with ROW binary logs, in this case each changes of tables will be write to the Log.

at this moment several companies declare this solution (plus many on GitHub):

MariaDB MaxScale CDC (Kafka) - http://126kr.com/article/5btzr54y53h
StreamSets - https://streamsets.com/products/sdc/  and https://streamsets.com/documentation/datacollector/latest/help/#Origins/MySQLBinaryLog.html#task_qbt_kyh_xx
Yelp - https://engineeringblog.yelp.com/2016/08/streaming-mysql-tables-in-real-time-to-kafka.html

You can search some other. Unfortunately at least my experience show - this products far from "ready to use" state. But this is possible direction for feature research.


How to export user with tables and tablespace oracle

Usual way of doing that is data pump. As you want to copy user, you'd use the schema mode.

This example is based on 11gXE. If you use another database version, you might need to adjust it.

As data pump requires existence of a directory (Oracle object), you'd have to create it in both source and target database - connect as SYS and run something like this:

SQL> create directory my_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory my_dir to scott;

Grant succeeded.

SQL>

Physical directory (c:\temp in my example) resides on a database server; as I run 11gXE, my laptop is the database server as well.

When exporting, the export (dmp) file, as well as log file, will be created in that directory. The same goes for import - you'll have to put the dmp file into it.

In both databases, I'm creating a user which will perform export/import and I'll grant it DBA role so that - during import - it also creates user. Otherwise, it'll fail. Also, I'm granting datapump_exp_full_database, datapump_imp_full_database so that it export privileges user already has (otherwise, everything will be exported, but newly imported user will lack in any privilege, even create session).

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> create user importer identified by imp
  2  default tablespace users
  3  temporary tablespace temp
  4  profile default
  5  quota unlimited on users;

User created.

SQL> grant dba, datapump_exp_full_database, datapump_imp_full_database to importer;

Grant succeeded.

At the operating system command prompt, run EXPDP to perform export:

expdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_e.log

It looks like this:

c:\Temp>expdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_e.log

Export: Release 11.2.0.2.0 - Production on ╚et Ruj 6 21:13:55 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "IMPORTER"."SYS_EXPORT_SCHEMA_01":  importer/********@xe schemas=scott directory=my_dir dum
pfile=scotte.dmp logfile=scott_e.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."BONUS"                             6.210 KB       1 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."EMP_LOG"                               0 KB       0 rows
. . exported "SCOTT"."TABLE01"                               0 KB       0 rows
. . exported "SCOTT"."TABLE02"                               0 KB       0 rows
. . exported "SCOTT"."TABLE03"                               0 KB       0 rows
Master table "IMPORTER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for IMPORTER.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\SCOTTE.DMP
Job "IMPORTER"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:14:18

c:\Temp>

Copy dmp file to target computer and perform import.

I'll just drop user Scott (as I don't have access to another database/server right now):

SQL> show user
USER is "SYS"
SQL> drop user scott cascade;

User dropped.

SQL>

OK, let's import it - operating system command prompt again; command is almost equal to expdp. If tablespace names are different in the source and target databases, you'll need the REMAP_TABLESPACES parameter.

impdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_i.log

Import looks like this:

c:\Temp>impdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_i.log

Import: Release 11.2.0.2.0 - Production on ╚et Ruj 6 21:14:50 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "IMPORTER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "IMPORTER"."SYS_IMPORT_SCHEMA_01":  importer/********@xe schemas=scott directory=my_dir dum
pfile=scotte.dmp logfile=scott_i.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39082: Object type TYPE:"SCOTT"."IBS_S_DOCUMENT_T_1" created with compilation warnings
ORA-39082: Object type TYPE:"SCOTT"."IBS_S_DOCUMENT_T_1" created with compilation warnings
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."BONUS"                             6.210 KB       1 rows
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."EMP_LOG"                               0 KB       0 rows
. . imported "SCOTT"."TABLE01"                               0 KB       0 rows
. . imported "SCOTT"."TABLE02"                               0 KB       0 rows
. . imported "SCOTT"."TABLE03"                               0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."A1_P_LOG" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."OVERDUETODAY" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."PROC" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."PROC_SECOND" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."P_MATCH" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."P_SURVEY" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."REPORTS_ITEM_INFO" created with compilation warnings

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"SCOTT"."MY_JOIN_TEST_SP_PACKAGE_3" created with compilation war
nings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"SCOTT"."TRG_BU_DEPT" created with compilation warnings
ORA-39082: Object type TRIGGER:"SCOTT"."TRG_BU_DEPT" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "IMPORTER"."SYS_IMPORT_SCHEMA_01" completed with 12 error(s) at 21:14:55

c:\Temp>

Certain objects were imported in invalid state; no wonder, I'm using Scott for testing purposes, creating & dropping objects all the time. In your case, import will probably look prettier.

Let's try to connect to newly imported Scott:

c:\Temp>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on ╚et Ruj 6 21:30:44 2018

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
DEPT_EXT                       TABLE
EMP                            TABLE
EMP_LOG                        TABLE
SALGRADE                       TABLE
TABLE01                        TABLE
TABLE02                        TABLE
TABLE03                        TABLE

9 rows selected.

SQL> create table test (id number);

Table created.

SQL>

Seems to be OK. Give it a try; you can't spoil anything. If anything goes wrong at the target database, just drop the imported user and try again.

I suggest you read data pump documentation. Seeing an example is nice, but - you'd better understand what's going on.

Good luck!


Index: Does 0% fragmentation imply a B-tree?

The "B" in B-tree doesn't necessarily stand for "balanced". It depends on who you ask. See for instance this. As often is the case, terminology degrades (over time).

All SQL Server "row indexes" (as opposed to other types of indexes in SQL Server like hash, columnstore, full-text, XML (to some extent) and geospatial) are B-tree indexes. They are always "balanced" in the sense that you have the same depth (number of pages) from the root to all leaf pages.

This has no relationship with any of the two types of fragmentation that we usually talk about in the SQL Server world: external (jump back and forth when following the linked list) or internal (non-full pages).

One can argue that a clustered index in SQL Server should be considered a B+ tree, since the leaf "has the data", but again I doubt that you will find and authoritative reference for how the terms are used.


Oracle 10g: query takes long time and adding index does not help

The optimizer may still look at the table scan to be a good idea.  To insure the optimizer uses the index, have you tried adding a hint to your query to insure the optimizer uses the index?  And echoing the earlier response, more information to work with would be nice.


Modification to GEQO (Genetic Query Optimization) of PostgreSQL

One way you can do this without the need to mess around with GEKO is by using CTE.

CTE are optimisation barriers, hence you could wrap the joins inside CTEs in the order you want and PG will be forced to do so.

For example if we want to force the DB to first join t1 with t2 and only then with t4 we could run something like:

explain
with j1 as (select *,t1.c4 as t1c4 from t1 join t2 on (t1.c2=t2.id))
    ,j2 as (select * from j1 join t4 on (t1c4=t4.id))
select * from j2;

This will result in:

                                  QUERY PLAN
-------------------------------------------------------------------------------
CTE Scan on j2  (cost=51485.00..67785.00 rows=815000 width=64)
CTE j1
 ->  Hash Join  (cost=3473.00..14521.00 rows=815000 width=40)
       Hash Cond: (t2.id = t1.c2)
       ->  Seq Scan on t2  (cost=0.00..26.30 rows=1630 width=20)
       ->  Hash  (cost=1637.00..1637.00 rows=100000 width=20)
             ->  Seq Scan on t1  (cost=0.00..1637.00 rows=100000 width=20)
CTE j2
 ->  Hash Join  (cost=289.00..36964.00 rows=815000 width=64)
       Hash Cond: (j1.t1c4 = t4.id)
       ->  CTE Scan on j1  (cost=0.00..16300.00 rows=815000 width=44)
       ->  Hash  (cost=164.00..164.00 rows=10000 width=20)
             ->  Seq Scan on t4  (cost=0.00..164.00 rows=10000 width=20)
(13 rows)

This is just an example, you can change it around as needed - in any case PG can't change the order between the different CTEs.

Hope it helps :)


How to prevent a highly dynamic PL-SQL query from injection using bind variables

I'm not sure there is a good answer. In general, string concatenation to build your SQL is always a bad idea and should be avoided whenever possible. That said, it would be difficult to avoid in your case except for replacing user_id references with bind variables. Other than that, you will have to come up with some custom logic to validate the content of qry1 and qry2 to ensure they only contain what you expect them to contain. Perhaps a length limitation, or a check of keywords or column names? Either that, or replace qry1 and qry2 with explicit values for the columns in each table, including wildcards, then turn those into bind variables.
As long as you have to accept actual SQL code snippets as your input, I would recommend something like this:
create or replace procedure Injection_test(p_qry1         varchar2,
                                           p_qry2         varchar2,
                                           p_user_id      varchar2)
is
   final_query varchar2(1000) := '';
begin

   -- put some logic here to validate the content of p_qry1 and p_qry2 to make sure
   -- they don't contain keywords like "select", "update", "delete", "1=1", ";" or
   -- equivalents, and/or that they do contain things you expect to see. Raise an
   -- application error anything unexpected is found

   if p_qry1 is not null
   then
      final_query := 'select c_num from z_test_a where userid = :user_id and '
                     || qry1;
      if p_qry2 is not null then
         final_query := final_query || ' intersect ';
      end if;
   end if;

   if p_qry2 is not null then
      final_query := final_query
                     || 'select c_num from z_test_b where userid = :user_id and '
                     || qry2;
   end if;

   if final_query is not null
   then
      final_query := 'insert into Z_final_result (c_num) ' || final_query;
      --dbms_output.put_line(final_query);

      if instr(final_query,'intersect') > 1
      then
         execute immediate final_query using p_user_id, p_user_id;
      else
         execute immediate final_query using p_user_id;
      end if;

      commit;
   end if;
end;


What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

This looks like a join table on Items and Users.  You may find putting ID columns these two tables and using them in the join table works well.  Feed would be a parent for Items, and you could make the primary key if Items a compound key including Feed Id.

For the table in question you likely want indexes on:

User_Id, Feed_Id, Item_Id (Primary key searchable for user's data)
Feed_Id, Item_Id  (Foreign key from Items compound key)
User_Id  (Foreign key from Users, optional as included in the first index)


Should master, model and msdb reside on drives with 64k AU size? Is it necessary? Recommended?

In general, make a standard and use it. To directly answer your question, if all other filesystems are going to be formatted for 64KB, then why not stick to it - though, regardless, I'd go with the storage vendor's recommendation. Let's look per DB:

Master: In general it doesn't have much "extra" data put in it and shouldn't really be growing or heavily transactional. Allocating 64k or 4k isn't going to make much of a difference.
Model: There should be no transactional activity happening in this database, in general, by default. There might be some additional objects added but this should be similar to master where it's largely static.
MSDB: This database is actually used more than both of the previous database. It holds things such as Jobs, Mail, Backup Information, etc. This database could grow to be very large when not properly looked after.

Without going into a large amount of technical detail, you should choose whatever the storage behind the virtualization layer likes as it's preferred or most optimized transfer size, that's what I'd use for my allocation unit size. This is because it matches what the storage layer likes the most and will be the most optimized.

If you want to get a little more technical, uniform extents are 8 contiguous pages which just happens to be 64k. This happens to nicely line up with a 64k allocation unit size. The largest possible log block can be 60k, which again fits nicely... but generally is much less, which results in space waste, and could not be the optimized size that the storage layer likes.

The allocation unit size is just the smallest possible fragment given to a file. Thus an AU of 64k means a file, holding just a single bit would still have an on disk size of 64k. Normally, larger AU sizes result in lower on disk fragmentation which may or may not matter, depending on the storage array's physical storage medium. In an example of this, SSDs and NVME hold internal translation tables for pages (not the same as DB pages) for wear leveling and write amplification. In this case, there is no idea of where the physical data will be saved, and thus may or may not be physically sequential.

Thus, again, I'd go with the value specified by the storage layer vendor. If you don't happen to know what that is, 4k and 64k are both well supported (though larger AU sizes may not support certain NTFS technologies, which shouldn't be used with SQL Server anyway - but for completeness I added it).

Really, what will make the largest difference is whether the storage layer has mixed 512 and 512e/h storage representations (which is the physical block size). This will cause more issues that allocation unit size of the filesystem.

  If necessary, I would like to be able to recover a VM from a snapshot that restores OS, SQL executables and system databases. Then restore application databases from SQL backups.

In a highly unrelated note, I'd double and triple check that this meets your disaster recovery or business continuity requirements/SLAs. Allocation Unit sizes should not impact this.


Jobs view is not visible to read-only account users

As @Jeffrey pointed out, ALL_* view is not useful in your case. You should use DBA_* views because they show the records for all owners of the job objects.

The following sequence works just fine for me:

$ sqlplus /nolog

SQL> connect / as sysdba
Connected.

SQL> select distinct owner from dba_scheduler_job_run_details;

OWNER
------------------------------
EXFSYS
SPONGEBOB
SYS

SQL> create or replace view sys.jcaf as
  2  select * from dba_scheduler_job_run_details
  3  where owner = 'EXFSYS';
View created.

SQL> grant select on sys.jcaf to spongebob;
Grant succeeded.

SQL> create public synonym jcaf_v for sys.jcaf;
Synonym created.

SQL> connect spongebob/passwd
Connected.

SQL> select distinct owner from jcaf_v;

OWNER
------------------------------
EXFSYS


Why is the schema name necessary here and how to compile without it

Indeed relation is the name of a table. It must exist in the schema where you run this code (= belong to the user you are connected as).
Later you say that admin.relation works fine. This means that the table called relation belongs to a user called admin.
So ...

Either compile and run your code as admin (= connect as admin).
Or use fully qualified names and run the code from another user. Provided that user has the proper right (SELECT privilege) on table relation

Actually there is a third option that avoids the need to fully qualify the table name: have user admin create a public synonym to the table, like this:
create public synonym relation for relation;
or have a DBA do it:
create public synonym relation for admin.relation;
That way all users (provided they have the right to read that table) can refer to it by its name, without needing to fully qualify it with the name of the owner.


After log shipping, I activate failover with a RESTORE. What happens when the next log shipping job runs?

I will run RESTORE DATABASE MyDatabase WITH RECOVERY to activate it, but then what happens next time the job runs to do log shipping? Will it fail? Should I disable the job manually? Will it overwrite my database and set it back to Restoring... ?

Yes they will run. Logshipping is simple copy and movement of logs so when you bring secondary database online it just brings the database online it does not affects any job running. So you should disable all backup,copy,restore and alerts jobs.
When next time on primary, backup job wil run it will succeed even copy job will succeed on secondary but restore job would fail on secondary as database is online and logs cannot be restored more over log chain would be broken.


How to add a new target in Oracle Enterprise Manager 12 C

Well this task i am afraid it can not be described here, i mean it could but is to much to write and i will not re-invent the wheel.:)
 So i recommend you follow this article How to add a new target on OEM , i wrote this article with this purpose(help guys like you ! kkkkk ).
 i might get a down vote for my short answer ! but for those intrested  -- fell free to read the article !! And thare a lot's of them on Oracle Enterprise Manager 12 C .


What is a Sequence?

  A sequence is a user-defined schema bound object that generates a
  sequence of numeric values according to the specification with which
  the sequence was created.

MSDN on Sequence definition reference.

Ok, it's a schema bound user defined object. What does SQL say about how many User Defined Objects we can have?

  Database objects include objects such as tables, views, stored
  procedures, user-defined functions, triggers, rules, defaults, and
  constraints. The sum of the number of all objects in a database cannot
  exceed 2,147,483,647.

MSDN on maximum UDF reference.

Summary answer: You can have 2,147,483,647 objects in a database. One of those objects referenced would be Sequence Objects which Sequences fall into that category. This is not explicitly stated, but it can be contrived from the wording "... the sum of the number of all objects in a database cannot exceed 2,147,483,647."


You plan to have queries of the type:

SELECT * FROM cities WHERE slug = 'new-york';

So, yes, you should definitely have an index on (slug).

Should it be UNIQUE index or a UNIQUE constraint?

That depends on whether you allow to have two rows in the table with same "slug". I think this is unlikely to be a requirement, as "slugs" are usually short terms to uniquely identify a thing (user, city, country, web page, etc).

So, yes, the index should be UNIQUE. MySQL makes no difference between UNIQUE INDEX and UNIQUE CONSTRAINT. I prefer to use the UNIQUE constraint syntax (which adds a UNIQUE index):

ALTER TABLE cities
    ADD CONSTRAINT cities_slug_uq     -- constraint name
        UNIQUE cities_sluq_uqx        -- index name
        (slug) ;


How to figure out what a DTSRun command is doing

You ave to use /!X /!C after the DTS command.

/!X ==> Do not run the package

/!C ==> copy the results to clipboard

To avoid this, you can create a  SQL Agent Job and choose CmdExec as a step type with below commandline:

 DTSRun /N"your package name" /S<servername> /E   --- for trusted connection

OR

DTSRun /N"Your package Name" /S<ServerName> /U<UserName> /P<Password>   --- for SQL authentication

As a sidenote, its time for migration to something supported by Microsoft sql 2005, sql 2008 R2 or 2012 along with migrating DTS packages to SSIS.

Refer to :

How to identify which DTS Package is being called by scheduled job
dtsrun Utility


No, you should not include a column make_id in the car_model table, it is implicitly defined by the series_id.  If you need to see the make details you could make a view that looks like your un-normalized table.

No this design cannot be normalised further.

To force the year ranges to be non-overlapping you could add a trigger (as MySql does not support check constraints) to ensure that start_year >= max(end_year) and end_year >= start_year.

The trigger would look something like this:

create trigger trg_car_model_date_range_unique before insert on car_model
for each row
begin
    if new.start_year < (select max(end_year) from car_model) then
        signal sqlstate '45000' set message_text = 'Date range is not unique';
    end if;
    if new.end_year < new.start_year then
        signal sqlstate '45000' set message_text = 'Date range is invalid';
    end if;
end


Sending an async notification to another database in PostgreSQL

If the other database has established a connection to the database to which you are connected and has issued a LISTEN, then you send a NOTIFY to it the same way you would send one to anything else using the NOTIFY/LISTEN interface.

If the other database has not volunteered to LISTEN, then NOTIFY is not the right tool for the job.  You could use dblink or FDW to insert records into a queue table on the other database.  Or, insert them into a local queue table, and have the other database fetch them when it wants to.


postgres: what is the archive_command and when to use it

The archive_command is useful when:

You want to do point-in-time recovery from a base backup to some past state in case of disaster, like accidentally dropping a database.
You're using streaming replication without a replication slot. In this case if the replica gets behind by more than wal_keep_segments and hot_standby_feedback is off or the replica gets disconnected temporarily, the master will remove resources it needs and it'll break. If you have WAL archiving in use, the replica can fall back to using its restore_command to fetch WAL to catch up and recover. Otherwise you'd have to re-create it from a new pg_basebackup.


Plug a PDB in a new CDB without XML manifest

you can recover the XML file of a not unplugged PDB.

COPY the PDB folder into the CDB folder on the new machine.
Connect as SYSDBA to the new CDB (I use SQLDeveloper)
EXECUTE the package DBMS_PDB.RECOVER

it uses 3 parameters:

/path/to/new/file.xml
NAME_OF_PDB (you can use the same as OLD PDB or a NEW ONE)
/path/to/pdb/folder/

EXAMPLE:

BEGIN
  DBMS_PDB.RECOVER (
  pdb_descr_file => '/path/to/new/file.xml.xml',
  pdb_name => 'NAME_OF_PDB',
  filenames => '/path/to/pdb/folder/'
  );
END;
/

THEN, you can plug the database using:

CREATE PLUGGABLE DATABASE NAME_OF_PDB USING '/path/to/new/file.xml' NOCOPY TEMPFILE REUSE;

THEN open the PDB:

ALTER PLUGGABLE DATABASE NAME_OF_PDB OPEN FORCE;

IF PDB DON'T OPEN, check PDB_VIOLATIONS

SELECT * FROM PDB_PLUG_IN_VIOLATIONS;

SOURCE
http://www.oraclebuffer.com/oracle/oracle-12c-lost-your-pdbs-xml-manifest-file-heres-how-you-can-recover-it/


Online Re-Indexing In SQL Server 2005/2008 Standard

The option is in SSMS but it will not work. SSMS is not Edition smart so to speak, they did not make it smart enough to hide options specific to the Edition you are running.

If you try to use it, I believe it just gives you a message relating to "this is an Enterprise only feature"; or something close to it.