Error 976 Severity: 14, State: 1. Always on Clustering

You are attempting to connect to databases on secondary replicas when the availabilty group replica is not configured to allow connections on anything other than the primary replica.

The following script will tell you the state of the secondary replicas, and whether they will accept read connections:

SELECT ag.name,
       replica_server_name,
       secondary_role_allow_connections_desc
FROM sys.availability_replicas ar
    JOIN sys.availability_groups ag
        ON ag.group_id = ar.group_id;

The result of each replica with be:

NO - no connections allowed to the database (default behavior)
READ_ONLY - connections allowed when the ReadIntent flag is passed in a connection string
ALL - all connections to the databases are permitted.

To change the setting for a replica you can either use the GUI, or a script:

ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON 'ReplicaName'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = 'TCP://ReplicaName:Port'))

For more details see the Microsoft documentation.


Get the highest parent id recursively in MySQL

WITH RECURSIVE
cte as ( SELECT id, id nextid, parentid
         FROM t1
       UNION ALL
         SELECT cte.id, t1.id, t1.parentid
         FROM t1
         JOIN cte ON cte.parentid = t1.id )
SELECT Id, nextid RootId
FROM cte
WHERE parentid IS NULL

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=209df940143d3e984d418e49929bd847


The main issue you are seeing is based on the nature of the encryption.  The Delphi application appears to not be using any native MySQL encryption/decryption mechanisms for the data.  This will result in certain disadvantages when querying for data.  Although the decrypted data is visible to the application while in use, native to MySQL, it has no what of indexing any encrypted data fields in any optimized way as the values would be unknown.  This would result in long query times as most likely all queries would be scanning the whole table to deliver any results.

If you can, use the Delphi application, or similarly developed application to export the data unencrypted into a file, easily parsed by MySQL (csv, SQL statements, etc.) that can be imported into your MySQL 5.7 database instance.

This will allow you to create indexes on the unencrypted data to run your queries as necessary.

If you need to reencrypt the data, you can use the native functions of MySQL:

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html

However, you will still be unable to index those columns in any workable way to query on those columns.  I would recommend if possible that any queries be done on any columns that can remain unencrypted and therefore be indexed accordingly to optimize the execution plans.


How to change default engine in WAMP to InnoDB

Find mysql configuration file my.ini, find default-storage-engine setting line in [mysqld] section, insert/edit it to default-storage-engine=INNODB, save changes, restart server service.


Index to filter a view before joining the underlying tables

Could I through indexing alone influence SQL server to run this much faster.

Possibly.

There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the first match is likely to occur earlier.

The query optimizer does not currently have the ability to explore separating the OR predicates into a UNION ALL, pushing each predicate below a local TOP (1) as a human query writer might naturally do.

It also has to make assumptions about the distribution of data to assess the chances of finding the first match, as mentioned above. In addition, there are all sorts of ways that the optimizer's calculations could be 'off', leading to it selecting the 'wrong' index strategy. Not being able to push index hints through a view also limits the opportunities for using a query hint via a plan guide, for example.

So, the best answer I can give from the information given is "possibly".

If you want a more concrete answer to your question, please supply complete DDL for the tables and indexes, and scripts for the existing statistics. Also, let us know how many logical processors, and how much memory, the instance is configured to use. I realize that is a lot of detail to provide, but a specific answer likely does depend on one or more of these details.


Setup mail notifications on SQL Server (severe) errors

If the SQL Agent Service is not running I'm not getting notified about errors.Therefore I'm wondering, there must be a better way to do what I want?

Few options

1.Monitor from different server

IF EXISTS(
SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')

2.Use a Third Party Monitoring service like SQLSentry

Also i don't see a reason why you need to Parse SQL error log .SQLServer alerts can be used to catch most of the errors/Performance conditions

below article has more details on how to set up alerting for different scenarios

https://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

And this article also shows  ,few more ways to monitor  using audits


Creating an SSIS package that uses a stored procedure

If you are calling the temp table from a different task try this

https://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat


Assigning a Variable Generic NEW Value by Pressing a Postgres Trigger

You have to use dynamic SQL like this:

EXECUTE format('SELECT $1.%I', some_key) INTO value USING NEW;


Postgresql cannot peer authenticate using usermap (provided user name (^dbuser^) and authenticated user name (^sysuser^) do not match)

In the end I had to comment the line:

local   all             all                                     peer

Or to put it below the following line:

local    mail            all                             ident map=mailmap


How to resolve COM server application local activation permission for machine account

As @spaghettidba said, the error message is pretty clear. APPID EE4171E6-C37E-4D04-AF4C-8617BC7D4914 is Microsoft SQL Server Integration Services 12.0. It looks like you already knew what you had to do:

Either grant access to the machine account in Component Services \ DCOM Config \Microsoft SQL Server Integration Services 12.0 (In properties \ Security \ Launch and Activation Permissions)
or
Change under which account the service request is being made.


Getting distinct list of records based on the MAX of a column

This answer was originally posted by the OP as an edit to the question; it is now reposted as a Community Wiki answer.

I was able to find an answer with some more searching in the archive.

https://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records

So the SQL I created from that post which works in my case is as follows...

SELECT tblTestActionStatus.ID_TestAction, StatusDateTime, ID_Status
FROM tblTestActionStatus
INNER JOIN
    (
        Select MAX(StatusDateTime) as LatestDate, ID_TestAction
        FROM tblTestActionStatus
        Group By ID_TestAction
    ) SubMax
on tblTestActionStatus.StatusDateTime = SubMax.LatestDate
and tblTestActionStatus.ID_TestAction = SubMax.ID_TestAction


How to insert json into a table and have certain columns remain json

The actual column name "p_attribute" and the key "p_attributes" in the json format were different...


In addition to fixing single quotes as instructed by Laurenz, you might have to use the (default!) replication set '{ddl_sql}' for the command replicate_ddl_command(). The error message

cannot drop active portal "pglogical"

indicates an open portal with a prepared statement that would conflict with the DDL command.
CREATE OR REPLACE FUNCTION public.intercept_ddl()
  RETURNS event_trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   PERFORM pglogical.replicate_ddl_command(current_query(), '{ddl_sql}');  -- !!
END
$func$;

You did not disclose the actual event trigger, but could look like this
CREATE EVENT TRIGGER foo
ON ddl_command_start
WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE', 'DROP TABLE')
EXECUTE FUNCTION public.intercept_ddl();

Filter command tags in the trigger itself, obviates the need to do so in the function. That's more efficient as the function is only called for applicable tags to begin with.
Quoting the manual of pglogical:

There are three preexisting replication sets named “default”,
“default_insert_only” and “ddl_sql”. [...] The “ddl_sql” replication
set is defined to replicate schema changes specified by
pglogical.replicate_ddl_command

And:

pglogical.replicate_ddl_command(command text, replication_sets text[]) Execute locally and then send the specified command to the
replication queue for execution on subscribers which are subscribed to
one of the specified replication_sets.Parameters:

command – DDL query to execute
replication_sets – array of replication sets which this command should be associated with, default “{ddl_sql}”


SQL Server add zero if there is length less than specific number

The following code does what you need, the bit you need is the REPLICATE section:

declare @var nvarchar(10);

set @var = '321'

select @var + REPLICATE('0',9-LEN(@var))

RESULT:

321000000


MySQL thread states when changing an in-memory temporary table to disk-based

At the point where (the lower of) tmp_table_size or max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of Copying to tmp table on disk instead of Copying to tmp table?

This will never happen.

The "same" query can't ever create a new thread id.  Nothing happening in a client thread will create a new thread id.  If it looks like you are seeing that, then it has to be something other than that... something else is connecting and running that query again.

Perhaps the MySQL event scheduler?  Perhaps a timeout at some layer in the application framework?  It seems all but impossible that this is anything other than something (or someone) sending the query again on a new connection.

You should be able to confirm this with the general query log, but it isn't spontaneously MySQL doing this.

Also, going for the quote into its full context, from High Performance MySQL:

  You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...

The point is that you can't tell from SHOW STATUS counters whether temporary tables were switched from memory to disk mid-query because of growth in the result-set or initially created as disk-based temporary tables because of some limitation of in-memory tables (like the lack of BLOB support) from looking at the counters.  You can tell when a table is being converted from one to the other because it should say "converting HEAP to MyISAM" and obviously, as you have seen, the processlist does indicate which type of table an implicit temporary table currently is.

But it's inconceivable that you have anything other than another query being sent to the MySQL server from somewhere, if you're seeing what looks like a query forking into or spilling over into another client thread.


mysql - calculate traffic for tmp tables

You can go 2 ways: logical and physical calculation.

PERFORMANCE_SCHEMA database has detailed io information about tables and file io. You may need to enable the right monitor metrics to get what you want.

For a physical path, there are multiple tools available. Separate your tmp dir on a different device (a temporary disk, a separate petition or if you cannot do anything else, a loop back device) and you can get detailed stats with some of the many standard Linux utilities or any of the higher level monitoring tools like Prometheus.


MySQL 5.6 OS X 10.8: how tell if my.cnf read?

Whenever mysqld is launched, it has four default locations it attempts to locate and read my.cnf
If you run the following:
mysqld --help --verbose | head -20 > junk.txt
cat junk.txt

You will see this:
mysqld  Ver 5.6.10 for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2013, 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
The following groups are read: mysqld server mysqld-5.6
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.

Note the line that says:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

These are the four files read (if they exist).
The above displays come from a Linux Box. So, please run
mysqld --help --verbose | head -20 > junk.txt
cat junk.txt

and see what locations are echoed.
UPDATE 2013-03-17 22:35 EDT
The reason you cannot see the --defaults-file option is not visible is simple: Since one or more of the default my.cnf files are present, mysqld_safe does not show it on the command line.
Please note the following:

mysqld uses the four defaults
You cannot set --defaults-file for mysqld. That's the job of mysqld_safe
mysqld_safe needs it explicitly specified in order for it to be visible in the OS

Running SHOW GLOBAL VARIABLES; will not show any defaults file
mysql> show global variables like '%def%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| default_week_format    | 0      |
| table_definition_cache | 400    |
+------------------------+--------+
3 rows in set (0.00 sec)

mysql>


Instead of matching the portion of the string that I wanted to fuzzy search on I instead, concatinated all of the terms into a string, and then ran the difference function (from the fuzzystrmatch module) against it. So it looked something like this.

FOR term IN terms LOOP
    term_string := term_string||' '||term;
END LOOP;

score := score + difference(term_string,string_to_compare_against);

I did need to set a threshold that needed to be passed by the score to keep from getting to many false positives.


Why would one need multiple instances of same Oracle database?

For availability (one instance goes down, another instance can continue to work), handling load between nodes (adding more nodes on as needed basis) and possibility to dynamically allocate resources based on needs. That is achieved by using Oracle RAC (Real Application Cluster) where instances are running on separate machines.
In Oracle for version up to and including 11.2, one database can be used by one instance (basic setup) or more instances (Oracle RAC).
 It's expected to see paradigm shift for instance vs database with soon-to-be-released Oracle 12c (having more databases inside one instance).


Performance Impact of Constant Login / Logout SQL On High Latency Connection

If Audit:Login EventSubclass=2, then this is expected, and shouldn't be an issue.  The client is just reusing a connection from a connection pool and "resetting" the connection to clear any impersonations, temp tables, session settings, etc.


Has the postgres sorting order changed between versions 11.5 and 13

The sorting order for strings in PostgreSQL depends on the collation used.
This collation is taken either from the operating system C library or from the ICU library; if you didn't specify anything, it is the C library.
So if your sorting order changed, that means that your C library changed. There is no direct connection to PostgreSQL.
Note that when you upgrade the operating system C library, you should reindex all indexes on string columns to avoid data corruption.


How to index a query with `WHERE field IS NULL`?

In this special case the column actually indexed is irrelevant for the query at hand. You can pick any column. I would pick something else than uploaded_at, which is useless. Some column that may be useful for other queries and is not bigger than 8 bytes, ideally.

CREATE INDEX foo ON table bar (some_col) WHERE uploaded_at IS NULL;

If you have no use case for any other column, it's still best to stick with the useless uploaded_at, so not to introduce additional maintenance cost for the index and restrictions for H.O.T. updates. More:

Is a composite index also good for queries on the first field?

Or use a constant as index expression if you have no use for any other index column. Like:

CREATE INDEX baz ON table bar ((TRUE)) WHERE uploaded_at IS NULL;

Parentheses required. This also keeps the index at minimum size. But while the index column is never bigger than 8 bytes (which is the case for timestamp) it's still at minimum size anyway.
Related:

Get count estimates from pg_class.reltuples for given conditions


When you define the foreign keys you can include ON DELETE CASCADE to enforce the delete rule.

As for problems in the design. One immediate issue I see is that you've defined the Identity columns as "Id". This design pattern almost always leads to trouble. What you want to use instead is an ID column name that includes the table name. For example, Id in the User table should be named UserID.


Installing SQL Server 2008 R2 Service Pack 1

It sounds like you're running the wrong file.

Download one of the files from http://www.microsoft.com/download/en/details.aspx?id=26727 and then run that. It shouldn't prompt you for "new installation or add new features", it should prompt you for which instance you want to apply the service pack to.


Building test site, update dates (back date records)

I think I got it.....

SET hdIssues.IssueDate =  COALESCE(DATEADD(day,@NumberOfDays,hdIssues.IssueDate),null),
hdIssues.StartDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.StartDate ),null),
hdIssues.ResolvedDate =  COALESCE(DATEADD(day,@NumberOfDays,hdIssues.ResolvedDate ),null),
hdIssues.LastUpdated =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.LastUpdated ),null),
hdIssues.DueDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.DueDate ),null),
hdIssues.AcknowledgeDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.AcknowledgeDate ),null)

