What MDX can and SQL Can't perform on the same datatbase

It's not a matter of what one can do vs the other, as you can always, in theory, write SQL to replace your MDX. It's a matter of how hard it is.

Example:

If you want the sales per month of your top 5 clients from last year in EMEA, your MDX query is something like

SELECT TopCount( [Clients].Members, 5, ( [Measures].[Sales], [Time].[2018] ) ) on Rows,
[Time].[2018].Children on Columns
from [Sales]
where [Geography].[EMEA]

and your cube definition will take care of what columns to query from which tables, getting the Territory name from the dim_territory table, the customer name from the customer table, etc.

The equivalent in SQL would be something like

Select c.customer_name, t.month_name, sum(f.sales)
from fact_sales f, dim_customer c, dim_time t, dim_geography g
where f.time_id = t.time_id
  and f.customer_id = c.customer_id
  and f.geography_id = g.geography_id
  and g.territory = 'EMEA'
  and t.year= 2018
  and c.customer_name in (
    select c.customer_name, sum(f.sales)
    from fact_sales f, dim_customer c, dim_time t
    where f.customer_id = c.customer_id
      and f.time_id = t.time_id
      and t.year = 2018
    group by t.year
    limit 5
  )
group by t.year, t.month_name
order by t.month_number

Not only the SQL query is much more complex to write (I'm not even sure what I wrote is correct, tbh), but a lot of variations that in MDX just require a small rewrite, in SQL they will require several nested queries, and lots of filtering, grouping etc.

Plus, MDX allows you a cross-tab result, where you get one column for each month (or whatever dimension level you choose), whereas in SQL it will return a normalised dataset and you'll have to pivot it on the client side if you want it in tabular format.


SQL: Merge rows when value doesnt change in continuous time

create table StatusTable
(
SerialNumber varchar(15),
BeginDateTime datetime,
EndDateTime datetime,
StatusID int,
TimeInStateInMilliseconds int
)
go

insert StatusTable
values
('DASimulator1','2/25/20 16:15','2/25/20 16:16',0,60000)
,('DASimulator1','2/25/20 16:14','2/25/20 16:15',0,60000)
,('DASimulator1','2/25/20 16:13','2/25/20 16:14',5,60000)
,('DASimulator1','2/25/20 16:13','2/25/20 16:14',0,60000)
,('DASimulator1','2/24/20 23:20','2/24/20 23:21',3,60000)
,('DASimulator1','2/24/20 23:19','2/24/20 23:20',3,60000)
,('DASimulator1','2/24/20 23:18','2/24/20 23:19',3,60000)
,('DASimulator1','2/24/20 23:17','2/24/20 23:18',3,60000)
go

with base as
(
select a.*
    , CASE when b.SerialNumber is not null or c.SerialNumber is not null then 1 else 0 end as GroupStatus
from StatusTable a
        left join StatusTable b on a.BeginDateTime = b.EndDateTime and a.StatusID = b.StatusID
        left join StatusTable c on a.EndDateTime = c.BeginDateTime and a.StatusID = c.StatusID
)

select SerialNumber, MIN(BeginDateTime) as BeginDateTime, MAX(EndDateTime) as EndDateTime, StatusID, SUM(TimeInStateInMilliseconds) as TimeInStateInMilliseconds
from base
where GroupStatus = 1
group by SerialNumber, StatusID

union all

select SerialNumber, MIN(BeginDateTime) as BeginDateTime, MAX(EndDateTime) as EndDateTime, StatusID, SUM(TimeInStateInMilliseconds) as TimeInStateInMilliseconds
from base
where GroupStatus = 0
group by SerialNumber, StatusID

order by EndDateTime desc
go

here's what i came up with based on only the data set that you've shared. if there are other data variations in your full data set then you need to adjust for it.

firstly, i'm a bit confused on your desired output result set. the third row EndDateTime value matches the first row BeginDateTime value and they both have the same StatusID value? so based on what you said about continuous time, wouldn't these two rows be merged together?

my query behaves as such, if you run my code, it will merge row 1 and 4 together.

also, shouldn't the fourth row in your result set have a BeginDateTime value of 2020-02-24 23:17:00.000 or are you just grabbing the EndDateTime value and making that the BeginDateTime value if it matches?


Creating SQL Server Performance Baseline Monitoring

More than one year later I want to let everyone know my experience and the final result of this question / topic.

I started out creating things on my own. Initially I followed the Article Collect and store historical SQL Server performance counter data with CMVs by Tim Ford to get something up and extended this with whatever Data I wanted to collect. So once a
per day I run several stored procedures on each Sql Server that collect some specific information from DMVs and stores the results on the local Server inside of a database. This includes index usage, missing indexes, specific log entries like autogrow, server settings, application database settings, fragmentation, job execution, transaction log info, file information, wait stats and more.

Additionally I added Brent Ozar's sp_blitz regulary execution results to this repository to collect additional valuable indications to work, improve and report.

All data is lateron collected from there into a dedicated monitoring Sql Server and this way I create a bundeled store for performance relevant information about all my servers and use this as a base for investigation and reporting.

Then I created excel sheets and also reports using reporting services to analyze and interpret. Some samples:

Also I configured some performance counters monitoring using TYPEPERF inspired by the article "Collecting Performance Data into a SQL Server Table" by Fedor Georgiev.

From my SQL Monitoring instance I trigger typeperf to run and collect a configurable number of samples with a configurable sampleinterval and store the results in my central monitoring db.

This allows me to observer longterm performance values, sample:

After a while of using this to gather baseline information, it tunred out that it is quite a lot of maintenance work that has to be spent on looking at failed jobs, de-buggin procedures (for example in case a DB was taken offline, some scripts failed), maintaining settings after a server was replaced...

Also the database collecting all the records tiself needs maintenance and performance tuning, so additional work comes up to keep the data useful...

What is finally completely missing is the ability to look at things that happen live. In Best Case, I will be able to tell what was possibly going on the next day after the data collectors have ran. Also all the details are missing. I do not have access to deadlock graphs, I can't look at query plans of queries that were running in a suspicious timeframe....

All of that made me going to charge management to spend money for a prefessional solution I am not able to create on my own.

The final choice was to buy SentryOne because in comparison to others it is convincing and delivers a lot of information required to identify our pain points.

As a final conclusion I would advice anyone looking for answers to similar question not to try creating things on their own as long as you don't have a small and basically healthy environment in place. If you have couple of systems and a lot of problems, better immedeately go for a professional solution and use the assistence of the vendor on your problems instead of spending a lot of time and money to create somehing less useful. However, this route was still very interesting and made me learn a lot I dont want to miss.

I hope you find this useful once you ran into this question thread.

EDIT April 20, 2017:
Brent Ozar recently posted the following article on facebook that is kind of a simiar approach taken by the SQL Tiger Team: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-baselining-reports-unleashed-for-enterprise-monitoring/


Reverse auto_increment id existing column in phpMyAdmin

You can create a new table like the old one and insert the values in a different order, for example:

CREATE TABLE new_table like old_table;
INSERT INTO new_table (date, name)
SELECT date, name FROM old_table
ORDER BY date ASC, id DESC

Change the ORDER BY to whatever suits to you- I have ordered by date so it doesn't trust that the original data was right, you can order just by id DESC.

You can later delete the old table and rename the new table as the old table:

DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

It can be done just using a table, but that is easier and lets you check that the contents are ok before changing the original table.

If you did something like this the ids will collide and the query would fail -it would require an additional column or the drop of the PK and two table rewrites instead of one:

SELECT max(id) INTO @maxid FROM my_table;
UPDATE my_table SET id = @maxid - id + 1;


Export Materialized View from postgresql as a table?

I don't think there is a single-step way to do this.  Are you trying to do this with one MV, or with a whole database full of them?

To get the proper CREATE TABLE statement with all the column types, you could do this:

CREATE TABLE dummy1 (like mv1 including indexes);

And then use pg_dump -t dummy1 > mv1.sql to get the CREATE TABLE statement.

To get the data, you would do:

\copy (select * from mv1) to mv1.txt

You would then have to stitch the two files together with a name change from 'dummy1' to 'mv1' in the first file, to get the definition and the data to be combined in one replayable file.


will this insert query take too long to run?

As the others have said 180 rows is not a big deal.

That you are forming the 180 rows into a single insert command is a good thing, it will reduce both latency and the workload on the database.

Any computer capable of running Postgresql should have no problem with that work-load. this includes NAS boxes, cell-phones, and Raspberry Pis.


Backing up a 400-500 GB database on MySQL/Amazon RDS?

RDS provides automated backups as a feature.  Those backups are system automated (persisting through the life of the RDS instance), or user initiated (persisting outside the life of the RDS instance).  They also automate binlog backup for point-in-time restores.  But you can't take those backups out of the RDS service, as it's all hooked together for automating restores, etc.

FAQ: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RDSFAQ.Backup.html

I assume you want a backup outside of RDS.  mysqldump would be the only way to do it, and unless you use some creative ETL to facilitate some sort of daily delta, you might save time.  Otherwise, that's a good chunk of data to backup on a regular basis.


naming a column differently then its original one in foreign data wrapper of PostgreSQL

You can name the columns of your foreign table whatever you want.  For example, after an IMPORT FOREIGN SCHEMA ... you can do

ALTER FOREIGN TABLE myschema.mytable RENAME a_column TO another_column;

(For CREATE FOREIGN TABLE, you have to specify the different column name in the OPTIONS clause to the column: CREATE FOREIGN TABLE foreign.employee (emp_id integer OPTIONS (column_name 'id'), ...)

What you cannot do is mixing columns from local and foreign tables.  To achieve something similar, you can create a view:

CREATE VIEW unified_employee AS
SELECT local.id AS emp_id,
       local.name,
       ...,
       foreign.blood_type
  FROM local_schema.employee AS local
  JOIN foreign_schema.employee AS foreign ON local.id = foreign.emp_id;

I swapped the tables in this example on purpose.  Your example tables don't differ enough, so a simple view on the local employee table, where the id column is named emp_id would do the trick, without ever touching the other database.


sql server 2012 master database log is corrupted

If any database is corrupted, I start to worry about the storage it lives on. If you haven't identified the root cause of the corruption yet - like a bad storage driver, a bad storage cable, a failing RAID array, or maybe even a known bug in SQL Server - then I'd start by assuming that the server and its storage is not completely reliable.

I'd start by failing over to a different server on different storage, and run CHECKDB against all of your user databases (not just master) to make sure they're okay. (Don't think that a clean CHECKDB on the existing server means you're out of the woods - master might have just been the first place that storage decided to corrupt.)

For much more details, check out my post, What to Do When DBCC CHECKDB Reports Corruption, which includes a checklist on:

Turn off your backup-delete jobs
Query the msdb.dbo.suspect_pages table
If you're using a SAN, alert the storage team
Alert the application owners and stakeholders
Review the history of your CHECKDB and backup jobs

And more.


Yes, by using # at the start of a line.

Actually everything after the # sign will be ignored, so this can be used after a parameter as well.

This is a bit hidden in the manual:

  Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table name includes a pound sign (#), then the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks

Quoted from:  http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#sthref137


Problem restoring a database in SQL Server

You're getting that first error because kdcb already exists.  And the backed up database is not that same database.  If you want to overwrite an existing, separate database (that isn't the same.  With the same you don't need to specify replace), you need to include the REPLACE option:

RESTORE DATABASE KeltieCochrane_v2
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK'
WITH
MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\KeltieCochrane_v2.mdf',
MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\KeltieCochrane_v2_1.ldf',
REPLACE

WARNING!!!  You need to be very careful and make serious consideration by specifying REPLACE.  Here is an exact excerpt from BOL regarding REPLACE:

  Replace Option Impact

  REPLACE should be used rarely and only after careful consideration.
  Restore normally prevents accidentally overwriting a database with a
  different database. If the database specified in a RESTORE statement
  already exists on the current server and the specified database family
  GUID differs from the database family GUID recorded in the backup set,
  the database is not restored. This is an important safeguard.

  The REPLACE option overrides several important safety checks that
  restore normally performs. The overridden checks are as follows:

  Restoring over an existing database with a backup taken of another database.

  With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if
  the specified database name differs from the database name recorded in
  the backup set. This can result in accidentally overwriting a database
  by a different database.

  Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option
  is not used.

  With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

  Overwriting existing files.

  For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database
  that is not online. Arbitrary data loss is possible if existing files
  are overwritten, although the restored database is complete.


after update trigger sends data to log table and sends tag_id .I need tag Name?

Based on your description, the Doc_Tag table should have the Tag_name you need.

In the future, giving actual table definitions, some sample data and the actual expected result will help in getting a more accurate and complete answer.

This query example might get you started

  SELECT DT.tag_name
  FROM dbo.logs L
  INNER JOIN dbo.doc_document_tag DDT
  ON DDT.Tag_id = L.Tag_id
  INNER JOIN dbo.Doc_Tag DT
  ON DDT.Tag_Id = DT.tag_id;


Delete User View named the same as system view

Making a guess with no information but is the name in sys.objects INFORMATION_SCHEMA.TABLE_CONSTRAINTS?

Try running this:

SELECT object_schema_name(object_id) AS SchemaName, name
FROM sys.all_objects
WHERE name like '%TABLE_CONSTRAINTS'

I'm guessing you get something like this:

SchemaName          Name
------------------  ------------------------------------
INFORMATION_SCHEMA  TABLE_CONSTRAINTS
dbo                 INFORMATION_SCHEMA.TABLE_CONSTRAINTS

First confirm that the second entry is actually a view then run the following:

DROP VIEW [dbo].[INFORMATION_SCHEMA.TABLE_CONSTRAINTS]

Please run the following query and post a screenshot of the results in your question.

SELECT object_id, schema_id, object_schema_name(object_id) AS SchemaName, name, type
FROM sys.all_objects
WHERE name like '%TABLE_CONSTRAINTS'


Specify Connection in Management Studio T-SQL Query

No way to do this as part of a script from SSMS, but you do have two options.

One thing you can do is use SQLCMD mode and the ::connect command in order to have a script that will connect to multiple servers and run the script. This works well if you save the script for the user and use the :r command to load the script from a file.

Another thing you can do is configure a Central Management Server and then run your script against multiple servers at once.


Database Engine and SSISDB architecture advice

This is a culture change as much as it a process change, so having management support you will be important, and ultimately shield you from some level of technical resistance and aid in broadcasting how this strategic shift will improve the lives of everyone.

Before we get into the specific case of the SSIS Catalog, let’s look at an overview of your options to better arm you with the knowledge to communicate why developers should change their system:

File system

Individual Package are stored on any folder, any location.
No security, files can be modified by any user or process without prior knowledge and no process natively exists to prevent this from occurring.
Not part of the regular backup schedule. System snapshots are not a viable backup and do not prevent accidentally modifications that can cause irreparable damage.
Must manually organize packages and no ability to group packages outside physical location and/or documentation
Forced to use Configuration Files to parameterize values

SSIS Package Store

Packages by default are stored specific folder “C:\Program Files\Microsoft SQL Server\<SQL Version Compiled>\DTS\Packages”. Can be moved.
Limited security; can be modified by elevated permissions
Not part of any backup strategy outside system snapshots (again, NOT A VALID BACKUP SOLUTION)
Can organize packages under Folders
Cannot use roles to restrict access to packages from the SSIS Package Store

SQL Server (msdb)

Packages are stored in msdb and can be exported to a file-share.
Security is tied to access to msdb.
System msdb is part of a valid backup solution.
Cannot be added to an AlwaysOn solution
Folders can be used to categorize
Can use Roles to manage permissions in 2016+ that avoid using sysadmin. Integration Services Roles (SSIS Service) - MS Docs

SSISDB Catalog

Packaged are stored exclusively inside SSISDB that is encrypted with a certificate
Security is further strengthened by separate permissions and roles (see above link).
SSISDB is part of a valid backup solution
SQL Server 2016+ has added support for AlwaysOn! SQL Server 2016 HA Series Part 2 – SSIS and Availability Groups – Blogs Microsoft
Mature management hierarchy that includes Parameters, Environments Variables.
Offers Full integration with Visual Studio
SQL Agent Jobs no longer directly use configuration file references or actual passwords (hope no one is doing this anyways) from being scripted out from SSMS.

  Ultimately, you can decide what path will work for your organization.
  SSIS Catalog is one of several methods, so getting your management to
  support you in this endeavor will go a long way to making changes.
  After describing an example scheme for deployment, the rest of this
  post will highlights some Visual Studio Management and SSIS Catalog
  Tips.

An example scheme could be the following:

All new efforts will use [Visual Studio] 2017 or 2015 for backwards compatibility and SQL Server Data Tools - (Download SQL Server Data Tools (SSDT) - docs.microsoft.com).
o   note in VS 2019 they added this as a marketplace component)
projects will be saved in the code management folder (GitHub folder) so efforts are versioned or they will use the SSIS Catalog on a DEV environment, though ideally use the GitHub Repository.
Package components are installed on each node for Dev/Cert environments first. The Enterprise DBA assists in gathering these downloads in a single file-share for ease of access/ transportation to target clustered servers and ensures they are on each target server.

BONUS SECTION:

Visual Studio Management Tips:

Your Package depends on both the Deployment Version (must match Integration Services of SQL Server instance)

And the 32-bit/64-bit configuration depending on the components used.

In order to use the Parameters, you must either user a package “parameter” (not variables) or Project Parameter. You an use these to environmentally control what connections are used.

You can connect and deploy to your target location (it doesn’t HAVE to use the SSISDB)

SSISDB Catalog Management Tips:

1)  You can Restore Versions with a click of a button to undo negative changes or even revert forwards!

2)  You can manage project parameters, validate, control versions from SSMS. No VS required.

