Finally I've got an answer at pgsql-general mailing list by Adrian Klaver (link)

There is some kind of unexpected behavior with using both --set ON_ERROR_STOP=on and --single-transaction options. The ON_ERROR_STOP stops the processing, but does not rollback current transaction in case of \include error.

So, the solution for my initial problem will be removing the single-transaction option and wrapping explicit BEGIN/COMMIT statements around my main script.


Optimizing bulk update performance in Postgresql with dependencies

Given that you can not afford to drop and recreate the table, this related answer would be a better fit:

Best way to populate a new column in a large table?

You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable).

And all the general advice for performance optimization applies.

There is not much more you can do, if you have to update the table bit by bit. Faster alternatives drop and rewrite / recreate from scratch.


How can I change three hundred procedures at once?

If you script the procedures out to a file, the search/replace can be trivially dome with a sed script along the lines of

s/\/file\/folder\/documents/directory_name/g

(note not tested, just off the top of my head, but you can fiddle with it).

Then you can re-load the stored procedures.  Note that if you're frigging with the code base you should really test what you're doing rather than doing a blind search/replace on production code.  What could possibly go wrong?


Scaling down DB where filesystem is almost full

If you are not experienced at both mysql and linux sysadmin I would suggest backing off from this task: if this is a production system there could be much trouble if something goes wrong and data is lost! If you do proceed, take care to make sure you take, and test, fresh backups before doing anything.

I'm assuming the tables are of InnoDB type as that is the default these days. IIRC InnoDB files can not be shrink by the standard tools: when data is deleted the space is just left unallocated internally (not released by to the filesystem) to be used again as more data comes in. As your file is not ibdata I assume you have the "one file per innodb table` option turned on, in which case to free unallocated space within the file, dump that table to file, drop the table, recreate the table and then import the dump file back into it. If there is little free space in the file to start with (i.e. there has been very little delete activity recently) this won't help much though: the resulting file will be more-or-less the same size after you have finished. Also, as the database will be in an inconsistent state during the process, you should block access to it (aside from the admin accounts you need to perform the dump/drop/rebuild/restore of course) for the duration.

If your server uses LVM for space/partition management then you may have free space in the appropriate volume group and can just expend the volume to allow extra space. If you have room on other file-systems you could move the file to one of those and link to it from its currently location (IIRC mysql is happy to follow softlinks to find data-files) like so:

stop mysql
move the files to a volume with more space (though not /tmp or anything with tmp in the name - these may be wiped on start-up after reboot or power-fail)
create a sym-link with the same name in the old location pointing to the new on
restart mysql

(obviously all your databases on this machine will be inaccessible during this process)

To see what free space you have where on current filesystems run df -h. If you are using LVM run the commands lvs, vgs and pvs to see what space you have where outside that currently allocated to filesystems (if you are not using LVM these three commands will either return nothing or not be found). I may be straying a tad far off-topic here IMO (out of DBA-land and into ServerFault territory) so I'll not go into more detail.

I'll say again though: if you are not a confident DBA+sysadmin and this is a production system I would stay away. Failing that, at least try practice on another system first (do you have a development environment setup similarly to the production one?) so you can verify what you are about to do before doing it on real data.

If you do do anything make sure you fully document your actions (all commands entered and why, all output received) so your usual DBA/sysadmin can review things upon return and make any further adjustments that may be needed to keep things running well long-term.


Create File Stream Enabled Database in Network File (NAS Server)

I didn't used Filestream enabled database before. Recently I had to install one for a document tracking and archiving software. I wanted to put the db on nas server because I don't have too much space on server harddisk. After some research I found out even I can put data and log files on nas I can not create filestream enabled database using nas so I installed totally on server instead.
"Thank You" for Whom Who Viewed my question.

Ferda


A query that only reads data will not block normal user activity. Readers don't block writers and vice versa.
However, it may consume substantial resouces on the database server, which could impair performance for the users.


DMV CONNECTION_HOST_APPLICATION field is NULL

Finally after a lot of research, I, found the solution by adding the property of "application name" in connection string that connects to analysis services instance.

ApplicationName='testapp';

So now both SQL Server Profiler and DMVS are able to tracks application name.


A materialized views is not a replacement for a database link. It would likely create a local materialized view using data pulled accross a database link. What you need to do is tune the query that you are using for your materialized view.

Run the query in sqlplus with timing and autotrace turned on. Look for cartesean joins, full table scans and sorts. There are probably some additional indexes or changes to indexes that you can make that will make the query cheaper to run. You want all of the filtering and sorting to happen on the source side of the link so that you are bringing accross as little data as possible.


service mongod cant start, permission denied mongod.lock when changing dbpath

If you now command:

sudo chown -R mongodb:mongodb  /home/user/mongodb
sudo service mongod restart

It will work!

Problems here was that you gave command sudo mongod --dbpath /home/user/mongodb/data/ what created ROOT ownded files to that /home/user/mongodb/data/ directory and your "normal" mongodb user don't have right to overwrite those.

So, NEVER start your mongod as root OR if you do so, remember reset ownership of those data files back to mongodb.


Mysql search matching multiple values in multiple columns

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options:

Update to MySQL 5.6 and use a FULLTEXT index
Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB.
Convert to a MyISAM table, or create a spare MyISAM table that you can join on specifically for searches.

Since you're building the query in Java, I'd also suggest removing the CONCAT('%' stuff in favor of just creating a second variable that has the %s already applied. That saves the DB from the extra work of parsing it and lets it utilize indexes better. Speaking of indexes, be sure you have indexes that span the columns where possible.

Regarding the Duplicate removal, you may be better off performing a GROUP BY p.idNumber to save the extra effort and data transfer in Java.


Why is there no max(uuid)/min(uuid) function?

I'd say that nobody saw any use in calculating the maximum of UUIDs, and I cannot see one either.
That said, it is easy to use CREATE AGGREGATE to define your own max, if you really need that.


Can't restart or stop Firebird 1.5 on linux

I think I found the solution, but I believe it is more related to software then database (So if someone knows how to move this to stack overflow please do this).

The problem was that when hibernate (the persistence layer) works with Firebird 1.5, it doesn't create the temporary tables used by hibernate like in other new databases, instead it creates actual tables with the prefix HT_ (HT_SOMETABLE). I tried to deactivate this with a "hack", until now it's working fine, no more hanging and nothing unusual has happened because of this hack.

This is what I did:

I created a custom Firebird dialect to stop hibernate from creating these tables:

import org.hibernate.dialect.FirebirdDialect;

public class FirebirdHackedDialect extends FirebirdDialect {

    @Override
    public String getCreateTableString() {
        String sql = "HackParaNaoCriarTabelasNoFirebird: ";
        return sql;
    }

}

And set this dialect to the persistence.xml:

<property name="hibernate.dialect" value="com.yourpackage.etc.FirebirdHackedDialect"/>


Unit of COST in an Oracle execution plan

Unfortunately the answers are as follows:

  Is there a unit for cost in an Oracle execution plan?

Not really.

  I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage?

Nope.

  What does this number stand for?

It's defined like this: (see the glossary)

    A numeric internal measure that represents the estimated resource usage for an execution plan. The lower the cost, the more efficient the plan.

That's about all you'll get in terms of definition of that number.

  Is there any way I can deduce how many seconds or ms this operation is going to take to complete?

No. It's a value that, in my experience, should only be used to compare different plans for the same query, and then with a bit (well, a lot) of salt. If one plan has a cost of 10, and another a cost of 1M, chances are the first will be faster. If the costs are close, chances are they'll have similar runtime behavior, but that's not always the case.

The The Oracle Optimizer - Explain the Explain Plan (PDF) document has a few words on that value, but:

    The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query. [...]
    The cost is an internal unit and is only displayed to allow for plan comparisons.

How does Oracle calculate the cost in an explain plan? on Stack Overflow has some good input related to that cost value (including a "formula" for Oracle 9i), and good advice from Justin Cave: the cardinality estimates are more meaningful, and you can actually compare them with reality.


USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)

Log.xml is the new log format of oracle database will be  substitute the actual alert.log in future (according to oracle roadmap).
Basically you can find in the log.xml all information available in the alert.log with some more details, like the USER_ID. However the database log file does not aim to alert you about specific user issue but only to problem might be considered database-wide.

This means that for almost all problem there is no USER_ID, like for

ORA-00257: archiver error. Connect internal only, until freed.

But for some issues caused and related to specific user action the USER_ID will be present. For example the alert log provide you only this line:

ORA-1555 snapshot too old: rollback segment number %s with name \"%s\" too small"

(in 11g you have also part of sql statement)

The log.xml will show more data. Furthermore, log.xml is close part of ADRCI, the tool for incident diagnose and management, and to do its own work needs more data than the simpe ORA-xxxx.

So, everytime a USER_ID is applicable to the error you will find in the log.xml, otherwise no.


SQL Server: how to troubleshoot login timeout expired error

A lot of time while connecting to this server from a remote host I to face connection timed out error. Developers also reported same error from their scripts as well.

Interestingly, it's not "all the time" which sounds like you can connect "some" of the time. Since this is intermittent it definitely sounds like a name resolution issue. Try registering and connecting to SQL Server with only the SQL Server IP address within SQL Server Studio.  For named instances you can type something like W.X.Y.Z\SQLInstanceName.


Are stored procedure query texts stored in any tables?

There are several ways to get this that require different input information

INFORMATION_SCHEMA:

You only need to know the name of the object you want the definition for.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcName'

Note, this schema is database specific, so you need to be in the context of the database where the procedure exists for this query to return the correct data. Also note, it only returns the first 4000 characters of the procedure.

