What is the difference between MySQL VARCHAR and TEXT data types?

divided linked to some info that explains the basic issue (there's performance differences), but it's not simple enough to say that one's always better than the other.  (otherwise, there'd be no reason to have both.)  Also, in MyISM, the 64k max size for VARCHAR isn't per field -- it's per record.

Basically, there's 4 ways to store strings in database records:

fixed length
C-style strings (marked with a NULL or similar character at the end of the string)
Pascal style strings (a few bytes to indicate length, then the string)
Pointers (store the string somewhere else)

MyISM uses something similar to #3 for VARCHAR, and a hybrid approach for TEXT where it stores the beginning of the string in the record, then rest of the string somewhere else.
InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

With 1&4, the stuff in the record is always the same length, so it's easier to skip over if you don't need the string, but need stuff after it.  Both #2 and #3 aren't too bad for short strings ... #2 has to keep looking for the marker, while #3 can skip ahead ... as the strings get longer, #2 gets worse for this particular use case.

If you actually need to read the string, #4 is slower, as you have to read the record, then read the string which might be stored elsewhere on the disk, depending on just how that database handles it. #1 is always pretty straightforward, and again you run into similar issues where for #2 gets worse the longer the string is, while #3 is a little worse than #2 for very small strings, but better as it gets longer.

Then there's storage requirements ... #1 is always a fixed length, so it might have bloat if most strings aren't the max length.  #2 has 1 extra byte; #3 typically has 2 extra bytes if max length = 255, 4 extra bytes if a 64k max.  #4 has the pointer length, plus the rules for #3 typically.

For the specific implementations within MySQL 5.1, the docs for MyISM state:

Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
Tables with VARCHAR columns may have fixed or dynamic row length.
The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.

While for InnoDB :

The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
For each non-NULL variable-length field, the record header contains the length of the column in one or two bytes. Two bytes will only be needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the two-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column.

...

as with so many other things when dealing with databases, if you're not sure what's best for your needs, try benchmarking it with similar data & usage, and see how they behave.


Simplified scenario:

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost`, `idrevision`),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `user1_idx` (`iduser`));

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, content`) VALUES (1, 1, 1, 'this is old content of first post');

Session1:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES
  ((SELECT max_idrevision + 1
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1),
   1, 2, 'abc xyz');

select max(idrevision) AS max_idrevision from revision where idpost=1;

The above query will fetch the value from the secondary index 'index1'.
Because idpost and idrevision is covered by index1 itself. So index1 is a covering index for this query.
It will take S lock on the row(1,1) in the index 'index1' due to repeatable read isolation level.
(Read about repeatable read isolation level).

Session2:

BEGIN;
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;

Above query will change the clustered index alone, it won't change any secondary index like 'index1' because 'content' is not part of any secondary index.

UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;

But in this case, idrevision has been changed. So it will try to update the key part of 'index1' ( because idrevision is a part of key in index1).

Why it leads to hang:

As I mentioned earlier in session 1, S row lock taken for the row (1,1) in the index1.
Now session 2 tries to take X row lock to update the row(1,1) in the same index1.
If you look lock type compatibility matrix, S and X locks are Conflicts.

Note:

During hanging, you can open one more session and give the following query to find out where it is hanging.

mysql> select * from information_schema.innodb_locks;
--------------
select * from information_schema.innodb_locks
--------------

+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id    | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 1826:8:4:2 | 1826        | X         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
| 1817:8:4:2 | 1817        | S         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+

The above analysis is based on mysql-5.6


Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails?

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?

No.

READ COMMITTED doesn't retry. Neither does SERIALIZABLE. The application is expected to retry transactions that suffer deadlocks, serialization failures, etc.

That description in docs is very misleading, I'll raise it on the docs list. PostgreSQL doesn't "start over" in READ COMMITTED at all, like some other DBs (e.g. Oracle) do. Instead it waits until the row it's waiting for commits or rolls back. If the other tx commits PostgreSQL reads the updated row, checks that it still matches any WHERE clause or other predicate, and then continues execution. The details are a bit arcane, see EvalPlanQual in the sources.

In either READ COMMITTED or SERIALIZATION the application should be able to re-issue a transaction. READ COMMITTED transactions can fail in ways that will succeed on retry, including:

Deadlock detection due to lock upgrades, lock ordering issues, etc
Administrative query cancel requests
Administrative shutdown/restart of the DB
Unplanned crash/restart of the DB
Connectivity interruption
... etc

SERIALIZABLE just adds some more failure cases.

Well written applications will cope with query failures and re-issue the transaction.


postgreSQL 9.3 streaming replication delay

Have you tried looking at:

SELECT
     CASE
          WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
          ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
     END
AS replication_lag;

The idea here is if both servers are synchronized, then there is no delay.  If they are not synchronized, then display the delay.

One potential issue here is if PostgreSQL replication stops, this won't work properly, so you'd need to check periodically by some other mechanism to see if replication is running.


Storing sports teams with multiple alternative names

Wiki answer from comments on the question:
Yes, this is what I do at the day job with textbook names where different schools will call the same textbook something completely different. Hopefully you won't run into an issue where different team names can overlap, as that makes this sort of problem 10x harder. - matigo
You may want to add a source column and an active to help disambiguate abbreviations since, presumably, a single site won't use the same name for two different teams at the same time even if two different sites use the same name for different teams.

Speaking about storing (subj) I'd add a column for additional attributes. Like "primary", "official full", "official short" and so on.. Speaking about scraping script I'd recommend to create normalizing vocabulary, which sets the relation between "any form" of a token and its canonical name (Utd -> United). This may simplify in "some name" converting to canonical name (or in searching for the most close canonical names list). - Akina


I suggest that you circumvent reading the error log directly and instead implement SQL Agent Alerting.  Under this methodology, you'd simply create alerts for, say, when SQL Server throws an error (I recommend separate alerts for severity levels 19 -25) and tell SQL Server to email you when the error occurs.

You can then generally ignore error log except for specific troubleshooting situations.

General details are here. The details for setting up SQL Agent alerts specifically using SSMS are on TechNet are here and are the same in Books On-Line here.

Hope this helps,
-Kevin

@kekline


MySQL Syntax Error When Creating a Trigger

You need to use the Delimiter /// first if you are creating a trigger.

Delimiter ///
CREATE TRIGGER Section_Insert AFTER INSERT ON Section
FOR EACH ROW BEGIN
INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT');
End;
///


the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.


Unable to configure oracle 11g net server

Go to www.sysinternals.com - download the Sysinternals suite - and then run Tcpview and check what executable is running on port 1521. You can sort by port by clicking on the "Local port" column. If you try and run netstat -abn from the command line, you'll (in all likelihood) get an error "The requested operation requires an elevation". Just why this error occurs when this information is available through Tcpview is a mystery.

Do you have an Oracle instance already installed? I've installed Oracle on a number of Windows machines and I've never come across port 1521 being used by anything else. In any case, Tcpview will tell you which executable is blocking your Oracle port.

[EDIT - after further research]

I was reading around this and I came across this post, which shows that running resmon.exe will give you the information htat you want - Go to the "Listening Ports" tab, "Ports" column, but as pointed out, the GUI is truly awful.

From the same question here, from the command line you can run netstat -aon | find /i "listening"``, but as I mention, I can't get the corresponding process name using the b flag.


parsing and purpose of oracle audit (.aud) logs

1) Audits are performed on actions you define.
For example: audit all by testing by access will audit everything that the user testing does.

2) I think you should look into XML auditing. Source
Example:
Set up your database by issuing alter system set audit_trail='XML, EXTENDED';
This modification does require a DB restart.
After you set that you'll notice your audit files start coming in a format that looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd" xmlns:
xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/
oracleas/schema/dbserver_audittrail-10_2.xsd">
  <Version>10.2</Version>
  <AuditRecord>
    <Audit_Type>1</Audit_Type>
    <Session_Id>108802</Session_Id>
    <StatementId>9</StatementId>
    <EntryId>1</EntryId>
    <Extended_Timestamp>2005-10-09T00:20:02.284327</Extended_Timestamp>
    <DB_User>SCOTT</DB_User>
    <OS_User>oracle</OS_User>
    <Userhost>prolin1</Userhost>
    <OS_Process>22158</OS_Process>
    <Terminal>pts/3</Terminal>
    <Instance_Number>0</Instance_Number>
    <Object_Schema>BANK</Object_Schema>
    <Object_Name>ACCOUNTS</Object_Name>
    <Action>103</Action>
    <Returncode>0</Returncode>
    <Scn>6447392335</Scn>
    <SesActions>---------S------</SesActions>
  </AuditRecord>
</Audit>

Once its in that format you'll find that you can do just about anything with it you like. Personally I send them to splunk for indexing, parsing, and handy searching.
Best of luck!


What database and setup can handle several million inserts per minute?

Ok, let me start simple: My old trusted 5 year old main db server (2x quad core AMD Opteron 2378) handles around 80,000 inserts per second, though small ones. That is without SSD for log and data on a SQL Server.

There are plenty of databases that can meet your requirement - it is MOSTLY a question of appropriate hardware. Heck, if you would go Oracle, then the smallest Exadata in a performance configuration (pure SSD) likely can do 10 times as much - we got in a project 5 gigabyte/second stable IO out of it, with a paltry 3 storage nodes.

That said, you WILL have to test it yourself. A lot depends on your programming or lack thereof. If you hit 1 insert per transaction, that is VERY different - and a lot harder to do (not at all because your log partitions need to handle 5 million IOPS then or fake the flush).


ORA-21780: Maximum number of object durations exceeded

Somehow you are trying to keep too many PL/SQL objects (I mean "TYPE AS OBJECT" objects). There is an internal limitation of around 64k of these per session. This is on the client side (not on server side), so this is a programming error, not related to database administration.

Not if that matters, but if you are simply curious why this message speaks about "durations" see this doc.


How can I send an email when objects get invalidated?

The scope of this question requires some work on your part but as a rough guide:

