texts: determining user types upon authentication

texts:
I've got 3 different types of users (entities) that will log into a website in developing for a client.

"FITTER", "CLIENT", "ADMIN"

the first method I came up with was this.

CLIENT TABLE    | FITTER TABLE | ADMIN TABLE
id              | id           | id
client          | title        | firstname
office          | firstname    | surname
address_ordinal | surname      | username
postcode        | username     | password
username        | password     |
password        |              |

a separate table for each user with the usernames and password inside.

This would require the user to select the  type of user they want to log in as via a drop down box.

I thought that was rubbish, I just want a form with a username and password box and the application will do all the logic.

so i came up with this:

AUTHENTICATE | CLIENT TABLE    | FITTER TABLE | ADMIN TABLE
id           | id              | id           | id
username     | client          | title        | firstname
password     | office          | firstname    | surname
type         | address_ordinal | surname      |
type_link    | postcode        |              |

Now there is one table with ALL the usernames and passwords.

The Authenticate.type record would either be a string of "FITTER", "ADMIN" or "CLIENT"

the Authenticate.type_link would be the ID of the table that is in authenticate.type!

So this record...

AUTHENTICATE
id | username | password     | type   | type_link
-------------------------------------------------
3  | john     | BF45DE4192DF | FITTER | 4

will authorize whoever's ID is 4 in the FITTER TABLE.

My question is...

 Is there a better way to do this?

There must be right?

because this method essentially uses authenticate.type and authenticate.type_link as a composite key.


texts: microsoft sql express 2012 authentication connection issues - can't login

texts:
I am a novice when it comes to Windows networking authentication, as well as just getting my feet wet with SQL Express, so please bear with me, hopefully this is a simple fix but so far the obvious fixes I have found in forums have not solved it.

I am trying to set up a geodatabase to be accessible by remote PC's running ESRI's ArcMap with standard license.

I have installed MS SQL Express 2012 as well as necessary components from the 2008 release to be able to access geodatabases.

I can successfully connect to the database from within ArcMap on the machine that is serving it, so I know the server is working.

However if I try and set up a connection on a remote machine I get authentication errors.  I can confirm the server machine is seeing the connection attempt and the firewall is letting in the TCP/IP, as I can see in SQL Management Studio Express's log on the server machine logging the failed attempts.

I get two errors depending on how i try and connect, if I use windows authentication I get a message that the domain is untrusted so i can't use windows authentication.  If I use database authentication I get a message that login failed and to check login/pwd/server.  As mentioned before I know the server part seems to be correct.

In SQL Management configuration I have added a user to the DB and given it a password, I have also switched authentication over to both windows authentication and db authentication.  However if I select db authentication at the connect dialog and put in the user/pwd I still get an error.

I have tried to add my remote machine's IP to the trusted domains on the server PC, but not sure I did that right.  Still get errors that say the domain is untrusted if i try windows authentication.

So not sure where to turn next, or what info you folks might require to sort this out.  Any leads are appreciated, thanks.


texts: Why does a table of varchar(255) columns take up less space than an identical table using the correct data types

texts:
I have two tables that are identical and hold the same rows of data.

one uses a varchar(255) for every column of the table, the other is correctly typed for each column (using date, int, decimal etc.).

Using Management Studio's properties window, I can see that the table that stores all data as varchar is far smaller, which is not what I expected to see. My understanding was that an int would only use 4 bytes of space and therefore use much less space than an int stored as a varchar.

What should I look for to understand what is happening here? Both tables have a primary key on an int column and no other indexes. For testing purposes they have been populated with identical datasets from the same source query.


texts:
I'm trying to create an index on a pretty big table (over 8 million rows).

The index creation was taking over 10 minutes, so I selected from master..sysprocesses from my process spid:

spid    kpid    blocked waittype    waittime    lastwaittype    waitresource        dbid    uid cpu physical_io memusage    login_time                  last_batch              ecid    open_tran   status      sid             hostname        program_name                                    hostprocess     cmd             nt_domain   nt_username     net_address     net_library loginame    context_info    sql_handle  stmt_start  stmt_end    request_id
62      11836   -2      0x0003      11762       LCK_M_S         TAB: 5:427148567:0  5       1   0   0           3           2016-01-03 10:03:39.217     2016-01-03 10:15:41.013 0       1           suspended   0x010500...     MyPC            Microsoft SQL Server Management Studio - Query  9636            CREATE INDEX    MyDom       Saul            xxx             LPC         MyDom\Saul  0x000...        0x0100...   522         776         0

What is that "blocked = -2"?  Why is my index creation not working?  And how do I repair this?


texts: How to debug "IO Error: End of TNS data channel" errors?

texts: I am seeing sporadic reports of an application failing certain queries with an "End of TNS data channel" error from the Oracle (10g) server.  Are there any queries I can run (e.g., with SQL*Plus or other tool) to ask the Oracle server what was the cause of such failures?  What logs should I look for?


texts: How do I choose a Mariadb version to install, and what is the difference between mariadb.com and .org?

texts:
I have not worked on databases before but I am needing to research information about databases for work

I will be using Mariadb

What should I look for when choosing which version to download? It looks like the latest version is 10.4.12-GA, but it is only available for Ubuntu 18.04 (only relevant Ubuntu option in the drop down menu here). I will be using Ubuntu linux in the database server computer, and it looks like the latest Ubuntu release is 20.04. Should I use the latest Ubuntu and latest Mariadb, or would it be better to stick with Ubuntu 18.04 and the latest mariadb? Also, this page says that Mariadb 10.1 is included by default in Ubuntu 18.04. is that the recommended version?

One issue that was brought up during our discussion was the interoperability between Mariadb and MySQL. It looks like after version 5.5 the version numbering between the two seems to have diverged, with MySQL at 8.x and Mariadb at 10.x, with the 10.x version being compatible with the 8.0 version. Is this something I should be concerned about? Our database server computer will only have Mariadb and since we are not upgrading from an existing database nor do we intend to change to some other database in the future, I don't think I need to worry about compatibility with MySQL, but I just wanted some opinions on this.

Lastly, a quick google search brings up mariadb.com and mariadb.org. For example, searching "mariadb download" in google gives me https://mariadb.org/download/ and https://mariadb.com/downloads/ as the top search results. Are both of them the same thing? Which one should I be using?


texts: Unexpected behavior of the “Money” field in PostgreSQL

texts:
I started developing a web sistem using Linux Ubuntu and at some point I had to do the following with the data type "money":

explode(" ", "R$ 3,000.00"); // [0] => "R$" and [1] => "3,000.00"

However when I installed the software in Windows I realized that the data is saved without space, that is, "R$3,000.00". Soon, the code snippet fails to function properly.

Note: 1 could "fix" this using:

preg_replace("/[R$]+/", "$0 $1", "R$3,000.00"); // "R$ 3,000.00"

But certainly not a better way.

Note 2: The version of PostgreSQL used is 9.5

Would anyone have any suggestions for resolving this?

Thank you very much.


texts: Collect disk space metrics for whole SQL Server instance

texts:
I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole.

I have found a code sample that can collect disk space information per database.

IF OBJECT_ID('DISK.dbo.disk_activity') IS NOT NULL
    DROP TABLE disk_activity

CREATE TABLE disk_activity (
       servername NVARCHAR(100)
    ,  database_id INT PRIMARY KEY
    , name NVARCHAR(MAX)
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO DISK.dbo.disk_activity (servername, database_id, name, data_used_size, log_used_size)
    SELECT
          (SELECT @@SERVERNAME AS "Server Name") AS SERVER_NAME
        , DB_ID()
        , DB_NAME()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

However, I would like to collect the metrics across the entire server, i.e. total disk space allocated for all databases, total disk space used, total disk space free, and percentage of disk space used.

How should I modify the above script or are there other options available?


texts: why timezone like '+00:00' ignored with select but error with create table?

texts:
I am wondering, why does mariadb ignore timezone like '+00:00' with select statement however throw the error with create table statement?
SELECT CAST('2020-07-09 09:55:36.933554+00:00' AS DATE) dt;

# dt
2020-07-09

CREATE TEMPORARY TABLE tmp AS
SELECT CAST('2020-07-09 09:55:36.933554+00:00' AS DATE) dt;

# (1292): Truncated incorrect datetime value: '2020-07-09 09:55:36.933554+00:00'

I read the question about the error and understand how to fix it. Just curious why such not obvious for me behaviour.

mariadb 10.3.15


texts:
We have extremely large database (mariaDB 10.3) and mysqldump taking 2-3 hours to take the full database dump and also causing downgrade in application performance during the dump process.
After some research, we found out that [mysqldump] section can be added in my.cnf file. also options like 'quick' , 'max_allowed_packet = 512M' can be added for it.
If we can add 'single-transaction' also along with above settings then DB will not be locked during the dump and it should speedup the application.
So are below entries are fine for my.cnf (mariaDB 19.3) ??
[mysqldump]
single-transaction
quick
max_allowed_packet = 512M


texts: What is really wrong with this query please?

texts:
SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent'
CONCAT ('£1.50', sum(activity_price) AS Amount, COUNT(*) AS 'N.Activities'
FROM Activities A, Parent P, Child C, Register R
WHERE C.parent_id=P.parent_id AND C.child_id=R.child_id AND A.activity_id=R.activity_id
GROUP BY P.parent_id;

The Query browser says error is near the 'CONCAT(£1.50', sum(activity_price)....... FROM at line 2.


texts: A table with a foreign-key, referencing the tables own primary key

texts:
I was looking at the database sample that is provided by the DB2 server installaion. This database is the one created when running db2samle.exe from the "bin" folder. I noticed that the table "ACT" contains a primary key with a references to it self, see sql below, and was wondering if there are any logic reason for this? or is it just a glitzh in the design?

CREATE TABLE "DB2ADMIN"."ACT" (
        "ACTNO" SMALLINT NOT NULL,
        "ACTKWD" CHAR(6) NOT NULL,
        "ACTDESC" VARCHAR(20) NOT NULL
    )

ALTER TABLE "DB2ADMIN"."ACT" ADD CONSTRAINT "PK_ACT" PRIMARY KEY
    ("ACTNO");

ALTER TABLE "DB2ADMIN"."ACT" ADD CONSTRAINT "RPAA" FOREIGN KEY
    ("ACTNO")
    REFERENCES "DB2ADMIN"."ACT"
    ("ACTNO")
    ON DELETE RESTRICT;


texts: How can I test sql scripts intended to run with sqlcmd -v in Sql Server Management Studio

texts:
From msdn I learn, that the Variable Precedence of :Setvar X Y is higher, than that of sqlcmd -v X=Y.

I want to write a script, that uses the variables supplied to sqlcmd with the -v option, but which uses some default values, when run in Management Studio using SQLcmd mode.

Best there would be some way to supply the values to use somehow to the Management Studio without having to provide them in the script.


texts: Does database mirroring replicate the time stamp for database backups?

texts:
We use Quest Spotlight for database server monitoring.  Depending on when SQL server decides to fail over automatically, we will get an alert stating that a database has not been backed up in over three days (the factory default) if that server has not been primary in over 72 hours.

My suspicion is that SQL Server does NOT replicate this data, but I wanted to hear from the community since a quick Google search didn't provide anything.  Plus, I am travelling and do not have time to test and I need to provide a fairly immediate response.


texts: A more efficient way to write multiple CASE statements

texts:
SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( Table 1.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( CASE WHEN Table 3.DO = 'Z' THEN 'Y' END ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3 )) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table 4. COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3)) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( Table 3.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  then CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1 ) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL AND ( Table.COLUMN1) - ( CASE WHEN ( CASE WHEN ( Table.COLUMN1)
  IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL
  THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) < 90 THEN 'Y' ELSE 'N' END END ) = 'Y' THEN 1 ELSE 0 END,