Configure the Environment Parameters from here:

Passwords, unlike in the SQL Server Agent jobs, are securely protected inside the SSISDB database.

Example:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'<name of step>',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'SSIS',
        @command=N'/ISSERVER "\"\SSISDB\Replication\SSISDB_Replication\SSISDB_Replication.dtsx\"" /SERVER <ServerName> /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
        @database_name=N'master',
        @flags=0

3)  Each SQL Server Agent Job then uses these parameters after you select the environments set.

Pick the Package Source SSIS Catalog.

Set the environment for your variables.

In most cases, the parameters will automatically be set.

4)  Check what actually went wrong inside SSMS without resorting to RDPing into your server.

5)  See performance between runs that uses real statistics!

Am I missing anything? Feel free to make this a community answer.


Fill in missing dates with data value from previous populated date for group

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between.

WITH TAwithnext AS
(SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate
 FROM TicketAssignment
)
SELECT t.TicketID, c.Date, t.DepartmentID
FROM dbo.Calendar c
JOIN TAwithnext t
    ON c.Date BETWEEN t.AssignmentDate AND ISNULL(DATEADD(day,-1,t.NextAssignmentDate),t.AssignmentDate)
;

All kinds of ways to get a Calendar table...


Is it possible to restore database only with .trn files?

