texts: MariaDB Parallel Replication Drifting at random times

texts:
I have recently been thrown in the deep-end to manage our Database servers since our DBA left.

The current set-up is Mariadb (mariadb Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu), InnoDB (I turned on innodb_file_per_table as we had some 200+GB ibdata1 files.). We use parallel (conservative) replication.

a little bit of the config (just without ssl certs)

innodb_file_per_table = On
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 20
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
table_open_cache = 8192
thread_cache_size = 256
table_cache = 70000
open_files_limit = 100000
log_slave_updates
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
## Logging
log-output = FILE
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/slow-log
log-slow-slave-statements
long-query-time = 30
log_warnings = 2

I have noticed that sometimes our servers drift but this usually corrects itself as I am assuming a large statement(s), one of the reasons I turned on Logging. But some servers will just not correct themselves and one of these only has a 2.6G ibdata1 file, I have restarted slave a few times but its just very slow. I am good on the Linux and Hardware side and ensured there is no other problems with disks. The network link between the servers via web, each server has 1Gbps Connection though and we see no latency ~6ms ping and stable. Some servers drift when there is no load on the servers, like the slave is not requesting from the master and it certainly doesn't seem to use full bandwidth. Overnight one of our servers has gone from being synced to 3 hours behind and seems to be increasing. I cannot see any issue on other database or even any users logged in to our application. could something be locking it from replicating?

Any help or suggestions would be really appreciated as I am kind of thrown in the deep-end here, I am not a DBA but whilst we do not currently have one I am the "best fit" and we need to get these servers replicated correctly for production.

If anyone also has any suggestions on learning resources, I think maybe this setup needs to be started from scratch, we do not use GTID for example, I wonder if we even use the correct replication method.

Many Thanks.


texts:
I am trying to write a query to pull group data by payment_method (one of columns in table) and by date. I can pull the query without grouping by date, but once I add the dates, I am unable to get the query to work. Does anyone have any idea how to fix my query?

Current query:

SELECT payment_method,SUM(net_amount)
DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0) as OrderDateByMonth
FROM Database
WHERE [criteria]
GROUP BY payment_method
AND DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0)
ORDER BY payment_method
AND OrderDateByMonth


texts:
select (column1+column2) AS column1, table1.*
from table1

both column1 and column2 are columns in table1. Is there anyway that when I run the query I can get only the new column1 is shown and the old one is ignored?

My current option is spell out every column in the table that I actually do want to show up

Edit: This is with SQL 2008 R2, but I'd be interested if other SQL versions have a solution as well


texts: When creating a many to many relation with an attribute, should I include a non-composite primary key?

texts:
For example, I have frame and voltage tables, both of which relate to a motor object:

frame(id, motor_attribute);
voltage(id, voltage_attribute);

I can pick a frame and pick a voltage and for each combination of those there is an assigned efficiency attribute.

I can thus create an efficiency table, but how?  I see two choices:

efficiency(frame_id, voltage_id, efficiency_attribute);
//composite key = (frame_id, voltage_id)

OR

efficiency(id, frame_id, voltage_id, efficiency_attribute);
//composite key = (frame_id, voltage_id)
//primary key = (id)

Do I include a non-composite primary key into the table?


texts: SQL server - parsing element of delemiated string

texts:
I have delimited string like this 'test1:test2:test3:test4:test5'.

I need to get the third and forth values one value at a time. The string always has five values.


texts: Identifying number of rows (COUNT) that fall between two timestamps for each minute in the hour

texts:
Good day all!

I'm attempting to write some SQL that can identify the number of rows that fall between two timestamps but iteratively count them up.

An example use case, a telephone call CDR for one user:

|Call Number|Start Time         |EndTime            |
|===========|===================|===================|
|1          |2019-01-01 10:01:23|2019-01-01 10:05:44|
|2          |2019-01-01 10:02:33|2019-01-01 10:05:22|
|3          |2019-01-01 10:03:50|2019-01-01 10:06:11|

I'd like a query that could produce this sort of result, per each minute for the hour (using the example data above, Call ID is an arbitrary number, so can be ignored in this context, it's the count of the calls in progress that's important):

|Time Chunk         |Calls In Progress|
|===================|=================|
|2019-01-01 10:00:00|                0|
|2019-01-01 10:01:00|                1|
|2019-01-01 10:02:00|                2|
|2019-01-01 10:03:00|                3|
|2019-01-01 10:04:00|                3|
|2019-01-01 10:05:00|                3|
|2019-01-01 10:06:00|                1|

A script would iterate through customers to produce reports per customer so the WHERE clause would consist of something like WHERE user = 123 for example.

I've tried a couple of queries, but they will only produce instantaneous results based on the start time of the call and then show me how many were in progress for that particular minute of the day, with a load of gaps in between, and not counting for any calls that are "still in progress", as follows:

SELECT
    FROM_UNIXTIME(
        CEILING(
            `timestamp`/60
        )*60
    ) AS timeslice,
    COUNT(*) AS calls_in_progress
FROM
    calls
WHERE
    account = {ACCOUNT_ID}
    AND `timestamp` >= UNIX_TIMESTAMP('2019-01-01 00:00:00')
    AND `timestamp` <= UNIX_TIMESTAMP('2019-01-01 23:59:59')
GROUP BY
    timeslice
WITH ROLLUP;

...which in turn produced the following undesirable result (the rollup is useful but not required)...

|timeslice          |calls_in_progress|
|===================|=================|
|2019-01-01 09:35:00|               18|
|2019-01-01 12:10:00|                2|
|2019-01-01 12:11:00|                2|
|2019-01-01 12:19:00|                1|
|2019-01-01 12:49:00|               51|
|NULL               |               74|

Performance impact of said query is quite important given this is going through a table with millions of rows, so the use of the indexes associated to timestamps would be critical.

Any input would be super useful.


texts: How do I set a geography point to be the North Pole?

texts:
I tried to execute this query on my SQL Server 2008 R2 instance:

UPDATE ITEMS
SET GEOG=geography::STPointFromText('POINT(0 90)', 4326);

SQL Server returned the following error:

System.FormatException: 24201: Latitude values must be between -90 and 90 degrees

I also tried POINT(0 89.99) and got the same error.

How do I set a point to be the North Pole?


texts: How can I dynamically create views in Postgres 10 using variables?

texts:
I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12.  I have about 200 views to create and the only difference in each one of them is the schema location and the where clause.
For example:
create or replace view SCHEMA1.myview as
    select * from SHAREDSCHEMA.sometable where MYCOL = 'ABC';

So what I need to do is create that view around 200 times with each iteration being:
SCHEMA1.myview
SCHEMA2.myview
SCHEMA3.myview
etc...

MYCOL = 'ABC'
MYCOL = 'EFG'
MYCOL = 'HIJ'
etc...

Hope that makes sense.  Again, the only difference between each iteration is the schema name changes and the where clause changes.
I could probably write some Python script or something to generate the code but I'd rather do it in Postgres if possible.
I know this isn't valid code, but essentially this is what I am trying to do:
do $$
declare
    myschema varchar(10) := 'SCHEMA1';
    mywhere varchar(3) := 'ABC';
begin
    create or replace view @myschema@.myview as
        select * from SHAREDSCHEMA.sometable where MYCOL = @mywhere@;
end $$;

Then each time I run that, I simply change @myschema and @mywhere.
Any help would be appreciated.
Thanks!


texts: how to migrate seemlessly from information_schema to sys/performance_schema

texts:
I am migrating my mysql 5.5 application to 5.7.9.

One thing that I'm picking my head about, is mapping the new performance_schema to the old information_schema, specifically for the following queries:

SELECT * FROM `information_schema`.`tables`;
SELECT * FROM `information_schema`.`columns`;
SELECT * FROM `information_schema`.`key_column_usage`;
SELECT * FROM `information_schema`.`schemata`;

Any idea where to find these ddl fields?


texts: Passing in multiple instances into the dbatools command GET-DBADBFILE

texts:
I am attempting to gather all of the dbfile information on all of our sql instances in one of our environments. It is a fairly large environment and would be tedious to perform one instance at a time. I thought there would be an easy way to do this with Powershell but I seem to be running into a roadblock.
I have a list of instances stored in a table/view [vSQLInstances]. If I pass those values into a variable and then use that variable as the SQLInstance parameter, I get an error.
Script I'm using...
$sqlinstance = Invoke-DbaQuery -SqlInstance dbasql01\sql2019 -Database DBAEstate -Query "select sqlinstance from vSQLInstance;"

Get-DbadbFile -SqlInstance $sqlinstance | Select-Object sqlinstance, database, logicalname, size

The error I receive:
Get-DbaDbFile : Cannot process argument transformation on parameter 'SqlInstance'. Cannot convert value
"@{sqlinstance=PRODSQLD\D}" to type "Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter". Error: "Failed to interpret
input as Instance: @{sqlinstance=PRODSQLD\D}"
At line:11 char:28
+ Get-DbaDbFile -SqlInstance $instancelist
+                            ~~~~~~~~~~~~~
+ CategoryInfo          : InvalidData: (:) [Get-DbaDbFile], ParameterBindingArgumentTransformationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,Get-DbaDbFile


texts: Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

texts:
I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables but writing on different output tables. Inside my transactions I have several DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION statements that declare some functions.

My guess is that DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION are locking. Am I right? I can solve the problem by moving the statement outside the transaction.

I've observed the following: two transactions A and B contains some DROP FUNCTION and CREATE OR REPLACE FUNCTION; if I execute transaction B while transaction A is still running, transaction B waits on the first DROP FUNCTION statements it encounters.


texts: Nested query not recognize external table

texts:
i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount.

I've made this query but the problem is the inner select does not recognize the w1 table.

MySql give me this error:

#1054 - Unknown column 'w1.Year' in 'where clause'

SELECT w1.CodC, c.NameC, w1.Week, w1.Year, (
    SELECT @rn:=@rn+1
    FROM(
        SELECT w2.CodC as Code, Sum(w2.Imports) As Amount
        FROM Weekly w2
        WHERE   w2.Year=w1.Year AND
                w2.Week<=w1.Week
        GROUP BY w2.CodC
        order by Amount DESC
    ) tab1,
    (
        SELECT @rn:=0
    ) tab2
) AS RkAmount
FROM Weekly w1
LEFT JOIN Companies c on c.CodC=w1.CodC
ORDER BY w1.Year DESC, w1.Week DESC, RkAmount ASC

How can i proceed?

