mysqldump: Does --lock-tables=false work with MyISAM tables?

Using lock-tables=false becomes irrelevant given MyISAM's table locking approach to DML.
You asked

My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?

Yes, you are correct. Furthermore, when it comes to XtraBackup and MySQL Enterprise Backup, there must be a FLUSH TABLES WITH READ LOCK if MyISAM is present.
Oracle and I recently had dialogue. They asked me to change everything to InnoDB in order to use Enterprise Backup. When I explained I had 40GB and 2TB MyISAM (most of the tables had hundreds of partitions), they understood. I still that after all these years, you must still take matters into your own hands for point-in-time consistency with MyISAM.
SUGGESTION
Your best solution would be to use MySQL Replication and run the backups from the Slave
I have suggested this before

Oct 01, 2011 : Quick MySQL Backup
Dec 05, 2011 : Set up a MySQL slave using mysqldump to get the initial data

You may want to use binlog_format=ROW on Master and Slave to catch granular changes before running STOP SLAVE; FLUSH TABLES; on the Slave. Then, you can mysqldump from the Slave without worrying about any Storage Engine locking or consistency issues.
Give it a Try !!!


MySql - partition by range and unique key

Then error message itself explains the problem. Please, read the manual,
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-k...:

"The rule is : All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
In simple words, every unique key on the table must use every column in the table's
partitioning expression."


Why do function inputs need an extra set on parens in this Postgres function?

You need the extra parentheses because your function epochseconds() is defined to accept a single value, a select returns a set of rows, the parentheses indicated that the select only returns a single row and column (a scalar value).

But there is no need for that, just pass the max value directly to the function:

create function tablerowepochseconds(integer) returns int as $$
  select epochseconds(max(baz.created))
  from baz
  where baz.id = $1;
$$ language sql;


How to configure/setup data at rest encryption in Couchbase 5.1

See also the Field Level Encryption feature in Couchbase Server 5.5 Enterprise Edition (released last week).  From the docs there, you can select the platform you use.  Note this is not part of Couchbase Mobile (which as of this writing has encryption planned for the 2.1 Couchbase Lite release), but can obviously be used with mobile apps if crypto at the app server works for you.  There's also a specification for how the fields are handled if you wanted to handle it elsewhere.


Oracle's GV$SQL_MONITOR - Uniquely identify execution of a SQL statement

Let's create the below function so we can have a long running parallel query and have time to query SQL monitoring data:

create or replace function f1 return number parallel_enable as
begin
  dbms_lock.sleep(1800);
  return 1;
end;
/

Then this table:

create table t1 as select * from dual connect by level <= 4;

And:

alter session force parallel query parallel 4;

And start this query:

select f1 from t1;

If you query GV$SQL_MONITOR, with some additional columns, you can see it is a parallel query, and the sessions it's spawned, and also find the session that originally started it (QCSID):

select sql_exec_start, sql_id, sql_exec_id, sid, px_qcinst_id, px_qcsid from gv$sql_monitor
where status = 'EXECUTING';

SQL_EXEC_START      SQL_ID        SQL_EXEC_ID        SID PX_QCINST_ID   PX_QCSID
------------------- ------------- ----------- ---------- ------------ ----------
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        270            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216         20            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        400            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        134            1        262


There is no way for non-dba sessions to kill other sessions, or interfere with their locks in any way. Think about it. That would be a security disaster.

You need to issue your commit or rollback in the same session that launched the query. If you lose the session, you must ask your DBA to kill it for you.


Consolidating multiple related queries, each using different window and/or criteria for counting records

When you think that optimizer should be able to combine two CTEs into one why don't you write a single CTE, human beings are still smarter than optimizers :-)

As there's only a different WHERE-condition the CTEs 2&4 and 1&3 can be combined using conditional aggregation:

WITH CTE1
AS (SELECT
  COUNT(DISTINCT Data.EventID) AS n__,
  COUNT(DISTINCT CASE WHEN TestResults.CompanyResults = 1 THEN Data.[EventID] END) AS n1_,
  TestResults.TestName
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName),
CTE2
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n_1,
  COUNT(DISTINCT CASE WHEN TestResults.CompanyResults = 1 THEN Data.[EventID] END) AS n11,
  TestResults.TestName,
  WordEventMap.Word
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
INNER JOIN WordEventMap
  ON Data.[EventID] = WordEventMap.EventID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName,
         WordEventMap.Word)
SELECT
  CTE2.TestName,
  CTE2.Word,
  n11,
  n1_,
  n_1,
  n__
FROM CTE2
INNER JOIN CTE1
  ON CTE2.TestName = CTE1.TestName
WHERE n11 > 0
;

But the main performance problem are those DISTINCTs, based on uniqueness in your actual data you might be able to remove some (for your example data no DISTINCT is needed).


MongoDB script to backup replication set locally to a ubuntu 14.04 server

You can execute mongodump from the remote server as mentioned below:

mongodump --host <ip-of-mongodb-server> --port <port-number-of-mongod> --out <some-directory>

This is required mongo server to be accessible from the remote server.

This dump file can be then compressed and archived on remote host or s3.

If the mongo server is not accessible from remote host, in that case you can execute mongodump on local mongodb server and copy it to remote or local share.


Is it possible to get rid of so-called 'Spurious Tuples' completely?

This is a great question.  Normalization beyond BCNF is extremely hard to understand.  Hopefully I can provide an answer that makes sense.  I struggled with these concepts for over 20 years before finally making sense of them thanks to Fabian Pascal's Practical Database Foundation Series.

The example provided is an EmpRoleProj R-table that looks like so:

It then goes on to show projections of the original EmpRoleProj R-table like so:

The reason you don't see anything wrong with the base tables Table 1 and Table 2 is that you aren't considering the dependency rules (in this case multivalued dependency (MVD) rules) defined in the business model describing the business rules. If we assume for the sake of example there are no MVDs defined in the business rules then EmpRoleProj is in 5NF despite the "appearance" of redundancies.  It appears for example that the information that Smith is a designer is stored redundantly.  It also appears that the information that a designer is needed on the Amazon project is stored redundantly.  While this appears to be the case, by learning that in fact these are not MVDs, what is actually the case is that Smith happens to be a designer on a few projects, but it is not a fact that Smith is a designer and thus that fact should not be inferred.  When table 1 and table 2 are joined, the result:

shows Jones to be a designer on the Nile project but we know that is not the case.

Let us assume instead the business model did say there were MVDs of empName-->>role and role-->>projName.  In this case, what those MVDs mean is that if an employee plays a role, and if that role is on a project, by definition that employee plays that role on that project.  In this example, that same EmpRoleProj table is now not in 5NF and now does suffer from redundancy.  Now, the facts that Smith is a designer and there is a need for a designer on the Amazon project are stored redundantly as those facts could be inferred from joining Table 1 and Table 2!  Likewise, taking the join of Table 1 and Table 2 now does not result in a spurious tuple as the inference that Jones is a designer on the Nile project is a fact now based upon the business rules defined by the MVDs.

This is why you cannot assess the normal form of any R-table without knowing the dependencies and the defined key.  Making any assumption, even one that seems to you to make sense, can be dangerous.  If you are ever asked what normal form an R-Table is in, you must ask for the dependencies to assess.  In addition to Fabian's series of papers, Chris Date's works provide the best information available on normalization theory.


Can you Schedule a job inside another job?

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job

E.g. The first job has a schedule and  final step of the job is to run:

exec msdb.dbo.sp_start_job N'My Second Job';

"My Second Job" doesn't have a schedule, relying on the first job to start it.


Creating a read only replica of SQL Server database for reports

Does Transactional replication suit my problem the most among SQL
  Server replication mechanisms?

Out of all of the replication options - Transactional sounds like it is what helps you the most here. It gives the minimal latency and it doesn't need to be (but can be) bidirectional. You aren't merging changes and don't need Peer to Peer.

That said - Replication isn't "free" -  you need to administer and monitor it and a poor setup could make performance problems worse. You should learn about the best options for performance and the best setup for performance and keep that in mind and keep your replication environment monitored.

Transactional replication is "easier" than Merge replication. But that doesn't mean you will always have a smooth sail and can always get away without someone with replication experience helping out.

  If I have an opportunity to migrate from SQL Server 2008 to SQL Server
  2012 would transactional replication have some breaking changes? I've
  read an article on technet that there might occur errors?

Unfortunately this is an "it depends" type of question. We could theoretically give you answers from our own experiences, but you will need to test. If you do bump into issues they will not necessarily be impossible issues that you cannot fix, they will require testing and tweaking.

  Isn't SQL Server 2012 Always On mechanism better to solve my
  problem(I'm considering this option as last one, as I'm still using
  2008 R2, and yet migration to 2012 is planned by not soon)?

In SQL Server 2012 AlwaysOn has something for you here that works potentially. Availability Groups are an answer here with the concept of an Active Secondary for reading purposes.  But there are licensing concerns - you need to be in Enterprise on both the primary and the read only/active secondary. With the replication approach you still need to be licensed on both ends, but this is a good option for sure. It, too, has overhead associated and some administrative concerns that you should learn about - but it makes a great option.


How to make MySQL query performant WHERE one column uses greater than and one column uses less than?

Having two "ranges" is not easy to optimize.  This may help:
INDEX(foo_id, enabled, start_time, end_time)
INDEX(foo_id, enabled, end_time, start_time)

(As you said...)  The optimizer will pick between those two "composite" indexes based on statistics that may tell it which range is shorter.  It will quickly check through the two = tests (foo_id and enabled; btw, they can be in either order).  Then it will scan through the 3rd value.  The final column is there in hopes that "Using index condition" (aka 'ICP') helps.
The reason for having 2 indexes -- the length of one of the scans is likely to be less than the other.
Is that the entire query?  Anything added may throw a monkey wrench into the analysis.
I don't think the time syntax with 'T' and 'Z' will work in MySQL.
A more complex solution...  Can the start_time..end_time ranges overlap?  If not, an optimization can take advantage of that.  http://mysql.rjweb.org/doc.php/ipranges