Run this:

RESTORE LOG [database_name] FROM DISK = N'path\filename.trn' WITH RECOVERY; GO

Notice I've changed it to WITH RECOVERY - this runs the recovery process and puts the database in a usable state.

Running WITH NORECOVERY leaves the database in a state where further log files can be restored.  This state prevents you using the database until you do a RESTORE LOG ... WITH RECOVERY


How would I sync tables in two separate servers in mysql?

What you likely want to use is called Replication, which is a methodology for keeping two databases across two servers in sync with each other. Please see the MariaDB documentation here on how to set it up: Setting Up Replication
Also I think you'll probably want to understand the different Binary Log Formats before you set it up as well.


Multi-Column Full Text Search Going Very Slow

PROBLEM
From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column.
Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on.
SOLUTION
What you really need is a single FULLTEXT index with all 3 columns
ALTER TABLE articles ADD FULLTEXT content_title_keywords_ndx (content,title,keywords);

Only then can you say
match(content,title,keywords) against ('cats' in boolean mode)

I have suggested making compound FULLTEXT indexes before

Mar 16, 2012 : Speed up search across multiple columns
Oct 13, 2012 : Can underscore be forced as a word splitter without a full-text parser plugin?
All my posts about FULLTEXT indexing and searching

GIVE IT A TRY !!!


Stored Procedure on a read-only AlwaysOn Availability Group replica

A possible workaround would be; Just create a database in the secondary instance and then create the stored procedures accessing the data from the databases being replicated.

EDIT: But this database wouldn't be replicated.


MySQL: Do you need to use backticks on column names when using the dot to refer to a table name (table_name.`column`)

Your syntax (without backticks) will work on

MySQL - see here and here,

See SQL at the bottom of the post.

(tl;dr) The answer to the question "MySQL: Do you need to use backticks on column names when using the dot to refer to a table name" is "No, you do not need to use backticks!".

See also

PostgreSQL - here,
MS SQL Server - here,
Oracle - here.

However, I beg of you, if you value your own sanity, do NOT EVER use SQL keywords. COUNT and TYPE are such words.  It makes debugging a nightmare and is completely unnecessary anyway. Use identifiers such as my_count or thing_type instead. Just because you can drive your car over a cliff, doesn't mean that you should!

Most (all?) modern RDBMSs have (at least) 64 character(*) identifiers. That_is_more_than_enough_dont_you_think?

Furthermore (and very importantly) if you use the superfluous (and highly annoying) backtick, you'll be bunched when cutting and pasting your SQL to different servers - my advice is to avoid at all costs. It's no longer required and is tricky to type!

Get some SQL naming conventions off the web (example), pick one and stick to it! This is somewhat of a religious topic (an "ecumenical matter" in the immortal words of Fr. Jack!). Read around and decide for yourself!

My own preference is for table names to be singular (a table is a collection anyway), SQL in UPPER CASE and identifiers_in_lower_case_with_underscores - makes reading easy!

You (and those who come after you) will be eternally grateful!

(*) from here, Oracle was 30 bytes up and including 12.1, but it's now 128 bytes - almost a tweet! :-)

Now, consider the following DDL, DML, query and response:

(simple, legible and portable!)

=========== DDL and DML ==================

CREATE TABLE table1
(
  my_count INTEGER,
  my_type  VARCHAR (10),
  count    INTEGER,
  type     VARCHAR (10)
);

INSERT INTO table1 VALUES
(1, 'type1', 34, 'type1_bis'),
(2, 'type2', 23, 'type2_bis'),
(3, 'type3', 56, 'type3_bis'),
(4, 'type4', 78, 'type4_bis'),
(5, 'type5', 90, 'type5_bis');

And a simple query:

SELECT
  t.my_count,  -- see second MySQL sample!
  t.my_type,
  t.count,
  t.type
FROM
  table1 t;

Results:

my_count    my_type     count       type
1             type1        34  type1_bis
2             type2        23  type2_bis
3             type3        56  type3_bis
4             type4        78  type4_bis
5             type5        90  type5_bis


The only way I have found is through a stored procedure

CREATE OR REPLACE FUNCTION print_notice(msg text)
  RETURNS integer AS
$$
DECLARE
BEGIN
    RAISE NOTICE USING MESSAGE = msg;
    RETURN null;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

So, executing SELECT print_notice('Foo ' || someText || ' !');

Will produce (for example) :

NOTICE:  Foo whatever !
query result with 1 row discarded.

I'm not sure if this can be done without the SELECT and the query ... row discarded message. But it works.


Why is it faster to create index and run query than run query without it?

The reason is the you repeatedly scan the Logs table. And you even have two cross apply in that query. The repeated scanning of this table is evidently more expensive than building the index and then using that index.
Nothing strange or unexpected here.
SQL Server could possibly do an index spool so it can used that for each visit to the Logs table. Perhaps the optimizer evaluated that strategy and discarded it since its estimates showed that it wouldn't be beneficial (incorrectly, perhaps). One very first step would be to study the execution plan, the estimates compared to actual values and take it from there.


PostgreSQL "freeze"/"unfreeze" command equivalents

There's no exact equivalent to what you want. Options include:

Atomic file-system snapshot

If you're using an atomic file system snapshot you don't need to freeze the database. It might make recovery a little faster if you force a CHECKPOINT first, but that's about it. When you take a filesystem snapshot and copy it, then start the copy, to PostgreSQL it's as if it crashed and is starting back up. That's perfectly fine, it's crash safe and designed to work that way.

This approach is only valid if the snapshot is atomic - it's all at the same virtual instant. You can't get an atomic snapshot across multiple file systems (on Linux, at least), so if your DB is split across multiple tablespaces or WAL is on a separate disk to the heap you cannot use this approach.

pg_start_backup / pg_stop_backup

If you can't do an atomic snapshot you can enable WAL archiving, run pg_start_backup, copy the DB, run pg_stop_backup and capture the last WAL archives generated.

It's a bit more complicated, but it gives you a consistent backup without stopping writes and without needing file-system level atomic snapshots.

pg_basebackup

An alternative to using pg_start_backup and pg_stop_backup is to use pg_basebackup to do a streaming copy of the DB over the PostgreSQL replication protocol with --xlog-method=stream. This requires only a PostgreSQL replication connection, doesn't require the DB to be stopped, and is pretty seamless.

--xlog-method=stream was only added in pretty recent versions, and pg_basebackup its self is fairly new.

pg_dump

I didn't initially mention it because you were looking for external tools, but there's always pg_dump, which gets a SERIALIZABLE snapshot of the database and dumps it. The DB keeps running like normal (it can still accept writes) and the dump is entirely internally consistent from the time you started the dump.

Write quiescence

Stopping all incoming transactions won't stop PostgreSQL writing. It'll still have VACUUM work to do with autovacuum, checkpoints to perform, stats to write, etc.

There's no feature in Pg to stop all writes at this point. It might be nice to add, but I'm not aware of anyone working on it.

Some file systems, like XFS, support write freezing at the file system level; this causes all writes to block until the freeze is released. It's safe to freeze all file systems then copy all file systems.


How to store external IDs from different sources?

The fact that the ExternalID is in an arbitrary format should not impact the design of the schema. As it's a many to many relationship, you can use the following:

Customer
- ID
- Name
- etc...

CustomerCompany
- CustomerID (PK, FK to Customer)
- ExternalID
- CompanyID (PK, FK to Company)

Company
- ID
- Name
- etc...

Depending on the business logic, it may also make sense to include ExternalID in the primary key for the CustomerCompany table.


Does SQL Server TDE decrypts when it enters the buffer pool and encrypts when it leaves

Yes, it is encrypted both "at rest" on disk, and also "in flight" between disk and SQL server.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

  TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files.

In other words, the data isn't decrypted until it gets to the SQL server.

Think of it this way: for it to be decrypted in-flight, the SAN itself would have to decrypt it before fulfilling the request and sending the data to SQL. And we know it isn't doing that, it is SQL that is encrypting/decrypting.


Any way to limit the number of connection to mysql for a command

Hm, unless mysql works like any other database I know...

one command takes exactly ONE connection.

The error is not your command, the error is having and keeping too many connections open (application error), likely by not properly closing them and forgetting about them.

But one command runs on one connection. Period.

You basically ask how to avoid a traffic jam in front of your house by limiting the pizza you ordered to only come with one car - which it always does (because you only ordered one pizza anyway).


Consequences of tnsnames.ora and sqlnet.ora being unavailable

Are these files used by any database processes, or only by client programs like SQL*Plus?

Yes, these files are used by Oracle processes while connecting to the database, once the connection is established they don't need these files.

For example,

If you have specified the following parameter in sqlnet.ora file on server side.

tcp.validnode_checking = yes
tcp.invited_nodes =<list the valid nodes here>

The Oracle server process will check whether the incoming connection request is valid or not. Once it finds the node is valid it will proceed ahead and it no longer needs the sqlnet.ora file.

Similarly, Oracle client process will look up TNS Name on tnsnames.ora file for the connection string. Once it finds the connection details to make connection it also no longer needs the tnsnames.ora file.

  If these files became unavailable, would existing client connections be affected, or only new connections?

Only the new connection will be impacted because existing connections have already used these files during the connection initiation.

  Would database links using the TNS aliases be broken while the tnsnames.ora file is unavailable?

If you are middle of the transaction using that database link and the tnsnames.ora is missing then it will not impact the on going transaction. However, next session which uses the TNS Name in database link will fail.