OBJECT_DEFINITION:

You need to know the object_id of the procedure you're looking for.

SELECT OBJECT_DEFINITION(*object_id*)

Database-specific as well, but returns the complete text of the object.

sys.sql_modules:

You need to know the object_id of the procedure you're looking for.

SELECT * FROM sys.sql_modules WHERE object_id = *object_id*

Database-specific as well, but returns the complete text of the object.


Attatch database - cannot browse to database

Just to eliminate access or permission related issue.... Check the following below:
**What login is running the SQL Service - For testing, change this to localsystem or a local administrator
**Are you running your SSMS with administrative privileges - If not run as administrator before connecting to your instance

Also try using other modules in SSMS that would require a browsing to a file and check if that works. For example, instead of using the attach option, test using a restore from backup. HTH


Enabling MSSQL System Trace for DB Objects

What you are using is part of a default trace, 2005 to 2012 SQL Server uses this but it is deprecated and in later versions system_health is an Extended Events session that is used going forward.

MSDN on default trace.

MSDN on system_health.

The default to store trace data for SQL Server is 20MB, so it is not a time based operation per se, but a space based. So it makes sense you can't see everything but you do see some data.

MSSQLTips: Aaron Bertrand on historical traces.

A common recommendation is to create a script setup to copy the data into a table that won't be deleted or to move and aggregate the trace log files.

For Extended Events, SQL Server will store up to four 5MB files, below is an article to view and adjust those settings for greater retention.

SQLWorldWide:Taiob Ali on Extended Event file retention.


Postgres: Use result of group by as column names

If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression:

select user_id,
  sum(case when message_type = 'private' then 1 else 0 end) private,
  sum(case when message_type = 'public' then 1 else 0 end) public
from yourtable
group by user_id

See SQL Fiddle with Demo

PostgreSQL has the ability to use a crosstab() that can be used by installing the tablefunc module.  This will perform a similar data transformation from rows into columns. Creating a dynamic version of the query is not a straight forward process, here is a great solution for a dynamic crosstab on StackOverflow.


Not Auto and User Generated Stats in Catalog view of SQL Server

Those are the statistics created when index was created. Quoting from BOL Statistics

  The Query Optimizer creates statistics for indexes on tables or views when the index is created. These statistics are created on the key columns of the index. If the index is a filtered index, the Query Optimizer creates filtered statistics on the same subset of rows specified for the filtered index. For more information about filtered indexes, see Create Filtered Indexes and CREATE INDEX (Transact-SQL).


Monitor Database Memory usage SQL Server

You can use the below query to determine the Memory usage at database level:

SELECT

(CASE WHEN ([database_id] = 32767)
    THEN N'Resource Database'
    ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
 FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id];
   GO

If you want to analyse further at object level that which object in that database from above query is using a lot memory use below query:

 EXEC sp_MSforeachdb
N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
 USE [?]
SELECT
''?'' AS [Database],
OBJECT_NAME (p.[object_id]) AS [Object],
p.[index_id],
i.[name] AS [Index],
i.[type_desc] AS [Type],
--au.[type_desc] AS [AUType],
--DPCount AS [DirtyPageCount],
--CPCount AS [CleanPageCount],
--DPCount * 8 / 1024 AS [DirtyPageMB],
--CPCount * 8 / 1024 AS [CleanPageMB],
(DPCount + CPCount) * 8 / 1024 AS [TotalMB],
--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
    FROM
   (SELECT
    allocation_unit_id,
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 1 ELSE 0 END) AS [DPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE 1 END) AS [CPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
FROM sys.dm_os_buffer_descriptors
WHERE [database_id] = DB_ID (''?'')
GROUP BY [allocation_unit_id]) AS buffers
 INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
 INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
 INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than   100MB
  ORDER BY [FreeSpacePC] DESC;
  END';

Use the querys above in SP's to run at a time period to collect the data you require and this would help you analyse better!


Why Oracle SQL Developer does not need Oracle client?

SQL Developer is using the so called "thin driver".

For this the JDBC driver (a file named ojdbc6.jar) implements the complete Oracle network protocol which is usually handled by the Oracle client. It could use the another implementation: the "OCI" (or "thick") driver which would require an Oracle client to be installed. That would be necessary if e.g. Transparent Application Failover in a RAC is required.

This is not something specific to SQL Developer but to any Java application using JDBC to connect to the database.


SQLite - What are .acd, .ard and related file types?

The -wal and -shm files are used to store data when the DB is in WAL mode.

SQLite does not require any specific file name or extension. These file names are chosen by the program that created the database.


Decide which you type of "relationship" each "link" is:  "1:1", "1:many", or "many:many".