Coalesce seemed to work for me, I had no idea what coalesce was before but saw it in this example and read up on it here

Anyone know of any drawbacks to this approach?


You should index as per usage. If you drop all indexes for the benefit of a small % of your workload you will hit pain elsewhere. Run pt-duplicate-key-checker 1st and remove the redundant keys. Then review your queries with pt-query-digest and weed out the poorly performing queries by reindexing them or rewriting them. You could paste the output of EXPLAIN with your table structure and status for your problematic query for further help tuning on that.


SequelizeDatabaseError While Upserting, Wrong Table Being Referenced

So from further searching I was able to solve this issue by adding a...

freezeTableName: true

value to the model file for the table.


I don't see any indexes in the Visual Studio object explorer.  But if you ran that code, the index is certainly there.

Check with TSQL:

select *
from sys.indexes
where object_id = object_id('Instructor')

Or connect with SSMS to (LocalDb)\MSSQLLocalDB and check with the SSMS object explorer.


You will have to include DG in your grouping...

Select Consignment_id
       ,Orders_boxsize
       ,Orders_boxweight
       ,Orders_boxlength
       ,Orders_boxwidth
       ,Orders_boxdepth
       ,Order_dispatchment_id
       ,SUM(Component_weight) as Component_weight
       ,SUM(_ic) as _ic
       ,DG
from
(
SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
,(
               SELECT CASE
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id
) a
Group by
Consignment_id
,Orders_boxsize
,Orders_boxweight
,Orders_boxlength
,Orders_boxwidth
,Orders_boxdepth
,Order_dispatchment_id
,DG


Currently all connections getting "Timeout expired"

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects.  By default, SqlConnection has a max pool size of 100.

The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable to grab another one, because there are no inactive connections in the pool to use.

The application should be making a call to SqlConnection.Close() or SqlConnection.Dispose() in order to release the connection and mark it as "inactive".

I wrote an extensive blog post (Connection Pooling for the SQL Server DBA) about connection pooling, and it should shed some light on why you are seeing what you are seeing, and also the programmatic fix to the issue.


Database growth causing issues sending backup over network

There is no need to ship over the entire database. In fact SQL Server has a built-in solution for exactly this scenario, namely log-shipping. With log-shipping you only need to send the full backup once and subsequently you send over the log backups. Log backups will only contain changes from the last log backup and thus be usually quite small. The stand-by database will be read-only, perfect for reporting and queries. The article linked contains a link at the end to a step-by-step guide how to set it up.


The only way to check if backup is reliable is to restore it.
DBCC Checkdb is not needed after successful restore if you just want to be sure that backup is reliable.


The "noop" would have fired any related triggers on the table, and it would have advanced the system change number.


Postgresql: When using SSH tunnel, I receive the fe_sendauth: no password supplied error

Your Windows 7 PC supports IPv6 natively.

I'd say your pg_hba.conf is set to require md5 on connections from ::1 but not on 127.0.0.1. This works on your XP machines because they won't connect over IPv6. On Win 7, they'll happily use IPv6 (and PuTTY will happily tunnel IPv6), so they probably appear to have the origin address of ::1.

To confirm if this is the case, look at the PostgreSQL log file on the server.

(Personally I find it much less of a pain to just configure SSL on my PostgreSQL servers anyway, mind).


The answer to your question is that

your MySQL system has 1365 rows
the EXPLAIN for your MariaDB shows 224377 rows
you're now telling me that your running system has ~500,000
535,000 divided by 1365 = ~400
400 * 17 = 6800
6,800 / 60 = 113 minutes ~ 2 hours

First thing to try.

Put separate INDEXes on b and c and see how that works. If not, try below. Are they reasonably distinct - i.e. is their cardinality high? If they are UNIQUE, that would be great.

Second thing to try.

I suggest that you redesign your table A - you should split it up as I suggested earlier. Maybe you should ask a new question in this regard (pointing back to this thread)? Your query is at least O(n) - or maybe worse? Post the output of SHOW CREATE TABLE A\G and some sample data and we'll try and help you with your new query. Also, give the cardinalities of the important fields.

My suggestion is to keep a, d and e together. Create two new tables B and C and have a PRIMARY KEY in those point back to your new PRIMARY KEY to your new A. Use a new AUTO_INCREMENT as the PK for all three. See how that goes.


One Materialized View in Two Refresh Groups

I believe that such query will return the next run date you require:

select least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'),
  next_day(sysdate, 'Sunday')) as answer from dual;

It calculates dates of next Sunday and 1st of next month and then returns the one which will be sooner.

Just 'Sunday' in next_day is NLS dependent.