Thanks a lot

Edit

The structure of Weekly table is:

CodC - Week - Year - Imports

and contains one entry for each society for each week if the import is >0.

Table Companies is only a Company description (in this query usefull only for name decoding).

And finally what i want is to Rank Companies by Imports sum from the beginning of the year.

Here an example:

CodC - W - Year - Import
AAAA - 1 - 2014 - 1000
BBBB - 1 - 2014 - 500
AAAA - 2 - 2014 - 700
BBBB - 2 - 2014 - 200
CCCC - 2 - 2014 - 2000

What i wanna output is

CodC - NameC - W - Year - RkAmount
CCCC - CompC - 2 - 2014 - 1
AAAA - CompA - 2 - 2014 - 2
BBBB - CompB - 2 - 2014 - 3
AAAA - CompA - 1 - 2014 - 1
BBBB - CompB - 1 - 2014 - 2

I hope this example has been more explanatory.


texts: Silently corrupted table after power-related shutdowns, records missing, *scrambled*?

texts:
Have a ssvr 2008 db, about 10gb. About 50 tables, 5 or so are frequent read/write, the rest are weekly write (append only), occasional read.

The server came up and down a couple times over the past week due to Sandy (gracefully, from what I can tell from the logs, it's on a UPS). Today, it was noticed a lot of records in a single table (one of the weekly write ones) were missing (everything from september forward to the first write after the last power issue). The newest records added after the last restart are fine.  That's problem 1.

The arguably weirder problem is that the surviving records before september are mostly correct, except for the data in two columns (nvarchar(50) and nvarchar(100)), which has been uniformly replaced with dates (YYYY-MM-DD) and bytes (ranging from 0 to 192). The dates and bytes don't have much relation I can see to the data that should be there or the other columns in that table (the dates are all "sensible", though, between 2012-02-27 and 2012-09-09).

I'm fetching backups for before and after each outage (simple recovery, hourly full backups) so I should be able to recover, but I'm a bit disturbed.

Main concerns:

How'd this happen without any events in the log or CHECKDB errors?
What could cause this in general?
Is there any way I can determine if other tables have been similarly corrupted in ways that may not have been as obvious as the first?


texts: Comparing sum against the average of all sums

texts:
I am looking for a way to compare the payroll of a team compared to the average of other teams in the same league.  Here is the table structure, three standard tables and two connecting tables to address my many to many relationships.  Using PostgreSQL.

tblPlayer
PNAME    PSALARY
player1  100000
player2  110000
player3  200000
player4  300000
player5  210000
player6  120000

contblPlayerTable

ID       PNAME       TNAME
1        player1     teamA
2        player2     teamA
3        player3     teamA
4        player4     teamB
5        player5     teamB
7        player6     teamC

tblTeam

TNAME
teamA
teamB
teamC

contblTeamLeague

ID       TNAME       LNAME
1        teamA       leagueA
2        teamB       leagueA
3        teamC       leagueB

tblLeague

LNAME
leagueA
leagueB

I need to calculate each teams payroll that belong to leagueA so that we can determine the average total team payroll for leagueA

I also need to calculate the difference for teamA's payroll compared to the leagueA average.

Final output should be a $amount that is (teamA payroll (410,000) - leagueA average (410,000+630,000)/2=520,000) = -110,000

This shows that teamA has a total payroll that is 110,000 below the leagueA average.

Is something like this possible?


texts: SQL Server Partition. How would splitting range with Guid work?

texts:
I have a very large table in my database, and it has a GUID column called Group_Id.  This Group_Id has a pretty even split of data, and it would be beneficial to partition the table by this column.

I've created the partition function and scheme, and whenever a new Group is added to the table, I split the Range on the new Group_Id.  Normally, this operation goes quickly.  However, I've noticed that while the table is in use (I do jobs that do a lot of reads/writes at once), the partition split takes a LONG time.  How can I make the SPLIT RANGE operation go faster?

I know conventional wisdom says that I should be incrementing ints, and splitting right on the new int Id.  Would this solve my problem?

What is really happening when I do the split? I thought since all the Guids live on a border in the partition, it wouldn't have to move any data, it would just have to insert the new range?n


texts:
I'm struggling with setting up mirroring with the following configuration:

Server 1 running SQL Server Standard 2008 R2, Principal
Server 2 running SQL Server Standard 2008 R2, Mirror
Server 3 running SQL Server Express 2008 R2, Witness

I followed the tutorial here from Microsoft twice now to try and eliminate user error to no avail.

The issue that I am facing is that I get as far as setting the Principal as the partner to the Mirror like so which executes correctly and succeeds:

ALTER DATABASE Tools
    SET PARTNER = 'TCP://***:7022';
GO

I then go to set the Mirror as the partner to the Principal like so and get the error message The server network address “TCP://***:7022″ can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

The ports are definitely accessible and I've tested these with telnet with no issue (note the *** are a placeholder(!)).

I'm using certificates as per the above guide which are all setup correctly and each ENDPOINT is running as expected.

The only thing I can thing of is that the after restoring the full backup and transaction logs, the database on the mirror is still showing restoring but hangs like that for an extremely long time.

Is this reason for the Principal not accepting the Mirror because the Mirror has not finished restoring (I'd assume this is not the case as the restore was executed as NO RECOVERY) or am I missing something here?


texts: How to optimize indexes on MySQL query with various sorts

texts:
I have an INNODB table levels:

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | int(9)       | NO   | PRI | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_id            | int(10)      | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(5,4) | NO   |     | 0.0000  |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | NO   |     | NULL    |       |
| playable_character | int(2)       | NO   |     | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |
+--------------------+--------------+------+-----+---------+-------+

There are ~4 million rows. Because of the front-end functionality, I need to query this table with a variety of filters and sorts. They are on playable_character, rating, plays, and date_published. The date_published can be filtered to show by the last day, week, month, or anytime(last 3 years). There's also paging. So, depending on the user choices, the queries can look, for example, like one of these:

SELECT * FROM levels
WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()
ORDER BY date_published DESC
LIMIT 0, 1000;

SELECT * FROM levels
WHERE playable_character = 4 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 WEEK) AND now()
ORDER BY rating DESC
LIMIT 4000, 1000;

SELECT * FROM levels
WHERE playable_character = 5 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 MONTH) AND now()
ORDER BY plays DESC
LIMIT 1000, 1000;

I should add that rating and plays are always queried as DESC. Only date_published may be either DESC or ASC.

I started out with an index idx_date_char(date_published, playable_character) that worked great on the first example query here. Based on some other answers, I changed to two other indexes (date_published, playable_character, plays) and (date_published, playable_character, rating).

The first query still runs very fast, however there's some unusual things happening in EXPLAIN, when player_character = x exceeds a certain number of rows (~700,000): the USING WHERE pops on in EXPLAIN.

So, first question is are there any improvements in the query or indexes possible, and, second, what MySQL settings should get altered to allow for the large result sets.

Any suggestions greatly appreciated. TIA.


texts: How to get the port of a connection in mysql

texts:
I can do:
select * from information_schema.processlist plist where id=connection_id();

To get the data from the current connection I am on, this will give me something that looks like:
# ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
'6558851', 'avails', '99-29.lit:55353', 'aQvails', 'Query', '0', 'executing', 'select * from information_schema.processlist plist where id=connection_id()'

And the port in there is 55353. Is there a way to grab the port directly, for example something like:
SELECT connection_id(), user(), port_id(??)


texts: Measuring Availability Group synchronization lag - Clarifications

texts:
Can I get some help on below

As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does this query or dashboard show the values including FileStream synchronization lag ?

My Databases are with FileStream enabled .
I am using SQL Server 2014 SP2 AOAG.

Please help me understand the above point and kindly advise me with good link or query to measure the Sync lad between replicas.


texts: Select rows with most recent date for the given date

texts:
I have a table to manage the stock which looks like below

product    batch   qty  date
Grape      BAT0    42   10/3/2016
Apple      BAT1    12   11/3/2016
Banana     BAT2    15   11/3/2016
Orange     BAT3    24   11/3/2016
Pine       BAT4    28   11/3/2016
Apple      BAT1    9    12/3/2016
Banana     BAT2    10   12/3/2016
Pine       BAT4    25   12/3/2016
Apple      BAT1    5    13/3/2016
Banana     BAT2    6    13/03/2016
GreenApple BAT5    15   13/03/2016

If I pass the date 12/03/2016 then I need the last dated row to return as below

product batch   qty date
Grape   BAT0    42  10/3/2016
Orange  BAT3    24  11/3/2016
Apple   BAT1    9   12/3/2016
Banana  BAT2    10  12/3/2016
Pine    BAT4    25  12/3/2016

Also I have more than 160,000 rows in that table and it will grow each day.

How can I write an optimised query?


texts: Recover deleted records from filestream-enabled table

texts:
I have a SQL Server database that I wrongly executed a delete script on it that removed an important records from a filestream-enabled table called Images.

after the execution immediately I created a backup, now this backup contains a big .LDF log file and also the filestream folder container contains the files that its records were removed.

Using the Dedicated Admin Connection I were able to see the file stream tombstone tables, for the backup I have the filestream tombstone table contains a lot of records which I think indicates to the removed records, but am I able to recover the whole rows or to get the row ID from the information provided in this table ?

The following data are provided by the tombstone table :

oplsn_fseqno
oplsn_bOffset
oplsn_slotid
file_id
rowset_guid
column_guid
filestream_value_name ---> I used this value to go to the physical file in the filstream container and its existed.
transaction_sequence_num
status

Using a recovery application I were able to recover almost all the records in the other tables, even the Images table rows restored, but the Data column which is the filestream  column always contains 48-byte long binary data that you can see an example of them from here, I'm wondering how can I use this data to link them to a "tombstone" row or maybe to get the their file name in the filestream container file ?


texts: Large time difference between two almost identical simple queries....why?

texts:
I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:

SELECT m.Metadata, COUNT(*) As Count
FROM TWG10MinData twg, Metadata m
WHERE twg.metadata = m.Metadata and (m.Metadata % 1000) = 100
GROUP BY m.Metadata
ORDER BY m.Metadata

Some statistics for this query are: 67000 logical reads, 3000 ms CPU time, 800 ms time elapsed.

SELECT Metadata, COUNT(*) As Count
FROM TWG10MinData twg
WHERE Metadata IN (1100,2100,3100,4100)
GROUP BY Metadata
ORDER BY Metadata