get an email package.  You can roll your own using UTL_SMTP (Pre Oracle 10) or it's succesor UTL_MAIL (Oracle 10 onwards).  However this is such a basic functionality that many people have written and shared their own versions. For Oracle 11 and beyond do not forget to allocate permissions for your database to access the ports required.
create a package to manage your errors.  Today you want to see when a package has been invalidated, tomorrow it will be something else. Plan for the future.... You would need to create a procedure with a cursor along the lines of
SELECT owner,
   object_type,
   object_name,
   status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
open the cursor, when you find something, use the email package to mail who you want
finally, if you find a problem, fix the problem: use the handy code here: http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php to recompile the package

Edit: the original poster asks how to track incidents.  More information is needed such, Oracle version, what kind of incidents, how often do you want to receive notifications.


log shipping backup fails every so often on only one server

Since your network drive is available most of the times and the backup job only fails randomly, I would first check the network. It could very well be the network issue. I would also look at the intervals the backup job fails, to see if there is a pattern to it.

Coming to restarting the logshipping part - the easier method is to restore the full backup and the standby server and reconfigure. But it depends on the size of your database, resources available etc


You can use window (ranking). functions for this:

WITH ct AS
  ( SELECT X, Y, RowAddedDate, ID,
           Rn = DENSE_RANK() OVER (PARTITION BY X, Y
                                   ORDER BY RowAddedDate DESC)
    FROM dbo.MyTable
  )
sELECT X, Y, RowAddedDate, ID
FROM ct
WHERE Rn = 1 ;

If multiple rows have the same date with same X and Y, all of them will be returned due to the DENSE_RANK().

If you want only one, you can replace it with ROW_NUMBER() and adjust the ORDER BY to control which of the tied rows will be returned.

As for efficiency, an index on (X, Y, RowAddedDate) INCLUDE (ID) would help.


Why can't I create a index using column definition in MySQL?

Why can't I do the same for indexes?

They just haven't got around to implementing and testing that syntax extension yet, would be my guess. Support for such definitions may be added in a later version.

IIRC inline index definitions are not part of any of the SQL standards but are something that some DB engines have added for convenience, so if you wish to retain as much compatibility with a range of database back-ends this may be a consideration that stops you using inline index definitions. Other DBs do implement that syntax of course (SQL Server added both forms, the one mySQL supports and the one you are looking for, in the 2014 release with a few enhancements in 2016) but perhaps not in a directly compatible way.


How to find out the service accounts for each of my instances in the current server?

DMV is specific to a particular instance, so no way to get info for all instances of sql server running on a given machine.

2 ways to do it -

Use your script to read registry and dump the info into a temp table and then join either  sys.dm_server_services and/or  sys.dm_server_registry depending on your needs. You can possibly join both the dmvs using the ImagePath
use powershell - dbatools - Find-DbaInstance and then pipe the discovered instances to any other service related commands for additional info. I have used Boe Prox's Get-SQLInstance to discover sql server instances on a given machine.


How to check values for records from different rows

I guess that what you would like is not exactly what you are asking for... so, correct me if I missinterpreted you.

Assuming this is your data (I represent it with a table, but it can be a view):

 CREATE TABLE v
 (
     ID varchar(9),
     LETTER_ID int,
     MEDICAL_ID int,
     SICKNESS_ID int,
     PRO_ID int,
     EDU_ID int,
     TRAINING_ID int,
     RECORD_ID int,
     NID int
 ) ;

 INSERT INTO v
     (ID,         LETTER_ID, MEDICAL_ID, SICKNESS_ID, PRO_ID, EDU_ID, TRAINING_ID, RECORD_ID, NID)
 VALUES
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, 47584, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, 4673735, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105419, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', 299080,   NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659652, NULL, NULL, NULL),
     ('MI1390033', NULL,     309609, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659650, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659654, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, 161077),
     ('MI1390033', NULL,     309608, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659651, NULL, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, NULL, 154745, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659653, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, 94272, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, 44917, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105418, NULL, NULL, NULL, NULL)
 ;

What you actually want is to flatten these data first.

That is, GROUP all rows with the same ID, and take the only non null value of the other columns (or, if there are more than one, the one value that actually is of interest to you).

I have chosen to do this using the MAX aggregate function. You may want another one, such as MIN, or a MIN || MAX or something like a string_agg or group_concat (which MS-SQL Server doesn't yet provide):

 SELECT
     ID,
     max(LETTER_ID) AS LETTER_ID,
     max(MEDICAL_ID) AS MEDICAL_ID,
     max(SICKNESS_ID) AS SICKNESS_ID,
     max(PRO_ID) AS PRO_ID,
     max(EDU_ID) AS EDU_ID,
     max(TRAINING_ID) AS TRAINING_ID,
     max(RECORD_ID) AS RECORD_ID,
     max(NID) AS NID
 FROM
     v
 GROUP BY
     ID ;

This would be the intermediate result:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID |  PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | ------: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null |  105419 | 659654 |       44917 |     94272 | 161077
 MI1390454 |      null | null       |        null | 4673735 |   null |       47584 |    154745 |   null

At this point, you can select only the rows with (Letter, pro, record, education) not being null:

 SELECT
    *
 FROM
    (SELECT
         ID,
         max(LETTER_ID) AS LETTER_ID,
         max(MEDICAL_ID) AS MEDICAL_ID,
         max(SICKNESS_ID) AS SICKNESS_ID,
         max(PRO_ID) AS PRO_ID,
         max(EDU_ID) AS EDU_ID,
         max(TRAINING_ID) AS TRAINING_ID,
         max(RECORD_ID) AS RECORD_ID,
         max(NID) AS NID
     FROM
         v
     GROUP BY
         ID
    ) AS flatten
 WHERE
        LETTER_ID is not null
    and PRO_ID    is not null
    and RECORD_ID is not null
    and EDU_ID    is not null ;

You will get:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | -----: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null | 105419 | 659654 |       44917 |     94272 | 161077

dbfiddle here

If you're only interested in the ID value, but not the rest, you can use a different approach:

SELECT DISTINCT
    ID
FROM
    v AS v0
WHERE
        EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND LETTER_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND PRO_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND RECORD_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND EDU_ID is not null) ;

dbfiddle here


I can see why Oracle RAC can handle split brain and PXC cannot.
What separates them in their architecture and data storage. In what ways ???
Oracle RAC

All RAC Instances deal with only one set of database files
Each RAC Instance has its own Log Buffer and LGWR (Log Writer) process
Writes to any Oracle RAC instance, even on nodes that go down temporarily, can have their log sequence numbers coalesced and serialized

Percona XtraDB Cluster

Each PXC node has its own complete set of data
Writes to one PXC node must be rolled back or committed on all nodes in unison
Writes to PXC nodes that are split in communication will only update/commit to its local copy of the data. Awareness of such writes by other unconnected PXC nodes does not exist when communication has been established again.

According to Percona's Frequently Asked Questions on PXC

Q: How would it handle split brain?
A: It would not handle it. The split brain is hard stop, XtraDB Cluster can’t resolve it. That’s why the minimal recommendation is to have 3 nodes. However there is possibility to allow a node to handle the traffic, option is:
wsrep_provider_options="pc.ignore_sb = yes"

SUMMARY

Oracle RAC is ACID compliant as a Cluster (again, one set of database files)
Percona XtraDB Cluster abides by CAP Theorm instead of ACID compliance
See YouTube video where Vadim Tkachenko (Percona CTO) discusses the split-brain scenario and how it is your responsibility (Video starts from 17:00 mark, listen up to 19:20 or more)


Unsure how to implement certain constraints and relations in this relational database schema

You get what you pay for on SO.  The advice against using natural keys (names instead of numbers, in this case) is common, but misguided.  If you don't believe me, ask your professor.

The number of seats would seem to be a property of the plane, right?  The number of booked seats would seem to be the count of seats booked, grouped by flight number.

The arrival and departure cities can be constrained with a CHECK constraint.  You might also want to ensure that departures happen before arrivals (assuming 21st century airport).

Your design is pretty good, better than some of the advice you're getting.  Stick with it, don't worry about datatypes for your keys, don't look for fanciness like triggers to implement application logic (and don't fob off real constraints to the application).  Do RTFM for CREATE TABLE several times.  If you think of a constraint that can't be declared, I suggest you document it in your project as a flaw in the DBMS.

Do think very carefully about what's unique.  I doubt you need BookingID, because PassengerID, Flight#, and Seat would seem to be unique.  Passengers are complex (in a real database you'd have a bunch of tables to describe someone) so they need an ID for convenient reference, but still you likely wouldn't want two records sharing name & address.


Sure. For example: use flashback query on DBA_VIEWS.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> create or replace view v1 as select * from dual;

View created.

SQL> select sysdate from dual;

SYSDATE
-------------------
2019-11-29 12:30:48

SQL> select count(*) from dba_views where view_name = 'V1';

  COUNT(*)
----------
         1

SQL> select text from dba_views where view_name = 'V1';

TEXT
--------------------------------------------------------------------------------
select "DUMMY" from dual

SQL> drop view v1;

View dropped.

SQL> select count(*) from dba_views where view_name = 'V1';

  COUNT(*)
----------
         0

SQL> select text from dba_views where view_name = 'V1';

no rows selected

SQL> select sysdate from dual;

SYSDATE
-------------------
2019-11-29 12:32:17

SQL> select text from dba_views as of timestamp timestamp'2019-11-29 12:30:48' where view_name = 'V1';

TEXT
--------------------------------------------------------------------------------
select "DUMMY" from dual

This may or may not work, depending on whether you still have the required unto to perform the above flashback query.


How to flatten and pivot a normalised hierarchy table

As far as you're using a hierarchical structure you can apply a recursive solution. But for a large number of rows you must deal with MAXRECURSION, have a look at MS Docs about it.