That should give your required interval in call to DBMS_REFRESH.MAKE procedure:

interval=>'least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'), next_day(sysdate, 'Sunday'))'


Resource Governor not limiting CPU / RAM

If you want to limit the CPU of a resource pool then you need to use the CAP_CPU_PERCENT setting:

  This settings is a hard cap limit on the CPU bandwidth for all
  requests in the resource pool. Workloads associated with the pool can
  use CPU capacity above the value of MAX_CPU_PERCENT if it is
  available, but not above the value of CAP_CPU_PERCENT.

The MAX_CPU_PERCENT setting is designed to limit the average CPU usage. If there isn't contention on the server then it can be ignored:

  If there is available CPU capacity, the workload uses it up to 100
  percent. The maximum value only applies when there is contention for
  CPU resources.

You can read more about different resource pool settings in the documentation.


Writing SQL Query Results to Event Log --- PowerShell + SQL Server 2012

Add a FOR XML (or JSON, if that's your thing) clause to the query. This will output all columns and rows in a single string, which can be written to the event log. Be aware of the size so you don't lose information.

If changing the query isn't possible, Powershell has a full set of XML features. Loop through the results and write one event. You can detect the risk of xml truncation in the event store and write further events to avoid loss.


How to handle TimeZone properly in SQL Server?

There's no quick fix for this, unfortunately. Internationalization of an application should be part of the very first design discussions, as it really goes to the core of a lot of different areas, including date/time comparisons and output formatting.

Anyways, to get on the track of Doing It Right, it's essential to store the time zone information with the time. In other words, realizing that the date/time 20130407 14:50 is meaningless without either (a) including the time zone's then-current UTC offset(note 1), or (b) ensuring that all logic inserting these values first converts to a certain fixed offset (most likely 0). Without either of those things, two given time values are incomparable, and the data is corrupt. (The latter method is playing with fire(note 2), by the way; don't do that.)

In SQL Server 2008+, you can store the offset with the time directly by using the datetimeoffset data type. (For completeness, in 2005 and before, I would add a second column to store the then-current UTC offset value (in minutes).)

This makes it easy for a desktop-type application, as these platforms normally have mechanisms to automatically convert a date/time + time zone to a local time and then format for output, all based on the user's regional settings.

For the web, which is an inherently disconnected architecture, even with the back-end data set up properly, it's more complex because you need information about the client to be able to do the conversion and/or formatting. This is usually done via user preference settings (the application converts/formats things before output), or simply showing things with the same fixed format and time zone offset for everyone (which is what the Stack Exchange platform currently does).

You can see how if the back-end data is not set up properly, very quickly it's going to get complicated and hacky. I would not recommend going down any of those paths because you'll just end up with more problems down the line.

Note 1:

A timezone's UTC offset is not fixed: consider daylight savings where a zone's UTC offset varies by plus or minus an hour. Also zones' daylight savings dates vary on a regular basis. So using datetimeoffset (or a composite of local time and UTC offset at that time) results in maximum information recovery.

Note 2:

It's about controlling data inputs. While there's no fool-proof way to validate incoming values, it's better to enforce a simple standard that doesn't involve computations. If a public API expects a data type that includes an offset, that requirement will be clear to the caller.

If that wasn't the case, the caller has to rely on documentation (if they read it), or the computation is done incorrectly, etc. There are fewer failure/bug modes when requiring an offset, in particular for a distributed system (or even just web/database on separate servers as the case here).

Storing the offset anyway kills two birds with one stone; and even if that isn't required now, it makes the possibility available later on if necessary. True it takes up more storage, but I think it's worth the trade-off because the data is lost if it's never recorded in the first place.


There is no problem in using the name as a primary key, provided that you never update that column.

Avoiding unnecessary constraints is a performance gain.


How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?

The 'big' players don't even put the images on the same machine. Instead they have "image servers" globally distributed and optimized for pumping out readonly images. In their case, the benefits of distributed processing outweighs the occasional inconsistency (link to non-existent image; vice versa). Yet other machines handle virus checking, scaling, stripping EXIF, etc.

MySQL cannot store a blob bigger than 4GB (yes, you could (should) chunk it). Even 16MB has some issues (with replication, tuning, etc.) that you may trip over.

If you have scans of meta data, and you have big blobs in the rows, the server spends a lot of time stepping over cow patties.  "Vertical partitioning" is an approach to avoid this problem.  In one implementation, I started with a thumbnail and the EXIF data in the main row.  Eventually I discovered the "cow patty" problem and changed to have those two blobs in a parallel table.  It ran noticeably faster.

I've implemented things either way.  Flip a coin to decide which to do.  Think about your decision for six months, then consider changing to the other mechanism.  Meanwhile, design your code so that you can make that change with minimal hassle.


To answer your question, yes normalization is needed.

Common sense is a relative term and is open to interpretation.  RDBMS's have been around since the 1970's.  Normalization has been put into use on countless projects over the past 30 years, much to the benefit of the applications being developed.

The most complex time-consuming problems I have worked on in the past 15 years can be directly attributed to the database designer's lack of understanding (or willful ignorance of) the rules of normalization.

If you don't have the time to design a database properly, then you are in the wrong field, or are working on a project that is doomed to failure.


What is the minimum input length required for sql injection?

Probably one of the shortest injections are used by sqlmap, for example: "union select version();" (note the leading space).

I can't think of anything shorter than ';call boom()-- - which exploits multiple-query feature of LibPQ, and - obviously - requires top-secret application-specific knowledge.

This code is exploitable:

import sys
import psycopg2
conn = psycopg2.connect(dsn='')
cur = conn.cursor()
sql = "SELECT reltuples, relpages FROM pg_class WHERE relname = '{}'".format(sys.argv[1])
cur.execute(sql)
for row in cur:
    print(row)

Exploited:

$ python exploitable.py pg_class
(342.0, 12)
$ python exploitable.py "';call boom()--"
Traceback (most recent call last):
  File "exploitable.py", line 6, in <module>
    cur.execute(sql)
psycopg2.InternalError: Boom!
CONTEXT:  PL/pgSQL function boom() line 1 at RAISE


What are the benefits of packages over standalone procedures and functions?

Benefits of Packages

Logical Grouping – Methods that work together can be put into a cohesive unit rather than just logically coupled but physically separate.

Secure Private Methods - Functions and Procedures can be made private to the package and only be used within it.  This makes the public surface simpler and more secure.

Privilege Management – Permissions can be granted once for a group of procedures that work together rather than separately for each procedure/function required.

Secure Wrapping - Wrapped packages are more difficult to unwrap than wrapped functions/procedures.

Simplified Naming – A larger namespace allows names that are simpler and can be re-used in other packages.

Better Performance – Packages can be compiled and are loaded into memory in entirety rather than piecemeal as other methods.  This benefit if it exists at all is minimal compared to the other benefits.

Reduced Invalidation – Changing a package body does not invalidate dependencies as changing a function or procedure does.

Unique Features - Package Variables, Package Constants, Initialization, Session State, Package Comments, and Overloaded Methods.

References:
11.2 Concepts Guide
Ask Tom Question
StackOverflow.com Question on Package Performance
Unwrapping PL/SQL Presentation (pdf)


SELECT column for a row only if its value is not empty

Not sure there is a way to do that with standard sql you could use case to change that blank to a value https://www.geeksforgeeks.org/sql-case-statement/

The consensus here was that this shouldn't be done on the DB side rather, in whatever mechanism is presenting the data:  Hide column if any value is null or zero via proc sql or view


How can I remove references to tables after removing .frm files?

Shut down mysql.  What files do you see?  Restart mysql; are things better now?

One possible fix is to CREATE a new table (with a different name), copy the .frm to somewhere else, drop the table, shutdown, copy the .frm back as the problematical table name, restart.


I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM
    pupils                   AS p
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class'
WHERE
    p.level = 1
    AND p.class = 'A'
    AND p.id_trimester = 1
GROUP BY
    p.id_pupil
  , p.name
  , p.surname ;


Option 1: Change engines
First thing to do, if at all possible, is probably change MyISAM by InnoDB, which handles concurrency much better. See "MyISAM vs InnoDB", specially the point about "Table-locking vs Row-locking". I quote them:

When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.
When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

[emphasis mine]
Option 2: Master/Slave replication
Another possible (although probably not cheap) solution is to have your database being replicated using an asynchronous Master/Slave setup. [I guess that if you cannot change from MyISAM to InnoDB, it won't be easy to setup a replicated DB... but I don't know either your possibilities nor your limitations.]
Your users (i.e.: your application) should always be working with your Master node, and all INSERTs, UPDATEs and DELETEs should always and only be executed there. The SELECTs can be executed in either node, taking into account that the Slave might be slightly not up-to-date. The SELECTs that take a long time should be executed in the Slave, so all the users in the Master node are not blocked at all.
The Slave node can not be completely up-to-date (as the replication is asynchronous, it can lag behing the master by anything from a few miliseconds to a long time, if the Slave could not process the updates from the master and they "accumulate").
This kind of scenario is not uncommon in applications where you have the Master node working with a setup optimized for OLTP (OnLine Transaction Processing); and the Slave node with a setup optimized for OLAP (OnLine Analytical Processing). OLTP means (mostly) entering and manipulating data, and making relatively simple queries, with small amounts of data every time, whereas OLAP means querying data, normally in large amounts and with aggregations and computations (i.e.: analysis and reporting).


In partition definition filegroup name appears twice, what does that mean?

A partition scheme works with a partition function.

you start by creating the function where you will define the range. then the partition scheme will tell you where to store the data according to that range.

Or, to say it in another way, that particular partition scheme is just stating that for values that fall under the first condition store it in the PRIMARY filegroup, and for values that fall into the the second condition use the PRIMARY FILEGROUP.

you'll sometime see it in examples as CREATE PARTITION ... ALL TO ([PRIMARY])

Hope this helps


How to load data from another sql file into a main sql file? - MYSQL

POSSIBILITY #1
You need to check the table to see if it is damaged or corrupt
Please run the following:
USE bh576
SHOW TABLES LIKE 'AIRLINE';
SHOW CREATE TABLE AIRLINE \G

The first 2 Lines will verify the presence of AIRLINE.frm
The SHOW CREATE TABLE AIRLINE \G will reach out and attempt to retrieve AIRLINE.frm metadata. If this command complains by giving you that error
ERROR 1146 (42S02): Table 'bh576.AIRLINE' doesn't exist

Then, the table is broken in terms of its attachment to the InnoDB data dictionary.
If you get this to work in MySQL Workshop every time, then a corrupt table is not the problem.
POSSIBILITY #2
You need to make sure you are standing in a database.
It may be possible that the target database is not set in the dbcreate_f.sql script.
Before running the scripts, run this:
mysql> SELECT database();

This should return the name of the database you are loading.
If this comes back as NULL just change to the target database before running
USE mydb
source dbcreate_f.sql
source dbload_f.sql


Create tables under a different schema by default instead of [dbo]

This is possible - POC below shown from SQL Server 2019
USE [Test]
GO

CREATE USER [TestSchemaUser]
WITHOUT LOGIN
GO

ALTER ROLE [db_ddladmin]
ADD MEMBER [TestSchemaUser]
GO

CREATE SCHEMA [TestSchema]
AUTHORIZATION [TestSchemaUser]
GO

ALTER USER [TestSchemaUser] WITH DEFAULT_SCHEMA=[TestSchema]
GO

EXECUTE AS USER = 'TestSchemaUser'
GO

CREATE TABLE T1([C1] INT)
GO

REVERT

SELECT S.name [Schema], T.name [Table]
FROM sys.tables T
    JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE T.name = 'T1'


How to optimize SQL with WHERE and ORDER BY

Q1: I don't understand why primary is chosen here?

The index you made helps neither the WHERE or the ORDER BY due to the columns in the wrong order.

Since you selected only the erp_orders_id, the PRIMARY KEY was chosen.

  Q2: Why is this query so slow even with execution plan indicates that it scanned only 1 row?

Since you forced the index, the entire index was scanned until one row was found that matched o.orders_export_time >= '2015-09-20'. If you have rows from earlier months, even earlier years, those rows would be read until '2015-09-20' was finally found.

  Q3 how am I supposed to optimize this query?

As @dnoeth said in the comment, reverse the column order in the index.

ALTER TABLE `erp_orders`
ADD KEY `IDX_ORDERID_EXPORTTIME`(`orders_export_time`,`erp_orders_id`) USING BTREE;


Can't connect to Azure SQL with error 18456

There are a bunch of reasons their attempt to log in could fail, chief among them being they are just not typing the username or password correctly
Check the sys.event_log DMV for a more detailed reason for the login failure.  You should be able to filter to connection_failed events:
SELECT *
FROM sys.event_log
WHERE event_type = 'connection_failed'
ORDER BY start_time DESC;

This will let you know if they are having firewall issues (blocked_by_firewall), timeouts (client_close), typos (invalid_login_name), etc.


How do I disable foreign key constraints in my Postgres dump file?

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS.

test=# CREATE TABLE table1 ( id serial PRIMARY KEY );
CREATE TABLE

test=# CREATE TABLE table2 ( fkey int REFERENCES table1(id) );
CREATE TABLE

test=# INSERT INTO table2 VALUES (1);
ERROR:  insert or update on table "table2" violates foreign key constraint "table2_fkey_fkey"
DETAIL:  Key (fkey)=(1) is not present in table "table1".

test=# ALTER TABLE table2 DISABLE TRIGGER ALL;
ALTER TABLE

test=# INSERT INTO table2 VALUES (1);
INSERT 0 1

I would never suggest anyone DISABLE TRIGGERS though, or make them NOT VALID. They're there to ensure data integrity and disabling them for even brief periods is a recipe for disaster and an anti-pattern in my opinion.

Just pg_dumpall and trim out what you don't need.


I have some queries you can run against the INFORMATION_SCHEMA

Run this to get the Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Run this to get the Total MySQL Data and Index Usage By Database

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Run this to get the Total MySQL Data and Index Usage By Database and Storage Engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

CAVEAT

In each query, you will see (SELECT 3 pw). The pw stands for the Power Of 1024 to display the results.

(SELECT 0 pw) will Display the Report in Bytes
(SELECT 1 pw) will Display the Report in KiloBytes
(SELECT 2 pw) will Display the Report in MegaBytes
(SELECT 3 pw) will Display the Report in GigaBytes
(SELECT 4 pw) will Display the Report in TeraBytes
(SELECT 5 pw) will Display the Report in PetaBytes (please contact me if you run this one)

Here is a report query with a little less formatting:

SELECT IFNULL(db,'Total') "Database",
datsum / power(1024,pw) "Data Size",
ndxsum / power(1024,pw) "Index Size",
totsum / power(1024,pw) "Total"
FROM (SELECT db,SUM(dat) datsum,SUM(ndx) ndxsum,SUM(dat+ndx) totsum
FROM (SELECT table_schema db,data_length dat,index_length ndx
FROM information_schema.tables WHERE engine IS NOT NULL
AND table_schema NOT IN ('information_schema','mysql')) AA
GROUP BY db WITH ROLLUP) A,(SELECT 1 pw) B;

Give it a Try !!!

UPDATE

I just ran the first query as is on a client's DB

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 3 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MyISAM         |             0.673 GB |             0.079 GB |             0.752 GB |
| InnoDB         |             4.227 GB |             2.436 GB |             6.663 GB |
| Total          |             4.900 GB |             2.515 GB |             7.415 GB |
+----------------+----------------------+----------------------+----------------------+
3 rows in set (0.79 sec)

mysql>

It works as is.

UPDATE 2015-01-16 14:46 EST

Here are Queries to Report Sizes and Computes the Memory Units On-The-Fly

Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(ENGINE,'Total') "Storage Engine",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT ENGINE,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT engine,data_length,index_length
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY ENGINE WITH ROLLUP) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database

SELECT IFNULL(DB,'Total') "Database",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM (SELECT DB,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM
(SELECT table_schema DB,data_length,index_length FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL) AAA GROUP BY DB WITH ROLLUP) AAA) AA) A,
(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database/Storage Engine

SELECT IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT DB,ENGINE,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT table_schema DB,ENGINE,
data_length,index_length FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY DB,ENGINE WITH ROLLUP) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;


