Does MySQL use its index metadata to optimize COUNT(*) queries?

It has to scan the whole index. Look at the output of:
SHOW INDEX FROM user_actions;

You will see the approximate cardinality of each index. The approximation is based on a few random dives into the index, not an exhaustively checked exact number. The approximation is sufficient for balancing.


Paging in SQL Server: Is an order by clause required? (currently, rows are being missed)

Using TOP(n) without ORDER BY is going to return you n rows in whatever way Sql Server cares. As per the documentation:

  If the query has no ORDER BY clause, the order of the rows is arbitrary.

In addition, there is no WITH TIES. Consider using it too, lest you might miss some rows too:

  WITH TIES  Specifies that additional rows be returned from the base
  result set with the same value in the ORDER BY columns appearing as
  the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified
  only in SELECT statements, and only if an ORDER BY clause is
  specified.

  The returned order of tying records is arbitrary. ORDER BY does not
  affect this rule.

Consider update to Sql Server 2012/2014. You could enjoy new features like automatic paging with OFFSET FETCH.


adrci not purge trace files and alert logs

ADRCI deletes the XML files, but not the plain text logs, which is a nuisance.

An easy but dirty solution would be overwriting the file with empty content, e.g:

$ cat /dev/null > listener.log

Deleting the file while the listener is running and logging is enabled may not lead to the expected result because of the file handling implementation on Linux/UNIX platforms.

Another solution is to temporarily disable logging, delete (or move or compress) the listener.log, then enabled logging again:

$ lsnrctl set log_status off
$ rm listener.log
$ lsnrctl set log_status on

Obviously, if you want, you can just stop the listener, take care of the listener log, then start the listener again.


You can use CAST to convert your VARCHAR value into DATE format.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

Sample execution: You can convert into DATE alone or DATETIME format:

DECLARE @TestDate AS VARCHAR (12) = '22-MAY-2015';
SELECT CAST(@TestDate AS DATE);

Output: 2015-05-22

DECLARE @TestDate1 AS VARCHAR (12) = '22-MAY-2015';
SELECT CAST(@TestDate1 AS DATETIME);

Output: 2015-05-22 00:00:00.000


Event 17058 - initerrlog: Could not open error log file (SQL Server)

I face the same problem after windows update on our server. Resolved with these steps.

rename or delete ERRORLOG file found in below directory

c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

start service

I think the file corrupted. When I change the name, it created a new file.


INSERT... ON DUPLICATE KEY UPDATE not working as I expect

Well this is the insert bit that you are using:

  INSERT INTO example (a, b, c) VALUES (1,2,3) ....

here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c.

The row in this case is updated when you provide a primary key (that you want to check is duplicate or not), along with the rest of the row data.
If you need to check and update according to record id, you have to provide the KEY as well, which in your case is id.

Try something like this:

INSERT INTO example (id, a, b, c) VALUES (1,1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);

Now if, the id is duplicate, the row will update.


Database planning... avoiding parallel fields

One row in a relational DB table holds one fact only. That user 12345 is connected to user 44444 and to user 55555 is two facts. Consequently it should be stored as two separate rows.

Columns capture meaning from the real world. Depending on the table, there can be an implicit semantics between the columns. A good example is a departmental reporting hierarchy. Typically this would be modelled:

Employees
   EmployeeID
   Name
   HiredDate
   ManagerID Foreign Key references Employees.EmployeeID

Although each manager is also an employee (and will have a row in the table for her data), for a given row it does not make sense to swap the values in the EmployeeID column and the ManagerID column. The two are not symmetric and there is meaning in the columns.

For your example, however, if A is a friend of B then B is a friend of A. The pairing is completely symmetric. Modelling this in RDBMS can be problematic as it breaks the implicit asymmetry mentioned earlier. There are techniques to do this in RDBMS. One way is to store each pair twice i.e. 12345/44444 and 44444/12345. Another is to query the table twice and swap the meanings assigned to columns between queries.

This can be tricky to understand and is likely to be quite challenging as a first journey into relational database design.


Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?

Try this query:

(
select * from (SELECT id, teacher_slots.teacher_id, date_from, date_to,  order_of_arrival
FROM teacher_slots  WHERE teacher_slots.date_from >= '2014-04-10 08:00:00'
    AND teacher_slots.order_of_arrival = 0
    AND teacher_slots.status = 0
    AND teacher_slots.city_id = 6015
    AND teacher_slots.subject_id = 1)
 teacher_slots
JOIN (
    SELECT DATE(MIN(date_from)) as closestDay, teacher_id
    FROM teacher_slots
    WHERE   date_from >= '2014-04-10 08:00:00' AND order_of_arrival = 0
            AND status = 0 AND city_id = 6015 AND subject_id = 1
    GROUP BY teacher_id
) a ON a.teacher_id = teacher_slots.teacher_id
AND DATE(teacher_slots.date_from) = closestDay

)

UNION ALL

(
SELECT id, teacher_id, date_from, date_to, order_of_arrival
FROM teacher_slots
WHERE order_of_arrival = 1 AND status = 0 AND city_id = 6015 AND subject_id = 1
    AND (
        (date_from <= '2014-04-10 08:00:00' AND  date_to >= '2014-04-10 08:00:00')
        OR (date_from >= '2014-04-10 08:00:00')
    )
GROUP BY teacher_id
)

ORDER BY date_from ASC;


ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

UNDO is the area that stores the change vectors required to restore the data to the state that it was in at the beginning of the transaction.

The only cures for this are to reduce the amount of data that you're changing (by changing less data or reducing the transaction size), or to increase the available undo size.

Reducing the transaction size, which you'd do by committing more frequently, is problematic because you still have to deal with rolling back transactions that you've committed, and because it would also promote snapshot too old errors when the database cannot reconstruct from undo the data required for consistency with the start of a query.

So, as commenters say, the answer really is to have the DBA's adjust the size of UNDO available. Strangely, they can be reluctant to do so, or at least to do so adequately. If you currently have 256MB of UNDO and you ask for 2GB, they might see this as some huge amount that might in some way be undesirable and offer you 1GB. Smile indulgently at them, because you already doubled the amount that you really wanted and because there is no such thing as oversizing your UNDO tablespace.


How to validate mongodb backup and restore

You should try this:

https://studio3t.com/

You can use "data synchronization" And if I remember correctly, you can validate data between 2 DB.

You can use it non-commercial use for free. Just download, and at the first start you can select between commercial and non-commercial use.

Also this: https://robomongo.org/


In Google App Engine, what is the most effective many-to-many join model?

I'm also working with the GAE datastore right now, you should check this article if you haven't already. If you have found something useful, please update your question.

EDIT:

I found this today, check it out.


Maintaining SQL Server 2008 R2 on Windows 10

Is there a way to get updates automatically?

SQL Server 2008 R2 is not supported on Windows 10. This means the SQL Server team will not be releasing any security updates or patches if they find any specific issues related to SQL Server 2008 R2 code. There is no way to get updates automatically as none would be released related to Windows 10.

Having said that, SQL Server 2008 R2 installation is not blocked when you try to install it on Windows 10. Of course, it is always better and advisable to install the latest Service Pack (Service Pack 3 is the final SP for 2008 R2).

If you are trying to install SQL Server 2008 R2 for testing purpose on Windows 10, you can go ahead; but since it's not supported it would be of no use to carry forward this idea.

  If not, do I have to install all the service packs in sequence, or can I install only the most recent?

Service Packs are cumulative, so you can directly install SQL Server 2008 R2 SP3 even on RTM bits. There are cumulative updates (CU) released after 2008 R2 SP3 as well, but my advice is don't install CU unless you face specific issues fixed in the CU.


Unable to Connect LocalDB Server in Navicat Premium

Is Navicat installed on the same computer as LocalDB? LocalDB doesn't allow for remote connections.

Assuming that Navicat is installed on the same computer: is Navicat a service or desktop application? LocalDB runs, by default, as a private user process that only your Login can see (no problem for a desktop application that you are running). If you want others (such as a service) to be able to access your LocalDB instance, you need to share it using the sqllocaldb.exe command line utility.

Also #1: (localhost)\MSSQLLocalDB is not a valid syntax. The reason for specifying (localdb)\InstanceName is that the (localdb)\ portion of it is a special syntax that the underlying connection library understands and hands-off to the LocalDB library (it is special because it can auto start up that instance of LocalDB simply by trying to connect to it using that syntax).

Also #2: SQL Server Native Client is a connection library (ODBC is another type of connection library), and it is being used by Navicat to connect to SQL Server. If Navicat is saying that you need to install SQL Server Native Client, then you need to download it from Microsoft. And yes, the last version of it was for SQL Server 2012. But if Navicat requires it, then you kinda need it.

Now, it appears that Navicat Premium is a desktop app so you likely don't need to worry about sharing the instance, unless there are other users logged onto your computer that you want to be able to access this instance. But for now, I will assume that it is only you using it. Keeping in mind the fact that LocalDB isn't a service and so not always running, and that it can be auto-started by attempting to connect to (localdb)\something, it should be understood that it takes several seconds for the LocalDB instance to start. The amount of time it takes is longer than the default connection timeout, at least in SQL Server Management Studio (SSMS). So, when initially connecting, it is expected to get the timeout error, so immediately try connecting again. If you wait too long (several minutes) to attempt connecting again, you will get the timeout again as the instance will auto-shutdown after several minutes of no connections.

So, again, assuming that both LocalDB and Navicat are running on the same computer AND by the same Windows Login, try connecting to (localdb)\MSSQLLocalDB. When you get the connection timeout error, immediately try connecting again. If it doesn't work, then something else is wrong.


RESTORE failing with OS error 32, file being used by another process

When using the backup files please make sure to use the logical filename. When using multiple backupsets within the same backup files, please make sure to check that the file being used is correct.

In this instance:

  File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file.

It looks as though you have the wrong logical file name or the wrong backup inside a backupset. I know you pointed this out, but it would make sense to interrogate the information in the backupset and double check.


Some queries not logging using RDS's SQL Server with C2 Auditing enabled

You have to query file located on D:\RDSDBDATA\DATA\audittraceXXX.trc, It is the first query you ran to get trace path


Array integer[]: how to get all distinct values in a table and count them?

You can use UNNEST.

select unnest(ports) as port, count(*) from foo group by port;

Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best avoided.