Quoted from docs:

  An incorrectly composed recursive CTE may cause an infinite loop. For
  example, if the recursive member query definition returns the same
  values for both the parent and child columns, an infinite loop is
  created. To prevent an infinite loop, you can limit the number of
  recursion levels allowed for a particular statement by using the
  MAXRECURSION hint and a value between 0 and 32,767 in the OPTION
  clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets
  you control the execution of the statement until you resolve the code
  problem that is creating the loop. The server-wide default is 100.
  When 0 is specified, no limit is applied. Only one MAXRECURSION value
  can be specified per statement.

WITH tree AS
(
    SELECT
        PositionCode as CurrCode,
        PositionCode,
        PositionCode as Lvl0Code,
        PositionName as Lvl0Name,
        cast('na' as nvarchar(10)) as Lvl1Code,
        cast('na' as nvarchar(10)) as Lvl1Name,
        cast('na' as nvarchar(10)) as Lvl2Code,
        cast('na' as nvarchar(10)) as Lvl2Name,
        cast('na' as nvarchar(10)) as Lvl3Code,
        cast('na' as nvarchar(10)) as Lvl3Name,
        ParentCode,
        Level
    FROM
        tbl
    WHERE
        Level = 0
    UNION ALL
    SELECT
        tree.CurrCode,
        tbl.PositionCode,
        tree.Lvl0Code,
        tree.Lvl0Name,
        case when tbl.Level = 1 then tbl.PositionCode else tree.Lvl1Code end as Lvl1Code,
        case when tbl.Level = 1 then tbl.PositionName else tree.Lvl1Name end as Lvl1Name,
        case when tbl.Level = 2 then tbl.PositionCode else tree.Lvl2Code end as Lvl2Code,
        case when tbl.Level = 2 then tbl.PositionName else tree.Lvl2Name end as Lvl2Name,
        case when tbl.Level = 3 then tbl.PositionCode else tree.Lvl3Code end as Lvl3Code,
        case when tbl.Level = 3 then tbl.PositionName else tree.Lvl3Name end as Lvl3Name,
        tbl.ParentCode,
        tbl.Level
    FROM
        tbl
    JOIN
        tree
        ON tree.PositionCode = tbl.ParentCode
)
SELECT
    CurrCode,
    Lvl0Code,
    Lvl0Name,
    Lvl1Code,
    Lvl1Name,
    Lvl2Code,
    Lvl2Name,
    Lvl3Code,
    Lvl3Name
FROM
       tree
OPTION (MAXRECURSION 0);

CurrCode | Lvl0Code | Lvl0Name | Lvl1Code | Lvl1Name | Lvl2Code | Lvl2Name | Lvl3Code | Lvl3Name
:------- | :------- | :------- | :------- | :------- | :------- | :------- | :------- | :-------
AA0001   | AA0001   | CEO      | na       | na       | na       | na       | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | na       | na       | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0003   | CIO      | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0004   | SnrMgr   | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0004   | SnrMgr   | AA0005   | JnrMgr
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0003   | CIO      | AA0006   | ItMgr

db<>fiddle here

If you don't like or don't want or simply you cannot use a recursive solution, you can use a series of nested CTE's (one for each level), and finally combine the results using a UNION operation:

WITH l0 AS
(
    SELECT PositionCode as CurrCode, PositionCode as Lvl0Code, PositionName as Lvl0Name,
           'na' as Lvl1Code,
           'na' as Lvl1Name,
           'na' as Lvl2Code,
           'na' as Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           level
    FROM   tbl
    WHERE  level = 0
),
l1 AS
(
    SELECT l0.CurrCode, l0.Lvl0Code, l0.Lvl0Name,
           t1.PositionCode Lvl1Code,
           t1.PositionName Lvl1Name,
           'na' as Lvl2Code,
           'na' as Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           t1.level
    FROM   tbl t1
    JOIN   l0
           ON t1.ParentCode = l0.Lvl0Code
    WHERE  t1.level = 1
),
l2 AS
(
    SELECT l1.CurrCode, l1.Lvl0Code, l1.Lvl0Name,
           l1.Lvl1Code,
           l1.Lvl1Name,
           t2.PositionCode Lvl2Code,
           t2.PositionName Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           t2.level
    FROM   tbl t2
    JOIN   l1
           ON t2.ParentCode = l1.Lvl1Code
    WHERE  t2.level = 2
),
l3 AS
(
    SELECT l2.CurrCode, l2.Lvl0Code, l2.Lvl0Name,
           l2.Lvl1Code,
           l2.Lvl1Name,
           l2.Lvl2Code,
           l2.Lvl2Name,
           t3.PositionCode Lvl3Code,
           t3.PositionName Lvl3Name,
           t3.level
    FROM   tbl t3
    JOIN   l2
           ON t3.ParentCode = l2.Lvl2Code
    WHERE  t3.level = 3
)
SELECT * FROM l0
UNION
SELECT * FROM l1
UNION
SELECT * FROM l2
UNION
SELECT * FROM l3
ORDER BY CurrCode, level;

db<>fiddle here


The problem with your query performance is that x = 5 or y = 5 queries don't usually use indexes.
We help this out with your code by creating greatest and least values as generated columns with indexes.
alter TABLE `pendency`
add lpen int as (least(id_user_pen,id_user_req_pen)),
add gpen int as (greatest(id_user_pen,id_user_req_pen)),
add index gpen(gpen),
add index lpen(lpen);

Then use a union, first to grap those with 5 as the greater user (talking to 1-4) and then 5 as the least talking to the others.
(
SELECT
    p.id_pen,
    p.id_env_pen,
    0 AS id_star,
    FALSE AS is_star,
    p.text_pen AS text_send,
    p2.text_pen AS text_response,
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,

    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
JOIN pendency p2 ON
  p2.id_pen_req_pen = p1.id_pen
  AND p.gpen= 5
)
UNION ALL
(
SELECT
    p.id_pen,
    p.id_env_pen,
    0 AS id_star,
    FALSE AS is_star,
    p.text_pen AS text_send,
    p2.text_pen AS text_response,
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,

    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
JOIN pendency p2 ON
  p2.id_pen_req_pen = p1.id_pen
  AND p.lpen= 5
)

Joining to users, handling of (id_user_req_pen!= -10) and ordering has been left as an exercise to the reader you.


Sql Server 2008 Replication "Generate Scripts" option not available

Are these push subscriptions?  If so, the Generate Scripts option will not be available on the subscribers.  To generate scripts for push subscriptions, right-click on the Publication on Server 1 -> Generate Scripts...  The generated script will contain T-SQL necessary to create the publication and the push subscriptions.

Pull subscriptions should have Generate Scripts option at the subscriber.


How to know whether a thread is available for new request or currently busy with other requests.

You can use DMV sys.dm_os_schedulers to get this information. The column you have to refer is work_queue_count. As per BOL it means

  Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable.

You can use below query to check number of waiting taks for all online schedulers

select work_queue_count from sys.dm_os_scheduler where status='Visible online'

I am not sure what you are trying to achieve but below is what I got from this blog

  select wt.session_id,
  ot.task_state,
  wt.wait_type,
  wt.wait_duration_ms,
  wt.blocking_session_id,
  wt.resource_description,
  es.[host_name],
  es.[program_name]
  FROM  sys.dm_os_waiting_tasks  wt
  INNER  JOIN sys.dm_os_tasks ot
  ON ot.task_address = wt.waiting_task_address
  INNER JOIN sys.dm_exec_sessions es
  ON es.session_id = wt.session_id
  WHERE es.is_user_process =  1

  I could see count more than 500 with suspended status.I dont see any blockings

A suspended is thread which is waiting for some resource and is currently not active. The wait can be I/O, network etc.. See the blog I have shared for more details.

  Can i use below query to find that i have worker thread starvation

No that is not the correct query.

Stop using sys.sysprocesses is legacy view and MS does not recommends it to use. Instead use sys.dm_exec_requests
You can get worker thread count for each scheduler using DMV sys.dm_os_scheduler

   select current_workers_count from sys.dm_os_scheduler where status='visible online'


Got it to work a few ways:

postgres@ironforge:~$ cat onerrorstop.sql
\set ON_ERROR_STOP on
select * from gurumeditationerror;
postgres@ironforge:~$ psql -f 'onerrorstop.sql'
psql:onerrorstop.sql:2: ERROR:  relation "gurumeditationerror" does not exist
LINE 1: select * from gurumeditationerror;
                  ^
postgres@ironforge:~$ echo $?
3
postgres@ironforge:~$

and:

postgres@ironforge:~$ echo "\set ON_ERROR_STOP on
> select * from smellyfarts;" | psql
ERROR:  relation "smellyfarts" does not exist
LINE 1: select * from smellyfarts;
                  ^
postgres@ironforge:~$ echo $?
3
postgres@ironforge:~$

and:

postgres@ironforge:~$ cat onerrorstop.sql
select * from gurumeditationerror;

postgres@ironforge:~$ psql -v ON_ERROR_STOP=on -f 'onerrorstop.sql'
psql:onerrorstop.sql:1: ERROR:  relation "gurumeditationerror" does not exist
LINE 1: select * from gurumeditationerror;
                      ^
postgres@ironforge:~$ echo $?
3
postgres@ironforge:~$

Version:

    postgres@ironforge:~$ psql --version
    psql (Post greySKULL) 9.5.19
    postgres@ironforge:~$


Grouping SQL Server Connections By Connection Pool

You can get a little closer if you add host process id but I don't think you'll be able to get an actual connection pool count 100% from server data because many connection string attributes are processed on the client and never sent to the server. This might be close enough for your needs, though.

SELECT
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id
    , COUNT(*) AS connection_count
FROM sys.dm_exec_sessions AS s
GROUP BY
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id;

A client-side exception like "timeout period elapsed prior to obtaining a connection from the pool" should be raised when the pool size is reached an another connection cannot be acquired within the connection timeout limit.


SQL Server Configuration Manager changes effected via Central Management Server