ORA-12154: TNS:could not resolve the connect identifier specified


Remove parent and child rows if child or parent fits criteria

This is one way to do it:

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
    DROP TABLE #Orders;

CREATE TABLE #Orders (
    OrderID int,
    OldOrderID int,
    Action varchar(10),
    EntryDate date,
    Source char(1)
)

-- Your sample data, plus a couple more rows
--to demonstrate the recursive query
INSERT INTO #Orders
VALUES
    (    1   ,     NULL      ,   'Insert'  , '2016-01-12',   'A'),
    (    1   ,     NULL      ,   'Remove'  , '2016-01-13',   'A'),
    (    2   ,     NULL      ,   'Insert'  , '2016-01-12',   'B'),
    (    3   ,     NULL      ,   'Insert'  , '2016-01-12',   'C'),
    (    4   ,       3       ,   'Insert'  , '2016-01-13',   'C'),
    (    4   ,     NULL      ,   'Remove'  , '2016-01-14',   'C'),
    (    5   ,       4       ,   'Insert'  , '2016-01-15',   'C'),
    (    5   ,     NULL      ,   'Remove'  , '2016-01-16',   'C');

WITH SourceData AS (
    -- Crosstab to get all orders on a single row
    -- The assumption is you can get just two actions (insert and remove)
    SELECT OrderId,
        ParentOrder = MAX(OldOrderID),
        InsertDate = MAX(CASE WHEN Action = 'Insert' THEN EntryDate END),
        RemoveDate = MAX(CASE WHEN Action = 'Remove' THEN EntryDate END),
        Source = MAX(Source)
    FROM #Orders
    GROUP BY OrderId
),
OrderHierarchy AS (
    -- Recursive query to get top OrderId
    SELECT *,
        OrderID AS TopOrderId
    FROM SourceData
    WHERE ParentOrder IS NULL

    UNION ALL

    SELECT SD.*, OH.TopOrderId
    FROM SourceData AS SD
    INNER JOIN OrderHierarchy AS OH
        ON SD.ParentOrder = OH.OrderID
),
AllOrders AS (
    -- Get MAX(RemoveData) for the order chain
    SELECT *, MaxRemovedDate = MAX(RemoveDate) OVER (PARTITION BY TopOrderId)
    FROM OrderHierarchy
)
-- Return just the rows that have no remove date in the chain
SELECT *
FROM AllOrders
WHERE MaxRemovedDate IS NULL;


dynamic postgres inside shell script not working

Three misunderstandings:

You cannot return data from a DO command.
You cannot SELECT without target in plpgsql code. That's what the error message tells you.
You don't need either for a simple SELECT statement. Just run the statement itself:

abc()
{
 $PG_CMD 'select * from customer'
}


The right way to handle this is to set up two different database users user_ro and user_rw for instance and then you grant usage on the schemas you need to access and grant only SELECT to the read-only user and all privileges to the read-write user. You then create two database connections in your application and use the appropriate one.

You cannot use pgpool-II to automatically distribute read and write queries because there is no way it can programatically inspect a query and say that it will not produce a write in the database. Even a simple select * from sometable; can trigger a write if there is a trigger on the table to record accesses ( a common scenario for sensitive data).

pgpool-II does a great job of keeping a connection pool open and ready and managing failover from master to replica transparently to the application but it won't solve the problem you have without using two separate connections and dealing with those connections in your application code. There are some ways to finesse this; like attempting all queries on all of the read replicas and retrying ones that failed on the master. But from a conceptual hygiene standpoint it's better to decide when you are are writing to the database and when you are reading from it.


For NOT IN queries, it's safer to use NOT EXISTS if columns are NULLable, rather than try to handle NULLs with some additional logic.

Here's a quick example script.

CREATE TABLE #t1 (Id INT NULL)

CREATE TABLE #t2 (Id INT NULL)

INSERT #t1 ( Id ) VALUES (1), (NULL), (3)

INSERT #t2 ( Id ) VALUES (1), (2), (NULL)

SELECT *
FROM #t1 AS t
WHERE t.Id NOT IN (SELECT t2.Id FROM #t2 AS t2)

SELECT *
FROM #t1 AS t
WHERE NOT EXISTS (SELECT * FROM #t2 AS t2 WHERE t2.Id = t.Id)

DROP TABLE #t1, #t2

Full disclosure: I do not work for either NOT IN Inc. or NOT EXISTS Corp.


Just like ypercube commented, Sql Server doesn't adhere to similar a boolean evaluation as, say, Java or C#. There are lots of articles about this, but facts are a bit hard to find.

As per the documentation:

  The order of evaluation of logical operators can vary depending on
  choices made by the query optimizer.

This behavior is caused by the fact that SQL is a declarative language. In such a language one describes the desired output, not the steps how to build the output. It is up to the RDBMS how to build the output. This often is counter-intuitive to programmers used to procedural and OOP approach, in which one tells explicitly step-by-step what the application should do.

If you got queries that mis-behave with boolean logic evaluation order assumption, I'm afraid you got to re-write those.


USERS Tablespace Best Practices for Application Schema

This is a best practice, and the answer is "it depends". For example, if the schema has low accesses and few data, using a different tablespace different to USERS/TEMP isn't really necessary,because you can assign a quota to user's data (in this case, creation of separated tablespaces is a merely question about organization). Conversely, if the schema has an high i/o or has a lot of data, the uses of different tablespaces is a good choice, because you can create them to a different pool of disks (for example, Oracle is installed in magnetic hard disk, and tablespace data is created on a pool of SSD). Note that: in this case, you want also a different tablespace for saving indexes


OpenProject pg_restore - input file does not appear to be a valid archive

I'll post an answer because it might be useful to someone else.
As the screenshot in the question displays, there is an error in the /home/openproject/.postgresqlrc file on the server on which I've run the backup.
The format of that file was not correct: I've fixed it by following the format described here and when I had run the next backup I get no errors, and I was also able to restore that backup successfully.


Is there any manual for the visual explain in mysql? The output plan is unreadable

ref evidently means a keyed lookup because the definition of ref in EXPLAIN is

  The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

As for ALL, my guess is that refers to a full table scan because the same URL says

  A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

In all honesty, I cannot tell what the ORDER BY is. You should try comparing that graph above to the text-based output from one of these:

pt-visual-explain (Documentation)
mk-visual-explain (Documentation)

and see if the JOIN ordering makes sense there.


Add a constraint to a column on an Oracle table so that only positive values are allowed

To add a constraint you need to use add constraint:

alter table SPEND_PLAN_DETAIL_VALUE
  add constraint check_detailvalue CHECK (SP_DETAIL_VALUE_ID > 0);

Online example


Order by custom filter without certain data

This will get your result set, though I did add the Name field for clarity. The common table expression (cte) with ROW_NUMBER() will get the email priority. Then I left joined that back to the base table to return the names and a NULL for Julius.

CREATE TABLE #emailOrPhone (ID INT IDENTITY(1,1), Type INT, Number VARCHAR(50), Name VARCHAR(20))

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1213','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1312','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1012,'sally@ourdomain.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'sallyf@gmail.com','Sally')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'frankiebaby@yahoo.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'franks@ibm.com','Frank')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1010,'555-123-9876','Julius')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'425-369-1470','Julius')

;WITH cte AS
(
SELECT Name, Number
, CASE WHEN Number like '%@ourdomain.com' THEN 1 ELSE 2 END AS 'emailPriority'
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) as rn
FROM #emailOrPhone
WHERE Number like '%@%'
)

SELECT DISTINCT Names.Name, Emails.Number
FROM #emailOrPhone as Names
LEFT JOIN cte as Emails on Names.Name = Emails.Name and Emails.rn = 1
ORDER BY Names.Name, Emails.Number

DROP TABLE #emailOrPhone


What synchronizes two delayed replicas, which is part of the replica set, included in sharded cluster?

There is an obvious problem with your "delayed backup" model, in that your delayed secondaries will reflect the state of each replica set but not the full state of the sharded cluster at a given point in time.

A simple example:

there is a chunk migration from shard1 => shard2 in progress
documents will exist on both shard1 and shard2 while they are being copied over
your "delayed backup" does not have the matching config metadata to interpret the state of the cluster (and it will be changing over time, unless you disable the balancer)

Depending on the length of the replication delay (your diagram mentions "hour" and "week"), multiple changes to the cluster metadata may have happened and the config data will be very out of sync.

While you will have a delayed copy of the "data", if it isn't in sync with the sharded cluster metadata your path to a full restore will be reloading (and resharding) the data. You will also have to worry about resolving duplicate documents which can exist due to in-progress migrations.

For more information see the Backup and Restore Sharded Cluster tutorials in the MongoDB documentation. If you have a large amount of data, you will typically want to use the Filesystem snapshot approach to create an approximate point-in-time backup.


Is it dangerous to use Linked Server? (SQL Server 2008)

You are creating a path to another location, be it SQL Server, Oracle, DB2 whatever.  So the security risks are there but can be whatever you make of them.  For example if you set up a linked server to a SQL Server and tell it to impersonate SA for every connection then the security risks are massive.  If however you tell it to only use the users current security context the risks are less so.  With any additional "option" that you turn on or use you increase your security risks by a certain amount.  You just need to make sure that you do your due diligence about how you set it up.

Personally I prefer to use the "Be made using the login's current security context" option.  No SQL Server IDs will be able to use it and only AD logins that are on both machines will be able to do so.  If you want to use the option "Be made using this security context" option then make sure that the user you are giving out has minimal permissions and that those are permissions you are willing to give out to EVERYONE.