Select All Field (if field not exist, make field with null value)

Your issue is you're using an INNER JOIN (JOIN for short) between the same ticket_id of your cc_queue_detail table a1, and two different sets of rows from your second and third instance of the cc_queue_detail table, a2 and a3. This basically is saying the ticket_id has to match between a1, a2, and a3 always, but because of your WHERE clause, that's not possible, since a2 and a3 are different sets of rows from your cc_queue_detail table.
You'll want to use an OUTER JOIN, such as a LEFT OUTER JOIN (LEFT JOIN for short) to accomplish your goal, something like this:
SELECT DISTINCT
    a1.ticket_id AS 'ticket_id',
    a2.data_content AS 'category_ticket_1',
    a3.data_content AS 'category_ticket_2'
FROM cc_queue_detail a1
LEFT JOIN cc_queue_detail a2
    ON a1.ticket_id = a2.ticket_id
    AND a2.field_name_id = "category_ticket_1"
LEFT JOIN cc_queue_detail a3
    ON a1.ticket_id = a3.ticket_id
    AND a3.field_name_id = "category_ticket_2"

Now you'll get all rows back from a1, your entire cc_queue_detail table, and where there isn't a match between the other instances of the table (a2 and a3) there will be NULL values.


Newer Version of SQL Server Less Stable?

You obviously don't need my confirmation but I can't think of a single possible reason why I would suggest to a client they go with 2012 over 2014. Particularly for support / end of life reasons, but also because of additional features and enhancements to existing capabilities.

Some reasons you might hear:

Performance degradation. Sure, this can happen, but it can also happen when going to 2012 or making any other major change (some of the reasons can also be attributed to migration without upgrade, failover, etc). Regressions are an expected part of change, and it's easy to blame an upgrade (just ask the VSTS team).
Higher cost. I've seen a few claims that 2014 costs more than 2012 for the same configuration, but haven't observed any licensing changes that would cause that to happen except that standby secondaries now require software assurance to be licensed. The other aspect is if someone without SA planned poorly and bought 2012 and now wants 2014 - yeah, that's going to cost them.

Generally worse performance? Less stability? No, both seem laughable to me. Ask your drunk friend for some real evidence, and maybe keep him distanced from clients, too. :-)


Split SSIS project in to multiple files to avoid merge hell

I've been using subpackages successfully on multiple projects. The typical scope is one sub-package per target table.  I also have a few utility packages eg Disable Indexes, Rebuild Indexes (on a table, passed as a parameter). A single "Master Control Package" is mostly Execute Package tasks.

With the Project Deployment method, devs need to be aware that prematurely checking in their changes to a single package can block the deploy (it wont deploy a project until all the packages validate cleanly).


How to Convert a Table into XML Document

The easiest way to do this is:

select dbms_xmlgen.getxml( 'select * from dept') as xml
from dual;

There are other way more complicated ways of achieving similar results, but the answer would be significantly longer!


Is it possible to view the maintenance plans created in SQL Server 2000 in SSMS 2005?

Figured it out. It's under Legacy->Database Maintenance Plans. Also all the plans, optimizations, integrity, trans log and complete backup are bunched up under one title.


help in multi-language medical database?

How can i put all these complexed parts in one DB with different languages??

Define the requirements

I can give you an example of how to model multilingual name data by assuming these business requirements:

The database should store the names of the ingredients of drugs in English, French and Spanish, and more languages yet to be defined.
Each ingredient is known by one name in each langauge.

Identify the languages

You need a way to identify langauges in your database so that you can associate drug names with a particular langauge.

A simple standard for identifying languages is ISO 619-1, which defines a two-letter code for the world's most common languages. For example, the code for English is en, French is fr, and Spanish is es.

The following SQL creates a table with a language code column, then populates the table with the example languages:

CREATE TABLE Languages (
  LanguageCode CHAR(2) NOT NULL,
  CONSTRAINT PK_Languages PRIMARY KEY (LanguageCode),
  CONSTRAINT CK_LowerAlpha CHECK (LanguageCode NOT LIKE '%[^a-z]%' COLLATE Latin1_General_BIN2)
);

INSERT INTO Languages (
  LanguageCode
)
VALUES
  ('en'),
  ('fr'),
  ('es');

There is nothing in your entity model that corresponds to this table, which could explain why you found it difficult to model multiligual data.

Identify the chemicals

Next you need a way to identify the chemicals that compose each drug.

A common way to identify chemicals is CAS registry numbering, which defines a numerical code for every chemical known to Science. For example, the code for water is 7732-18-5, formaldehyde is 50-00-0, and mustard oil is 8007-40-7.

The following SQL creates a table with a chemical code column, then populates the table with the example chemicals:

CREATE TABLE Chemicals (
  ChemicalCode VARCHAR(12) NOT NULL,
  CONSTRAINT PK_Chemicals PRIMARY KEY (ChemicalCode),
  CONSTRAINT CK_Dashes CHECK (ChemicalCode LIKE '%_-__-_'),
  CONSTRAINT CK_Digits CHECK (REPLACE(ChemicalCode, '-', '') NOT LIKE '%[^0-9]%')
);

INSERT INTO Chemicals (
  ChemicalCode
)
VALUES
  ('7732-18-5'),
  ('50-00-0'),
  ('8007-40-7');

The Chemicals table roughly corresponds to the Drugs_Ingredients entity in your diagram, but lacks a column for the name.

For simplicity, the table does not enforce the checksum constraint. You should enforce the checksum constraint before using CAS registry numbers in production.

Identify the chemical name

If you were to store the name in a column in the Chemicals table, it would be difficult to support more than one language. You could have one column per langauge, but you would have to modify the schema every time you wanted to support a new langauge.

To free yourself from these limitations, you should model the chemical name as an identifiable entity in itself. You can identify the chemical name using a compound identity of the chemical code and the language code.

The following SQL creates a table with columns for the chemical code, the language code, and the checmical name, then populates the table with one name in each language for each chemical:

CREATE TABLE ChemicalNames (
  ChemicalCode VARCHAR(12) NOT NULL,
  LanguageCode CHAR(2) NOT NULL,
  ChemicalName NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_ChemicalNames PRIMARY KEY (ChemicalCode, LanguageCode),
  CONSTRAINT FK_ChemicalNames_ChemicalCode FOREIGN KEY (ChemicalCode) REFERENCES Chemicals(ChemicalCode),
  CONSTRAINT FK_ChemicalNames_LanguageCode FOREIGN KEY (LanguageCode) REFERENCES Languages(LanguageCode)
);

INSERT INTO ChemicalNames (
  ChemicalCode,
  LanguageCode,
  ChemicalName
)
VALUES
  ('7732-18-5', 'en', 'Water'),
  ('7732-18-5', 'fr', 'Eau'),
  ('7732-18-5', 'es', 'Agua'),
  ('50-00-0', 'en', 'Formaldehyde'),
  ('50-00-0', 'fr', 'Méthanal'),
  ('50-00-0', 'es', 'Formaldehído'),
  ('8007-40-7', 'en', 'Mustard oil'),
  ('8007-40-7', 'fr', 'Huile de moutarde'),
  ('8007-40-7', 'es', 'Aceite de mostaza');

The ChemicalName column is of type NVARCHAR so that it can store any Unicode string. You should always use Unicode in international applications to store text so that you can handle the writing systems of different langauges in a uniform way.

Query the chemical names

I can think of two questions that you might reasonably ask of your database. With the ChemicalNames table, you can answer both of them with simple queries.

What are the names of all the chemicals in Spanish?

Query:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'es';

Result:

CHEMICALCODE    CHEMICALNAME
50-00-0 Formaldehído
7732-18-5   Agua
8007-40-7   Aceite de mostaza

What is water called in each langauge?

Query:

SELECT LanguageCode, ChemicalName
FROM ChemicalNames
WHERE ChemicalCode = '7732-18-5';

Result:

LANGUAGECODE    CHEMICALNAME
en  Water
es  Agua
fr  Eau


Disk space unreleased after cleaning up rows from PG table

From the docs,

  In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

At all. Never. There are no exceptions. When ever you delete a row, you essentially mark it as inactive for your snapshot. Other snapshots started by prior transactions can still see it. This means it's still on disk.

Rows aren't stored in their own file on disk. In order to delete that row, even when you want, you have to essentially rewrite the files stored on disk without that row. DELETE does not do this and under normal circumstances that's perfectly fine because dead rows that aren't visible to a transaction can be reclaimed internally by  a simply VACUUM. After being marked for reuse new data can be stored in the heap.

So essentially, only operations that internally cause the table's heap to be rewritten reclaim space. Those operations are:

CLUSTER
VACUUM FULL
TRUNCATE
ALTER TABLE (forms that rewrite the table)

All of these operations require an ACCESS EXCLUSIVE lock. And, TRUNCATE is such a sledge hammer it even violates MVCC.

  @EvanCarroll: and so technically, I can actually go out of diskspace even after having removed a ton of rows here? – Hassan Baig 1 min ago

Yea, really and technically and everything else, but let's make one special caveat to the above. If you DELETE a bunch of rows and commit they get marked as inactive. When you VACUUM the table's heap space gets reclaimed. From that point, to run out of disk space you to either

Exhaust that reclaimed space on that table's heap because PostgreSQL will just store stuff in that space.
Or, exhaust a different table's heap.

But yes, VERY bad things can happen when you exhaust your disk space. And, they'll happen with every MVCC database vendor. Simply, don't run of disk space.