Deleting & Reclaiming space from InnoDB table

This is a good question. You have several solutions but your table is quite big so none  will be without pain :)

You have three solutions to "shrink" InnoDB tables:

1. OPTIMIZE TABLE

You can use OPTIMIZE TABLE as you mentionned it but you should care about the innodb_file_per_table variable :

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Let me explain:

The OPTIMIZE TABLE with InnoDB tables, locks the table, copies the data in a new clean table (that's why the result is smaller), drops the original table and renames the new table with the original name. That why you should make sure to have twice the space of the original table available on your disk (You'll probably need less, since the optimized table will be smaller, but it's better to be safe than sorry).

innodb_file_per_table = ON : In this mode, all tables have their own data file. The OPTIMIZE statement will then create a new data file with optimized space usage. When the operation is finished, MySQL will drop the original one and replace it with the optimized version (so at the end the 700GB -- probably less because it will be optimized -- of data generated during the operation will be released)

innodb_file_per_table = OFF: In this mode, all data is contained in one data file: ibdata. This mode has a big drawback since it cannot be optimized. So during the OPTIMIZE process, your new table will be created (near 700GB), but even after the drop and renaming operation (and the end of OPTIMIZE phase) your ibdata will not released the ~700GB, so you wanted to free some data, instead you have 700GB more, cool isn't it?

2. ALTER TABLE

You can also use an ALTER TABLE statement, the ALTER TABLE will work in the same way as OPTIMIZE TABLE. You can just use:

ALTER TABLE myTable ENGINE=InnoDB;

3. ALTER TABLE (ONLINE)

The problem of OPTIMIZE and ALTER TABLE is, that it locks the table during operation. You can use the Percona tool : pt-online-schema-change (from Percona Toolkit : link
). pt-online-schema... provide mechanisms to optimize the table, while keeping the original table available for read and writes. I use this tool in production for ALTER statements on big tables and it's pretty cool.

Note that any FOREIGN KEYs referencing your table might complicate things, since locks might lead to locks on other tables and so on. To check this, simply query:

mysql> SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME = "myTable";
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

Here is how I use pt-online-schema-change:

pt-online-schema-change --alter "ENGINE=InnoDB" D=myBase,t=myTable --user --ask-pass

Note that my note on innodb_file_per_table is true also for this solution.

4. mysqldump

The last solution is to recreate all databases from a dump. It takes forever, but it's extremely efficient. Note that this is the only solution to optimize your ibdata file, if innodb_file_per_table is OFF

Max.


SQL TDE and Certificate Handling Recommendations

I just have in the back of my mind, that if using one cert for all databases, that if the cert is 'compromised' then all databases that use it could be decrypted by an attacker?

I assume that (compromised means), when "someone can steal database files .mdf and .ldf or backups and TDE Certificate along with it's Private key and could know the Password that been used to encrypt Certificate backup".  Do you think that can happen?

  My question is, should I use one certificate for all TDE use, or one certificate for each database?

I don't think that having multiple certificate per each database can prevent it (above mentioned scenario). Because, there is possibility that someone who can steal one certificate can also steal multiple certificates.

  Answer (in my opinion)

However, in my opinion, database encryption alone cannot be full solution on security, you could also consider following:

Infra level: Building strong firewall around the database servers
OS level: Avoiding multiple users as builtin\administrators in database servers etc..

Also, the physical security (which is out of scope technical scope)

I believe, if you could implement TDE as per Standard TDE Architecture, would be fine and focus on surrounding security hiccups that may arise (as mentioned above).

...

Edit Post:

How strong your password (Source)?

To check the strength of your passwords and know whether they're inside the popular rainbow tables, you can convert your passwords to MD5 hashes on a MD5 hash generator, then decrypt your passwords by submitting these hashes to an online MD5 decryption service.

For instance, your password is 0123456789A, using the brute-force method, it may take a computer almost one year to crack your password, but if you decrypt it by submitting its MD5 hash C8E7279CD035B23BB9C0F1F954DFF5B3 to a MD5 decryption website, how long will it take to crack it? You can perform the test yourself.


SQLite - Counting referenced rows in one table using a different, related self-referencing table

Test

WITH RECURSIVE cte AS
(
SELECT id, guid, parent_id, id _id_, guid _guid_, parent_id _parent_id_
FROM metadata_items
UNION ALL
SELECT cte.id, cte.guid, cte.parent_id, src.id, src.guid, src.parent_id
FROM cte
JOIN metadata_items src ON cte._parent_id_ = src.id
)
SELECT cte.id, cte.guid, cte.parent_id, MAX(target_metadata_item_id) IS NOT NULL
FROM cte
LEFT JOIN media_subscriptions src ON cte._id_ = src.target_metadata_item_id
GROUP BY cte.id, cte.guid, cte.parent_id
ORDER BY cte.id;

fiddle

If you need the data for one separate record only then add proper WHERE.


Generate multiple running totals with GROUP BY day

I see a couple of issues. This should do it:

SELECT * -- ⑥
FROM   (  -- ①
   SELECT the_day::date
   FROM   generate_series(timestamp '2020-01-01', date_trunc('day', localtimestamp), interval '1 day') the_day
   ) d
LEFT   JOIN ( -- ②
   SELECT customer_id
        , created_at::date AS the_day -- ⑥
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'tsla')) OVER w AS tsla_running_amount -- ③
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'goog')) OVER w AS goog_running_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'  -- ④
   GROUP  BY customer_id, created_at::date  -- ⑤
   WINDOW w AS (PARTITION BY customer_id ORDER BY created_at::date) -- ③
   ) t USING (the_day) -- ⑥
ORDER  BY customer_id, the_day; -- ⑦

db<>fiddle here

① Generating all days of the current year in optimized fashion. See:

Generating time series between two dates in PostgreSQL

② The LEFT JOIN you had in mind. You may really want one row per combination of (customer_id, day). See:

Fill missing dates within groups

③ While you also GROUP BY customer_id the Window frame needs to lead with PARTITION BY customer_id.

Drop rows unbounded preceding. The default window frame should be just fine. The manual:

  The default framing option is RANGE UNBOUNDED PRECEDING, which is the
  same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Use the aggregate FILTER clause instead of the CASE expression. Shorter, faster, cleaner. See:

Return counts for multiple ranges in a single SELECT statement

Finally wrap the result of the aggregation in another sum() for the window function. See:

Get the distinct sum of a joined table column

The separate WINDOW clause avoids spelling out the same repeatedly. No effect on performance.

④ The WHERE is logically redundant, but assuming there are older rows, too, it excludes irrelevant rows early, improving performance.
  Asuming no or almost no future timestamps, so no upper bound.

⑤ You cannot have t.stock_ticker, t.amount in GROUP BY as you want one row per (customer_id, the_day).

⑥ Use the same column alias (the_day in my example) to allow the simple USING (the_day) in the JOIN clause, and SELECT * at the top level.

⑦ Not sure about the final sort order. This seems more useful to me. Adjust to your preference.


Versioning entities in db with draft option, select query

You can use the following, which relies on DISTINCT ON, and turns calculates version -1 as 9999999:

select DISTINCT ON (field_id) *
from field_mappings
where COALESCE(NULLIF(version, -1), 9999999) < 4
ORDER BY field_id, COALESCE(NULLIF(version, -1), 9999999) DESC

The query retrieves all the rows with unique fields (by field_id), prioritising the version you specify, or the draft one (which has its priority bumped to the top). If you want the draft or last version, then use < 10000000 instead of < 4.


When creating primary and foreign keys is the CONSTRAINT keyword optional?

For SQL Server, the Microsoft docs spell this out in excellent clarity (bold is my formatting):

  CONSTRAINT
  Is an optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.

Thus you do not need to specify CONSTRAINT xxx in your CREATE TABLE statement.

Note, however, that if you don't specify the constraint name, SQL Server will generate one for you if you include a constraint definition.  This example shows creating a PRIMARY KEY constraint, without naming it.