Stored procedure performs worse than straight query

This might be a case of parameter sniffing.

You could try simply defining two local date variables at the top of your stored procedure, and assign them the value of the two input date parameters. Then replace the usage of the input parameters in the stored procedure code with those new local variables.

This has usually done the trick for me in the past, when dealing with parameter sniffing.


Why is the following query so bad performance-wise

OR is not usually "SARGable" when you use different columns

That is, case you can optimise for either CardNumber or FullCardNumber searches with an index on each column but not both columns. This is a "seek".

An index on both columns won't work because you it is OR: values for every row must be examined. This is a scan.

If the condition was AND, SQL Server could do a residual lookup on FullCardNumber after findng CardNumber. Seek followed by a secondary seek basically.

Anyway, try this to remove the OR scan and have 2 individual efficient seeks

SELECT
  @TempCardNumber = CardNumber
FROM
    (
    SELECT CardNumber
    FROM Cards
    WHERE CardNumber = @CardNumber
    UNION -- removed dupes
    SELECT CardNumber
    FROM Cards
    WHERE FullCardNumber = @CardNumber
    ) X

Note that the query assume you only get one row: is this intended?


Is changing the SQL Server port really that much safer?

It helps against common port scans that can be initiated through port scanning websites. But it won't help against a committed attacker. It's just another layer, but doesn't add much over the firewall, as you mentioned.


Thanks to Phil's comment the correct statement looks like this:

SET SERVEROUTPUT ON

DEF username = 'cuser2'

DECLARE

   i_matching NUMBER(1) := null;

BEGIN

   SELECT COUNT (1) INTO i_matching FROM dba_users WHERE username = UPPER('&username');

  DBMS_OUTPUT.put_line(i_matching);

   IF i_matching = 0 THEN

    EXECUTE IMMEDIATE ('create user &username IDENTIFIED BY &username');
    EXECUTE IMMEDIATE ('GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO &username');
    EXECUTE IMMEDIATE ('ALTER USER &username DEFAULT TABLESPACE USERS');
    EXECUTE IMMEDIATE ('ALTER USER &username TEMPORARY TABLESPACE TEMP');

   END IF;

END
;
/


You have an AUTO_INCREMENT sql_id, so you have the rows numbered 1,2,3, 4,5,6, ... (unless you are using a clustering or Multi-Master setup that sets auto_increment_increment to something other than 1).

So, load the data into a table, then copy thirds into the three desired tables, and finally DROP the initial table.

INSERT INTO table_1
    SELECT * FROM table_all
             WHERE sql_id % 3 = 0;   -- 0 for table_1. 1 for table_2, 2 for table_3

If you are adding to tables1/2/3, and they have an AUTO_INCREMENT, you can get unique ids by _skipping sql_id in the transfer:

INSERT INTO table_1 (b,c,d,e)
    SELECT b,c,d,e FROM table_all
             WHERE sql_id % 3 = 0;

This assumes the columns in table_all are

sql_id,b,c,d,e

And table_1/2/3 have another column that is AUTO_INCREMENT.


If you need a specific order, you should specify it with the ORDER BY clause.

The most efficient way to perform an order-by is to ORDER BY somefield.

If you need those records sorted as 3,1,2, then perhaps you should add a field sortorder that is ordered the way you want so you can simply do ORDER BY sortorder.


create difference column of time series in MySQL

Hope this helps. It's SQL Server code, but you should be able to perform the same join on your table with the date_add function.

DECLARE @TABLE TABLE ([Date] Datetime, [Value] int);

INSERT INTO @TABLE VALUES ('2015-Sep-01',11);
INSERT INTO @TABLE VALUES ('2015-Sep-02',13);
INSERT INTO @TABLE VALUES ('2015-Sep-03',15);
INSERT INTO @TABLE VALUES ('2015-Sep-04',20);
INSERT INTO @TABLE VALUES ('2015-Sep-05',68);

SELECT
    Main.Date,
    Main.Value - Previous.Value as [Difference]
FROM @TABLE Main
LEFT OUTER JOIN @TABLE Previous
    ON Previous.[Date] = DATEADD(DAY,-1, Main.Date);


Reducing query cache for mostly INSERT-heavy database?

query_cache_type = 0
query_cache_size = 0

Seriously, turning off the QC is probably best. especially based on what you said.  Keep in mind that every INSERT (or UPDATE) to a table causes all entries in the QC to be purged.  Furthermore, if the QC size is large, (say, over 50M), the purge time slows down the write.

The ENGINE's cache is important to both reads and writes.  Generally MyISAM's key_buffer_size should be 20% of available RAM.  OR InnoDB's innodb_buffer_pool_size should be 70% of available RAM.


Force a Member to Become Primary when Primary fails in mobgodb

If you have 2 members in a set, and one of them is unhealthy then you cannot elect a primary.  There must be >50% (a strict majority) to elect a primary, with 1/2 nodes up you cannot satisfy that condition, you can only ever get 50% exactly, which is not sufficient.  Your only option is to restore the other node or to add another node (perhaps an arbiter). However, adding a node will require a forced reconfiguration because you do not currently have a primary. Fortunately the procedure is well documented here.


What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?

There are many implementation options available to developers of BTree and B+Tree algorithms that will affect the answer here. In a simplistic BTree, all nodes are the same size, and when a node overflows it is split into two half-full nodes with no other key redistribution occurring. Since there will on average be a uniform distribution nodes between half-full and full, the average fill factor will be 75%. You can calculate everything else from that.

Real implementations may however redistribute keys into one or two additional adjacent nodes, which increases the average fill factor. In addition, an implementation may detect (or be notified) that a bulk insertion of pre-sorted keys is happening, and will modify the split algorithm to leave behind a trail of full nodes with only the final node being incomplete; the advantages of this behaviour should be obvious.

In a B+Tree, all key values are present in leaf nodes - so a B+Tree will have as many leaf nodes as the equivalent BTree has nodes overall. The B+Tree will also have internal nodes which contain the keys used as splitters, and the same repetition of values occurs up the tree. Actual implementations however might truncate these keys to fit more in (which changes the fan-out radically, especially at the root level), and of course key redistribution can also be done.

Many implementations use enlarged root nodes, and some allow other nodes also to expand into additional pages, to reduce the hassle of splitting and key redistribution, and to handle very large key values.

Finally many implementations cut short the process of merging nodes on deletion, to the point of only deleting nodes that become empty. There is a number of nasty edge cases with B+Trees regarding merging (consider where you delete a small key from a leaf, where that key was used as a splitter; now you need to replace that splitter with the next value that may be large, and causes the internal node to split!), so it can be easier to just drop it, and any performance impact is not often a concern. So the actual fill factor depends not only on the keys, but also on the history.

The upshot is that the question you're trying to answer is only ever asked for academic interest. It's almost never relevant to real implementations.


out of shared memory error when doing pg_dump

pg_dump takes share locks on each table it dumps. So you need to configure Postgres to support that many locks It's unclear to me if you have 3 schemas with 1200 tables each - which would mean a total of 3600 tables, or if there are 1200 table distributed across 3 schemas.

Quote from the manual

  The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table

Assuming everything is configured as the defaults, you wind up with 64 * (100 + 0) = 6400 objects that can be locked. If pg_dump is the only thing running on your database the default configuration should be fine.

The fact that you only occasionally get that error, means that you have concurrent transactions that also use many object locks so that pg_dump can't allocate enough locks.

You should increase that value nonetheless to avoid the "occasional" errors. I would start with increasing it to 128.


How can you create a ROLE which isn't allowed to do anything?

Just use

CREATE ROLE foo;

Be aware of default privileges (https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html).


How to check if a table exists in Informix

To check whether an Informix table exists, use the following query:

SELECT       COUNT(*)
       FROM  systables
       WHERE tabname = 'yourtablename';

If the result is not 0, then the table exists.


MariaDB & MySQL - Trigger not firing: “Field '__' doesn't have a default value” (Old Bug not really fixed?)

I opened a ticket with MariaDB. They have confirmed that it's still broken, and it has been put in the backlog to fix it.

Update:

The ticket has been fixed in MariaDB versions: 10.1.21 & 10.2.4


Are there implicit indexes in InnoDB like MyISAM?

This is not at all the case for InnoDB. Here is why:
For InnoDB table, A primary key is InnoDB is stored in gen_clust_index, the Clustered Index. Every secondary index has an internal key back to the gen_clust_index.
Given this background on PRIMARY KEY storage, now for differences:
MyISAM
Given Primary Key id and index a,b,c, the implicit keys are

(a)
(a, b)
(a, b, c)

This is always true of the index a,b,c regardless of the index being unique or non-unique. The column id is not known to the index. There is a rowid that links each .MYI entry back to a row in the .MYD, but but no direct knowledge of id. The id is quite visible in the primary key.
InnoDB
Given Primary Key id and index a,b,c, the implicit keys are

(a, id)
(a, b, id)
(a, b, c, id)

The reason id is known is due to the fact that every secondary index entry has an implicit key back to the gen_clust_index.
Please see my past posts on the gen_clust_index.
PROOF OF THIS...
Let's create two sample tables. One with MyISAM and the other InnoDB

Same Data
Same Columns
Same Indexes