The issue repeated for me on a legacy PostgreSQL 9.2 to 11 upgrade. The pg_upgrade would fail with: FATAL:  unrecognized configuration parameter "unix_socket_directory"

Blog entry at https://altinukshini.wordpress.com/2018/05/03/upgrade-postgres-9-2-to-9-6-in-centos-7-bold-lines/ gives the solution, ditto:

mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >>
   /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Run now the pg_upgrade (as postgres user), the unix_socket_directories error should be gone.
Lastly, (as root) revert:
mv -f /usr/bin/pg_ctl{-orig,}


Populate one table with data from another one on selected cells

This would work ;

UPDATE users
SET users.email = (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id)
WHERE EXISTS (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id)
AND users.email IS NULL;


Select records with the same first column value and different second column value? SQL Server

You could use intersect to get the matching records

SELECT ContentId
FROM dbo.Content
WHERE TagId = 1003
INTERSECT
SELECT ContentId
FROM dbo.Content
WHERE TagId = 1;

Result

ContentId
1002
1006
1007

DbFiddle


Indexed varchar column very slow (40M+ rows)

Do you really need to select every column?  If not, then don't.  It might be generating extra TOAST table lookups, or might be inhibiting index-only scans.

You are selecting 1.5 million rows.  Indexes are awesome, but they are not magical.  1.5 million is a lot, especially when scattered randomly throughout a giant (how big is it?) table.

You can almost certainly speed this up by clustering the table on the "index_events_on_type" index.  But this a resource intensive operation which will probably require a maintenance window to do, and it won't stay clustered upon future inserts/updates.  Alternatively, you could partition the table by "type".  This has more or less the same effect as clustering (in this context!) but it stays partitioned in the face of future actions.

What version of PostgreSQL are you using?  I would have expected you to get a parallel query here if it is recent version (not that it will necessarily be effective, but still I would have expected one).

Given that you are retrieving ~4% of the table, a seq scan might be more effective than an index scan.  You can do set enable_bitmapscan=off and see how that does.


I believe removing the category without showing it as being moved into a "movies" reference table is an oversight.