CREATE TABLE dbo.ConstraintTest
(
    ConstraintTest_ID int NOT NULL
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

SELECT o_parent.name
    , o.name
FROM sys.key_constraints kc
    INNER JOIN sys.objects o ON kc.object_id = o.object_id
    INNER JOIN sys.objects o_parent on kc.parent_object_id = o_parent.object_id
WHERE o_parent.name = 'ConstraintTest';

DROP TABLE dbo.ConstraintTest

The results of the SELECT statement above:

+----------------+--------------------------------+
|      name      |              name              |
+----------------+--------------------------------+
| ConstraintTest | PK__Constrai__471ED41490593601 |
+----------------+--------------------------------+

This leads to the generally accepted best-practice of naming all constraints, as in:

CREATE TABLE dbo.ConstraintTest
(
    ConstraintTest_ID int NOT NULL
        CONSTRAINT PK_ConstraintTest
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

As always, the "It Depends™" moment here is for temporary tables; we almost never specify the name of a constraint applied to a temp table since we want them to be unique, and allowing SQL Server to name them ensures they are in fact unique.  If you do specify the constraint name, then only a single copy of that temp table can exist at any given time; attempting to create more than one copy of the temp table will result in an error:

  Msg 2714, Level 16, State 5, Line 19
  There is already an object named 'PK_ConstraintTest' in the database.
  Msg 1750, Level 16, State 0, Line 19
  Could not create constraint. See previous errors.

You can easily add a UNIQUE constraint to an existing column without a name; as in:

ALTER TABLE dbo.Blah
ADD UNIQUE (SomeColumn);

In the example you've added to your question, the only difference is you are not specifying a constraint name, thereby allowing SQL Server to generate a name for the constraint.  Other than the name of the constraint, there is no difference between the two tables.


How to delete records without a primary key in a stardard way (i.e. not using ctid)

CAUTION : This can make you lose data, so consider using transaction block.

I think this is the more generic solution:

If you consider that the line you want to delete is:

select * from the_log limit 1

then if you have one column that has a unique value...

delete from the_log
where unique_the_log_column in
(select unique_log_column from the_log limit 1);

If you do not have a unique column, but you have two columns that combined generates a unique value:

delete from the_log
where (col1, col2) in (select col1, col2 from the_log limit 1);

Where any_the_log_column is a column with a unique value in this table.

This will probably delete the first line of the log table in any dbms - if data in column is not repeated, but remember that tables sometimes is not selected ordered in some databases so you must have a id in this table...

Consider creating a id column and increasing it by the numbers of the lines in the table, based on date or something if you don't want to get hurt in the future...


Some information can be found in SET Syntax and this bug.

  The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION, but this syntax is deprecated in favor of SET without OPTION.

How to fix it

If the problem happens when you are creating the dump (your case):
Upgrade you MySQL client tools to the same version of your MySQL server (in your case, 5.6)
If the problem happens when you are restoring the dump: At the beginning of the file, you have some SET OPTION statements; edit your dump, and change all instances of SET OPTION to SET SESSION like described here


Stored procedure to output date differences

Using a CASE expression, I think you could do something like this:

Declare @T table (ID int,creation_date date,process_date date,done_date date)

insert into @T(id,creation_date,process_date,done_date) values
(1,'2018-01-01','2018-01-01','2018-01-01')
,(1,'2018-01-01','2018-01-01','2018-01-01')
,(1,'2018-01-01','2018-01-03','2018-01-01')
,(1,'2018-01-01','2018-01-03','2018-01-02')
,(1,'2018-01-01','2018-01-03','2018-01-03')

SELECT ID
    ,sum(CASE
            WHEN process_date = creation_date
                THEN 1
            ELSE 0
            END) AS 'P=C'
    ,sum(CASE
            WHEN process_date = (dateadd(day, 1, creation_date))
                THEN 1
            ELSE 0
            END) AS 'P=C+1'
    ,sum(CASE
            WHEN process_date >= (dateadd(day, 2, creation_date))
                THEN 1
            ELSE 0
            END) AS 'P=C+2+'
    ,sum(CASE
            WHEN done_date = creation_date
                THEN 1
            ELSE 0
            END) AS 'D=C'
    ,sum(CASE
            WHEN done_date = (dateadd(day, 1, creation_date))
                THEN 1
            ELSE 0
            END) AS 'D=C+1'
    ,sum(CASE
            WHEN done_date >= (dateadd(day, 2, creation_date))
                THEN 1
            ELSE 0
            END) AS 'D=C+2+'
FROM @T
GROUP BY ID

| ID | P=C | P=C+1 | P=C+2+ | D=C | D=C+1 | D=C+2+ |
|----|-----|-------|--------|-----|-------|--------|
| 1  | 2   | 0     | 3      | 3   | 1     | 1      |


Which kind of table design best suitable for normalization in SQL server?

It can sometimes be tempting to go down the EAV route (or even diabolically tempting), but hic sunt leones! This delightful image encapsulates the difficulties with such an approach - just like in primary school, you can't mix apples and oranges! Take a small amount of time to peruse the links to see where EAV can lead.

If I were you, I would do something like the following.

CREATE TABLE vehicle
(
  vehicle_id INTEGER PRIMARY KEY,
  vechicle_make VARCHAR (50), -- another table?
  vehicle_name VARCHAR (50),
  vehicle_estimated_value INTEGER,
  vehicle_reserve INTEGER
);

CREATE TABLE gold
(
  lot_id INTEGER PRIMARY KEY, -- database generated
  dealer_id INTEGER, -- another table?
  lot_name VARCHAR (30), -- unique/ or unique with dealer
  gold_carat SMALLINT,
  gold_value INTEGER,
  gold_reserve INTEGER
);

CREATE TABLE portfolio
(
  portfolio_id INTEGER, -- database generated
  pf_asset_type INTEGER, -- FOREIGN KEY from asset_type table
  pf_asset_id,           -- FOREIGN KEY from asset (gold) table

  -- make the combo of pf_asset_type and _id UNIQUE

  pf_asset_value INTEGER,
  pf_asset_reserve INTEGER

  --  If I were you, I'd be using a TRIGGER to feed values
  --  from the different asset tables into these two monetary
  --  fields. Not sure of the exact capabilities of SQL Server,
  --  maybe some sort of non-updateable calculated field?
  --  The advantage this has is that you're not having a massive
  --  sum query for all your assets... Test and see what works for you.
);

This schema should work on any server (tested on PostgreSQL), but obviously, you'll have to include Microsoft SQL Server specifics for auto-incrementing keys and syntax for the PK and FK constraints.

Other asset types can be readily added as required!


Why does minimal logging appear to result in a smaller table size?

When I initially looked over your question, I thought that perhaps the second insert was benefitting from the first insert. Now that I look more closely, my initial though was that, due to the exclusive table lock, you were getting much denser pages than without the lock. This is why the rebuild on the heap created without the lock gets you down to almost identical table size.

To test my theory, I did things slightly differently. I generated two separate tables in advance, and then ran each of your queries so that I created two tables that both existed at the same time (this eliminates quasi-related things, like page reuse from the truncate you performed). This way I could compare them directly.

SELECT * INTO dbo.Heap_Tablock
FROM FactResellerSalesXL_PageCompressed WHERE 1 = 0;
ALTER TABLE dbo.Heap_Tablock REBUILD WITH (DATA_COMPRESSION = PAGE);

SELECT * INTO dbo.Heap_NoTablock
FROM dbo.FactResellerSalesXL_PageCompressed WHERE 1 = 0;
ALTER TABLE dbo.Heap_NoTablock REBUILD WITH (DATA_COMPRESSION = PAGE);

My first observation was that the population without tablock, even if run second (so benefitting from data already in cache), execution time was about 4X (only about 2X with clean BP each time). This is partly due to minimal logging, of course. But all of it? Not sure yet.

-- 3 seconds; 9 seconds after DROPCLEANBUFFERS
INSERT INTO dbo.Heap_Tablock  WITH (TABLOCK)
SELECT TOP 100000 * FROM dbo.FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

-- 13 seconds; 17 seconds after DROPCLEANBUFFERS
INSERT INTO dbo.Heap_NoTablock
SELECT TOP 100000 * FROM dbo.FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

Rather than look at sp_spaceused, which is rather high level and doesn't answer any questions, I looked at sys.dm_db_database_page_allocations instead. First, I just looked at the counts:

SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_Tablock'), 0, NULL, 'LIMITED');

-- 737

SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_NoTablock'), 0, NULL, 'LIMITED');

-- 1,569

So, without tablock, a lot more pages are associated with the table. But are they all allocated? Are the pages in the smaller table denser, contain ghost records, more likely to be compressed? Inquiring minds want to know!

SELECT is_allocated, is_page_compressed, has_ghost_records, COUNT(*),
  AVG(1.0*page_free_space_percent)
FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_Tablock'), 0, NULL, 'DETAILED')
WHERE page_type = 1
GROUP BY page_type, is_allocated, is_page_compressed, has_ghost_records;

-- is_allocated  is_page_compressed  has_ghost_records  #     %
-- ------------  ------------------  -----------------  ----  ----------
--            0                   2                  0     2   50.000000
--            1                   1                  0   721  100.000000

SELECT is_allocated, is_page_compressed, has_ghost_records, COUNT(*),
  AVG(1.0*page_free_space_percent)
FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_NoTablock'), 0, NULL, 'DETAILED')
WHERE page_type = 1
GROUP BY page_type, is_allocated, is_page_compressed, has_ghost_records;

-- is_allocated  is_page_compressed  has_ghost_records  #     %
-- ------------  ------------------  -----------------  ----  ----------
--            1                   0                  0  1562   99.967989
---- *****************************^  DANGER!

I think this is an additional, unadvertised benefit of the exclusive lock (and it also explains the faster execution time: less I/O).

Again, just a theory based on zero source code access and a reluctance to fire up a debugger, but it seems like populating a heap, without the exclusive lock, does not page compress the data initially - it waits for the rebuild.

This is somewhat confirmed in Data Compression: Strategy, Capacity Planning and Best Practices, under "Manipulating Compressed Data", in the heap section of the table:

  The newly inserted row is page-compressed:
   - if new row goes to an existing page with page compression
   - if the new row is inserted through BULK INSERT with TABLOCK
   - if the new row is inserted through INSERT INTO ... (TABLOCK) SELECT ... FROM
  Otherwise, the row is row-compressed.*

  * The resulting row-compressed pages can be page-compressed by running a heap rebuild with page compression.

That is not a verbatim confirmation of what we're seeing, but it seems to jive, since you are inserting rows into new pages that can't already have been page compressed, and without the TABLOCK hint, you don't meet any of the criteria for page compression at insert time. (And without deeply investigating the data, there may not be much benefit to row compression alone, which is common for certain data patterns.) So, you don't see the page compression savings until you rebuild.

Just another argument for clustered indexes, IMHO.


As a quick fix, you can just remove the entries (with respect to your database)from both the tnsnames.ora and listerner.ora.
In your scenario, remove this from tnsnames.ora

 PROJECT =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = PROJECT)
        )
      )

and this from listerner.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\Oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PROJECT)
      (ORACLE_HOME = F:\Oracle)
      (SID_NAME = PROJECT)
    )
  )

and then you can try to open up the database.
These entries would be created automatically.
And we hope the listener listens to us this time.


Strategy for regularly updating datasets from excel files

One method is to have two sets of tables (This1, This2, That1, That2, etc.), or a set of tables in separate databases/schemas. Either way, you now have SetA and SetB. Have a set of views that access one set of tables, say SetA. Perform the nightly imports to SetB. When the new data is ready to go, recreate the views, pointing them now to the SetB tables. The next night, use SetA to import.

The views will act as a software switch with an unnoticeable switch-over rate. And it should be dead simple to add this to existing Ruby script to coordinate all of it.


Simple PostgreSQL lookup table is inexplicably slow