Here is the code:
DROP DATABASE IF EXISTS pavel;
CREATE DATABASE pavel;
USE pavel
CREATE TABLE rolando_myisam
(
    id int not null auto_increment,
    a int not null,
    b int not null default 99,
    c int not null default 99,
    primary key (id),
    key abc_ndx (a,b,c)
) ENGINE=MyISAM;
CREATE TABLE rolando_innodb LIKE rolando_myisam;
ALTER TABLE rolando_innodb ENGINE=InnoDB;
INSERT INTO rolando_myisam (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
INSERT INTO rolando_innodb (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;

Here it is loaded:
mysql> DROP DATABASE IF EXISTS pavel;
Query OK, 2 rows affected (0.23 sec)

mysql> CREATE DATABASE pavel;
Query OK, 1 row affected (0.00 sec)

mysql> USE pavel
Database changed
mysql> CREATE TABLE rolando_myisam
    -> (
    ->     id int not null auto_increment,
    ->     a int not null,
    ->     b int not null default 99,
    ->     c int not null default 99,
    ->     primary key (id),
    ->     key abc_ndx (a,b,c)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE rolando_innodb LIKE rolando_myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER TABLE rolando_innodb ENGINE=InnoDB;
Query OK, 0 rows affected (0.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_myisam (a)
    -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
    -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
    -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
    -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
    -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_innodb (a)
    -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
    -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
    -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
    -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
    -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

Here two queries to show a retrieval of the id columns:
SELECT id FROM rolando_myisam;
SELECT id FROM rolando_innodb;

Let's explain them:
mysql> EXPLAIN SELECT id FROM rolando_myisam;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_myisam | index | NULL          | PRIMARY | 4       | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Notice the Query optimizer chose the PRIMARY KEY for the MyISAM table, but chose the abc_ndx key for the InnoDB table. What does this show? The abc_ndx index for the MyISAM table cannot see the id column. Thus, id is not implicit for MyISAM.
Let's try a different type of query:
EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;

Look at the EXPLAIN plans:
mysql> EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | rolando_myisam | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Again, abc_ndx cannot be used for getting id because it is not implicit for a MyISAM index. It is definitely implicit for InnoDB.
Let's try another different type of query:
EXPLAIN SELECT id FROM rolando_myisam where a = 12;
EXPLAIN SELECT id FROM rolando_innodb where a = 12;

Look at the EXPLAIN plans:
mysql> EXPLAIN SELECT id FROM rolando_myisam where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | rolando_myisam | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | NULL  |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | Using index |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>

Once Again,abc_ndx index cannot be used for getting id because it is not implicit for a MyISAM index. It is implicit for InnoDB. Note that the InnoDB lookup use the index only. This is why the Extra in the EXPLAIN plan had Using index for InnoDB, but had nothing for MyISAM. The MyISAM lookup had to reach into the .MYD to get id.


Install MariaDB which will run on top of a MySQL-5.7 data directory. This has SphinxSE included in every mariadb-server package on Ubuntu.
You will need to install the plugin:
INSTALL SONAME 'ha_sphinx';

Please check the incompatibility documentation and make a backup before upgrading.
Note there is little effort being put into maintaining SphinxSE (note MariaDB is a few versions behind the latest 2.11, only minor differences looking at code), and that it has been dropped from SphinxSE 3.0 onward.
I agree its a beautiful piece of software, and I know of one large production use of SphinxSE from a former client of mine who's websites are still running and appear to be using it still.


Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!


How to get the sum of a particular item id in a column

Particular Item

SELECT SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable WHERE Item = '102814742801';

All Items By Item

SELECT Item,SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable GROUP BY Item;

All Items By PackageID

SELECT PackageID,SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable GROUP BY PackageID;

All Items By brandname

SELECT brandname,SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable GROUP BY brandname;


Difference between DEFINE_CHAIN_EVENT_STEP and DEFINE_CHAIN_STEP procedure

First of all a chain is a name referred to a series of programs which are linked together for a specific objective.

So the DBMS_SCHEDULER.DEFINE_CHAIN_STEP procedure define an step to point to a program or a nested chain.

BUT, if you wish to define a step which waits for a event to occur to be executed then you need to use DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP procedure.

The following blog post from Database Journal contains a very good example of using Job Chains.

Figure: Flowchart is taken from database journal blog post by Jim Czuprynski.

For example, according to the above flowchart, to perform employee paycheck file validation steps it file has to be arrived. To define such step you need to user DEFINE_CHAIN_EVENT_STEP procedure.

Go to the referenced link for details.

Reference: Job Chains


Stored procedure running for a long time

One possibility is that the stored procedure was created with settings for ANSI_NULLS and QUOTED_IDENTIFIER that differ from those set on your local connection.

These settings are stored with the procedure definition and override the session's current value. These two options can dramatically change an execution plan, and may prevent the use of indexes on computed columns and indexed views. Script the stored procedure from SSMS to check the stored settings values.

For a very comprehensive look at general issue see Slow in the Application, Fast in SSMS? by Erland Sommarskog.


advantages of single-leader (transactions) over multi-leader replication

I came across your question while reading the same book.

I think the issue to consider is that in the single leader scenario, there is a natural total ordering as it can apply a sequence number per transaction.  It always knows "which happened first". It's hard or impossible to do this correctly in multi leader due to issues like clock skew.

So I think it boils down to, does the system even know which happened first.

Your "write if" suggestion is mentioned/used many times and I think is agnostic to single/multi leader. Write if is application level (the user probably has to enter what they think the old value is) whereas single/multi leader is probably not exposed to the user. In single leader, the write if would unambiguously avoid conflicts; the total ordering determines which operation happens first, and whichever gets there second will safely fail due to the write if.

I think the advantage of multi is performance based; the advantage of single is "correctness/complexity" based.


Database Design for NoSQL Fantasy Baseball League

Team {
    id : id,
    name : String,
    teamLink : String,
    username : String,
    userLink : String,
    league : Number,
    Stats : [{
        desc : String,
        value : String,
        globalRank : Number,
        globalPts : Number,
        divRank : Number,
        divPts : Number,
        year:Number
    }]
}

League {
    id : Number,
    name : String,
    link : String,
    division : Number,
    acrive : Boolean
}

rankings : {
            week : Number,
            league_id: Number,
            year: Number,
            ranks : [{
                team_id : Number,
                position : Number,
                points : Number
            }]
        }

match {
       id : Number,
       league : Number
       team_visit : Number,
       team_ext : Number,
       ining : Number,
       time_start : Datetime,
       time_game : Number,
       team_visit_score : Number,
       team_ext_score : Number
}

EDIT: explain schema.

First is necessary separate all stats for team in a interval time,
because changes in time for season, years, stats, etc...
Add the match documents, for each match a document. Separate all
for    documents, League, Teams, Ranks, is better for search and
relation    with others team in match, rank...
And the solution is MySQL I'm not so sure about that, because if you
have a lots data in match, league, teams, ranks... Is Better documents with all stats of any team, or all rank in any interval time
because in MySQL you need JOINS for that.


Server load discrepency in Slave with Master load being low and constant

You will have to look at this from different perspectives

Data / Storage Engine

The InnoDB Storage Engine is constructed to do as little writing as possible. As an ACID-complaint storage engine, InnoDB has mechanisms in place for concurrent manipulation of data. The structures used by the InnoDB Infrastrcuture are the following:

Buffer Pool : Changes to Data and Index Pages that are Cached in RAM
Double-Write Buffer : Backup buffer to prevent data corruption
Insert Buffer : Changes to secondary non-unique indexes
Redo Logs : Playback instructions in the event of Crash Recovery
Undo Logs : Reverse instructions in the event of Crash Recovery/Rollback
See Pictorial Representation

Master

In all likelihood, there are concurrent operations from multiple DB Connections performing

INSERTs
UPDATEs
DELETEs
SELECTs

on the Master. These changes and lookups are cached and managed with the InnoDB Infrastructure. Flushing all recorded changes is serially managed but has no immediate effect on data concurrency. The only discernible bottleneck would be the time an SQL transaction decides to write the SQL Commands into its Binary Logs. The OS is responsible for flushing changes to binary logs to disk.

Slave

Here is where things can get interesting. While the Master handles everything thrown at it, it has to write the SQL Commands into its Binary Logs. The Slave has the responsibility to process what the Master gives it.

Here is the MySQL Replication Paradigm

IO Thread on Slave Connects to Master
The Master Does the Following on a Continuous Basis:

Completes SQL Command
Records SQL Command in its Binary Logs

The Slave Does the Following on a Continuous Basis:

IO Thread from Slave Reads incoming SQL Commands from the Master's Binary Logs
IO Thread Records the SQL into its Relay Logs
SQL Thread from Slave Reads Next SQL Command to Be Executed
SQL Thread Executes the Latest SQL Command From the Relay Logs
If Binary Logging is enabled on the Slave, Slave Records SQL Command in its Binary Logs

If you carefully follow the flow of control from this Paradigm description, you can easily see that all the SQL sent from the Master is serialized. In other words, the SQL commands from the Relay Logs are executed one at a time. This can manifest itself on the Slave as follows:

When you run SHOW SLAVE STATUS\G, you see Seconds_Behind_Master increasing
When you run SHOW PROCESSLIST;, the SQL thread (whose user is 'system user') is executing an SQL statement that is taking a long time on its own.

This can easily happen when a Master is handling hundreds of SQL commands in parallel. It is when the SQL statement are lined up single-file and recorded as a FIFO queue on the Master, that the Slave must then process each command first-come, first serve only without any benefits from parallelism.

Conclusions Drawn

From the roles of Master and Slave given an all-InnoDB database, a Master to could handle multiple SQL transactions and be done with all commands. By design a Slave has no choice but to process commands in a serialized fashion. The background processing performed by a Master would easily get finished a stay quiet for a long time while a rather busy Slave would have process SQL one-by-one and have resources handle processed changes in a similar manner.

Troubleshooting / Resolution

Once you can see this funneling of SQL from Master to Slave, your only recourse would be to consider some of the following options to apply to the Slave and Master

Query Optimization
Create the needed indexes to support Query Optimization
Make adjustments in the networking setup between Master and Slave

Install a 2nd NIC on Master and Slave
Assign IP address to the 2nd NIC (NIC_IP)
Run CHANGE MASTER TO command to assign (NIC_UP) to MASTER_HOST

Upgrade to MySQL 5.5 (if not using MySQL 5.5)
Configure MySQL 5.5

configure InnoDB for multiple core engagement
configure SemiSynch Replication

Upgrade RAM
Change to Individual Tablespaces


Convert datetime stored as char the YYYMMDDHHmmSS format to datetime

Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime.

Something like this:

declare @S varchar(50)
set @S = '20120606122012'

select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)


Conditional one to many queries in mysql

As Nick Chammas pointed out in his comment, your test tables should be combined if they have the same fields and segregated if they have very different fields.

It is difficult to imagine 100,000 different test tables.  If this is not complete hyperbole then you might need to consider other options.

One option would be a document-oriented database.  This would be efficient for storage of loosely structured data and easy retrieval of data for a particular patient.  It would be pretty useless for rapid retrieval of data for similar tests across multiple patients.

Another option would be Entity-Attribute-Value (EAV).  EAV is considered an anti-pattern by most people, but yours may be a situation where it could be useful.  EAV would be less efficient at retrieving the results of a particular test for a particular patient, but it would certainly be better at getting all results for a particular patient than joining thousands of different test tables.  It would also be better than other approaches for getting the same test result values across multiple patients.


OCIEnvCreate failed to create environment handle after installing oracle_fdw

You have to set the environment variables LD_LIBRARY_PATH and ORACLE_HOME for the user postgresql. Then you have to set the service to conncet in the file tnsname.ora, the you have to create a foreign server with dbserver=Service (you should have defined the Service in the file `tnsname.ora

I send you my files configurations in Debian:

I added to /etc/profile (apply for all system user) but you can use .bashrc or another for only one user.

Oracle

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client
export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

Postgresql Path

PATH=$PATH:/usr/local/pgsql/bin
export PATH
MANPATH=$MANPATH:/usr/local/pgsql/man
export MANPATH

The variable TNS_ADMIN will be a directory where you install a tnsnames.ora

tnsnames.ora:

ORA11 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA11)
 )
)

Then create a foreign server for oracle_fdw with option dbserver=ORA11


MS Migration Assistant (SSMA) index renamed

While there are reasons that a tool might make intelligent decisions about index names (like to prevent duplicate names in cases where there are actual collisions, and further when it actually matters, like when they are in the same table), it would appear that SSMA just creates new, unique names for all indexes. Perhaps only in the case where it finds at least one index name that is reused, not sure. I would test this, but I don't have Access installed anywhere.

Why does it do that? Who knows? Extreme conservatism? Paranoia? I think it's just being extremely safe.

There are cases where duplicate names would be a problem, but these are isolated to first class entities that end up in sys.objects, like primary key constraints. Index names are only enforced to be unique at the table level (since the name is only exposed in sys.indexes), not at the database level.


Can I get similar Full Text Search functionality without using Full-Text Search?

Native full-text can handle all of the features you've mentioned.

Ranking/relevance, weighting
Word inflection and expansion
Customisable noise words and thesaurus

Whether or not it will be the best solution for your particular domain is a tough call. Rather than attempt to hand-roll an alternative, spike a proof of concept using SQL FTS and repeat the exercise with one or more open source alternatives.

Lucene is the obvious contender to compare and comes with the "Stack Overflow search is now 81% less crappy" endorsement of the SO team.


Ideal way to return a scalar from function (RETURN vs. SELECT)?

I'd prefer to use RETURN rather than RETURN QUERY SELECT in pl/pgsql.

But there's no need to jump through hoops. The cost of returning results with SELECT is negligible compared to other costs in pl/pgsql procedures.

Everything is a result-set in PostgreSQL anyway, even a single scalar, so you're not saving much with RETURN.


Migration from MySQL to Oracle generates empty tables

Solution: create a user/schema in Oracle that has the same name as the database you want to import.


Database convert character_set_server latin1 to utf8

character_set_server is not relevant.  The setting on the database is just a default for when you CREATE TABLE without specifying a DEFAULT CHARACTER SET.

You need

The bytes in your client to be encoded utf8.
The connection specifies utf8.  If you are using mysqli, use mysqli_set_charset('utf8').
The column in the table must be CHARACTER SET utf8.  Look at SHOW CREATE TABLE; if it is not explicit on the column then look at the default for the table.
If you are doing html output, then it needs <meta charset=UTF-8>.


That your query on the lock table didn't return anything provided the answer to your question--it was not waiting on a lock.  (Assuming you did it correctly).

It is still kind of annoying and error-prone to have to open a new session and run the query and interpret the results, so I wrote a patch to send NOTICE when you are waiting on a lock.  There were several proposed ways to improve it, but no consensus on the best way, so for now it is just a proposal.  If you are willing to compile your own PostgreSQL you can always adopt this patch for your use.  I don't know what pgAdmin will do with such notifications, however, as I use psql not pgAdmin as my main tool.

As @a_horse_with_no_name indicates, you can also use columns in pg_stat_activity to get information on this.  The quality of that information improves from version to version.  Running older versions does come at a price.  Putting in the effort to upgrade every couple years is well worth it.

Finally, your system monitoring tools are invaluable. A quick look at "top" (for Linux) or "Task Manager" (for Windows) would probably have immediately indicated that your job was busy doing something--either CPU or disk.


How is an Active Log different from MinLSN?

MinLSN is the LSN of the oldest uncommitted transaction.

This is not exactly true

LSN stands for Log Sequence Number.
Each log record has a LSN and each new log record is written to the logical end of the log with a higher LSN than the previous LSN.

Using this simplified Log Sequence:

LSN Log
80  Begin Tran 1
82  Begin Tran 2
83  Update Tran 2
84  Check Point
85  Update 1
86  Commit Tran 1
87  Check Point
88  Update Tran 2

Next LSNs will be 89, 90, ...

The Log Sequence Number of the first log record that is required to successfully perform a full recovery of the database is recorded in checkpoint records. This LSN is called Minimum recovery LSN or MinLSN.

MinLSN is the minimun of:

LSN of the beginning of the oldest active transaction
LSN of the start of the last checkpoint
LSN of the beginning of the oldest replication transaction that has not yet been replicated

If we look at the above LSN records, MinLSN candidates are:

Oldest active transaction = Begin Tran 2 with LSN 82
LSN of the last checkpoint = LSN 87

Therefore MinLSN is LSN 82.

  What exactly does Active Log hold?

The list of LSNs starting at MinLSN and up to the last recorded LSN is called the Active Log. Here Active log is composed of LSN 82 to 88. MinLSN is just the first LSN of the Active Log.

  Isn't it suppose to hold all the uncommitted transactions?
  Does it hold any other log records which are already committed?

You can see that the Active Log still contains already commited transactions (TRAN 1) because their LSNs are bigger than the MinLSN.

  What happens to MinLSN and Active Log when a CHECKPOINT happens?

When a new checkpoint occurs and is recorded:

LOG records linked to a Dirty page being written to disk are written from memory to disk before making any change to the page. This guarantees the successful recovery of the database before writing a page to disk. Since LOG are written sequentially, records in memory not related to the current page being written may have to be record before it.
Dirty pages are written to disk (ie. all pages in memory that have been changed since the last checkpoint or since they were read from disk, however the status of the transactions),
Checkpoint LOG record is created. Its LSN is compared to the LSN of the begining of the oldest active transaction:

If its LSN is smaller, it becomes the new MinLSN.
Otherwise, the LSN of the oldest trasaction remains the MinLSN.


MySQL Error when importing CSV with empty fields

Something like this, checking every value for '', and inserting NULL instead, may help. Change a,b,c,d for the actual name and number of rows on the table:

$ cat /tmp/test.csv
test1, test2, test3, test4
,,,
1,2,3,4
,,,

mysql> LOAD DATA INFILE "/tmp/test.csv" INTO TABLE test.test
          FIELDS TERMINATED BY ','
          OPTIONALLY ENCLOSED BY '"'
          LINES TERMINATED BY '\n'
          IGNORE 1 ROWS
       (@a, @b, @c, @d)
       SET a = IF(@a = '', NULL, @a),
           b = IF(@b = '', NULL, @b),
           c = IF(@c = '', NULL, @c),
           d = IF(@d = '', NULL, @d);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test.test;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
|    1 |    2 |    3 |    4 |
| NULL | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

Check the syntax for LOAD DATA for more details.


Questions on updating statistics and index maintenance jobs

For question 1 - Cancelling or stopping the job after it is doing work will not roll back the work that has already been done, so there is no harm to stop it or kill the execution.
For question 2 - Keep in mind that online index rebuilds do still take some locks and there are limitations involved as to whether an online index can be performed. I would look here for a discussion of those limitations. I probably wouldn't run this script during the day, even if you can get the online index rebuilds to happen. Index maintenance can be resource intensive.
For question 3 - Ola's scripts aren't going to do anything that is going to damage the database.
When I looked at your other question, I had to wonder whether the index maintenance routine is causing the log file growth.
You say, "Because of the problem with the log partition running full after every database update I've received a different script which where I can set Proportion of maximum indexes to be maintained per database. But I think this script is not as powerful as olas script, maybe because it doesn't update statistics or doesn't rebuild enough indexes? I've inserted a lot of new data recently and some querys are really slow since then and it's up to the database."
It seems to me that part of your complaint is things run slow after you update and change a large amount of data and people suggested updating the statistics. You could do that with a separate Ola job with T-SQL like the below, which is on his site.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'


The process could not connect to Subscriber ''

I recently started to document Replication issues since they are usually unique, and can be troublesome.

if the Subscriber is NULL

Likely this is a problem where the subscription was setup on one side yet not on the other or became corrupt so that an empty value returns. In these cases, recreating Subscription Agent is often the best solution to clean out the errors in the Distribution database.

Generate the publication scripts via SSMS for creating and dropping the subscriptions onto their own query window.
ONLY run the drop subscription to the problem Subscription.
Manually ensure this subscription no longer exists as a job and/or subscription on the Subscriber side.
Run the create subscription statement. Check that the @sync_type is also set properly. Sometimes this can be incorrect.
Generate the snapshot by either starting the Snapshot Agent for the publication or right click on the publication in SSMS and select the Reinitialize Subscription... and select generate new snapshot.

If the Subscriber is not NULL but still has problems

The SQL Agent or some AD account used during the replication process need to be checked.

Check the Type of Replication and start from the side the replication Agent is executed from normally.
Check if the replication is supported.

It is considered supported if the Distributor and the Subscriber are at least 2 Major editions from each other. For example, SQL Server 2008 R2 to 2014 is supported but not 2008 R2 to 2016 and vice versa. Deprecated Features in SQL Server Replication | Microsoft Docs
If unsupported...well, you can guarantee odd issues come up and this will require lots of patience.

Check the Agents Involved using the following query on each Instance (Distributor and Subscriber)

SELECT servicename, service_account
FROM   sys.dm_server_services

Ensure the Instance Has a valid Login and that the database has a valid User Login Mapped to that database as db_owner

Check the Replication Agents from The source of the Subscription Agent (Pull on the subscriber and Push on the Distributor)

The Distribution Agent is db_owner of the database for Replication.
The subscription Agent is also db_owner of the replicated database and a member of the PAL or Publication Access List | Microsoft Docs.

Check the AD account is valid on the server box.

the Agent must be able to Login into the server if using an AD account or directly to the Instance via a valid SQL Server Login. Please note that a SQL Server Login is not sent using very robust encryption methods and is considered a weak form of security. Utilize Active Directory Objects instead to hide the credentials from unwanted intruders.
For both servers, verify the AD account is also valid for the Snapshot Folder used by replication. If the Agent is the Distributor, it needs at least write access. If the Agent is the subscriber, it needs at least read access.

Most likely, one of these steps will reveal the mystery...though, for unsupported replications I have seen a few odd situations.


From 2012 onwards this is possible and can be done with help OF sys.dm_exec_describe_first_result_set DMV.

  This dynamic management function takes a Transact-SQL statement as a
  parameter and describes the metadata of the first result set for the
  statement

You can refer here for a read on this sys.dm_exec_describe_first_result_set

Also, refer to this post explaining the same. Is there a way to generate table create script in TSQL?


SQL Server - NOLOCK vs. READONLY connections

Locking is mainly used to help writes and reads write and retrieve data that is ACID compliant.  Thus a 'read only' query can have several layers of locking.  By default, MS SQL Server uses pessimistic locking where as Oracle uses optimistic locking.  That means in SQL Server reads by default will always block writes and vice versa.  In Oracle's default isolation level, reads do not block writes.  Thus unless you are familiar with your isolation level and if you're OK with dirty reads, then technically could.  However that doesn't mean you should.

Read up on some of the issues you'll face with NoLock and alternatives.

Copy/paste:

  NOLOCK Effects

  What are the effects you need to worry about? Well, others, like Andrew Kelly, have described this before, Paul White digs quite deep into it, these search results are quite telling, and Kendra Little even has a video about it… but I'll point them out anyway:

  "Dirty read" – this is the one most people are aware of; you can read data that has not been committed, and could be rolled back some time after you've read it – meaning you've read data that never technically existed.

  Missing rows – because of the way an allocation scan works, other transactions could move data you haven't read yet to an earlier location in the chain that you've already read, or add a new page behind the scan, meaning you won't see it at all.

  Reading rows twice – similarly, data that you've already read could be moved to a later location in the chain, meaning you will read it twice.

  Reading multiple versions of the same row – when using READ UNCOMMITTED, you can get a version of a row that never existed; for example, where you see some columns that have been changed by concurrent users, but you don't see their changes reflected in all columns. This can even happen within a single column (see a great example from Paul White).

  Index corruption – surely you are not using NOLOCK in INSERT/UPDATE/DELETE statements, but if you are, you should be aware that this syntax is deprecated and that it can cause corruption, even in SQL Server 2014 RTM – see this tip for more information. Note that you should check for the hint in any views that you are trying to update, too.

  Read error – because the underlying data could be moved or deleted during your read.


Security best practices for SSIS package execution via SQL Agent job

You don't need to use a proxy account nor do you need to grant sysadmin to your ETL team for them to be able to run SSIS packages and SQL Agent jobs.
You should first look into the SQL Server Agent Fixed Database Roles, such as the SQLAgentOperatorRole. (I'm guessing you have already, but in case you haven't yet.)
If that alone doesn't solve your issue then you should also look into Integration Services Roles (SSIS Service) - Roles in the msdb database. If you need the ETL team to be able to execute SSIS packages that they don't own, then you'll want to particularly look into the db_ssisoperator role or db_ssisadmin role which are certainly more limited than sysadmin. (It sounds like db_ssisoperator is probably what you want.)
One caveat with the db_ssisadmin role is it has the ability to run an SSIS package that can execute the T-SQL to grant the sysadmin role to a user (if the package runs under an existing sysadmin account), should someone on your team feel maleficent enough to create such a package and run it, as mentioned in this warning:

**** Warning ****Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent.

If you only need your ETL team to be able to run only their own packages, then the above warning is moot, because you can use the db_ssisltduser role instead.


test data to generate high index fragmentation level

You can use Paul Randal's script or similar to it.

The idea here is to create two similar table (of the same size) with clustered PK on identity (or other ever increasing key), then to drop the first created table and to SHRINK database.

Since SHRINK takes the pages from the end of data file and moves them to file beginning your second clustered index will be "inverted" and will be perfectly fragmented as the physical order now will be perfectly inverse to its logical order.

Why you should not shrink your data files

-- Create the 10MB filler table at the 'front' of the data file
CREATE TABLE [FillerTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'filler');
GO

-- Fill up the filler table
INSERT INTO [FillerTable] DEFAULT VALUES;
GO 1280

-- Create the production table, which will be 'after' the filler table in the data file
CREATE TABLE [ProdTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO

INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1280

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO

-- Drop the filler table, creating 10MB of free space at the 'front' of the data file
DROP TABLE [FillerTable];
GO

-- Shrink the database
DBCC SHRINKDATABASE ([DBMaint2008]);
GO

-- Check the index fragmentation again
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO


pg_dump --exclude-table not working on one specific table

Including the schema fixed it. The other tables were in the public schema but article_plain was in a schema of it's own:
postgres@server:~/bin$ pg_dump -s --clean clientportal -T otrs.article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log | grep article_plain
DROP SEQUENCE otrs.article_plain_id_seq;
-- Name: article_plain_id_seq; Type: SEQUENCE; Schema: otrs; Owner: sg1admin
CREATE SEQUENCE article_plain_id_seq
ALTER TABLE article_plain_id_seq OWNER TO sg1admin;
-- Name: article_plain_id_seq; Type: ACL; Schema: otrs; Owner: sg1admin
GRANT ALL ON SEQUENCE article_plain_id_seq TO sg1;


Is it possible to take incremental backup in MongoDB?

MongoDB doesn't provide an incremental backup option out of the box, but it's possible to do a file snapshot and replay the oplog.

As per MongoDB BOL Here If the volume where MongoDB stores its data files supports point-in-time snapshots, you can use these snapshots to create backups of a MongoDB system at an exact moment in time. File system snapshots are an operating system volume manager feature, and are not specific to MongoDB. With file system snapshots, the operating system takes a snapshot of the volume to use as a baseline for data backup. The mechanics of snapshots depend on the underlying storage system.

For example, on Linux, the Logical Volume Manager (LVM) can create snapshots.

For your further ref  Here and Here


How to recover .ibd and .frm files in MySQL

*.ibd files are not enough, they store data, but InnoDB needs a dictionary which is stored in ibdata1.

To recover the tables you'll need a linux box. Then follow instructions in http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/

Or you can try method described by Chris Calender http://www.chriscalender.com/?p=28

In the worst case you'd need to fetch records from ibd files and then reload into fresh InnoDB instance. See instructions here http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start

UPDATE: Data recovery toolkit moved to GitHub


Could not obtain exclusive lock on database 'model' - who is using it?

Your model database is locked now so what you should do is to find out the session that has a lock on it, this can be done using sys.dm_tran_locks:
select request_session_id
from sys.dm_tran_locks
where resource_type = 'database' and
      resource_database_id = 3 and
      request_type = 'LOCK' and
      request_status = 'GRANT';

You can have a user that just opened an SSMS query window with the context of model database and did not ever execute any query there, but that session still holds S lock on the database resource.
Once you have found the locking session, just kill it.


Oracle 11gr2 instant client installation

I'd say that Oracle Call Interface and Oracle Java Client, plus may be SQL*Plus if you want to be able to run scripts, should be sufficient.