Taken from SQLServerPedia:

  Manage Your Services Remotely

  Another big advantage of CMS grouping all your servers together in a single place is that you can administrate the SQL Services on the
  target box easily. To do this right-click on the instance name. On the
  submenu you have a couple of service-related options. Under the
  Service Control option you can quickly Start, Stop, Pause, Resume or
  Restart your SQL Service. Of note, this action only applies to your
  SQL Engine service so if you have SQL Agent Service running (which
  relies on Engine) you may need to manually restart that service
  depending on how you have your services configured.

  For a little more control over the services on the target system select SQL Server Configuration Manager from the menu selection. This
  opens your typical Configuration Manager console but difference being
  that it now shows you the services on the target server you selected.
  To verify this you can see the target server's name in parenthesis
  next to SQL Server Configuration Manager in the left hand window pane.
  From here you can look at and configure your various SQL Server
  Services such as the database engine, Reporting Services, SQL Server
  Agent or SQL Server Browser. Be aware, however, this remote management
  of configuration manager only works on SQL Server 2005 editions and
  above.


MySQL Workbench copies Schema, but not data, from SQL Server 2008

According to the MySQL Documentation, the steps for the Migration Wizard should be

Connecting to the databases
Schemata Retrieval and Selection
Reverse Engineering
Object Selection
Migration
Manual Editing
Target Creation Options
Schema Creation
Create Target Results
Data Migration Setup
Bulk Data Transfer
Migration Report

Sounds like you got to Step 9. You have to make sure to setup Data Migration. Apparently, it is not automatic.

If you are having problems with the import, you are not alone. This has been a source of heartache for many. You may need to try :

MySQL Migration Toolkit
SQLyog


Restored database from .bkp file in SQL Server 2014 generates some strange errors

The errors have nothing to do with the fact that the database is restored (in fact they aren't even errors, just intellisense being flaky).

As you can see in the dropdown top left and the status bar (in yellow) you are executing your query against the master database.

Either change your database in the dropdown or execute a USE mydatabasename;.

As noted by Aaron Ctrl-shift-R refreshes the intellisense but that could take a while before taking effect.


There are commercial (Redgate for example) and free products that will do this for you as part of the backup.

http://sourceforge.net/projects/mssqlcompressed/


table exists on 'show tables' - can't query

Look at the question again. You ran

mysql> show tables;

What does the output header say ?

+-------------------+
| Tables_in_photos  |
+-------------------+

That means you were standing in the photos database.

If you had run

SELECT database();

or

SELECT schema();

It would have echoed photos.

Now, look at the error message

mysql> select * from captions_images;
 ERROR 1146 (42S02): Table 'ig_call.captions_images' doesn't exist

Since you were standing in the ig_call database and you issued select * from captions_images;, mysql assumed you want to select from captions_images tables in the ig_call database.

Try the following

select * from photos.captions_images limit 10;

If you get the same kind of message, then you have corrupt table issues in terms of the InnoDB data dictionary. If you get data, then you were simply in the wrong place at the wrong time. In your case, you were in the wrong database at the wrong time.


Prevent Firebird's default instance from starting

There are two options

Manually change the startup type of the service
Remove (and optionally reinstall) the service

Manually change the startup type of the service

You can disable it in the Services configuration. Use Windows-key+R or open run from the start menu, enter services.msc, find the Firebird instance (Firebird Server - DefaultInstance), right click, Properties and change the Startup type from Automatic to Manual:

Remove (and optionally reinstall) the service

You can also remove the service entry completely, to do this, you need to start the command prompt as Administrator. Under Windows 10 this would be Windows-key+X, A. I believe this doesn't work under Windows 8.1, there you may need to use Start, search for cmd or Command prompt, right click and select Run as Administrator.

In the command prompt go to the location of your Firebird install (or for Firebird 2.5 and lower, its bin-folder), and run

instsvc stop
instsvc remove

If you want to have the service entry, but just not automatically started, you can reinstall it with:

instsvc install -demand

This will install the service with startup type Manual (just using instsvc install or instsvc -auto will install it as Automatic); see instsvc for more information.


Is there a Maintenance Plan that checks my database for corruption?

Good question. The integrity check in the maintenance plan is enough since it will run a dbcc checkdb.
From MSDN:
integrity check

The Check Database Integrity task encapsulates the DBCC CHECKDB statement.

Running this task will be resource intensive. You don't want to do this in prod. servers if you don't have a maintenance window.
The common way is the following:
1. Backup your databases
2. Restore your db's on a test environment
3. Run a dbcc checkdb on your databases.
This way you check your DB integrity and verify your backup.


Collect SQL Server Query Execution time in seconds

There are indeed multiple methods.
Query Store
Since you have SQL Server 2016, may I recommend Query Store? You can enable it per database from the "properties" menu in SSMS, or by running something similar to the following script:
USE [master]
GO
ALTER DATABASE [TestDB] SET QUERY_STORE = ON
GO
ALTER DATABASE [TestDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO)
GO

Once it is turned on, you can look at query performance details.

Gotchas:  Fair warning - turning on Query Store will clear the plan cache! Query Store, as a newer feature, can have some quirks. I've run across several myself. As far as performance, I see it used in environments with 20k batches/sec and only a couple of percent CPU overhead.
DMVs
Another option is to use the dmvs. The one you are looking for in this case is sys.dm_exec_query_stats, specifically the "elapsed_time" columns. Here's a query to look at recent long-duration queries.
SELECT TOP 50
st.text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qp.query_plan,
qs.execution_count,
qs.last_execution_time,
qs.last_worker_time/1000000.0 AS last_worker_time_s, --this is CPU time
qs.last_elapsed_time/1000000.0 AS last_elapsed_time_s --this is clock time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time > DATEADD(minute,-5,GETDATE()) --last 5 minutes
ORDER BY qs.last_elapsed_time DESC

Gotchas to be mindful of with this dmv are that it doesn't capture RECOMPILE queries, and it doesn't capture canceled queries (which is what happens with a query timeout from the application).
SSMS
Finally, if you know the exact query, you can run it in SSMS and look at the actual execution plan. Highlight the select, and look at the properties window on the right. You'll see extremely helpful information about CPU and duration (in milliseconds). Similar information exists for the operators as well.

Gotchas with this method include the fact that queries submitted through SSMS do not necessarily reflect what happens with application queries. Canonical link here.
In short, I highly recommend Query Store.


How can I limit a hierarchical query to the point where there are multiple children?

Simplified with window functions:

with hier as
    (select   level as the_level, <columns>
      from <tables>
        start with <predicate>
        connect by prior <child> = <parent>
    ), levels as
    (select hier.*, count(*) over (order by the_level) as count_items
      from hier
    )
  select levels.*
    from levels
    where the_level = count_items ;


I think this advice is more a safety suggestion than a requirement.  Further, if a table is corrupted/damaged such than it needs repair ran on it that means something that wasn't supposed to happen happened.  The slave very well may be okay and not need to go through the expensive table rebuild.

While it is true a slave will execute anything written to the binlogs after it was stopped once restarted, you can simply prevent certain statements from even being written to the binlog in the first place.

If you run

set sql_log_bin=0;

then anything you execute in that session thereafter will not be written to the binlog; will never be executed on the slave.

Edit

After repairing a table on the master you'll want to checksum it.  If you can afford to have your master table offline for a period beyond the repair you can just run

checksum table ;

If you need to get back in action quickly you should look to using pt-table-checksum from the Percona Toolkit.  This might also be something to consider over a straight checksum even if the table isn't being actively used.

Here's why.  pt-table-checksum will check your table in chunks.  You can read the details on exactly how it operates in the docs.  In short it will check parts of the table, for example, IDS 1-1000, 1001-2000, etc.  This is meant to server as an anti-lock braking system if you will for live tables.  A portion is checksumed then the lock on the table or rows being checked is released, allowing other live traffic waiting to make some progress in between chunks.

Further, even if extended locking is not a concern it gives you insight to what parts of the tables differ. Maybe it turns out you only need to synch up a small segment of rows instead of copying for gigabytes of data.

The percona toolkit also includes pt-table-sync to resolve such conflicts.


Query that checks for subset to mysql query

That is a challenge!

By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.

SELECT  have.patient_id,  have.pharmacy_id
    FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
            FROM sells AS s
            JOIN prescriptions AS pr  USING(drug_id)
            GROUP BY  s.pharmacy_id, pr.patient_id ) AS have
    JOIN ( SELECT patient_id, COUNT(*) AS ct
            FROM prescriptions
            GROUP BY patient_id ) AS need
        ON need.patient_id = have.patient_id
    WHERE need.ct = have.ct

That can then be used as a derived table to get the other specifics:

SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
    FROM ( the-above-query ) AS x
    JOIN pharmacies as PH  USING(pharmacy_id)
    JOIN patients as PA    USING(patient_id)

Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.


Should I put users in admin DB or in the DB that they should have access to

in general not just Mongo talking in general given root/sysadmin privileges or adding all users to the admin groups is not recommended due to obvious reasons. And in mongo like advanced databases there are enriched security groups and roles can be created and assign each user as per the requirement. please refer the mongo documentation on creating user roles and assigning privileges.
https://docs.mongodb.com/manual/tutorial/manage-users-and-roles/


I tested this, first I installed MongoDB 2.4.6 with brew, and then used launchctl to load and unload.  In my testing, it sends a SIGTERM to the mongod process which then shuts down as expected.  Here are the commands I used as well as the logs for the mongod process:

Commands:

launchctl load -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist
launchctl unload -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist

Logs:

tail -f /usr/local/var/log/mongodb/mongo.log
Tue Oct 22 17:33:32.774 [initandlisten] MongoDB starting : pid=13192 port=27017 dbpath=/usr/local/var/mongodb 64-bit host=adamc-mbp.local
Tue Oct 22 17:33:32.774 [initandlisten] db version v2.4.6
Tue Oct 22 17:33:32.774 [initandlisten] git version: nogitversion
Tue Oct 22 17:33:32.774 [initandlisten] build info: Darwin minimountain.local 12.4.0 Darwin Kernel Version 12.4.0: Wed May  1 17:57:12 PDT 2013; root:xnu-2050.24.15~1/RELEASE_X86_64 x86_64 BOOST_LIB_VERSION=1_49
Tue Oct 22 17:33:32.774 [initandlisten] allocator: tcmalloc
Tue Oct 22 17:33:32.774 [initandlisten] options: { bind_ip: "127.0.0.1", command: [ "run" ], config: "/usr/local/etc/mongod.conf", dbpath: "/usr/local/var/mongodb", logappend: "true", logpath: "/usr/local/var/log/mongodb/mongo.log" }
Tue Oct 22 17:33:32.775 [initandlisten] journal dir=/usr/local/var/mongodb/journal
Tue Oct 22 17:33:32.775 [initandlisten] recover : no journal files present, no recovery needed
Tue Oct 22 17:33:32.806 [websvr] admin web console waiting for connections on port 28017
Tue Oct 22 17:33:32.806 [initandlisten] waiting for connections on port 27017
Tue Oct 22 17:34:21.682 [signalProcessingThread] got signal 15 (Terminated: 15), will terminate after current cmd ends
Tue Oct 22 17:34:21.682 [signalProcessingThread] now exiting
Tue Oct 22 17:34:21.682 dbexit:
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: going to close listening sockets...
Tue Oct 22 17:34:21.682 [signalProcessingThread] closing listening socket: 9
Tue Oct 22 17:34:21.682 [signalProcessingThread] closing listening socket: 10
Tue Oct 22 17:34:21.682 [signalProcessingThread] closing listening socket: 11
Tue Oct 22 17:34:21.682 [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: going to flush diaglog...
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: going to close sockets...
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: waiting for fs preallocator...
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: lock for final commit...
Tue Oct 22 17:34:21.683 [signalProcessingThread] shutdown: final commit...
Tue Oct 22 17:34:21.692 [signalProcessingThread] shutdown: closing all files...
Tue Oct 22 17:34:21.692 [signalProcessingThread] closeAllFiles() finished
Tue Oct 22 17:34:21.692 [signalProcessingThread] journalCleanup...
Tue Oct 22 17:34:21.692 [signalProcessingThread] removeJournalFiles
Tue Oct 22 17:34:21.692 [signalProcessingThread] shutdown: removing fs lock...
Tue Oct 22 17:34:21.693 dbexit: really exiting now

I did this several times to confirm the behavior.  In Chrome at least the status page no longer responds and I receive an error (as expected) once it has been shut down.

The only difference between what I am doing and what you have posted is that I am not using sudo (in fact it refuses to load or unload due to dubious ownership of the file).  So, I changed the ownership of the plist file to root and tried sudo with the same results.

The only way I was able to recreate a failure to unload was as follows:

Start with sudo launchctl (root is the owner of the plist file)
Change ownership of the plist file back to my regular user
Try to unload without sudo

This fails with an error however:

launchctl: Error unloading: homebrew.mxcl.mongodb

Note: Subsequently changing the ownership back to the regular user made the unload successful

Similarly, this also produces the same error:

Start without sudo (regular user owns the plist file)
Change ownership of the plist file to root
Try to unload with sudo

I was unable to recreate the silent failure you seem to be having with any of the various combinations I tried.

Some information gathering tips which may give you a clue:

When you launch with launchctl, what does the output of this command say: launchctl list | grep mongodb? (it should list something like 13340  -   homebrew.mxcl.mongodb
If you run this same command after you run unload (without error), it should show the exit status in that middle column (-15)
Sometimes it can take a while for MongoDB to exit - so tail the log (see example above), see if the TERM signal is being received
Why is it you are using sudo?  Have you installed brew as root?  If so this might be at the core of the issue here - generally it is not recommended to run MongoDB as root.


Selecting rows ordered by foreign key count and filtering by date

So this is happening because even though you used a LEFT JOIN, you're filtering on a field in the right-side table of that LEFT JOIN in the part of your query that does WHERE v."createdAt" >= '2020-01-01 00:00:00' so this removes all the records in your left-side table "Server" where there are no rows it could join to in the "Vote" table (i.e. where createdAt is null).
You can add this additional condition in your WHERE predicate to fix that:
SELECT s.id, s.title, s.content, count("serverId") AS "voteCount"
FROM "Server" AS s
LEFT JOIN "Vote" AS v ON (s.id = "serverId")
WHERE v."createdAt" >= '2020-01-01 00:00:00'
    OR v."createdAt" IS NULL
GROUP BY s.id ORDER BY "voteCount" DESC;


How output a json file in postgresql functions?

The JSON representation of NEW in a trigger is just row_to_json(NEW), so that part is straightforward.

But writing into files on the server is a different story. It's only allowed to superusers because it gives the ability to corrupt or destroy all the instance's data.

I don't think there's a builtin function to write on the filesystem, but
the contrib module adminpack provides one:

pg_file_write(filename text, data text, append boolean)

data would be row_to_json(NEW) in your case.

The file should be inside the data directory, because:

  Only files within the database cluster directory can be accessed

although I think you may use symbolic links to circumvent that limitation,
if using a file system that supports them.

Here's a working example:

CREATE FUNCTION mytrigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
 PERFORM pg_catalog.pg_file_write('outputlog', row_to_json(NEW, true)::text, true);
 return NEW;
END
$function$

The file outputlog will be created in the data directory.


Selecting 3MB (or more/less) data from database server

For your purpose @Daniel's simple query should do the trick:

select repeat('A', (3 * 2^20)::int);    -- "3 MB"

"Size" depends. There is the size on disk, with or without overhead, with or without padding, with or without compression, with or without indexes / toast table / free space map ...
There is also the size of RAM needed to store the same data (typically more than on disk), the size of the text representation when sent over the wire, the compressed size, with or without tcp/ip overhead ...

You can use the function pg_column_size() to get an approximation.

Retrieve just enough rows from any table that's big enough:

SELECT *
FROM   any_big_tbl
WHERE  big_id <= (
   SELECT big_id
   FROM  (
      SELECT big_id, sum(pg_column_size(b)) OVER (ORDER BY big_id) AS size
      FROM   any_big_tbl b
      ) sub
   WHERE  size > (3 * 2^20)
   ORDER  BY size
   LIMIT  1
   );

big_id being the pk of any_big_tbl.
This computes a running sum of the number of bytes used to store the rows to find the id of the row reaching the requested size. The outer query returns all rows up to that id.

Or, to measure the size of the text representation, including parentheses for the row and escape characters where needed (typically results in fewer rows):

SELECT *
FROM   any_big_tbl
WHERE big_id <= (
   SELECT big_id
   FROM  (
      SELECT big_id, sum(pg_column_size(b::text)) OVER (ORDER BY big_id) AS size
      FROM   any_big_tbl b
      ) sub
   WHERE  size > (3 * 2^20)   -- "3 MB"
   ORDER  BY size
   LIMIT  1
   );

More about measuring "size":
Measure the size of a PostgreSQL table row


DB2 sorting by lowercase uppercase - problem

You'd need to use the UCA (Unicode Collating Algorithm): the COLLATE value would be something like UCA500R1_LPO_CU for Polish and uppercase first. More info here.

Note that you cannot change the collating setting of an existing database; you would have to create a new one with the required collation and move your data.


Things to check:

Is your Log file size set to a limit?
Have you run out of disk space on the drive where the log file resides (seems unlikely)?
Is this a physical or virtual server?
What service pack are you running?
What, if any, are the preceding errors in the error log?


Migrating a Mariadb/wordpress site with only .frm files

I have resolved my issue. Instead of copying only the database folder, I copied the entire /var/lib/mysql folder to the mariadb install. Then I did sqldump on each database and moved them to the mysql server. I'm not sure why the copy of the database folder didn't work but I got all my data back.


mysql Lock wait timeout exceeded; try restarting transaction

You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec.

mysql> set innodb_lock_wait_timeout=100;

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

The transaction which is timeout, try to lock table which is hold by another process. and your timeout variable set with little number of second. so it shows error.
You can see more status by the command.

SHOW ENGINE INNODB STATUS\G

You can see list of locked tables by-

 show open tables where in_use>0;

Now see the thread which is using this table

  show full processlist;

now you can kill that thread or wait to complete it.


Use a data-modifying CTE to chain the inserts in a single statement.

Assuming from your example that you want to insert 1 for related_key_id in all rows.

WITH ins_key AS (
   INSERT INTO key (key_name)
   VALUES ($$key_1$$)
        , ($$key_2$$)
   RETURNING key_id       -- return newly generated key_id(s)
   )
INSERT INTO related_key (related_key_id, key_id)
SELECT 1, key_id
FROM   ins_key;


How can I properly merge the rows of several table with default values?

You could use FULL JOIN but the code gets a bit messy - at least for my taste. With 3 tables it's not so bad, you'd only need to change:

FULL OUTER JOIN t3
  ON t1.id = t3.id

to:

FULL OUTER JOIN t3
  ON COALESCE(t1.id, t2.id) = t3.id

but with more tables, it gets rather ugly. The other option is to gather all distinct id values and then LEFT JOIN all the tables:

SELECT
  d.id,
  COALESCE(t1.count_1, 0) AS count_1,
  COALESCE(t2.count_2, 0) AS count_2,
  COALESCE(t3.count_3, 0) AS count_3
FROM
  ( SELECT id FROM t1
  UNION
    SELECT id FROM t2
  UNION
    SELECT id FROM t3
  ) AS d
  LEFT JOIN t1 ON t1.id = d.id
  LEFT JOIN t2 ON t2.id = d.id
  LEFT JOIN t3 ON t3.id = d.id
ORDER BY id ;


MySQL: Select rows, grouped by ID, where none of the rows have a value in a certain column

I was able to solve my problem with the following query:

SELECT SUM(CASE
             WHEN o.CurrentOwner IS NULL THEN 1
             ELSE 0
           END) AS numNull,
       COUNT(*) AS total,
       LotID
         FROM owner o GROUP BY LotID HAVING numNull=total


Why is my SQL .mdf file 30GB when my data is only 4GB

Shrink file has to move allocated pages situated at the end of the file to the beginning of the file. The file can be shrunk only as much as the offset of the last allocated page. If you did this correctly things should work:

DBCC SHRINKFILE(<DataFile>, 4096);

If you believe you did this correctly but the file stays at the original size, open a support case.


How to select multiple columns between columns in Oracle SQL?

This is a crazy idea and would makes future maintenance really tricky.

It is not possible to guarantee that column order will never change.  There are plenty of situations where column order changes could potentially be beneficial.  It may happen accidentally (update the wrong database) or it may be done for performance reasons (e.g. moving mostly NULL columns to the end).

Even if it is possible - don't do it.


Issues converting MyISAM table to InnoDB (auto column issue)

At present, only MyISAM permits auto_increment values in a composite key. I wrote about this over a year ago : How can you have two auto-incremental columns in one table?

According to the Book

Section 5.7 Page 89 Paragraph 1 states the following:

  The MyISAM Storage Engine supports composite indexes that include an AUTO_INCREMENT column. This allows the creation of independent sequences.

which I know you are well aware of. I simply stated this for the benefit of other readers.

As I stated in my past post

  This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!

Allowing this would require redesigning the gen_clust_index to accommodate such a mechanism.


Mysql query to get count result and group by

SELECT r.username,
       u.realname,
       ( SELECT  COUNT(*)
           FROM offline
           WHERE username = r.username
             AND message LIKE CONCAT('%', r.jid, '%')
       ) as "count"
    FROM roster AS r
    JOIN User AS u  ON r.username = u.username
    WHERE r.username = 'jwyvm_rdyt'

Sometimes it is best to do a COUNT in a subquery in the SELECT clause - to avoid over-counting when doing a JOIN.  And, I think, GROUP BY is avoided.


Does ROLLBACK TRAN honor LOCK_TIMEOUT value?

No, this is not possible.

SQL Server will always finish the ROLLBACK, no matter what. Even if you cut the power to the system in the middle of the ROLLBACK, the ROLLBACK will continue after the power is returned. ROLLBACK also ignores any connection interruptions and LOCK_TIMEOUT settings. In fact, a connection interruption will start a ROLLBACK if there is an open transaction at the time of interruption.


There may be several reasons:

Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small
Change the lookup cache mode from Full to Partial or no cache mode.
Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in  Integration services project properties page).
Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)
Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