Yes, removing that column as shown in  your question does result in data loss.


Excluding Read only Log backups using ola-hallengren script

The main restrictions on backups in an Availability Group are:

Full backups on a secondary must be COPY_ONLY.
Differential backups cannot be taken on a secondary.

Your backup application should take into account the backup preference setting of your availability group, but it is not forced to do so. Ola's scripts do respect this setting and only backup on the preferred replica.

If you're seeing log backups on your secondary, check the backup preferences in the AG, as they may have Prefer Secondary set, which means log backups could happen on any replica. Change this to Primary Only and you won't see log backups on your secondary with Ola's scripts.

NOTE: is_read_only in "sys.databases" only indicates if the database is in READ_ONLY or READ_WRITE mode. This is not the same as a readable AG secondary database. See this link for more info.


Use An Out Variable And Perform Calculation In One Query

You need to assign the name column to a variable, and return it as well as the @stat variable:

CREATE TABLE dbo.PPerformance
(
  [name] VARCHAR(500)
  ,attempts INT
  ,success INT
)

INSERT INTO dbo.PPerformance ([name], attempts, success)
VALUES ('Bob', '22', '10')
    , ('Red', '14', '12');

DECLARE @SQL NVARCHAR(MAX);
DECLARE @name VARCHAR(500);
DECLARE @stat FLOAT;
DECLARE @vars NVARCHAR(MAX);

SET @vars = N'@stat float OUTPUT, @name varchar(500) OUTPUT';

SET @SQL = N'SELECT @name = pp.name
    , @stat = pp.attempts / pp.success
FROM dbo.PPerformance pp
WHERE pp.name = ''Bob'';';

EXECUTE sys.sp_executesql @SQL, @vars, @stat OUT, @name OUT;

SELECT Name = @name
    , Stat = @stat;

DROP TABLE dbo.PPerformance;

The error is coming from this part of your code:

Select name, @stat = attempts/success

This is attempting to assign a value to the @stat value and show the content of the name column.  You cannot perform both operations in a single statement.

There are a couple of items in your source code that are potentially troublesome:

You should always specify the schema name when referencing objects in T-SQL.  So, select column1, column2 from mytable should be select column1, column2 from schema.mytable.
End each statement with a semi-colon:  select column1, column2 from schema.mytable;
Get in the habit of using an alias for tables referenced in T-SQL statements.  For example:  select mt.column1, mt.column2 from schema.mytable mt;
Don't use reserved names as column or table names.  You've named the first column in your table name, which is a reserved word; I'd change that to PerfName or something.


Upgrade Postgresql from 9.6 to 11 with different data directory

You can specify a custom data_directory as an optional parameter to the command pg_upgradecluster:

pg_upgradecluster [-v newversion] oldversion name [newdatadir]

So for your case, it should be:

sudo pg_upgradecluster -m upgrade 9.6 main /opt/postgresql/11/main

Note that you must first remove any existing cluster at the specified location (/opt/postgresql/11/main) and create an empty directory with the correct owner before running pg_upgradecluster:

sudo pg_dropcluster --stop 11 main
sudo mkdir -p /opt/postgresql/11/main
sudo chown postgres:postgres /opt/postgresql/11/main


Does amazon rds PostgreSQL require vacuum?

Run this query to show if/when your tables were last vacuumed.

SELECT * FROM pg_stat_all_tables;

A table won't be vacuumed until the insert/update/delete threshold is reached, which is 20%.

You can tweak the setting in RDS by creating a new DB Parameter Group and change the setting you want.


In SQL Server Always On 2019 environment should SSRS/SSIS be on a separate server?

The documentation at Reporting Services with Always On Availability Groups (SQL Server) states:

Reporting Services and Power BI Report Server offers limited support for using Always On availability groups with report server databases. The report server databases can be configured in AG to be part of a replica; however Reporting Services will not automatically use a different replica for the report server databases when a failover occurs. The use of MultiSubnetFailover, with the report server databases, is not supported.
Manual actions or custom automation scripts need to be used to complete the failover and recovery. Until these actions are completed, some features of the report server may not work correctly after the Always On availability groups failover.

So in general, you won't get any benefit of putting the reporting service database in an availability group without figuring out what needs to be done after a failover occurs.
Using SSIS with an availability group is supported and you can review the pertinent information at SSIS Catalog.
In general, putting them on a standalone server will be easier.  If you have hardware redundancy at the VM host layer, you are getting almost no value out of putting them in an availability group, particularly if your VMs are on SSDs that can reboot in about 20 seconds.


Providing production MySQL access to outside company

Your approach seems to be sound. However, I am unsure of what real business intelligence can be gleaned if the reporting is made off of incomplete data. Create a dedicated user with limited privileges (IE No writing/dropping allowed). There is always a tradeoff of information security and information quality.

Perhaps it would be best to understand what you're hoping to accomplish with this reporting and identify what sensitive info can be removed.


Alter auto-increment of table with 16 million rows

It depends, could be an hour or so. Perhaps you may like to give percona toolkit a try; pt-online-schema-change.

pt-online-schema-change alters a table’s structure without blocking reads or writes.
Specify the database and table in the DSN. Do not use this tool before reading its
documentation and checking your backups carefully.

Source: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html


Tom Kyte posted something like this once and the issue was the CURSOR_SHARING parameter.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520

If it is set to FORCE at the system/database level on host2, it could be mapping/forcing the 'XXXX' hard coded value to an arbitrary 32 character variable.


Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header?

The sqlite3 shell is designed to be simple, so there are limited customization mechanisms.

There is no built-in way to modify the widths in this way.

You'd have to either modify the shell's source code, or use some alternative shell written in a scripting language.


Table Design for Student Attendance hourly for each day

It appears the OP is conflating how the data should be stored in the database with how the data will be displayed and/or entered when taking classroom attendance.

These are not necessarily the same thing.  Display should not drive storage.

The UI could display the data the way the OP shows above in a classroom attendance form and have a middle layer that maps those values to and from more normalized tables in the database.

More work for the middle layer but more extensible and configurable when

(1) the attendance day is longer or shorter
(2) students are added or removed from the class

An entry in an CLASS_ATTENDANCE table will have all the data that changes on a daily basis with foreign keys for the class, student, and status.

(0) CLASS_ATTENDANCE ID
(1) CLASS ID
(2) STUDENT ID
(3) DATE
(4) STATUS CODE
(5) and perhaps TEACHER ID of the person who took attendance

An entry in a CLASS table will have

(0) CLASS ID
(1) COURSE ID
(2) PERIOD ID
(3) and perhaps TEACHER ID of the person who normally teaches the class

An entry in a PERIOD table will have

(0) PERIOD ID
(1) START TIME (for example 08:00)
(2) END TIME (for example 08:55)

Periods usually do not change during a single semester

There would like be a CLASS_STUDENT table that tracks which students are assigned to which class and drives filling in the CLASS_ATTENDANCE table daily.

(0) CLASS_STUDENT ID
(1) CLASS ID
(2) STUDENT ID

This just one way to normalize the data.  And there is still some redundancy between the CLASS_STUDENT table and the CLASS_ATTENDANCE table.


The problem appears to be that the lines that are not importing correctly have characters that, encoded in code page 950, having a trailing byte of 0x7C, which is valid, but also happens to be the pipe symbol, which you are using as a delimiter. For example:

SELECT CONVERT(VARBINARY(20), CONVERT(VARCHAR(20), N'四會'
                                      COLLATE Chinese_Hong_Kong_Stroke_90_CI_AI));
-- 0xA57C B77C

Each of those two characters has a trail-byte of 0x7C. The parser seems to be viewing that as a delimiter instead of part of a 2-byte sequence for a code page 950 character. This is why you get a "?" in both "City" and "Country" with the remaining input line in the "Gender" column. The "?" in those two columns is due to the lead bytes 0xA5 and 0xB7 not being valid on their own.

The next line has the same issue:

SELECT CONVERT(VARBINARY(20), CONVERT(VARCHAR(20), N'元朗大坑|'
                              COLLATE Chinese_Hong_Kong_Stroke_90_CI_AI))
-- 0xA4B8 AED4 A46A A77C 7C

This time I kept the delimiter that comes at the end of the "City" value (so it would be clearer that the delimiter is the same byte value as the trail byte of that 4th character). The 4th character, 坑, is encoded as 0xA77C in code page 950. This is why only the 4th character shows up as "?".

So, this might be a bug in SSIS. Or, perhaps it's a configuration issue. Is there a way to indicate that the entire file, not just this one column, is code page 950? Dealing with code pages on a column-by-column basis, in terms of a text file, makes no sense. The entire file is encoded as code page 950, not just that one column. If it's possible to change the delimiter that might help, but it most likely just delays the problem as any delimiter can be encoded as a valid trail-byte value for double-byte characters. Given that OPENROWSET works correctly, I have to believe that this also can (although you aren't setting the file's encoding for OPENROWSET, just the column, so this could still be an SSIS bug).

Please try the following to set the file's encoding within SSIS:

Go to the Flat File Connection Manager Editor (General Page)
Under "Code Page", enter in 950.
Make sure that "Unicode" is not selected.

Also, check the "CITY" column under "Output Columns" and make sure that it is using "Standard Parse" and not "Fast Parse" (since "Fast Parse" is locale-insensitive).