Is there a more efficient way to write this statement?


texts: SQL Server Management Studio (SSMS) is way too slow in its GUI

texts:
I know this question has been asked before here and here. But none of them could sovle my problem. I have this environment:

Windows 10, build 1903 (freshly installed)
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Enterprise 10.0  (Build 18362: )
SSMS v17.3 (14.0.17199.0)

Any activity I want to do in it, from opening, to connecting to a database engine, to right-clicking on a database, to creating a new database, to opening up a new query window, to browsing tables, any activity that is not query takes like 5 to 10 seconds to perform. It's clearly apparent that SSMS is doing something for each activity, and it gets stuck somewhere.

Here are the things I've done so far, without effect:

Blocked Microsoft's certificate URL (adding 127.0.0.1    crl.microsoft.com to hosts file)
Downloading certificate and installing it from http://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl
Connecting to "local" instead of "."
Resetting user-defined settings in "C:\Users\user\AppData\Roaming\Microsoft\SQL Server Management Studio"
No antivirus is installed (only Windows Defender, the default of Windows)

It's a shame that a program from such a reputable company can't work smooth out of the box, and troubleshooting it is sooooooooooooooo difficult.

Could you please help. How can I diagnose what's wrong with SSMS.

Update: This problem exists even with SSMS v18.2 (15.0.18142.0)


texts: Orders and Quotes Tables - Database Design

texts:
Below is the ERD of my current db schema for the orders part of an e-commerce site. The site is based on oscommerce, but has been modified, also I have removed quite a few of the fields from the tables to simplify the diagram.

I am planning to add the functionality to create quotes in the administration of the site.
A quote will be very similar to an order, and most quotes will be converted to an order. There will be some different data needed for each, although most fields will appear in both, such as customer details.
Also the original quote should be maintained so that it can be used again in the future, that is modifying an order that has been created from a quote does not modify the original quote.

Considering the similarities; it felt unnecessary to duplicate the whole of the orders structure for quotes, especially as the child tables for orders are in the format needed to store the data for quotes.

The approach I thought I might go for is to create a quotes table, that would be similar to orders and then reuse most of the orders child tables to work also with a quote.

(Probably I will still duplicate orders_status and orders_status_history to quotes_status and quotes_status_history as there will be different statuses for quotes, and a quote and order should only have a relevant status.)

Tables I was hoping to re-use for a quotes:

orders_total
admin_watch_list
orders_products
orders_products_attributes
orders_products_downloads

In order to re-use these tables, it would seem to me that I would have to do one of the following:

add a quotes_id field to each one of the tables listed above. Although then each of theses tables will have a 'quotes_id' and 'orders_id' one of which should always have a value and the other should always be NULL.
add an orders_id field to the quotes table, start the auto increment of the orders_id in my quotes table to say 1000000000 so that it doesn't interfere with the auto-increment orders_id that started from 0 in my orders table. The orders_id in quotes could then reference all the other tables using it's orders_id, although that orders_id would never appear in the orders table

Both of these seemed solutions did not seem ideal and I was wondering if there was a better way to accomplish what I wanted to do.

Ideally I would like to re-use as much as possible as it could mean faster development and less to maintain in the future.

I already have a page in my administration for editing orders, and I feel the closer I keep to the structure of orders, the easier I think it would be to reuse my code from edit orders page for a create/edit quote page which I need to add.

All help or advice is appreciated.


texts: Refresh materalized view incrementally in PostgreSQL

texts:
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?

Consider this table & materialized view:

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

Periodically, new values are added to graph or an existing value is updated. I want to refresh the view graph_avg every couple of hours only for the values that have updated. However in PostgreSQL 9.3, the whole table is refreshed. This is quite time consuming. The next version 9.4 allows CONCURRENT update but it still refreshes the entire view. With 100s of millions of rows, this takes a few minutes.

What's a good way to keep track of updated & new values and only refresh the view partially?


texts:
I have 142 SQL databases on Azure.
There are using Standard Elastic Pool (100 eDTUs).
Azure Portal recommended an index for one my database.
It says disk space needed is 1045.19 MB.

Azure portal offers that index for 14 out of 142 impacted databases.

I created that index and it's size is only 8960 KB.
My Index Size measurement script is :
DECLARE @TableName sysname = N'PosItemStoreOverrideMaps';

SELECT i.name AS IndexName
      ,8 * SUM(s.used_page_count) AS IndexSizeKB
FROM sys.indexes AS i
    INNER JOIN sys.dm_db_partition_stats AS s
        ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;

I want to understand why it says 1045.19 MB.
Regards


texts: Can FOR XML PATH return NULL for an empty resultset?

texts:
I would think this query

select null for xml path('foo')

would return NULL, but actuality

<foo/ >

is returned. Is there any way to achieve this behavior?

To clarify, the behavior I would expect to see is demonstrated in this example

SELECT
'foo' WHERE 1=0
for xml path('Outer'), type

This returns a different result and I'm not understanding why.

SELECT
(SELECT 'foo' WHERE 1=0)
for xml path('Outer'), type

My real world query is an xml subquery, but the example is valid either way.

SELECT
(SELECT 'foo' WHERE 1=0 FOR XML PATH ('Inner'),type)
for xml path('Outer'), type

This query works in the simplest case, but does not allow adding attributes to the root element, which is required in my case.

SELECT 'foo' WHERE 1=0 FOR XML PATH ('Inner'),root('outer'),type


texts: Does restarting Mongodb config servers restart the mongos?

texts:
Our environment is a 2 node Shard of 5 node replica set with a 3 node replica set config servers and 6 mongos.
We happened to change the hostname(short to FQDN) of 1 of the config server and we restarted the config server. Once the config server came up, we saw all the 6 mongos servers restarted(with 1 second interval).
So, would like to know if this is how the mongo works or is there something I am missing?

Regards,
Kenny


texts: Which DBMS is good for super-fast reads and a simple data structure?

texts:
I am developing a product that, as a part of it's operation, must track a large number of files/directories. The idea is to store stat information in a database then, on boot, create watches for each file. Files that change will be queued (in the database) for a group sync to a remote database. They will be synced in order of priority, a number between 1-10.

Information about database:

< 100,000 entries of stat info
Entire database read at boot, only the file path is necessary
Queued files will have a priority field (nothing else needs be searched by)
Insertions can be slow

I've found a couple databases that I think will work, but I'm not sure which would be the best:

Redis- store file-path as key, stat data as value; queue would be a list
MongoDB- more query options than Redis, but still fast

I'm thinking a NoSQL database would be the best solution here, as there isn't too much relational logic going on, and the total data size isn't too large (something like < 100 mb, closer to < 30 mb).  I did look at SQLite because it seems to be simple enough to embed in an installable application.

Since this is a distributed application for end-users and not a high-load server, the database doesn't have to support many simultaneous users. The main priority here is to find a database whose model makes the most sense.

So the question, which database would be most applicable for this situation?

Also, are there any other databases that would make more sense for an application like this?


texts:
I'm using CentOS 8 and I have a MariaDB 10.5.4 Galera set up with three nodes. Now, I want it to create a backup to my central server from time to time. I'm thinking of mysqldump but restoration would take a lot of time and mariabackup does not support remote backups (I think). So I'm thinking of creating a Master-Slave to from my galera cluster setup to a stand-alone setup on my central server then I run mariabackup from there like this:

For now my current set up is that I had my third node act as a backup for HAProxy and it is where mariabackup is currently running (see image and remove central server). But my boss wanted me to store backup on central and let central do the backup instead of the database server.
Are there any simple alternative? I'm thinking of following this guide, but it's a bit outdated and I'm not sure if it would work. Thanks


texts: ENABLE KEYS on a large table is crashing mysql

texts:
I am trying to copy our data over to a new server, and at the same time, testing our backup-and-restore processes. I have a 2.6GB file created by SELECT INTO OUTFILE, containing 19 million rows. I've been trying to use LOAD DATA INFILE to get it into our new MySQL 5.5 server (this came from a 5.0 server). The table is MyISAM. I get the following after about 35 minutes:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Looking at the error log, we have:

120509  9:47:34 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=67108864
read_buffer_size=16777216
max_used_connections=3
max_threads=151
thread_count=3
connection_count=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 820301 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x906de60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
120509 09:47:36 mysqld_safe Number of processes running now: 0
120509 09:47:36 mysqld_safe mysqld restarted
...
Version: '5.5.19-log'  socket: '/data/databases/mysql/mysql.sock'  port: 3306
Distributed by The IUS Community Project

Note that I've had similar problems using mysql database < database.sql, but I don't know if it's the exact same error message.

Fake edit: So I've discovered that it can load the data just fine, if I disable keys beforehand. It dies when enabling keys. So now I know it's not necessarily the size of the data that's causing problems. This test server is running i686, 32bit, version of MySQL, whereas the table came from a 64bit version of MySQL. Could that possibly contribute to this?


texts:
I have a multi-database architecture with a single database for each client and a separate database to handle system-wide data. In this separate database, I have an 'Updates' table where a row is updated every time certain data from the client databases are updated. The 'Updates' table is setup somewhat like this:

DbName     CustomerId
ClientA    1
ClientB    1

I would like to join this data to a table in the database denoted in the 'DbName' column and end up with something like this:

DbName     CustomerId     CustomerName
ClientA    1              Bob Jones
ClientB    1              John Smith

What is the cleanest and most efficient solution for this problem?


texts: Performance impact of repeatedly PREPARE-ing the same query?

texts:
Relevant documentation: https://www.postgresql.org/docs/current/static/sql-prepare.html

The performance benefits of preparing an SQL query once, and using execute to run it with different values is known. My question is slightly different. How would prepare/execute behave in the following scenario:

App maintains a pool of DB connections (so connections are not being repeatedly opened & closed)
An ORM issues a bunch of prepare/execute statements, but doesn't really re-use the prepared statement explicitly. Which means, the following two statement may be issued over & over again:

Example:

PREPARE findUserById (int) AS
  SELECT * FROM users WHERE users.id=$1;
EXECUTE findUserById({different user id each time});

How would Postgresql behave when the same query is prepared over & over again using the same recycled connection (via a DB pool)? Is it going to incur the parsing overhead each time, or will it start using some cache after a few such repetitions?


texts: Select distinct values in column with condition

texts:
I have the products table as follows:

ProductID | ItemId | Color
---------------------------
1           X        5N
2           X        10N
3           X        2N
4           Y        12S

and a second one joined by ItemId

ID | ItemId | Group | Color | Value
-------------------------------------
1    X        T       null    5
2    X        T       5N      7
3    X        M       2N      9
4    X        M       null    6
5    Y        M       null    12

I'd like to write a select that will return distinct Group values. As you can see there two groups with T value and ItemId is X, so in this case if color is the same in product and in other table then value with same color must be used.

If there is no such color, then take null value. What is important is that all distinct groups for each product must be fetched. The only thing is the priority for the same groups if color match.

Desired result:

ProductID | ItemId | Group | Color | Value
-------------------------------------------
1           X        T       5N      7
1           X        M       5N      6
2           X        T       10N     5
2           X        M       10N     6
3           X        T       2N      5
3           X        M       2N      9
4           Y        M       12S     12

DDL:

create table ProductPrices
(
  Id bigint not null
    constraint pk_product_prices
      primary key,
  ItemId varchar(50) not null,
  Group varchar(10) not null,
  Value decimal(18,4) not null,
  Color varchar(50)
)
go

create table Products
(
  ProductId nvarchar(70) not null
    constraint PK_table_4
      primary key,
  ItemId nvarchar(50) not null,
  Color nvarchar(50) not null
)
go


texts: Enable query logging for single user on SQL server 2008 and SQL server 2016

texts:
I need to monitor queries executed by a specific set of users on a database.
I need to check them on a weekly base. Is that possible on SQL server 2008r2? And 2016?


texts: Postgresql synchronous_commit off and select queries

texts:
If data is inserted into a postgresql database using asynchronous commit, is it possible for a subsequent select query to guarantee that the data it reads has been written to disk?

I am storing generated data in postgresql and since the data can be regenerated, I would like to take advantage of asynchronous commit to increase throughout. But the generated data is periodically synchronised with an external system and it is important that data that has been sent to the external system is not lost.


texts: SQL Server 2017: How does Batch Mode Memory Grant Feedback Work?

texts:
Batch Mode Memory Grant Feedback is part of a family of features in the 2017 query processor that consists of:

Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback

So how does Batch Mode Memory Grant Feedback work?


texts: MySQL client out of memory - Linked Table MsSQL to MySQL

texts:
I'm currently having an issue on 2 out of 3 SQL servers when copying large amounts of data to a MySQL table. The MySQL table currently houses ~6 million records, and the query that is running is trying to update / delete / insert another 100,000 records.

I've attempted to use OPENQUERY on the commands I'm running to attempt to do this but that's not working either and I am just presented with the error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.6.35]MySQL client ran out of memory"

I thought it could be a driver issue so updated to the latest ODBC driver (5.2) but that did not work either.

The two servers i am using are both 32 bit operating systems which may have a great deal to do with it (the 64 bit server hasn't had an issue as yet).

Is there another setting i can have on the server that will let it pool values and process with what memory it has available or am I just stuck with an issue of needing more memory for processing a result list that is this large?

UPDATE: the third server is now not working either, all showing same results

My cnf settings just have:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 512K
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
join_buffer_size = 128K
innodb_buffer_pool_instances = 1

Have to setup the symbolic-links section still, but at the moment this is still a non-production server so not a big issue.

The two statements that are failing are inserting and deleting rows from a MySQL table that is > 4 million rows. Both are inside a stored procedure that reads values from a transfer table (transfer table is filled via triggers on the main tables which read in whether the query should send a delete or insert request to the mysql table)

    DELETE
        a
    FROM
        [LinkServerTable]...Table1 as a
    INNER JOIN
        [dbo].[TransferTable] as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        a.Branch = 'Databases Branch'
    AND
        (b.QueryType = 'Delete' OR b.QueryType = 'Update')
    AND
        b.DateQueried < @DateNowVar

    INSERT INTO [LinkServerTable]...Table1
    (
            Columns,
            Branch
        )
    SELECT
        b.Columns,
        Branch = 'Databases Branch'
    FROM
        [dbo].[TransferTable] as a
    INNER JOIN
        [dbo].OriginalTable as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        (QueryType = 'Insert' OR QueryType = 'Update')
    AND
        DateQueried < @DateNowVar
    AND
        NOT EXISTS
            (SELECT
                1
             FROM
                Transfertable as c
             WHERE
                a.Key1 = c.Key1
             AND
                a.Key2 = c.Key2
            AND
                a.Key3 = c.Key3
            AND
                a.Key4 = c.Key4
            AND
                a.Key5 = c.Key5
             AND
                a.DateQueried < c.DateQueried)

That extra statement at the end of the insert section is so it won't process multiple updates / deletes if not required (if they've been updated multiple times in the time it takes to run the procedure)


texts: Homebrew MariaDB Server Start Error with 'mysql.server start'

texts:
When starting MariaDB 10.3.8 on MacOS 10.13.6 the following strange behavior occurs:

mac:~user$ mysql.server start

Starting MariaDB

.180813 17:50:11 mysqld_safe Logging to '/usr/local/var/mysql/mbp.local.err'.

180813 17:50:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql.. ERROR!

There are actually many more dots in the output before ERROR! is displayed; the process takes a couple of minutes. The odd thing is the server does run. And shutdown appears normal:

mac:~user$ mysql.server stop

Shutting down MariaDB.. SUCCESS!

Any idea how to determine what is causing the error message on startup and fix it?


texts: How to delete constraint inside a trigger function

texts:
I have read a lot about it, but being new to Postgres, I am at a loss.

The trigger is :

CREATE TRIGGER foopolygon
BEFORE INSERT OR UPDATE OR DELETE ON polygon
FOR EACH ROW EXECUTE PROCEDURE maj_foo();

Part of the function is :

CREATE OR REPLACE FUNCTION maj_foo()
RETURNS TRIGGER AS
$BODY$
    IF TG_OP = 'INSERT'
     THEN

    ALTER TABLE points
    DROP CONSTRAINT bar;

    INSERT INTO points(gid,fookey)
    VALUES(0,NEW.fookey);

    EXECUTE 'UPDATE points SET fookey = ' ||  quote_literal(NEW.fookey)  || ' WHERE ST_Within(points.geom, $1)'
    USING NEW.geom;

  ALTER TABLE points
  add constraint bar
  foreign key (fookey)
  references polygon (fookey);

      COMMIT;
IF TG_OP = 'UPDATE'......


texts:
I need to extract some statistical information form drupal database tables. The problem with drupal is that it splits it's data for entities across multiple tables and getting simple information from such database usually requires multiple joins.