Rather than "VARCHAR(40000)" why not use "TEXT"?
HASH index use is discouraged (see docs at http://www.postgresql.org/docs/current/static/indexes-types.html).
Have you run "ANALYZE" on your tables before running the query?
Giant IN lists can be performance killers.

How do the following queries perform?

SELECT e.entityId
FROM entities e
INNER JOIN triples t ON (t.object = e.entityId)
LIMIT 10000;

or

SELECT e.entityId
FROM entities e
WHERE EXISTS (SELECT 1 FROM triples t WHERE t.object = e.entityId LIMIT 10000);


Using MySQL Workbench 8 on RHEL7 gui - When I highlight text in the SQL editor it turns black

Turns out this is a bug resolved by upgrading to 8.0.16


MongoDB Update specific entries in an array

The short answer here is no, at least if you want to match and not just set specific elements (rather than looking to match multiple elements), something like this:

> db.students.findOne()
{
    "_id" : "abcd",
    "arrayItems" : [
        {
            "name" : "Peter",
            "age" : 23,
            "profession" : "Student"
        },
        {
            "name" : "Paul",
            "age" : 80,
            "profession" : "Student"
        },
        {
            "name" : "Mary",
            "age" : 45,
            "profession" : "CEO"
        }
    ]
}
> db.students.update(
    { "_id" : "abcd"},
    { $set: {"arrayItems.0.age": 27, "arrayItems.1.age" : 23 }}
  );
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.students.findOne()
{
    "_id" : "abcd",
    "arrayItems" : [
        {
            "name" : "Peter",
            "age" : 27,
            "profession" : "Student"
        },
        {
            "name" : "Paul",
            "age" : 23,
            "profession" : "Student"
        },
        {
            "name" : "Mary",
            "age" : 45,
            "profession" : "CEO"
        }
    ]
}

Unfortunately, unlike this simple test, with real usage there is no guarantee that the array you are updating has the structure you think it does, even if you fetch it immediately beforehand there is always the chance that someone updates between the read and your update.

In order for this to work you need SERVER-1243 to be implemented, and that is not currently planned for an imminent release. Here is the current statement on that lack of capability from MongoDB (as of Jun 2016) from that ticket:

  MongoDB appreciates the challenge of maintaining applications
  utilizing schemas with large arrays, especially with respect to
  updating many or all array elements, and we very much recognize the
  interest from the community around this ticket.

  Unfortunately, to implement such a major feature there are serious
  requirements:

  a specification for new language features (like update modifiers or    expressions), since we cannot break existing uses
  should be included with support to match all array elements, as well was those matching a query
  requests for support to update the last element(s) could be considered.
  must support all existing update modifiers (correctly, and in a non-backwards-breaking way) $rename, $set, $unset, $pull, $push,
  $bit...
  must work efficiently with all arrays, including those having thousands of elements
  cannot change current update semantics or behaviors which existing applications and deployments depend on (non-backwards-breaking).

  In summary, adding this as a new feature, or improvement, is not
  trivial. It will require resources which are currently working on
  other projects, and in short is a matter of prioritization over other
  parts of the whole server.

As you can infer from the age of the ticket and the lengthy discussion, this has come up many times and there are plenty of workarounds in the ticket and elsewhere but (as yet) there is no built-in method to achieve this.


How to make ON CONFLICT work for compound foreign key columns?

I think you may be misunderstanding what a CONFLICT is.  A CONFLICT is a violation of uniqueness, basically the row that is being added should not be added because another row with the same values already exists.

In your example of insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) values (3, 'a3', 'b3') on conflict do nothing;, the ON CONFLICT condition will never be reached because you have no primary key or unique constraint on my_table:

edb=# alter table my_table add primary key (ftable_id_a,ftable_id_b);
ALTER TABLE
edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 1
edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 0
edb=# select * from my_table ;
 id | ftable_id_a | ftable_id_b
----+-------------+-------------
  3 | a3          | b3
(1 row)

As you can see in the example above, my second insertion into my_table did nothing because there would have been a primary key violation.  If I omit the ON CONFLICT clause, I get:

edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (ftable_id_a, ftable_id_b)=(a3, b3) already exists.

The message that you mentioned in you original post (ERROR: insert or update on table "my_table" violates foreign key constraint "my_table_a_b_fk") pertains to a Foreign Key violation (not a primary key/uniqueness violation).  This violation occurs when there should be a row in foreign_table with id_a=a3 and id_b=b3, but there isn't.  The database expects this row to exist because you have defined that my_table references foreign_table (in other words, my_table depends on foreign_table).  Because the row doesn't exist in foreign_table, your insertion into my_table fails.  First, insert into foreign_table, and then you can insert into my_table:

edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
ERROR:  insert or update on table "my_table" violates foreign key constraint "my_table_a_b_fk"
DETAIL:  Key (ftable_id_a, ftable_id_b)=(a3, b3) is not present in table "foreign_table".
edb=# insert into foreign_table values ('a3','b3',1);
INSERT 0 1
edb=# select * from foreign_table ;
 id_a | id_b | id
------+------+----
 a3   | b3   |  1
(1 row)
edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 1
edb=# select * from my_table ;
 id | ftable_id_a | ftable_id_b
----+-------------+-------------
  3 | a3          | b3
(1 row)


Use Create Trigger to create New table using calculated values from common groupid column values

We can use MySQL CREATE TABLE AS statement to create a TABLE_B from existing TABLE_A by copying the existing table's columns

CREATE TABLE TABLE_B AS
SELECT  id, name, SUM(cost) AS 'cost', organization
     FROM TABLE_A
     GROUP BY name, organization;

In MySQL, we cannot have after INSERT or UPDATE description together, like in Oracle unfortunately. So you have to create two TRIGGERs

delimiter //
CREATE TRIGGER update_tableb
 AFTER INSERT
  ON TABLE_A FOR EACH ROW
     BEGIN

   -- variable declarations

   -- trigger code

    END;//
delimiter ;


SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?

CREATE VIEW encapsulates creation and alteration.  Here's a sample script using a login named test mapped to the current database:

grant create table to test
grant create view to test

execute as user='test';

--succeed
create table t ( Id int );
create view v as select * from t;

revert;

deny create view to test;

execute as user='test';

--fail:  permissions error
alter view v as select * from t;

revert;

In the first step we grant create table & view to test.  Executing as test, we're able to create a table and a view.  Then, we revert back to the real account and deny create view for the test user.  When we try to alter the existing view, we get a permissions error:  Cannot alter the view 'v', because it does not exist or you do not have permission.


Performance between Delete In and For Loop

If the task can be performed using native SQL then always go for it. Because SQL and PLSQl are two different languages and if you call SQL from PLSQL block then context switch(exchange of processing control between SQL and PLSQL) occurs.

So I would prefer the native SQL.

Now, you have couple of options such as-

If you are going to delete large percentage of rows from the table, keeping 1 million rows is better that deleting 2 millions rows from table having 3 millions rows,

Then create <new table> as select * from <old table> where <condition>, you can also use parallel here,  drop old table, rename  to , create necessary indexes in parallel.

Down side of this method is that other session can't use this table during the operation.

Another method is to partition the table then drop the unnecessary partitions.

Note: If you still want to do it with PLSQl(for concurrency) then instead of deleting one by one, you can use BULK DELETE.


Storing the last updated date of a row in a different table

My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.

Your method will work, but that seems almost more likely to break something.. In that case, you're talking about doubling your write load. In the other case you're talking about adding a single column time stamp with time zone DEFAULT now() that won't otherwise be touched or seen,

unless someone wrote code that relies on *
and,

that code depends on the amount of columns returns.
or, reorders the columns (ie., sorts) and depends on the sorted order.

I would say adding a column is an extremely safe operation and the right thing to do.

See also

Create a trigger on all the last_modified columns in PostgreSQL

Trying a workaround

I tried creating a TRIGGER to add the column BEFORE INSERT on mysql, that also failed. So the only method you have is

another table if you're trying to insure against spurious use of INSERT without explicit column names.
replication