Did you try sphinxsearch (http://sphinxsearch.com/)?
It is search engine, but not only. It can query very fast with group by, order, filters. Mysql should be good for fast writes with per-table and per-host sharding.

You can split your sphinx index to several parts and use distributed search:

http://sphinxsearch.com/docs/2.2.2/distributed.html

Partitioning is done manually. You should

setup several instances of Sphinx programs (indexer and searchd) on different servers;
make the instances index (and search) different parts of data;
configure a special distributed index on some of the searchd instances;
and query this index.

When searchd receives a query against distributed index, it does the following:

connects to configured remote agents;
issues the query;
sequentially searches configured local indexes (while the remote agents are searching);
retrieves remote agents' search results;
merges all the results together, removing the duplicates;
sends the merged results to client.


It's binary data with some additional Hex characters for padding, etc per the Microsoft Docs: binary and varbinary

Padding is achieved by using hexadecimal zeros.


Fire a trigger function when checks change

You can use the event trigger machinery to achieve your goal.

Say, you have a table in the noalter schema, and want to show which modifications took place.  First, you need an event trigger function:

CREATE OR REPLACE FUNCTION test.tr_alterbla()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
DECLARE obj record;
        q text;
BEGIN
    q := current_query();
    RAISE WARNING '%', q;
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE EXCEPTION 'This happened: % %', tg_tag, obj.objid::regclass;
    END LOOP;
END;
$function$
;

For testing, this will prevent altering tables by raising an error, in which telling the type of operation and the table name.  Additionally, it will emit the query that initiated the table change.  While the obj record would return everything about the triggering query in its command column, there is no out-of-the-box way to inspect it deeper (or just show it), so you have to parse the output of current_query(), unfortunately.

With all this, you should be able to change the function body to do your UPDATE.

Now what remains is creating the event trigger:

CREATE EVENT TRIGGER show_alter
    ON ddl_command_end WHEN TAG IN ('ALTER TABLE')
    EXECUTE PROCEDURE tr_alterbla();

This reduces the scope of the trigger to ALTER TABLE only.  Unfortunately, there is no way of further focusing on a single table - that's why the obj.objid::regclass bit is present in my example function.

Note: if you are really changing functions, you have to specify CREATE FUNCTION in the WHEN clause above.  I'm afraid in this case it will be (much) harder to figure out which check of which table is affected - that might be worth a separate question here.


The best way to find out will be to run some known code samples with and without using the profiler. The differences normally should not be much but indeed can vary. Even dbms_application_info can hurt when put inside a high frequent loop.


Assuming you have access and sufficient rights to the database on the SQL server via SSMS.
Create a shared folder on a different network server 192.I.Have.Access.  Grant the SQL engine and agent accounts read and write access to the shared folder.
You can take backups to the share (with write access) and you can restore backups from the share (with read access).  Direct access to folders on the server the SQL instance is installed on is not required.
BE-AWARE If someone besides you is responsible for SQL backups on the server.  Taking backups and/or restoring database can and probably will break the backup chain.  This will make disaster recovery for some period impossible, understand the implications to disaster recovery in your organization before making a one off backup or doing a restore.

If the only copy of the backup you want to restore is on the server in a folder you can not access; Congratulations you just learned why it is important copy backups to a safe location on a different server for disaster recovery.  I hope the lesson has not been overly expensive.


Permissions are enough to handle this requirement: just GRANT permissions on the wrapper stored procedure and you should be fine. Don't GRANT permissions on the Internal_* stored procedures and users won't be able to execute them.

Example:

USE tempdb;
GO

CREATE USER ZeroCool WITHOUT LOGIN;
GO

CREATE PROC Select1
AS
SELECT 1
GO

CREATE PROC Select2
AS
SELECT 2
GO

CREATE PROC SelectAll
AS
EXEC Select1
EXEC Select2
GO

GRANT EXECUTE ON object::SelectAll TO ZeroCool
GO

EXECUTE AS USER = 'ZeroCool'

-- Works
EXEC SelectAll
-- Fails
EXEC Select1

REVERT
GO


Is Tablespace something like Logical Volume?

Historically, PostgreSQL stored tables and indexes in individual files.  Tablespaces are a means of placing multiple tables/indexes into a single file or group of related files in the same directory.  Other database management systems use similar techniques, although tney can store multiple objects within a file.  On PostgreSQL a tablespace is implemented as a directory that individual files are placed in, but the files are still one-per-database-object.

A table must reside in a single tablespace unless it is partitioned, in which case each partition must reside on a given tablespace.  On some DBMS platforms you could add files to a tablespace as you suggest, although in the case case of PostgreSQL a tablespace is essentially just a directory for grouping files together.  On systems where this is possible it could result in suboptimal I/O performance as individual disks could contain I/O hot spots.  Probably, you would be better off setting multiple disks up in a RAID volume and placing the tablespace on that.

Depending on your workload you may wish to set up individual disk volumes with tablespaces on each volume and distribute parts of your database over the different volumes.  The most trivial case is separating database logs onto a separate volume, but you could distribute data over multiple volumes for various reasons.

EDIT: Here is a link on PostgreSQL tablespaces.


Entity Relationship Diagram - Final Physical Data model Review

Yes, aircraft_id should be a FK to the Aircraft_ table, just as pilot_id should refer to the Pilot_ table. There are other problems, such as there is no way to limit pilots to only aircraft they are rated to fly. Any pilot may be thrown in with any aircraft. And not all airports can handle all aircraft. Many cannot handle the larger aircraft (know as heavies). One of the attributes I would think important for airports is how many heavy-capable runways they have. Come to think of it, how many runways total.

Look into other relationships. Write them out in plain English, such as: "Each aircraft has one or more owners." This shows a relationship between aircraft and owners that is not represented in your schema design.

The actual_flight_ table relates a pilot with an aircraft, thus the need for FKs, and other attributes into a, well, actual flight. This can also be stated in plain language so you can understand the entities and their relationships.


Help writing a query that select the min column in a group by

You can try this:

SELECT
    u.id,
    u.nickname,
    t.minPrice
    FROM
      user u
      LEFT JOIN user_service us ON us.user_id = u.id
      INNER JOIN (
    SELECT user_id, MIN(price) minPrice
        FROM user_service
        GROUP BY user_id
    ) t ON t.user_id = u.id AND us.price = t.minPrice
    WHERE us.service_type IN ('TYPE1','TYPE2') and u.id=1
   group by u.id;


most effective architecture to apply mulitple functions

I have full control over the architecture of the source table (as I create it in an earlier process). Is there anything I can do with the source tables (index, partitions, etc.) that will help the CTAS run more efficiently?

Optimizing a CTAS is generally the same as optimizing the SELECT part of the CTAS. Run an explain on it to see what it's doing. The CREATE part is just sequentially writing the output of the SELECT so it wouldn't be the cause of slowness.

  Should I be using a CTAS at all or would an UPDATE be more efficicent (my gut here says no way, but I'd like some validation)

It depends on the size of the UPDATE. If the target table is indexed and you have few updates then the amount of work to do the UPDATE would be less. Specifics matter here as there's not general rule for which is faster. For an extreme example, if you have 100M rows and you're only changing one row via and indexed UPDATE, the UPDATE would clearly be faster.

In the end what counts is the total amount of I/O operations that will happen (both reads and writes). You can either analyze it by studying the EXPLAIN plans or test both approaches to get stats on which takes longer.

  Is there something else I can do to the functions to make them more efficient?

Well you could remove them entirely. Switching between SQL and PL/SQL is fairly quick and isn't really much of a bottle next if it's in the SELECT columns. If it's part of the WHERE clause and you're filtering a column on a the result of a PL/SQL function it can be very slow because they're usually not indexed (though Oracle does allow for indexes on deterministic functions).

If it makes logical sense, you can mark the function(s) as DETERMINISTIC. This tells Oracle that the result will always be the same for the same inputs (like a mathematical function, e.g. x^2). This means Oracle can cache the results between function calls. That may speed things up.

You can also try wrapping the function calls in a CASE/WHEN block to only call the function when it's not null. Again I don't think it makes that much difference as SQL -> PL/SQL context switches are pretty fast but it's something to try out:

CASE
    WHEN my_column IS NULL THEN NULL
    ELSE my_func(my_column)
END AS result_column

  Is there anything else I should be thinking about?

If the functions are querying lookup tables (ex: fn_country_name(country_cd IN VARCHAR2)) then you can consider replacing them with outer joins to the lookup tables directly. That should be faster than a function call as the optimizer can use a hash join (vs. a nested loop if it's in the function call).

Finally if things are really slow/confusing it's good to take a step back and see what the process is supposed to do end to end. You may need to go back a step in your processing chain to make things more efficient.


If this were me, I'd use SSIS and import everything into a DW locally. Then you can do whatever you want/need with minimal impact to all of those remote systems.


Easiest Way to Migrate data from MySQL to MongoDB

Migrating between a RDBMS and a NoSQL DB is not the easiest thing.
If you can dump your data to JSON (or XML), I know that Navicat for MongoDB can import data in either of those formats.

I know that MySQL workbench can export in JSON format.  You might also want to try a Navicat for MySQL.  I use it and I know that it can export to either of those formats.

Navicat products aren't free, but both come with a 14 day trial.  That should be plenty long enough to get the job done.

Let me know how that works for you!

Dave


Yet another grouping or window. It is a bit longer due SQL Server 2008 doesn't support LAG functions and it must be simulated.

-- add a row number to simulate a lag function
with a as
(
    select [key], value, recorddate, row_number() over (partition by [key],value order by [key], value, recorddate) rn
    from ratetable
    where value = 'abc'
)
  -- set reset points
  , b as
  (
      select t1.[key], t1.value, t1.recorddate, t2.recorddate rd2,
             case when t2.recorddate is null
                       or t1.[key] <> t2.[key]
                       or datediff (day, t2.recorddate, t1.recorddate) > 1 then 1 end is_reset
      from a t1
      left join a t2
      on t2.[key] = t1.[key]
      and t2.rn = t1.rn -1

  )
    -- generate groups
    , c as
    (
        select [key], [value], recorddate, count(is_reset) over (order by recorddate) grp
        from   b
    )
    -- max/min of each group
    select [Key], min(recorddate) as StartDate, max(recorddate) as EndDate
    from c
    group by [Key], grp;

GO

Key | StartDate           | EndDate
--: | :------------------ | :------------------
  1 | 01/01/2012 00:00:00 | 02/01/2012 00:00:00
  1 | 05/01/2012 00:00:00 | 05/01/2012 00:00:00
  1 | 08/01/2012 00:00:00 | 08/01/2012 00:00:00
  2 | 10/01/2012 00:00:00 | 12/01/2012 00:00:00

Warning: Null value is eliminated by an aggregate or other SET operation.


How to find the AD user that logged in to SQL Server using SQL Login

It's not possible I'm afraid.  This is one of the down sides of using SQL Server logins.  The only thing you can do is to look at the Hostname.  Most people connect using their local machine and if your company has a list of user to machine name then you can track them that way.

Unfortunately this information is not normally logged.  You will have to set up a trace, extended events session or server audit and collect login events and make sure you include the hostname property.


Disable all constraints and table checks while restoring a dump

So you look up other tables in a CHECK constraint.
CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction in the manual:

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

Still, expressions in CHECK constraints are allowed to use functions, even user-defined functions. Those should be IMMUTABLE, but Postgres does not currently enforce this. According to this related discussion on pgsql-hackers, one reason is to allow references to the current time, which is not IMMUTABLE by nature.
But you are looking up rows of another table, which is completely in violation of how CHECK constraints are supposed to work. I am not surprised that pg_dump fails to provide for this.
Move your check in another table to a trigger (which is the right tool), and it should work with modern versions of Postgres.
PostgreSQL 9.2 or later
While the above is true for any version of Postgres, several tools have been introduced with Postgres 9.2 to help with your situation:
pg_dump option --exclude-table-data
A simple solution would be to dump the db without data for the violating table with:
--exclude-table-data=my_schema.my_tbl

Then append just the data for this table at the end of the dump with:
--data-only --table=my_schema.my_tbl

But complications with other constraints on the same table might ensue. There is an even better solution:
Solution: NOT VALID
Up to Postgres 9.1, the NOT VALID modifier was only available for FK constraints. This was extended to CHECK constraints in Postgres 9.2. The manual:

If the constraint is marked NOT VALID, the potentially-lengthy
initial check to verify that all rows in the table satisfy the
constraint is skipped. The constraint will still be enforced against
subsequent inserts or updates [...]

A plain Postgres dump file consists of three "sections":

pre_data
data
post-data

Postgres 9.2 also introduced an option to dump sections separately with -- section=sectionname, but that's not helping with the problem at hand.
Here is where it gets interesting. The manual:

Post-data items include definitions of indexes, triggers, rules, and
constraints other than validated check constraints. Pre-data items
include all other data definition items.

Bold emphasis mine.
You can change the offending CHECK constraint to NOT VALID, which moves the constraint to the post-data section. Drop and recreate:
ALTER TABLE a
  DROP CONSTRAINT a_constr_1
, ADD  CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID;

A single statement is fastest and rules out race conditions with concurrent transactions. (Two commands in a single transaction would work, too.)
This should solve your problem. You can even leave the constraint in that state, since that better reflects what it actually does: check new rows, but give no guarantees for existing data. There is nothing wrong with a NOT VALID check constraint. If you prefer, you can validate it later:
ALTER TABLE a VALIDATE CONSTRAINT a_constr_1;

But then you are back to the status quo ante.


How to tell MySQL to ignore all indexes for a given query?

After a table name add USE INDEX() without arguments. That will make MySQL to ignore all index in the table.

Example:

SELECT SQL_NO_CACHE field_a, field_b FROM table USE INDEX() WHERE xxx;

Sorry for my english.


Unable to fix ROW size too large even with innodb_file_per_table

Set the following variable in your MySQL conf file and try

innodb_strict_mode=OFF


Defragmentation of Indexes - Higher value after defragmentaion

For indexes with small page_count this vaue is normal. Page_count can be seen from sys.dm_db_index_physical_stats. If page_count > 1000 only then go for index rebuild or reorganize. Sometimes when you rebuil index and use lower value of fill factor more pages are required and fragmentation might occur. Also when page count is low allocation to new pages are not done from uniform extent but from mixed so chances are that fragmentation will occur.But such fragmentation will not hamper any activity

If you are rebuilding index with ONLINE=ON option and used MAXDOP hint there can be more fragmentation as pointed in article from Bob Dorr from Microsoft have a look at this

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

PS: If you have small page count no need to worry


How to insert values in mysql taking previous row into account

In any case you'll need some sort of sequence. As MySQL does not provide such things, as far as i know, the only resort is an AUTO_INCREMENT column. Put some in a table abused as a sequence. Than have a BEFORE INSERT FOR EACH COLUMN trigger on your game table. Inside it, insert into the sequence table, so that a new incremented value is created. Select the maximum value of the sequence table. You can now calculate your values for player end round:

Let v be the selected value from the sequence table. Then the player p = (v - 1) mod 2 + 1 and the round r = ceil(v / 2).

But be aware, that deleting rows from your games table will not reset the sequence table. The pattern will continue on where it left the last time an insert into the games table was made.

Example:

CREATE TABLE game (id INTEGER AUTO_INCREMENT,
                   player INTEGER,
                   round INTEGER,
                   PRIMARY KEY (id));

CREATE TABLE game_sequence
             (value INTEGER AUTO_INCREMENT,
              PRIMARY KEY (value));

DELIMITER $$
CREATE TRIGGER game_ai
               BEFORE INSERT
               ON game
               FOR EACH ROW
BEGIN
  DECLARE _value
          INTEGER;

  INSERT INTO game_sequence
              VALUES ();

  SELECT max(value) INTO _value
         FROM game_sequence;
  SET NEW.player = (_value - 1) % 2 + 1;
  SET NEW.round = ceil(_value / 2);
END;
$$

DELIMITER ;


Should I use a compound or surrogate PK in a many-to-many table?

FWIW ISBN is terrible as a primary key. For one, what happens if you get a book you want to put up for pre-order, but the ISBN hasn't been assigned yet? What happens when the ISBN changes (yes, this happens!)? What happens when they change the ISBN format yet again? I would say make that a candidate key but use a surrogate for the PK.

Adding to that the other issues with using strings as keys, and then on top of that a compound key that you require, I just don't understand why you want to fight surrogates when you're getting the suggestion from so many angles. You know that many of the people who are pushing you that way have a lot of experience, right?


The VIP should only be assigned to one host: in a replication system, it must be assigned to the current primary/master server. Only failover or switchover should move the VIP to the standby/slave server.


Most probably you are connecting to different database clusters.
Compare host and port in your settings in pgAdmin and for psql default connection.

Read the manual here about connecting with psql.


Select rows with repeat data within certain timeframe

how about this:

 SELECT t1.task_id, t1.reference
   FROM tasks t1
   JOIN tasks t2
        ON t1.reference = t2.reference
           AND abs(t1.end_date - t2.end_date) < 30;

Note this assumes that end_date is a date, and not a timestamp.  If a timestamp cast to date first.


Can I access the file stream shared folder using Listeners in case of AlwaysOn cluster environment?

I do not believe your extracted quote is relevant to your problem.

Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get the active IP and other times it will get the one that is offline.

This is the purpose behind the MultiSubnetFailover parameter that the listener acknowledges.


The SQL*Plus command you're looking for is host. It allows you to run a Linux shell command, so

host mkdir $(date +%F);

Adjust the date format as necessary (man date will help you with that).


How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

Every trigger have some internal tables , INSERTED and DELETED.
In this case, we use only the INSERTED table, which hold the records inserted.

ALTER TRIGGER [xxx] ON [dbo].[Main]
AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status)

    SELECT refno,subject,datereceived,'REC','TEST',author,StatDesc
    FROM INSERTED AS I
    LEFT OUTER JOIN dbo.DocStat AS DS
    ON I.Status = DS.StatID

END


Content of the book

The author analyzes the ACID concept from a very theoretical point of view.

The essential part might be the first sentence of the last paragraph of the chapter:

  Overall, then, we conclude that the transaction concept is important
  more from a pragmatic point of view than it is from a theoretical one.

Another important part is his introduction of the "multiple assignment" operator (p. 487), which he uses as a solution for some problems which are addressed by practical DBMS by supporting transactions. This operation does not seem to work in MySQL, Oracle nor MSSQL. (Make it possible to update several tables with one query.)

His statement is: If we had this multiple assignment operator, deferred checks of constraints are not needed and should be applied immediately and not at the end of a transaction. The latter one results in making the C of ACID useless, as the database is then always consistent (if the C stands for consistency) or it's correctness is unenforceable (if the C stands for correctness).

Concerning Isolation he claims that this should mean all transactions should have absolutely no knowledge of other transactions might be running. This is likely not doable. He further tells that most DBMS provide "isolation level" settings, that undermine this concept in it's base.

Durability he writes could only apply to the most outer transaction, if nested transactions are supported.

Atomicity according to him is only needed because there is need for transactions. If the multiple assignment operator is available as atomic, one would not need atomic transactions.

What to take away

This is a very fundamental discussion about the ACID concept. Without judging if his critics is correct or not, there is a living landscape of DBMS, which is based on transactions.

C.J. Date himself writes that "we now have a better understanding of some of the assumptions on which that research was based". This means it's one of the fundamental aspects, which one cannot change easily in living DBMS. Remove transactions and push in atomic multiple assignment operators and no enterprise will want to update it's architecture, because this is a lot of change.

Thus you have read a theoretical chapter proclaiming how one could improve the properties of DBMS conceptually, and which would erase some problems fundamentally. But only because something might be superior, it will not be used in practice immediately.

You have basically reached the border between someone with a mere theoretical point of view and the practical world, in which there is more than theory, but an existing world, which has to wrap around.


multi-condition left join conditions in MS access

Are the tables you are querying indexed?

If not, I would add an index to the "Inv.SN=Dep.SN"columns ,
" Det.RecSN = Inv.SelfID" and lastly the dates "Det.DDTime, Dep.Start".

NOTE: Be careful when adding indexes. Specially if you are working in your production database. Do your research first on creating indexes on transactional tables, to avoid locks.

If you already have these indexed, then how many records do you have on each table?

Suggestion: I would Separate the OR conditions in different queries and save into a temporary table, so that I can analyze the data sets and results. Ultimately, querying the temporary tables for the final join.

Rule of thumb:

query just one record Id, and see the performance.
Always check if index exists.

There may be various issues, and multiple approaches.


MySQL replication: "Houston, We've Got a Problem"

If you really don't care about that table, you can use pt-slave-restart on the slave and have it skip those problems.  I would be conservative about running it and make sure that you are only skipping queries for the table/database that you don't care about or at least for only a specific error.

You didn't post what the error code was in the output from SHOW SLAVE STATUS, but I suspect it is error 1146.

For example, this will skip all errors for 1146:

pt-slave-restart -u root -p pass --error-numbers 1146

Or, you could try skipping all errors that reference that table

pt-slave-restart -u root -p pass --error-text 'database2'

Another way to do this would be to set replicate-ignore-db=database2 and restart MySQL on the slave, but there are some caveats to how that works that you should read about in the documentation


table or view does not exist when using insert into

It is the definition v_ord_rec   c_ord_cur%ROWTYPE; that is wrong. It must be v_ord_rec   shoba.ord%ROWTYPE;. Also the insert statements should insert into real table and not into the cursor.


How can I get SQL to do the sensible thing without resorting to a query hint?

It looks like you need to edit your index on Grouping_ID to include ThingID and Amount as per the comments, this will eliminate the Key Lookup in the second plan (76%).


Necessary to have separate index for lookups against one of a multi-column PK?

In short, it is sufficient. As far as the searched field is the first in a composite index (PK, unique, or regular index), no need for other separate index.

However, you mentioned that you

  (won't be using an autoincrement column)

Is there a reason for that? Composite PK might have performance hit, specially if you are using InnoDB engine, and if you have many updates on the table (insert/update/delete)


Auto-increment (aka "identity") columns are something completely different than "a column indicating the number of updates to a row".
Even if a sequence (the "magic" behind identity columns) did work without gaps, you still have a single sequence for the entire table, not for each row.
If you want to track how often a row was updated, create a trigger:
create or replace function increment_update_count()
  returns trigger
as
$$
begin
  new.update_count := new.update_count + 1;
  return new;
end;
$$
language plpgsql;

And create the corresponding trigger:
create trigger trigger_track_upate_count
  BEFORE update on the_table --<< change your table name here
  for each row
  execute procedure increment_update_count();


Oracle startup error (could not open parameter file)

I see two things wrong here.  Oracle tries to open a parameter file in the $ORACLE_HOME/dbs directory with the format "spfile" + $ORACLE_SID + ".ora".  If it cannot find it, it then tries to open "init" + $ORACLE_SID + ".ora".  This is where the problem(s) are.

By virtue of the fact that Oracle is trying to open a parameter file named "initXE.ora", your SID must be (or was at one point) equal to "XE".

1) This is the easy part, so try this first.  Since (in your comments) doing an echo $ORACLE_SID yields nothing, try setting it.

export ORACLE_SID=XE

Then try starting Oracle again.

2) In your comments above, you show the file is named "initxe.ora".  Is that really the name of the file?  Or did your paste into a comment somehow lowercase the output of the ls command?

The reason I point this out, is that Ubuntu (Linux) has a case-sensitive filesystem.  So initxe.ora and initXE.ora are actually two different files.  So if your comment is correct and the file is lower-cased, you'll want to set your ORACLE_SID to "xe" instead:

export ORACLE_SID=xe

Try that, and see if it works.


Oracle Materialized View Overloads Database

Materialized views over a DB link most certainly can be set up as fast refreshes (i.e. only changes since the last refresh) as we use this approach ourselves.  However, what cannot be done are refresh on commit mviews over a DB link, so the mview will have to be on a refresh schedule (unless you simply want to refresh on demand, of course).

This works on Oracle Database Versions >= 10g (personally tested).  I can't vouch for < 10g versions since I started using Oracle Database in 10g.

We see very little database impact on both of our source DB's and destination DB's using this approach with roughly 30 mviews (most are medium size tables, a few million rows each) that refresh every 15 minutes.

A few notes/gotchas: The mview log has to be created on the source DB, not the destination DB (which is usually a point of confusion with this setup).

Create the DB link on the destination DB that connects to the source DB.  If the usercode that the DB link is using to connect to the source DB is different than the schema where the table/mview log table resides, then the DB link usercode on the source DB will have to be granted select privileges on both the source table and the source mview log table.

In addition, the mview log deletions don't move the HWM, so for large transactional tables, you may want to consider having a nightly process that locks the mview log table, then checks if the mview log table is empty, then truncate the mview log table in order to reset the HWM to keep fast refreshes as fast as possible.


Source control. This way you have a centralized store for all of your scripts so that any DBA/Developer can access them and use them if needed. Tracking changes to core scripts used day to day in your environment is critical.

You could convert them to stored procedures so that they are included in the database backup it's self but this doesn't provide an easy way to view changes to the script that happened at some time in the past.


You are asking how Database Snapshots work - there is a good article on this on MS TechNet

The short answer: it is so fast because when you do a snapshot you do not backup or copy any data. All that is done - is an empty file created.


How to create implicit select statements

This will work on 2005+ provided you don't have any columns or objects with apostrophes in them - if so you'll need to do some extra massaging to the output. Also, please don't use INFORMATION_SCHEMA unless you absolutely have to...

DECLARE @sql nvarchar(max); SET @sql = N'';

SELECT @sql = @sql + N'PRINT ''SELECT ' + STUFF((SELECT ',' + QUOTENAME(name)
    FROM sys.columns AS c WHERE [object_id] = t.[object_id]
    ORDER BY column_id FOR XML PATH,
    TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
  + ' FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

(Also, your list of columns needs to be reasonable, < 8K, for the PRINT portion of this to work.)


The right way is like the following:

Users {user_id (PK), username (Unique)}

Profiles {profile_id(PK), user_id FK, user_views}

in the given structure, we are making user_id as PK to link between tables, and making the username as Unique to avoid duplication, some people will do the username as PK, but this will make a problem if you want to update the username in any related detailed table linked to that username, in general, you should make sure that your table has only the correct data, without duplication, easy to update, normalized and easy to select with the given PKs or FKs
...

Please read more info from the following reference:
http://dev.mysql.com/doc/refman/5.0/en/create-table-foreign-keys.html


SSRS shared data sources and shared schedule

SSRS has a number of configuration files, including RSReportServer.config.

Both Shared Schedules and Shared Data Sources are good things to create since they help you manage your configuration without giving each and every report it own schedule. View this as saving you some work.  Of course, independent Schedules and Data Sources will likely be needed for some reports.

Since you are concerned with overall load, note that the MaxQueueThreads configuration will control how many parallel reports can run at once.  0 means run as many as possible, but some other number (e.g. 3) will limit Reporting Services to run that number reports at one time.

If you have 10 reports on the same schedule the other reports will run as Queue Threads open up for use.

To configure appropriately need to know your resources available and configure appropriately.  Probably there will be a little trial and error until you get things balanced.


SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?

Trigger code executes in the scope of the transaction that invokes it. Since, as you say, each of these three records is inserted by a separate session, this means three different transactions were completed, each inserting one record and executing trigger code for that one record.

If you initiate three (or more) transactions "simultaneously", you cannot control the order in which parts of execute transaction will execute beyond setting the transaction isolation level. If the isolation level is set to serializable, then the trigger code will execute "one by one (according to the order they are saved in)"; otherwise it will seem to run "in any random order SQL seems fit", subject to whatever locks each transaction obtains.


Why do MySQL threads often show "freeing items" status when the query cache is disabled?

Check the value of innodb_thread_concurrency.

For my system increasing the value from 8 to 32, per the guidelines in the MySql documentation, caused a discernible decrease in the number of threads concurrently reporting the "freeing items" state. Also, the obesrved average query time dropped by an order of magnitude.

While this made a large difference in overall server performance, it was not the "freeing items" silver bullet. My hardware ecosystem leads me to hypothesize that this state is mostly seen on systems with "slow" disks (2x10k disks raid 1), and is less prevalent on systems with faster storage (12x15k disks raid 10). So, a check of disk performance may also be warranted.

Good Luck!

Also:

It is worth noting that the default value of innodb_thread_concurrency is radically different depending on what 5.0 point release is being used.

  The default value has changed several times: 8 before MySQL 5.0.8, 20 (infinite) from 5.0.8 through 5.0.18, 0 (infinite) from 5.0.19 to 5.0.20, and 8 (finite) from 5.0.21 on. -- source

This means that a seemingly innocuous upgrade from 5.0.20 to 5.0.21 changed the default from infinite to 8, and brought along with it the performance ramifications.


MySQL 5.1.73 to 5.6.17 structure + data export

Should be binary compatible, but I would ALWAYS use mysqldump. See here (options not always optimal) and here (phpMyAdmin can't handle large dbs) for why. See here and here for how. And let's not forget the docco!


MySQL 5.7 No Longer Returns Meaningful Error Messages

Looking through the MySQL error log, I noticed the following line: Can't find error-message file '\share\errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive. That file did not exist, however \share\english\errmsg.sys did exist. I found out from here that this error message is not entirely accurate, since MySQL does not actually look in the \share\ directory - it looks in the \share\*YOUR LANGUAGE*\ directory.

I looked in my my.ini and discovered that I had a line that said basedir = (that's all that the line said - there was no path after basedir). I'm not entirely sure how that configuration came to be, but I commented out that line and everything works great now!


A colleague of mine had a similar-ish problem with long-running shrinks.  He took some time to investigate it and found that he had lots of indexes at the 'end' of the file.

A little index maintenance, and then the shrink operation ran fine.  You can read his blog about it here: http://sqltrees.wordpress.com/2013/08/11/shrinking-databases/


Creating Users on secondary server in log shipping

Two options:

On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and name = '<<YourUserNameHere>>'. (SID is basically a unique ID - it's completely unreadable) Then, on the secondary server, create a login with that specified SID: CREATE LOGIN [<YourLoginNameHere>>] WITH PASSWORD ='<<YourPasswordHere>>', SID = <<the SID from primary server>>. By specifying the SID, that login will have the same SID as the user in your logshipped database - you should then be able to log into that user.
Create a login with server-level permissions (i.e. sysadmin) on your log shipping destination. This is a really bad idea for a multitude of reasons (especially if you have other databases on that server).

Worked example of creating a user: I have a database called LogShippingTest and I want to give a login on my secondary server db_datareader on the logshipped copy so that Marketing can read stuff.

On the primary server:

USE [LogShippingTest];
CREATE USER [DataReaderForMarketing] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [DataReaderForMarketing];
SELECT sid FROM sys.database_principals dp WHERE type = 'S' AND name = 'DataReaderForMarketing'

Let's say the SID is 0x70DD0D59ADF3FD4ABB000037826A2888 (I just made that up but we'll go from there). Now, either force log shipping to run, or wait until it runs itself and the user exists on the secondary side. Then, on the secondary:

USE [master];
CREATE LOGIN [DataReaderForMarketing] WITH
   PASSWORD = 'ThisIsAVerySecurePassword',
   SID = 0x70DD0D59ADF3FD4ABB000037826A2888

You should then be able to log in as the DataReaderForMarketing login on the secondary server and have db_datareader on the logshipped secondary database. (You can change the permissions on the primary database user and they'll logship down to apply to the secondary, too!)


with start_date (d) as ( select
  case when current_timestamp < CONVERT(datetime,
          cast(datepart(yyyy, current_timestamp) as varchar) +
          '-04-01T00:00:00.000', 126)
    then convert(datetime,
           cast(datepart(yyyy, current_timestamp) - 1 as varchar)
           + '-04-01T00:00:00.000', 126)
    else convert(datetime,
           cast(datepart(yyyy, current_timestamp) as varchar) +
          '-04-01T00:00:00.000', 126)
  end )
SELECT central_enquiry.enquiry_number,
       central_enquiry.enquiry_name,
       central_enquiry.enquiry_time
    FROM central_enquiry, start_date sd
    WHERE central_enquiry.enquiry_time >= sd.d
      AND central_enquiry.enquiry_time <  dateadd(yyyy,1,sd.d)

This should always select data for the current book year.


Something like this, then?

SELECT  fk_location,
        search_token,
        priority,
        ROW_NUMBER() OVER (
            PARTITION BY fk_location
            ORDER BY priority, last_started_execution )
          AS Ranking
FROM    search_token
ORDER BY Ranking, fk_location
LIMIT 20000;


Foreign Keys to tables where primary key is not clustered index

Taken from the MSDN article on FOREIGN KEY Constraints

  A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
  KEY constraint in another table; it can also be defined to reference
  the columns of a UNIQUE constraint in another table.

I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.


Yes you can restore native backups and AWS has gone to the trouble of writing a very complete tutorial on how to backup from and restore to a RDS SQL Server instance.

restore backup

exec msdb.dbo.rds_restore_database
        @restore_db_name='database_name',
  @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';

There are some limitations to keep in mind and they have explained them all in detail. Some of those limitations are database size, and the types of instances you can restore to: The micro rds sql server instance for example doesn't support restores or backups.

Also keep in mind the difference in versions between what you have locally and the RDS version you are restoring to. Amazon offers SQL Server 2008R2 and up.

https://aws.amazon.com/rds/sqlserver/

Craig


ANSI/ISO plans for LIMIT standardization?

The latest draft SQL standard that I could find on the internet (dated 21/12/2011) has the following available for use in a query expression:

<result offset clause> ::=
    OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
    FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }


No. However, there are some capabilities supported with log shipping and replication on the primary. https://msdn.microsoft.com/en-us/library/ms151224.aspx

As Aaron stated, you could add an additional secondary. If you wanted to use replication to handle subsets of the primary data, or build out a better formatted reporting server (different indexes, subsets of data through filtering, etc), then I would recommend replicating from the primary.

Hope this helps!


Here are some example queries that simplify the SQL, as well as provide solutions to what you are asking.  As far as performance, you'll have to test them:

--RETURNS ALL PARENT CATEGORIES FOR A CITY
SELECT cats.parent_category
FROM city_venues AS venues
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3

--COUNTS HOW MANY CATEGORIES IN EACH CITY
SELECT venues.city_id, COUNT(cats.parent_category)
FROM city_venues AS venues
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3
GROUP BY venues.city_id

--COUNTS THE VENUES IN EACH CATEGORY FOR A CITY
--(Specified in the WHERE     statement)
--REMOVE THE "WHERE venues.city_id = 3" TO GET OVERALL
--COUNT OF VENUES IN ALL CITIES
SELECT venues.city_id, cats.parent_category, COUNT(venue.venue_id)
FROM city_venues AS venues
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3
GROUP BY venues.city_id, cats.parent_category


postgresql multi column unique or check constraint

ALTER TABLE  table_name ADD CONSTRAINT  constraint_name CHECK (
   column_price > 0
   AND column_discount >= 0
   AND column_price > discount
);


Why do we have Top N Sort in this example?

It's a somewhat annoying limitation of the current optimizer.

I wrote about this a little as part of The Eager Index Spool and The Optimizer:

  Index spools do not tell the optimizer they support output ordered by the spool's index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.


How to get particular object from jsonb array in PostgreSQL?

It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup.

Assumptions

Assuming this table definition:

CREATE TABLE tbl (tbl_id int, usr jsonb);

"user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead.

Query

The query is not as trivial as the (now deleted) comments made it seem:

SELECT t.tbl_id, obj.val->>'count' AS count
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '1'
WHERE  t.usr @> '[{"_id":"1"}]';

There are 3 basic steps:

1. Identify qualifying rows cheaply

WHERE  t.usr @> '[{"_id":"1"}]' identifies rows with matching object in the JSON array. The expression can use a generic GIN index on the jsonb column, or one with the more specialized operator class jsonb_path_ops:

CREATE INDEX tbl_usr_gin_idx ON tbl USING gin (usr jsonb_path_ops);

The added WHERE clause is logically redundant, but it is required to use the index. The expression in the join clause enforces the same condition but only after unnesting the array in every row qualifying so far. With index support, Postgres only processes rows that contain a qualifying object to begin with. Does not matter much with small tables, makes a huge difference with big tables and only few qualifying rows.

Related:

PostgreSQL operator uses index but underlying function does not
Index for finding an element in a JSON array

2. Identify matching object(s) in the array

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres array types.) Since we are only interested in actually matching objects, filter in the join condition right away.

Related:

How to turn json array into postgres array?

3. Extract value for nested key 'count'

After qualifying objects have been extracted, simply: obj.val->>'count'.


Store FOR XML result in xml variable (using WITH statement)

You put the assignment after the CTE declaration(s).

declare @X xml;

with C as
(
  select 1 as X, 2 as Y
)
select @X =
  (
  select *
  from C
  for xml path('root')
  );

If you want the XML to end up in a table variable it would look like this.

declare @T table (X xml);

with C as
(
  select 1 as X, 2 as Y
)
insert into @T(X)
select (
       select *
       from C
       for xml path('root')
       );


Mysql speed issues with SHOW COLUMNS or SHOW FIELDS

Would you consider a RAM disk for temp tables ?
First, create the RAM disk (16GB)
mkdir /var/tmpfs
echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab
mount -t tmpfs -o size=16g none /var/tmpfs

Next, add this to my.cnf
[mysqld]
tmpdir=/var/tmpfs

Then, just restart mysql
service mysql restart

Now, all temp tables will land in RAM
I have discussed this before

Oct 09, 2012 : Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL
Nov 30, 2012 : Is it bad to create many mysql temporary tables simultaneously?
Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
Nov 15, 2013 : MySQL creates temporary tables on disk. How do I stop it?

Give it a Try !!!


Adding Full Text Search via command line

I used the GUI version of setup to create a ConfigurationFile.ini that can be used to run the command-line setup to add the necessary Full Text Search components to an installation of SQL Server Core.

The contents of the ini file, which would need to be modified for your scenario, include QUIET="True" which prevents setup.exe from displaying a Graphical User Interface.

Modify the below items for your needs, then save the file as ConfigurationFile.ini in a location where you can refer to it when running setup.exe on the core server.

;SQL Server 2012 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="Install"

; Detailed help for command line argument ENU has not been defined yet.
ENU="True"

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
UIMODE="Normal"

; Setup will not display any user interface.
QUIET="True"

; Setup will display progress only, without any user interaction.
QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="False"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
FEATURES=FULLTEXT

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"

; Displays the command line parameters usage
HELP="False"

; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"

; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.
SQMREPORTING="False"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
ERRORREPORTING="False"

; Specify the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Add description of input argument FTSVCACCOUNT
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$INSTANCE"

An example command-line might be:

setup.exe /IACCEPTSQLSERVERLICENSETERMS /CONFIGURATIONFILE configurationfile.ini

See https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file for details about the configuration file.


Flat similar rows on few columns - Oracle

select id, name, customer_code, min(column1), min(column2)
from the_Table
group by id, name, customer_code
order by id;

Note that if there are more rows with non-null columns this would still only return one per combination of id, name and customer_code (but it does return the result in your question)

Edit (after the question was changed)

To get all values for that combination use listagg()

select id,
       name,
       customer_code,
       listagg(column1, ',') within group (order by column1) as col1,
       listagg(column2, ',') within group (order by column2) as col2
from the_Table
group by id, name, customer_code
order by id;


How can I change SQL Agent Log file to use unicode format?

@spaghettidba's solution worked for this matter.  By unchecking the 'Write OEM error log' check box (cycled the server as well) by right clicking 'Error Logs' under (servername) -> SQL Server Agent -> Error Logs (in the SQL Server Management Studio) the logs started writing and were readable again.


There is no way, there is only the journey! You could start here with MySQLTuner - it's a tuning script which issues recommendations - it doesn't modify anything on the server and I have read (can't find ref, sorry) that it's impact on performance while running is minimal. There's also this - but it seems to be dead.

I'll just repeat the warning that comes with MySQLTuner:

  It is extremely important for you to fully understand each change you
  make to a MySQL database server. If you don't understand portions of
  the script's output, or if you don't understand the recommendations,
  you should consult a knowledgeable DBA or system administrator that
  you trust. Always test your changes on staging environments, and
  always keep in mind that improvements in one area can negatively
  affect MySQL in other areas.

There are many books out there on MySQL - the most highly recommended one at the moment is Schwartz et al's High Performance SQL. Continuous monitoring of your database is as important as tuning, so that when things do start to go wrong, you can at least begin to point the finger in the right direction.

Another thing that is very important to remember is that your MySQL database is just part of a system and it's also critical to be monitoring the various components of that system using Linux's tools for that iostat, vmstat, sysstat &c.

You say:

  I also have had look at slow query but what do you recognize or
  recommend?

but we have no query, no tables (SHOW CREATE TABLE blah1, blah2...) - so it's difficult to help with that. This site is more for specific questions on technical issues rather than broad questions to which you might get a better answer from studying a text.

[EDIT] in response to OP's comments.

Take a look at the table of contents for Schwartz's book here. You'll see that it's a large book with a lot to say about MySQL. More importantly, it places the database in the context of a SYSTEM. I can't tell without more information, but you may need to change the key_buffer_size or your issues may not relate to that particular variable.

In the first instance, I have two recommendations for you - firstly, upgrade to 5.6. Then you'll have the Performance Schema at your fingertips allowing you to properly profile your application - after profiling, you can then proceed to tuning. Profiling is determining where (at a detailled level), your system spends its time - once you figure that out, you can then make rational logical decisions as to where to put your tuning efforts - it could be MySQL variables - it could be to increase RAM, CPU, disk throughput or network bandwidth (in conjunction with OS profiling as I mentioned above).

Secondly, I recommend that you move your system to InnoDB - it makes better use of the machine's resources and is better in almost every way  - Googling "innodb vs myisam" will give you a truckload of links - take a look. MyISAM is nearing end of life - see here for remarks by Bill Karwin and Morgan Tocker - both big hitters in the MySQL world.

Then, read the book that I've recommended (yes, I know, groan...) and then post here if you have more specific questions.

Disclaimer: the usual warnings about testing changes before implementing them on a production system apply!


I wouldn't do this, I would store them all separate and generate them with a function.

CREATE TABLE foo (
  protodate date    DEFAULT CURRENT_DATE,
  id        serial,
  whatever  text
);
INSERT INTO foo (protodate, whatever)
VALUES
  ('2000-01-01', 'foo'),
  ('2000-01-01', 'bar'),
  ('2000-01-02', 'foo'),
  ('2000-01-03', 'baz');

CREATE OR REPLACE FUNCTION to_protoid( date, int )
RETURNS char(20)
AS $$
  SELECT '323926' || to_char($1, 'YYYYMMDD') || to_char($2, 'FM099999');
$$
LANGUAGE 'sql'
IMMUTABLE;

Now to query it,

SELECT to_protoid(
  protodate,
  row_number() OVER (PARTITION BY protodate ORDER BY id)::int
) AS protoid, *
FROM foo;
       protoid        | protodate  | id | whatever
----------------------+------------+----+----------
 32392620000101000001 | 2000-01-01 |  1 | foo
 32392620000101000002 | 2000-01-01 |  2 | bar
 32392620000102000001 | 2000-01-02 |  3 | foo
 32392620000103000001 | 2000-01-03 |  4 | baz
(4 rows)

If that's not good enough you can always create a view or materialized view out of it...

CREATE VIEW myfoo
AS
  SELECT to_protoid(
    protodate,
    row_number() OVER (PARTITION BY protodate ORDER BY id)::int
  ) AS protoid, *
  FROM foo;

Why would I do this? More efficient storage. You store the inputs to the function rather than output which I find cleaner. You have easy use of indexes on date or int.

Lastly, if you want to, you can have a trigger insert into another table the id and the result of the call to to_protoid you can do that, though I wouldn't. I wouldn't use the protoid for anything internal. I would use your regular id and generate it as demonstrated.


Txn Log backups - does it backup empty space?

Log backups only backup actual data and do not backup empty space.

It sounds like there are still processes running overnight.  Got any index rebuilds or any maintenance tasks (aside from DB backups) that may be running overnight?  Just because you think the server should be quiet, doesn't always mean that it is.  There could be overnight batch jobs, miscellaneous tasks, all sorts of stuff.


SELECT query slow even with nonclustered Index and a full-text index

Each of the elements in the where clause are using functions. When functions are used sql cannot determine what index to use. It cannot determine what fields are in the where clause causing a table scan.

Converge the date between to date => and date =<
Is the database really case sensitive? Can you get rid of the lower()
Can you turn the contains into a patindex


How to measure improvement after splitting tablespaces into multiple disk volumes? (Oracle 10g AWR)

From user APC:

The AWR has this section called IO Stats. Not unreasonably, this is where it reports the statistics for IO activity. Within that section there are figures for each tablespace. You need to run these reports against the target database. Ideally you want several different runs, each run being no more than twenty minutes long; the longer the run, the more likely it is that the law of averages will drain meaning from the figures you get.

So, the principle is: you run this report and get a benchmark. Then you deploy your changes and re-run the report. The difference in the various columns is the performance benefit of the change. Really you would want to requests and data per sec go up, average time and waits go down.

To make these figure more convincing you should run the same workload in the before and after tests. This is where something like Real Application Testing really comes into its own.


User can alter procedures without permission

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE
(
  dp.type <> 'R'
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;


How to restore a database to a point in time using a script in Microsoft SQL Server

You need at least two backups for this. One full backup and one transaction log backup. So first you use a command like your above command (but you can remove UNLOAD option, it is a dummy option when you restore from disk). And then you have something like:

RESTORE LOG MyDatabase
FROM DISK = N'C:\Documents\BackUps\MyDatabase.trn'
WITH
 FILE = 1
,STATS = 5
,STOPAT = '20190308 11:31:50.237'

That log backup had to been produced after the point in time to which you want to restore. And since you need a log backup, you need to have the database in FULL (or BULK_LOGGED) recovery mode, not SIMPLE.


Overview of how MongoDB uses its various threads

Max connection count is usually %80 of ulimit value which is 1024 in most linux distros by default. That gives you 819 connections. It can be changed by changing ulimit value.

In MongoDB, each host can use 10 connections which is set by MongoOptions.connectionsPerHost property. These connections are controlled by an internal connection pool. It is thread-safe and you can have up to ConnectionPerHost x threadsAllowedToBlockForConnectionMultiplier threads running simultaneously. threadsAllowedToBlockForConnectionMultiplier also can be changed, its default value is 5. That is by default, you can have 50 simultaneous threads running from one host. Threads more than that value will get the "Out of semaphores to get db connection" exception.

You can change MongoOptions.connectionsPerHost and threadsAllowedToBlockForConnectionMultiplier values appropriately according to your needs.

If you are using drivers on a web application, you should use it as singleton class. No need to 'open connection-> do some work -> close connection' loop. Just declare a global connection once (and initialize in init method of your servlet) and use it in your entire web application.


Multiple mariadb instances - how to access to the not default instances via command line?

As the manual says:

  If hostname is not specified or hostname is localhost, then Unix sockets are used. Unused connection parameters (such as port) will be ignored.

To override the default behaviour, use either the --protocol option to use TCP

mysql --defaults-file=/etc/mysql/conf.d/mymoodle.cnf --port 3307 --protocol tcp

or the --socket option to direct it to the second instance's socket (I assume you have specified a different socket in /etc/mysql/conf.d/mymoodle.cnf


Use the first query from the answer you linked and add a simple WHERE clause to get the partitions of a single table:

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';


In mongodb, drop collections, but still in db.stats() ? In db.stats(), fileSize is greater, greater than storageSize

Number of collection is equal 2:  The 2 collections are the system.indexes system.users. Execute the "show collections" command under your database to confirm.
Filesize: MongoDB allocates "physical files" or files on disk per database. For example eventsdb.0, eventsdb.1 ... Collections are stored with a distributed way within these files. Dropping a database will delete all these files and return the disk space to the OS. Dropping a collection marks the pages within the files as dead but doesn't reduce the filesize. You might have heard that as fragmentation. To reclaim disk space you need to execute the repairDatabase (http://docs.mongodb.org/manual/reference/command/repairDatabase/#dbcmd.repairDatabase) which i don't recommend because uses the global lock. Instead if you running on a 3 member replica set which is recommended, just stop one SECONDARY, delete the data directory and start the SECONDARY again. The initial sync will remove fragmentation.
Show dbs: Show dbs warps the size from filesize, so the number you see is correct.


MySQL Server 5.6 only runs when making swapfile

MySQL 5.6 wants more memory

It's likely that MySQL 5.6 just needs more memory than your previous version.

MySQL 5.6 tries to allocate more memory per connection than previous versions - I believe a lot of this is attributable to the performance schema, which is now enabled by default.

Small servers (esp. ~512MB memory), or servers with a high max_connections setting, can run out of memory even if the other settings are fairly conservative.

If this is the case, you might see some messages from the OOM Killer in your kernel log - via the dmesg function or in /var/log/kern.log, indicating that it's killed the MySQL process because it wanted too much memory.

If you don't want to use a swap file, you may be able to prevent this behaviour by reducing max_connections or disabling the performance schema with performance_schema=off in your my.cnf file.

Swap space is generally a useful thing

Percona's Peter Zaitsev has discussed the merits of swap files on MySQL servers before on his MySQL performance blog.

It's a good read, but to summarise, his own preference was that "I tend to keep swap enabled on MySQL server but of course ensure there is not swapping happening (si/so columns in VMSTAT are zero or close to zero)."

The upside of having swap space is that the server won't kill off processes if it runs out of memory. The downside is that the server might spend time paging instead of running entirely from memory. It's really up to you, but I'm inclined to agree with the author.


Converting DATE and TIME integers to timestamp

Use to_timestamp() with an appropriate format template.
For string input, with padding to the left like in your example:
to_timestamp(lpad("STARTDT", 8, '0')
          || lpad("STARTTM", 8, '0'), 'YYYYMMDDHH24MISSMS')

For integer input, this should be most efficient:
to_timestamp(lpad(("STARTDT" * bigint '100000000' + "STARTTM")::text, 16, '0') , 'YYYYMMDDHH24MISSMS')

Produces timestamptz (based on your current timezone setting). A plain cast to timestamp gets the according local timestamp:
to_timestamp( ... )::timestamp


The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

I fixed the problem by changing folder permissions on the Postgresql ODBC driver folder and giving read/execute access to Network Service. Because MS DTC runs under network service, which I confirmed by looking at the service properties.

That got rid of the error!


List all parents/ascendants of all nodes

You were on the right path and almost had it.  In the select of the second section the parent column should come from the cte instead of S2 and also in the second section the join was backwards (S2.id = p.parent vs S2.parent = p.id).  But that's it!

create table [table]
(
id int,
parent int
);

insert into [table] values(1,NULL),
    (2,1),
    (3,1),
    (4,2),
    (5,2),
    (6,2),
    (7,3),
    (8,4);

;WITH ctetable(id, parent, depth, path) as
(
    SELECT S.id, S.parent, 1 AS depth, convert(varchar(100), S.id) AS path
    FROM [table] as S
    UNION ALL
    SELECT S2.id, p.parent, p.depth + 1 AS depth, convert(varchar(100), (RTRIM(p.path) +'->'+ convert(varchar(100), S2.id)))
    FROM ctetable AS p JOIN [table] as S2 on S2.parent = p.id
    WHERE p.parent is not null
)
SELECT * FROM ctetable ORDER BY id, parent;


completely remove mariadb database and root password

You need to delete the data in your datadir, since that is where this is stored. The default location is /var/lib/mysql/. Shut down MariaDB and delete everything inside the datadir without deleting the datadir itself (as you want to avoid having to recreate the directory with exactly the right permissions):
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*

Then use mysql_install_db to re-initialise the datadir.


There are a few things that I can think of off the top of my head. You're running a multi-instance failover cluster so in theory I'd expect to see each node to be sized such that at any given point in time it can handle the load of all three instances. Chances are that this is not the case, but maybe it is. Ideally, you'd also have a spare node that can handle failures but that doesn't sound like it's the case here.

There are some configurations that you can check to ensure that you've not set yourself up for failure and the first one I'd check would be to run

sp_configure 'max server memory (MB)'

If your run value is 2147483647 then you've got it set to allow SQL Server to take as much memory as it thinks it needs. This is set per instance so when you have multiple instances trying to consume all available RAM you will get memory pressure.

Having said that (read: actually, start here), you've not given us any other information about what you've done to discover why the application stops responding. Is it just the application that connects to the C node that chokes, or does the original application also not work? This could end up being something as simple as the application connection string is connecting to the IP/DNS name of the C node and not the VIP. If this is the case, then when C is no longer serving SQL Server then you're not going to be able to connect.

Step 1: Ensure the connection strings are actually connecting to the instance/VIP name and not the nodes.

Step 1.5: (Thanks to Thomas Stringer), make sure that you're giving the new instance enough time to actually recover the database. Connect to the instance via SSMS and see if your databases are in recovery.

Step 2: If Step 1 is correct, then get on the node that is running multiple instances and see what's going on. I'd recommend using PerfMon because "Task Manager is a dirty, filthy liar" and looking at metrics for the various subsystems starting with Memory, Network, CPU, and Disk IO. This answer contains much of what you'd need in order to check for resource pressure assuming you have connectivity to the instance and the databases are all fully recovered.


Several options:

You could use SQL Server Profiler against the Workgroup Edition server from a different client tools installation of SQL Server Developer, Standard, Enterprise or Evaluation Edition.
Use the semi-documented trace flag 4032, as explained by Tom LaRock.
Evaluate third party tools like the free DataWizard SQL Performance Profiler, xSQL Profiler (free for one instance), or ExpressProfiler (BSD Licence).

My preference would probably be option (1), downloading Evaluation Edition if necessary.


Nested-Loop-Join: How many comparisons and how many pages-accesses?

We can force SQL Server to do exactly this and see what actually happens.

R has 1000 tuples and 100 page-accesses = 10 tuples/page = 806 bytes/tuple.
S has 50 tuples and 25 page-accesses = 2 tuples/page = 4030 bytes/tuple.

These are the tables:

drop table if exists dbo.R;
drop table if exists dbo.S;
go

create table dbo.R(n int, filler char(785)  not null default '');
create table dbo.S(n int, filler char(3990) not null default '');

Filler columns sizes have been rounded down to allow for row overhead. Note that there are no indexes. I have a "numbers" table which I'll use to populate R and S:

insert dbo.R(n) select Number from dbo.Numbers where Number between 1 and 1000;
insert dbo.S(n) select Number from dbo.Numbers where Number between 1 and 50;

We can check just how many pages are involved:

set statistics io on;

select * from R
select * from S

The messages tab of SSMS shows

Table 'R'. Scan count 1, logical reads 100, ...
Table 'S'. Scan count 1, logical reads 25, ...

We have just the right number of pages. A bit of jiggery-pokery will get the behaviour you want to examine

select *
from dbo.R              -- R will be outer
inner loop join dbo.S
    on r.N = s.N
option
(
  force order,          -- dictate which table is outer and which inner
  NO_PERFORMANCE_SPOOL  -- stop the QO from doing something clever but distracting
);

select *
from dbo.S              -- S will be outer
inner loop join dbo.R
    on r.N = s.N
option (force order, NO_PERFORMANCE_SPOOL);

Which gives this in the messages tab (inner table is listed before the outer table)

Table 'S'. Scan count 1, logical reads 25000, ...
Table 'R'. Scan count 1, logical reads 100, ...

Table 'R'. Scan count 1, logical reads 5000, ..
Table 'S'. Scan count 1, logical reads 25, ...

In SQL Server query execution proceeds row-wise. For each row in the outer table the corresponding row(s) in the inner table will be referenced. Since there are no indexes the only option is to read all the rows (i.e. all the pages) from the inner table every time. For R-join-S we have 1,000 outer rows times 25 inner pages giving 25,000 inner page references plus, of course, 100 outer page references. For S-join-R there are 50 rows times 100 pages giving 5,000 inner page references plus 25 outer page references.

In terms of tuple comparisons you are correct - there will be O(R)xO(S) comparisons - 50,000. This is supported by looking at the query plan. For both queries the "Number of Rows Read" for the inner table references are both 50,000.

If there are indexes the query optimizer (QO) has choices other than a table scan. Rewinds may be used for duplicate outer keys. No pages may be read for non-matching keys. In the extreme case where a constraint says there cannot be any matches the inner table may not even be referenced.


In SQL Server, what is the purpose of grouping stored procedures?

This is like overloading a method. Essentially you could create two or more versions of your stored procedure, where each does different things - take a different number of parameters, operate on different tables, have different output, etc.

They are called Numbered Procedures and they are absolutely deprecated (announced since 2005). They are still supported in SQL Server 2012, but some features don't play nicely with them. For example, they are considered a containment breach in Contained Databases, and any procedure numbered > 1 will not be created:

  Msg 12829, Level 16, State 1, Procedure blat, Line 1
  The stored procedure 'dbo.blat' refers to a group of numbered stored procedures. Numbered stored procedures are not available in contained databases. Please consult the Books Online topic Understanding Contained Databases for more information on contained databases.


Select most occurrences of a varchar2 in Oracle