1:1 should be represented by having the columns in the same table.  (There are exceptions, but you aren't there yet.

1:many is implemented via an id of the "1" in the "many" table.  Example:  A person owns "many" cars.  The cars table has a column person_id to say who it is owned by.

many:many needs an extra table with ids of both of the tables.  Example:  students and classes:

CREATE TABLE WhoTakesWhat (
     student_id INT UNSIGNED NOT NULL,
     class_id INT UNSIGNED NOT NULL,
     PRIMARY KEY(student_id, class_id),
     INDEX(class_id, student_id)
) ENGINE=InnoDB

I encourage you to be thinking in terms of SQL; you will eventually have to use it.

Meanwhile:

A "database" contains several "tables".
A "table" is a set of "rows" and "columns".
"Columns" tend to be dissimilar (id, name, date, ...)
"Rows" are different instances of what is being stored in the table (various "students" in the table named students)


Iterating through a record set and selecting certain records

As I understand it you want to find records where running total of POQuantity is less than the ReturnQuantity values.

Windowing functions are ideal to help with this problem!  See these resources for further info:

https://www.brentozar.com/sql-syntax-examples/window-function-examples-sql-server/
https://www.red-gate.com/simple-talk/sql/learn-sql-server/window-functions-in-sql-server/
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Ive mocked up a quick example from your data to demonstrate this:

DECLARE @Data TABLE(
    ItemNumber            INT
    ,POQuantity            DECIMAL(20, 2)
    ,PurchasePrice        DECIMAL(20, 2)
    ,CreatedDateTime    DATETIME2(3)
    ,ReturnQuantity        DECIMAL(20, 2)
)

INSERT INTO @Data
VALUES
    (138799528, 4, 518.40, '2017-11-02 09:43:18', -18)
    ,(138799528, 8, 518.40, '2017-06-06 15:15:00', -18)
    ,(138799528, 12, 480.00, '2017-01-26 15:52:33', -18)
    ,(138799528, 8, 450.00, '2016-11-14 11:36:42', -18)
    ,(100, 3, 100.00, '2017-11-02 09:43:18', -4)
    ,(100, 7, 150.00, '2017-10-02 19:30:12', -4)
    ;

WITH CalculateRunningTotals AS (
    SELECT
        ItemNumber
        ,POQuantity
        ,PurchasePrice
        ,CreatedDateTime
        ,ABS(ReturnQuantity)    AS ReturnQuantity    /* Make it a positive number for easier calculations later */
        ,SUM(POQuantity) OVER (PARTITION BY ItemNumber ORDER BY CreatedDateTime DESC)    AS RunningTotalQuantity
    FROM
        @Data
), FindApplicableData AS (
    /* We need to consider the previous running total to make sure
       we get "at least" the total POQuantity.  Otherwise we miss the
       last row needed.
    */
    SELECT
        *

        ,CASE
            WHEN RunningTotalQuantity <= ReturnQuantity THEN POQuantity
            ELSE ReturnQuantity - (RunningTotalQuantity - POQuantity)
        END AS POQuantityForAverages    /* Calculate how many "POQuantity" of this row are needed to make up the ReturnQuantity */
        ,RunningTotalQuantity - POQuantity AS PreviousRunningTotalQuantity

    FROM
        CalculateRunningTotals
    WHERE
        ABS(ReturnQuantity) >= (RunningTotalQuantity - POQuantity)    /* Compare to previous running total - remove this to see why you need it ;-) */
)
SELECT
    ItemNumber
    /* Calculate the average price - its not a simple AVG as we need to include the quantities sold at various prices */
    ,SUM(POQuantityForAverages)                                                                            AS TotalQuantities
    ,CAST(SUM(POQuantityForAverages * PurchasePrice)    AS DECIMAL(20, 2))                                 AS TotalPrice
    ,CAST(SUM(POQuantityForAverages * PurchasePrice) / SUM(POQuantityForAverages)    AS DECIMAL(20, 2))    AS AvgPrice
FROM
    FindApplicableData
GROUP BY
    ItemNumber

As you can see, this no longer needs a cursor.  This would allow you to create this as a view to make your subsequent querying much easier.

Hope this helps you.


No need to serialize. A relational database (such as MySQL) is intended to store one fact per row. This is the proper way to do it.

Have an Ailments table. It will have 200 rows - one per ailment. This will be used to populate the user's check list.

On save, write each checked ailment to a UserAilment table. It will consist of a foreign key to the user table and a foreign key to Ailments.


What's MongoDB's default behaviour when no sharded collection is specified

The answer is for existing data NO. But when you create new DB, different databases have different primary shard (system use round robin selection criteria), but as long as the collection (at that DB) is not sharded, all data stays at that primary shard.


Summing the count of two columns in postgres that contain the same value

You can get it converting the two columns into a single column.

SELECT node, COUNT(*) as node_count
FROM
(
    SELECT id, node1 as node
    FROM   t1
    UNION
    SELECT id, node2 as node
    FROM   t1
) t2
GROUP BY node
HAVING COUNT(*) > 1
ORDER BY node;

node | node_count
---: | ---------:
 111 |          5
 122 |          2
 124 |          2

db<>fiddle here


Restore System Databases from MDF/LOG files

msdb and model you may be able to recover data from by following the same process as you would if moving them. Use a test/dev instance for a dry run.

Stop SQL
Start with trace flag 3608 (master only recovery mode) NET START MSSQLSERVER /f /T3608
ALTER DATABASE msdb|model MODIFY FILE( NAME=xyz, FILENAME='filepath')
Stop SQL, move files in to place, restart SQL

KB914171 details one possible error that may be reported and a fix/workaround by:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go


MongoDB - real disk usage is LOWER than dbStats.storageFile

Whatever you do, do not shut down that mongod process until you back up your data (see below). There are missing files in that database directory, and I suspect they have been manually deleted at the OS level.  The data files should not have any gaps in them, ever.  In other words you should have files starting at myBase.0 all the way up to myBase.37, there should be no gaps in the numbers.

To explain, if you delete the files using rm or similar at the OS level it will succeed, the OS allows it, but because the mongod process that is running has an open file handle to the files they will not actually be deleted by the operating system until you stop the process.

Here's an example of what the lsof command shows for a normal data file called foo.0:

mongod     5786             adam  mem       REG                9,0   67108864
805306654 /data/db/test0/foo.0

And here is what it looks like when you have manually deleted the file:

mongod     5786             adam   24u      REG                9,0   67108864
805306654 /data/db/test0/foo.0 (deleted)

From within MongoDB that file still exists and is accessible, I can query, run db.stats() etc. successfully, but if that mongod process is restarted the file will be removed and the data is at that point essentially gone (barring efforts to undelete at the filesystem level).

So, what should you do?  Well, the first thing is to make sure you have a copy of the data before shutting down that process and losing it.  To do that you have a couple of options:

If this is a node in a replica set (even single), add a new secondary set member and let it sync - that will still succeed and then you will have a fully populated version of the data ready to take over on that secondary. (Note: If this is not a replica set you can't turn it into one without a process restart, and that would delete the data - my recommendation is to always run as a replica set, even a single node for anything in production)
Run mongodump to dump the data out somewhere else before it gets deleted.  This won't be fast, and you will need plenty of space, but at least it will give you an easily restorable version of your data

A repair on the database might work, but only if you have enough space to accommodate 2x the data plus index size on that disk.  It must be a repair command, not a restart with --repair because the restart would cause the files to be deleted.

Finally, you need to figure out what is deleting these files and stop it - is there a cron job or other process that is automatically deleting large files (the data files will usually be 2GB) over a certain age or similar?  I've seen things like that before wipe out MongoDB data files with similar results.


Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

Based on your comment try something like this I think

select datetime, PLANT_STATUS, INCOMER_PWR, PLANT_DEMAND, PLANT_OUTPUT
from d909 as d1
where
    (PLANT_STATUS >=400 AND PLANT_STATUS < 500)
UNION ALL
select datetime, PLANT_STATUS, INCOMER_PWR, PLANT_DEMAND, PLANT_OUTPUT
from d909 as d1
where (select COUNT(*)
        from d909 as d2
        -- find all records within a minute of the first set of records
        where abs(DATEDIFF(MI,d2.datetime,d1.datetime)) <= 1
        -- not sure if this status check required
        AND (PLANT_STATUS >=400 AND PLANT_STATUS < 500)
        ) > 1
AND (PLANT_STATUS >=400 AND PLANT_STATUS < 500)


mongodb set feature compatibility to 3.4 fail

You probably ran your command

 db.adminCommand ({getParameter: 1, featureCompatibilityVersion: 1})

on a mongos instance. The documentation states in this case:

  The operation is undefined on the mongos instances. For a sharded cluster that has access control enabled, to run the command against a member of the shard replica set, you must connect to the member as a shard local user.

When you connect to a mongod instead, the command should work as expected.


How to handle duplicates in an UPDATE on a primary key in Postgres?

We need move stocks between companies, right? What means we need add stocks to existing users and change company for new users. Or, what the same, we could delete all stocks of company "foo" and insert .. on conflict new rows for company "bar"
with rows as (
  delete from PersonCompanyStocks
    where company = 'foo'
    returning person, num_stocks
)
insert into PersonCompanyStocks (person, company, num_stocks)
  select person, 'bar', num_stocks from rows
  on conflict(person,company) do update set
    num_stocks = PersonCompanyStocks.num_stocks + excluded.num_stocks;

Transactional, no race conditions here thanks to row locking during delete.


Count duplicate occurrences in MYSQL table based on value in two columns using having and inner join.

You can reduce the queries to one:

select owner_id, sum(vehicle_type='Car') as cnt_car, sum(vehicle_type='Van') as cnt_van
from vehicles
where vehicle_type in ('Car', 'Van')
group by owner_id
having cnt_car > 1 and cnt_van > 1

Apart from that, the index that would provide the best performance would be a combined index on the columns (owner_id, vehicle_type).


Your tables are incredibly small. You should be able to run

OPTIMIZE TABLE table1, table2, table3;

very quickly.

Looking at the statistics in your question

Handler_read_rnd_next : The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Handler_read_prev : The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC.
Created_tmp_disk_tables : The number of internal on-disk temporary tables created by the server while executing statements. If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
Opened_tables : The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

If these global status variables present a problem after running OPTIMIZE TABLE, this is usually a strong indicator that you have to improve the design of your queries and the size of certain caches.


MySQL: Why is DATEDIFF and TIMEDIFF/60.0/60.0/24.0 results different?

The answer lies in fully understanding the TIMEDIFF function and what it returns as an integer.

First, let's consider TIMEDIFF in its native element and what it returns:

mysql> select timediff('0:0:0','1:0:0') as timediff;
+-----------+
| timediff  |
+-----------+
| -01:00:00 |
+-----------+
1 row in set (0.00 sec)

Second, what does TIMEDIFF return in an integer context - done by adding zero:

mysql> select timediff('0:0:0','1:0:0')+0 as timediff2;
+--------------+
| timediff2    |
+--------------+
| 10000.000000 |
+--------------+
1 row in set (0.01 sec)

Now, we use the TIME_TO_SEC function to return what we actually want:

mysql> select time_to_sec(timediff('0:0:0','1:0:0')+0)/60 as timediff3;
+-----------+
| timediff3 |
+-----------+
|   60.0000 |
+-----------+
1 row in set (0.00 sec)

The integer value returned by TIMEDIFF is not minutes or seconds, but rather a base-10 representation of the actual time - so 01:00:00 becomes 10000, and 05:05:01 becomes 50501.

Instead of using TIME_TO_SEC one can also use TIMESTAMPDIFF instead of TIMEDIFF and set the units to whatever is desired:

mysql> select timestampdiff(MINUTE,'2012-04-13 0:0:0','2012-04-13 1:0:0') as timediff4;
+-----------+
| timediff4 |
+-----------+
|        60 |
+-----------+
1 row in set (0.00 sec)

Instead of MINUTE, you can also use FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The time functions in MySQL (in this case, version 5.1) are all described in the manual. The online manual also links to other versions (such as MySQL 5.5) as well.


This is very easy to prove on your own. We can create a table with a computed column that uses a scalar user-defined function, and then check plans and function stats before and after both an update and select, and see when an execution gets recorded.
Let's say we have this function:
CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT 'XX' + SUBSTRING(@x, 3, LEN(@x)-4) + 'XXXX');
END
GO

And this table:
CREATE TABLE dbo.Floobs
(
  FloobID int IDENTITY(1,1),
  Name varchar(32),
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)),
  CONSTRAINT pk_Floobs PRIMARY KEY(FloobID),
  CONSTRAINT ck_Name CHECK (LEN(Name)>=8)
);
GO

Let's check sys.dm_exec_function_stats (new in SQL Server 2016 and Azure SQL Database) before and after an insert, and then after a select:
SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

INSERT dbo.Floobs(Name) VALUES('FrankieC');

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

SELECT * FROM dbo.Floobs;

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

I see no function call on the insert, only on the select.
Now, drop the tables and do it again, this time changing the column to PERSISTED:
DROP TABLE dbo.Floobs;
GO
DROP FUNCTION dbo.mask;
GO

...
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)) PERSISTED,
...

And I see the opposite happening: I get an execution logged on the insert, but not on the select.
Don't have a modern enough version of SQL Server to use sys.dm_exec_function_stats? No worries, this is captured in the execution plans, too.
For the non-persisted version, we can see the function referenced only in the select:

While the persisted version only shows the computation happening on insert:

Now, Martin brings up a great point in a comment: this isn't always going to be true. Let's create an index that doesn't cover the persisted computed column, and run a query that uses that index, and see if the lookup gets the data from the existing persisted data, or computes the data at runtime (drop and re-create function and table here):
CREATE INDEX x ON dbo.Floobs(Name);
GO

INSERT dbo.Floobs(name)
  SELECT LEFT(name, 32)
  FROM sys.all_columns
  WHERE LEN(name) >= 8;

Now, we'll run a query that uses the index (actually it uses the index by default in this specific case anyway, even without a where clause):
SELECT * FROM dbo.Floobs WITH (INDEX(x))
  WHERE Name LIKE 'S%';

I see additional executions in function stats, and the plan doesn't lie:

So, the answer is IT DEPENDS. In this case, SQL Server thought it would be cheaper to re-compute the values than to perform lookups. This could change due to a variety of factors, so don't rely on it. And this can happen in either direction whether or not a user-defined function is used; I only used it here because it made it that much easier to illustrate.


CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

I don't think that the SQL standard defines how to create indexes at all.

A quote from this Wikipedia page:

  Standardization

  There is no standard about creating indexes because the ISO SQL
  Standard does not cover physical aspects. Indexes are one of the
  physical parts of database conception among others like storage
  (tablespace or filegroups). RDBMS vendors all give a CREATE INDEX
  syntax with some specific options which depends on functionalities
  they provide to customers.

The Postgres manual seems to support this here:

  There are no provisions for indexes in the SQL standard.

More evidence under this related question on SO.


Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables?

You can do it in both SQL Server (see fiddle here) and IBM DB2 (fiddle):

I adapted your table create syntax slightly because it fails on SQL Server 2019 on the fiddle otherwise (I imagine it would fail on all versions of SQL Server). Your syntax works on IBM DB2.

The first table:

I had to change (SQL Server) the IDENTITY column slightly from u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, to u_id INT IDENTITY NOT NULL, otherwise the statement fails.

CREATE TABLE uregister(
u_id INT IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
u_mail VARCHAR(150) UNIQUE NOT NULL,
u_password VARCHAR(8) NOT NULL,
PRIMARY KEY(u_id,u_mail)
);

And I had to change (both servers) the line u_mail VARCHAR(150) NOT NULL, to u_mail VARCHAR(150) UNIQUE NOT NULL, otherwise the following error occurs on trying to create the child table. This is the case with all other servers AFAIK - you have to have a child's parent field be either UNIQUE or a PRIMARY KEY (on its own, and not just part of a PK (if it's only part of a PK, it is not guaranteed to be unique in that table!).

Msg 1776 Level 16 State 0 Line 1
There are no primary or candidate keys in the referenced table 'uregister' that match the referencing column list in the foreign key 'FK__question__email__286302EC'.
Msg 1750 Level 16 State 1 Line 1
Could not create constraint or index. See previous errors.

If the PK or UNIQUE constraint(s) are not present for that field in the IBM DB2 fiddle, this is the error:

[IBM][CLI Driver][DB2/LINUXX8664] SQL0573N  A column list specified in the references
clause of constraint "EMAIL..." does not identify a unique constraint of the parent
table or nickname "FIDDLE_UHGEYTFVKRUDCQDSXLQL.UREGISTER".  SQLSTATE=42890 SQLCODE=-573

Same thing for the dregister table!

CREATE TABLE dregister(
d_id INT IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
d_mail VARCHAR(150) UNIQUE NOT NULL,
d_subject VARCHAR(150) NOT NULL,
d_experience INT NOT NULL,
d_occupation VARCHAR(150) NOT NULL,
d_password VARCHAR(8) NOT NULL,
PRIMARY KEY(d_id,d_mail, d_subject)
);

Then, the two foreign keys are created without problem (as per the fiddle).

CREATE TABLE question(
q_id INT IDENTITY NOT NULL,
question VARCHAR(500) NOT NULL,
email VARCHAR(150) NOT NULL,
PRIMARY KEY(q_id, email),
FOREIGN KEY(email)  REFERENCES uregister(u_mail),
FOREIGN KEY(email)  REFERENCES dregister(d_mail)
);


Postgresql Equivalent of MySQL SHOW CREATE xxx

You cannot do it in SQL, but you can use the pg_dump executable to get the complete table definition:

pg_dump --schema-only --table=tablename databasename

This won't work for databases, however.


From your question I'd translate that you have the following table structure:

Orders:
    OrderId,
    OrderDetails

OrderLines:
    OrderLineId,
    OrderId,
    Shipped

I'd use the following statements for reading data:

-- shippable order lines
Select ol.*
from OrderLines ol
where ol.Shipped > 0

-- shippable orders
select *
from Order o
where Exists
(
    select *
    from OrderLines ol
    where ol.Shipped > 0
    and ol.OrderId = o.OrderId
)

In case the last script is again slow, than I'd guess that you need an index on the column OrderId in tabe OrderLines. In case this also doesn't work, then come back with the query that you used, the real tables definition and, most important, the EXPLAIN ANALYZE of your query.

PS: please keep in mind that I used the MS SQL flavor of the language, you might need small adjustments to make it work properly in PGSQL :-).


Is it ok to have huge page life expectancy?

Page Life Expectancy is simply how long (in seconds) a page will remain idle in the buffer before being removed to make room for another page.

There's no active harm in a very large PLE. It's possible that you've got more memory going to SQL Server than is absolutely required, but that's still not necessarily a bad thing.


Mariabackup Throws Unknown Argument Error

I'm pretty sure this error was caused by supplying the wrong username. After I resolved that, the error went away and the backup initiated.


What is a real-world use case for a marked transaction?

Marked transactions make it easy to do point-in-time restores without worrying about actual time. Instead, one restores to logical event.

As a practical example, consider an update that needs to be undone. Now, the update was such that a database restore is needed as wrong value was set to all the columns and original data is lost. Maybe a business analyst set VAT to 24% for all the products instead of a subset. We don't know what the previous VAT values were, so the update cannot be undone.

Restoring the database is simple, but how much of the transaction logs would be applied? At which point-in-time would one stop the log restores? Does the business analyst keep a detailed journal that records the beginning of the update? Maybe all that is remembered is that "it was done Monday afternoon". If there are lots of changes in the database, recovery to right spot is trial and error.

Should the update be done within marked transaction, the DBA will simply query msdb.dbo.LogMarkHistory table and pick symbolic name for the transaction. Log restore is then done with STOPBEFOREMARK = 'VAT update' parameter to stop at the precise point before marked transaction was started.


Selecting Records from a duplicate column whose value in another column is less than

Create Table

create table t1(
col1    varchar2(20),
col2    varchar2(20),
col3    number
);

Sample Data

insert into t1
select 'Tom','Blue','10' from dual
union all
select 'Tom','Orange', '12' from dual
union all
select 'Alex','Blue','30' from dual
union all
select 'Alex','Orange', '29' from dual
union all
select'Lisa','Orange','15' from dual
union all
select 'Lisa','Blue','14' from dual;

Select Query

select * from
  (
      select *
      from t1
  )
  pivot
  (
    MIN(col3) for col2 in ('Blue' as blue,'Orange' as orange)
  ) where blue<orange;

Output

COL1  BLUE    ORANGE
Tom   10      12
Lisa  14      15

dbfiddle


Can't connect to MySQL database over SSH with Workbench

Since you are connecting through a SSH tunnel, this means that the MySQL port  3306 from us-east-1.amazonaws.com is being opened locally on your computer. The ip address of your computer is 127.0.0.1 or localhost.   When you connect to the mysql server on us-east-1.amazonaws.com, you're actually accessing it via 127.0.0.1, i.e. your computer.  If you had another tunnel open, or MySQL running locally on your computer, then it may be that other MySQL server that's rejecting your authentication attempts

There are some tests you can try :

1. What ports is you Windows computer listening on

From a command prompt : netstat -a (lists all the ports that are open)

In linux it would be : netstat -tlpn

2. Basic connectivity test

From a DOS command prompt or linux console : telnet 127.0.0.1 3306

If you get a time out, or some other program responds then your tunnel isn't setup correctly.

3. Change the port number that MySQL Workbench is opening locally

We're assuming that MySQL workbench is creating the tunnel on your computer.  If so, in MySQL workbench, try tunneling through another port number like 9000.

Make sure that 9000 wasn't listed as an open port from : netstat -a

  If you have ssh access to us-east-1.amazonaws.com

4. Try connecting to MySQL from us-east-1.amazonaws.com

mysql -u myuser -h 127.0.0.1 -p

And as Rolando said, you'll want to verify that you are connecting with the right credentials.  For example, if you are connecting as myuser@127.0.0.1 and you have a user myuser without a host, you probably won't be able to connect using user@127.0.0.1.


Will column size change when trying to use single unicode character in place of multiple ASCII characters?

"{br}" currently takes 4 bytes in ascii, latin1, utf8, utf8mb4 and perhaps other CHARACTER SETs.  (The "COLLATION" is irrelevant.)

ⓑ is the 3-byte utf8 encoding (in hex) E2 93 91.  See http://www.fileformat.info/info/unicode/char/24d1/index.htm

The size of the table will not change much.  If those things are all that you have, then each {br} will shrink by 1 byte.

If this is an optimization, it is probably not worth doing.

The field (either TEXT or VARCHAR) must be declared CHARACTER SET utf8 (or utf8mb4).  The client must be using utf8.  You should probably do SET NAMES utf8.

There are obscure advantages to VARCHAR over TEXT.  Note that the VARCHAR(100) can hold 100 characters; with CHARSET utf8, that is up to 300 bytes.  {br} is 4 chars and 4 bytes; ⓑ is 1 char and 3 bytes.  The (100) counts chars; disk space counts bytes.

See the following blog for too much more info:
http://mysql.rjweb.org/doc.php/charcoll


On Aug 05, 2015, I answered the post Set sql_mode “blank” after upgrading to MySQL 5.6

In my answer, I explained how Oracle created an additional my.cnf called /usr/my/cnf.

It has this (keep in mind this is MySQL 5.6 I am answering back then)

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I guess some developer just gave up putting it in the code and slapped up a configure file as some demented shortcut.

If you have such a file, please comment out the last line and restart mysqld.

MySQL 5.7's default value for sql_mode is as mentioned

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION

UPDATE 2017-04-27 17:23 EDT

I just ran mysqld --help --verbose | head -13 and got this