In this case I have structure similar to this (I'm simplifying things for the purpose of this question as the whole structure is way more complicated):

node table

nid
title
uid (user id from users table)
type (can be: flight or airport)
[others]

departure_field table

id
flight_node_id (node id from nodes table with type 'flight')
departure_node_id (node id from nodes table with type 'airport')

arrival_field table

id
flight_node_id (node id from nodes table with type 'flight')
arrival_node_id (node id from nodes table with type 'airport')

The query

Now let's say I want to know top airports visited by the given user. The query could go like this:

SELECT
  COUNT(a.nid) AS cnt, a.nid, a.title
FROM
  node a, /* Airport */
  departure_field dep,
  arrival_field arr,
  node f /* Flight */
WHERE
  /* All flights of user with id 100 */
  f.type = 'flight' AND f.uid = 100
  AND /* Include data for departure and arrival airports */
  f.nid = dep.flight_node_id AND f.nid = arr.flight_node_id
  /* Combine that with airport data */
  AND (a.nid = dep.departure_node_id OR a.nid = arr.arrival_node_id)
GROUP BY
  a.nid
ORDER BY
  a.cnt DESC

The problem

This query, although looks quite simple, uses temp table and file sort. And of course runs for ages. How can I optimise it for MySQL to run fast enough to be usable during webpage rendering? For now the query executes few minutes with database having data for 50k+ flights.

Note that this is not the actual query and table structure. The field naming in the drupal database is insane, so I tried do simplify things for the purpose of this question.


texts:
I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code:

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC;

Almost every minute new rows are inserted. So, after one hour, I have several rows not ranked and I need to update the whole table again.

However, it takes too much time. Is there any way to optimize or a different way to update the table faster? Maybe a different approach? Partitioning the table?


texts:
We have a SQL Server 2008 R2 cluster with 2 node and total data is about 2 TB now that cluster is log ship to 4000 Miles another data center. I know about a 2 years back we have mirroring setup instead of log shipping, but issue happening at night when load jobs runs @ take 22 hours instead of 3 to 4 hours every night so we try to put mirroring in asynchronous mode but still it didn't make any difference so we configure log shipping for every 15 minutes.

But now it needs upgrade to SQL Server 2012 & we like to do always on but its mirroring behind always on so will it work or is there any other way?


texts: View collation are incomplete joins in couchdb

texts: I was trying to create view collations following this tutorial: http://elegantcode.com/2012/02/14/view-collation-in-couchdb/ but I realize that the result was an array with 5 elements with 2 objects types instead of an array with 2 items and those items with another array. Am I supposed to organize this array when I receive the result? Or is there another way of doing relationships on couchdb?


texts: Check against multiple duplicate records

texts:
I have data that i should insert into table.
But before inserting i need to check against duplicate records and report list of those records.

Table:

CREATE TABLE `test` (
  `A` varchar(19) NOT NULL,
  `B` varchar(9) NOT NULL,
  KEY `A` (`A`),
  KEY `B` (`B`)
) ENGINE=InnoDB;

I need to check for both columns:

Number of records to insert: ~1000

Rows in table: ~1.000.000

What is the efficient way of doing this.

Thanks in advance.


texts:
Select * from (select tmp.userid userid, tmp.username ,tmp.likescore+tmp.commentscore+tmp.sharescore+tmp.tagscore+tmp.userscore+tmp.lsharescore+tmp.friendscore as score from
    (
    SELECT u.userid, u.username,
    (select 10*count(1) from b_likes l where l.userid = u.userid) likescore,
    (select 15*count(1) from b_comments c where c.userid = u.userid) commentscore,
    (select 25*count(1) from b_page_shares s where s.userid = u.userid  ) sharescore ,
    (select 20*count(1) from b_tags t where t.userid = u.userid ) tagscore,
    (select count(1)*10 score from (
                            Select Distinct friendid,userid
            from b_friends f ) a
            where a.userid=u.userid ) friendscore,
    (select 25*count(1) from b_shares ls where ls.userid = u.userid ) lsharescore,
    (SELECT (100 * count(rightflag))
            FROM `b_scores` s
            WHERE  rightflag='1'
            and level_no!=''
            and u.userid=s.userid) userscore
    FROM `b_users_data` u) tmp) tmp2 where tmp2.score != 0
    order by tmp2.score desc, tmp2.username asc  limit 10;

The slow query log says it took 72 seconds and 12.7k rows were examined.
Apart from id, no other columns are indexed in any table.


texts:
We currently work with table named tb_tranfers. This table has 40 million rows and is ~26 GB in size (11 GB data, 15 GB indexes).

10 to 15% of the rows are soft-deleted rows (DeletedDate is not null). The applicaiton only uses rows where DeletedDate is null. All queries to this table will include a clause to that effect.

There are 15 indexes on this table. The missing index DMV contains suggestions to create indexes with DeletedDate as an included column.

Would it be helpful to use a filtered index WHERE DeleteDdate IS NULL on all 11 nonclusetered indexes? Or would it be better to have the DeletedDate column as an included column?


texts: MariaDB root password change using Ansible

texts:
I had tried to change MariaDB root password using following Ansible script.
      - name: Dump MySQL root Password
        debug:
          msg: "MySQL new Password : {{ mysql_pass }}"

      - name: Set MySQL root Password
        become: True
        mysql_user:
          name=root
          host="localhost"
          password="{{ mysql_pass }}"
          check_implicit_admin=yes
          login_user="root"
          login_password=""
          state=present

In my /etc/ansible/hosts
[myhosts:vars]
mysql_pass=mynewpassword

As I was told password variable may not be taken, I added debug/msg step.
TASK [Dump MySQL root Password] ********************************************************************************************************************************
ok: [vizua@node1] => {
    "msg": "MySQL new Password : mynewpassword"
}

Similar issue discussed here. Thats where I got Password changing steps. This step runs without error, showing changed, but yet I can access without password or any password.
MariaDB : Server version: 10.1.47-MariaDB-0ubuntu0.18.04.1
Ansible : ansible 2.5.1


texts:
I'm backing up a MongoDB server using (for better or worse) mongodump. After running db.stats() I get a storageSize of 8.9GB and a dataSize of 44.5GB. This makes sense to me as I expect a lot of the data (at least 1/2) to be highly compressible.

My question is this, why would my backup's be 45GB in size, if the storage size is only 9GB - I've also checked my actual disk usage (the disk is used ONLY for mongo and has slightly over 9GB of disk usage.

I'm running mongodump with the following options:

/usr/bin/mongodump -h $HOST -d $DBNAME --username=$DBUSER --password=$DBPASS --archive="$OUTPUT"

I'm aware of the gzip option, but if the data is stored compressed, surely it should be backed up compressed too - and gzip should simply be a second level of compression?


texts:
While importing I get this error:

pg_restore: [archiver (db)] COPY failed for table "transaction_details":
ERROR:  invalid input syntax for type money: "$0.00"

restore completed, but the transaction_details table is empty.
This is Heroku's PostgreSQL Dump database.

PostgreSQL version on Heroku is 9.3.15 and the same on my system

The command I used:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U root -d database 577b86e3-8d96-4410-9b36-dd0f78cfe32f

Could anyone suggest a solution?


texts: error in SSIS - [SQL query execution task] Error: The binding name of the results must be zero for the complete result set and the XML results

texts:
I have an Execute SQL task that returns "Full result set". Despite that, the task fails. Please how can I fix it ?

[SQL query execution task] Error: The binding name of the results must be zero for the complete result set and the XML results.


texts: Connect to postgres with different privileges

texts:
From python program, I want to connect to postgres with various privileges.

lower_privileges = connect(db, username, password)
higher_privileges = connect(db, username, password)

How do I setup postgres 9.5 on arch linux, so I can do that?

create database dbone;
grant all privileges on dbone.* to 'writer'@'localhost' identified by 'pass';
grant select on dbone.* to 'reader'@'localhost' identified by 'pass';

The problem is I enter createdb dbone then psql then grant all privileges on database dbone to writer; and I get "db does not exist error" then I exit psql and go dropdb dbone and get "db does not exist"


texts: Approach to Analyzing Database Requirements

texts:
When presented with requirements for a database driven system, I always find it useful to imagine a world with no computers, where data records were kept in filing cabinets and data was collected by filling in certain forms. Then from that, I proceed to build my table of unnormalized data, then finally get it up to 3NF (if I can).

But the difficulty I often run into in my approach above is, how big should I make my forms? For example, let me use the same scenario from my last question, but extend it further.

An animal charity that aims to house rescued pets to new loving home owners. This charity works slightly differently, it has three types of variations on "Person".

Temporary Homeowner [TH]- Will take care of the pet temporarily until a new permanent homeowner is found.
Permanent Homeowner [PH] - Can view the files of all available pets at the shelter and pick one to adopt permanently.
Charity Sponsor [CS] - Sponsors money to the charity because of the good work they do!

Slightly different information is recorded for each type of person. The [TH] comes and registers himself/herself at the charity to help out, he/she states what type of animal he/she are willing to temporarily look after and how many animals max they're allowed to temporarily house.

The [PH] needs his/her details recorded, so that the charity can give them a call back whenever they're free to see how the poor animal is settling in.

The [CS] also needs his/her details stored in case the charity sends them a newsletter and / or a thank you note for the help and support and informing them how their money is being used to do good.

So in a world before computers, I think in such a case, there would be a "person" registration form, but to highlight my issue, how big should this form be and what information should it capture? Here is what I currently envisage this form to be like (created in word):

I suspect that my approach to getting the unnormalized data into a table would affect how smooth the process of normalization progresses later on.

From the above form, I can bang the whole thing into a table, where each form field would equate to a column of the table, but that would lead to many null values for rows of data, depending on what the person type is [TH][PH] or [CS].

I am looking for some guidance on this, and would greatly appreciate some advice on the best approach to take.


texts:
Lately I experience below errors when restoring db2 database

SQL2043N Unable to start a child process or thread
SQL1084C  Shared memory segments cannot be allocated

db2stop and then db2start always helps.

As a part of my job (testing) I restore databases often during the day. Current usage can be decreased via deactivating databases when is it really close to Memory Limit. But lately only deactivation didn't help and I was still getting these errors. Then I found out that HWM usage might be the trouble part as it is only increasing and after I do db2stop & db2start both Current usage & HWM usage are reset.

Is there any other way how to 'clear/reset' HWM on db2?

DB2 9.7.2 on SuSE


texts: MySQL - Is it possible to assign permission to count the number of tables in a database without permissions on individual tables?

texts:
As part of a script I am trying to count the number of tables that exist in a particular MySql schema.

Using my own user I am using the query

SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = dbName

However I'd like the user running this script to have as few permissions as possible, specifically I don't want them to have any select privileges on any of the tables belonging to the schema

Is it possible to assign a grant which would allow a user to count the number of tables without having any access to the tables themselves?


texts: Error while connecting SQL Server management studio 2008

texts:
I have installed SQL Server 2008 management studio. When I connect, it shows an error. Also when I go to the SQL Server configuration manager and click on SQL Server SERVER services, this message is shown:

  There are no items in list to view

No connections are found in the SQL Server configuration manager while my local network is connected.

How can I fix this?


texts: Efficient way to get last 10 rows of each group in SQLite, can CTE help?

texts:
Consider there is a table of job runs history with the following schema:

job_runs
(
    run_id integer not null, -- identifier of the run
    job_id integer not null, -- identifier of the job
    run_number integer not null, -- job run number, run numbers increment for each job
    status text not null, -- status of the run (running, completed, killed, ...)
    primary key (run_id)
    -- ...
)

and it is required to get the last 10 runs with status != 'running' for each job (jobs differ by job_id). To do that I wrote the following query:

SELECT
    *
FROM
    job_runs AS JR1
WHERE
    JR1.run_number IN
    (
        SELECT
            JR2.run_number
        FROM
            job_runs AS JR2
        WHERE
            JR2.job_id = JR1.job_id
            AND
            JR2.status != 'running'
        ORDER BY
            JR2.run_number
        DESC
        LIMIT
            10
    )

It do what I need, but even though there is a multifield index on the job_id and run_num fields of the job_runs table the query is slow, because it scans job_runs table and for each its row runs subquery. The index helps subqueries to run fast each time, but the fact that the nester query scans entire table kills performance. So how can I tune performance of the query?

some thoughts:

Number of jobs (different job_ids) is small and if there were a FOR loop in SQLite it would be easy to loop over all distinct job_ids and run the subquery
passing the job id instead of JR1.job_id then UNION all results.
Also I've tried to construct the query using recursive CTE, but unsuccessfully.

important:

Please don't suggest to run the loop inside the source code of my application. I need pure SQL solution.


texts:
I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL?

(I.e. does ssl = on it make it impossible to connect without SSL encryption?)

Are there other ways to ensure that all clients always connect over SSL/TLS?


texts: BCNF decomposition which generates redundant relations (as for 3NF)

texts:
Sometimes the 3NF synthesis decomposition algorithm (such as the one described here p.4) generates redundant relations, where all attributes of some R_i already appear in another R_j. The algorithm is supposed to delete such redundant relations.
I read several descriptions of BCNF decomposition algorithms (see an example below) and none of them mention a similar final deletion step, which let me think that maybe the reason is that this situation never occurs with BCNF decomposition.
However I stumbled upon this example : consider R(A,B,C,D) and the set of functional dependencies
F = {A→B; B→C; B→D}.
I use the BCNF decomposition algorithm described here:
Input: relation schema R and set of FD's for R
   (1) compute keys for R based on FD's
   (2) repeat until no more BCNF violations:
         (2a) pick any R' with AA → BB that violates BCNF
         (2b) decompose R' into R1(AA,BB) and R2(AA,CC)
              where CC is all attributes in R' except (AA U BB)
         (2c) compute FD's for R1 and R2
         (2d) compute keys for R1 and R2 based on FD's

step (1) leads to only one key: A.

(2a) the DF BC→D is a consequence of F and BC is not a superkey therefore R is not in BCNF.

(2b) we decompose R using BC→D and get R1(BCD) and R2(ABC).

Applying (2c) and (2d) for R1, we get that F1={B→C; B→D} is a cover for the FD's of R1, and then that R1 is in BCNF.

Applying (2c) and (2d) for R2, we get that F2={A→B; B→C} is a cover for the FD's of R2 and that A is the unique key for R2. Therefore R2 is not in BCNF  since B→C belongs to F2 and B is not a superkey.

If we decompose R2 using B→C, we obtain R3(BC) and R4(AB), and we are done because R3 and R4 only have two attributes.

Finally we obtain a BCNF decomposition of R as R1(BCD),R3(BC) and R4(AB). But all attributes of R3 already appear in R1, therefore it would be preferable to delete R3.

Did I apply the algorithm incorrectly, or does it mean that one should add this additional deletion step to this BCNF decomposition algorithm ?


texts: Why does TLS1.2 break connections to SQL Server?

texts:
NOTE: Requested to move the question from StackOverflow to dba

Don't know if this is really a SQL Server problem or not.

Client moved to TLS 1.2, it broke an app that connects to SQL Server 2017 and it got dumped on my lap.

Being a Linux person all I can say is when they went back to TLS 1.1 it worked and here is the only error message I can glean in all this mess:

  dbnetlib connectionopen secdoclienthandshake ssl security error

In SS Studio, when I click on the DB I get this error message:

  Error connecting to 'hostname'.

  Login failed for user 'CORP\myaccount'. (.Net SqlClient Data Provider)

  Server Name: hostname
  Error Number: 18456
  Severity: 14
  State: 1
  Line Number: 65536

  Program Location:

  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
  at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
  at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
  at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
  at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
  at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
  at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
  at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
  at System.Data.SqlClient.SqlConnection.Open()
  at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
  at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ConnectToServer(UIConnectionInfo connectionInfo, IDbConnection liveConnection, Boolean validateConnection)

TLS 1.2 was turned/put on the server that hosts SS.


texts: Does Oracle run queries to completion after SQLdeveloper disconnects?

texts:
The query is a

CREATE TABLE t as SELECT .....;

statement. Can I reliably count on Oracle to complete this after I disconnect?

After I re-connect, how can I check on the status of this query?


texts: Why does SUM() return 0 when no rows in table?

texts:
I've been saying for years that if you are assigning an aggregate value to a variable that you need to handle if there are no rows returned. ie:

DECLARE @MyVar INT

SELECT @MyVar = SUM(t.MyValue) FROM dbo.MyTable t WHERE /* clause */

IF @MyVar IS NULL
    SET @MyVar = 0

However a developer recently pointed out that they don't need to do the @@ROWCOUNT check as the SUM always returns a value (even if there are no rows).  Upon doing some further digging I've found that there seems to be inconsistent behaviour from SQL Server:

So if I run:

DECLARE @MyTable TABLE(ID INT, MyValue INT)

/* you get a value of 0 back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100

I get a single row with a value of 0 back.

However if I add on a GROUP BY clause:

DECLARE @MyTable TABLE(ID INT, MyValue INT)

/* when you add on a GROUP BY, you no longer get a record back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100 GROUP BY t.ID

I get no rows back (which is what I expected), I've done some digging into the MS documentation but can find no reference to this difference in behaviour.  Can anyone explain why this is occuring?  Also is this a change in how SQL Server works?

Note: My tests have been in SQL Server 2008R2

** Edit: looked back on some emails I'd sent in the past and corrected my check, I've been saying If the value is still NULL handle it...  (handles no rows as well as assigned NULL)


texts:
I'm trying to get a clearer picture of how communication works with Availability Groups.  I know the listener technically 'lives' in the sqlserver.exe process on the primary server.  I also know that the primary server has 2 IP addresses when its the primary.  One corresponding to the listener IP and one corresponding to the server itself.

My question is that, when an application connects through the listener, does all subsequent traffic get routed back through the listener?  Or does the listener simply point the application to the correct server and it connects directly to the primary?  Basically, does traffic take 2 hops back through the listener or is a connection made directly to the actual sql server?


texts: Modification to GEQO (Genetic Query Optimization) of PostgreSQL

texts:
I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these possible join sequences at random. Source :
http://www.postgresql.org/docs/9.3/static/geqo-pg-intro.html

My question: how to modify the GEQO function if I definitively know the right join sequence, so that I don't have to search different join sequences. For ex., if I knew that the best way of joining the 4 relations is 4-1-3-2, I needn't have to check for other permutations.

There aren't any good materials on how GEQO is implemented in PostgreSQL. PostgreSQL only gives the overall view of the GEQO functionality but doesn't explain much.

Or could I achieve this functionality in the standard_join_search() itself without using GEQO?


texts:
I need to query the values of V$OSSTAT and am able to do so using tools like dbVisualizer. But when I take the identical query and put it into some Javacode I fail to find the table.

I had this problem before with specific application schemas and was able to solve this by putting the applications schema name in front of the table simiar to this: SELECT NAME FROM FOOAPP.USERS.

But in this case I simply don't know which schema to use for gaining access to V$OSSTAT. I tried prefixing the view / table with SYS and SYSTEM already.

SELECT STAT_NAME, VALUE, COMMENTS FROM SYS.V$OSSTAT
SELECT STAT_NAME, VALUE, COMMENTS FROM SYSTEM.V$OSSTAT


texts: Login permissions outside of sa - SQL Server

texts:
Where I'm at, our DBA team is split up into admins, BI, DEV. We are wanting to have our admins have SA rights and the other two teams have everything but sa and to take away the ability to change server settings (min/max memory, file locations, etc...)

I have played around with different server-level permission combos, but they force the need to give individual rights on the database(s) and we are looking to avoid this since these groups are still in our DBA on-call rotation.

I also found the Control Server securable. While this does everything I want it to, it also gives the ability to change server settings (Alter Resources). This would be the perfect answer if only it didn't give the ability to change server side settings. I have tried running a DENY on Alter Resources, but Control Server trumps this DENY.

Is there a way to do what I'm needing in SQL Server that I haven't thought of?

I believe I have tested this thoroughly, but wanted to ask the community before I go back to my team lead. I hope I've given enough information, but let me know if I need more.


texts:
I have used the below query with a constraint to check the values which are inserting to the stu_code column start with 's'.

mysql> CREATE TABLE student_info(
       no INT(3) PRIMARY KEY,
       stu_code VARCHAR(10),
       CHECK (stu_code like 's%'));

Query OK, 0 rows affected (2.68 sec)

but it does not work. When I inserting the values (like below) I can insert values which are not starting with 's'.

mysql> INSERT INTO student_info VALUES (1,'k001');
Query OK, 1 rows affected (0.47 sec)

I am beginer with SQL, Can anyone help me find out what is the issue?


texts: Unable to start PostgreSQL service after pgAgent installation

texts:
I had Postgre running just fine on my Windows 10 (64-bit).

Then I tried to install pgAgent via the stackbuilder for postgres. I got some error that it was not able to create a .conf file under %\user\appdata\postgres.

(Note That folder has never existed. I had my data under C:\Program Files\PostgreSQL\11\data)

After that is asked me to restart my PC, so I did.

Now I am not able to start pgAdmin, not able to start postgre from microsoft services. I am not either able to run the stack builder for postgres, it simply suggest that it might help to reinstall postgres.

Can one stackbuilder install really corrupt my entire database?

I got these messages in the logger:

2019-09-10 15:33:03.300 EEST [146960] ERROR:  could not open extension control file "C:/Program Files/PostgreSQL/11/share/extension/pgagent.control": No such file or directory
2019-09-10 15:33:03.300 EEST [146960] STATEMENT:  CREATE EXTENSION pgagent;
2019-09-10 15:35:23.957 EEST [118560] ERROR:  could not open extension control file "C:/Program Files/PostgreSQL/11/share/extension/pgagent.control": No such file or directory
2019-09-10 15:35:23.957 EEST [118560] STATEMENT:  CREATE EXTENSION pgagent;
2019-09-10 15:43:44.944 EEST [152352] ERROR:  schema "pgagent" does not exist
2019-09-10 15:43:44.944 EEST [152352] STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 15:48:53.247 EEST [152560] ERROR:  schema "pgagent" does not exist
2019-09-10 15:48:53.247 EEST [152560] STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 15:51:53.369 EEST [146440] ERROR:  schema "pgagent" does not exist
2019-09-10 15:51:53.369 EEST [146440] STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 15:59:04.716 EEST [152972] ERROR:  schema "pgagent" does not exist
2019-09-10 15:59:04.716 EEST [152972] STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 16:17:15.856 EEST [151636] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
2019-09-10 16:17:15.857 EEST [151792] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
2019-09-10 16:17:26.569 EEST [14320] LOG:  received fast shutdown request
2019-09-10 16:17:26.575 EEST [18252] ERROR:  canceling statement due to user request
2019-09-10 16:17:26.576 EEST [18188] ERROR:  canceling statement due to user request
2019-09-10 16:17:26.580 EEST [14320] LOG:  aborting any active transactions
2019-09-10 16:17:26.580 EEST [20960] ERROR:  canceling statement due to user request
2019-09-10 16:17:26.609 EEST [14320] LOG:  background worker "logical replication launcher" (PID 18252) exited with exit code 1
2019-09-10 16:17:26.609 EEST [14320] LOG:  background worker "TimescaleDB Background Worker Launcher" (PID 18188) exited with exit code 1
2019-09-10 16:17:26.610 EEST [14320] LOG:  background worker "TimescaleDB Background Worker Scheduler" (PID 20960) exited with exit code 1
2019-09-10 16:17:26.732 EEST [17544] LOG:  shutting down
2019-09-10 16:17:26.898 EEST [14320] LOG:  database system is shut down

EDIT: When I try to execute pg_ctl start -D C:\Program Files\PostgreSQL\11\data,

I get this error:

The code execution cannot proceed because LIBPQ.dll was not found.
Reinstalling the program may fix this problem.

The code execution cannot proceed because libiconv-2.dll was not
found. Reinstalling the program may fix this problem.

I do have postgres in windows path

EDIT2: I ended up reinstalling the PostgreSQL server. Now I got it working again. The source of the original problem was probably that pgAgent was installed in a folder where already existing postgre files were


texts: SSAS - what could be a good starting point for memory settings?

texts:
I have received the following alert regarding memory settings of SSAS:

Alert: SSAS 2014: Invalid TotalMemoryLimit Configuration Source:
MSSQLSERVER Path: SASBI01Alert time: 9/15/2015 5:38:50 PM
Alert description: The configured TotalMemoryLimit for the SSAS instance (307.1728515625 GB) exceeds the configured threshold required for necessary operating system functions and may lead to system instability or failure.
Total memory on the server: 0 GB Total memory
usage: 0 GB Memory used by non-SSAS processes: -3.89069747924805 GB
Memory used by SSAS: 3.89069747924805 GB SSAS Memory Limit Low:
249.57795715332 GB (0%) SSAS Memory Limit High: 307.1728515625 GB (0%)

What could be a good starting point for those memory settings in SSAS? Please see the image below.

This server has ONLY SSAS running on it.


texts: PostgreSQL what happens if TimeZone is not set?

texts:
From the docs on 9.1, I see

  If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.)

This section was however removed subsequently in 9.2, what happens if 9.2+ is not specified in postgresql.conf or as a server command-line option?

My timezone currently says localtime and I'm wondering what exactly that means and how it's decided. The original text seems to answer it if it's still applicable. Are we still relying on the C library function localtime()?

# show timezone;
 TimeZone
-----------
 localtime
(1 row)

I was trying to add this information in this answer for completeness when I realized that the older docs had more details.


texts: The backup or restore was aborted - what could it be?

texts:
use master
go
ALTER DATABASE Apcore SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE Apcore
FROM DISK=N'C:\Backups\QG-V-SQL-TS$AIFS_DEVELOPMENT_APCore_FULL_20180909_230034.bak'
WITH  FILE = 1,
MOVE N'APCoreDataPrimary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreDataPrimary.mdf',
MOVE N'APCoreData1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreData1.ndf',
MOVE N'APCoreData_nonclusteredIndexes' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreData_nonclusteredIndexes.ndf',
MOVE N'APCoreLog_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreLog_log.ldf',
recovery,  NOUNLOAD,  REPLACE,  STATS = 1

While restoring a database on a developer's machine I got the above error.

The way I have done it is:

First let me say that I did not worry about - permissions, indexes, triggers, synonyms, etc.
I copied the backup file (less than 1 GB) from live to her local machine
did the restore filelestonly which is ok
I had to set the database into single_user mode because there are some services attempting to use it at every second
prepared the script above, and when running it and got that error message.

what could it be?


texts: A SQL Server database backup/restore issue

texts:
A backup/restore issue has taken place on one of our client’s server.

We use Microsoft SQL Server 2014 installed on Microsoft Windows Server 2012.

Problem results: - Critical data loss, inability to restore the database.

Problem details: - We used to perform scheduled ‘encrypted’ backups from the client’s database. The database encryption is implemented using MASTER KEY ENCRYPTION BY PASSWORD.

Using the script:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '#W0rrtyldOrtf$$ByryackupP@trSs0rd#';
GO
CREATE CERTIFICATE DBBackupEncryptCert
   WITH SUBJECT = 'DBBackupEncryptCert';
GO

The problem occurred after a server hardware failure led to a corruption of the server HDD that contains our databases and its backups encryption certificates. Now, we have an encrypted backup of the database, but its certificate was on the damaged HDD and was lost.

We also have a one-week earlier non-encrypted backup of the database. But the one-week lost data is very critical to us and to the client.

Our trials

We tried to recover the damaged HDD physical files, but this option was not possible
We have tried to find a way to re-generate the backup certificate using an older backup of the master database by creating an encryption certificate out of it with the same password, but this attempt did not work
We thought that if we could somehow re-create the certificate using the old backup, this would help us decrypt the database backup file.

So, kindly I ask you for help. If there is a way we can restore this database using the current encrypted backup without the certificate key, this would be appreciated.

And please, do not hesitate to ask for any further information or resources to let you know more about the issue for resolution.

Thank you


texts: Query records that chain together based on date gaps

texts:
I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a gap between them. Once there is a gap of more than 30 days the chain is broken and does not continue. Overlaps should continue the chain. Is there a way to solve this without resorting to something slow and recursive?

Schema

ID          (int)
Arrival     (datetime)
Departure   (datetime)

Sample data

ID Arrival       Departure
1 2009-04-19    2009-04-28
2 2009-04-27    2009-05-21
3 2009-06-11    2009-07-01
4 2009-07-19    2009-07-24
5 2004-04-19    2004-04-21
6 2009-02-28    2009-03-05

Desired Query Result

ID Arrival       Departure
4 2009-07-19    2009-07-24
3 2009-06-11    2009-07-01
2 2009-04-27    2009-05-21
1 2009-04-19    2009-04-28


texts:
I could not find storage requirements for JSON type columns in MySQL documentation

How much storage space allocated for JSON type column in InnoDB? Are JSON columns  the same as BLOB?


texts: Is using MySQL views a good translation mechanism?

texts:
We want to use a foreign vendor MySQL database, but the whole schema is barely understandable by our development team. We could alter the schema to rename tables and columns once for all, but the database is periodically updated and reinstalled on our production servers.

Would building isomorphic views be a good translation mechanism in our case? We would use our locale in application queries and yet be able to refer to native locale in case we wish to interact with the database vendor.

As we would have strictly the same tables and columns, would there be any performance alteration, in particular with regards to existing indexes?


texts: Determining Cardinality in a Logical Model

texts:
How should one determine Cardinality in a Logical Model ?
Should it be based on how the rows of an entity relate to another entity or should we consider the natural relationship between the entities i.e. conceptual relationship between the entities ?
Example:  If I have an entity Course and an entity Course Type, what would be the cardinality ? Each course can have only one course type. For example, Bachelor of Arts is a course of course type Bachelors and Master of Science is of course type Masters
If I have Course Type as part of Course entity, then Course Type would only contain list of valid course types and it would be "many-to-one" (non-identifying) as there are many courses which will could 1 course type.
On the other hand if I model it in such a way that Course Type entity has Course ID (foreign Key) and Course Type , then the relationships between Course and Course Type is "one-to-one" (identifying).
Basically what I am trying to understand is, which one the following is right ?
each course has one course type  OR  many courses have one course type
How should one make this decision ? Are there any guidelines ?
P.S. : I am a beginner and using Oracle Data Modeler


texts:
Assume a tool where one can create an useraccount, then store 'fields' for that account. Each field can contain zero or more 'sub-fields' and each of those can contain zero or more strings.

I wonder how a proffesional developer would design a database structure for such a use case.

My approach would be:

Create a table for each user
Add two rows, fields and id
store a big JSON object in the first row, which contains every subfield and its strings

But somehow i think that this won't be the best way to solve this and i'm having a giant brain fart.

I would greatly appreciate any suggestions.


texts: What queries read my indexes and what queries apply inserts / updates to my indexes?

texts:
I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system.

Using the top query posted by Kendra:

SELECT
    querystats.plan_handle,
    querystats.query_hash,
    SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1,
                (CASE querystats.statement_end_offset
                    WHEN -1 THEN DATALENGTH(sqltext.text)
                    ELSE querystats.statement_end_offset
                END - querystats.statement_start_offset) / 2 + 1) AS sqltext,
    querystats.execution_count,
    querystats.total_logical_reads,
    querystats.total_logical_writes,
    querystats.creation_time,
    querystats.last_execution_time,
    CAST(query_plan AS xml) as plan_xml
FROM sys.dm_exec_query_stats as querystats
CROSS APPLY sys.dm_exec_text_query_plan
    (querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset)
    as textplan
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext
WHERE
    textplan.query_plan like '%PK_Sales_Invoices%'
ORDER BY querystats.last_execution_time DESC
OPTION (RECOMPILE);
GO

I see all queries that use my index from the cache. Closer inspection shows they are used because we have to maintain the index.

How do I only view queries that use my indexes for reads regardless of writes? If 7 / 7 queries use my index because they are updating columns that force the server to update my index and it never reads the index, I want to be able to see those separately from queries that read my index or have a flag indicating such.

I have this query I found and have tried:

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
'INDEX NAME'    as IndexName,
getdate() as TimeStart,
   DB_NAME(E.dbid) AS [DBName],
   object_name(E.objectid, dbid) AS [ObjectName],
   P.cacheobjtype AS [CacheObjType],
   P.objtype AS [ObjType],
   E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=''[INDEX NAME]''])') AS [ScanCount],
   E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=''[INDEX NAME]''])') AS [SeekCount],
   E.query_plan.query('count(//Update/Object[@Index=''[INDEX NAME]''])') AS [UpdateCount],
   P.refcounts AS [RefCounts],
   P.usecounts AS [UseCounts],
   E.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
WHERE
   E.dbid = DB_ID('DATABASE NAME') AND
   E.query_plan.exist('//*[@Index=''[INDEX NAME]'']') = 1

My concerns with that query is:

a) Accuracy, is it for sure going to tell me if I had scans or seeks (reads) on my index in question?

b) How do I know about inserts into my indexes with that query? It looks like it shows me the updates to the index but not the inserts. (Clustered index insert impacting non-clustered index)


texts: Is it a good idea to shrink prior to a SQL Server upgrade to a new instance

texts:
We currently have a 100GB database which is in simple recovery mode, and it is running on 2008 R2. We have a new server with SQL 2016 Enterprise installed, ready to go - and we're building a migration plan, for how to do the upgrade.

We've ran a test run using SQL 2016's Data Migration Wizard (https://www.microsoft.com/en-us/download/details.aspx?id=53595), which we used to point to our current, live, production DB instance and to restore the DB to '16. This took 15 minutes, which is very quick considering the other approach (backup currently-running DB, copy over to new server) -- mainly because we can't back up / restore over a UNC path.

We've realised that we have a lot of old/unneeded data in our DB, and have kicked off some scheduled tasks using the SQL Agent to perform some deletes, so by the time we're near to our "go live" date then we'll have less data residing in the DB, with the idea being that we should be able to restore the live instance over to '16 quicker with less data.

However, obviously the actual size of the database FILE on disk will not shrink until we perform a DB shrink.

We're thinking of shrinking the DB for these reasons:

faster backup times
less files to restore when we take a copy of production,  scrub it and restore to a test environment, for a like-for-like copy of production to perform tests against
there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)

I'm aware that by shrinking the DB then the DB size will just grow again in some time, but I think we've previously overlooked some aspect of data growth, to which we've put some processes in place to reduce excessive DB writes. We currently have a 75MB autogrow which happens around 2-3 times a day.

Will this be a good idea? We'll be restoring our DB over, upgrading the SQL compatibility level and then running an index rebuild (using Ola Hallengren's index script) & updating stats. I'm curious if the shrink will massively spike these index operation times as it'll leave the DB pretty fragmented from what I've understood. I'm not sure what the best approach is here - perhaps don't shrink and only rebuild the index on '16 after the data migration?

Thanks


texts: How do data checksums interact with streaming replication?

texts:
Data checksums are a new feature introduced in 9.3, and:

  there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption is detected

In the event of a checksum failure on a replication master, will that corrupt data replicate to slaves or will replication halt. Does it depend on the setting of ignore_checksum_failure?

This README has some useful related information but doesn't directly answer the question.


texts: SSIS Slow Data Transfer between Oracle and SQL Server 2016

texts:
I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS.
It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the transfer had been realized with a Linked Server.

I do have the logic done and used the "Native OLE DB\Microsoft OLE DB Provider for Oracle" as well as the "Native OLE DB\Microsoft OLE DB Provider for Oracle" OLEDB connection.

When I run the package, the transfer of just 9,934 rows takes about a minute. Which for a table containing 28,228472 records is to long.

I tried using the "Oracle Source" component with an Oracle Connection Manager.
While I can test the connection, as well as design the process and even preview the result set, as soon as I run it in my Visual Studio, it states the error:
"The component metadata for could not be upgraded to the newer version of the component. The PerformUpgrade method failed."

The Visual Studio is:
Visual Studio 2019 16.2.0

The Visual Studio project compatibility mode is:
SQL Server 2016

The targeted SQL Server is:
SQL Server 2016 (13.0.5233.0)

The package is supposed to be run on an SQL Server:
SQL Server 2016 (13.0.5264.1)

The Oracle server version is:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0  Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Thank you for your help.


texts: High Log Space Used for model System database

texts:
I was running DBCC SQLPERF(LOGSPACE) and noticed that while my user databases have nice, low Log Space Used percentages, my model system database is currently at 98%. I have it set to the default Full Recovery and would rather keep it that way but I was wondering what's the recommended way of handling this.

My instinct is to run a Log Backup on it but I also know the system databases are a different breed of creatures. Most places I've looked suggest switching it to Simple Recovery or just leave the matter at 'model is small and rarely changes, backing up the log is unnecessary'. But do they really mean the best practice is to let it autogrow constantly?


texts:
I've got an app where users can place orders for recurring delivery of a particular service.

The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.

When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.

My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?

I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.

I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!

(I'm using Postgres 9.4.1.2 in case that matters, but I figure this is more of a generic SQL modeling question)


texts: ( RDS ) MySQL to MySQL migration within aws

texts:
RDS MySQL to MySQL migration

I've 2 MySQL(5.7) RDS instance in my AWS account namely prod & dev.
My task here to migrate all objects from Prod instance to Dev in order to make sure all prod objects exist in dev.

Both MySQL instance sits in same aws account.

What would be right approach so that I can further re-research about it.


texts: Oracle SQL Creating Copy of Table Using DBMS_metadata

texts:
so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this to create a new identical table?

I know you can easily create a copy of a table with create table t1 select * from table2; etc. So how can I make use of dbms_metadata to create a new identical table? Any advice would be greatly appreciated. Thanks!


texts:
I have a SQL Server 2014 Standard Edition used by my devs to test their applications pointing to a common instance. Lately I've been informed that for this purpose I can use Developer Edition, that it would keep all the features they need to work.

I've see on Microsoft website that this counts as an Edition Downgrade and won't have a proper "Wizard" to help on this task. Seeing this, I planned on:

Backup all databases, export users, jobs and grants.
Uninstall Standard Edition.
Install Developer Edition.
Restore databases and re-create users, give grants and re-create jobs.

I did use DBA Tools Powershell Library to migrate a previous instance on the exact same case. Is this plan the easiest option to achieve this migration?

Thanks in advance!


texts: Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other?

texts:
I have following query which contain group_concat and SQL_CALC_FOUND_ROWS together as I want result + total no. of records present in it.

SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(ca.id)
FROM notification AS ca
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 '
AND  ca.date!=""
AND  ca.alert_msg_id
NOT IN(26,27) AND ca.from_id != '777'

SELECT FOUND_ROWS();

But this does not return the desired result.

Please suggest.


texts: Postgresql: How to pass an array to trigger function in TG_ARGV?

texts:
I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be available in the TG_ARGV array, as described in the docs.
This works fine as long as all of my arguments are scalars, but there are a couple of them that really should be represented as arrays.
Here's a simplified example of what I'm trying to do:
CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS
$$
DECLARE
    special_names text[];
    special_users text[];
BEGIN
  special_names := TG_ARGV[0];
  special_users := TG_ARGV[1];
  IF NEW.name = ANY(special_names) THEN
    NEW.name = 'special';
  END IF;
  IF NEW.user = ANY(special_users) THEN
    NEW.user = 'flag';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_function(
    ARRAY['magicname1', 'magicname2']::text[],
    ARRAY['user1', 'user2']::text[]
);

Postgres complains with

ERROR:  syntax error at or near "["
LINE 5:  ARRAY['magicname1', 'magicname2']::text[],

In other contexts, that's valid syntax for an array literal (e.g. SELECT ARRAY['magicname1', 'magicname2']::text[]; works fine). I've also tried using other syntax varieties, like '{"magicname1", "magicname2"}'::text[] but without any success.
Can anyone suggest a good approach for passing an array parameter to a trigger function? Is it just impossible and I should give up and encode/decode via a JSON string or something?


texts: AWS RDS POSTGRES - Japanese/Chinese Full Text Search support

texts:
I have a requirement to support FTS (Full Text Search) of Japanese and Chinese on PostgreSQL.

I am aware that there are extensions to handle this however AWS RDS PostgreSQL appears to restrict the available extensions to a certain list.

Would anyone be able to suggest strategies to enable FTS for Japanese and Chinese given the restrictions RDS imposes (or be able to correct any miscomprehension's I may hold)?

EDIT: We're on 9.6.5 and the available extensions can be found at:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.96x


texts:
Is it best not to use Percentage file growth for autogrowth settings?

The below recommends using percent value growth for databases under 500GB but is this recommended?

http://performance-expert.blogspot.ie/2012/06/tuning-autogrow-settings-of-sql-server.html

  If disk space is limited or databases cannot be sized, you should configure the >autogrowth value to a fixed percentage. For example, configure the autogrowth value to >10 percent for databases under 500 GB and to a fixed number of megabytes if a database >exceeds 500 GB.

Thanks!

UPDATE: Below I have added a snapshot of Autogrowth on some databases. Any recommendations on this?

Also what is best way to predict database size?


texts: What's called 'Universe Data Source' in Crystal Reports?

texts:
There are several options to choose which data source we want to connect in Crystal Reports including SAP BEx, Relational Connection, and Universe. I have difficulties in understanding what exactly do they mean my saying a Universe data source. How are Universe data sources created and where are they used for? What is its difference with relational connection?
Thanks.


texts:
We have a central SQL Server 2012 database.  There is a single table [tbl_Users] that stores all kinds of information pertinent to an employee, for example,

UserID
CustomerID
FirstName
Surname
EmployeeID

All users are stored in the same table regardless of Customer, and segregated using CustomerID.

Some customers are now asking for new columns to be added, which are only relevant to their particular business.  In the past, we've just added the columns into [tbl_Users], but now this table is becoming untidy with too many columns, and for the most part, many row values end up being NULL because of their small relevance to others.

Can anyone offer any suggestions on how to allow us imitate this type of design dynamically (if it is possible).  I've considered the following approach:

Create a table called [tbl_ColumnNames] that contains each unique column name and data type
Remove all non-generic columns from [tbl_Users] move them to [tbl_ColumnNames]
Create a many-to-many table [tbl_CustomersColumns] where we assign columns to any respective Customers
Create a table [tbl_CustomerColumnData] to hold the data moved from [tbl_Users], possibly with SqlVarient type for the actual data field

Is this a decent approach?  Our ASP.NET application would then dynamically create the forms to allow data population.  Has anyone used SqlVarient types for this kind of thing?  Or, is there a better way altogether?

Many thanks.


texts: Tables not displaying after creating database by script. Do I need to insert?

texts:
I successfully created (scripted) a database in SQL Server 2012.

My problem is that while the database itself is listed in the object explorer, the tables from the database are not. The database I scripted did not contain actual data, i.e., there were no 'Insert' statements. Is this last the reason why the tables are not listed?

I tried to see if the tables were stored somehow without appearing by running an insert into statement for  one of the tables in the script statement , but I get an error:

  " Msg 208, Level 16, State 1, Line 1 Invalid object name
  'ContactInfo'. "

What am I doing wrong? Why aren't the database tables listed?

Here is the actual script I used:

CREATE DATABASE [Name];
GO

CREATE TABLE Company  (CompName Varchar(10) PRIMARY KEY , CompCity VarChar(20), CompState CHAR(2), CompAddress Varchar(50), CompZip SMALLINT  );

CREATE TABLE NamePhone (CompName VarChar(10) NOT NULL PRIMARY KEY REFERENCES Company(CompName) , CompPhone   Varchar (12) NOT NULL );

CREATE TABLE ContactInfo (ContactFName NVarChar(20) PRIMARY KEY , ContactLName NVarchar(20), ContTitle Varchar(10), ContactDirPhone Char(10), ContEmail Varchar(320)
, ContCompany Varchar(10) REFERENCES Company(CompName) );

CREATE TABLE ContactReportsTo (ReportstoFName NVarChar(20) , ReportstoLName NVarchar(30), ReportstoPhone Varchar(50), ReportstoCompany Varchar(10) REFERENCES
Company(CompName),  ContactFName NVarchar(20)  REFERENCES ContactInfo(ContactFName));


texts: Suggestions for proposed SQL 2017 SSIS Scale-out layout along with some questions

texts:
We're putting in a new SQL 2017 environment, and given we're heavily dependent on SSIS I would like to use the SSIS SCale-out feature.  We've generally setup pairs of SSIS servers for redundancy, so I'm excited to check out Scale out.  I have some questions though which I can't find answers to in any of the MS docs or elsewhere.

My proposed layout is this:

SQL Server 1 - AG Replica 1
SQL Server 2 - AG Replica 2
Master SSIS Server (SSISDB on AG)
Worker SSIS Server 1
Worker SSIS Server 2

So with this my questions are:

If the Master SSIS Server is down, will the Worker SSIS Servers still be able to execute jobs?
Can I setup multiple Master SSIS Servers for redundancy?
What is the recovery process if the environment needs to be rebuilt?
If a job is set to run every 5 minutes and it starts executing on Worker 1 at 10:05pif it's not complete by 10:10pm will Worker 2 execute another instance of it?
Would it be better to run Worker/Master on one server and Worker on another?  Reducing the number of servers?
When deploying SSIS solutions are they deployed to Master which pushes them to the Workers or are they deployed to each Worker one at a time?
Any other caveats to running in this layout?

My goal is two fold - performance and redundancy as SSIS has always been the one place we had no redundancy given it wasn't supported to run the SSISDB on an AG until now.

Thanks for any suggestions or feedback.


texts: How to re-assign two variables in a conditional UPDATE

texts:
Consider a simple update as

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
    col2=IF(@var2>50, @var2 := @var2-col6, @var2);

How we can change two variables in one condition. For example, consider that when @var1<50, we want to change not only @var1 := @var1+col5, but also re-assign @var2 := @var2 + 100. In fact, regardless of the condition 2, we want to increase @var2, if the first condition fails (the second part).

Is it possible to re-assign two user-variable in a IF STATEMENT or we need to add another IF STATEMENT?

This will be equivalent to

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
    virtual_col=IF(@var1>50, NULL, @var2 := @var2+100),
    col2=IF(@var2>50, @var2 := @var2-col6, @var2);

Here we have created a useless column, not to collect the data, but to repeat the IF STATEMENT for the @var2. Obviously, it is not good from performance point of view, as we are running a useless UPDATE action.


texts: Oracle parallel query: finding out which Producer a Consumer is waiting for

texts:
One of our materialized view refresh's (part of a daily ETL routine) was hanging today; it usually completes within 5 minutes, but this time, I had to kill it after 23 hours.

V$SESSION contained this information:

SID   USERNAME  BLOCKING_SESSION  EVENT                   P1TEXT
491   USER1                       PX Deq: Table Q Normal  sleeptime/senderid
1073  USER1     491               PX Deq: Execute Reply   sleeptime/senderid

So it seems that SID 1073 (presumably the query coordinator) was waiting for SID 491 (a parallel consumer), which itself was waiting for data from at least one producer (according to Doug Burn's paper "Tuning Parallel Execution", that's the meaning of PX Deq: Table Q Normal ). But there was no BLOCKING_SESSION for the consumer session.

How can I find the parallel producer session the consumer is waiting for?