texts: Restoring backup with a MySQL Database downgrade

texts:
I want to migrate a MySQL 5.7 database to a MySQL 5.1 version because the MySQL version of the server I’m migrating my WordPress webpage is 5.1. I tried to do it in the usual way but it was not possible. Moreover, I read on this webpage:

https://dev.mysql.com/doc/mysql-enterprise-backup/3.12/en/restore-upgrade.html

that restoring to an older MySQL version should only be performed when the original and the final server versions are in the same release series.

I was wondering what other options I have besides creating the WordPress webpage from scratch using the MySQL 5.1 version this time.


texts: Why is a table showing up twice in pg_class?

texts:
I am attempting to run a PostgreSQL statement to query approximate table size count estimate using the following statement:
SELECT reltuples as approximate_row_count FROM pg_class WHERE relname = 'table_name';

ref: Count estimate (PostgreSQL | Wiki)
However, my query is returning two rows, one with a size and one with zero.
Why?


texts: How do I completely remove MySQL from macOS Mojave?

texts:
I'm new to SQL and have only started learning. I've recently installed MySQL on macOS Mojave but found it difficult to use for some reason (given that I'm new to the tool and language). Wanted to remove it completely from the system. Please let me know how to wipe it out completely (including hidden/registry files). Alternatively, I would also like to install another SQL RDBMS so please also recommend something for a starter.

Advice on whether this is the right decision to remove MySQL will also be appreciated.

Thank you.


texts: Selecting data from a one-to-many relationship table

texts:
Admins, please note although it shares the same structure from the question I posted earlier about hierarchical data but it is a completely different question. I have the following tables:

Teachers:

+------+--------+
|  id  | deptId |
+------+--------+
|  1   |    2   |
+------+--------+
|  2   |    1   |
+------+--------+

Department:

+------+-----------+
|  id  | contactId |
+------+-----------+
|  1   |     2     |
+------+-----------+
|  2   |     6     |
+------+-----------+

Contact:

+----+--------------------+
| id | nonImportantColumn |
+----+--------------------+
| 2  |   street street    |
+----+--------------------+
| 4  |   street2street    |
+----+--------------------+

SchoolBranch:

+----+-----------+----------------+
| id | contactId | parentBranchId |
+----+-----------+----------------+
| 3  |    2      |     null       |
+----+-----------+----------------+
| 4  |    5      |     3          |
+----+-----------+----------------+
| 5  |    7      |     null       |
+----+-----------+----------------+
| 6  |    2      |     3          |
+----+-----------+----------------+

Given a teacher's ID let's say 2, I am trying to get the teacher's linked branch, which should be BranchID:3 row but I am receving an extra row for BranchID:6 because it also shares the same contactId.

I am using SQL Server v 18, in case it helps.
I have tried this query

select b.* from SchoolBranch b where b.contactId = (
    select c.id from  Contact c
        inner join Department d on c.id = d.contactId
        inner join Teachers t on d.id = t.deptId
    where t.id = 2
    )

but it returns two rows instead of one. Here is dbfiddle link


texts: Trouble setting up Nagios to monitor Oracle services

texts:
I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and LD_LIBDRARY_PATH of course, but it keeps generating the following error:

/usr/local/nagios # libexec/check_oracle_health --connect "oracle-server:1551" --username user --password "pass" --name OFFDB1 --mode tablespace-can-allocate-next --warning 20 --critical 30
CRITICAL - cannot connect to oracle-server:1551. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (DBD ERROR: OCIServerAttach)

I'm still fairly new to Oracle, but my googlings seem to indicate that SERVICE_NAME should match the GLOBAL_DBNAME in listener.ora, which is OFFDB1. Do I need to do something else here like modify the connect string?

As a note, there are multiple instances of oracle sharing the target box, but each seems to be intalled to separate partitions and are running their own listeners or various ports.


texts: Call to undefined function sqlsrv_connect(), Mysql and php

texts:
I am working with Apache, XAMP and MySQL with my mac and I get this error message:

  Call to undefined function sqlsrv_connect().

Been googling it for a bit but I am still stuck.

I have added these lines in the two of my php.ini files. I find them in XAMPP/etc and i add these "codes" in both of the ini files:

extension=php_sqlsrv_56_ts.dll
extension=php_pdo_sqlsrv_56_ts.dll

I restart the apache servers but I still get the error message.

Any clues?

Do I have to use a PDO instead? When I read into it it seems like sqlsrv only works for Windows? Do I need to download anything or is just pasting the code inside the ini enough?


texts:
Me and a coworker are seeming to disagree on this topic.

The context is Product that have multiple components. Sometimes its multiple of the same component.

I think its better to have a record for each component where as my coworker thinks its better to have a quantity column. Personally I think this is what a one to many relationship in db design represents in a very real since, where he is saying its easier to work with (he is more of a c# guy where I am the dba). So, I can def see where we might be coming at this form different angles.

I guess since I am new to this, I figured I would ask the people who know better what their opinions are.

Which is better - extra rows per "product" or having a quantity column?


texts: Why is this rollback needed when using sp_addextendedproperty in a stored procedure?

texts:
In enter link description here I showed, how I document databases.

To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls.

But lately I wanted to catch the errors about not existing objects when I run the script on an older version of the database.

I found the following working code by trial and error

begin try
drop procedure dbo.BK_add_Tableproperty
end try begin catch end catch

go
create procedure dbo.BK_add_Tableproperty (
    @table_name sysname,
    @property_name nvarchar(max),
    @Property_value sysname
)
as
    declare @error integer;
begin try
    EXEC sys.sp_addextendedproperty @name= @property_name,
    @value = @Property_value,
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'TABLE',
    @level1name=@table_name
end try begin catch
    SET @error = @@ERROR;
    if @error = 15135
        Print 'Table missing no extended properties added: ' + @table_name
    else if @error = 15233
        Print 'Table has already property: ' + @table_name + ' - ' + @property_name
    else
        Print 'unexpected error ' + str(@error)
    ROLLBACK;
end catch

go

What me confuses, is the fact, that I have to add a ROLLBACK to the catch path. At msdn I found no hint, that sp_addextendedproperty uses transactions.  Can anyone explain this?

Answer to gbn:

But return  exits unconditionally the procedure. I see only 1 commit in each path:

BEGIN TRANSACTION

begin
    EXEC %%ExtendedPropertySet().AddValue(Name = @name, Value = @value, Level0type = @level0type, Level0name = @level0name, Level1type = @level1type, Level1name = @level1name, Level2type = @level2type, Level2name = @level2name)
    IF @@error <> 0
    begin
        COMMIT TRANSACTION
        return (1)
    end
end

COMMIT TRANSACTION
return (0)

Reported as bug to Microsoft connect:

https://connect.microsoft.com/SQLServer/feedback/details/658556/sp-addextendedproperty-leaves-on-open-transaction-when-an-error-occured

Improved version (better error handling and fixed parameter type.
(the original problem is unaffected, but this handels NLS in erropr messages)

create procedure dbo.BK_add_Columnproperty (
    @table_name sysname,
    @Column_name sysname,
    @property_name nvarchar(max),
    @Property_value sql_variant
)
as
begin try
    --SET XACT_ABORT ON
    EXEC sys.sp_addextendedproperty @name= @property_name,
    @value = @Property_value,
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'TABLE',
    @level1name=@table_name,
    @level2type=N'COLUMN',
    @level2name=@Column_name
end try begin catch
    -- SELECT XACT_STATE()    -- returns 1 or -1 when SET XACT_ABORT ON
    if ERROR_NUMBER() = 15135 -- object invalid
        print '      ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE()
    else
        print '##### ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE()
    ROLLBACK;
end catch

go


texts: Unable to use datetime2(0) in Partition Function

texts:
I have a large table that I want to partition by a datetime2(0) column. AFAIK, datetime2(0) is stored as 'YYYY-MM-DD hh:mm:ss'. However, I can't create the partition function with the same format.

Use PartitionLogTest
Go
CREATE PARTITION FUNCTION pfRequestTimeRange (DATETIME2(0))
AS
RANGE RIGHT FOR VALUES (NULL,
                        '2015-01-01 00:00:00',
                        '2016-01-01 00:00:00',
                        '2017-01-01 00:00:00',
                        '2018-01-01 00:00:00')

Error message:

Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

I've tried formats like '20150101000000' or '2015-01-01 00:00:00.000' but still couldn't make it work. Is there a correct format or I can't/shouldn't create partition function with datetime2(0)?


texts: How to convert timestamp to string in Pentaho Kettle?

texts: I have a timestamp field in my input table like this: yyyy-mm-dd hh24:mm:dd,  I want  output sting like this: yyyymmdd.


texts: Error 1052 Field is Ambiguous using Inner Join Function

texts:
Error '1052' Field is ambiguous. I understand the error that is happening, and I've looked at several places for the fix and then applied the inner join function in attempt to fix it. It's still not working, and it gives me the same error.

EventNo is a primary key in the eventrequest table and a foreign key in the Eventplan table. I'm trying to list the event number, event date, and count of the event plans while only including event requests in the result if the event request has more than one related event plan with a work date in December 2013. This is my code.

SELECT EventNo, DateAuth, COUNT(PlanNo)
FROM eventplan INNER JOIN eventrequest ON eventplan.eventNo = Eventrequest.EventNo
WHERE COUNT(PlanNo) > 1 BETWEEN '2013-12-01' AND '2013-12-31';


texts: How do I know if a AWS Server instance has license SQL Server Standard or SQL Server Web?

texts:
I'm looking my AWS billing and this doubt has come up. How do I know if a specific instance is running SQL Server 2008-R2 Standard or SQL Server Web? (using the AWS Console or SQL Server Management Studio)

The Management Studio About shows the following:

Microsoft SQL Server Management Studio 10.50.2806.0
Microsoft Analysis Services Client Tools 10.50.2806.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5456
Operating System 6.1.7601

Looking for version 10.50.2806.0, I've saw that it may be related for both licenses (link), so I don't know how to distinguish them.


texts: Can pg_dump be broken into batches for large DBs?

texts: I have a fairly huge DB and pg_dump croaks on me when I try to export it to a single file. Is there a way to do it in batches, whereby each batch would cover a segment of tables and export the schema/data in its own file rather than all in one file?


texts: Postgres Service Not start using via HomeBrew

texts:
I have to install homebrew in my macOS then after I tried to install Postgres on my mac using the homebrew. but when I checked my Postgres status is it not running.

/usr/local/Cellar/postgresql/11.4/bin/pg_ctl -D /usr/local/var/postgres start

I have tried with that cmd but it only gives me pg_ctl: could not start server

2019-07-16 18:32:36.148 IST [9541] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-07-16 18:32:36.149 IST [9541] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-07-16 18:32:36.162 IST [9541] LOG:  could not open directory "pg_tblspc": No such file or directory
2019-07-16 18:32:36.166 IST [9542] LOG:  database system was interrupted; last known up at 2019-07-14 17:27:00 IST
2019-07-16 18:32:36.350 IST [9542] LOG:  could not open directory "pg_tblspc": No such file or directory
2019-07-16 18:32:36.350 IST [9542] LOG:  could not open directory "pg_tblspc": No such file or directory
2019-07-16 18:32:36.350 IST [9542] FATAL:  could not open directory "pg_replslot": No such file or directory
2019-07-16 18:32:36.350 IST [9541] LOG:  startup process (PID 9542) exited with exit code 1
2019-07-16 18:32:36.350 IST [9541] LOG:  aborting startup due to startup process failure
2019-07-16 18:32:36.352 IST [9541] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I have tried find some solution but not working for me.


texts: Postgres 12 create table as simple select + hash join takes 8+ hours for 100GB?

texts:
I'm using a "create table as select ..." statement to create a copy of a subset of the columns in a ~100GB table (400 million records). I am also performing a hash join with another table which is only 36 records. It takes 8-9 hours to perform this copy. Does that seem reasonable, or is it indicative of a problem?
The server should have plenty of disk space, 128 GB of RAM, and 16 cores.
The source table is partitioned.


texts:
I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to exercise multiple cores when a query maximizes on a single thread.

In a DWH environment, I would like to use PL/Proxy to do the following:

develop functions that query a local database on a single node
individually querying partitions
aggregating the intermediate data sets into a single result

Is this possible?


texts: How to enable odbc connnections in new sql server setup?

texts: Fresh server 2012 R2 Essentials install, trying to get SQL Server Express to accept ODBC connections. My database is setup and I have all of my data populated, but now I am trying to get my reporting software to connect to the database and am not sure how to get the server to allow incoming connections.


texts:
I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the entire table from which I'm getting the MAX while the transaction is completed.

I researched the isolation modes and lock hints available. The closest seems to be TABLOCKX, which is the only one that avoids inserts in the entire table (also updates and selects).

Since I no will be affected by updates or selects in the table I wish to avoid only inserts during the transaction.  This is possible?

UPDATE: The field on wich the MAX is performed is seted only on inserts, thus there's no risk of get stale results for updates.

I'm using SQL Server 2000.


texts:
I have SQL Server database which has size of 65GB and 6 million rows. We have essays in tables and the average size of an essay is 450 words. When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast).

Please let me know how to optimize it so I can get queries in less than 1 sec.

Query example:

SELECT top 1 * FROM tblContent WHERE CONTAINS(pagecontent,'"increasing the likelihood"')

Here is detail of server(VPS):

OS: Windows Server 2012
SQL Server 2012 Enterprise (Trial)
Processor: set to 0
Memory: set to 0
RAM: 4GB
Processor: QUAD 4Ghz


texts: Should this be an identifying relationship or not?

texts:
Well I've attached the image to explain what I need to know:

I thought it was supposed to be an identifying relationship, but now I kinda thought maybe it wasn't? The thing is the user won't necessarily be both: the patient and the professional? So how do I recognize which one he is or whether he could be both(if that's possible)? Should Patient and SpiProfessional have additional primary keys of their own? I've kind of got confused on this one. I was also thinking of maybe adding one additional field to recognize whether the user will be a patient or an spiProfessional, but then again you might want to add other types later and then the user would only be limited for one type...


texts: How to structure T-SQL query with many left joined "attributes"

texts:
I am writing a T-SQL query for a complex report, with a unique key, and have run into a bit of a performance problem.  I am not sure where it's coming from, but I thought I'd ask how it should be done.

One issue that may become apparent is that I am doing lots of left joins on the same attribute/lookup tables.

Basically, I the query is structured into a "trunk" and "leaves", like so:

select *
  -- many many fields, probably about 150
  -- I explicitly list them in the real query
  from
  -- the "trunk" of the query, all of which are necessary,
  -- and which resolve to a single row
       foo inner join bar on foo.id_foo = bar.id_foo
           inner join baz on baz.id_bar = bar.id_bar
           inner join bon on bon.id_bar = bar.id_bar

   -- the "leaves", where i do a bunch of lookups:
           left join attribute height on height.CD_NM = 'height'
                                     and height.id_foo = foo.id_foo
           left join attribute weight on weight.CD_NM = 'weight'
                                     and weight.id_foo = foo.id_foo
       --    etc, for maybe 50 different attributes, etc
  where foo.id_foo = @ReportId

Currently, I am applying the bisection method to figure out if there is some number of attributes that the database starts choking on.  I commented out half the left joins, and un-commented until it started slowing down.

Execution time appears to increase non-linearly, and quickly, in the number of lookups.  At one point, the execution time jumped from <1s to 14s, and now each additional look up appears to add a fixed amount of time.

Strangely, the "live query statistics" say that the key lookups on my core tables (which have indexes, which are getting used) are taking up the bulk of the time.  I would have presumed this would continue to take less than 1s, regardless of the number of additional lookups.

I'm not even done doing lookups for this query and I'm already closing in on 20 seconds.

I am just a lowly analyst, not a database engineer, so I'm not in a place to do much to tune the database itself, but anything I can include in an SSRS dataset is fair game.  (Theoretically, I can make a tuning change happen, but I'd need something concrete to take to the database people)

Is splitting this query up into multiple queries really my best option?


texts: Use string functions and arithmetic calculations in SQL query or by programming language?

texts:
I need heavy arithmetic and string functions for processing the data captured from mysql database. Which is preferred: mysql functions or programming language (e.g., PHP)?

$result = $mysqli->query("SELECT A, B, C FROM table1");
while($row = $result->fetch_assoc() )
{
$a = str_replace("X", "Y", $row['A']);
$b = ($row['B']/$row['C')*1000;
}

OR

$result = $mysqli->query("SELECT REPLACE(A, 'X', 'Y'), ((B/C)*1000) AS B FROM table1");
while($row = $result->fetch_assoc() )
{
$a = $row['A'];
$b = $row['B'];
}

This just a simple example with no significant difference, but when having heavy calculations, which one is preferred?


texts: Mariadb started giving erroneous query results for 1 view

texts:
10.1.44-MariaDB-0ubuntu0.18.04.1

Have a view volv_action which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its datetime column is included in the where clause. This started happening in more than one database with this particular view on the same instance each with their own data in the tables (details on that part at bottom).

Data that's there:

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020;

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22

With action_date_created doing a comparison in where clause like this gets no rows returned:

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020 and action_date_created > '2020-01-01'

Change ">" to "<" and ask for records before 2020 which clearly there are none, and it will return exactly 1 record which is not before 2020.

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020 and action_date_created < '2020-01-01'

scene_id    action_date_created
11020     2020-02-08 07:14:28

Deleting that particular record will cause the next record to be retrieved instead.

Adding "order by 1" on the end causes a query to give correct results

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020 and action_date_created > '2020-01-01'
order by 1

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22

A query where "action_date_created is not null" without other clauses on that column gives correct results, but not if there is one of the ">" clauses that causes failure.

Querying the underlying table with action_date_created works fine. Spot-checking other views that have similar datetime columns does not reproduce the problem. Analyzed all tables in the database.  Have recreated the view, problem persists.

Later found it is not just this database on the instance with the problem for this specific view.

There is a separate dev database on the same Mariadb instance with an identical data model but different data in it. Checked and it has started showing the same exact problem with its data as well.

The queries had worked for years. I know it worked successfully at 11pm last night, but was broken when someone ran one around 2am.

We've done a service mysql restart, but same problem persists. I'm perplexed. Any help would be appreciated.

Edit to give link to schema code:
https://raw.githubusercontent.com/volundmush/mushcode/master/Schema.sql


texts: SSIS Promote Package - Move Some Stored Procedures

texts:
I am trying to create an SSIS Package in Visual Studio that I can use when front end deployments are made.  Quick rundown -

copy prod DB to temp backup
move new/updated stored procedures from prod to test
run some queries to update prod tables with new version info, error messages, etc.
validate
drop temp backup if validation passes.

I'm stuck on #2... Is there a way I can compare PROD and TEST stored procedures for differences and update/add to PROD if there is a difference.  I don't want to have to add a SQL Task for each stored procedure for each deployment.  I'd also prefer to not wipe all stored procedures and replace them all...

This brings me to a related question, I have another SSIS package (runs nightly) that wipes TEST, recreates TEST from PROD, runs any table changes, then I manually add SQL tasks to the package for new/updated stored procedures one by one... is there a better way to go about it than manually adding each SP through a SQL task?

In both cases I'd like to be able to automatically identify and resolve the discrepancies of stored procedures between databases and resolve without having to manually add SQL Tasks for each new/altered procedure.


texts: Error connecting to MSSQL server from Linux

texts:
A colleague is trying to connect to the MS SQL Server Instance from linux using ODBC driver 17 and get's the below error.

  ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

The Connection string appears as below:

SQL_SERVER = 'Myserver'
SQL_DB = 'Mydatabase'

'DRIVER={ODBC Driver 17 for SQL Server};SERVER=MYServer;DATABASE=MyDatabase;Trusted_Connection=yes'


texts: I'm noticing a high number of waitingRequestsCount on SQL server. The latch_class is buffer. See attached screenshots. Going through SQL server dashboard, I see high number (32221334) waits for Buffer Pool. Can someone please explain what could contribute to high number of waits?


texts: Galera log has WSREP: failed to initialize io-cache

texts:
I'm having trouble tracking down the cause of an error message that is appearing in my logs every 15 minutes or so.

The cause of the error happened whilst performance testing a 3 node Galera Cluster (running on MariaDB 10.1.13). Two of the read-only nodes fell too far behind the write master causing them to request an SST sync and pulling the cluster off-line whilst the write master fulfilled the sync.

Since that point though the master has regularly been posting the following message into the logs:

2016-08-26 18:03:16 139975496911616 [ERROR] WSREP: failed to initialize io-cache
2016-08-26 18:03:16 139975496911616 [Warning] WSREP: binlog trx cache not empty (0 bytes) @ connection close 6510
2016-08-26 18:03:16 139975496911616 [Warning] WSREP: binlog stmt cache not empty (0 bytes) @ connection close 6510

Google is sparse on any details and the only good hit I've had is from the source code, which appears to suggest that it's unable to initialise a cache for reading or writing (hmmm, much like the error message suggests).

   32   if (reinit_io_cache(cache, READ_CACHE, 0, 0, 0))
   33   {
   34     WSREP_ERROR("failed to initialize io-cache");
   35     return ER_ERROR_ON_WRITE;
   36   }

This message continues despite a reboot and full SST sync from the other nodes, so I'm a little lost as to the cause. Does anyone know what this means and how to fix it?


texts: Return items not found in database in Oracle

texts:
I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number.

However, the names given are not necessarily exactly as they appear in the database.  I therefore also need to know which ones it does not find, so we can do some further manual research to locate them and find out their employee id - but I cannot figure out how to also return the employees not found.

Example:

select name, emplid
from employees
where name in ('Smith,John','Brown,Jill','Doe,Jane')

If one of the names is 'Doe,Jane April') in the db, then I don't find her.

I am using Oracle, and I do not have the access to create tables.  Is there any way to do this?


texts:
I currently backup daily which I think is fine, however, I want to backup more frequently when there's no activity (e.g., after X minutes of no INSERT/UPDATE/DELETE excluding SELECT).

I'm currently thinking of creating a custom script that will check for CHECKSUM TABLE mytable and see if that changes, every X minutes.

Is this a good idea or is there a better way, perhaps an existing tool?


texts: How to grant rights for a user in DB2 Express?

texts:
I have installed DB2 Express on a PC. My user is not an administrator user, however as far as i know, it is possible to grant a user rights, so he can create/delete/modify a database.

I have executed: db2set DB2_GRP_LOOKUP=local
after that i created a Local group: DB2Admins, and added myself to it. After that i executed `db2 update dbm cfg using sysadm_group db2admins.

After that i tried to create database test, but i get the error SQL1092N The requested command or operation failed because the user ID does not have authority to perform the requested command or operation. User ID: "myUserID".

I created a local user, and added it to the same group. If i log in with this local user, then it is ok. The user can create a a database.
My user however is a domain user, and with that it is not working.

Does anyone has any idee how to solve this?

Thanks in advance!


texts: Column does not exists when accessing inner type field in ORDER BY

texts:
In PostgreSQL 9.4 I have two types:

CREATE TYPE a_schema.type_child {
  an_order smallint
}

CREATE TYPE a_schema.type_parent {
  pluto uuid,
  child type_child
}

Then in a view I have this script:

SELECT
    something_a,
    something_b,

    ARRAY(SELECT
            ROW (a_uuid,
              a_schema.get_a_type_child(a_type_child_id)
            )::a_schema.type_parent AS tp
          FROM a_schema.a_table
          ORDER BY ((tp).type_child).an_order ASC)
FROM ....

where the stored procedure just does a select as:

CREATE FUNCTION a_schema.get_a_type_child(IN type_id smallint, OUT a_type a_schema.type_child)
  RETURNS a_schema.type_child AS
$BODY$
DECLARE
BEGIN

  SELECT
      ROW (a_order)::a_schema.type_child
  FROM a_schema_another_table
  WHERE at_id = type_id
  LIMIT 1
  INTO a_type;

END;

$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 1;

The error I get is:

  ERROR: column "tp" does not exist

related to the ORDER BY clause.

I know that I could just do

ORDER BY tp

But then I have to define an ordering for the type, which introduces a lot of code.

Is there any way to access the inner type field an_order for ordering purpose?


texts: phpmyadmin is no longer letting me set auto_increment value

texts:
I am following these instructions to reset the value of auto_increment to the last highest value using phpMyAdmin.
Example:
If I have this table and data:
DROP TABLE IF EXISTS `Tbl_Lis_Agencias`;
CREATE TABLE IF NOT EXISTS `Tbl_Lis_Agencias` (
  `IdAgency` int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `AgencyCodU` int(3) UNSIGNED ZEROFILL NOT NULL DEFAULT '000',
  `AgencyName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Agency_Order` int UNSIGNED DEFAULT NULL,
  `AgencyStatus` int UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`IdAgency`),
  UNIQUE KEY `IdAgency` (`IdAgency`),
  UNIQUE KEY `Agency_Order` (`Agency_Order`),
  UNIQUE KEY `AgencyName` (`AgencyName`),
  KEY `xAgencyStatus` (`AgencyStatus`)
) ENGINE=InnoDB AUTO_INCREMENT=12345 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `Tbl_Lis_Agencias` VALUES
(001, 001, 'Panama', 1, 1),
(002, 020, 'Aguadulce', 2, 1),
(003, 080, 'David', 3, 1),
(004, 010, 'Vacamonte', 4, 1),
(005, 060, 'Prueba', 5, 1);

If I run this:

phpMyAdmin says that is was a succeful update:

but when I switch to the operation tab I see this again:

What I don't want to do and I am forced is to go to the terminal and run this:
ALTER TABLE Tbl_Lis_Agencias AUTO_INCREMENT = 5

Then I look at the operations tab again

Why isn't the operations tab updated in the first example?
The solution in this post is not useful, it is mandatory for me to do everything through phpMyAdmin, not through the terminal.
update
i have check that phpmyadmin show ghost value; related to this post: https://github.com/phpmyadmin/phpmyadmin/issues/16378


texts:
I have a MySQL database with InnoDB tables.
There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) statements.
What I fear is a possible concurrent access to data causing only INSERTs and no UPDATEs.
Is LOCK Table WRITE the only solution?


texts: Can't migrate standalone MongoDB to Cluster

texts:
I'm trying to convert a standalone MongoDB database into a cluster in order to migrate my database to MongoDB Atlas.

I've followed the following steps to try and convert it:

Stop my mongodb instance
Changed the configuration to the following content:

........................

#how the process runs

processManagement:
     timeZoneInfo: /usr/share/zoneinfo

security:
   authorization: enabled

replication:
  replSetName: "rs0"

Restarted mongodb with the following command: sudo mongod --config /etc/mongod.conf --fork
Logged into mongodb, to the admin database, with a user who has the following roles: clusterMonitor, readAnyDatabase, userAdminAnyDatabase
Executed the command rs.initiate()

And it gave me the following error:

{
        “ok” : 0,
        “errmsg” : “not authorized on admin to execute command { replSetInitiate: undefined, $db: \“admin\” }“,
        “code” : 13,
        “codeName” : “Unauthorized”
}

I have tried the previous steps in a dev server, and everything worked fine, bun in my production environment it didn't work, any help is appreciated.


texts: TempDB internal space usage slowly growing from service broker

texts:
Am running SQL Server 2012 (11.0.6540.0) in a AO high availability group with one other server.

tempDB usage usually sits around 819MB  (averaged over a week from 2 weeks ago) but since we did a fail-over and patch cycle the tempDB started growing gradually (no DDL changes have been made in this time).
We've just done a test fail-over and moved the tempDB off the joint SAN drive that both machines have access to, (we don't want one piece of hardware that can bring down both our primary and secondary replica).

The most recent failover and Instance restart (to move the tempDB) saw the tempDB usage jump to 6348MB used and its slowly growing.

The growth is all on the internal objects which can be seen using:

SELECT   top 5 session_id, request_id,
         SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count
FROM     sys.dm_db_task_space_usage
GROUP BY session_id, request_id
ORDER BY request_internal_objects_alloc_page_count DESC

Looking into it, it all is running from spid 35 and 32 which are both service broker related.

I've had a look around and there's talk about this being due to having a WITH CLEANUP on an end conversation but the word cleanup only ever appears in comments so I'm confident that this isn't the issue.

When the instance came back up and took over the primary node the service broker wasn't working so I needed to cycle all of the queues to disabled then enabled state (we've had to do this once before a a year ago but didn't see this issue).

You can see here the use on tempDB as it currently stands over the past 4 days. the drop off this morning is the instance restart and fail-overs.

Does anyone know what I'm missing to get this back under control.


texts: Mysqlfailover command - No slave is listed in health status

texts:
I have successfully created replication using GTID_MODE. It works perfectly. Now I need to setup automatic failover feature in it. I have run the following command.

mysqlfailover --master=root:abc@10.24.184.12:3306 --discover-slaves-login=root:abc

I have got the following results. No slave is listed.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Tue May

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog
mysql-bin.000016  9568

GTID Executed Set
8fe8b710-cd34-11e4-824d-fa163e52e544:1-1143

Replication Health Status
0 Rows Found.
Q-quit R-refresh H-health G-GTID Lists U-UUIDs U

But when I execute the mysqlrplcheck and mysqlrplshow commands, the slave is listed.

Is this normal?


texts: How to trigger a function with DDL query?

texts:
I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored function like this:

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION checksql (
       stmt TEXT
   )
   returns int
BEGIN

   if locate("create", stmt) = 1 then
       if locate("primary key", stmt) = 0 then
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Primary key defined';
       end if;
   else
       SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Unknown SQL statement';
   end if;
   return 1;
END$$
DELIMITER ;

but I could not find a way to trigger a function whenever a  DDL get executed.

One way is:

START TRANSACTION;
SET autocommit=0;
SELECT checksql("CREATE TABLE ...");
CREATE TABLE ...;
COMMIT;

But this forces me to make the changes in my code. Is there any methods that i can use to trigger the function without making any code changes?


texts: How long does query optimisation take relative to the execution time?

texts:
Could anyone please point me to a benchmark comparing the time spent by the query optimiser relative to the overall query execution time (any relational DBMS will do, but PostgreSQL would be preferable)?
I'm curious about roughly how significant is the time spent on optimizing the query relative to the overall execution time.
In particular, whilst reading Bao: Learning to Steer Query Optimizers by Marcus et al. 2020 I was surprised by the fact that the DBMS' query optimizer was run 48 times for each query (required even at inference), whilst the model outperformed the default PostgreSQL query performance regardless.


texts: Does PostgreSQL optimize queries in transaction?

texts: In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, amount of records. And there is a question, will DB engine optimize it for me that rows won't be updated twice, or should I do it by myself?


texts:
We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures.

Tested on SQL2016 and SQL2017 Enterprise (recent CU).
AWS server with 2Tb of memory and 64 vCPU
8 tempdb files located on local SSD drive.
SSD drive is healthy. Tested on different instances and different instance types. Disk performance is reasonable.
trace flag 3427 is on for SQL2016 (as far as I know, it is not required for SQL2017).
performance for other tempdb operations is excellent (table creation, sort for index rebuild, etc.)

We found that tempdb spill process for heavy queries overwhelms local SSD drive, creates very large disk queue and basically brings server on its knees.

I understand that the issue can be fixed for every individual query but is there a way to change how SQL Server writes data in tempdb during a spill and make it less aggressive for all queries?

Over time we will walk through each query and, hopefully, fix it. However, I don't think that it is an expected behavior when server is getting unusable because of a large spill.

Issue does not occur when query has small memory grant. Here is an example:

Issue occurs:

by default query has 78Gb memory grant.
query execution time ~37 minutes
tempdb spills are happening every minute. 15Gb each time

Issue doesn't occur:

query has 0.8Gb memory grant OPTION (max_grant_percent = 1)
tempdb spills are happening very frequent without significant pressure on SSD drive
query execution time ~35 minutes

UPDATE

We were able to decrease the impact on the server drastically by reducing max memory grant percent in Resource Governor to 1%. It had no negative impact on overall server performance. Disk queue on SSD now spikes up to 300-500, which is still bad but it doesn't bring the server down.

ALTER WORKLOAD GROUP [default] WITH (request_max_memory_grant_percent=1)

Wish we could go lower but 1% is the minimum SQL Server accepts.

UPDATE (2019-04-25)
We found that aggressive spills are happening even when tempdb is on shared drives (remote Storage, in our cases AWS EBS volumes) and MAXDOP <> 1.
So the only case when it works fine - is when tempdb on shared volumes and MAXDOP = 1 (which is true for our case, don't ask why :) ).
We are working with Microsoft to get to the bottom of the issue.


texts: How do I ORDER BY timestamp in this recursive query?

texts:
I have a working reqursive query for my comments table.

WITH RECURSIVE comments_tree(
  id,
  content,
  created_at,
  path,
  level
) AS (
  SELECT
    id,
    content,
    created_at,
    ARRAY[id],
    1 as level
  FROM comments
  WHERE
    parent_id IS NULL
UNION ALL
  SELECT
    comments.id,
    comments.content,
    comments.created_at,
    path || comments.id,
    comments_tree.level + 1 as level
  FROM comments_tree
  JOIN comments ON comments_tree.id = comments.parent_id
  WHERE NOT comments.id = ANY(path)
)
SELECT * FROM comments_tree ORDER BY path;

I have added 5 comments (2 of them have content 'parent x', others - 'child x').

Example of output (formatted to be more readable):

"-> parent 1: 16:49"
"--> child 1: 16:50"
"--> child 2: 16:51"
"--> child 3: 16:52"
"-> parent 2: 16:53"

I want to order comments by their creation time (created_at field) like this:

"-> parent 2: 16:53"
"-> parent 1: 16:49"
"--> child 3: 16:52"
"--> child 2: 16:51"
"--> child 1: 16:50"

How do I do that?


texts: Depicting permissions to access table via E -R diagram

texts:
I am designing a database schema for a web application. Its a first time I am designing a database with a commercial aim. I have started with drawing E-R diagrams after going through requirements phase.

My question is regarding conditions in schema.

I have a table category and sub category with one to many relationship between them. One category has many sub categories.

Category Table

====================
|  Category Name   |
====================
|  category1       |
|  category2       |
====================

Sub category Table

===========================================
| Owner Category   |  Sub category name   |
===========================================
|   category1      |  subc1               |
|   category1      |  subc2               |
|   category2      |  subc3               |
|   category2      |  subc4               |
===========================================

Now a user has access to subc1 if he has access to category1.

Is there any way to depict this scenario with E-R diagram so that by looking up diagram one can know this constraint?

Basically I want to know how this access constraint is propagated to next phase of designs.


texts: How to tell if a backup file contains CDC?

texts:
Using SQL Server 2008 through to 2014, I know how to backup and restore a database with Change Data Capture enabled (CDC), but what I am looking for is a way to query a full backup file to determine if it contains CDC data.

What would be great would be a way like RESTORE FILELISTONLY that gets the file list from the database, but instead gets the CDC status to determine if the restore will need to use the KEEP_CDC flag?

Is there a way to determine if a backup contains CDC data?

thanks!


texts: Run SQL Server Snapshot Agent automatically

texts:
I am trying to setup automatic transactional replication. Basically I have some script files that are executed after each other. They set up a replication from one server to another and this works just fine. Only problem is, that it is never starts. If I look into the Snapshot-Agents status, I can see that it has never been run before. If I just click start in that dialogue, everything works as expected afterwards. But I don't want the user to have to Open the sql server studio and activate the snapshot agent manually.

What I already tried:

running the snapshot.exe with all params set. It seems to work fine, but afterwards I get an error in my replicationmonitor that something from a path could not be read, but there arent any settings for a path, so I don't know how to fix this.
exec msdb.dbo.sp_start_job .. here I tried some params but I have no clue what to put here. My best guess was @job_name='myreplication' but I just get an error that it cant be found.

What is the easiest way to automate this?


texts: Gathering Read Routing information using SMO is inaccurate in SQL Server 2016

texts:
I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016.

With earlier versions the routing list was quite simple, the list would be processed in order presented, but with 2016 they introduced the round-robin algorithm allowing you to have multiple secondary replicas accept the read traffic.

While this data is accessible through T-SQL it does not appear as though SMO has been updated to reflect this, which means that you cannot accurately get, or set the configuration that way as it is still a simple string collection.

Is there some attribute that I am missing that would allow me to get accurate data for SQL Server 2016? (example SMO call for C# below)

It is just the round-robin info that does not seem to be accessible, it lists all of the replicas that are in the read-routing list, it just does not seem to differentiate the various groups of replicas that would exist.

Connect bug report. Connect is dead...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SmoTesting
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter the servername");
            string connectServer = Console.ReadLine();
            Console.WriteLine("Enter the AG name");
            string agName = Console.ReadLine();

            Server srv = new Server();

            try
            {
                srv = new Server(connectServer);
                srv.ConnectionContext.StatementTimeout = 60; //timeout after 60 seconds running the query

                foreach (AvailabilityGroup ag in srv.AvailabilityGroups)
                {
                    if (ag.Name == agName)
                    {
                        ag.PrimaryReplicaServerName.ToString());
                        foreach (AvailabilityReplica ar in ag.AvailabilityReplicas)
                        {
                            if (ar.Name.ToString() == "connectServer")
                            {
                                foreach (Object obj in ar.ReadonlyRoutingList)
                                {
                                    Console.WriteLine("  {0}", obj);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.InnerException.ToString());
            }
            finally
            {
                srv.ConnectionContext.Disconnect();
            }

            Console.WriteLine("press a key");
            Console.Read();
        }
    }
}


texts: Find Transactions in Time Range, Ignoring Date

texts:
My client wants to know all the people that logged in to the system during the night hours. I keep all login times. Basically looking for anyone who logged in from 1am-5am for all of this year. Is there a way to find this using SQL?

I keep my time in basic DateTime Format with a 24 hour clock.


texts: How to login to Sybase ASE using OS credentials?

texts:
I come from an Oracle background, and I'm used to the concept of OS users that can login to Oracle with DBA privileges in the machine the DB is installed on using their OS credentials as long as the OS user is part of a certain OS group (oinstall) using the below syntax:

sqlplus / as sysdba

In a machine (Oracle Linux 6.3) where Sybase ASE (15.7) is installed, is there any way I can login the same way as sa user?!


texts: switch TOP clause between percentage and rows by parameter

texts:
For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.

I have two ideas

Calling two different sub stored procedure based on passed param.

if @param ='percent'
begin
      exec sp_data_TOP_by_Percent
end
if @param ='perRow'
begin
    exec sp_data_TOP_by_PerRow
end

other idea is to make dynamic TSQL query something like this

declare @command nchar(max)

select @command = 'select top(10) '
                   + case @param
                              when 'percent' then percent
                              else ' '
                     end
                   + ' * '
                   + 'from table
                        order by 1';
exec sp_executesql @command

Is there third solution for something like this ?
What is better approach ?
First one avoiding dynamic TSQL but is harder to maintain code in two places.
I am using MSSQL2005 as databse.


texts: allow multiple NULL values in unique key

texts:
I am trying to allow for multiple NULL values in a unique key as allowed by ANSI SQL. E.g. in the below table I want to allow multiple NULL values in column f1 and still have a unique constraint on it:

CREATE TABLE foo (
    i UNSIGNED INTEGER NOT NULL,
    f1 VARCHAR NULL
);
ALTER TABLE foo ADD PRIMARY KEY (i);

The naive approach fails:

CREATE UNIQUE INDEX foo ON foo(f1);
INSERT INTO foo(i, f1)
select 1, null  union all
select 2, null;

… the above fails with:

  Attempt to insert duplicate key row in object 'foo' with unique index
  'foo_f1_unq'

There's two approaches that apparently work for SQL Server:

 workaround #1: the null buster

This is described here but it doesn't work with Sybase ASE. The below fails:

CREATE TABLE foo (
    i UNSIGNED INTEGER NOT NULL,
    f1 VARCHAR NULL
);
ALTER TABLE foo ADD PRIMARY KEY (i);

CREATE UNIQUE INDEX foo ON foo(f1) ;
ALTER TABLE foo ADD f1comp AS (CASE WHEN f1 IS NULL THEN CAST(i AS VARCHAR) ELSE 'non-null' END);
ALTER TABLE foo ADD CONSTRAINT foo_f1_unq UNIQUE(f1, f1comp)

… with:

  Virtual computed column 'f1comp' cannot be referenced in any
  constraints.

 workaround #2: create an index only on the non-null columns

CREATE TABLE foo (
    i UNSIGNED INTEGER NOT NULL,
    f1 VARCHAR NULL
);
ALTER TABLE foo ADD PRIMARY KEY (i);

CREATE UNIQUE INDEX foo ON foo(f1) WHERE f1 IS NOT NULL;

The above fails with a syntax error:

  Incorrect syntax near the keyword 'WHERE'.

So my question is: how can I achieve this in Sybase ASE? Alternatively, is there a way to create a stored procedure that will be triggered on every insert or update of table foo and will raise an exception if a non-null duplicate value is found?


texts:
We have different drivers like ODBC, OLE DB, JDBC. I understood when we want to work with Excel in SQL Server, we need to load different driver. But my question is why we need different drivers, what work they do? Will drivers be at both client and server end? Suppose I am running SQL query from my local machine, will there be drivers on my machine? So far my search ended here, any info would be greatly appreciated.

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-jdbc.html


texts: SQL Server Select line where jobCategoryKey EXISTS once for one candidateKey

texts:
I'm blocked with an SQL Query that I have to made on SQL Server 2014.

Here is my table TR_Candidate_To_Jobs_Category.

CREATE TABLE [dbo].[TR_Candidate_To_Jobs_Category]
       ( [candidateKey] [int] NOT NULL,
         [jobCategoryKey] [int] NOT NULL,
          CONSTRAINT [PK_TR_Candidate_To_Jobs_Category] PRIMARY KEY CLUSTERED
            ( [candidateKey] ASC, [jobCategoryKey] ASC )
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
       ) ON [PRIMARY]

INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (2, 223)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (2, 1245)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (2, 225)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (5, 137)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (5, 142)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (5, 143)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (6, 1245)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (6, 223)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (6, 225)

I have to find all the candidates which have the jobCategoryKey = 1245 and only this one. So they may not have one or more other jobCategoryKey or zero jobCategoryKey.

I tried with HAVING, GROUP BY and so on, but I didn't find the right solution.


texts: Add info to mysql view around an IP address - 10.10.1.100 to https://10.10.1.100

texts:
I have built a database in MySQL to replace the use of MANY excel spreadsheets that document our servers, network, IP addresses, etc.

In that database I have created a view called DRAC (dell remote access card) that pulls a server name, asset tag, and IP address.

SELECT Matrix.Name AS Name,
  Matrix.DracIP AS DracIP,
  Matrix.Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

I have been able to pull this information to a webpage (w/ DokuWiki+sqlcomp plugin) and it generates a nice table for me.

I was wondering if there was some kind of foo I could use to translate the ip address from this: "10.10.1.100" into this "https://10.10.1.100" within the view so it will display on the page as links that people can use instead of just a bare IP.

EDIT: I ended up w/ this code for those that are interested.

SELECT Matrix.Name AS Name,
  Concat('', Matrix.DracIP,'') AS DracIP,
  Matrix.Asset AS Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'


texts: Foreign key references on column definitions are ignored. Feature, not a bug — why?

texts:
Good old references constrains. They work like a charm when defined at the table level.

create table foo (id int primary key);

create table bar (id int, foreign key(id) references foo(id));
insert into bar values (1);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (...)

But if you come from another ecosystem and are used to occasionally define foreign key constrains at the column level, this is what happens:

create table baz (id int references foo(id));
insert into baz values (1); -- happily takes a value that isn't there in foo
select id from baz;
-- 1

What happens is that the references has been recognized, but ignored.

It turns out that this is not a bug. The MySQL documentation says they do it, and that's all you need to know:

  MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

The MariaDB documentation is slightly more verbose on their rationale:

  MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. However, only the syntax described below creates foreign keys.

Now what could be the use for this "feature" that helps "compatibility" with other DBMS — and the standard — by silently breaking the very purpose of the reference, while at the same time, correctly implementing it does not look like a big effort since foreign key constrains are indeed enforced when declared at the table level? And don't tell me this cannot be fixed because people rely on the fact that foreign constrains can be broken when declared at the column level.

Please help me make sense out of this.

EDIT: I just realized that by "compatibility with other DBMS", the MariaDB documentation may actually be referring to MySQL. This could either be a good motive for MariaDB to stick to the (unmotivated) behavior of MySQL, or a missed opportunity to improve their fork.


texts:
I need to create a snapshot of one of our mirror databases.

I have gone through a couple of links and it looks like querying can be achieved here by creating a snapshot at a mirror server.

However, I would need your help in understanding this for the DB in my case, where it is over 16 TBs and is used 365*7. Things like when you should create snap files and how much extra storage would be required in setting this snapshot database.

As far as I can understand, it should not be another 16 TB, apart from the  reserved 16 TB of the mirrored database. Also how would one ensure that a snapshot is in sync with principal with acceptable refresh or lag period of 30 minutes?

Please provide some advices.


texts: Adding a clustered index to a heap table

texts:
I've noticed that we have a few heap tables on our database, they all have a non clustered index on the primary key (which is an Int). The biggest table has over 2 million rows, a forwarded record count of 663,479 and page count of 609,028. Also noticed that the forwarded records per second are 972 on the server.

From my limited knowledge and a bit of googling this all looks pretty bad so i'm looking into adding a clustered index to this table.

Would you agree this is the right thing to do?
If so what are the best practices for adding a clustered index to this table, i
assume i will have to remove the existing non clustered index on the primary key (and any foreign keys referencing it) and then add the clustered index and recreate the foreign keys? Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?
Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?

Sorry if i haven't provided enough information, let me know if you need to know anything else to be able to help, thanks!


texts: Does PostgreSQL ever retry failed transactions internally?

texts:
I am writing an application in Python and PostgreSQL (using psycopg2) and in one section we are using the following pattern.

As separate python commands which I assume translates to separate calls to PostgreSQL

1) BEGIN

2) create temporary table AAA as select ... from ...

3) create temporary table BBB as select ... from AAA

4) create temporary table CCC as select ... from BBB

5) insert into YYY select ... from CCC

6) END

At no point do we ever read any data back from the transaction making it a blind write.
If this series of statements would cause a conflict with another transaction and fail would PostgreSQL restart it internally or would that revert to me the same as if I was mixing in select statements into the above code.

Would it make a difference if I converted all the temporary statements to common table expressions using the "with" construct? From the perspective of needing to do manual transaction restarts that is.

At what point if ever does PostgreSQL ever restart transactions internally, does multiple statements like that work? Will it work for a single statement as with "with"? Will it never work?

ps. I can not release the full code as it apart from being several pages long is the property of my employer.


texts:
I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow.

My code looks like this:

SET @table_ = CONCAT(tablename, '_meta');
CREATE TABLE @table_ (name varchar(50), value varchar(50)) ENGINE=MyISAM;

Where tablename is the in parameter (as varchar(50) utf-8)

But I always face this error:

  Syntax error near 'CREATE TABLE @table_ (name varchar(50), value
  varchar(50)) ENGINE=MyISAM'

Am I missing something? Anyone has a hint?


texts: what will cause a sql server AG readonly replica to stop serving readonly clients?

texts:
I understand that if a failover happens (among any nodes in an AG), then all nodes lose their existing connections.
And I assume that if a primary suspends data movement, that will have no effect on secondaries (just a potential pile of logs).
But suppose the primary node goes down?
Or a database is removed from the AG on the primary?
Will the secondary replicas keep doing their thing in those scenarios?


texts: How to recover a database from a failed Availability Group?

texts: We are currently testing a SQL Server 2012 AlwaysOn HA implementation where we have 2 out of the 3 nodes are synchronous.  The third, asynchronous node, does not have a vote in the quorum.  There is a scenario in which the first two nodes experience complete failure.  When this happens, the third node is up and running but it has it's database in the AG marked as "Recovery Pending".  How can I get this database out of this state and make it available?


texts: What are the top 5 design decisions which need to be made when implementing a database?

texts:
I am implementing an Oracle System to store event alerts from an application so I guess all the schema, database design will be handled by the application through a sript.

I am new to Database design, what are the top 5 design decisions which need to be made when implementing a database?


texts: After successfully executing this query, how do go I back to the previous stage in MySQL?


texts:
I have a row which primary key value is 4 and there are another 8 values.
Those are 45  78 89 54 98  14  19  70. I want to select the average value, ie 58.37 from this row. How do I do this ?
The avg() function takes one argument only but i need to send 8 arguments.
My table picture is


texts: dtexec returns immediately without any messages

texts:
I'm trying to setup a command-line call to dtexec to validate packages, using the project model.  I'm calling dtexec like this:

dtexec /val /proj my.ispac /pack my.dtsx  /rep v

Though the real paths to the ispac and dtsx are longer.  WHen I run this, all I see on the console is:

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.7001.0 for 32-bit
Copyright (C) 2016 Microsoft. All rights reserved.

Started:  10:40:01 AM
PS D:\>

The whole thing runs in under a second which leads me to believe that it did nothing.  And no messages of any kind are issued., even though I asked for verbose reporting.

Any ideas out there as to how to make this work?


texts: INNER JOIN taking so long for even simple query

texts:
My inner queries when executed independently works fast(<2 sec). I have then applied INNER JOIN on two sub query results and final output takes around 25 minutes.
SELECT DISTINCT
tableA.consumer,
tableA.ltm,
tableA.consumer_dpip,
tableA.consumer_dpname,
tableA.consumer_port,
tableB.provider,
tableB.provider_DPIP,
tableB.provider_DPNAME,
CASE when instr(tableB.provider_DPIP, tableA.consumer_dpip) = 0
then  tableB.provider_port
else null end ,
tableB.endpoint,
tableB.endpoint_port
FROM
(
select DISTINCT
sender.consumername   AS consumer,
devices.ltmip         AS ltm,
devices.dpip          AS consumer_dpip,
devices.dpname        AS consumer_dpname,
appdevices.port       AS consumer_port
from
vz.t_vz_mapnames                  mapnames,
vz.t_vz_sender                    sender,
vz.t_vz_application_devices       appdevices,
vz.t_vz_device_info               devices
WHERE
mapnames.sno = sender.parentid
AND appdevices.appid = sender.consumername
AND upper(appdevices.zone) = devices.zone
 AND mapnames.type = 'consumerRoutingTable'
AND appdevices.type = 'Consumer'
AND devices.env = 'Production'
) tableA
inner join
(
select  sender.consumername   AS consumer,
sender.providername   AS provider,
LISTAGG(distinct(devices.dpip), ',') WITHIN GROUP(order by devices.dpip) as provider_DPIP,
LISTAGG(distinct(devices.dpname), ',') WITHIN GROUP(order by devices.dpname) as provider_DPNAME,
appdevices.port as provider_port,
dest.url              AS endpoint,
dest.port             AS endpoint_port
from
vz.t_vz_mapnames                  mapnames,
vz.t_vz_sender                    sender,
vz.t_vz_application_devices       appdevices,
vz.t_vz_device_info               devices,
 vz.t_vz_ep_destination_template   dest
WHERE
mapnames.sno = sender.parentid
AND appdevices.appid = sender.providername
AND upper(appdevices.zone) = devices.zone
AND dest.applicationid = sender.providername
AND mapnames.type = 'consumerRoutingTable'
AND appdevices.type = 'Provider'
AND devices.env = 'Production'
AND dest.env = 'PROD'
 group by sender.consumername,sender.providername,appdevices.port, dest.url, dest.port
)tableB
on tablea.consumer=tableb.consumer  and tableB.endpoint is not null and tableB.endpoint<>' '
order by tableA.consumer, tableA.ltm

Indexes on the tables:-
index vz.IDX_SNO on vz.t_vz_mapnames(sno);

index vz.IDX_PID on vz.t_vz_sender(PARENTID);
index vz.IDX_CNAME on vz.t_vz_sender(CONSUMERNAME);
index vz.IDX_PNAME on vz.t_vz_sender(PROVIDERNAME);

index vz.IDX_APPID on vz.t_vz_application_devices(APPID);
index vz.IDX_ZONE on vz.t_vz_application_devices(ZONE);
index vz.IDX_PORT on vz.t_vz_application_devices(PORT);
index vz.IDX_TYPE on vz.t_vz_application_devices(TYPE);

index vz.IDX_DP on vz.t_vz_device_info(DPNAME,DPIP,LTMIP);
index vz.IDX_DPDPIP on vz.t_vz_device_info(DPIP);
index vz.IDX_DPNAME on vz.t_vz_device_info(DPNAME);

Execution plan:

Am I doing anything wrong here or do I need compound indexes added ? Any inputs highly appreciated.


texts: MySQL Workbench Schema tables are missing

texts: just started exploring MySQL Workbench and I recently restarted MySQL Workbench and I couldn't find the table in the model. I believe it's still there but I'm not sure how to display it like before. On the second picture(earlier), I can see and edit all my tables


texts: Equivalent Linux commands for Windows ones to create user and database

texts:
There is a manual for configuring PostgreSQL on Windows with these commands:
C:\"Program Files"\PostgreSQL\12\bin\createuser.exe --username=postgres --superuser --pwprompt saleor

C:\"Program Files"\PostgreSQL\12\bin\createdb.exe --username=postgres --owner=saleor saleor

I'm going to do the same on Linux, but I'm not sure how.
I know that I have to do:
> sudo su postgres
> psql

psql (13.3)
Type "help" for help.

postgres=#

But I'm not sure what to do next. I appreciate any help.


texts: Visual Studio Code Postgres SQL Connection

texts:
I would like to use Visual Studio Code to preform my PostgreSQL queries, and general DB maintenance as an alternative to pgAdmin4. I have read the documentation included with the appropriate extention (PostgreSQL by MS). When I try to Create A New Profile I get the following prompt:

I am working from an Apple Mac so I attempted localhost at first. That did not work. I tried the ip address of the local computer with the same result. I attempted just giving the name of the DB, but that did not work either. Any suggestions as to what I can try next. I am terrible at spelling and rembering names so I would like some form of IDE support when I am working.

This is the error I get:
PostgreSQL: Failed to connect: could not translate host name "localhost\Local" to address: nodename nor servname provided, or not known


texts: getting SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)

texts:
I am getting error with MySQL NDB Cluster 7.3.5 as described below:

I have two tables:

-User Profiles

-User Services

User services is a child table which contains foreign key constraint (user_profile_id).

insert into user_services (address_type, apn_id, ggsn_id, ip_address, user_profile_id, vpn_id, id) values ('DYNAMIC', 2, 'GGSN01', null, 250, 3, 102);
/* SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (Unknown error code) */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec. */

The strange thing here is that the parent table User Profiles has the entry with Id 250, but still I am getting error when inserting a row using 250 as user_profile_id.

I have checked the log files for data nodes(ndb_id_out.log) and API node(mysqld.id.err) but no luck!

I don't know where to check the logs for these kind of errors.

DDL of Schemas are as described below:

CREATE TABLE `user_services` (
  `id` decimal(22,0) NOT NULL,
  `apn_id` decimal(22,0) DEFAULT NULL,
  `ggsn_id` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `vpn_id` decimal(22,0) NOT NULL,
  `user_profile_id` decimal(22,0) NOT NULL,
  `ip_address` varchar(15) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `address_type` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ser_profile` (`user_profile_id`),
  CONSTRAINT `fk_ser_profile` FOREIGN KEY (`user_profile_id`) REFERENCES `user_profiles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1

CREATE TABLE `user_profiles` (
    `id` DECIMAL(22,0) NOT NULL,
    `group_id` DECIMAL(22,0) NOT NULL,
    `imsi` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_bin',
    `username` VARCHAR(32) NOT NULL COLLATE 'latin1_bin',
    `password` VARCHAR(32) NOT NULL COLLATE 'latin1_bin',
    `last_modified` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `unique_username` (`username`)
)
COLLATE='latin1_swedish_ci'
ENGINE=NDBCLUSTER;

can you help me on this ?


texts: Will {fn concat} work without issue in MySQL, SQL Server and Oracle?

texts:
According to this (quite old) article the principle behind ODBC escape sequences is that

  The ODBC driver reads the escape sequence and translates it into the
  DBMS-specific syntax before sending the query to the database

However if I try the following

SQL Server

CREATE PROCEDURE foo_bar
AS
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

My SQL

DELIMITER $$

CREATE PROCEDURE foo_bar()
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

The escape sequences remain untranslated in the object definition and calling the routines works fine. That indicates to me that the RDBMSs themselves natively understand the syntax rather than relying on translation so will work independent of connection method. Is that understanding correct and if so does this also apply to Oracle?


texts:
this query:

select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual

will return:

Eighteenth MARCH , Two Thousand Fourteen

i am wondering if there are any additional formatting keywords that will allow including a list of characters like "of" , "the" ... inorder to have an output like:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen

or is that requires splitting the date conversion and using a concat function? thanks

for example :

select 'Today is the ' || to_char(sysdate, 'Ddspth') || ' of' || to_char(sysdate,' MONTH, Yyyysp') from dual


texts: What column type can store txid_current value?

texts:
AFAIK txid_current returns uint 64 value (source).
However, the biggest int column type is bigint which is signed (docs).
From what I understand, NUMERIC type is slow and should be used "for storing monetary amounts and other quantities where exactness is required" (docs).
Should I store the txid_current value as text?
I need to perform mainly greater than queries on this field:
select * from t where fxid > 438298432

What would be the most efficient (from search speed POV) for full-transaction-id (unsigned integer 64)?


texts: What is the appropriate GCC version I should use for building my External Procedure?

texts:
When working with Oracle External procedures, what is the GCC version I should use to build my procedure?

Does Oracle get shipped with a GCC version and libraries that it keeps in one of its own folders? Or the correct version to use is the one of Linux Server where Oracle is installed?

If I do gcc --version on a Linux computer, it gives me the GCC version, if I do that on the Linux Server with my Oracle installation, is that the version of GCC I should use to build my External Procedure?

I am asking this because I have been building with the GCC version that gcc --version indicates, but we are experiencing some weird issues that could be due to differences in the libraries' versions.


texts:
I am unable to change DDL for the tables, but I need to join on two text fields.  I get an error of

  Msg 402, Level 16, State 1, Line 23
  The data types text and text are incompatible in the equal to operator.

Below is sample DDL that illustrates my issue, how do I overcome?

Declare @Table1 Table(id text,name nvarchar(200))
Insert Into @Table1 (id, name) Values
('123', 'Joe'), ('222.00', 'Jim')
Declare @Table2 Table (id text,green nvarchar(200))
Insert Into @Table2 (id, green) Values
('123', 'JDSKLJS'), ('222.00', 'KLJKLJJKL')
Select
*
FROM @Table1 t1
INNER JOIN @Table2 t2
ON t1.id = t2.id


texts: Can PostgreSQL data files be moved from macOS to Fedora?

texts:
Can I copy the PostgreSQL data directory on a macOS (10.12.3) machine to a Fedora (25) machine then expect PostgreSQL to work on the destination?

I specifically want to avoid pg_dump/pg_restore because index creation would take forever on the (much slower) destination disk.

PostgreSQL versions are identical on both (9.6.2).  Both are 64 bit Intel machines.  The macOS machine is running PostgreSQL from Homebrew.  The Fedora machine is running PostgreSQL from the official yum repo

Related question: is there somewhere in the official PostgreSQL docs that gives an answer?  I searched a bit and couldn't find it.


texts: MS SQL Server 2016 Oprimal Disk Configuration

texts:
New server build for SQL SERVER STANDARD 2016 install. Total of 12 disks: 6 standard 15K HDs and 6 SSDs. This is the disk setup I am going for.
Could this setup be improved on, if so how what would change?

I could move TEMP DB mdf to DATA Drive and have TEMP DB log with other DB Logs on SSD LOG drive.

This will be for a SQL Server Production environment only.
Optimizing for speed but also HA to an extent.
Backups will get copied to an offsite location throughout the day.
Server will be mirrored not AG. Although mirroring is deprecated, due to install being Standard Edition I don't see benefits of AG at this point with 2 servers.
Biggest waits are LATCH_EX followed by CX_PACKET

Also looking to replicate some data for reporting purposes.

LOG average file size is 9.16 MB over past 6 months, Log backup every 15 mins
MAX size has been 389.47MB in past 6 months

TEMP DB info for past 6 months


texts: Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

texts:
I've got about a billion rows of data in a table with a name and an integer in the range 1-288.  For a given name, every int is unique, and not every possible integer in the range is present--so there are gaps.

This query generates an example case:

--what I have:
SELECT *
FROM ( VALUES ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) AS baz ("name", "int")

I'd like to generate a lookup table with a row for each name and sequence of contiguous integers.  Each such row would contain:

name   --  the value of the name column
start  --  the first integer in the contiguous sequence
end    --  the final value in the contiguous sequence
span   --  end - start + 1

This query generates example output for the above example:

--what I need:
SELECT *
FROM ( VALUES ('foo', 2, 4, 3),
              ('foo', 10, 11, 2),
              ('foo', 13, 13, 1),
              ('bar', 1, 3, 3)
     ) AS contiguous_ranges ("name", "start", "end", span)

Because I have so many rows, more efficient is better.  That said, I only have to run this query once, so it isn't an absolute requirement.

Thanks in advance!

Edit:

I should add that PL/pgSQL solutions are welcome (please explain any Fancy Tricks--I'm still new to PL/pgSQL).


texts: Where does master.dacpac come from in VS 2019?

texts:
We have some build tasks that run and for the life of me I can't figure out why the ms build tools isn't installing the master.dacpac file needed for the master and system dacpac files.

Here's where it lives now:

C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\150\SQLSchemas

Currently, in VS2019 Professional it is added with the Data Storage and Processing Workload.  SQL Server Data Tools is a dependency for several other workloads, such as:

ASP.NET and web development
ASP.NET and web development tools
Azure Data Lake and Stream Analytics Tools
Cloud tools for web development

In MS Build Tools, I noticed that the dacpac file isn't generated after installing the Data Tools workload.

Am I looking in the wrong place?  What is generating the master.dacpac and the system.dacpac in C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\150\SQLSchemas?


texts:
Do the drives that contain the database have to be the exact same size on the primary server as secondary?
I currently have a primary database for OLTP and a standby database used for reporting.
I'm going to create 2 new standby databases then cut over to them as an Always On Availability Group.
The 2 AG databases must be exact matches but can't the new standby disks be different size than the current primary - as long as the drive letters match and the files fit?
Thank You!


texts: Stored Procedure taking longer than a normal query

texts:
I have a stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query.

Procedure

    DELIMITER $$
DROP PROCEDURE IF EXISTS `local_db`.`getAllMatchedStockByCompany` $$
CREATE
    PROCEDURE `local_db`.`getAllMatchedStockByCompany`(
    IN Company_ID BIGINT(20)
    )
    BEGIN
        SELECT t1.*
    FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),'&nbsp;',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'&rsquo;',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive
           ,t2.CompanyID FROM t2
          ) UNION
          (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),'&nbsp;',''),' ',
                30),'\r\n\t\r\n\t',''),'&#39;','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive
        ,t3.CompanyID FROM t3
          )
         ) t1
         WHERE CompanyID = Company_ID AND IsActive = 1
         ORDER BY ScheduledTime DESC;
    END$$

DELIMITER ;

The total execution time of this is 16 seconds with 1 row.

If I use below query:

SELECT t1.*
    FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),'&nbsp;',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'&rsquo;',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive
           ,t2.CompanyID FROM t2
          ) UNION
          (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),'&nbsp;',''),' ',
                30),'\r\n\t\r\n\t',''),'&#39;','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive
        ,t3.CompanyID FROM t3
          )
         ) t1
         WHERE CompanyID = 931 AND IsActive = 1
         ORDER BY ScheduledTime DESC;

It took only .5 to 1 seconds with one row.

Can anyone of you let me why this happening or how I can improve my SP execution time?


texts:
I was wondering if anyone could help me with pivoting data in sql? I am a newbies and need some help

I want to pivot data based on "LastLogOn" column. Is it possible to do in SQL?

From:

To:


texts:
Error log is:

016-02-21 21:22:34 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
....
....
2016-02-21 21:22:37 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:22:37 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:22:37 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:22:37 GET LOG:  CreateProcess call failed: A blocking operation was interrupted by a call to WSACancelBlockingCall.

     (error code 299)
2016-02-21 21:22:37 GET LOG:  could not fork new process for connection: No error
2016-02-21 21:22:37 GET LOG:  incomplete startup packet
2016-02-21 21:22:38 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:22:38 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:22:42 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:22:42 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:24:26 GET LOG:  could not receive data from client: An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.
2016-02-21 21:24:26 GET LOG:  incomplete startup packet
2016-02-21 21:24:29 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:24:45 GET PANIC:  could not write to log file 000000010000000100000041 at offset 4259840, length 8192: Invalid argument
2016-02-21 21:24:45 GET STATEMENT:  COMMIT
2016-02-21 21:24:53 GET LOG:  server process (PID 470944) was terminated by exception 0xC0000409
2016-02-21 21:24:53 GET DETAIL:  Failed process was running: COMMIT
2016-02-21 21:24:53 GET HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2016-02-21 21:24:53 GET LOG:  terminating any other active server processes

Can anyone help to solve the problem? I spent many hours but didn't find a solution.

Postgres version 9.4
Operating System: Windows

postgresql.conf is:

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 128MB
dynamic_shared_memory_type = windows

pg_hba.conf is:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5


texts: How to measure the performance improvement that NUMA brings?

texts:
I am testing how one of our stored procedure is working on SQL Server Standard vs SQL Server Enterprise editions.

I have created two virtual machines and install a SQL Server(SQL Server 2014) and restore a database on each instance. Then I have generated some test data (large volume but the same on each database) and started testing if there is an execution time difference.

I have been told that there should be 4x better performance on the Enterprise edition when a large volume of data is used because there is NUMA Aware Large Page Memory and Buffer Array Allocation.

So far, there is no such difference and the execution time is almost the same (a - or + second difference).

I cannot say I understand completely what is NUMA and how it works, but I guess the hardware NUMA is on as the following query returns me 0 and 64:

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

I am not interested in the software Numa.

Could anyone tell if such 4x optimization really exists and should I continue generated more data in order to see it?

This is the VM set up:


texts:
Wondering if I could get some insight on a design I saw recently. A collection can contain a few or tens of thousands of documents.

Each document has the same structure of _id and JSONValue. The latter just contains a large string which contains the JSON for that document.

I can’t quite understand why this is useful? You can’t query it easily. It seems to me like MongoDB is just a data store with the application doing all the grunt work.

Am I missing the point? Can you suggest some use cases for this design approach?


texts:
I know if I encrypt mariadb,  no one can steal my data.
What's the disadvantage of  encrypt  mariadb?


texts: SQL - Sum amounts daily from non-daily data


texts:
I receive randomly errors for database connections, like: socket closed.
I observed postgres logs and found suspicious line: database system was interrupted. Can that happen that postgres drops the connections while performing vacuum? Is any other reasons for making postgres drop connections?

    2018-01-26 04:38:15 EST [31]: [1-1] user=,db=,app=,client=LOG:  autovacuum launcher started
    2018-01-26 04:38:48 EST [63]: [1-1] user=postgres,db=osm,app=[unknown],client=10.10.0.9LOG:  process 63 still waiting for ShareLock on transaction 107312 after 1000.088 ms
    2018-01-26 04:38:48 EST [63]: [2-1] user=postgres,db=osm,app=[unknown],client=10.10.0.9DETAIL:  Process holding the lock: 50. Wait queue: 63.
    2018-01-26 04:38:48 EST [63]: [3-1] user=postgres,db=osm,app=[unknown],client=10.10.0.9CONTEXT:  while updating tuple (0,125) in relation "databasechangeloglock"
    2018-01-26 04:38:48 EST [63]: [4-1] user=postgres,db=osm,app=[unknown],client=10.10.0.9STATEMENT:  UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '172.18.0.5 (172.18.0.5)', LOCKGRANTED = '2018-01-26 09:38:47.340' WHERE ID = 1 AND LOCKED = FALSE
    2018-01-26 04:39:07 EST [51]: [1-1] user=readonly,db=osm,app=pgAdmin 4 - CONN:1678543,client=10.255.0.2ERROR:  permission denied for relation global_locks
    2018-01-26 04:39:07 EST [51]: [2-1] user=readonly,db=osm,app=pgAdmin 4 - CONN:1678543,client=10.255.0.2STATEMENT:  select * from global_locks
    2018-01-26 04:40:27 EST [91]: [1-1] user=postgres,db=osm,app=[unknown],client=10.10.0.14LOG:  process 91 still waiting for ExclusiveLock on tuple (0,125) of relation 16502 of database 16443 after 1000.122 ms
    2018-01-26 04:40:27 EST [91]: [2-1] user=postgres,db=osm,app=[unknown],client=10.10.0.14DETAIL:  Process holding the lock: 63. Wait queue: 91.
    2018-01-26 04:40:27 EST [91]: [3-1] user=postgres,db=osm,app=[unknown],client=10.10.0.14STATEMENT:  UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '172.18.0.5 (172.18.0.5)', LOCKGRANTED = '2018-01-26 09:40:26.789' WHERE ID = 1 AND LOCKED = FALSE
    2018-01-26 04:40:40 EST [104]: [1-1] user=postgres,db=osm,app=[unknown],client=10.10.0.15LOG:  process 104 still waiting for ExclusiveLock on tuple (0,125) of relation 16502 of database 16443 after 1000.110 ms
    2018-01-26 04:40:40 EST [104]: [2-1] user=postgres,db=osm,app=[unknown],client=10.10.0.15DETAIL:  Process holding the lock: 63. Wait queue: 91, 104.
    2018-01-26 04:40:40 EST [104]: [3-1] user=postgres,db=osm,app=[unknown],client=10.10.0.15STATEMENT:  UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '172.18.0.5 (172.18.0.5)', LOCKGRANTED = '2018-01-26 09:40:39.211' WHERE ID = 1 AND LOCKED = FALSE
    2018-01-26 04:41:52 EST [25]: [1-1] user=,db=,app=,client=LOG:  database system was interrupted; last known up at 2018-01-26 04:38:15 EST
    2018-01-26 04:41:52 EST [25]: [2-1] user=,db=,app=,client=LOG:  database system was not properly shut down; automatic recovery in progress
    2018-01-26 04:41:52 EST [25]: [3-1] user=,db=,app=,client=LOG:  redo starts at 0/6DDA36F0
    2018-01-26 04:41:52 EST [25]: [4-1] user=,db=,app=,client=LOG:  invalid record length at 0/6DDA51F8
    2018-01-26 04:41:52 EST [25]: [5-1] user=,db=,app=,client=LOG:  redo done at 0/6DDA51C0
    2018-01-26 04:41:52 EST [25]: [6-1] user=,db=,app=,client=LOG:  last completed transaction was at log time 2018-01-26 04:38:39.909895-05
    2018-01-26 04:41:52 EST [25]: [7-1] user=,db=,app=,client=LOG:  checkpoint starting: end-of-recovery immediate
    2018-01-26 04:41:52 EST [25]: [8-1] user=,db=,app=,client=LOG:  checkpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.002 s, total=0.010 s; sync files=1, longest=0.002 s, average=0.002 s; distance=6 kB, estimate=6 kB
    2018-01-26 04:41:52 EST [25]: [9-1] user=,db=,app=,client=LOG:  MultiXact member wraparound protections are now enabled
    2018-01-26 04:41:52 EST [22]: [4-1] user=,db=,app=,client=LOG:  database system is ready to accept connections


texts: Rename foreign keys en masse on SQL Server 2017

texts:
I have a database that was recently migrated from Oracle to SQL Server 2017. Whoever initially setup the database set all the foreign keys to be system generated names.  In other words if I look at the foreign keys in a table they are named (SYS_C005214, SYS_C005271, SYS_C005272). I would prefer to have these all renamed to:

FK_ChildTable_childColumn_ParentTable_parentColumn

I would like to setup a script that will change all of the foreign keys to the approprite name. Is there a way to do this without having to go into each key to see it's definition and then renaming it by hand?

Note: I now realize after running Aaron Bertrand's script, I also need this for primary keys.


texts: Weak entity and one-one or many-many relationship

texts:
In Entity Relationship Model, can the supporting (or identifying) relationships of the weak entities be many-many or one-one rather than many-one?

Suppose I have a customer entity (C), a video entity (V) and a weak entity reservation (R). C - R has a one-many relationship and it's a supporting relationship for R. However between V - R the same one-many relationship wouldn't make sense, since a video shouldn't appear in two different reservations.

So I wonder if a one-one relationship can be a supporting one for the weak entity? Or should it be a normal one-one instead of a supporting one?


texts: Insert data through linked server into two remote related tables

texts:
I want insert data through linked server into two remote related tables as below:

(SQL 2008R2 on both machines)

SERVER_AAA:

create table #server_AAA_table_1
(
        [var_1] [int] NULL,
        [var_2] [bigint] NULL,
        [var_3] [varchar](64) NULL
)

create table #server_AAA_table_2
(
        [var_4] [char](1)NULL,
        [var_5] [int] NULL,
        [var_6] [int] NULL,
        [var_7] [int] NULL,
        [id_1] [int]  NULL
)

SERVER_BBB:

create table server_BBB_table_1
(
        [id_B_1] [int] IDENTITY(1,1) NOT NULL,
        [var_1] [int] NULL,
        [var_2] [bigint] NULL,
        [var_3] [varchar](64) NULL,
constraint [PK_server_BBB_table_1] primary key CLUSTERED ([id_B_1] ASC)
)

create table server_BBB_table_2
(
        [id_B_2] [int] IDENTITY(1,1) NOT NULL,
        [var_4] [char](1)NULL,
        [var_5] [int] NULL,
        [var_6] [int] NULL,
        [var_7] [int] NULL,
        [id_B_1] [int] NULL,
constraint [PK_server_BBB_table_2] primary key CLUSTERED ([id_B_2] ASC)
)

alter table server_BBB_table_2 with check add  constraint [FK_server_BBB_table_2_server_BBB_table_1] foreign key([id_B_1])
references server_BBB_table_1([id_B_1])
on update cascade
on delete cascade
go

SERVER_AAA:

Linked server: LNK_AAA_2_BBB

declare @id_1 int

insert into LNK_AAA_2_BBB.server_BBB_DB.server_BBB_table_1 select * from #server_AAA_table_1

set @id_1 = scope_identity();

/*
 some sql code: @id_1 + #server_AAA_table_2
*/

insert into LNK_AAA_2_BBB.server_BBB_DB.server_BBB_table_2 select * from #server_AAA_table_2

Unfortunately, in any way I can't get scope_identity() value from first insert, I always get NULL.

I tried solution with OUTPUT and procedure, no success.

Is this possible to do via linked server?
Maybe I need some other solution?


texts: Why I don't need to COMMIT in database trigger?

texts:
We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database trigger:

CREATE OR REPLACE TRIGGER user_login_as
  AFTER LOGON
    ON SCHEMA
BEGIN
  INSERT INTO user_login_log(username, log_date, action) VALUES (user, sysdate, 'User has logged in');
END user_login_as;

The trigger does not contain autonomous transaction procedure with commit inside that, so who is commiting the insert? This triggger works like a charm and inserts new record into log table after user logon. It smells like hidden Oracle functionality and I can't find any reference in Oracle docs about that. I'm using Oracle11g.


texts: Exception when starting Snapshot Agent when setting up database replication

texts:
I am trying to set up replication between databases. I followed these tutorials:

Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)

and after:

Tutorial: Configure replication between two fully connected servers (transactional)

I went through first without any problems. Almost completed the second, but I got stuck on step View the status of snapshot generation

When I try to start the Snapshot Agent, I get an error:

  Exception Message: An unspecified error had occurred in the native SQL Server connection component.

I tried to search on the internet MSSQL_REPL55012 (that's error code I get), but didn't succeed.

I tried to look in SQL Profiler, Jobs history, Replication Monitor, even ran failing job step from PowerShell, but couldn't get any more info on that.

What might be the issue? Anybody else stumbled upon that?

I am using:

SSMS version 15.0.18206.0
SQL Server engine 15.0.2000.5

Also, I run two instances of SQL Server and have two Management Studios installed, if that's relevant.


texts: How does the operator "&" work in sql server?

texts:
I was running a trace on one of our test servers when someone did this:

and one of the queries I could catch in the trace was:

declare @UserOption int
select @UserOption=convert(int, c.value) from sys.configurations c where c.name='user options'

SELECT
CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions],
CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit],
CAST(@UserOption & 8 AS bit) AS [AnsiWarnings],
CAST(@UserOption & 16 AS bit) AS [AnsiPadding],
CAST(@UserOption & 32 AS bit) AS [AnsiNulls],
CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier],
CAST(@UserOption & 512 AS bit) AS [NoCount],
CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort],
CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]

there is lots of information that can fit in a int if you know -

How does the operator & work?


texts: Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution

texts:
We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine.  Our main database server has 16 physical cores with hyper threading and 2 numa nodes.  We have set the maxdop to 16 for this machine.
Edit: I had previously set it to 8 and saw the same issues.
The mirror host has a much weaker cpu, so we had to set the maxdop to 4.
When we are running an intense query on the principle database, we sometimes get an error:
Unexpected job failure.: System.Data.SqlClient.SqlException (0x80131904): The query processor could not start the necessary thread resources for parallel query execution.

I am wondering if could be due to the underpowered mirror server running out of resources while it is trying to commit all the transactions that are being fed across to it from the principal server.
I'm not a DBA, and we currently do not have one on staff (so I guess I am the DBA), so a lot of these settings and errors are foreign to me.
Edit2:  This question is different from the one suggested because I originally followed the directions and scripts in that post to set my MAXDOP to appropriate values.


texts: How to order databases by size? (For use in full backups)

texts:
Recently, I've been trying to make our company's full backup process more efficient. In order to do this, my team has decided that instructing the backup job to tackle the full backups starting with the smallest databases and ending with the largest is the best way to meet our objectives. To do this, I added a cursor to the existing job to set the database order before running:

DECLARE @db_name VARCHAR(100), @db_id INT, @db_size INT;

PRINT '**********Order Databases by Size**********'
PRINT ' ';

DECLARE DB_by_Size CURSOR FAST_FORWARD FOR
SELECT db.[name], db.database_id, mf.size
FROM sys.databases AS db INNER JOIN sys.master_files AS mf ON db.database_id=mf.database_id
WHERE mf.[type] <> 0 AND db.name <>'tempdb'
ORDER BY mf.size;

OPEN DB_by_Size

FETCH NEXT FROM DB_by_Size
    INTO @db_name, @db_id, @db_size

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC BackupScripts.dbo.DatabaseBackup @Databases = @db_name,
        @Directory = N'\\*****\Backups',
        @BackupType = N'Full',
        @Compress = N'Y',
        @LogToTable = N'Y',
        @Period = N'Weekly',
        @Execute = N'Y'

    FETCH NEXT FROM DB_by_Size
        INTO @db_name, @db_id, @db_size

END;
CLOSE DB_by_Size;
DEALLOCATE DB_by_Size;
GO

I've been running the job like this for a couple weeks and observing the run times for each database in the command logs:

USE BackupScripts

SELECT *
FROM dbo.commandlog
WHERE StartTime >= 'yy-mm-dd 00:00:01.000' AND StartTime <= 'yy-mm-dd 23:59:00.000' AND CommandType <> 'UPDATE_STATISTICS' AND CommandType = 'BACKUP_DATABASE' AND Command NOT LIKE '%\Nightly\%'
ORDER BY StartTime DESC

When I look at the results for the above query, the longest-running backups aren't happening at the end like I expected. The run times seem to be pretty arbitrary instead of more or less from shortest to longest like I was trying to do. I think this has something to do with the way I'm measuring size (correct me if I'm mistaken here), so I'm thinking mf.size doesn't seem to be a good measure to accomplish what I'm going for.

Does anyone know a better table to pull my database size information from instead of sys.master_files? Or a better way to gauge how long a database will take to run a full backup before it actually runs the job? I've been hunting around for awhile, and can't get an accurate read from the tables I'm finding. Thanks!