$ mysqld --help --verbose | head -13
2017-04-27T21:17:13.237941Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-04-27T21:17:13.238060Z 0 [Warning] Changed limits: max_connections: 214 (requested 500)
2017-04-27T21:17:13.238065Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2048)
mysqld  Ver 5.7.17-log for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Please run mysqld --help --verbose 2>/dev/null | head -13 | tail -1

Check every file mentioned

UPDATE 2017-04-29 19:03 EDT

The /usr/my.cnf trick I mentioned in Set sql_mode “blank” after upgrading to MySQL 5.6 comes from a post written by Morgan Tocker under the heading "Changes in MySQL 5.6". This bait-and-switch method for sql_mode may not apply in this case since this is MySQL 5.7.

As I mentioned in my earlier comment, I have successfully sql_mode by adding this to /etc/my.cnf

[mysqld]
sql_mode=''

You should not have to restart mysqld. You login to mysql and run

SET GLOBAL sql_mode='';
SELECT @@GLOBAL.sql_mode;

This will set incoming connections to a blank sql_mode

This will not change the sql_mode of currently established connections.

Restarting mysqld will guarantee the all incoming connections will have it blank.

If you cannot restart mysqld, you must restart your app/web servers and make them disconnect. Then,m restart your app/web servers to establish connections.

Please try this and let us the results.


How to calculate total distance by datetime


The answer is in your question:

  ERROR 1235 (42000): This version of MySQL doesn't yet support
  'multiple triggers with the same action time and event for one table

For any given table you can only have one trigger per each event (insert/update/delete) AND per each time of execution of said event (after/before).


Will table partitioning speed up my queries?

No. Partitioning does not speed things up. Your table that has 2 million records before partitioning, it will continue to have exactly the same 2 million records after partitioning. If you want to look only at a small subset of the records use an index. It does seem like your data is a multi-tenant schema with the tenant key be the ManufacturerID. In such cases the most likely design is to have the ManufacturerID be a leading key in the clustered indexes. BTW, I recommend reading Multi-Tenant Data Architecture.

Partitioning would help in scenarios involving fast data switch-in and switch-out, or in scenarios requiring distribution of data do different physical paths (filegroups). A good read is How To Decide if You Should Use Table Partitioning.

Before you say 'but what about partition elimination?' I'll say this: there's almost nothing partition elimination can do that an index cannot do better. Not to mention that partition elimination is a lottery while range scan on leading key in clustered index is pretty much a sitting duck. Besides, how many tenants will you have per partition? And how will you distribute them evenly, consider how unbalanced they seem to be? Both these problems are, again, addressed much better by a leading key in the clustered index. And, not least, consider the huge design changes partitioning brings (e.g. kiss goodbye the unique/primary key constraints or introduce unaligned indexes), see Special Guidelines for Partitioned Indexes, pay special attention to the 'Memory Limitations' section.

Partitioning is an awesome feature, but is not a performance feature. An example of what partitioning could help is to do a very fast switch-in of the staged data (which is a problem you raised), but that would require one partition per manufacturer, and it will not scale, assuming that each 'manufacturer' is a client of your business (a tenant).


Move data from one table to other in batch's

while (1=1)
begin
    delete top(10000) from oldtable
       output deleted.columnA, deleted.columnB, deleted.columnC
       into newtable (columnA, columnB, columnC)
    if @@ROW_COUNT = 0
       break;
end


Immigrate to file-per-table mode in MySQL

This is so funny. Yesterday, someone asked how to do the opposite and I answered that question : Migrate from innodb_file_per_table to off in MySQL

Here the steps for what you want

STEP #1

Set innodb_file_per_table to 0 in my.ini

[mysqld]
innodb_file_per_table = 1

STEP #2

Login in Command Prompt in Administrator mode and run

C:\> net stop mysql
C:\> net start mysql

STEP #3

Convert every InnoDB table to InnoDB again

set MYSQLUSER=root
set MYSQLPASS=rootpass
mysql -u%MYSQLUSER% -p%MYSQLPASS% -ANe"SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine='InnoDB';" > C:\ConvertInnoDBToInnoDB.sql
notepad C:\ConvertInnoDBToInnoDB.sql

Once you view the script and are satisfied, login to mysql and run this

mysql> source C:\ConvertInnoDBToInnoDB.sql

Give it a Try !!!


What to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshot

Replace 'drop database [myDb_snapshot]' with:

declare @cmd nvarchar(max);
set @cmd = 'drop database [myDb_snapshot]';
exec (@cmd);

The entire SQL statement is evaluated for correctness prior to running it.  Since the snapshot doesn't exist, it won't run.  Dynamic SQL removes that limitation.


SQL Server LaunchPad "The service did not respond in a timely fashion" + No Log in ExtensibilityLog

I has this issue, this tutorial did the trick for me.

https://www.mssqltips.com/sqlservertip/4558/sql-server-launchpad-service-cannot-be-started/

Should be noted an easy work around for me was just to change the account to NETWORK SERVICE in config manager, if you're not fussed about using a dedicated account for security reasons it will likely work for you also.


Database for building a realtime analytics system

There is a trick to building fast realtime analytics, regardless of the platform.  I've done this with Microsoft Analysis Services, but you can use similar techniques with other platforms as well.

The trick is to have a leading partition that can be populated with near-realtime data and a historical partition (or partitions) that are optimised for fast queries.  If you keep the leading partition small enough it will be quick to query as well.

To manage this, your ETL processing populates the leading partition and you build a supplementary process that periodically converts the partitions to the fast query optimised format.  The exact nature of this process will vary with your platform.

On MS Analysis Services the leading partition is done as a ROLAP partition that reads directly off the table.  Trailing partitions are converted to MOLAP with aggregates.  Other OLAP systems will work similarly.  On Oracle you can create bitmap indexes and materialised view partitions on your trailing partitions to speed up queries.  Some other systems have this type of feature as well, although I'm not aware of MySQL supporting it.

At a guess, I'd say the cheapest mainstream platform that would do this is MS Analysis Services, which is only available bundled with SQL Server and can cannot be purchased separately.  For the partitioning with 2008 R2 you will need Enterprise Edition of SQL Server, which runs to about £22,000 per CPU socket in the UK and a bit less on the other side of the pond.  Microsoft are shipping a new 'Business Intelligence' edition of SQL Server with 2012.  Once this hits RTM the B.I. edition of the product does support partitioned cubes and is substantially cheaper than Enterprise Edition.  Depending on your budget and time constraints you may be able to use that instead.

Another aspect of the problem you will have to tackle is changed data capture - efficiently identifying and pushing new or changed data rows into the ETL process.  Most DBMS vendors' CDC features only work with their own databases, so if you want a CDC solution you may have to go to a third party app or triggers on the source.

Various third parties punt CDC applications that will migrate across database platforms.  A list of CDC products can be seen on the wikipedia entry on the subject.  Note that you may still have issues with integration.  For example IBM Infosphere CDC can only trigger external processes on a per-row basis, rather than per batch, which could cause efficiency problems on large data volumes.
You can create a set of triggers on the source tables that push out the changes into a staging area.  This would require you to have sufficient access to the source database to do this, so it may not be an option on vendor-supported databases.
If the data is from a file (for example a web server log) you would have to write a client side process that monitors the tail of the files for new records.

It is quite likely you will end up having to implement a pull process that polls the data sources.  In this case you have to work out the tolerable latency and write your process so it is efficient enough at detecting changes so it can be run sufficiently quickly.  There is an old saying, sometimes found in embedded systems circles, to the effect of: 'You know they're getting serious about reliability when they start polling'


To avoid the issue with duplicate keys when using auto_increment PKs, you need to configure the system variables auto_increment_increment and auto_increment_offset.

From the MariaDB Knowledgebase: AUTO_INCREMENT:

  To make master-master or Galera safe to use AUTO_INCREMENT one should
  use the system variables auto_increment_increment and
  auto_increment_offset to generate unique values for each server.

So in other words, with two masters: On master1 set auto_increment_increment=2 and auto_increment_offset=1, and on master2 set auto_increment_increment=2 and auto_increment_offset=2.


I can't answer to question 1 as I do not have enough information about the MySQL instances offered by different hosting providers.

To answer the rest of your questions, yes, if you can leverage the features of the RDBMS you're using, you might replace PHP code with SQL scripts that can run faster than your code. Just think about the time you're saving up by not transferring your data back and forth.

On the other hand you would probably still end up using PHP code or other programs (crontab or any other kind of scheduler) to run your timed tasks as there's no built-in scheduler in MySQL.


Have you committed or rolled back the transaction? It seems like transaction isn't committed.

You just need to either add an exit at the end of the script, or run it as sqlplus username/password@server/dbname < Setup.sql (i.e. redirected input, < instead of @).

You can check if that's the issue by just typing 'exit' in the hanging session.


Accidental DBA: Unsure why I have a broken log chain?

Your command for full backup

  --Take full backup to begin new TLogChain

  backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential;

does not make full backup.

  DIFFERENTIAL

  Used only with BACKUP DATABASE, specifies that the database or file
  backup should consist only of the portions of the database or file
  changed since the last full backup.

As @dbamex mentioned full and restore commands use different file names.


Naming conflict between function parameter and result of JOIN with USING clause

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or at the start of the function definition, declaring how you want such conflicts to be resolved.
The 3 possible settings are error (the default), use_variable and use_column:
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;


openssl s_client does not indicate that SQL Server is using TLS, however the connection is secure. How is that possible?

Have the DBA run the following query to see if native SQL Server client-server connection encryption is being used.