I hope that it helps.


How can I split Date column values in Access into 2 columns?

ALTER TABLE table ADD COLUMN Start_Date TEXT(6)
ALTER TABLE table ADD COLUMN End_Date TEXT(6)

update table
set Start_Date = Left(column,6),
End_Date = Right(column,6)


Percona XtraDB Cluster: How to skip SST when starting?

I think you want wsrep_sst_method=skip not wsrep_sst_mode=skip

mysql> show variables like 'wsrep_sst%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| wsrep_sst_auth            |            |
| wsrep_sst_donor           |            |
| wsrep_sst_method          | xtrabackup |
| wsrep_sst_receive_address | AUTO       |
+---------------------------+------------+
4 rows in set (0.00 sec)

mysql>


Want to order on two columns but not the usual way

SELECT *
FROM Mytable
ORDER BY
   userID, Date

I assume Date is really a date/time type and not varchar...

Edit, after clarification:

Untested

SELECT
    M.*
FROM
    ( --one row for each user
    SELECT MIN(Date) AS FirstUserDate, userID
    FROM MyTable
    GROUP BY userID
    ) foo
    JOIN
    MyTable M ON foo.userID = M.userID
ORDER BY
    foo.FirstUserDate, M.userID, M.Date;


Suppose the table's layout is as follows:
CREATE TABLE mytable
(
    id int not null auto_increment,
    col1 datetime,
    col2 datetime,
    primary key (id)
);

You may want to try one of two things
IDEA #1 : Create a column to store difference in seconds
Create the column and populate as follows
ALTER TABLE mytable ADD COLUMN dtdiff INT NOT NULL;
ALTER TABLE mytable ADD INDEX (dtdiff);
UPDATE mytable SET dtdiff = UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)l

If your table does not have foreign keys or constraints, do this instead:
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD COLUMN dtdiff INT NOT NULL;
ALTER TABLE mytable_new ADD INDEX (dtdiff);
INSERT INTO mytable_new
    SELECT id,col1,col2,
    UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)
    FROM mytable
;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Once done, your query would essentially

SELECT * FROM mytable WHERE dtdiff > 0 for col1 > col2
SELECT * FROM mytable WHERE dtdiff < 0 for col1 < col2

IDEA #2 : Create a separate table to store difference in seconds
CREATE TABLE mytable_dtdiff
(
    id int not null,
    dtdiff int not null,
    primary key (id),
    key dtdiff (dtdiff)
);
INSERT INTO mytable_dtdiff
SELECT id,UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)
FROM mytable;

Once done, your query would essentially
SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff > 0) A
LEFT JOIN mytable B USING (id);

for col1 > col2 and
SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff < 0) A
LEFT JOIN mytable B USING (id);

for col1 < col2
Give it a Try !!!


Query To Return Values Where NOT All Text

Fairly simple:

Select * from @table where field1 LIKE '%[0-9]%'


The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Some errors automatically rollback the transaction.  See generally
Error and Transaction Handling in SQL Server

And if your error already rolled back the transaction, the first line of your catch block will throw this error.

So replace

  ROLLBACK TRANSACTION REALLOCATE;

with

 if @@trancount > 0 ROLLBACK TRANSACTION;

(Remove the 'REALLOCATE' label because this will NOT roll back only the nested transaction.  A Nested Transaction can have a name, but cannot be individually rolled back.  For that you would need a savepoint, but that's probably not the issue here).


Why are parallel writes in MySQL as slow as sequential writes?

Yes, I think you're IO bound and you haven't parallelized the harddrives involved in the writes.  There are lots of things that go into insert performance. You can learn more here.


You didn't tell us what the "failed attempt" means. But something like this should work:

with ldist as (
   select name,
          levenshtein(substring(name,1,200), lag(name) OVER (order by name))  as distance
   FROM books
   WHERE name <> ''
)
select *
from ldist
order by distance;

you don't want partition by name because that essentially puts every name into a single group and thus there is no "previous" row to.

As you you want the "previous" row based on the ordering of the name column, you need an order by name in the window definition.


Full name as a Primary Key (PK) is my preferred choice; is there another way?

You are absolutely right that a surrogate key (ID or otherwise) won't solve your root problem which is more fundamental than the database schema - "How do you tell speakers apart in the real world?"
That's a business question, and full name may or may not be good enough.
You should ask yourself - If I have 2 speakers named John Doe, how do I tell them apart in the real world, and that will be your answer to the key.

Common attributes used to identify people in these kind of data universes are Email address, User name / Alias (login to your system), SSN, perhaps even name + address may be unique enough for your business needs - what are the odds of 2 people with the exact same name residing at the same address?
Theoretically, you should accommodate every possible case, but in practice this would be much better than having a surrogate key, which will guarantee duplicates as you correctly observed, and not having any way to tell the entities in the database apart.
Using an ID will lead to much worse data consistency problems later on when the 2 John Doe start showing up in the wrong conferences :-)

HTH


EDIT

Amazingly enough, INFORMATION_SCHEMA.ROUTINES  does not have a param-list column.

You can use mysql.proc. For example, looking at a procedure of mine:

SELECT * FROM mysql.proc WHERE name='run' \G
*************************** 1. row ***************************
                  db: common_schema
                name: run
                type: PROCEDURE
       specific_name: run
            language: SQL
     sql_data_access: MODIFIES_SQL_DATA
    is_deterministic: YES
       security_type: INVOKER
          param_list:
  in query_script text

             returns:
                body: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\\')) and (LEFT(query_script, 2) != '/*') then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end
             definer: root@localhost
             created: 2012-08-17 17:24:46
            modified: 2012-08-17 17:24:46
            sql_mode:
             comment: Run given QueryScript text
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\')) and (LEFT(query_script, 2) != '/*') then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end

Look for the param_list column (I believe that was your main concern).
Also look for db, name, type.

=================

Alternatively, can use INFORMATION_SCHEMA.ROUTINES. For example, looking at a procedure of mine:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='run' \G
*************************** 1. row ***************************
       SPECIFIC_NAME: run
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: common_schema
        ROUTINE_NAME: run
        ROUTINE_TYPE: PROCEDURE
      DTD_IDENTIFIER: NULL
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\')) then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end
       EXTERNAL_NAME: NULL
   EXTERNAL_LANGUAGE: NULL
     PARAMETER_STYLE: SQL
    IS_DETERMINISTIC: YES
     SQL_DATA_ACCESS: MODIFIES SQL DATA
            SQL_PATH: NULL
       SECURITY_TYPE: INVOKER
             CREATED: 2012-08-17 17:24:46
        LAST_ALTERED: 2012-08-17 17:24:46
            SQL_MODE:
     ROUTINE_COMMENT: Run given QueryScript text
             DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: latin1_swedish_ci

In particular, look at the columns ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION. These provide most of the interesting data.


How to use Unique key via combinations of table fields?

Did you try

CREATE TABLE contacts (
 id int auto_increment primary key,
 name varchar(20),
 network_id int,
 network_contact_id int,
 UNIQUE KEY (`network_id`, `network_contact_id`)
);


What are some best practices for using schemas in SQL Server?

We use them

to separate permissions per client (eg we have desktop, WebGUI etc schemas)
for logical grouping eg (Data and Staging schemas for our tables).

Useful and practical observations past the white paper mentioned by Marian:

GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions of the schema
Nice groupings in SSMS Object Explorer
OBJECT_SCHEMA_NAME
You are forced to qualify object names (which is best practice)


In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day?

Your table seems fine at first glance. If autovacuum gets done and you have less than 30% of dead tuples, I see no need to worry.
You might want to use the pgstattuple extension to check if the table has a lot of free space; if yes, that would be an indication to make autovacuum faster.
The keyword here is faster: you'd have to lower autovacuum_vacuum_cost_delay or increase autovacuum_vacuum_cost_limit for that. Making autovacuum run more often won't do any good.
But if you are already experiencing performance problems with autovacuum being as fast as it currently is, I'd leave the settings alone.


ORA-02270: Error report: no matching unique or primary key for this column list

Foreign keys must reference another table's primary key, or a unique column in the other table (that is, a column with a unique constraint or index of some sort).

teamname is not unique in team, so a foreign key cannot link to it.

you need to either make teamname unique in team, or use a composite foreign key to match `team's composite primary key:

ALTER TABLE entry
    ADD CONSTRAINT entry_team_fk FOREIGN KEY ( teamname, carndate )
        REFERENCES team ( teamname, carndate );

Note: carndate may not be appropriate for this foreign key - just using it as an example. You would know best if there's a date in entry that would match team.carndate.


updating same column as in the select query

ROOT CAUSE

Look at the following parts of your query

UPDATE table_name (<<-- table_name)
   SET quantity = 19
 WHERE  productID = 148357
               AND productCost IS NOT NULL
               AND exampleDate1 >=
                      (SELECT min(exampleDate1)
                         FROM table_name (<< -- same table_name)
                        WHERE exampleDate1 >=
                                 (SELECT min(exampleDate)
                                    FROM table_name2
                                   WHERE description LIKE "We are true"))

You cannot execute an UPDATE or DELETE query against a table that uses a subquery against that same table. I have discussed this before :

Feb 22, 2011 : Problem with MySQL subquery
Jan 23, 2013 : Problem with nested UPDATE queries
Apr 30, 2013 : UPDATE table based on the same table

SUGGESTION

Rewrite it as two queries

#
# Retrieve Minimum Date into a Session Variable
#
SELECT min(exampleDate1) INTO @MininumDate
FROM table_name
WHERE exampleDate1 >=
(
    SELECT min(exampleDate)
    FROM table_name2
    WHERE description LIKE "We are true"
);
#
# Use Session Variable in the WHERE clause of the UPDATE
#
UPDATE table_name
    SET quantity = 19
    WHERE  productID = 148357
    AND productCost IS NOT NULL
    AND exampleDate1 >= @MininumDate
;

GIVE IT A TRY !!!


Under Solution Explorer On Righ Side In BIDS
---->RightClick
     ---->Properties
          ---->Configuration Properties
               ---->Debugging
                    ---->Debug Options
                         ---->Run64BitRuntime
                              ---->False


Handling Reporting Services and SharePoint Integration considering deprecation

Now our client wants SharePoint integration with Reporting Services

If they just want to embed reports in SharePoint pages, or write reports consuming SharePoint List Data, go with a native-mode installation of SSRS or Power BI Report Server and use the Report Viewer Web Part, or just the Web Page Web Part for embedding.
You might suggest they go with Power BI Report Server as it not only has the SSRS functionality, but also the Power BI Interactive Reports that are the successor to the old PowerView reports in SharePoint.
SSRS SharePoint mode adds the ability to deliver scheduled reports to SharePoint Lists, but there's reasonable workarounds for that in Native Mode.  And adds the ability to manage report content (reports, data sources, etc) inside SharePoint, so that's the key difference between Native and SharePoint integrated mode.  If they really need that, go with SSRS 2016.  It will be supported until July 2026.


If the foreign key is not nullable, then there is no reason for the join at all, is there? So why not let SQL Server scan the most efficient index available instead of computing a join that will result in the exact same count?

SELECT COUNT(*) FROM dbo.messages;

That said, you can do this much more efficiently:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.messages')
  AND index_id IN (0,1);

This will be slightly inaccurate due to in-flight transactions, but it also won't block (or be blocked by) any activity against the table, so pick your poison.


if DB_ID(N'DB_NAME') returns null you will get object_id's from all databases.  Try running from the target database:

use db_name

SELECT IOS.index_id,
       O.NAME                       AS OBJECT_NAME,
       I.NAME                       AS INDEX_NAME,
       IOS.leaf_allocation_count    AS PAGE_SPLIT_FOR_INDEX,
       IOS.nonleaf_allocation_count PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM
sys.dm_db_index_operational_stats(Db_id(), NULL, NULL, NULL) IOS
JOIN sys.indexes I
  ON IOS.index_id = I.index_id
     AND IOS.object_id = I.object_id
JOIN sys.objects O
  ON IOS.object_id = O.object_id
WHERE  o.NAME NOT LIKE 'sys%'
       AND i.NAME IS NOT NULL
       AND IOS.leaf_allocation_count != 0
       AND IOS.nonleaf_allocation_count != 0
ORDER  BY IOS.leaf_allocation_count DESC


What is PostGIS doing with ST_DistanceSphere?

There are two problems here.

Though it calculates the distance on a sphere, PostGIS doesn't calculate with the major-axis radius in WGS84 as presumed in the question. The sphere is created with a radius that is the mean radius of the WGS84 ellipsoid. This is by design.
Neither does MySQL, however the radius in MySQL was lifted from a lazy-copy-job from PostGIS that converted a radius in miles to meters from an obscure constant from a random 20-year old PostgreSQL module. Far from by design, this is presumed to be a bug.


How do I debug an ETL process that is unable to commit any records?

Since you're using a standard Oracle-to-Oracle database link, Oracle automatically does two-phase commits.  If you commit (or rollback) on one system, any changes made as part of the same transaction on the other system will committed (or rolled back) as well.

If there is an open transaction on the remote system that has row-level locks, that strongly implies that the local session is still there as well.  So you probably just have to do a commit (or a rollback) in the local session to remove the locks.

If the ETL process is really stuck waiting on locks that are held by the other session, you should be able to query gv$session on the remote database and see that the session that the ETL application is running is waiting on row-level lock contention.  Specifically, you would expect to see an event of enq: TX - row lock contention in gv$session.  You would also expect that if you queried dba_blockers that you would see the session ID of the session that connected from the local database over the database link and that if you queried dba_waiters that you would see that the ETL process's session was blocked by that session.


How to get all records from one table and see if they are used in another without a sub query

You can use a LEFT JOIN to get the same results. Note how the WHERE condition of the subquery was moved to the ON clause of the join:

SELECT
    uc.UserContractId,
    uc.UserContractName,
    oc.OrderID
FROM
    tblUserContracts AS uc
    LEFT JOIN tblOrderContracts AS oc
    ON  uc.UserContractID = oc.UserContractID
        AND oc.OrderID = 466
        AND oc.OrderContractStatus = 'Active'
WHERE
    uc.UserName = 'vandel212'
    AND uc.UserContractStatus = 'Active' ;


Oracle JDBC TLS connection fails (handshake_failure) after OS upgrade

Restarting the listener solved the issue... I can't understand what happened, as the listener was already restarted during the server reboot.

I should have tried that before.

Thanks to everyone who has taken time to help.


I'm a big fan of MERGE.
You can try the following;

MERGE INTO table1 main
USING ( SELECT TKey, a, b
          FROM table2 ) subsel
ON (main.TKey = subsel.TKey)
WHEN MATCHED THEN
UPDATE SET main.ColA = subsel.a,
          main.ColB = subsel.b

Here's a quick example;

SQL> SELECT * FROM maintab;

TKEY                 COLA                 COLB
-------------------- -------------------- --------------------
rec1
rec2
rec3

SQL> SELECT * FROM updtable;

TKEY                 A                    B
-------------------- -------------------- --------------------
rec1                 123                  456
rec2                 789                  321
rec3                 654                  987

SQL> l
  1  MERGE INTO maintab main
  2  USING ( SELECT TKey, a, b
  3            FROM updtable ) subsel
  4  ON (main.TKey = subsel.TKey)
  5  WHEN MATCHED THEN
  6  UPDATE SET main.ColA = subsel.a,
  7*            main.ColB = subsel.b
SQL> /

3 rows merged.

SQL> SELECT * FROM maintab;

TKEY                 COLA                 COLB
-------------------- -------------------- --------------------
rec1                 123                  456
rec2                 789                  321
rec3                 654                  987


Database Design - When to use "attributes" rather than additional columns

There are two schools of thought with this type of design:

The developer / DBA.
The users.

The Developer/DBA will want to create a system that self-maintains.  In other words precisely what you are pursuing above.

The Users will want a very simple system that they can understand without really understanding anything other than their day-to-day job.

You will be successfully fulfilling your role if you create a system that is easy to manage and not too difficult to learn.

My experience tells me normally the user wants a flat-file based system where they can easily see all the fields "they would ever need" on a single form, without having to design anything themselves.  As a developer/DBA I have created both flat-file based designs and highly normalized designs.  The flat-file designs were me deciding I could no longer tolerate trying to convince the user that they really did need to be able to extend their design without getting me to do the work in the future.  Needless to say, around 10 milliseconds after I put their "everything I'll ever need" system into production, they wanted to add some fields.  Me bitter?  Never.

If you can find a user that understands how to manage the normalized design you want to utilize, get them on your side and have them vocalize their concerns about being able to extend the design without having to involve I.T. for every minute field change.  Then build a really slick user interface that allows them to build the fields (attributes) they want.  Then build the back-end the way YOU want.  Don't make it so complex that if you put it down for 6 months you'll have a hard time understanding what you did.


Could not open file "pg_clog/0000": No such file or directory

If pg_clog is gone..... thats bad, this stores the commit log so the database knows what has and has not been committed.  I would start looking at backups now, or if the data is disposable and there is no backup then building a new cluster.  There are ways you can get the data out, but as far as I know they are very time consuming.

The bigger question though is what happened to your machine to cause this?


For the simple queries (without GROUOP BY), you can rewrite them as:

SELECT version
FROM versions
-- WHERE important = 1
ORDER BY version DESC
LIMIT 1 ;

and check if the execution plan does not do a table scan and using the index in the way that you expect.

Additionally, you can add an index on (important, version).

For the GROUP BY type queries, an index on (important, type, version) would fit better.

Rewriting that query with LIMIT 1 is not trivial as we want many maximum (one for each type). But if the index alone doesn't improve things much, here is a different query that will use the above composite index.

Performance is usually improved with this method when there are few distinct type values:

SELECT dt.type, v.version
FROM
    ( SELECT type
      FROM versions
      WHERE important = 1
      GROUP BY type
    ) AS dt
  -- LEFT
  JOIN versions AS v
  ON  v.important = 1
  AND v.type = dt.type
  AND v.version = ( SELECT vi.version
                    FROM versions AS vi
                    WHERE vi.important = 1
                      AND vi.type = dt.type
                    ORDER BY vi.version DESC
                    LIMIT 1
                  ) ;

There are variations you could try. For example, removing the important = 1  (only from inside the derived table dt) and using LEFT JOIN there. This would give you all types (and NULL values for the types that have no version marked as "important".

If you have a separate table, with all the types (i.e. PRIMARY KEY (type)), you could completely replace the dt with that table.


oracle streams - how to get last successfully applied scn for crashed apply?

Found it.

  Low-Watermark and High-Watermark for an Apply Process

  The low-watermark for an apply process is the system change number
  (SCN) up to which all LCRs have been applied. That is, LCRs that were
  committed at an SCN less than or equal to the low-watermark number
  have definitely been applied, but some LCRs that were committed with a
  higher SCN also might have been applied. The low-watermark SCN for an
  apply process is equivalent to the applied SCN for a capture process.

  The high-watermark for an apply process is the SCN beyond which no
  LCRs have been applied. That is, no LCRs that were committed with an
  SCN greater than the high-watermark have been applied.

  You can view the low-watermark and high-watermark for one or more
  apply processes by querying the V$STREAMS_APPLY_COORDINATOR and
  ALL_APPLY_ PROGRESS data dictionary views.


How do I check the connection timeout in Oracle?

These are properties of user profiles. Query DBA_PROFILES.

CREATE PROFILE

  resource_parameters

  ...

  CONNECT_TIME

  Specify the total elapsed time limit for a session, expressed in
  minutes.

  IDLE_TIME

  Specify the permitted periods of continuous inactive time during a
  session, expressed in minutes. Long-running queries and other
  operations are not subject to this limit.

  ...


Total Memory used by SQL Server (64 bit)

I'm not sure about a single view for memory, but you can get the information from two queries.  The first (taken from Glenn Berry's DMV queries) leverages sys.dm_os_buffer_descriptors and will show you buffer pool by database:

SELECT
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second query I put together by looking at sys.dm_os_memory_cache_entries to show me non-buffer pool information:

select
    name
    ,sum(pages_allocated_count)/128.0 [Cache Size (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0
group by name
order by sum(pages_allocated_count) desc

There are a host of DMVs related to memory, notated by dm_os_memory_*.  You can use these to investigate how memory is used by SQL Server on a variety of levels.


How to copy a SQL Agent Job on same Server?

I'm certain that this isn't the most elegant solution, but it will work. You'll need the dbatools PowerShell module, which you can install from the PSGallery with install-module dbatools or via the other methods at https://dbatools.io/install

Once dbatools is installed, from a PowerShell prompt/console, run the following:

Get-DbaAgentJob -SqlInstance YOURINSTANCE -Job "YOUR_SOURCE_JOB_NAME" | Export-DbaScript -FilePath c:\tmp\MyJobToCopy.sql

You can then open up that SQL file in Management Studio, change every instance of YOUR_SOURCE_JOB_NAME to the "new" name you want the copy of the job to have, and execute the SQL. Lather, rinse, repeat for the other 4 copies you need.

This could definitely be better as a self-contained script to do the name changes and query execution all in one go. But for a one-off, this will get you where you need to go.

If you were copying this job out to 5 different instances with the same name everywhere, it'd be a lot cleaner & easier thanks to Copy-DbaAgentJob (also from dbatools).


Restore job run successfully without error but actually not applied during log ship from older version to newer one

You cannot restore using STANDBY from a lower version. If that is what you mean nu "open these standby databases after a long time).

STANDBY implies a recovery, which would need to physically upgrade the database. An database cannot be downgraded again, which in turn would be necessary when restoring a subsequent log backup.

Timestamp of database files are irrelevant. Don't bother with those. They aren't updated when information inside the file is modified.

You can keep the database in a restoring state for a long time, i.e., log shipping doing restore using NORECOVERY. But you need to have a schedule between the three agent jobs so that the restore job actually have the backup file available. So, for instance backup every hour, copy file every hour, restore (NORECOVERY, of course) every hour. And then delete the backup files after, say 1 day. That way the files neeeded for the restore will be on disk.


MariaDB "EXECUTE AS" or User Impersonation

You can create a number of different roles and SET ROLE to switch between them.


If you have this scenario

table_a has an auto_increment field
table_b and table_c have table structure identical to table_a
You are running MySQL 5.1+

you can create a Stored Procedure and a MySQL event instead of a Trigger

For the sake of example, let's say table_a looks like this:

CREATE TABLE table_a
(
    id not null auto_increment,
    ....
    primary key (id),
);

Create a Stored Procedure on the Slave to Copy Data to table_b and table_c

DELIMITER $$
DROP PROCEDURE IF EXISTS `Load_New_Data` $$
CREATE PROCEDURE `Load_New_Data` ()
BEGIN
    DECLARE min_id,min_idb,mid_idc INT;

    SELECT MAX(id) INTO min_idb FROM table_b;
    SELECT MAX(id) INTO min_idc FROM table_c;
    SET min_id = IF(min_idb < min_idc,min_idb,min_idc);

    DROP TABLE IF EXISTS table_a_delta;
    CREATE TABLE table_a_delta SELECT * FROM table_a WHERE 1=2;
    INSERT INTO table_a_delta SELECT * FROM table_a WHERE id >= min_id;
    INSERT IGNORE INTO table_b SELECT * FROM table_a_delta;
    INSERT IGNORE INTO table_c SELECT * FROM table_a_delta;

    DROP TABLE table_a_delta;
END $$
DELIMITER ;

Then, schedule a MySQL Event on the Slave for the Stored Procedure to go off every 5 minutes starting 5 minutes after you create the event:

CREATE EVENT ev
    ON SCHEDULE
      EVERY 5 MINUTE
      STARTS (NOW() + INTERVAL 5 MINUTE)
    DO
      CALL Load_New_Data();

Don't forget to Add This to the Slave's /etc/my.cnf

[mysqld]
event_scheduler=1


You can shrink you database file using SQL Server Management Studio.

  In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  Expand Databases and then right-click the database that you want to shrink.
  Point to Tasks, point to Shrink, and then click Files.

Source: https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file

Please be advised, that the databse needs at least some free space to work. So don't shrink it all the way down to 20 GiB


Adding an identity field to be the new cluster of a large table - how do I get the "right" order?

I just performed the following experiment on a SQL Server 2012 box:

CREATE TABLE rdtest (label varchar(100), value int);

INSERT INTO rdtest VALUES
       ('one', 1)
      ,('two', 4)
      ,('three', 9)
      ,('four', 16)
      ,('five', 25)
;

ALTER TABLE rdtest ADD test_id int IDENTITY(1,1);

SELECT * FROM rdtest;

Result:

label    value       test_id
-------- ----------- -----------
one      1           1
two      4           2
three    9           3
four     16          4
five     25          5

Next:

ALTER TABLE rdtest DROP COLUMN test_id;

ALTER TABLE rdtest ALTER COLUMN value int NOT NULL;
GO

ALTER TABLE rdtest ADD CONSTRAINT PK_rdtest PRIMARY KEY ([value] DESC);

ALTER TABLE rdtest ADD test_id int IDENTITY(1,1);

SELECT * FROM rdtest;

Results:

label    value       test_id
-------- ----------- -----------
five     25          1
four     16          2
three    9           3
two      4           4
one      1           5

And, one more test run:

ALTER TABLE rdtest DROP CONSTRAINT PK_rdtest;

ALTER TABLE rdtest DROP COLUMN test_id;

ALTER TABLE rdtest ALTER COLUMN label varchar(100) NOT NULL;
GO

ALTER TABLE rdtest ADD CONSTRAINT PK_rdtest PRIMARY KEY ([label] ASC);

ALTER TABLE rdtest ADD test_id int IDENTITY(1,1);

SELECT * FROM rdtest;

Results:

label    value       test_id
-------- ----------- -----------
five     25          1
four     16          2
one      1           3
three    9           4
two      4           5

I suppose this isn't a guarantee that the clustered index dictates the order a new IDENTITY column gets filled in - but, I'd say it strongly indicates that it's very likely.


Postgres - Get the query cost assign to a variable

Based in this answer [https://pt.stackoverflow.com/questions/155113][1] :

CREATE or REPLACE function custo_consulta(_consulta text[])
returns table (consulta text, custo_execucao numeric, tempo_execucao numeric ) as '
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute ''EXPLAIN ( FORMAT JSON, ANALYZE ) '' || consulta INTO custo;
        custo_execucao := split_part(split_part(custo, ''"Total Cost": '', 2), '','', 1);
        tempo_execucao := split_part(split_part(custo, ''"Actual Total Time": '', 2), '','', 1);
        return next;
    end loop;
end;
' language plpgsql;

SELECT *
FROM custo_consulta(array['SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''OPORTUNIDADE''', 'SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''REVISAO'''])
ORDER BY custo_execucao desc;

Result

 consulta                                                               custo_execucao     tempo_execucao
 SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'REVISAO'       38426,44           128,267
 SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'OPORTUNIDADE'  38252,65           123,996

Just another problem, i am receiving an error when using $$, but this will be solved.


CDC error - Error converting data type varchar to uniqueidentifier

I think its the same as this

But I was upgrading to SP2 CU7.  Anyway I disabled and re-enable the CDC and the log reader agent resumed.


Aside from what maialithar said, you can also check the alert_yourSID.log or the listener.log