try this

C:\Program Files\PostgreSQL\9.6\bin>psql -U postgres dbname < D:\testdump.sql

or

C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres dbname < D:\testdump.sql


Postgres - how to find contents of one field within another field

The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows

So the absence of any actual position is what you are looking for, which is simpler:

SELECT recordId, path
FROM   table_a
WHERE  path !~ recordId::text;

Note the cast to text (or varchar, all the same).

Do NOT cast to char like you have it in your added (broken) solution. char is a synonym of character (or bpchar) and defaults to character(1) (!), so effectively only takes the first digit of your number and produces many false positives. Related:

Best way to check for “empty or null value”

!~ is the operator for negated regular expressions

NOT LIKE would do the job, too:

...
WHERE path NOT LIKE ('%' || recordId::text || '%');


Granting password protected role to another role problem in Oracle

First of all, your command is syntactically incorrect. It should be:

grant OBLAH_DEE to "OBLAH_DAH" IDENTIFIED BY "15gx9FR7GG";

The problem with that: you can not grant password protected roles to another role. See https://support.oracle.com/epmos/faces/DocContentDisplay?id=1663997.1 for details.

ERROR
-----------------------
ORA-28405: cannot grant secure role to a role

...

Starting With Oracle 11.2.0.4.0 and 12c granting secure or password
protected role to any role  (normal or password protected) we raise
this expected error.

grant::=

grant_system_privileges::=

grantee_identified_by:

As you can see in the 3rd picture, when using the IDENTIFIED BY clasue, the grantee can be only an user, but not a role. (By the way, that clasue is used for something else.) Using the regular grantee_clause, this is the error you would receive:

SQL> create role r1;

Role created.

SQL> create role r2 identified by 1;

Role created.

SQL> grant r2 to r1;
grant r2 to r1
*
ERROR at line 1:
ORA-28405: cannot grant secure role to a role

And what you tried:

SQL> grant r2 to r1 identified by 1;
grant r2 to r1 identified by 1
*
ERROR at line 1:
ORA-01920: user name 'R1' conflicts with another user or role name

As a workaround, the below parameter can be set to revert to the old behaviour:

 alter system set "_grant_secure_role"=true scope=spfile;

You must restart the database for the change to take effect.


could this have happened because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart?

Absolutely.  If you kill -9 mysqld and you're using MyISAM tables, you're almost guaranteed to crash some tables unless the server is really, really, really not busy... although the definition of a "crashed" table isn't as catastrophic as it sounds in all cases... it only means, at the simplest level, that the table is not in the state that it should have been in had the server closed it properly.

  Is there anther way to fix/repair/recover that table?

Have you been through the steps covered in How to Repair MyISAM Tables?

Also, consider these things, which may give you some ideas that could get you on a faster path to recovery if they are not familiar concepts:

With MyISAM tables, everything is in the files.  You could literally take your wp_options.* files (assuming for the sake of discussion that they were not crashed), copy them into a database directory on another MySQL server, SHOW TABLES and you would find that your table is visible and available on that other server.  For future recovery, if you had a slave server, you could shut it down and copy this table's files directly from there back to the master and be back online.  (This is only true for MyISAM and ARCHIVE, not InnoDB, but by contrast, InnoDB tables are extremely immune from this sort of corruption due to a hard/forced shutdown).
The myisamchk utility is not technically a part of the actual MySQL Server itself -- it is a standalone utility built from many of the same underlying libraries, but can be used on a different computer, even without MySQL server installed.  The myisamchk utility has various capabilities and levels of aggression.
You could, therefore, stop MySQL, copy the table files to a different machine -- one with a nice speedy SSD, for example, could make a big difference in recovery time -- and repair the files with myisamchk, then (with the MySQL server stopped again) copy the table files back.  If you have other things running on your MySQL server, you could bring those other things back online while not bogging the server down with this repair.

By contrast, mysqlcheck is nothing but a simple program that connects to your MySQL server using the client libraries and...

  ...uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user.

...so while the two utilities use many of the same methods to repair the tables, they are also fundamentally very different.


Declarative and Procedural Query Languages

The writing is poor. "Declarative" is informal & they use it it in two ways. The general meaning is, "describe a result (not a process)". But many descriptions of results have an obvious interpretation as a process. So those "describe a result & a process" whether or not they were intended to. The first says "the notations can be used to describe results without intending any particular process". The second says "the algebra has a procedural interpretation & the calculi don't".

But the latter statement is a myth. It is parroted received [pseudo-]wisdom. The algebra has an obvious procedural interpretation--we call operators on arguments. But the calculi do too. The calculi don't have an obvious procedural interpretation in terms of standard relational operators.

But the idea is that the calculi aren't used to request such procedures.