SELECT @@SERVERNAME AS 'sql_server', a.host_name AS client_host,
    a.login_name, client_interface_name, b.encrypt_option
FROM sys.dm_exec_sessions a
JOIN sys.dm_exec_connections b ON a.session_id=b.session_id
WHERE login_name = '<login_name>'

If the encrypt_option column is false, then it's not using the native encryption.  At this point, any encryption that is wrapped around the connection would have to be at the networking layers, either Windows IPSec (or something similar), or encryption implemented by the network devices.  So you'll have to ask the DBA exactly how the connection is encrypted in that case.


To me, it seems like an application connecting using the 'mastersuser' account is not properly closing connections, so yes I'd say it's a problem. They likely disappear after reaching the wait_timeout or interactive_timeout setting.


Converting a string to an object reference

What you're looking to do is called dynamic SQL.

Using your example, I think you want to do something like this:

DECLARE @SERVER sysname,
        @DB sysname, @SCHEMA sysname,
        @TABLE sysname,
        @FIELD1 INT,
        @FIELD2 DATE,
        @FIELD3 VARCHAR(50);

SELECT TOP 1 @SERVER = Server,
             @DB = [Database],
             @SCHEMA = [Schema],
             @TABLE = [Table],
             @FIELD1 = Field1,
             @FIELD2 = Field2,
             @FIELD3 = Field3
FROM #example
--ORDER BY Server
--You need to order by a unique column or set of columns to make this determinstic

DECLARE @StringToExecute NVARCHAR(MAX) = N''

SET @StringToExecute += N'
SELECT *
FROM '
    + QUOTENAME(@SERVER)
    + N'.'
    + QUOTENAME(@DB)
    + N'.'
    + QUOTENAME(@SCHEMA)
    + N'.'
    + QUOTENAME(@TABLE)
    + N'
WHERE
    Field1 = @sp_FIELD1
    AND Field2 = @sp_FIELD2
    AND Field3 = @sp_FIELD3
';

PRINT @StringToExecute;

EXEC sys.sp_executesql @StringToExecute,
                       N'@sp_FIELD1 INT, @sp_FIELD2 DATE, @sp_FIELD3 VARCHAR(50)',
                       @sp_FIELD1 = @FIELD1, @sp_FIELD2 = @FIELD2, @sp_FIELD3 = @FIELD3;

Take note of a couple things:

I'm using QUOTENAME to escape objects that I can't parameterize -- the server, database, schema, and table names.
I'm using sp_executesql to parameterize what I can, to prevent SQL injection.

I'll link to it again, because it's a really important thing to read: The Curse and Blessings of Dynamic SQL


Feasibility of Partitioning existing tables with huge data in Oracle

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

You can redefine the table online and it will be accessible for DML. The table will be locked in an exclusive mode for a negligible period of time, where the size of the table doesn't matter.

The downside is you have to have extra space equal to the size of your table(1TB).

Update:

  Online Conversion of a Nonpartitioned Table to a Partitioned Table

  Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.

In Oracle 12c Release 2, you can convert your non-partitioned table to partitioned table using ALTER TABLE statement. But, as per your tagging, you are in 11g R2 and you don't have this option.

Documentation:
 Redefining Tables Online
Oracle 12.2: Online Conversion of a Nonpartitioned Table to a Partitioned Table


You can not "switch" a server's role in replication between subscribers and publisher.

Unless you are doing it repeatedly, I would highly recommend the method you describe, as it is the only supported method, and the most reliable.

It is possible to update system tables directly to change the name of a server, as I describe in this post. This method is unsupported, and is not easier--it simply shifts the type of work, depending on your priorities.

Note that in my post, I'm describing only the publisher, and only for transactional replication, not merge replication. You would need to identify different tables for merge replication, and do additional changes if changing the subscriber as well. However that article shows the general method. You would need to test heavily to ensure everything is working properly.


check the progress of table upload-mysql

I recommend you to import the text file in batches, that will allow you easier monitoring of the progress by committing from time to time. This can easily be achieved with tools like pt-fifo-split from Percona. The first example you can see on that page is what you should do, changing the cat for the mysqlimport.

The reasons you are seeing 0 rows is because you are importing to an InnoDB table and that means that its insert won't be seen until the process is complete. There are two way in which you can monitor the progress:

If you are using innodb_file_per_table (default on since MySQL 5.5), you should see an increasing in size table called something like alltweet.ibd inside the twitter_data directory on your datadir. You can check the location of your datadir by executing:

SHOW GLOBAL VARIABLES like 'datadir';

By default it is usually on /var/lib/mysql.

The size of that file will be roughly the size of the .txt file, maybe a bit larger (depending on the structure). That, and the progress in which it is growing can be an indicator of the time left.
Monitor the Handler_write status variable:

SHOW GLOBAL STATUS like 'Handler_write';

This will give you the number of rows inserted on your database. Assuming there is no other inserting activity, this can give you the number of inserts/second, which you can compare with the number of lines of the file.

As a reminder, both MariaDB and Percona Server have better defaults to show you the pending work remaining.

If you want to speed up the mass insertion using a .txt file, sometime ago I wrote a guide on how to speed it up. In summary:

Increase your transaction log size
Increase your buffer pool
Disable your double-write buffer
Use a recent version of MySQL
Split the file in several pieces and insert it in parallel


best approach to keys, if data from separate DBs to be presented in same UI

I'd consider composite PK instead of prefixing values in one column. It seems a bit more clear to me, and it should help avoiding potential problems if you switch to single server (or cluster) in the future (then this column can be used as partition key).


SQL Server Transaction Rollback: does log shrink or grow?

When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size?

While the transaction is open and working toward its goal, it'll reserve some log space in case it needs to rollback. Generally then, a rollback shouldn't change the size of the log as the space has already been reserved, though it is just an algorithm so it could reserve too much or too little and you won't know until it's too late. There is no way to change this behavior, currently.

There have been some announcements with SQL Server 2019 that will change the way this works if you turn on new features, FYI.

  Do all the entries generated during and related to the transaction get deleted again (in both DB and LOG) or does the undo result in the same number of entries in the log, just undoing what was done before, growing the LOG file further?

It doesn't have to be one-to-one as compensation records are created to undo the effects of the transaction, thus cancelling out the changes. Nothing is physically deleted, the opposite (compensation) operation is performed for each item in the transaction. Which compensation records and how many, depends, but the types should stay the same (the anti-type of the original).


Change all varchar date formats with a single query

Create the new column as type DATE.

Then run 3 queries, one for each conversion needed.  Sure, they could be combined into a single query, but why bother; it just makes it more complex and likely to break.

And finally drop the old column and rename the new on into place?

The queries would be something like:

ALTER TABLE add COLUMN new_date DATE;
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d-%m-%Y') WHERE substring(date,3,1) = '-';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d/%m/%Y') WHERE substring(date,3,1) = '/';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%Y/%m/%d') WHERE substring(date,5,1) = '-';

Then do a SELECT to see if any rows still have a DEFAULT value for new_date;


Should my database be on the same server as my web server?

There are a number of reasons as to why it is advisable to have your database exist on a separate server to your web application.

In my opinion chief amongst these are the security implications. For instance a simplistic network architecture would see your web server in a DMZ, or public subnet, and your database server in a private subnet. The web server can accept inbound traffic from the untrusted internet (providing you are not using a load balancer etc.) whereas your database subnet only accepts inbound traffic from the public subnet.

For more info see this similar stack overflow post here.
The second point in the top answer around scalability is also a key argument to bare in mind.


PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

Left unconfigured, PostgreSQL will outperform MySQL. In fact, left unconfigured, InnoDB in some older versions of MySQL are better than newer versions of MySQL.

Tuning PostgreSQL and MySQL and then Benchmarking for occassional usage in the presence of memcached is a lot like building two cars, drag racing them, and then parking them forever. That only leaves the open road and whatever happens to be in the middle of highway.

If you have a large working set and high turn around of data, then perhaps memcached and other second-tier caches woiuld be unnecessary. From that point forward, choice of database and benchmarking would be worthwhile and intriguing.


CREATE VIEW as join between tables with identically named columns

There was a bug in my answer to the question you referenced. While being at it, I improved some other details:

Prepend table name to each column in a result set in SQL? (Postgres specifically)

If you have many columns and / or for repeated use, I would use dynamic SQL as outlined over there.

If you'd been using a proper naming convention, you could avoid most of these cases. Never use id as column name, it's too ambiguous as you are just finding out the hard way. I would use tbl_id instead, "tbl" being the table name.
Unfortunately, some ORMs work with this anti-pattern.

How to implement a many-to-many relationship in PostgreSQL?

And you do not get any exception for a plain SELECT because Postgres does not require distinct column names in a plain SELECT (although the usefulness of this is limited beyond ad-hoc queries). Distinct names are required for columns of a table or view, though - even derived tables in subqueries. The manual:

  Just as in a table, every output column of a SELECT has a name. In a
  simple SELECT this name is just used to label the column for display,
  but when the SELECT is a sub-query of a larger query, the name is seen
  by the larger query as the column name of the virtual table produced
  by the sub-query.

Postgres has no need for distinct labels. But actual column names must be distinct to avoid ambiguities.


I normally do not endorse any one specific product, but in this case I will make an exception.
I have personally evaluated a product called Gazzang. It can encrypt data in such a way that when transported to another server, the data is unreadable unless the encryption key is copied and verified on that external server. I have tried this and copied /var/lib/mysql on another server that did not have the encryption key. The only SQL command that worked with the database encrypted is SHOW DATABASES;. Nothing else worked.
There are some hoops to jump through to get an entire /var/lib/mysql folder encrypted. Once done, you never have to worry about data encryption. Perhaps you could start like this:

Installation MySQL on a DB Server
Apply Gazzang's ezncrypt program against the empty /var/lib/mysql
Load a mysqldump into it

The beautiful part of my evaluation was that the MySQL data was fully accessible by standard DB Connections. Gazzang does not shield normal access protocols. You must take up responsibility for securing passwords:

June 6, 2012 : Limiting database security
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Feb 17, 2012 : What is the mysql.db table used for?
Jan 19, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Sep 28, 2011 : Weird MySQL Users Been Created (eg. bug115166_10073) and not by me
Sep 11, 2011 : Is this a normal set of MySQL privileges?

EPILOGUE
As for the physical data itself, this worked during the 30-day evaluation. Please evaluate it for yourself. I will leave it to you to investigate production case studies on Gazzang.
UPDATE 2015-09-29 12:26 EDT
Cloudera purchased and deprecated Gazzang.
Cloudera Data Encryption is still available.


Can you change the value according to the date?

A mapping table will do this. It could be implemented as a persisted table, a view, a common table expression (CTE), a CASE expression of maybe some other ways. Reuse, volatility of values and performance, not to mention DBMS capability, will guide the implementation choice.


Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

You may find this surprising, but MySQL 5.1 may outperform MySQL 5.5 under certain circumstances.

Percona performed a bake-off among multiple releases of MySQL

MySQL 4.1
MySQL 5.0
MySQL 5.1 (with built-in InnoDB)
MySQL 5.1 with InnoDB-plugin
MySQL 5.5
MySQL 5.6

All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The results?

MySQL 4.1 performs the best single-threaded
MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

If you want newer versions of MySQL to perform better, you must tune for it. In fact, I described in the DBA StackExchange the idea of performing a MySQL Bakeoff.

What do I mean tune for it?

In MySQL 5.5, there are new InnoDB options for utilizing more dedicated read threads, write threads, and overall I/O capacity. This can engage more CPUs in multicore servers. Left unconfigured, MySQL 5.5 would operate on the same level playing field, in most cases, as older versions of MySQL. Sometimes, it could perform worse.


My simple RAID 10 array running on old hardware with 300GB SAS disks can handle 200-300 inserts per second without any trouble; this is with SQL Server running on a VM, with a lot of other VMs running simultaneously.

With just a consumer grade SSD, you can expect 3,000 to 5,000 or more 4K I/Os per second.

What exactly is your question?


Performing Backup and Recovery in Oracle® Database 2 Day DBA is what you are looking for.


Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting

Let the DB raise an error.

Testing first isn't safe for concurrency because you'll get a collision eventually because 2 threads may pass the "NOT EXIST" and both will try to write. This applies to both "READ COMMITTED" and MVCC/Snapshot lock strategies.

You can use lock hints to force isolation, but you reduce performance.

I call this the JFDI pattern (SO link). For "update if exists" then see this here: Need Help Troubleshooting Sql Server 2005 Deadlock Scenario. These are SQL Server. MySQL has INSERT IGNORE which handles this gracefully. Not sure about the rest


Should I duplicate the data or have linking table?

It's hard to say, without knowing how you plan to use this data.

For your first option, where you make physical copies of the records when the collection is copied, this might make sense if you plan to treat each instance of an item as distinct, and you anticipate that the details of the copied items will eventually diverge from where they were copied.