Some statistics for this query are: 20 logical reads, 0 ms CPU time, 0 ms time elapsed.

Table "twg" has a nonclustered index on "Metadata" field (its primary key fields don't appear on the query). Table "Metadata", has its field "Metadata" as primary key and therefore it has a clustered index on it.
As you can see, the only difference is specifying the concrete values that, at the moment, result from this computation "(Metadata % 1000) = 100". Shouldn't SQL engine compute first the valid values for Metadata and then apply the filter? Is this so much time consuming to cause such a big difference in their performance numbers?

Thanks in advance!!


texts: Set search path for Postgres in a Rails app

texts: I have a project in heroku. I have the URI that enables me to connect with the database in my external rails app. How do I change or set the search path in ruby code?


texts: Error installing SQL Server Database Engine Services Instance Features - The User Log directory in the registry is not valid

texts:
We have a SQL Server:

Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1 (X64)   Mar 22 2019 22:33:11  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

Tried to install "Cumulative Update Package 18 for SQL Server 2017 - KB4527377"
https://www.microsoft.com/en-us/download/details.aspx?id=56128
Setup was doing something for 15 min, installing and updating files, stopping / starting services, it looked like its going to succeed, but in the end it showed this:

Action required:
Use the following information to resolve the error, and then try the setup process again.
Feature failure reason:
An error occurred during the setup process of the feature.
Error details:
Error installing SQL Server Database Engine Services Instance Features
The User Log directory in the registry is not valid.
Verify DefaultLog key under the instance hive points to a valid directory.
Error code: 0x851A0044
Visit https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.3257.3&EvtType=0xD8FB5EBA%400x97A656BB%401306%4068&EvtType=0xD8FB5EBA%400x97A656BB%401306%4068 to get help on troubleshooting.

What's preventing CU18 from installing ?


texts: How to determine when to reduce physical memory for SQL Server?

texts:
If I repeatedly notice SQL Server reporting a lot of free memory, can I likely remove about that amount of memory from the server without affecting performance? Or, can/should it use more for caching?

For this particular example, the server is running SQL Server 2012 Standard Edition on Windows Server 2012. It has 20 GB of physical memory and hosts over 100 GB of data.
SQL Server is the only application running on the server.

SQLServer;Buffer Manager
    Buffer cache hit ratio       99.737 %
    Page life expectancy            874 s
SQLServer:Memory Manager
    Database Cache Memory         6,744 MB
    Free Memory                   5,937 MB
    Optimizer Memory                  5 MB
    SQL Cache Memory                 29 MB
    Target Server Memory         19,015 MB
    Total Server Memory          18,407 MB


texts: XtraBackup Restore Full Backup OK but I can't initialize mysqld

texts:
I'm using XtraBackup mode Streaming and Compressing Backups to perform my backup on a MariaDB 10.19:

$ innobackupex --user=$db_user --password=$db_pass --compact --stream=tar ./ --no-timestamp | gzip - > $WORKING_DIR/$BCK_FILE

All seems ok even when I restore the backup, innobackup output shows:

...
innobackupex: Finished copying back files.

160801 13:52:31  innobackupex: completed OK!

but when I start the mysqld I find the next message in its log:

2016-08-01 15:52:33 7f08d8ed87c0 InnoDB: Error: page 371 log sequence number 2340146
InnoDB: is in the future! Current system log sequence number 2337804.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
...
a lot of times!

In some cases I can't initialize the mysql.

On the page 23 of the manual 'PerconaXtraBackup-2.1.9.pdf' you can read:
'Note that the streamed backup will need to be prepared before restoration. Streaming mode does not prepare the backup.'

I have no idea of how prepare the backup before the restoration in this situation.
In the manual explains how prepare it using --apply-log argument but the backups aren't performed using stream and compressing mode.

Anybody knows how prepare it correctly?

How can I retore it?

Should I use the --apply-log to prepare it? How?

Thanks for your time.

Best rgrds,

Boadrius


texts:
Our site goes down because the IO on our database servers is too slow.

Troubleshooting this we found that mysql is really really slow. It's constantly reading from disk and I think not using enough memory. It's using about 30G when it usually uses about 100G.

Here is the pt-mysql-summary https://gist.github.com/simao/4e809db0aa3a588764e2

What can I check to see what is causing massive IO reads from mysql?

How can I find out why mysql is so slow and using so much disk IO?

Thank you

Update:

This is the current iostat https://gist.github.com/simao/3657e6ac9fab0376acc0

But we are up at the moment and when we are down IO is 100% and disk reads are really slow.

We are using InnoDB with SSD storage on a dedicated machine.


texts: SQL Job Fails Logging In to Integration Services

texts:
I have a SQL Server 2012 where all databases are set up in instances.

I created a DTS package that accesses a table in a database, let's say MyDB\MyInstance and CSV files in a network folder.

Integration Services is NOT running in an instance.

My domain account doesn't have rights to login to Integration Services, so the DBA used his account and deployed the DTS package to Integration Services MSDB relying on the package for access and it runs from there without any issues. The DTS package uses my domain account to login to the database and access the table.

Then I created a SQL Job to run the package. It didn't run, of course. I create a proxy account to run the package. The proxy account uses the credentials of my domain account (which doesn't have access to SSIS, but the DBA temporarily made it a local admin on the SQL Server assuming that would give it the rights that it needed but that didn't work either. The error message states that it can't access the Integration Services database.

We gave the proxy SQL Agent operator role so we run the job with it.

The account running the SQL Agent for the instance is NT Service\SQLAgent$MyInstance

I don't know what else to try.

If there's any information missing please let me know. I apologize, there are a lot of moving parts.


texts: MySQL InnoDB Configuration with 60Gb of Memory

texts:
I'm running a large MySQL Database, in the range of 30Gb in size. The MySQL 5.5 server is running on a dedicated server with 60GB of RAM, with no other processes installed apart from monitoring tools, on Debian Wheezy.

I've read around the best way to configure the my.cnf file and opted to increase

innodb_buffer_pool_size = 40G

Adding that to the [mysqld] section of the my.cnf file resulted in the MySQL service failing to reboot. Error log shows no errors. Removing the variable change makes the server load properly.

Am I missing something or doing something that's not supported? If I am, can you point me towards the right direction on how to best optimise the MySQL configuration to make best use of the given memory and CPU processing power (16 CPU Cores). I've looked around but could only find information related to 4 or 8Gb of memory.

my.cnf file contains the following

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /var/lib/tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 0.0.0.0

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

#innodb_buffer_pool_size = 45G

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

Memory free on the server when MySQL is switched off is 58GB


texts: Deleting Billion records in a range vs exact ID lookup MYSQL

texts:
I have a database table which is around 700GBs with 1 Billion rows, the data is approx 500 GB and index is 200GB,
I am trying to delete all the data that's before 2021,
Roughly around 298,970,576 rows in 2021 and remaining before this are 708,337,583 rows.
To delete this I am running a non-stop query in my python shell
DELETE from table_name where id < 1762163840 limit 1000000;
id -> 1762163840 represent data from 2021. Deleting 1Mil row taking almost 1200-1800sec.
Is there any way I can speed up this because the current way is running for more than 15 days and there is not much data delete so far and it's going to do more days.
I thought that if I make a table with just ids of all the records that I want to delete and then do an exact map like
DELETE from table_name where id in (select id from _tmp_table_name);
will that be fast? and will it going to be faster than first making a new table with all the records and then deleting it?
The database is setup on RDS and instance class is db.r3.large 2 vCPU and 15.25 GB RAM, only 4-5 connections running


texts: Insert Only first Occurence in Oracle table

texts:
i have a table

CREATE TABLE PEOPLE
(
NAME VARCHAR(64 BYTE)PRIMARY KEY NOT NULL,
TIME TIMESTAMP(6),
NICKNAME VARCHAR2(16 BYTE),
STATE VARCHAR2(50 BYTE),
CITY VARCHAR(128 BYTE)
)

I want to insert in this table values from other tables, but for the columns NAME, the reference table can have duplicate NAME, in my TABLE PEOPLE, i want it to INSERT only the first occurence of NAME, and discard when it already exist.
I tried this

INSERT
INTO PEOPLE( NAME, TIME,NICKNAME, STATE)
SELECT
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME
LEFT JOIN LIEN l ON l.NAME = p.NAME
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00'
order by p.TIME desc

But i still get oracle error : Duplicate Key Errors (ORA-00001)
.
Thx for any help


texts: Select unique value pairs that occur (count) more than once inside a table

texts:
How can I retrieve an row with unique value pairs that occur more than once...
Heres an example of what I'm what I am trying to..

SELECT
 column_fk_id1, column_fk_id2,
 COUNT(distinct column_fk_id1, column_fk_id2) AS unique_pair_repeats
FROM table
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING unique_pair_repeats > 2


texts: Understand responsibilities of database and backend

texts:
Still learning, but there is something I can't understand.
Some features of MySQL seem (to me) to flow on the responsibilities of the backend.
In particular, I'm talking about data validation (excluding relations) for example NULL/UNIQUE.
Now, I'd like to know why this duplicate? Is it a second layer? More in general, in a good application, do I have to count on the errors raised by the DB as an organic part of the application, or should the application be designed to avoid those errors and only count on them as an extrema ratio?


texts: Turn xact_Abort on for specific applications or user

texts:
We have a 3rd party application that requires xact_abort to be turned on, however we have other applications that require it to be turned off.

The service that requires it turned on always logs in with the same username and has its own application name.

Is there a way that we can configure the connection string to ignore the default setting and turn xact_abort on for it's connection? We don't have access to the source code of the application so are unable to add a command to set it to on.

The server is SQL 2012 Enterprise


texts: How to rollback transaction if row changed?

texts:
I am using Postgres 9.3.

When I update row in a table my flow looks like this:

Fetch old row (ex. SELECT * FROM tbl WHERE id = 1)
Validate new data in scope of old data (old row fetched in point 1) - this is done by my app outside of postgres.
Update row with new data (ex. UPDATE tbl SET .... WHERE id = 1)

The problem is that old data may change during point 2 so validation, even if passed may be outdated during point 3 (updating row). I know I could perform update like this UPDATE .... WHERE id = 1 AND column1='oldValue' to ensure certain fields didn't change, however my validation (also comparising new and old values) is very complex and I can't "write" it in SQL UPDATE statement.

So I thought about something like this:

BEGIN
SELECT * FROM tbl WHERE id = 1
Perform validation on my side
UPDATE SET=... WHERE id = 1
COMMIT

And I would like to COMMIT fail if row with id = 1 changed during transaction execution. Is it possible to use transaction like this? If not what is other solution?


texts: SQL Find Minimum Value Greater than Previous Row

texts:
How do I find the minimum value greater than the previous row?
This code needs to be optimized to run over million rows and business keys, we may have 3-10 timevalue columns at most. I am thinking its some type of recursive CTE.

background: conducting slowly changing dimensions for many tables which are being denormalized. And trying to find proper begin dates.

Data Sample:

create table dbo.timetest
(
TimeTestIdentityId int primary key identity(1,1),
businesskey int,
timevalue1 int,
timevalue2 int,
timevalue3 int
)

insert into timetest
values
(5,131,134,137),
(5,131,138,135),
(5,131,140,135),
(5,143,141,145),
(5,149,141,148),
(5,150,141,148),

(6,134,137,140),
(6,134,141,138),
(6,134,143,138),
(6,146,144,148),
(6,152,144,151),
(6,153,144,151)

Final Values:

,etc

Row are ordered by TimeValue1.

Select MIN value from multiple columns


texts: How to store massive quantities of machine generated data?

texts: I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances.  Our project will need to keep up to 10 TB of data (maybe more in the future) and serve queries fairly quickly.  I have been reading about columnular rdms' as well as nosql options, which all seem promising.


texts: Displaying null fields as 0 in mySQL select


texts: What is the difference between LOB and binary data in SQL Server?

texts:
What is the difference when speaking about 'LOB' and 'binary' data?

Is it the same, in terms of binary data being stored in a separate filegroup, or are there differences?


texts: Using the auto increment id from one query as the fk of the next insert

texts:
I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees.

I have two tables, both have auto incrementing ID's.

Table 1:
ID - auto
field - int
other field - varchar

Table 2:
ID - auto
Table1Id - FK
field - varchar

the first table stores an article and the 2nd stores the location of the images related to it.

For my form that uploads the data all information to be stored is taken.  I need to run two inserts simultaneously (which I realise is daft) using the same ID.

My plan(flawed) is to insert into table 1, get the id and then insert into table 2.

one of the problems if is a user accesses the site in the gap between the two inserts and sees a mess.

is there an easier way, I think I've made a mistake or I'm missing something obvious as it shouldn't be this difficult. :)


texts: Create employee rows for each date in date range

texts:
I have a list of employees.
I need to create output which is one row per date in a date range for each employee.  The date range will always be the last 15 days, so it is expected to have 15 rows for each employee.  A "few thousand" employees. This is SQL Server.

SAMPLE SOURCE EMP TABLE:
EMP1
EMP2

RESULT:
EMP1, JAN 1
EMP1, JAN 2
EMP1, JAN 3
EMP2, JAN 1
EMP2, JAN 2
EMP2, JAN 3


texts:
I'd like to be able to have this query running daily selecting only where central_enquiry.enquiry_time is in the current financial year. The financial year runs from 01/04/YYYY to 31/03/YYYY. Ideally this would be dynamic so that I wouldn't have to manually change the dates next April

SELECT
  central_enquiry.enquiry_number,
  central_enquiry.enquiry_name,
  central_enquiry.enquiry_time
FROM
  central_enquiry
WHERE
  central_enquiry.enquiry_time


texts: DB Restart Taking too long, site is down

texts:
I tried to restart MySQL through WHM and now its taking way too long to restart, the progress bar is still showing.

What can I do?

I can still access the server via SSH but I cannot connect to MySQL. This is urgent as my online store is now down.

Will restarting the HTTP Server (Apache) help?


texts: Applying patch oracle 11.2.0.4 on 11.2.0.2

texts:
We are going to apply Oracle patch 11.2.0.4 on Oracle 11.2.0.2. We have 11.2.0.4 patch. We are using (DBUA) for this upgrade. As DBUA will check the preupgrade requisites, we want to run preupgrade check manually.

Could you please help where we can find preupgrade check?
How to run this script?

From which ORACLE_HOME should we run this script, as we have two ORACLE_HOME directories?


texts:
I found this explanation of how prepared statements are compiled, but I'm still unsure.

What exactly is "compiled" in the context of a prepared statement?  Is a C compiler executed to create a custom executable?

If so, is that limited by prepareThreshold?

If not, what exactly is it compiled into, and are there any projects for converting prepared statements into executables?


texts: SPOOLing gives extra rows, copy-paste does not

texts:
I am using oracle sql developer. My table has 58484 rows, but exporting to a csv gives 58532 rows! First I used this command (and tried fiddling with the set options for a bit to see what happened):

set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
SPOOL "C:\USERS\JULIANCIENFUEGOS\DESKTOP\TBLFACILITY_SURVEY.CSV"
SELECT /*csv*/ * FROM TBLFACILITY_SURVEY;
ORDER BY FACILITYID
SPOOL OFF;

When I do a regular expression search in the csv for "\n\n" or "\n,,,," nothing comes up, so there are neither blank lines, nor any rows that are empty comma separated lists.

If I copy and paste the table into excel, I get the proper number of rows.

Could someone explain this?


texts:
I have a 200 GB SQL Server database (including a file stream) which is taking more than 20 hours to backup on a nightly basis. What is the most cost effective way of decreasing this time?
I could add more hardware. Are backups IO, CPU or memory bound?
Or do I need to isolate the database which is currently on shared infrastructure?
Or can I make some coding changes to remove file stream data into blobs?


texts:
I am running Hallengren's IntegrityChecks job which fails with the following error:

  CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'T:\SQL_Data\Example.mdf_MSSQL_DBCC9'.

SQL Server Agent service account is NT Service\SQLAgent$INSTANCENAME, which has full access to the T drive including all sub folders.

Any idea how to overcome this?

Thanks


texts:
Currently we are having problems with our backups, specifically disk space issues.  On investigation it seems the notification mail isn't being sent when the job fails.  This is a weird issue as a notification mail is sent when the job completes successfully.

Any ideas why this would be? We are using SQL Server 2008.


texts: Difference between request and session in sp_blitzwho?

texts:
I'm starting to use sp_blitzwho, and I was wondering about a few of the columns.

There are a set of columns that start request_ such as request_writes, and then a set of columns that start session_, such as session_writes. Could someone explain the difference between these?
There is a column called wait_info. This holds a wait time and type, that I assume is what the proc call is waiting on? Is this correct?

If so what is the top_session_waits column showing? This column often has a few Waits in there?


texts: MySQL subquery with WHERE filters not working as expected

texts:
I have a question/tagging system like StackExchange.  I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been taken offline or is suspended, then it is not included in the total number count.
Tables are:
QUESTIONS - includes fields: suspended and offline
TAGS - includes fields: tag_id and tag_name
TAGS_X - includes fields: tag_id, question_id
The query below almost works, but the subquery seems to return the total count of times the tag is used and the filtering on the suspended and offline fields is not functioning as I intended (does not seem to filter on those conditions).
SELECT tags_x.tag_id, tags.tag_name, tags_x.question_id, questions.suspended, questions.offline,
(select count(tags_x.tag_id) from tags_x WHERE tags_x.tag_id=tags.tag_id AND questions.suspended = 0 AND questions.offline = 0) num
from tags_x
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions ON questions.question_id = tags_x.question_id
WHERE questions.suspended = 0 AND questions.offline = 0

Below shows a typical result.  Tag 'a' actually shows up in 21 rows.  One row is filtered where the offline value is 1.  The subquery count is returning 22, but I really want it to show the filtered result of 21.  Seems like the WHERE/(offline, suspended) filters in the subquery are incorrectly applied.
Can you help me out to determine the correct way to do this?


texts: How to write concat query for multiple 'select into' statement in mysql stored procedures?

texts:
I am trying to assign values to variables in mysql stored procedures. Here is my code
declare count int;
declare ParamOrgId int;
declare isPremium int;

SET @sql = CONCAT(' select count(id) , orgid , (select id from org_subscription t where t.orgid=mainOrgID)  into ', @count,',',ParamOrgId ,',',isPremium ,' '
 from ', tableA );

PREPARE stmt from @sql;
EXECUTE stmt ;

SELECT concat('my val is ', ParamOrgId);
END


texts: Table Partitioning Using Inheritance and Triggers

texts:
I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I have created! I wrote the following SQL code:

DROP TABLE IF EXISTS title_basics_1874to1899;
CREATE TABLE title_basics_1874to1899 (
    CHECK (
        CAST(startyear AS INT) >= 1874
        AND
        CAST(startyear AS INT) <= 1899
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_1900to1949;
CREATE TABLE title_basics_1900to1949 (
    CHECK (
        CAST(startyear AS INT) >= 1900
        AND
        CAST(startyear AS INT) <= 1949
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_1950to1999;
CREATE TABLE title_basics_1950to1999 (
    CHECK (
        CAST(startyear AS INT) >= 1950
        AND
        CAST(startyear AS INT) <= 1999
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_2000to2115;
CREATE TABLE title_basics_2000to2115 (
    CHECK (
        CAST(startyear AS INT) >= 2000
        AND
        CAST(startyear AS INT) <= 2115
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_null;
CREATE TABLE title_basics_null (
    CHECK (
        startyear IS NULL
    )
) INHERITS (title_basics);

-- INDEXES
DROP INDEX IF EXISTS title_basics_1874to1899_index;
CREATE INDEX title_basics_1874to1899_index ON
title_basics_1874to1899 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_1900to1949_index;
CREATE INDEX title_basics_1900to1949_index ON
title_basics_1900to1949 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_1950to1999_index;
CREATE INDEX title_basics_1950to1999_index ON
title_basics_1950to1999 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_2000to2115_index;
CREATE INDEX title_basics_2000to2115_index ON
title_basics_2000to2115 USING BTREE(startyear);

-- TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION title_basics_partitioner()
RETURNS TRIGGER AS $$
    BEGIN
        IF ( CAST(NEW.startyear AS INT) >= 1874 AND CAST(NEW.startyear AS INT) <= 1899 ) THEN
            INSERT INTO title_basics_1874to1899 VALUES (NEW.*);
        ELSIF ( CAST(NEW.startyear AS INT) >= 1900 AND CAST(NEW.startyear AS INT) <= 1949 ) THEN
            INSERT INTO title_basics_1900to1949 VALUES (NEW.*);
        ELSIF ( CAST(NEW.startyear AS INT) >= 1950 AND CAST(NEW.startyear AS INT) <= 1999 ) THEN
            INSERT INTO title_basics_1950to1999 VALUES (NEW.*);
        ELSIF ( CAST(NEW.startyear AS INT) >= 2000 AND CAST(NEW.startyear AS INT) <= 2115 ) THEN
            INSERT INTO title_basics_2000to2115 VALUES (NEW.*);
        ELSIF (NEW.startyear IS NULL) THEN
            INSERT INTO title_basics_null VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Out of range year value.';
        END IF;
        RETURN NULL;
    END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS insert_title_basics_trigger on title_basics;
CREATE TRIGGER insert_title_basics_trigger
BEFORE INSERT ON title_basics
FOR EACH ROW EXECUTE PROCEDURE title_basics_partitioner();

Also, my parent table which is called title_basics has the following data types (SQL code below):

CREATE TABLE title_basics (
    t_soundex VARCHAR(7),
    startYear INTEGER,
    isAdult VARCHAR(1),
    genres VARCHAR(150),
    endYear INTEGER,
    primaryTitle VARCHAR(500),
    tconst BIGSERIAL PRIMARY KEY NOT NULL,
    runtimeMinutes VARCHAR(10),
    titleType VARCHAR(15),
    originalTitle VARCHAR(500)
);

My goal is to have the data from the parent table (title_basics) move over to the children table based on the trigger, this must be achieved through a trigger or a rule. My code was based on this documentation: https://www.postgresql.org/docs/12/ddl-partitioning.html

PostgreSQL Version: 11


texts: Combine 4 rows based on common ID and Date

texts:
I have a query that returns 4 lines per ID. I would like to be able to merge these into one single row in a new table, based on the ID and Date.
Can anyone help? I've researched and seem can't find any examples.
Simplified version of how the data looks at the moment:

There's no more fields to identify if it's in or out. This is a log type database usually 4 entries a day per user id.


texts:
I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema:

Messages

_MessageID (PK)
_MessageParentID (if a reply this will equal MessageID)
_Body
_Date
_CreatorID (FK userID of the message creator)

MessageRecipients

_MessageRecipientID (PK)
_MessageID (FK to message)
_SendTo (FK UserID)

In short what I'm doing is creating a one to many relationship for messages. One user can send a message to many recipients. But the replies are one to one, in short message blasts are possible but there are no group conversations.

I created a SQL Fiddle to demo: http://www.sqlfiddle.com/#!6/6421e3/7

In the query I have now, I'm close. The User 101 should have 3 rows instead of the 4 showing. MessageID 4 is a reply to MessageID 3 (MessageParentID = MessageID) so I'm wanting the latest message to display and since the date of MessageID 4 is greater it should be displayed in the query. Think how FB Messaging works, you see the latest reply to a thread - not a separate outbox.

E.g. expected output:

3 rows

MessageID 1, 2, 4

Any help would be great.


texts: How to grant permissions to database but restrict to certain schemas?

texts:
I am new to database administration and work within a small team of data analysts trying to modernise. We have recently set up an Azure SQL DB server we are accessing using SQL Server Management Studio.

We have an admin account and I have created a user login for each of us for day-to-day work. I would like all user logins to have the ability to create, drop, select etc. freely for all objects where the schema is unspecified or specified as "dbo" but to only have read-only permissions where the schema is specified as  "testschema". I have attempted this using the following code from the admin account:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'testschema'))
BEGIN
    EXEC ('CREATE SCHEMA [testschema] AUTHORIZATION [dbo]')
END

---- Create permissions group:
EXEC sp_addrolemember 'team_member', 'Mel';

-- GRANT BLANKET PERMISSIONS
GRANT CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES, UPDATE ON SCHEMA::dbo TO team_member;

-- REVOKE PERMISSIONS TO SPECIFIED SCHEMAS
DENY CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES,
          UPDATE ON SCHEMA::testschema TO team_member;

-- ALLOW TO SELECT AND VIEW ON THESE SCHEMAS
GRANT SELECT, VIEW DEFINITION ON SCHEMA::testschema TO team_member;

I have been playing around with granting, revoking and denying permissions to myself all morning but I can't seem to get the permissions settings correct so that my user account can create and drop tables with the "dbo" schema and see and select from tables with the "testschema" schema, but can't drop/alter/delete tables with the "testschema" schema.

Can anyone advise on the correct syntax for doing this?

EDIT: I have altered my original code slightly to DENY rather than REVOKE and instead of general permissions I have set dbo-specific schema permissions. The permissions are given as follows and appear to be correct, but team_member users still can not see or select from any testschema tables (there are no other currently-used schemas beyond "testschema" and "dbo").


texts:
Given the following SQL code against the AdventureWorks Database:

begin transaction;

drop trigger Sales.iduSalesOrderDetail;
alter table Sales.SalesOrderDetail
    add LineNumber int null;
go

with sod as (
    select
        SalesOrderID,
            /* other cols */
        LineNumber,
        NewLineNumber = row_number() over
            (partition by SalesOrderID order by SalesOrderDetailID) + 1
    from Sales.SalesOrderDetail
    where SalesOrderID <= 44000)
update sod
set LineNumber = NewLineNumber,
    ModifiedDate = getdate()
output
    inserted.SalesOrderID,
    /* other cols */
    inserted.LineNumber;

select
    SalesOrderID,
    /* other cols */
    LineNumber
from Sales.SalesOrderDetail
where SalesOrderID <= 44000;

rollback transaction;

When running this code on SQL Server 2008R2, it runs fine; that is to say, it updates the data in Sales.SalesOrderDetail and outputs the results, which match the data from the following SELECT statement.

However, when running this code on SQL Server 2012, I get the message:

  Msg 4196, Level 16, State 1, Line 2
  Column 'deleted.NewLineNumber' cannot be referenced in the OUTPUT clause because the column definition contains an expression using a window function.

This is odd because I am not referencing deleted.NewLineNumber in any way.  I have attempted removing various OUTPUT columns and get the same result.  The only two ways I have found to stop the error have been to

make the NewLineNumber column be exactly the result of the row_number() (that is to say: NewLineNumber = row_number() over (...)); or
remove the OUTPUT clause altogether.

Is this possibly a bug in how SQL Server is processing the expressions in the CTE? Has anyone else run into this?

I'm working on possible workarounds, but I am curious about this.


texts: Is Log Shipping or Mirroring a better solution for Disaster Recovery?

texts:
I am planning an index rebuild, however my logs are prone to huge overblown sizes.

In fact I was leaning towards doing an intelligent re-indexing, but since the fragmentation levels are greater than 30% I thought I should do a rebuild.

My databases use the full recovery model.  Should I change the recovery model to Bulk logged before I do the rebuild later this weekend when there are less transactions happening.

Log shipping is enabled as a DR solution in these databases.

What do you suggest if my latency is quite high? Is there a better DR solution that I should look into?


texts: SSAS cube fact and dimension relationship

texts:
I have a cube that all the tables has one row per ID and have measure in all of them except for the calendar dimension.

When I view the measures in excel from Table 1 with attributes from table 2, I have no issues, but when I view the measures from Table 2 with attributes from Table 1 it returns the same number for each value of the attributes:

BillingCycle        HCD Accounts For Review
7                     4067
14                    4067
30                    4067

How can I overcome this issue? Does this have anything to do with the direction of the relationship between the tables?


texts:
I had installed Oracle 12 to my Linux box. I tried to login using sqlplus but it prompt out this error at below. How to solve it?

  Enter user-name: SYSDBA

  Enter password:  ERROR: ORA-01034: ORACLE not available

  ORA-27101: shared memory realm does not exist

  Linux-x86_64 Error: 2: No such file or directory

  Additional information: 3701

  Additional information: 542678011

  Process ID: 0 Session ID: 0 Serial number: 0

EDIT:
How to find out default ORACLE_SID?


texts: I stuck on the network's settings with Griddb on my ubuntu

texts:
Hostname -i
I got 127.0.1.1
with this command ip -f inet -o addr show eth0 | cut -d' ' -f 7 | cut -d/ -f 1
I got Device "eth0" does not exist
So what do you suggest to fix it? or should I continue to use the ip 127.0.1.1?


texts: Can CLR execution continue after 'THROW' is encountered in T-SQL?

texts:
I am calling a T-SQL stored procedure from a CLR stored procedure. and the THROW statement in T-SQL stops the execution of CLR without going into the catch block. Is there something I can change in my code to cause the CLR procedure to execution the catch block?

T-SQL:

CREATE TABLE t1 (ID INT PRIMARY KEY clustered);
CREATE TABLE t2 (ID INT CONSTRAINT fk_1 FOREIGN KEY REFERENCES t1(ID))
GO

CREATE PROC dbo.TestThrow
AS
BEGIN
BEGIN TRY
    INSERT INTO t2 VALUES (1)
END TRY
BEGIN CATCH
    THROW
END CATCH
END
GO

CLR:

    public static void TestThrow()
    {
        String query = "EXEC DBO.TESTTHROW";
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            try
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        SqlContext.Pipe.Send("no error");
                    }
                }
            }
            catch (SqlException e)
            {
                SqlContext.Pipe.Send("sqlexception");
                SqlContext.Pipe.Send(e.Message);
            }
            catch (Exception e)
            {
                SqlContext.Pipe.Send("exception");
                SqlContext.Pipe.Send(e.Message);
            }
        }
    }


texts:
We are seeing parallelism causing worker thread shortage in SQL,while investigating we found session id is null for some sessions,Below is the query used.What does this session id null means ?

select session_id,count(*) as 'totalthreads' from sys.dm_os_tasks
group by session_id
having count(*)>1
order by totalthreads desc

Output varies as below:


texts: MongoDB Ops Manager - CPU - No Data Available

texts:
I have what I would consider a default installation of the latest version of MongoDB Ops Manager and MongoDB.

Everything is working except the Hardware > CPU monitor for my server.

Attached screenshots of the configuration and the screen showing no data.

CPU not showing data

Server Info

Ops Manager Version - 3.4.6

Auth Schema - 5

Feature Compatability - 3.4

Advanced startup options (default) -

nojournal - false

logappend - true

syslog - false

slowms - 100

profile - 1

Log Path - /var/log/mongodb/mongod.log


texts: Factoring out subselect (with clause) ends in ORA-00904

texts:
I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again.

This is my base query which works quite well:

select tka.id, tka.name, txt.text as langname, einh.text as einheit, tka.typ,
  vgd.a_wert, vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
  inner join vgs vgs on
    vgd.vgs_id = vgs.id and vgs.schluessel = ?
  inner join tka tka on tka.id = vgd.tka_id
  left join (select txt0.* from txt txt0, spr spr
               where txt0.spr_id = spr.id and spr.name = ?) txt on tka.txt_id_langname = txt.id
  left join (select einh0.* from txt einh0, spr spr
               where einh0.spr_id = spr.id and spr.name = ?) einh on tka.txt_id_einheit = einh.id
  inner join mtdb mtdb on tka.name = mtdb.tedabattribut
               and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
  where tka.status = 2
    and tka.felddimension = 0
    and not exists (select 1 from vgd vgd2
                      where vgd2.vgs_id = vgd.vgs_id
                        and vgd2.tka_id = vgd.tka_id
                        and vgd2.ai_id > vgd.ai_id);

Now as you can see, I have to pass the value to  spr.name twice. I would like to avoid that. The query has been transformed to this for starters
EDIT The query has been rewritten to ANSI style as Leigh Riffel wrote but still no avail.

with spr0 as (select id from spr where name = ?)
select tka.id, tka.name, txt.text as langname, einh.text as einheit, tka.typ,
  vgd.a_wert, vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
  cross join spr0 spr
  inner join vgs vgs on
    vgd.vgs_id = vgs.id and vgs.schluessel = ?
  inner join tka tka on tka.id = vgd.tka_id
  left join (select txt0.* from txt txt0
               where txt0.spr_id = spr.id) txt on tka.txt_id_langname = txt.id
  left join (select einh0.* from txt einh0
               where einh0.spr_id = spr.id) einh on tka.txt_id_einheit = einh.id
  inner join mtdb mtdb on tka.name = mtdb.tedabattribut
               and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
  where tka.status = 2
    and tka.felddimension = 0
    and not exists (select 1 from vgd vgd2
                      where vgd2.vgs_id = vgd.vgs_id
                        and vgd2.tka_id = vgd.tka_id
                        and vgd2.ai_id > vgd.ai_id);

But SQL Developer is still giving me

ORA-00904: "SPR"."ID": ungültiger Bezeichner
00904. 00000 -  "%s: invalid identifier"

Table names have been shortened for brevity.

EDIT 2: I have followed miracle173's advice, reduced the query to a mininum and ran with SQL*Plus. Here is the output:

select spr.*, txt.*, vgd.*
from vgd vgd
  cross join spr spr
  inner join tka tka on tka.id = vgd.tka_id
  left join (select txt0.* from txt txt0
               where txt0.spr_id = spr.id) txt on tka.txt_id_langname = txt.id
  where vgd.vgs_id = 32 and spr.name = 'de';

FEHLER in Zeile 6:
ORA-00904: "SPR"."ID": ungültiger Bezeichner

The error is in line 6. So there must be some issue with the cross join and the alias.

Is anyone able to spot the missing link? Where is the flaw in my idea?

Solution: Thanks for Leigh's tip to outfactor table spr I have a working solution now:

select tka.id, tka.name, txt.text as langname, einh.text as einheit,
  mtdb.camosmerkmal as merkmal, tka.typ, vgd.a_wert, vgd.i_wert,
  vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
  cross join spr spr
  inner join vgs vgs on
    vgd.vgs_id = vgs.id and vgs.schluessel = ?
  inner join tka tka on tka.id = vgd.tka_id and tka.status = 2
                        and tka.felddimension = 0
  left join txt txt on tka.txt_id_langname = txt.id and txt.spr_id = spr.id
  left join txt einh on tka.txt_id_einheit = einh.id and einh.spr_id = spr.id
  inner join mtdb mtdb on tka.name = mtdb.tedabattribut
               and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
  where spr.name = ?
    and not exists (select 1 from vgd vgd2
                      where vgd2.vgs_id = vgd.vgs_id
                        and vgd2.tka_id = vgd.tka_id
                        and vgd2.ai_id > vgd.ai_id);


texts: Oracle 10g: Optimizing an update query that uses a subquery

texts:
I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.

  UPDATE TBL_REPORTING REP
  SET    (REP.ISSUER_ID, REP.ISSUER_NAME
         ) = ( SELECT DISTINCT TMP.ISSUER_ID
                       ,TMP.ISSUER_NAME
                FROM   TMP_REPORTING TMP
                WHERE  TMP.CMD_KEY = REP.CMD_KEY
                AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            )
  WHERE  REP.CMD_KEY = v_cmd_key;

TBL_REPORTING has 332124 rows in total.
TMP_REPORTING has 55589 rows in total.

I have tried rewriting the subquery using the WITH clause, but I can't get it working when there's 2 columns in the SET statement. I was reading that perhaps I could use the MERGE statement, but I haven't been able to get a working query yet. Ideas?

Update:

I managed to rewrite the query using the MERGE statement, but I still need to check if the results are the same:

MERGE INTO TBL_REPORTING REP
USING (SELECT DISTINCT TMP.CMD_KEY, TMP.REPORTING_KEY, TMP.ISSUER_ID, TMP.ISSUER_NAME
            FROM TMP_REPORTING TMP
            WHERE TMP.CMD_KEY = v_cmd_key) comp
            ON (REP.CMD_KEY = comp.CMD_KEY AND REP.REPORTING_KEY = comp.REPORTING_KEY)
            WHEN MATCHED THEN UPDATE SET ISSUER_ID = comp.ISSUER_ID, ISSUER_NAME = comp.ISSUER_NAME;

Update 2: Results are not the same. The UPDATE statement updates 40 more rows than the MERGE statement.

Execution plan for update:

Plan
UPDATE STATEMENT  ALL_ROWSCost: 644,432  Bytes: 322,065  Cardinality: 7,157
    6 UPDATE TBL_REPORTING
        2 TABLE ACCESS BY INDEX ROWID TABLE TBL_REPORTING Cost: 302  Bytes: 322,065  Cardinality: 7,157
            1 INDEX RANGE SCAN INDEX IDX_TBL_REPORTING_R4 Cost: 8  Cardinality: 7,157
        5 SORT UNIQUE  Cost: 44  Bytes: 45  Cardinality: 1
            4 TABLE ACCESS BY INDEX ROWID TABLE TMP_REPORTING Cost: 43  Bytes: 45  Cardinality: 1
                3 INDEX RANGE SCAN INDEX TMP_REPORTING_13R2 Cost: 10  Cardinality: 6,949

Table TBL_REPORTING has indices:
  PK_TBL_REPORTING on REPORTING_KEY and
  IDX_TBL_REPORTING_R4 on CMD_KEY.

Table TMP_REPORTING has:
  an index on CMD_KEY,
  but not on REPORTING_KEY.


texts: Int for identity/primary key and Guid for public identifier, best practices?

texts:
I almost always use an int for identity/primary (auto incrementing) clustered key for my tables. They're easy to query and debug, bounce around in SSMS, and plenty of other reasons.

I have a case where I'm creating an application that has your typical CRUD interface (create, read, update, delete) and I want to use a Guid in the url instead of the int.

An example table would be:

CustomerId int identity(1,1) PK clustered
CustomerName nvarchar(100)
Guid uniqueidentifier

In the past when someone was viewing a detail record I would just pass the identity:

/app/details/1 <-- id of the record

As I've seen in many applications, I like the idea of this:

/app/details/guidinstead

So in short, an int as the identity, and a guid as the public facing key.

What I'm concerned about is performance if any given table using this method grows substantially. There are business reasons for this, as in a person that has access can share a particular link with another to view details.

Another reason is because it does reduce some overhead on queries in a different manner, as in determining if the person has the "right" to view a particular record.

The initial hit is looking up the real ID of the record. After that all nested resources on the record are easily achieved with joins on the identity field across many tables.

I currently have no indexing set on the [Guid] uniqueidentifier column.

So to sum it up, I want to avoid showing incrementing keys in the URLs and I don't want a nightmare later on down the road if the tables end up with lots of data. Inserts and updates are currently using the int/identity column.

Operations are like this:

When creating a record, I use Guid.NewGuid() in .NET to generate the Guid column.
When updating a record, I find the CustomerId based on the Guid and update accordingly because this usually ends up updating/inserting in other relational tables as well and I need that CustomerId to do so.
When viewing the details of the record, it's WHERE Guid = @Guid
When deleting a record, it's WHERE Guid = @Guid

Questions:

Is it worth having both int and guid in the table or should I just drop the int entirely?
Any other settings I should place on the table?
Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

It's early stages so easy for me to make changes now.


texts: I have a .mdb file on my computer that I would like to convert into a MySQL Schema and I have found that there is a migration tool within Workbench. However it doesn't seem to allow you to connect to a .mdb instead it looks for a server. How should I either link to my file or host my file easily as I have not done this before.


texts: PostgreSQL - Out of memory on count group by query

texts:
I'm trying to execute the following statement in PostgreSQL database:

create table tmp_nodes as select node_id,count(way_id) as count
from way_nodes
group by node_id;

This query fails because the machine runs out of memory. (Unfortunately, I can't find the corresponding error in the log files; however, I can see memory usage steadily increasing and that the process terminates with not results.) This is probably because the result table is kept in memory and is written to disk after the query has been executed, count with group by. Note, the number of rows in way_nodes is about 2.5 billion:

EXPLAIN create table tmp_nodes as select node_id,count(way_id) as count
from way_nodes
group by node_id;
                                    QUERY PLAN
--------------------------------------------------------------------------------
 HashAggregate  (cost=53797289.60..54287411.12 rows=49012152 width=16)
    ->  Seq Scan on way_nodes  (cost=0.00..41209846.40 rows=2517488640 width=16)
(2 rows)

I was wondering if there is a good approach to get the query to be executed. The options I have thought of and I did some research about are:

Is there a query parameter, or something, that forces the query to write the result earlier to the disk, meaning to not keep everything in memory?
I found cursors as a way to do the same thing as (1), but manually. However, I am not sure how to do that. (If you think this is a good approach, can you show me some example code, either a link or in your response, please?)
Thanks to TomH's response: Saying 'set enable_hashagg = false;' gives a query plan as shown below. Does this force the query to be executed in pipelined mode. (Is there any hint in query plan?) If so, this solves the problem.

Query plan for (3):

explain create table tmp_nodes as select node_id,count(way_id) as count
from way_nodes
group by node_id;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 GroupAggregate  (cost=477330853.69..496702140.01 rows=49012152 width=16)
   ->  Sort  (cost=477330853.69..483624575.29 rows=2517488640 width=16)
         Sort Key: node_id
         ->  Seq Scan on way_nodes  (cost=0.00..41209846.40 rows=2517488640 width=16)
(4 rows)


texts: Cant Uninstall SP3 from SQL Server 2008r2

texts: I installed SP3 for sql server 2008r2 RTM release on windows server 2008r2. After testing i found some unknown issues in my application. Now i want to downgrade this database engine again into RTM release. I try control panel add/remove option but its remove all database instance. Please suggest


texts: Is there any performance difference between UNIQUE INDEX and INDEX with the same cardinality on MySQL?

texts:
I have a table that has id and created_at.
Column     | Non Unique | Cardinality
-------------------------------------
id         | 0          | 1000
created_at | 1          | 1000

id is unique, and created_at is non unique.
I want to know if there's any performance improvement if I convert the created_at index from non unique to unique.


texts: How to Limit DTU Percentage Consumption for a Given Session in Azure SQL Database?

texts:
We have an Azure SQL Database tier S4 (200 DTU), and there is one big table inside
    When I run a "alter index rebuild..." statement, it takes about 18 mins to complete

This particular nonclustered index is 5 Gb in size, and during those 18 minutes of rebuilding, it takes all 100% of DTU available to this database
Of course, this is impacting other applications that are trying to work with this database

Question:

How can we limit available resources for a given session that runs a rebuild ?
Say, we want to make it use only 50%-60% of Database's DTUs, but not 100%

Is this possible on Azure SQL Database ?

Regards,


texts: Mysql: Unable to drop a columns NULL default

texts:
Running mariadb 10.3

I have a bunch of columns that were created by default as NULL defaults. I was asked by a third party integrator to remove the NULL defaults, but so far have been unsuccessful.

I first do:

mysql> Describe Tablename;

I see my columns with Null defaults. e.g:

+--------------------------+---------------------+------+-----+---------+-------------------+
| Field                    | Type                | Null | Key | Default | Extra             |
+--------------------------+---------------------+------+-----+---------+-------------------+
| Tech ID                  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment    |
| State                    | tinyint(1) unsigned | NO   |     | 1       |                   |
| Ref ProdID               | int(10) unsigned    | NO   | MUL | NULL    |                   |
| Prenom                   | varchar(50)         | YES  |     | NULL    |                   |

Then I do:

mysql>ALTER TABLE Tablename ALTER COLUMN Prenom DROP DEFAULT;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

Describe again shows that the Null default has not been removed from column Prenom.

+--------------------------+---------------------+------+-----+---------+-------------------+
| Tech ID                  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment    |
| State                    | tinyint(1) unsigned | NO   |     | 1       |                   |
| Ref ProdID               | int(10) unsigned    | NO   | MUL | NULL    |                   |
| Prenom                   | varchar(50)         | YES  |     | NULL    |                   |

Is it even possible to do this?


texts:
Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES:

He gives some examples and etc. about INCLUDE, but I'm confused about the actual index he creates.

This is the query:

SELECT Id, DisplayName
FROM   Users
WHERE  Reputation > 400000;

What I would do is:

CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)

I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.

But Erik did this:

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )
 INCLUDE ( Reputation )
 WHERE Reputation > 400000
 WITH ( DROP_EXISTING = ON );

The problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?

And just for a simple test, running this query for a test in a table with millions of rows:

select COL1
      ,COL2
      ,COL3
from MyTable
    where COL2 > 4513516

SQL tells me to create this index:

CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
    (
        [COL2]
    )
INCLUDE
    (
        [COL1],
        [COL3]
    )
GO


texts: Seek predicate not using all available columns

texts:
I have a strange query compilation issue which is hard to reproduce. It only happens under high load and cannot be easily repeated.

There is a table T with columns A,B,C,D.
There is a non-unique clustered index on T(A, B, C, D).
There is a query SELECT * FROM T WHERE A=@P1 AND B=@P2 AND (C=@P3 OR
C=@P4) AND D=@P5. The seek condition is on all columns of the
clustered index, the 3-rd column has an OR.

The problem is that the query plan for this query has Seek Predicate only on A and B! The predicate on C and D is an ordinary predicate, so this means that search tree on columns C and D is not utilized.

The data types for all parameters match column data types.

Could anyone provide any hints on why this could be happening? SQL version is 2008 R2 (SP1) - 10.50.2789.0 (X64)


texts: Is MySQL more scalable than PostgreSQL due to the difference in how they handle connections?

texts:
I'm trying to decide if either MySQL or PostgreSQL would be more suitable for an application that will get hit by potentially thousands of simultaneous requests at a time.
During research, one fact that stands out is that PostgreSQL forks a new process for each connection, whereas MySQL creates a new thread to handle each connection.

Does this mean that MySQL is more efficient than PostgreSQL at handling many concurrent connections?

How much of an impact does this difference have on how well both systems scale? Is it something that I should worry about to begin with?


texts: Mongo shell 3.2 has different results on sh.isBalancerRunning() as mongo shell 3.4

texts:
I have a mongo shards cluster and a mongos instance. These servers are running on MongoDB 3.4.0. When I use mongo shell 3.4.0 to connect to the mongos, I got below output from sh.isBalancerRunning(),

mongos> sh.isBalancerRunning()
false

but I got a different result when I use mongo shell 3.2.13:

mongos> sh.isBalancerRunning()
true

Is this something not compatible between mongo versions? If yes, why other commands work fine just this command doesn't?


texts: Selecting the first purchase item of each customer

texts:
I have a table like this:

CREATE TABLE SALES
(
CID NUMBER(4),
PDATE DATE,
ITEMCODE NUMBER(4),
ITEMNAME VARCHAR2(20)
);

INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (1111,'17-JAN-2011',4002,'DRY FRUIT');
INSERT INTO SALES VALUES (1111,'20-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'03-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'04-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (2222,'05-FEB-2011',4005,'APPLE');
INSERT INTO SALES VALUES (2222,'06-FEB-2011',4001,'LUX');
INSERT INTO SALES VALUES (2222,'09-FEB-2011',4006,'ORS');

INSERT INTO SALES VALUES (3333,'03-FEB-2009',4007,'CHOCOLATE');
INSERT INTO SALES VALUES (3333,'09-MAR-2011',4001,'LUX');
INSERT INTO SALES VALUES (3333,'15-APR-2011',4008,'MILK');
INSERT INTO SALES VALUES (3333,'03-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (4444,'11-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (4444,'19-JAN-2011',4010,'SUGAR');
INSERT INTO SALES VALUES (4444,'21-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (4444,'05-JAN-2011',4005,'APPLE');
INSERT INTO SALES VALUES (4444,'15-JAN-2011',4001,'LUX');

INSERT INTO SALES VALUES (5555,'01-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (5555,'03-JAN-2011',4010,'TELENOR CARD');
INSERT INTO SALES VALUES (5555,'22-JAN-2011',4005,'APPLE');

I need to fetch the first purchased item name by all the id individually.

How can I do this?


texts: How to determine if an SSRS data driven subscription job has completed

texts:
I have a report that generates PDF's using a data driven subscription process.

When the job is started, it shows a success status shortly afterwards, however I believe that this is really asynchronous and therefore cannot be used as an indication of the jobs status. Is there a reliable indicator that can be used?

Ideally this would be something that would allow me to automate a check in a stored procedure.


texts: Why does SQL Server not use the Index it recommends?

texts:
The query

I had a specific query I wanted to optimize:

SELECT * /*12 columns*/
FROM [dbo].[EnterpriseGroup]
WHERE
    (EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234)
AND StatusCd >= 2

There is also already an index on FatherId, but not on the ChildId. The primary key is among the 12 selected columns, but none of those used in the WHERE clause here.

The use

This is a simple query but it's run very, very often during daily work. The table is also small, around 8000 rows.

The query is used to find groups of enterprises. There are about 2 Millions of enterprise entries, so only less than 0.5% have a matching group row and thus most of the time, no group will be found.

The recommendation

When using SSMS and inspecting the "Actual Execution Plan" it gives this plan:

The predicate shown is actually the WHERE clause.

And, it recommends to create an index, which is basically indexing on the WHERE clause and adding all the queried columns directly into the index. Seems not very clever to me but maybe this is what this questions is all about:

/*
Missing Index Details from SQLQuery6.sql .....
The Query Processor estimates that implementing the following index could improve the query cost by 68.9052%.
*/

/*
USE [...]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[EnterpriseGroup] ([StatusCd])
INCLUDE (....all the 12 queried columns......)
GO
*/

The result

After I create the recommended index I get the following plan:

It's not used at all! (And the Table Scan hover info is exactly the same, still having a copy of the WHERE Clause as predicate)

The Question

Why does SQL Server not use an existing index that the SSMS adviced to create?

If not, then what is the significance of a missing index recommendation in the execution plan of SSMS (SQL Server Management Studio) against a Microsoft SQL server?

The notes

Note: I am no DBA, but a software developer. I have read a bit into this, including: https://www.brentozar.com/archive/2013/07/dude-who-stole-my-missing-index-recommendation/ but I did not clarify to me.

Note: In case it matters:
 - SQL Server Version 11.0.7493.4, running on Windows NT 6.3.
 - Microsoft SQL Server Management Studio is Version 11.0.7493.4


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

texts:
While writing a query other day a thought came to me and have stuck in my mind.

What is preferable, first checking if a value for a unique column exists and then inserting  or insert and let db raise unique constraint error? Will it even matter?

Edit: As suggested below in answer that this issue depends on database I am adding tag postgresql.


texts: Table Permissions Sanity Check: GRANT not working

texts:
I have been googling this for a few months now and I am confounded why I have not been able to get a solid answer on this.

Goal: Grant several roles SELECT, INSERT, UPDATE, DELETE permissions.

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1"."new_table_name" TO "role1";

I run this SQL script with no errors returned, but when I attempt to run a simple

SELECT *
FROM schema1.new_table_name

I get a permission denied for relation returned.

Do you have any rules of thumb or recommended "further reading" for how to handle and understand Postgres permission and hierarchy setup for users?

I setup a role hierarchy that doesn't seem to be respected and now GRANT scripts don't seem to be working either


texts: Schema Master, Schema, Scheme - table name/design help

texts:
I’m stuck! 3 table names.

Schema Master, Schema, Scheme.

They are too confusing for me I always get them mixed up....

Any ideas on how to name better?

I also maybe designing this database incorrectly but I'm really struggling to come up with any other way to handle multiple schemas, especially when custom ones per team will start popping up.

I also want to try and be clever and maybe other the just industry sectors also include Country > Region A > Region B or maybe something like Equity > Global Equity > European Equity > Germany Equity.

I'm working in MSSQL.

Schema Master

BICs
GICs
ICB

Schema

GICS Level 1, parent - NULL
GICS Level 2, parent - GICS Level 1
etc...

Scheme

GICS, Level 1, energy, parent - NULL
GICS, Level 2, coal, parent - energy

etc

This is a diagram for GICS. What i'm trying to avoid is having to design/create a table for each schema and then tables for each level. I think it can be handled using the method above.


texts: PostgreSQL - how to generate an array containing column name and column data...?

texts:
I'd like to create a dictionary - an array - from a simple query, to include column names in output.

IE, I'd like to turn this:

SELECT id, last, first FROM names;

  id | last     | first
-----+----------+-------------------------
001  | Smith    | John

into:

{ "id": "001","last": "Smith", "first": "John" }

Yes, bonus points for the quotes and colons! Ha!

We do have this very interesting catalog function, which will return all column names into an array.

SELECT array_agg(column_name::TEXT)
FROM information_schema.columns
WHERE table_name = 'people';

How to aggregate (couldn't help myself) that into a regular query?


texts: Need to filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour)

texts:
I want filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour)
I want to fetch data from "ProDataTable" by in between Start Date and End Date with Interval of (1 Sec, 1Min, 10 Min, 1 Hour , 2 hour)

for Fetching data from Database i am using following query.

Select *
from ProDataTable
Where Date_Time between '2017-11-17 19:05' AND '2017-11-21 19:23'

So kindly let me know how i can filter out data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour).

I want result as follows
Exa : for 1 Min

Exa : for 1 1hour


texts: Restore all applicable files in a backup device in T-SQL

texts:
I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transaction log backup is added to the backup device.

When restoring this onto a new server using SSMS I first create a new database, then add the backup device and then restore from that backup device. I then have to manually select the applicable files:

The Full backup (always the first one in the list)
The latest differential backup
All transaction log backups that came after the last differential backup.

This works fine for the occassional restore, but I need to automate this in T-SQL.

There is some logic on which files to pick, but I don't even know how to get to the content of the backup device yet.

I would like to have a stored procedure that takes the name of the database and the backup device and restores the latest version. Anybody knows of such a thing?

Or do I miss something here and there is a simple command for this?


texts: Getting aggregate sub-values in aggregate query

texts:
I have a query that need to show aggregated values in an already aggregated outer query.

Consider a query that fetches total count of accounts being credited on a certain day.

I would like to display

total number orders
Total orders that are "PENDING"
Total orders that are "OPEN"

Here is the query that seems to make sense but as resultset show below, I am obviously not using the right approach. The "OPEN" column displays the entire total, the "PENDING" column stays NULL.

I feel I may be using the wrong approach, what are the options to have this work?

SELECT s.created, COUNT(s.id) 'total_accounts_credited', SUM(s.withdrawal) 'total_amount_credited',
(select count(s.id) WHERE s.status_id = 'OPEN')  total_open_credited,
(select count(s.id) WHERE s.status_id = 'PENDING') total_pending_credited
  FROM statements s
WHERE s.status_id in ('OPEN', 'PENDING')
GROUP BY
  YEAR(s.created),
  MONTH(s.created),
  DAY(s.created)
  ORDER BY s.created DESC
;

Results:

+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| created             | total_accounts_credited | total_amount_credited | total_open_credited | total_pending_credited |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| 2019-01-19 00:00:00 |                   18050 |              20813.18 |               18050 |                 NULL |
| 2019-01-12 00:00:00 |                   18135 |              24768.43 |               18135 |                 NULL |
| 2019-01-10 09:00:27 |                      80 |               1497.75 |                  80 |                 NULL |
| 2019-01-09 09:20:55 |                      51 |                933.50 |                  51 |                 NULL |
| 2019-01-08 16:45:14 |                      10 |                187.50 |                  10 |                 NULL |
| 2019-01-05 18:21:00 |                   17588 |              16968.49 |               17588 |                 NULL |
| 2018-12-29 00:00:00 |                   17893 |              25404.18 |               17893 |                 NULL |
| 2018-12-28 15:23:04 |                       1 |                 35.00 |                   1 |                 NULL |
| 2018-12-22 00:00:00 |                   17353 |              17048.18 |               17353 |                 NULL |
| 2018-12-15 00:00:00 |                   16893 |              10181.34 |               16893 |                 NULL |
| 2018-12-08 00:00:00 |                   16220 |              99547.09 |               16220 |                 NULL |
| 2018-12-01 00:00:00 |                   15476 |              87699.59 |               15476 |                 NULL |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+


texts: I want to pivot a table and aggregate a column into strings


texts: MSSQL Server XE column client_connection_id accessible through system view/table for auditing?

texts:
In our .NET application we create a SqlConnection to talk to the database.
After the connection is opened, we get the ClientConnectionId, a GUID.
Within the extended events I can manage to trace the information what happens on the database regarding this ClientConnectionId via the column client_connection_id (for e.g. in the sql_statement_completed event).
Looking at the sys.dm_exec_sessions or the sys.dm_exec_connections I can't find a matching to the client_connection_id from the extended event.
In sys.dm_exec_connnections is a column connection_id but the uuid differs from the one created by the SqlClient in .NET.
Question
Where does the SQL Server store the client_connection_id (table/view) and is this accessible without creating an extended event?
Background
Our support team still uses the SQL Profiler and don't know anything about extended events. The profiler is good enough to get some small insight what happens on the database which then can be handed over to the developing team for further investigations.
Usually we used the SPID, but there will be some changes made to the application where we will use connection pooling in the future and with ef core there will be no permanent connection opened for each client, but rather for each db query a new connection.
Now I am trying to get a solution in how to be still possible to audit the database for each client (and not too complex for the support team).
Manipulate the application name or the hostname in the connectionstring would be another possibility, but I doubt this is recommended.
Any thoughts and help are warmly appreciated.


texts:
I've created following trigger :

CREATE TRIGGER probeer2
  AFTER INSERT ON libtn01leb.kofax_release
  FOR EACH ROW
  UPDATE libtn01leb.kofax_release SET RRNR='bleh'

This trigger will update EVERY ROW in the table, instead of the row I'm currently adding.
How can I make it only be valid for the row I'm inserting ?


texts: Piecemeal restore only one filegroup without primary filegroup restore

texts:
I have a db in SIMPLE recovery model, and regularly take FULL,DIFF backups.

This db also have filegroups for each month.
Each filegroup has exactly one NDF file.

Like this:

FileGroup: PRIMARY
File: Primary.mdf

FileGroup: FG201801
File: 201801.ndf

FileGroup: FG201802
File: 201802.ndf

FileGroup: FG201803
File: 201803.ndf

etc

My goal would be twofold:

Be able to take backup per partition level.
As I read its only possible when I mark the filegroup readonly.
So i have separated partial backup BAK files.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
Second goal would be (my question is here), to be able to restore only one filegroup, without restoring the PRIMARY filegroup or touching any other filegroup.

Is it even possible?

As understood, if i want to restore for let's say FG201802 only, leaving PRIMARY and the others untouched, then first i have to restore the FULL backup containing the PRIMARY filegroup, then I can restore my partial backup of FG201802.
How could I do the restore of FG201802 without restoring PRIMARY?

Could anybody point me to an online resource where this is demonstrated?
All article on the net (which i found) always starts to restore the PRIMARY full backup then applying the rest of the partial backups one by one.

I wish only restore a partial backup, how could it be done?

Thank you!


texts:
We've got a client that we're going to allow to use one of our applications.  Staff on site regularly connect to our network via VPN.  The .Net application that connects to the SQL Server uses integrated security.  The laptops they use are members of our domain.

The client has requested use of the application as well.  So we gave them a network account and the ability to create a VPN session on our network.  I gave that account a login on the SQL server with the same rights as our own staff.

The problem is that when they try to use the application they get the following error:  The login is from an untrusted domain and cannot be used with Windows authentication.

I'm guessing that since they are using a computer that is not on our domain, it doesn't matter that they are using a login with the VPN that is.  So, what is the best way to address this problem?  I'd rather not switch over to SQL authentication for this small application.


texts: SQL Server 2008 Expr. SP2 install fails - "failed to retrieve data for this request.."

texts:
I tried to install SQL Server 2008 Express SP2 but the installation process fails with the following error message "failed to retrieve data for this request.."
First the window "SQL server installation center" appears. If click on "new installation" then the error message comes up.

What can i do to completely remove SQL server from my system to do a clean new install of SQLS2008R2?

Other question. I found this folder in my user folder:

Any idea why this folder is located under "user"?
Can i delete this folder?

Can i delete SQL Server by deleting all registry keys and deleting
all the files in the folder "C:\Program Files\Microsoft SQL Server"?

Any help is greatly appreciated. Greatly appreciated.

-Sammy


texts: Confirmation of SQL statement disappeared

texts:
Until today after every SQL statement eg. CREATE user t identified by t; I had a confirmation of the execution "User created." Now I have nothing (no confirmation) but if I check if the user exists it is positive:

select username from all_users where username='T';

USERNAME
--------
T

How to turn it on again?


texts: Select data from single table using Self join

texts:
I have requirement to select a data from single table. The requirement is as follows
Select data from table which has one value but not another.
for e.g
I want to select distinct ID which has value 1 but not value 8 present. For for above e.g the desired o/p should be

ID      Value
123     1
123     8
234     8
456     1
876     5
876     1
765     8
765     5

O/p
ID

456
876

Also my table contains 500K record so query should be able to execute with good performance
Any help regarding it as i am stuck.


texts:
I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into NUMBER FK columns referencing a table dedicated to BLOBs only (i.e. a PK ID column and a BLOB column). This would allow our ORM library to do lazy initialization of objects much more easily with less code.

What are the performance or data organization implications to doing this? The database is quite large in size due to these BLOBs.


texts: ORA-04031: unable to allocate bytes of shared memory ("","","","")

texts:
I am receiving this error in a development database.
It happens on some specific queries (simple inserts and deletes via .NET application).
The database is used by one user, and error happens regardless for how long the database was running.

The only memory related parameter which is set is: MEMORY_TARGET=1.2G

Oracle 11.2 x64 Standard Edition One
Windows Server 2008 R2