The calculi have a straightforward procedural interpretation in terms of standard relational operators where you mechanically convert them to "prenex normal form" then mechanically convert them to standard relational algebra. (Codd's reduction algorithm.) Also, they have an obvious procedural interpretation (not in terms of relational algebra) in terms of trying every possible tuple for membership in the result. (Like standard Tarskian semantics for predicate calculus.) Also, they have an obvious procedural interpretation in terms of an algebra with two simple but impractical operators added--a generalization of UNION (corresponding to OR) & an operator returning the tuples not in a relation (corresponding to NOT). (Already NATURAL JOIN corresponds to AND & PROJECTing out corresponds to EXISTS.) (Like Tarskian cylindric-algebra semantics for predicate calculus.)

Difference between Relational Algebra and Relational calculus
Procedural and non-procedural query language difference


Yes, you can simply stop one config server, upgrade and restart it. That is one of the reasons why there are three: even when taking down one, there are two others to read from, so one might fail.

A few things to keep in mind, though:

When one config server is down, the cluster meta data is read only: no chunk splits or migrations during that time. So in order to prevent JumboChunks, you better hurry a bit.
Talking of JumboChunks: you should check your cluster for them after update
As for the order: Start with the last server in your configdb string, finish with the first
Always, Always, ALWAYS read the migration guide of the version you are updating to


mysql: select join table with latest record for both table

I hope that query is selfexplanatory enough.

SELECT a.*, b.*
  FROM
       (SELECT MAX(w.id) AS id
             , w.social_num
          FROM tableA AS w
         GROUP BY w.social_num
       ) AS maxa
  LEFT JOIN
       (SELECT MAX(z.id) AS id
             , z.social_num
          FROM tableB AS z
         GROUP BY z.social_num
       ) AS maxb  ON maxb.social_num = maxa.social_num
  JOIN tableA       AS a  ON a.id = maxa.id
  LEFT JOIN tableB  AS b  ON b.id = maxb.id
;

The only assumption I've made is that persons from tableB are the subset of persons in the tableA and there is no persons listed in the table B and not in the tableA. Otherwise the FULL OUTER JOIN not implemented by mysql should be emulated by UNION.


Best way to compare strings using pre-built ngrams?

I've done this in SQL Server using a modified version of Dice's Coefficient. Basically, you store a precomputed table of q-grams from your lookup data set. This table should have the primary key of the source record, the resulting q-gram, and the number of times that q-gram appears when breaking up the string.

Then when you want to do a lookup, break your input string into a similar list of q-grams (temporary table, subquery, etc.), and join those to your lookup table on the q-gram, grouping by the primary key from the lookup row (which, in the lookup table, is actually a foreign key rather than a primary key). For each matched q-gram, calculate the number of matches as the minimum cardinality between the input value and the lookup table row, e.g. the string 'aaaa' could have 'aa' * 3, and 'aaaaa' could have 'aa' * 4, thus you would have 3 matches. Double this number to get 6.

Once you've got this doubled number of matches, divide by the total number of q-grams obtained from both the input string, and the rows in the lookup table associated with a given primary key. So 'aaaa' would have 3 q-grams, and 'aaaaa' would have 4. You would calculate the similarity as 6/7. You can then filter this similarity quotient based on your desired threshold.

The nice part about this algorithm is that it's fast, since after splitting your input string, it's just a bunch of index lookups. The downside is that it doesn't put any emphasis on ordering of q-grams, so having two words out of order won't affect the match quality (assuming you're not letting q-grams cross word boundaries).

Following is some sample code from SQL Server to explain the concept, but it makes use of features not found in MySQL. You'd probably need to rewrite some of this using subqueries rather than common-table expressions.

CREATE PROCEDURE FuzzyCustomerLookup
    @name varchar(8000),
    @threshold decimal(10,4) = 0.0,
    @limit_results int = 2147483647
AS
WITH inp_q AS ( --Split input string into q-grams
    SELECT
        q.qgram,
        COUNT(*) AS cardinality
    FROM dbo.Qgrams(@name) q --Function to split string into q-grams
    GROUP BY q.qgram
),
matches AS ( --Count matches of input string against each lookup row
    SELECT
        c.row_id,
        SUM(CASE WHEN c.cardinality < i.cardinality THEN c.cardinality ELSE i.cardinality END) AS matches
    FROM CustomerQgrams c --Precomputed table of q-grams
        INNER JOIN inp_q i
            ON c.qgram = i.qgram
    GROUP BY c.row_id
),
dice AS ( --Calculate match quality
    SELECT
        matches.row_id,
        CAST(matches.matches * 2 AS decimal(10,4)) / (CAST(i.cardinality AS decimal(10,4)) + CAST(c.cardinality AS decimal(10,4))) AS similarity
    FROM matches
        INNER JOIN (
            SELECT row_id, SUM(cardinality) AS cardinality
            FROM CustomerQgrams
            GROUP BY row_id
        ) c
            ON matches.row_id = c.row_id
        CROSS JOIN (
            SELECT SUM(cardinality) AS cardinality FROM inp_q
        ) i
)
SELECT TOP (@limit_results)
    ch.CustomerNo,
    ch.CustomerName,
    dice.similarity
FROM dice
    INNER JOIN Customers ch
        ON dice.row_id = ch.row_id
WHERE dice.similarity >= @threshold
ORDER BY dice.similarity DESC


wrong number or types of arguments in call to X?

This:

DECLARE

    TYPE studId_scholarshipModification IS RECORD(studentID studenti.id%TYPE,
      modificationPercentage NUMBER);
    TYPE modificationsRecordVarray IS VARRAY(2) OF studId_scholarshipModification;

    modifications modificationsRecordVarray:=modificationsRecordVarray();

BEGIN

    modifications.extend;
    modifications(1).studentID:=500;
    modifications(1).modificationPercentage:=0.4;
    modifications.extend;
    modifications(2).studentID:=501;
    modifications(2).modificationPercentage:=0.8;

    collegeManagement.modifiyScholarships(modifications);

END;

Should be this:

DECLARE

    modifications collegeManagement.modificationsRecordVarray :=
      collegeManagement.modificationsRecordVarray();
    -- no type definition, using the type from the package

BEGIN

    modifications.extend;
    modifications(1).studentID:=500;
    modifications(1).modificationPercentage:=0.4;
    modifications.extend;
    modifications(2).studentID:=501;
    modifications(2).modificationPercentage:=0.8;

    collegeManagement.modifiyScholarships(modifications);

END;

Defining a type with the same name and properties is not the same as using the actual type defined in the package.

You can test it with:

create or replace package pkg1 is
type t_m is varray(2) of number;
procedure p1 (p_m in t_m);
end;
/

create or replace package body pkg1 is
procedure p1(p_m in t_m) as
begin
  null;
end;
end;
/

declare
  type t_m is varray(2) of number;
  m t_m := t_m();
begin
  pkg1.p1(m);
end;
/

declare
  m pkg1.t_m := pkg1.t_m();
begin
  pkg1.p1(m);
end;
/

The first call fails, the second call succeeds.


truncate table in mysql 8.0 is slower than older version

Alas, DDL statements are slower in 8.0.  This is because they can now be inside transactions and rolled back.
If the goal is to start with a fresh set of empty tables for a new test, I am having trouble coming up with a faster way.  Perhaps something involving a "Logical Volume" (cf "LVM").  (Note MySQL needs to be stopped throughout the steps below.)
Setup:

Establish a separate disk partition for the main directory tree for MySQL.
Clone it (LVM).  (This will be the master for a later step)

When starting a new test:

Drop the current logical volume
Clone the above clone to get a fresh set of empty tables

(Sorry, I don't have all the details.)


Does the definition of 2NF prohibit _transitive_ dependencies on a partial key or only direct ones?

"Loose" dependency is the right idea

You seem to be thinking of "strict" vs "loose" or "direct" vs "direct or transitive" FDs where the explicitly given FDs are taken as the "strict"/"direct" ones. Such a notion of "transitivity" is only an artifact of how you chose to define the situation. The set of given FDs is only relevant in that their closure is relevant.

The definition of 2NF refers to all partial FDs and all full FDs. What particular ones given to describe a bunch that hold is irrelevant. When certain sets of FDs are present, certain other FDs are too. They are all just present together. By definition they are the ones in the closure of the given ones.

Re given FDs

It is a common problem that people will just say "these FDs hold". Note that that means "all the FDs in their closure hold", because if the given ones hold, the closure's hold--by definition of the closure. But if they don't say that the ones in the closure are the only ones that hold then you are (in general) limited in what you can determine about the relation. So usually they really mean, the only ones are the ones in the closure of the given ones. Ie that the given ones form a cover for the ones that hold.

Moreover if they say this in practice beware that they may not actually know that there aren't any other FDs than in the closure of the given ones, ie that they form a cover. Without knowing that there are no others it is possible to answer "what is the closure of these", but it may not be possible to answer "what are the CKs".

Transitive vs non-transitve FDs

The term "transitive" FD and "non-transitive" FD are defined and play a role in the definition of 3NF. (A non-transitive FD is one that's not transitive.) But they are not defined in terms of given FDs as "direct" either.

FD (functional dependency) A-> C is transitive when there is an X where A -> X and X -> B and NOT X -> A. (Likely that last conjunct is unexpected.)

Transitive FDs are those that hold as a consequence of all the FDs that hold, not just the ones given.

Similarly the definition of 3NF refers to all transitive FDs and all FDs. (2NF plus every non-prime attribute non-transitively dependent on every candidate key gives 3NF.) There is no notion of "direct" FD except "non-transitive" FD.


How to correctly use PostgreSQL to limit multiple and/or concurrent executions of a task

Since you're trying to serialize work, I'd update a record in a table.

CREATE TABLE task_keys (
  task varchar(10) primary key,
  last_executed timestamp with time zone not null,
  by_worker_id integer
);

INSERT INTO task_keys(task, last_executed)
VALUES ('FOO', '-infinity');

then to see if you can run a task yet:

UPDATE task_keys SET
  last_executed = current_timestamp,
  by_worker = $1
WHERE task = 'FOO'
  AND last_executed < (current_timestamp - INTERVAL '1' MINUTE)
RETURNING *;

The isolation rules of READ COMMITTED guarantee that if this query successfully updates the table and returns a row, no other query can have concurrently done so. A row lock is taken on the relevant task_keys row. If another UPDATE tries to affect the same row, it'll wait until the row lock is released by a commit or rollback of the holding transaction... then it will re-check the WHERE clause. If the other tx committed then the WHERE clause will no longer match, so it'll affect zero rows.

See the documentation on transaction isolation.

If you need concurrency this gets a little tricker. What you really want is a token pool that refills on a timer, where workers can grab tokens from the pool to do work. That's effectively what we're doing here, with one token - so one option is to add more rows for the same task and grab the first task where the last_executed timestamp is old enough.

There are two flaws with this whole approach though:

It doesn't know when a task finishes, so long running tasks could overlap; and
It doesn't care if a task succeeds or fails

To solve those you need to use a proper work queue implementation. Those are currently very difficult to implement correctly in the database, so I suggest you look at using an external message queue / work queue system to manage them. In PostgreSQL 9.5 the new FOR UPDATE SKIP LOCKED feature will make implementing work queues like this in the database quite simple, though.

BTW, advisory locking is often a good choice for this sort of thing, but it won't help you with the need to expire the lock automatically after a certain amount of time.


Is there a standard implementation of a Users Database?

I suggest that you use typical normalization standards. One user per row.

User ID (incrementing bigint)
User Common Name (to be displayed on
the site)
User Email Address
Password Salt (Unique for every user, inserted when the account is created)
Password (Hashed with the salt - MD5 or SHA1, your preference)
Date Account Was Created

The rest is up to you given your business rules.


MySQL specific database configuration file

Let's say

you are using MySQL in Linux
datadir is /var/lib/mysql

You could use the init-file option

You could just create an init file called /var/lib/mysql/init-file.sql

Put the customer SQL in /var/lib/mysql/init-file.sql

You could then try one of two things

Try #1

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file.sql

then run

service mysql restart

Try #2

Directly run this

service mysql restart --init-file=/var/lib/mysql/init-file.sql

Give it a Try !!!

CAVEAT

That was a global setting of init code. If your session has to enable autocommit, then just run

SET autocommit=0

in the session or put SET AUTOCOMMIT=0 in the init-file option file. This may still not produce the effect you want since everything goes back to default settings upon connection termination.

UPDATE 2013-02-28 15:40 EST

If you are looking for a startup option for my.cnf to affect the client, try this:

[client]
autocommit=0

or

[mysql]
autocommit=0

Perhaps try adding another my.cnf in the Linux home directory with these options

For more information, see the MySQL Options File Documentation


Why isn't partition-level lock escalation the default?

BOL has answer -- It is not ON by default as it may increases the potential for deadlock.

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.


What is the syntax to sp_recompile logon trigger?

I think I found it:

sp_refreshsqlmodule (Books Online)

  Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database.

EXEC sys.sp_refreshsqlmodule
    @name = 'ddl_trig_name',
    @namespace = 'SERVER_DDL_TRIGGER';


You can do all that without subqueries:

SELECT DISTINCT S1.starttime
     , S1.endtime

  FROM Slot AS S1
  JOIN Slot AS S2  ON S2.assigned_To_Id = S1.assigned_To_Id
                  AND S1.assigned_To_Id IN (2, 3, 4)
                  AND S1.id != S2.id -- a little trick to prevent joining rows with itself
                                     -- slightly reduce the derivative table size
                                     -- and speed up the overall performance

 WHERE S1.starttime >= '2018-08-08 00:00'
   AND S1.endtime <= '2018-08-08 23:59'
   AND S1.freetime > 0
   AND ( S1.freetime > '00:10:00'
         OR ( S2.starttime = S1.starttime + INTERVAL 60 MINUTE
              AND ADDTIME(S1.freetime, S2.freetime) > '00:10:00' ))

 ORDER BY S1.starttime ASC
;

In fact aliasing is not transitive operation in the mysql. Say, simple query

SELECT 1 AS a, a AS b;

yeild an error Unknown column 'a' in 'field list'. So you can create an alias for the real object not for another alias.


Postgresql Backup all databases seperate files

You can combine pg_dump with a batch script that iterates over all databases:
I use something like this to dump my development databases:
@echo off
set PGBINDIR=c:\Program Files\PostgreSQL\13\bin

rem avoid the password and user prompt from pg_dump
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=********
set PGPORT=5432

rem create a text file listing all databases
rem adjust here if you want to exclude a database
"%PGBINDIR%\psql" -X -c "select datname from pg_database where not datistemplate" -A -t -o dblist.txt -d template1

rem dump all user accounts and roles
"%PGBINDIR%\pg_dumpall" --globals-only --file=postgres_globals.sql

for /f %%i in (dblist.txt) do (
  echo Backing up: %%i
  "%PGBINDIR%\pg_dump" --create --format=c --file=%%i.pgdmp %%i
)


Converting UTC time to timestamptz always adds the +01 offset in Postgres

A simple cast from timestamp to timestamptz assumes your current time zone. If you want the cast to assume UTC (or any other time zone) you have to use the AT TIME ZONE construct again:

SELECT (timestamptz '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';

You may be missing that the AT TIME ZONE has two different use cases. It can transpose timestamptz to timestamp and vice versa.
Related answer with detailed explanation:

Confused about TIMESTAMPTZ internal conversion

Be aware that your statement does not make sense:

  I would expect to get 2016-02-20 14:15:54.000495 as timestamptz.

The text representation of timestamptz is always displayed as local time according to your current time zone setting, extended with an offset to UTC. The literal you display has no offset, so it is a timestamp literal, not timestamptz. Basics here:

Ignoring timezones altogether in Rails and PostgreSQL


DB2 LUW Timestamp differences in versions

Depending on its precision, a TIMESTAMP value may have fractional seconds. For literals, the precision is defined by the presence of a fractional part after seconds, as well as the number of digits in the fractional part. For columns, the precision is defined as an optional parameter after the name of the type in the column definition. For instance:

SomeColumn TIMESTAMP(9)

defines the column as a timestamp with nanosecond precision (9 digits after the decimal point). (As you have SQL Server background, you can compare this to the SQL Server datetime2 type, which also has optional precision.)

For reference, see IBM Knowledge Center - Datetime values.

It looks likely that the column in question is defined differently between the development environment and production, the development definition being TIMESTAMP(0) (no fractional seconds) and the production definition TIMESTAMP(6) (or simply TIMESTAMP, which means the same).

That explains why your developer never has to specify fractions of a second in the development environment – because the column's values cannot possibly have fractional seconds. At the same time, she may have to specify timestamps with a fractional part in the production environment because the column type there allows fractions of a second in the values and some timestamps apparently do have fractional parts.


SQL Server 2012 CPU spike due to LinkedServer - how to find the offending query?

To answer the question: How to find the offending query:

Since the spikes in the graph you posted last for several minutes you have plenty of time to use the following method:

Download sysinternals process explorer

start process explorer and find the SQL Server process.
right click and select properties
look at the thread tab.
Sort on the CPU column and note the thread id (TID) that is consuming the most CPU.

Use this query and lookup the query that is currently being executed by that thread:

SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <thread id>


The transaction log file can't make up for a corrupted database file.  Its just going to try and rollback or roll forward transactions on the database file as it is recovered.

One more possible thing to try although I doubt it will work.  Even if it does, with a corrupt mdf, you're probably not going to get back everything you need.

As a preliminary step, and being sure that you have a copy of your mdf and ldf files somewhere safe, remove any entries of the original database

DROP DATABASE mydatabase

1. Create a new database

Use the the same physical file names and logical file names as was defined by the original corrupt database

--create the database
CREATE DATABASE mydatabase
  ON(NAME=’mydatabase,
        FILENAME='C:\my\path\to\file.mdf')
  LOG ON(NAME='mydatabase_Log',
        FILENAME='C:\my\path\to\file.ldf')

GO

2. Shutdown MSSQL

SHUTDOWN

Now copy in the corrupt mdf and ldf files over the newly created ones

3. Start MSSQL

You should now see the database in suspect mode

4. Put database into emergency mode SQL Server 2000

USE master
GO
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

UPDATE sysdatabases SET status= 32768 WHERE name = 'mydatabase'

5. restart MSSQL

5a. (optional)  Since you're data is now accessible via Query Analyzer, you could just export your important data into a new database.

-- create the new database
CREATE DATABASE mynewdatabase

-- import data from the old database into the new
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.

This won't help though with your triggers and metadata, but at least you'd have your data accessible in case the repair fails

6. Repair the database

This is the method I've used for repairing the database once its in emergency mode.

USE mydatabase
GO

-- put database into single user mode
sp_dboption 'mydatabase', 'single_user', 'true'
GO

-- repair the database, please be sure you have an extra copy somewhere of
-- your two db files
DBCC CHECKDB ('mydatabase' repair_allow_data_loss)
GO

-- turn off emergency mode
USE master
sp_configure 'allow updates', 1
UPDATE sysdatabases SET status= 0 WHERE name = 'mydatabase'
GO

-- check space allocation consistency
DBCC CHECKALLOC ('mydatabase')

-- verify db integrity
DBCC CHECKDB ('mydatabase')

-- take the database out of single user mode
sp_dboption 'mydatabase', 'single_user', 'false'
GO

-- disallow updates to the system tables
USE master
GO
sp_configure 'allow updates', 0
GO


Optimizing UDF - Is Using DUAL Expensive

No. Oracle knows that DUAL is "in-memory" table. Your problem is somewhere else.

BTW: you can also use call fname(...); statement.


Perform TABLESAMPLE with WHERE clause in PostgreSQL

tablesample is an "attribute" of a table, not a query. So you need to write it write after the table name:

select *
from customers tablesample system (1)
where last_name = 'powell';

Note that the where clause will be applied after the table has been sampled. It will not return 1% of all customers that have the last name 'powell'. But instead it will apply the filter on the sampled table.


Deleting other files during maintenance plan

As per MS here

  The Maintenance Cleanup task removes files related to maintenance
  plans, including database backup files and reports created by
  maintenance plans.

  By using the Maintenance Cleanup task, a package can remove the backup
  files or maintenance plan reports on the specified server. The
  Maintenance Cleanup task includes an option to remove a specific file
  or remove a group of files in a folder. Optionally you can specify the
  extension of the files to delete.

I have not deleted any other extension files via maintenance plans and seems from above that a non sql file may not be deleted via Maint'ce plans:

What we use is a VB script and schedule it via SQL agent job:

Select type as :- Type ActiveX scipt when you create the step for below code in SQL agent job:

On Error Resume Next
    Dim fso, folder, files, sFolder, sFolderTarget
    Set fso = CreateObject("Scripting.FileSystemObject")

    'location of the  files
    sFolder = "B:\FolderA\ABC\"

    Set folder = fso.GetFolder(sFolder)
    Set files = folder.Files

    'used for writing to textfile - generate report on files deleted
    Const ForAppending = 8

    'you need to create a folder named "scripts" for ease of file management &
    'a file inside it named "LOG.txt" for delete activity logging
    Set objFile = fso.OpenTextFile(sFolder & "\Report\LOG.txt", ForAppending)

    objFile.Write "================================================================" & VBCRLF & VBCRLF
    objFile.Write "                     FILE REPORT                " & VBCRLF
    objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF
    objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF
    objFile.Write "================================================================" & VBCRLF

    'iterate thru each of the files in the respective folder
    For Each itemFiles In files
       'retrieve complete path of file for the DeleteFile method and to extract
            'file extension using the GetExtensionName method
       a=sFolder & itemFiles.Name

       'retrieve file extension
       b = fso.GetExtensionName(a)
           'check if the file extension is abc(whatever is the extension of you're file
           If uCase(b)="abc" Then

               'check if the files are older than 1days
               If DateDiff("d",itemFiles.DateCreated,Now()) >= 1 Then

                   'Delete any old files to cleanup folder
                   fso.DeleteFile a
                   objFile.WriteLine "FILE DELETED: " & a
               End If
           End If
    Next

    objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

    objFile.Close

    Set objFile = Nothing
    Set fso = Nothing
    Set folder = Nothing
    Set files = Nothing

Note* Above scripts work such that you need to create folder Report within you're base folder location and within that place a file LOG.txt which captures or logs the details of files deleted by the process. You can use this additional feature which we use to track, else modify the script as per needs!


Master Slave Error user specified as a definer ('dbadmin'@'%') does not exist

Perhaps the GRANTs on the Slave are not consistent with those on the Master?

On Master, do SHOW GRANTS FOR 'dbadmin'@'%';.  Then apply the grant(s) it gives you to the Slave.

Then do START SLAVE;