The second option makes more sense to me (but again, I don't have enough usage information). This option would let an item be referenced in multiple collections, but they are not true copies. I'd build the tables like this:

items
-----
  id (PK)
  (other fields)

collections
-----------
  id (PK)
  (other fields)

items_in_collections
--------------------
  id (PK)
  item_id (FK to items.id)
  collection_id (FK to collections.id)
  (other fields)

With this structure, you can easily copy an item to another collection by adding a new record to items_in_collections with the collection_id of the new collection. Accessing the data is not that complex. Here's how to get all items for a specified collection

select items.*
from items
inner join items_in_collections on items.id = items_in_collections.item_id
inner join collections on collections.id = items_in_collections.collection_id
where collections.id = $COLLECTION_ID --user specified collection ID


However, when I run a count(*) on the table, I've been getting the same number just shy of 38m for the last 15 hours

The restore will be running in a single transaction - or, at least, that particular table will be being loaded by a COPY command that runs in one transaction. If that's the case you won't see the new rows until the transaction commits.

You haven't shown the contents you mention from pg_stat_activity so it's hard to do more than guess here.

  Also, is there any way to determine the number of rows in a .backup file? Perhaps, then I can know if it has captured all of the data.

Use pg_restore without a target database. It can extract a pg_dump custom-format file, dumping the whole file or individual tables as SQL, as CSV, etc. You can examine that dump contents that way.

  I don't think the issue is a hardware one, as previously a restore of this table took just 3 hours (it had around 25m rows at that stage).

This could be a locking issue - if something else is still connected to the database it might be preventing a table truncation, preventing inserts, etc.

Check pg_locks; see Lock monitoring.


Modeling a product database and dealing with inheritance

You might like to look here - they give a lot of sample data models.

There are two in particular which might be of use to you. Here is a BOM (Bill Of Materials) which looks as if it might be a good fit for your business (manufacturing machine guns). Take a look at the "Manufacturing" section (no. 13).

You appear to have a table for all your different components Barrel, Buttstock, Sight and Scope amongst others. I'm not so sure that they're all necessary? But, as Einstein said, things should be made as simple as required, but no simpler.

What's your RDBMS? It's always good on this site when asking a question to provide your database server.

Take the tour of the site, visit the help centre and check out the "Help us to help you" blog. p.s. welcome to the dba.stackexchange.


...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.

This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types.

  It definitely appears that the LOB data must be loaded into memory on the server in order to be deleted.

As Paul Randal is reported to have said (update 2, in the question you linked to), SQL Server must traverse the LOB tree in order to locate the pages that must be removed. To do this, SQL Server needs to bring these LOB tree pages into memory. There really is no way around this; the information is just not available in any other way.

  It seems like this deallocation should happen in a clean-up task, and not up-front.

The way this works is not very well documented.

In my tests, SQL Server deletes the LOB data immediately if there is no special reason to keep the row around. This seems to be an optimization: after all, if we are already touching the LOB data, we might as well delete it while we are there. Deferring it to ghost cleanup (GC) would only add overhead, since GC would also need to traverse the chain. In addition, logging is reduced, since SQL Server only has to record the deallocation, not the complete LOB content.

The LOB delete operation is deferred to GC if (for example) there is a trigger on the table, or if a row-versioning isolation level is enabled. Both these features use row versions, which must be maintained on the LOB rows. In these cases, the LOB delete is fully logged. When GC runs later on, it performs the deallocation of the LOB pages. GC can be temporarily disabled with (documented) global trace flag 661, to see these ghosted LOB records if you want to test it yourself.

Given all that, it is hard to see how (fully) logging the delete and deferring a repeat of much of the same work already done to the GC would help you. It is likely faster to log less and remove the LOB data during the delete.

There's not enough in the question to speculate much about the precise cause of your poor delete performance, beyond the possibility of an overwhelmed/underspecified storage system, but I can say that in general it is best to:

Avoid accessing LOB data under read uncommitted isolation
Avoid having triggers on tables with LOB data when performing deletes
Be aware of the side-effects of row versioning
Ensure the storage subsystem is adequate for logging and general I/O
Run the latest build of SQL Server (currently 11.00.6523 for SQL Server 2012)

For more information about point 1, see Performance bug: NOLOCK scans involving off-row LOB data by Paul Randal. For more on points 2 and 3, see Deletes that Split Pages and Forwarded Ghosts (by me).

It could equally be that the simplified example misses a crucial detail, or the table variables produce an inefficient execution plan, or your table has billions of ghost LOB records already. A detailed analysis would probably require access to the system itself.


How to verify SSL always being used on Postgresql 9.6

Non-SSL connections can be disabled through pg_hba.conf.

For instance, it may start like this:

# allow local connections through Unix domain sockets
local  all  all  peer

# allow non-encrypted local TCP connections with passwords
host       all  all  127.0.0.1/32   md5
host       all  all  ::1/128        md5

# reject any other non-encrypted TCP connection
hostnossl  all  all  0.0.0.0/0     reject
hostnossl  all  all  ::/0          reject

# other rules...

The rules are tested in order and until the first match, so any rule after these will have no effect when one of these matches.

At runtime, to check which sessions are encrypted, there's the pg_stat_ssl system view  (since PostgreSQL 9.5). Its pid column is a reference to  pg_stat_activity that holds the other bits of information that might be relevant to identifying the connection such  as usename, datname, client_addr..., so you might use this query, for instance:

SELECT datname,usename, ssl, client_addr
  FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid;


Load csv into MySQL database programmatically

What you want is nearly impossible, no one can know if you want to import '1' as a characters string, n decimal integer, a base-2 integer, an hex integer, a float, a blob, a "boolean", ...

You can try heuristic programs, or import them all as strings and run an analysis program like SELECT... ANALYZE TABLE();. It would be easier if the csv had the datatypes as an information.

Regarding programs that create the table (with chars) based on the list of columns, that is easier to find (GUI) and program.

Here it is one in bash as an example.


Discover available HD storage from inside SQL Server

I use the below to get SQL Server OS drive partition\volume information SQL Server per instance in both GB and MB.

INFORMATION: This is using a SQL system DM (sys.dm_os_volume_stats) and sys.master_files table with the CROSS APPLY to get the volume information from which that instance can see.  It then does mathematic calcutations (e.g. dividing by 1024 a time or two or three or perhaps multiplying by 100, etc. as you see below in the CAST statements) to these and lists in both MB and GB as and uses the NUMERIC function.

This will get both TOTAL and FREE space space per disk partition in both GB and MB as well as the percentage of FREE SPACE per disk partition using straight TSQL.

SELECT DISTINCT UPPER(vs.volume_mount_point) AS Drive_Letter
    ,vs.logical_volume_name AS Drive_Label
    ,CAST((vs.total_bytes / 1024.0 / 1024) AS NUMERIC(18, 1)) AS Total_Size_MB
    ,CAST((vs.available_bytes / 1024.0 / 1024) AS NUMERIC(18, 1)) AS Free_Space_MB
    ,CAST((vs.total_bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18, 1)) AS Total_Size_GB
    ,CAST((vs.available_bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18, 1)) AS Free_Space_GB
    ,CAST((vs.available_bytes / 1024.0 / 1024 / 1024) / (vs.Total_Bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18, 3)) * 100 AS Free_Space_Percentage
FROM sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
ORDER BY UPPER(vs.volume_mount_point)

SQL Server get drive space information, get volume space information, get disk space information, get disk free space, disk space allocation, report disk space, report storage space


Short answer: No, you need more indexes.

If you're going to create a significant number of queries involving [brand] and [colour], you should index both columns:

CREATE INDEX products_colour_idx ON dbo.products (colour);
CREATE INDEX products_brand_idx ON dbo.products (brand);

If you mostly query using both columns, and there are not queries involving colour that do not also involve a brand, a multi-column index would be better, because by using just one index, the database can retrieve all the relevant rows. You would create it this way:

CREATE INDEX products_brand_colours_idx ON dbo.products (brand, colour);

The order of the columns [(brand, colour) vs. (colour, brand)] should be normally chosen in such a way that the most selective one comes first (basically: the one with more different values, first).

If you happen to have a significant number of queries with brand, colour and both brand and colour, you should have at least the two first indexes; if you need the fastest possible speed, have one index for (brand, colour) and another one for (colour). You don't need an index on purpose for (brand), the (brand, colour) one is good for looking for brands.

[product_code] will be indexed by the database automatically to enforce the UNIQUE constraint on it. See Create Unique Constraints and Unique Constraints and Unique Indexes. You don't need to create an index explicitly even if query_2 is frequent.


Generating 50k rows of unix time entries auto-incremented 5 mintes each

Plan A:  (Drawback: you might still have gaps.)

Find all epoch values from the 4 tables using UNION DISTINCT.
LEFT JOIN from that table to the other 4 tables.

Plan B:  (Drawback: I don't say how to do step 1.)

Build a TEMPORARY table with every 5-min time for the next N months.
LEFT JOIN ...

Plan C:  (Drawback: Step 1.)

Upgrade to MariaDB.
Use a "sequence" table to 'virtually' generate enough integers.
Use some simple arithmetic to convert the integers to epochs.
LEFT JOIN ...


Package state not re-intialized on raise_application_error

The response from Oracle is that this is not really a bug because the application is catching the 4068 and handling it, thereby not allowing the normal resolution of the error to occur. Their explanation doesn't seem adequate to me because if the package throws an ORA-01476-Divide By Zero (for example) doing a raise_application_error does not effect whether the ORA-01476 is included in the stack or not.


Can't change authentication mode from Windows to both SQL Server and Windows

The EXECUTE permission was denied on the object
  'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'.
  (Microsoft SQL Server, Error; 229)

You can fix the problem by granting the user direct access to the procedure.

USE master
GO

GRANT EXECUTE ON [sys].[xp_instance_regread] TO [DOMAIN\USER];

--To veriy that the user has been granted the privilege
EXECUTE AS USER = 'DOMAIN\USER';Select * from fn_my_permissions('xp_instance_regread','Object')

  Cannot alter the login 'sa', because it does not exist or you do not
  have permission.

Error message saying that, You do not have administrator's permissions

Log into SQL Server Management Studio as an Administrator, because only admin user has permission to change passwords for users.

How to change sa user password see here: How to change a password for the sa user in MS SQL? and this link


Table design for departments with duplicate department numbers

The PK of your department table is not dept_id.  The column dept_id is an attribute of a manager table that you have not defined, since it is functionally dependent on the manager_id.

The PK of your department table is dept_name.

Primary keys don't have to be integers.

Note: If your HR department creates departments with the same name, that is going to land you back in hot water.  You might need to create a composite primary key of dept_id and dept_name.  This could still lead to trouble if your HR department can't be trained to keep department names unique within managers.  Excel is not a good data source for being able to enforce constraints.


TDS: unexpected token

TDS (transaction Data Stream) is the application layer protocol of Sybase & MS SQL for communication between OpenClient & OpenServer
The sample messages in the post point to TDS exchange between ASE & DB-LIB client not being in sync according to the TDS protocol
The network exchange machine state in the affected TDS connection at the ASE OpenServer end is not expecting a request from the DB-LIB client for any resultset
The above situation may happen if an existing DB-LIB client is picking up a new TDS connection instead of the original one to continue with getting the resultset from a query submitted earlier
A typical scenario would involve a pool of connections from a Sybase client to the ASE, should an existing client incorrectly switches to another connection in the pool after submitting a query, the OpenServer of the new, incorrect connection would not understand the client's TDS token requesting resultset
Another possible root-cause could be network issue because 56 is not a valid TDS token (0x38 is not a valid token nor 0x56)
BTW, DB-LIB is an arcane, unsupported Sybase client (newer, supported Sybase client is CT-LIB)


SQL Server 2014 user login keeps being dropped

Possibly setup, sql audit, a server side trace/extended event and filter for sp_droplogin or a server-scoped trigger like below.

CREATE TABLE ##AuditDroppedLogin(UserName sysname, DateDropped DATETIME);
GO
CREATE TRIGGER WhoDidIt
ON ALL SERVER
FOR DROP_LOGIN
AS
BEGIN
INSERT INTO ##AuditDroppedLogin
SELECT USER_NAME(), GETDATE()
END

Then simply query ##AuditDroppedLogin for any events. Very basic example.

Below is also a server side trace script to track sp_droplogin

    /****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 10/01/2017  13:15:30         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text C:\Traces\WhoDroppedLogin, with your existing appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

declare @stoptime DATETIME
declare @filecount int

SET @stoptime =DATEADD(hh,48,GETDATE()) -- runs for 48 hours

SET @filecount = 5

exec @rc = sp_trace_create @TraceID output, 2, N'C:\Traces\WhoDroppedLogin', @maxfilesize, @stoptime, @filecount
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%sp_droplogin%'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

To stop before 48 hours run, replace 2 with your TraceID.
EXEC sp_trace_setstatus 2, 0


Should I create an index for non key columns?

Please be more specific: "It will contain a lot of rows". How many? Millions, thousands or billions. "Is it a good idea?" It depends.

If your queries are like the ones you mention, you should create two b-tree indexes, one for each field. Instructions are here: http://www.postgresql.org/docs/9.1/static/sql-createindex.html

You should only create one index for both fields, ONLY IF all your queries are like:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id AND "OWNER_B_ID"=owner_b_id;

This index will also work for queries like:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;

but NOT for

SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;


Linking two fields from different tables (are relationships required?)

You need a table to sit between Employees and Products.  It will contain the emp_Id and pro_id, though you may discover other columns later. As an employee sells a product insert the corresponding combination of IDs into this table. Remove emp_products and pro_employees from your model.


Restore with Standby - Tail-log backup is greyed out

A tail-log backup is only relevant for databases in full or bulk-logged recovery, and you can only perform a tail-log backup if you have access to an online copy of the same database.

If you're just restoring a copy of a backup of some other database in a new location (regardless if you're replacing an older copy), and all you have is a static .bak file, you don't need to check this box.

I think they just sent you instructions for how they restore this backup internally, maybe, but this portion is irrelevant to you if you don't have access to the original source database in order to take a tail-log backup.

(Also, I would ask your vendor for RESTORE DATABASE commands, instead of a guide to pointing and clicking.)


Mongo ReplicaSet - arbiter in connection string

Connecting to a replica set

Most drivers will show the following behavior, as far as I know

If only a single node is given, the driver will simply connect to this node.
If the replicaSet option is given in the connection string, the driver will start to explore said replica set to identify all non-hidden members, regardless of the number of nodes given in the connection string.
The same behavior is triggered when multiple nodes are given in the connection string. Since this is a bit more driver dependent than the above (for example, you can give multiple mongos as well), I personally would not rely too much on it.

Should I include an arbiter in the connection string?

The reason why you should always give at least two members of a replica set in a connection string is that this enables the driver to connect to a replica set, even when one node is down. This can be any node (except a hidden one, I suppose, but I have never tried that), since each node is fully aware of the replica set.

In a three node replica set with two data bearing members, it does not make much sense to include the arbiter into the connection string. If you give the two data bearing nodes, and both are unreachable, your replica set is not reachable any more.

In a five node replica set, however, you may include an arbiter in the connection string, since you may want to add 3 nodes altogether to make sure that either you can connect to one of them or can safely assume that the replica set is unavailable.

TL:DR

It does not hurt to include an arbiter in the connection string, though it makes a bit less sense in a minimal replica set consisting of two data bearing nodes and an arbiter.

To be on the safe side, you will almost always include the majority of voting members in your connection string. Which type of member the individual member is does not play a significant role for the purpose of replica set discovery.

I would suggest to always add the replicaSet option to the connection string, and be it just for being clear on the intended purpose.