Example code (doesn't work)

CREATE TABLE f (a int, b int);

delimiter //
CREATE TRIGGER use_postgresql_insert
BEFORE INSERT ON f
FOR EACH ROW
BEGIN
  SET NEW.b = 1;
END;
//

INSERT INTO f VALUES (5);
ERROR 1136 (21S01): Column count doesn't match value count at row 1


One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH


PostgreSQL : FATAL: could not open log file "/path/to/log/filesystem/postgresql-Sun.log": No such file or directory

User, which runs postgresql, often postgres, must be able to traverse to this /path/to/log/filesystem/ (often /var/log/postgresql), specified by log_directory config option and be able to write files there. If this path does not exist, you will get No such file or directory error.
You can try switching to this user (e.g. su postgres) and check cd /path/to/log/filesystem (whatever this path really is) and if that throws error, then create the directories (e.g. mkdir -p /path/to/your/log_directory).
There's also a possibility that service manager changes mount points for the service, like systemd is capable of.


Find all IP's or users who access a specific SQL Server

You can look at the currently running processes in SSMS in Activity Monitor.

You can also get it using sys.dm_exec_sessions as well as sys.dm_exec_connections and sys.dm_exec_requests.

Query:

SELECT DB_NAME(database_id) as [DB]
    , login_name
    , nt_domain
    , nt_user_name
    , status
    , host_name
    , program_name
    , COUNT(*) AS [Connections]
FROM sys.dm_exec_sessions
WHERE database_id > 0 -- OR 4 for user DBs
GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;

Hostname, process name or domain user names often give more information than just an IP address. Other columns can be added such as: login_time, last_successful_logon

IP and other information can be retrieved with a JOIN to sys.dm_exec_connections on session_id: client_net_address, local_net_address, connect_time, ports, ...

sys.dm_exec_requests can also give useful information: command type, sql_handle, ...

This query is just a sample. You must JOIN these 3 views together and output/store relevant information from either of them.

Data from these view disapear when SQL Server is restarted. Therefore if the application is not regularly connected it may be a good idea to store them on a regular basis using a job or a script (Powershell).


MySQL cannot create file (permission denied). even my folder permission is 1777 (Ubuntu)

Ubuntu ships with AppArmor which may be causing your issues. You might want to look at some of the discussion at https://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile


inconsistent type after querying search_condition column in all_constraints table Oracle

if you have xmldb installed (not sure if this will work for 9i) you can use something like this,

SELECT table_name, constraint_name, sc FROM   (SELECT  constraint_name, table_name, search_condition ,to_char(extractvalue(dbms_xmlgen.getxmltype ('select search_condition from user_constraints x' ||' where x.constraint_name  = ''||t.constraint_name||''''),'//text()')) sc FROM   user_constraints t) where sc like '%IS NOT NULL'


Use array_agg or string_agg

select
    k.name,
    array_agg(pr.position order by pr.position) as pr_position,
    array_agg(pcr.position order by pcr.position) as pcr_position
from
    project_keywords pk
    inner join
    keywords k on pk.keyword_id = k.id
    inner join
    project_reports pr using (project_id,  keyword_id)
    inner join
    project_competitor_reports pcr using (project_id, keyword_id)
group by k.name
;


How do I restore my Heroku pg_dump on DigitalOcean?

It looks like you have already restored this data once before, so the objects already exist.  And trying to load it into the database named 'postgres' is a dubious practice anyway.

You should create a new database with a different name and load the data into that new database instead of into 'postgres'.  Or maybe you meant to use the -C option, in which case it will create and use the database which is named inside the dump file as the database to load the data into (in which case the database named in -d is only used to connect to in order to issue the CREATE DATABASE command).

If you really want to load this data to the database named 'postgres', you could use the -c option (without -C), which will drop the existing objects before recreating them.


DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x92\x96'

When I specified CHARSET='UTF8MB4'in my connection parameters, it worked.


Okay, I've just gave another shot with IF clause and figured out the simplest query that I can think of. It's now giving the desired result, here's my query:

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
WHERE IF('$variable' != '', table2.column = '$variable', 1 = 1)

Successfully, I placed that IF() clause in the right place. If you guys have thoughts about this code or have a very better one than this, kindly share it :)

  [ADDITIONAL]

  This is an answer from @ypercubeᵀᴹ and I love it!

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
WHERE table2.column = '$variable' OR '$variable' = ''


Your example (idea II) looks like it shows how to store the response to the forms. Would you store the form descriptions (created by your admin) in the same fasion, as it:

id | name | value
-----------------------------------------------------------
1  | form1|{"question1":"description", "question2":"score"}

If you go this route, it means you have to store the full description of a form as a very long text string. You won't be able to reuse questions between forms. You won't be able to (easily) query to find which form had a specific question. It will be possible to have more interesting types of form questions such as multiple-choice, but it won't be pretty (and you won't be able to re-use option-sets).

I'm not sure why you think you need an EAV solution. A very simple schema could look like this:

form
----
  id
  name

question
--------
  id
  form_id
  question_text

question_answer
---------------
  id
  user_id
  question_id
  answer_value

Data:

form
----
id | name
-----------
1  | form1

question
--------
id | form_id | querstion_text
-----------------------------
1  | 1       | description
2  | 1       | score

answer
------
id | question_id | answer_text | user_id
----------------------------------------
1  | 1           | test123     | 1
1  | 2           | 90          | 1

Would this not work?


in MySQL what is the query to show these student's that each marks less than 50?

SELECT Name,
       MAX(CASE WHEN Subject = 'Word' then Marks END) Word,
       MAX(CASE WHEN Subject = 'Excel' then Marks END) Excel,
       MAX(CASE WHEN Subject = 'Access' then Marks END) Access
FROM Datatable
GROUP BY Name
    -- If ALL marks must be below 50
-- HAVING GREATEST(Word, Excel, Access) < 50;
    -- If at least one mark must be below 50
HAVING LEAST(Word, Excel, Access) < 50;


At which point in a log backup does SQL Server truncate the log file?

Technically, the virtual log files (VLFs) are only attempted to be marked as inactive (can be reused) when they have been successfully backed up and are no longer needed by internal (and sometimes external) processes - such as replication or availability groups.

  What happens is that a log backup begins on schedule, but on some days the connection with the backup server is lost during the backup.

This shouldn't cause any issues with SQL Server. SQL Server will see that the connection was broken and kill the session plus whatever was executing. This means that backup wasn't successful so the next log backup should start at the same place because it hasn't yet finished successfully.

  The job sits in limbo and when it restarts with the same job id there is a broken chain detected and it begins a full backup of affected databases.

Sounds like the application thinks it ran a backup successfully even though it didn't and "detects" there is a problem... but there isn't. It really sounds like the application logic in the backup program is either flawed or running into a config issue... or it might be their standard logic (by design).

Now if it takes a full backup and walks away... that's not going to affect the log reuse.

  But on a certain day of the week, at a certain time of day half of the jobs hang because the backup server goes down for a few minutes and when they restart again they begin a full backup on some databases. And from what I can tell it looks for the LSN, runs a backup, job fails, restarts looking for the same LSN.

Sounds like you have other infrastructure issues that may be contributing. Assuming they are not, though, it sounds like the backup application is confused. If the job fails to backup the log successfully it should start at the same place because it hasn't yet been successfully backed up. If the backup application chooses to see that as an issue and take a full backup to reset itself (the backup application) internally, that's on the application vendor to fix/decide/working as intended/whatever after you tell them about it.

However, since this only happens when the backup application server goes down... you may want to also have a stern talking to your infrastructure team and get that issue situated - or at the very least, if it's downtime because of patching or something, that all jobs be held until the patching (or whatever) is completed.


Merge MySQL tables without data repetition

It is not clear to me, if you want to keep records, which are duplicated, or you want only those records, which are in only one of the tables.

You can try:

-- create tables
create table tbl1
(
    id INT,
    name VARCHAR(50),
    city VARCHAR(25)
);

create table tbl2 like tbl1;

-- insert data
insert into tbl1 values (1,'adam', 'eden'), (2, 'eva', 'eden');
insert into tbl2 values (3,'adam', 'eden'), (4, 'pedro', 'alexandria');

This will give you records with unique name column:

select * from (select * from tbl1 union select * from tbl2) as a group by name;
+------+-------+------------+
| id   | name  | city       |
+------+-------+------------+
|    1 | adam  | eden       |
|    2 | eva   | eden       |
|    4 | pedro | alexandria |
+------+-------+------------+
3 rows in set (0.00 sec)

Note: this will keep id from tbl1, if you want to keep id from tbl2, just switch order of tables in sql statement

And this will give you only those records, which are in one, or other table, but not in both

select * from (select * from tbl1 union select * from tbl2) as a group by name having count(*) = 1;
+------+-------+------------+
| id   | name  | city       |
+------+-------+------------+
|    2 | eva   | eden       |
|    4 | pedro | alexandria |
+------+-------+------------+
2 rows in set (0.00 sec)

Is that what you wanted?


Should a TeamMembers table have a relationship with the Student Table or the Enrolment Table?

I would have assumed that one team can have many users and one user can be part of many teams thus creating a new junction table, Users_Team. From there I would have related users to Enrollment or user_team to enrollments depending on business logic. Am I right in assuming that enrollment will contain recrods such as Maths, Science, Art etc...?

Does it make sense for a user to be enrolled in maths as a individual, and also as a team?

From my understanding, I would drop that TeamMembers table.


Calling stored Procedure from one db to give me a result in another db

SUGGESTION #1

You need to explicitly set the database before calling the Stored Procedure

USE targetDB
CALL Shared.AccountReqs(...);

SUGGESTION #2 (OPTIONAL)

Since you have all these UNION clauses against the same table, try combining all the WHERE clauses under a single SELECT. I see two SELECTs in your original question. Therefore, the Stored Procedure should look more like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AccountReqs`(
  IN IsNewAccountStart VARCHAR (255),
  In AccountName varchar (255),
  IN AccountNumber varchar(255),
  In PaymentTerm varchar(255),
  IN Batch varchar (255),
  In AdditionalEmailAddresses varchar (500),
  In WorkEmail varchar(500),
  In PersonalEmail varchar (500)
)
BEGIN
    SELECT * FROM ACCOUNTS
    WHERE (IsNewAccountStart <>'TRUE' AND IsNewAccountStart <>' ')
    OR    (AccountName=' ') OR (AccountNumber=' ')
    OR    (PaymentTerm NOT IN ('Net 30','Net 45','Net 60','Due Upon Receipt'))
    OR    Batch<> 'Batch1';

    SELECT * FROM ACCOUNTS
    where AdditionalEmailAddresses<>' '
    Or  WorkEmail<> ' '
    Or  PersonalEmail<> ' ';
END


What is the maximum number of users in MS SQL

There is no defined max number of logins and users in SQL Server. Although, based on how your application is configured you can hit the max number of user connections you can have to a given database (32,767) rather quickly.


Get data excluding time intervals in another table

This is a classic anti-join scenario. You want rows from one table excluding those matching another table.

In English, you could put the condition like this:

  Select every row from Message for which there is no matching row in BlockUser.

In SQL, one way of expressing the above is like this:

SELECT
  *
FROM
  dbo.Message AS m
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      dbo.BlockUser AS bu
    WHERE
      m.DateCreated >= bu.BlockStartDate
      AND m.DateCreated < bu.BlockEndDate
  )
;


how to install a second server of mariadb

You will probably run into more conflicts than just what is listed in the question, e.g. TCP port, datadir, /etc/my.cnf, as well as maybe pid and socket. I think these configuration issues can be overcome by using different settings (MariaDB-specific sections in the .cnf files).

I'm not sure whether installing MariaDB from packages alongside a standard installation of MySQL (from packages) is feasible. rpm has an option --relocate OLDPATH=NEWPATH and --prefix NEWPATH which allows you to set a non-standard install path for rpms marked as relocatable. Not all rpms are reloatable.

However, see Installing MariaDB alongside MySQL (mariadb.com) which talks about installing the compiled binary distribution of MariaDB 5.5 from source code alongside an existing installation of MySQL. This is also the approach used by tools like dbdeployer.

See also Running Multiple MySQL Instances on One Machine (mysql.com) which talks about installing multiple MySQL instances on the same server. This may or may not be useful.

One relatively low-effort solution could be to install MariaDB in a Docker container. See e.g. MariaDB and Docker use cases, Part 1:

  Docker use case #1: Multi Tenancy

  A common use case for MariaDB and Docker is running several instances of MariaDB on the same physical hosts.


Merge replication with PDAs AND P2P replication between servers?

Since the intention is to have the same data at multiple locations, Peer-to-Peer replication would be the most appropriate. Peer-to-Peer is the only out-of-the-box technology that SQL Server provides to handle writes at multiple nodes, so it makes sense to use it. (For completeness of this answer, Peer-to-Peer does require Enterprise edition, while Merge replication does not.)

Once you get the security of the topology ironed out (I found this MSDN page very handy), it's actually not that difficult to set up. If you're considering Merge replication, chances are your schema is already is decent shape, so conforming to the requirements and restrictions for Peer-to-Peer shouldn't be a problem.

While I don't know your exact data requirements, it seems like writes would be nicely partitioned (i.e., data entered on one side probably never needs to be edited in multiple locations after that), meaning update conflicts would be extremely unlikely.


Is there such thing as Custom Data Types?

Does MySQL have any support for custom data types?

Simple answer: no

  Is there anything remotely like this on any database engine? I mostly
  use MySQL, but I am curious if this has ever been implemented, short
  of making the application call a function like the INET_ATON function.

Oracle has CREATE TYPE which is analogous to some degree to a OO class, including features like member functions and inheritance

Postgres has CREATE TYPE which is a bit less like OO classes (no member functions or inheritance) but are incredibly flexible and useful, even allowing you to create new base types. There is also CREATE DOMAIN which allows a form of inheritance or sub-typing and basically extends a base type with some constraints. Postgres also has quite a few interesting base types by default, eg inet and geometric types. In Postgres one can write an extension in C for a custom datatype, such as in this example here with base36 data type.

SQL Server has CREATE TYPE which allows you to create a custom data type based on an existing system data type.  For example I could create a type called SSN which is basically defined as VARCHAR(11) but this way I don't have to remember how big a field it is.


How can I find table name for each row result?

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation:

  Every table has several system columns that are implicitly defined by the system.

tableoid is one of them. The solution I provided under the questions you refer to is also suggested in the manual here. And it works. I have been using it for years in various Postgres versions.

Either you are not using Postgres, or template is not a table. Is it a VIEW maybe? If you define a VIEW like:

CREATE VIEW template AS
SELECT * FROM some_table;

Then system columns are not included by default.

What do you get for

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

Is relkind 'r', 'v' or something else?
Or do you get multiple rows? Then consider this:

Why does a PostgreSQL SELECT query return different results when a schema name is specified?


Use directory mode and run several parallel jobs:
pg_dump -F d -j 4 -h localhost -n test_schema -f /data1/backup/test_schema mydb

That will automatically compress the backup.


How to use GROUP_CONCAT for a column and COUNT(*)?

This would helpful, Give a try once
SELECT GROUP_CONCAT(ArticleID)
   ,COUNT(1) AS CNT
FROM (SELECT Tag
         ,ArticleID
         ,COUNT(1) AS CNT
  FROM TagMap Tag IN (...)
  GROUP BY Tag
           ,ArticleID) tmp
  GROUP BY Tag
  ORDER BY cnt


VarChar to NVarChar causing truncate error

Well this won't happen unless

a trigger is firing on the target table, and the error happens there
the INSERT does some processing that adds enough data to make it too wide

Also, what happens with this please? This can not fail unless there is some processing.

INSERT INTO table (col1, col2)
SELECT LEFT(column1, 200)
      ,column2
  FROM tmpTable


SQL Server 2008 R2 service packs are cumulative and can be used to
  upgrade all releases of SQL Server 2008 R2 to Service Pack 3.

From http://www.microsoft.com/en-US/download/details.aspx?id=44271

Given the documentation, you should be able to go directly from SP0 to SP3.


SQL Server : GRANT REFERENCES Fails With Msg 15151 as Windows User with sysadmin role and also as 'sa'

This is not an ASYMMETRIC KEY, it is a SYMMETRIC KEY.
Change

GRANT REFERENCES ON ASYMMETRIC KEY

to

GRANT REFERENCES ON SYMMETRIC KEY


Postgres 10.3 : table grows in size after delete-insert

VACUUM will only shrink the data file if the free space happens to be at the end. What you probably want is a VACUUM FULL. However be aware of a couple of caveats:

It will lock the table while it runs (which may be a while)
It will require equivalent temporary space to the table

But when it is finished, the final space used will be just enough for the actual data you have.


PHP MySQLi Lock Tables Query Sometimes Does Not Complete

Gag!  LOCK TABLES is essentially unnecessary if you are using ENGINE=InnoDB.  If you are still using MyISAM, convert!

After converting, you will need to understand how to put things in "transactions" (BEGIN...COMMIT) and use SELECT ... FOR UPDATE.


SQL 2005: logical consistency-based I/O error: incorrect checksum

Yes, although the problem may be memory. I suggest you upgrade SQL Server 2005 to the latest release, and if that is check the CU upgrade level at SQLRELEASESERVICES (Type it into google - it ia blog by the team releasing monthly (!) updates). Maybe you also run into an issue on the sfotware side.


How to perform a left join of list of dates with an existing table of records smartly

You must CROSS JOIN the Users table with your Calendar query.

It will give you this result with one of each date for each user:

selected_date | firstname
2016-03-08    | Mat
2016-03-09    | Mat
2016-03-10    | Mat
2016-03-11    | Mat
2016-03-08    | Tom
2016-03-09    | Tom
2016-03-10    | Tom
2016-03-11    | Tom

This can then be LEFT JOIN with the Performance table. (SQL Fiddle)

Query:

SELECT u.id, u.firstname, cal.selected_date, p.val
FROM Users u
CROSS JOIN (
    SELECT selected_date FROM (
        SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
        from
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    ) v
    WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11'
) cal
LEFT JOIN performance p
    ON u.id = p.employeeId AND cal.selected_date = p.date;

Output:

 id | firstname | selected_date |    val
  1 |       Mat |    2016-03-08 |    100
  2 |       Tom |    2016-03-08 |     90
  1 |       Mat |    2016-03-09 |     80
  2 |       Tom |    2016-03-09 |    100
  1 |       Mat |    2016-03-10 | (null)
  2 |       Tom |    2016-03-10 | (null)
  1 |       Mat |    2016-03-11 | (null)
  2 |       Tom |    2016-03-11 | (null)


Altering the existing partition which is partitioned by date range in SQL Server 2012

Yes, but I wouldn't recommend it. Splitting partitions with lots of data takes an unpredictable amount of time and typically eats a ton of transaction log. I'd insert the data into a new table with the correct partitioning. It's more work, but you are in control of the move. Best practice (hindsight's 20/20) is to always have an empty left and right partition for these types of scenarios.

Best Practices

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

https://msdn.microsoft.com/en-us/library/ms186307.aspx


Write a query that pulls the student who registered the last?

The second query does not necessarily do this, depending on the data type of the EnrolmentDate column and how granular the data is. If this column does not contain a time component, the query will return all students registered on the last day a student registered, which does not satisfy the question. If there is a time component, it's possible (but much less likely) that there will be multiple rows returned.

(Edit: Alex Kuznetsov correctly pointed out in the comments that the first query doesn't necessarily return the last enrolled student either when there are ties. It is, however, guaranteed to return a record in that event, instead of all records, which is normally satisfactory. I think my point was more that comparing the two queries is comparing apples to oranges, so to speak.)

In any event, if we assume all enrollment dates/times are unique, from what's given, the answer to the question isn't necessarily clear cut either. You would need to qualify for me what you mean by more efficient.

The first query will only scan once, but could potentially incur an expensive sort (you didn't say which indexes exist on the table, so I assume none). The latter query will do a scan to find the maximum, then do another scan to find all matching rows, which would possibly use less CPU, but more logical I/Os. It's entirely possible the second query would be less expensive overall (again, with no indexes available).

Having said all that, if I was to start doing performance tuning on this business operation, I would most certainly start with query (a).


Duplicate entries - condition on second table with join

Good day.

Using the WITH syntax your subqueries may be more readable.

WITH PLATE_MOVEMENT AS (
    SELECT P.PLATE_ID, P.PLATE_NUMBER, P.PLATE_COUNTRY,
           M.MOV_ENTRYDATE, M.MOV_EXITDATE
    FROM PRUEBA.PLATE P INNER JOIN PRUEBA.MOVEMENT M
         ON (P.PLATE_ID = M.PLATE_ID)
)
SELECT A.PLATE_ID, A.PLATE_NUMBER, A.PLATE_COUNTRY,
       A.MOV_ENTRYDATE, A.MOV_EXITDATE
FROM PLATE_MOVEMENT A INNER JOIN PLATE_MOVEMENT B
     ON  A.PLATE_NUMBER = B.PLATE_NUMBER
     AND A.PLATE_COUNTRY = B.PLATE_COUNTRY
     AND (A.MOV_EXITDATE = B.MOV_ENTRYDATE OR B.MOV_EXITDATE = A.MOV_ENTRYDATE)
-- optionally an order by
ORDER BY A.PLATE_COUNTRY, A.PLATE_NUMBER, A.MOV_ENTRY_DATE

With result

PLATE_ID    PLATE_NUMBER PLATE_COUNTRY MOV_ENTRYDATE MOV_EXITDATE
----------- ------------ ------------- ------------- ------------
          1 AS25         UK            03/01/2019    03/05/2019
          2 AS25         UK            03/05/2019    01/01/0001

  2 record(s) selected.

Quite obscure will be the condition

A.MOV_EXITDATE = B.MOV_ENTRYDATE OR B.MOV_EXITDATE = A.MOV_ENTRYDATE

If omitted the second part, the last row in the chained collection of historic rows will be omitted.  If omitted the first part, the first row in the chained collection of historic rows will be missing.


In PostgreSQL, how do I make it so that either column A or column B must be non-null?

You need a table-level check constraint:
alter table <name>
  add constraint either_email
    check (email is not null or p2pmail is not null);

If you're only allowed to enter one, but not both:
alter table <name>
  add constraint either_email
    check (email is null <> p2pmail is null);

The second form is possibly a little confusing at first glance: what it does is compare both columns' null status -- they aren't allowed to be the same.
The constraint can also be created simultaneously with the table:
create table <name> (
  ... columns ...
  constraint either_email check (email is not null or p2pmail is not null)
);


look like You try to run mysql(?d) from inside mysql console
this command must be run from OS command line:

c:\Program Files\MySQL\bin>mysql -u root -p sample < d:/sample.sql

all from Windows command line


How can I disable swappiness just for Mariadb and not for other applications on the same EC2 Instance?

Plan A (if possible):  Use "huge pages" for the buffer_pool.  They can't be swapped, thereby helping keep from swapping MariaDB.
Plan B:  Use swappiness = 1 -- This, in theory, discourages the OS from swapping any process.  It will still swap if it is absolutely necessary.  At that point, perhaps it is better to slow down some process(es) rather than crashing?


When it is looking at rounding it only looks at the next decimal place to see which direction to round. So in your case it is seeing 4.524 which does not round up to 4.53. I have never heard of a system that rounds up all decimal values until it gets to your limit.

Think of it this way if 2 decimal points is what is important (think a lot of currencies) would you really want the 4th or later decimal value to impact what is calculated for a payment amount?


What does Oracle use for handling a client connection (process or thread)?

Oracle up to 11g used a thread based model on Windows and a process based model on Linux.

Quote from the manual:

  A process is a mechanism in an operating system that can run a series of steps. The mechanism depends on the operating system. For example, on Linux an Oracle background process is a Linux process. On Windows, an Oracle background process is a thread of execution within a process

You can optionally enable so called "shared processes" where one process handles several client connections. Think of that as a kind of in-database connection pooling. On Windows this would mean a single thread handles multiple connections, on Linux this would mean a single process handles multiple connections.

Starting with 12c you can optionally enable the thread-based model for a Linux installation.

Quote from the manual:

  In releases earlier than Oracle Database 12c, Oracle processes did not run as threads on UNIX and Linux systems. Starting in Oracle Database 12c, the multithreaded Oracle Database model enables Oracle processes to execute as operating system threads in separate address spaces


Why am I getting table does not exist error?

The table isn't missing. The message "table "automobiles" does not exist, skipping" stems from the DROP TABLE IF EXISTS statement and just informs you that there wasn't such a table to be dropped.

If you look at the message it starts with NOTICE: which tells you that it's not an error. Error messages start with ERROR: (as you can see later on in your script)

Your table was successfully created.

However, your INSERTs are failing as you are providing the values for the date column in the wrong format. The most robust way to supply a date constant is to use the ANSI SQL syntax for dates: DATE '2018-03-12', so your inserts should look like this:

INSERT INTO automobiles(id, make, model, year, owner, msrp, purchase_date)
VALUES
(   1,
    'Ferarri',
    'F40',
    '1987',
    '',
    '1690000',
    DATE '2019-03-12'),
(   2,
    'Tundra',
    '4X2',
    '2019',
    '',
    '39625',
    DATE '2019-03-12'),
...;

If you don't like the ISO formatted date literals, you can also use the to_date() function instead: to_date('3/12/2019', 'mm/dd/yyyy').

The message

date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.

suggests to change the DateStyle setting in order to allow the implicit conversion of a string value like '3/12/2019' to a proper date.

I strongly discourage the use of the DateStyle parameter as that makes your SQL statements dependent on the environment and they might still fail when run on a different computer.


What will be the order of statement execution in this use case?

It depends on the transaction_isolation.

It depends on whether the SELECT includes FOR UPDATE or IN SHARE MODE.

The SELECT is inside a transaction.  Normally, the SELECT will see the table as of the instant the transaction started.  Other queries may update/insert/delete rows that the SELECT is reaching for, but SELECT won't see them.  This puts a burden on InnoDB to keep old/new copies of row around for cases like this.

If you say FOR UPDATE, the other transaction will probably be delayed, awaiting the "intention to change" lock to be released.

Or it might lead to a deadlock.  InnoDB is good at seeing if the situation can't be resolved by waiting.

Perhaps you would like to discuss a specific case?


Output parameter not set if stored procedure fails inside a TRY / CATCH

You're off to a good start with that test setup, but it is missing something that is causing you to misinterpret what is actually happening. If you put in PRINT statements at the beginning, middle, and end of the stored procedure then the additional output will make it clearer as to what is going on here. For example:

USE [tempdb];
GO
CREATE PROCEDURE test_output
(
  @Result VARCHAR(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

    SET @Result = 'hello';

    PRINT 1;
    RAISERROR('This is an error', 16, 1);

    PRINT 2;
    SET @Result = 'error';

    PRINT 3;
END;
GO

The output from your first test query is:

1
Msg 50000, Level 16, State 1, Procedure test_output, Line xxxx [Batch Start Line yyyyy]
This is an error
2
3
Result is: error

And that is probably what you were expecting anyway. But, the output from the second test query is:

1
Msg 50000, Level 16, State 1, Line xxxxx
This is an error
Result is: empty

That is quite a bit different. We can now see that within the TRY...CATCH construct, execution is halted immediately upon the RAISERROR being called (i.e. it becomes a batch-aborting event). On the other hand, RAISERROR does not immediately halt execution when not called within a TRY...CATCH construct.

However, as you pointed out in your update to the question, this does not explain why the OUTPUT parameter is not then set to hello. That is due to the intention of normal stored procedure behavior to not reflect partial execution (due to a batch-aborting error). This is discussed in the following blog post:

TSQL Basics II – Parameter Passing Semantics

Meaning: even though the stored procedure did execute the step setting the variable to hello, the RAISERROR is now a batch-aborting error when called within a TRY...CATCH construct, and stored procedures do not reflect any changes to parameters when they are aborted.

This behavior is also at the heart of the following explanation:

Why must TVPs be READONLY, and why can't parameters of other types be READONLY


Containment check tstzrange @> timestamptz not using btree or gist index

The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work.

The reason is quite trivial. B-tree indexes do no support the containment operator @>. Neither for range types like tstzrange nor for any other type (including array types).

The manual:

  ... a btree operator class must provide five comparison operators, <, <=,
  =, >= and >.

And GiST indexes do not support <, <=, > =, >= and >. See these chapters of the manual:

Built-in Operator Classes for GiST indexes
Behavior of B-Tree Operator Classes

In Postgres indexes are bound to operators (which are implemented for certain types), not data types alone or functions or anything else. Related:

Optimizing queries on a range of timestamps (two columns)
Can PostgreSQL index array columns?

The GiST index event_seating_lookup_created_at_idx2 you have is pointless. It is created on the timestamptz column created_at. Such a GiST index would be useful for a range type (the opposite direction of logic).

Creating a GiST index on a timestamptz column is only possible since you installed the additional btree_gist extension to allow such useless indexes. (There are useful applications for multicolumn indexes or exclusion constraints ...) In stock Postgres you'd get an error:

  ERROR: data type timestamp with time zone has no default operator class for access method "gist"

So while it's logically valid and technically possible to use a btree index (or a GiST index) for the query, the case is not implemented: no index support for timestamptz <@ tstzrange (where timestamptz would be the indexed expression!). It can be solved with <, <=, >, >= more efficiently. So I guess no developer felt (or will feel) the need to implement it.

Function to rewrite expression with favorable operators

Your implementation makes sense to me. It does what you want and due to function inlining makes use of a plain btree index on the timestamp column - event_seating_lookup_created_at_idx in your example. For calls with a constant range (like in a single function call) I suggest this modified version:

CREATE OR REPLACE FUNCTION in_range(timestamptz, tstzrange)
  RETURNS boolean AS
$func$
SELECT CASE WHEN lower_inc($2) THEN $1 >= lower($2) ELSE $1 > lower($2) END
   AND CASE WHEN upper_inc($2) THEN $1 <= upper($2) ELSE $1 < upper($2) END
$func$  LANGUAGE sql IMMUTABLE;

Declare it IMMUTABLE, because it actually is. Not to help function inlining (can even prevent it if the declaration is false) but for other gains. Related:

Can declaring function volatility IMMUTABLE harm performance?
PostgreSQL Inline Function Behavior

It can be inlined and uses the index just like your version. The difference: this one suppresses a redundant index condition for exclusive bounds. Fortunately, your consideration in this regard is slightly off target:

  The reason for first matching on the $1 >= lower($2) AND $1 <= upper($2)
  condition and then checking the upper_inc and lower_inc
  constraints is to benefit from range scan first and then filter the result.

Postgres 11 (at least) is even smarter than that. I see no Filter step for your version. For default [) bounds (like in your example), I get this query plan (line breaks in conditions added by me):

  ->  Index Only Scan using foo_idx on foo (actual rows=5206 loops=1)
        Index Cond: ((datetime >= '2018-09-05 22:00:00+00'::timestamp with time zone)
                 AND (datetime <= '2018-09-05 22:30:00+00'::timestamp with time zone)
                 AND (datetime < '2018-09-05 22:30:00+00'::timestamp with time zone))

An actual Filter step could add a more substantial cost for corner cases with many hits on an excluded bound. Those would be fetched from the index and then discarded. Quite relevant for time ranges where values often end up on bounds - like timestamps on the full hour.

The actual difference is see is minor, but why not take it?

  ->  Index Only Scan using foo_idx on foo (actual rows=5206 loops=1)
        Index Cond: ((datetime >= '2018-09-05 22:00:00+00'::timestamp with time zone)
                 AND (datetime < '2018-09-05 22:30:00+00'::timestamp with time zone))


insert into select with union needs derived table

Try this it will work

INSERT INTO phppos_categories (name)
SELECT DISTINCT category FROM phppos_items
UNION
SELECT DISTINCT category FROM phppos_item_kits


You can use the 2nd query as a subquery in this way:

SELECT
    usr1.name,
    posts.id,
    text_posts.text,
    posts.location
    posts.createdate
    COUNT(*) AS total,
    (SELECT
        COUNT(user_id)
    FROM
        follows
    INNER JOIN
        users usr2 ON usr2.id = follows.user_id
    WHERE
        usr2.id = usr1.id
    GROUP BY
        user_id) as totalFriends
FROM
    posts
INNER JOIN
    likes ON likes.posts_id = posts.id
INNER JOIN
    users usr1 ON photos.user_id = usr1.id
INNER JOIN
    text_posts ON text_post.posts_id = posts.id
GROUP BY
    posts.id
ORDER BY
    total DESC


Use the T-SQL compatible DATEPART construct to obtain the quarter, and construct the date from that.

For instance using T-SQL on SQL Server:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) +
    STUFF(
        '00'
        , 3 - (LEN(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3))
        , LEN(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3)
        , 1+(DATEPART(QUARTER, GETDATE()) * 3) - 3
        ) +
    '01');

Returns 2015-01-01 00:00:00.000, the start of the current quarter of 2015.

According to the documentation for Sybase IQ 15; you'd need to modify the above to something like (I can't check the syntax since I don't have Sybase IQ on this machine):

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) +
    STUFF(
        '00'
        , 3 - (LENGTH(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3))
        , LENGTH(1+(DATEPART(QUARTER, GETDATE()) * 3) - 3)
        , 1+(DATEPART(QUARTER, GETDATE()) * 3) - 3
        ) +
    '01');

See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/X315942.htm for details.


Can Oracle row-level security work with user session credentials?

From the documentation:

  Oracle database server provides a
  built-in application context namespace
  (USERENV) that provides access to
  predefined attributes. These
  attributes are session primitives,
  which is information that the database
  captures regarding a user session.
  Examples include the user name, the IP
  address from which the user connected,
  and a proxy user name if the user
  connection is proxied through a middle
  tier.

  Predefined attributes are useful for
  access control. For example, a
  three-tier application creating
  lightweight user sessions through OCI
  or thick JDBC can access the
  PROXY_USER attribute in USERENV.

So yes, if your middle tier is handling the proxy username correctly.


Percona XtraDB Cluster - do I add arbiter IP in gcomm://?

While I too could not find a definite answer in the documentation, the following link states:
While Galera Arbitrator does not participate in replication, it does receive the same data as all other nodes.
Which leads me to believe that yes, you would have to add the Arbitrator IPs to all configuration files as if it were another node.
EDIT: This guide seems to indicate the same, although it is not official documentation.


"Aggregation" by hour when data may be much longer or much shorter

One idea is to start with a list of hours. Consider a table of numbers and then convert it to a list of hours using dateadd. Like this but with hour instead of days: https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788

Then join your table of hours to your point table ON h.thehour > dateadd(hour,-1,r.StartRange) AND h.thehour < r.EndRange

Now you can work out the number of seconds (or however granular you want it) within each hour using datediff with CASE, taking the smaller bounds of the hour and range. Something like:

SUM(datediff(second,
    CASE WHEN h.thehour > r.StartRange
         THEN h.thehour
         ELSE r.StartRange
    END,
    CASE WHEN dateadd(hour,1,h.thehour) < r.EndRange
         THEN dateadd(hour,1,h.thehour)
         ELSE r.EndRange
    END
))

You could also just use a table of numbers and consider the first hour is zero, but then you'll need to convert it to hours before doing the aggregate, so it'll be easier to get your hourly blocks first.


How to fix mysql uppercase query script in php

The version for live sire was linux kernel. but the new dev site is ubuntu.

I have done this on ubuntu/ mysql CML and it didn't work.

The solution is I had to reconfigure the mySql database in AWS/rdbs

You have to modify the “lower_case_table_names” parameter for your DB Instance(s). Prior to today, the lower_case_table_names parameter was not modifiable, with a system default of zero (0) or “table names stored as specified and comparisons are case sensitive.” Beginning immediately, values of zero and one (table names are stored in lowercase and comparisons are not case sensitive) are allowed. See the MySQL documentation for more information on the lower_case_table_names parameter.

The lower_case_table_names parameter can be specified via the rds-modify-db-parameter-group API. Simply include the parameter name and specify the desired value, such as in the following example:

  rds-modify-db-parameter-group example --parameters "name=lower_case_table_names, value=1, method=pending-reboot" --region us-east-1

Support for modifying parameters via the AWS Management Console is expected to be added later this year.

setting the lower_case_table_names parameter via a custom DB Parameter Group and doing so before creating an associated DB Instance. Changing the parameter for existing DB Instances could cause inconsistencies with point-in-time recovery backups and with Read Replicas.

Amazon RDS


You need a table of numbers, and fill this table with sequential number as much as you want.

CREATE TABLE numbers (
    number int
)
ENGINE = InnoDB;

INSERT INTO numbers (number) VALUES (1), (2), (3), (4), (5); -- you can add more number according your needs.

And then join this table with your table.

SELECT p.id, p.product, 1
FROM products p
     JOIN numbers n ON p.qty >= n.number;

This should produce result as you want.

If you can't create table, you can simulate table with UNION.

SELECT p.id, p.product, 1
FROM products p
    JOIN (
                SELECT 1 as number UNION
                SELECT 2 UNION
                SELECT 3 -- you can continue this line
               ) n
    ON p.qty >= n.number;


Select everything besides the Max(date) for each group

This is a classic situation where the use of window functions can come to the rescue!
Something like:
SELECT * FROM
(
  SELECT
    group_id,
    dates,
    ROW_NUMBER () OVER (PARTITION BY group_id
                        ORDER BY dates DESC) AS rn
  FROM table_data
) AS t
WHERE rn != 1
ORDER BY group_id, dates DESC;

Can I urge you not to use a keyword as an alias - use something like my_date or the_date instead - it helps readability and is less prone to problems when debugging!
Also, please include your SQL Server version in your questions.
Finally, you could make things a lot easier for those trying to help you if you provided a fiddle with your tables and data - this allows those answering to test their replies and helps eliminate duplication of effort (include the fiddle data in the question also).


"Replicate" sql structure between testing and staging on same database instance

There are many free tools - native and opensource available for comparing schema and data between 2 databases (on same or different servers):

Open DBDiff ==> This is on Codeplex and works great.
SQL Admin Studio ==> This is now a free tool.
Hidden Gem from SQL 2005 and up :  tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
Powershell - You have to write your own code or build up on existing ones
If you are using SQL Server 2012, SSDT is also an option.
I have not tried this one, but just to put it in place
SqlDiffFramework

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.


Powershell Run Stored Procedures in Parallel in Database

Regarding your question:

  Is there anymore efficient or recommended method to conduct this, or
  does the code seem valid?

I'm not aware of a more efficient or recommended method, but I believe I did validate the basic code you are using.

I created 5 identical stored procedures (SP1 - SP5) that each have a WAITFOR '00:01:00' to force the procedure to wait for 1 minute before exiting.

DROP PROCEDURE IF EXISTS [dbo].[sp1];
go
CREATE PROCEDURE [dbo].[sp1]
AS
BEGIN
    SET NOCOUNT ON;

    waitfor delay '00:01:00'
END

I tweaked your original Powershell code to only select stored procedures where the name was like SP% to make sure that only SP1 - SP5 would be selected.

I started your Powershell script and immediately went back into SSMS to execute sp_WhoIsActive so I could see whether the stored procedures were being executed in parallel and noted that all 5 stored procedures were executing.


Probably because the table "dict_verbs_german" is the last table joined.

When I last researched this, "using temporary; using filesort" meant MySQL was writing the the join result into a temporary table, then sorting. (See note below.) In order to avoid "using temporary; using filesort", you'd have to meet all these conditions.

dict_verbs_german is the first non-constant table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

To avoid "using temporary", you'd have to meet all these conditions.

dict_verbs_german is the first table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

Note: I'm talking about ORDER BY, because that's specifically what my research dealt with, and I'm pulling these notes from an email I wrote in 2010. I think the same optimizations applied to GROUP BY, but I could be wrong. Improvements to the optimizer over the last couple of years might have changed things, too.

MySQL documentation

  In some cases, MySQL cannot use indexes to resolve the ORDER BY . . ..
  These cases include . . . joining many tables, and the columns in the
  ORDER BY are not all from the first nonconstant table that is used to
  retrieve rows. (This is the first table in the EXPLAIN output that
  does not have a const join type.)

MySQL documentation for optimizing limit queries

(I don't think this last reference will help you with this problem, but it's good to know where to find it.)

Later . . .

Cutting the WHERE clause seems to eliminate the filesort. (That might not be true for real-world data.) Maybe you can create a view without a WHERE clause and a LIMIT clause, then query the view and still avoid a filesort. I'm sorry that, for personal reasons, I'm not able to test that for you right now.


Mysql query group by to be combined with order by desc

Perhaps this is what you are looking for?
SELECT Day(startDateTime) As "day",
       Month(startDateTime) As "month",
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime ))
             As totalDuration,
       count(*) As trips,
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip
WHERE vehicleID = :vehicleID
  AND vehicleEndCodeID != 1
  AND startDateTime BETWEEN :startDateTime And :endDateTime
Group By 2, 1
ORDER BY 2 DESC, 1 DESC

(I'm assuming you want to sort by both month and day, latest first?)
Also consider
GROUP BY DATE(startDateTime)
ORDER BY DATE(startDateTime) DESC

More
!= is hard to optimize.  I don't see any way to avoid the 'filesort', but this index might help with performance:
INDEX(vehicleID, startDateTime)   -- in this order

However, since that is already the PRIMARY KEY, do not add this INDEX.
Starting over
Now that the CREATE TABLE has been added (and this is why I ask for such so often), here is how I would answer the question:
When possible, have the same ORDER BY as GROUP BY.  This allows the Optimizer to both at the same time.  In your question, you did not quite have the same stuff in each.
A variation on such is to change the direction:
GROUP BY a     , b
ORDER BY a DESC, b DESC

is also optimal.
(Not relevant here:)  In most cases, any mixture of ASC and DESC on the columns in ORDER BY prevents any optimization of the ORDER BY.
My suggestion of using DATE(..) instead of the two columns avoids the issue and solves the potential bug that will show up next January.  BUT...  It may run into the "only full group by" issues since DAY(..), MONTH(..) is not recognized as a subset of `DATE(..)
To avoid some of the above mess, I started by suggesting
GROUP BY 2     , 1
ORDER BY 2 DESC, 1 DESC

If this fails, I will pull a subquery out of my sleeve.


Compressing Base64 Encoded Image in Database Field. Table Size too Large

Base64 is an encoding for transferring data reliably over the wire. It usually increases a files size so I wouldn't look to store a file in this format.

BLOB
The first thing I would do is decode the Base64 back to the original file and store it as a BLOB field. This could shrink the files by a third.

Table design
You said each row has a 40kb image. Why are they all the same size? If some rows share the same image then you could put the images in a table of their own and reference them from your big table.

Limit filesize
Most sites that let you upload an avatar limit the filesize/resolution of the image. Without knowing what these images are for perhaps you could impose a similar limit. You could even rescale the image automatically when they upload it.

Sorry if these are already obvious to you. It's difficult to be more specific without knowing the background about what the images are for.


May sound drastic but if you can't find a good way to debug within SSIS then you can always resort to Network Monitor or Wireshark.  That way you can see what's happening during the FTP session.  Another option may be to ask for some extra FTP logging on the receiving end.


Error log - What does this mean and what can I do about it?

My first answer is: why is your database even accessible from outside through the Internet? That network traffic really ought to be blocked by router Internet gateway router or firewall.

If you really need to allow some connections from the Internet to your database, then limit it to the valid IP address who should be connecting.

At this point that's not really a dba question but a network admin question.