texts:
I'm working on a SQL Server 2012 database where the entity [scenario] has a One-To-Many relationship with a [client] table. The users working with this database might have access to only a subset of clients and for each client might have different type of read/write/delete permission. Hence the 2-dimension row level security. Below is an example of the type of design I'm thinking of:

User   Client     Read  Write  Delete
user1  no_client  True  True   False
user1  client1    True  False  False
user1  client2    True  True   True
user2  no_client  True  True   True
user2  client1    True  True   True
user2  client2    True  True   True

I have managed to implement this kind of design based on the SQL Server Label Security toolkit and its white paper. My implementation of the toolkit relies on the creation of three different roles for each client (i.e. client1Read, client1Write and client1Delete).

To me it does not seem a very good design given that the database might contains thousands of clients. Furthermore, my understanding is that this toolkit was based on the assumption that the set of label is fully known in advance. Extending the row security to new security labels is quite cumbersome.

Is there a better way to implement this kind of 2-dimension row level security?

EDIT: I think I might have found a decent solution. Here what I did:

Created a table that maps the users to the client id for each he/she has some sort of permission, with an additional field that specifies which kind of permission ('Read', 'Write' and 'Delete');
Implement the row level access through a view that filter the rows of the main scenario table using the table created above;
Prevent the insert/update/delete actions based on after insert triggers;


texts: How to provide the key value in a join when a match is not found

texts:
My DB2 database has the following tables:

CustomerLog

customer  version  userid  description
--------  -------  ------  ----------------------------
Orange    101      2       New customer
Orange    102      2       Bespoke widgets added
Orange    103      4       Fix errors after review
Orange    104      3       Priority bugfix
Peach     101      1       New customer
Peach     102      3       Custom screens for Peach
...

Users

userid  name
------  -------------------
1       Andrew
2       Belinda
3       Charles

With the select statement:

select c.customer, c.version, u.name, c.description
from customerlog c, users u
where c.customer='Orange'
and c.userid = u.userid

I get the following result:

customer  version  name        description
--------  -------  ----------  ----------------------------
Orange    101      Belinda     New customer
Orange    102      Belinda     Bespoke widgets added
Orange    104      Charles     Priority bugfix

If I instead used a left join, I could get the following row selected as well:

Orange    103      null        Fix errors after review

... but is there a way to get the userid in place of the null?  ie:

Orange    103      4           Fix errors after review


texts: Tracking the last execution date of a query

texts:
My manager is requesting to know when was the last time a number of reports were last ran to see if they are getting used. He requested info from our vendor and the vendor if coming to me for SA level permission to dig through the relative SQL Server.

Obviously, I won't be doing that but I would like to know is there a way to track the last time a specific query has been executed?

This database resides on an old SQL Server 2008 R2 install so I am sure my options are limited if not non-existent especially since he wants to look back as far as 18 months.

Just figured I would ask in case there might be at least a partial way to go back in history and track this information.


texts:
I'm using a jdbc connection to connect a vaadin application to a MySQL database. It works fine, but after a couple of uses it gets really slow, and even freezes the application to a point I have to restart the server.

Everything works fine in my local environment. It fails when I try to use it in an EC2 Linux server

This is the code I use to connect:

connectionProperties = new Properties();
    connectionProperties.put("user", "root");
    connectionProperties.put("password", dbPassword);
    try {
        connectionPool = new SimpleJDBCConnectionPool(
                "com.mysql.jdbc.Driver",
                "jdbc:mysql://localhost:3306/" + dbName, "root", dbPassword, 2, 50);
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + dbName, connectionProperties);
        statement = connection.createStatement();

    } catch (SQLException e1) {
        e1.printStackTrace();
    }
    dbContainers = new DataBaseContainers();

Thanks in advance for any help you can give me!!!


texts: How to make IntelliSense understand temp tables, linked servers and synonyms?

texts:
I'm using SSMS 17.6 which is the latest version at the time of posting. It is supposed to have the latest official IntelliSense version. However, it underlines my scripts with red squiggles where I'm using temp tables, linked server tables and synonyms. It's difficult to edit queries because I don't get correct error detection. Everything is filled with "errors", but queries run, and the results are what I want, as expected.

I've tried a few third-party addons like SQL Complete and SSMSBoost, but they don't seem to improve IntelliSense, but rather extend the SSMS in other ways like tooltips and popups. Or maybe I just haven't figured out how to use them for improving IntelliSense.

What can I do to make IntelliSense correctly handle these things? If there is no first-party way, then what third-party extensions exist and how to configure them to improve error highlighting?


texts: GZip existing varbinary(max) column in Sql Server 2008+

texts: I have an existing legacy table that is ~180GB in size due to a client application storing PDF files as varbinary.  I'd like to be able to compress that column for all rows using GZIP to help save space while I create a new solution (I was hoping there was a way to do it in SQL and not have to write client code for this).  I see that the COMPRESS method is available for Sql Server 2016 but I need a solution that will work with 2008.  Any ideas would be appreciated.


texts: Free Drive Space and SQL Server Agent Alerts

texts:
I need to receive an email when free drive space on E: or H: becomes less than 20%
I know that can be achieved by setting up a job that executes PowerShell or T-SQL scripts on a schedule, that checks for free drive space and sends an alert email when drive space is low
But the question is - can same task be achieved using just an Alert ?
There is alert type SQL Server performance condition alert
Is there an Object / Counter that can help to check for drive space and fire this alert if its value "Falls Below" specified threshold ?


texts:
I have an awesome script that gives vital data for running worker processes. What I'm attempting to do is to get these three values into a table so that they can be displayed all pretty-like. I previously used a .net extension to accomplish this but I've add problems with compatibility so I'd like to use strictly Powershell here. Here is my script:

$myprocess = "w3w*"
$filter = "*\process(" + $myprocess + ")*"
$pids = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\id process" -and     $_.path -like $filter} | select cookedvalue | ForEach {$_.cookedvalue}
$wsp = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\working set" -and $_.path -like $filter} | select cookedvalue | ForEach {$_.cookedvalue}
$w3wpn = $pids | foreach {
$filter = "processid='" + $_ + "'"
$wmip = gwmi Win32_Process -filter $filter
if($wmip.CommandLine -match "-ap `"(.+)`"") {
$appName = $matches[1] -replace '([^^"].)".*', '$1'
Write-Output $appName
}}

'WP,IP,Memory',$w3wpn,$pids,$wsp | ConvertFrom-CSV | Format-Table -Auto

and here is the output I'm looking for:

WP                  Process ID Mem (KB)
--                  ---------- --------
MyAppPool                 4816 76,435,456
AnotherAppPool           16924 205,369,344
DefaultAppPool           34508 233,574,400


texts:
Are there any hints, stats or alike available to check thoroughly which indexes can be safely deleted from a PostgreSQL 9.4.9 database? Our database keeps growing and growing and, we think there might be some lost in indexes not being used. In the past, we tried to trim down the database overall size (about 2TB now, mostly historic data), but customers kept needing access to old data, so we had to stop expiring old "supposedly unused" data because bringing it back alive, was a huge pain.

Any additional comments on saving space would be greatly appreciated.


texts: Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them

texts:
I have a table with below structure:
create table TBL_TEST
(
  col_id   NUMBER,
  col_name VARCHAR2(500)
)

Some example data :
col_id | col_name
-----------------
  1    | aetnap
  2    | elppa
  3    | ananab

What I need to do is to split characters of column col_name for each col_id for example for col_id=1 we must have :
col_id | col_name
-----------------
  1    | a
  1    | e
  1    | t
  1    | n
  1    | a
  1    | p

this query is fine when there is only one record in the table :
SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL
  FROM tbl_test t
CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null

but as soon as I insert another record in the table (say col_id=2 and col_id=3) I can not have the desired result. I want to know two things:

Why is this query works fine for one record and it does not for more ?
what is the best way to split the characters when there is no delimiter(like , or . or *) between them?

Thanks in advance


texts: Unknown column "X" in field list on delete, but the column exists

texts:
I'm using MySQL and i'm trying to run a delete statement as follows:

DELETE FROM `myTable`
 WHERE `nincidusua` = 84

and the following error occurs:

  Unknown column 'nincidusua' in 'field list'

My first thought was that the column was inexistant, but it trully exists, when i run this simple SELECT:

SELECT nincidusua FROM myTable

it works completely normal:

DESC myTable

shows the column on the list and even selecting the columns from information_schema shows up the column name and its structure.

I noticed that the same error happens on UPDATE queries, because I had the idea to create a dummy column, save the data from the faulty column and then recreating it, but when I try running:

UPDATE myTable SET DUMMY = nincidusua

the error persists.

I dumped the whole schema and restored it on my local machine, and everything works as expected, but dropping from the production database and restoring again, did not fix the issue, the same scenario happens. That's why i can't provide a verifiable example, because I couldn't even reproduce it on another database.

What I noticed is that I can't alter the column structure, if I try running a MODIFY column on this exact column, changing anything at all, the query executes but the column stays the exact same. This don't happen with other columns on this same table.

Due to this extremely odd behaviour, I don't know anymore where to look or what is the underlying cause, I would suspect it's some kind of corruption on information_schema but I honestly don't have experience enough to be sure, neither to fix it.

If there's any lack of information, tell me in the comments and I will provide it as soon as possible.

Thanks!

Edit:
Result from desc myTable nincidusua:

+------------+---------+------+-----+---------+-------+
|   Field    |  Type   | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| nincidusua | int(11) | NO   | PRI |       0 |       |
+------------+---------+------+-----+---------+-------+


texts:
I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2.

The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you can see the partition column is not the primary key.

I have three partitions. Left boundary <=boundary 2, 5 and the last one is empty. All of the partitions are on primary file group. There are no other filegroups.


texts: How to update pg_opclass as non-postgres superuser

texts:
I'm using poorly documented third-party software that does esoteric things with PostgreSQL 9.3.  At some point during its setup, it attempts the following:

UPDATE pg_opclass SET opckeytype=xxxx WHERE ...;

The statement is running as a database superuser (though not postgres), and fails with this error:

ERROR:  permission denied for relation pg_opclass

What permissions do I need to grant to allow this statement to work?

Note #1:  I tried granting all permissions on that table to no avail.
 \z from psql reports the following (zzz is my superuser's name):

mydb=# \z pg_catalog.pg_opclass
                                 Access privileges
   Schema   |    Name    | Type  |  Access privileges   | Column access privileges
------------+------------+-------+----------------------+--------------------------
 pg_catalog | pg_opclass | table | =r/postgres         +|
            |            |       | zzz=arwdDxt/postgres |
(1 row)

mydb=#

Note #2: The exact same statement, when run as the postgres user, works just fine.


texts: Apply SP3 on SQL Server 2012 AlwaysOn AAGs

texts:
If Always-On is configured, while applying Service Pack, should I:

Directly move SQL Server Instance (Secondary Replica) to make it passive and apply Service Pack
Or First move Availability Group and then move SQL Server Instance (Secondary Replica) and finally apply patch and restart the Server.

Can you please let me know?


texts: Points that support creating a separate database for source(which is usually coming in through an API Request)

texts:
I am doing an academic project with limited knowledge of data warehouse. I am using SQL Server to build that.

I have three different sources -
Sources - JSON Request, Web Crawling, Excel Data Source.

I Presume we need three destinations - Source Database, Staging Database, Data Warehouse.

The loading of the source database happens through SSIS - Execute SQL Task. I am running a python script in there, to pull the data and store it in the database tables.

I am a bit confused here - Should I change my script in such a way that the script does all the transformations and load them into the Staging Tables ? If I can do that, then there is no need for a Source database itself here.

Please share the best practices here - I understand the question is vague, but

  what are the points that could support my argument in having a
  separate database for source?


texts:
I'm trying to copy a user from my live server to my DR server using the following command:

CREATE LOGIN [xxx]
WITH PASSWORD = 0x010006278DD83306AD19FA8B0CE4405F13DA83DDDFB25B088D7C HASHED
    , SID = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D
    , DEFAULT_DATABASE = [master]
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF

I then get an error:

  Msg 15433, Level 16, State 1, Line 1
  Supplied parameter sid is in use.

So I check the sys.syslogins with:

SELECT *
FROM sys.syslogins
WHERE sid =  '0xFEEBD63ECD0F6A41AE60A6E0414F5A1D'

This finds nothing. So my question is what else could be using this sid?


texts: How to keep database size low in MongoDB?

texts:
Newbie here.

TL;DR : What is the best way to compact data in MongoDB? Will it use much more space than other database systems?

I wanted to create a database for something more or less like a forum. I started learning MySQL, but I don't need a RDBMS for what I want (most of my relations are 1 to 1 (at most, 1 to Many), even if I have a little data redundancy I'd rather store the information as field-and-value pairs of a document, I think it's faster for JSON than the many table joins in MySQL I would otherwise need). I also prefer JavaScript to SQL.

But looking now at the storage requirements, I find nothing like a TINYINT, CHAR(3), etc. I wanted to keep costs down with server's space, but at the same time record a lot of information (time, ratings, votes, comments, tags, karma, favorites, etc.). I hope I'm wrong, but MySQL seems to waste reasonably less space (although I imagine this is compensated in perfomance by MongoDB).

What are the best practices for reducing size in MongoDB? And am I completely wrong in thinking MongoDB uses more space than MySQL and other DBs (disregarding the benefits of joining tables in MySQL to avoid data redundancy)?

So far, these have been the best articles I found on it:
How to limit MongoDB database size (force size limits on the database)
https://www.compose.com/articles/sizing-and-trimming-your-mongodb/ (cap database and release prefetched space)
https://docs.mongodb.com/manual/reference/command/compact/ (use compact, specially on WiredTiger DBs)
https://stackoverflow.com/questions/2966687/reducing-mongodb-database-file-size (repairing data, and same as above)
As @Fyodor Glebov reminds in his answer, there are also 2 compression algorithms: snappy and zlib.

Thanks for any advices!


texts: SQL Server 2014 uses high memory at startup

texts:
The process of SQL Server 2014 is using high memory even it's idle and no query is running on. after restarting the service, it happens again and memory usage grows to 2.2GB after a while.

the host is Windows Server 2012.

Why? and how can I find the problem?


texts: sp_BlitzFirst: blocking_session_id not displayed in list of session_ids

texts:
I have version sp_BlitzFirst 2016-12-10.

I executed the following:

sp_blitzfirst @ExpertMode = 1, @CheckProcedureCache = 1, @OutputType = 'Opserver1'

Below output shows SPID 124 is blocked by SPID 143, however SPID 143 did not show up in any of the output.  Is there another way to have sp_BlitzFirst show the info of the blocking_sesesion_id as well, so I can see what query is being executed?


texts: How to access a SQL Server database using another user with permissions in the same intranet

texts:
While I'm waiting for my user to get access permissions, I'm stuck using a colleague's, and whenever I try to run basic SQL queries I have to switch from my Windows user to hers.

Hopefully this is clear enough: I want to access a SQL Server for which another user on the same intranet has permissions while I only have her credentials, and that the server utilizes Integrated Security.


texts:
I've just setup multiple availability groups within the same group of servers, simply for separation more than anything else, the databases in the new one are for Skype for Business so it comes with a handful of databases.

Anyway, it all works fine, I set everything up, I set up a new listener for it and then something happened I didn't expect. Using both the old and new listeners gives me all the databases on that instance, from both groups. I wasn't expecting this, I thought in my head (and I will hold my hand up and admit I'd totally made this up myself) that by assigning a listener to each of the groups, I would only see the databases in that group when using that listener.

Anyway, my question is... what would be the usual best practice around this? If each listener will give you all the databases anyway, should I create one for each group? or just one for the first group? or have I fundamentally missed something and there should be a way of me setting up each listener to only see the databases in the group associated with it?

I'm happy to be getting the "There's no point creating multiple blah blahs for doing that" messages if that's the case, no worries, but please only if you actually answer my main question.


texts: Can a single PostgreSQL query use multiple cores?

texts: In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?


texts: postgresql logical replication sync frequency / interval

texts:
I've been trying to find something in the docs about frequency / interval of sync between subscriber and publisher under the logical replication model of postgresql
Can this be specified or does it happen continuously? I would like to be able to have the subscriber sync say once an hour


texts:
I search an update-command, which update me only some records (more than one row) in a table. The update Information comes from an other table.
Unfortunately it updates all 3500 records instead of only 603 records.
Here is the update/select. What is wrong:

update Z_VERTRAGSDATEN_HIST HIST
  set
    (HIST.VERHI_BISDAT) =
    (select
  UPDA.VERHI_BISDAT
     from
        Z_VERTRAGSDATEN_HIST HIST
        , Z_VERTRAGSDATEN_UPDATE UPDA
    where
        HIST.VERHI_VER_ID = UPDA.VERHI_VER_ID
    and
        HIST.VERHI_BISDAT != UPDA.VERHI_BISDAT
    )
;
commit;


texts:
I want to grant a user permission to select,insert,update,delete on a few tables in the prod database. However, I find it time consuming to write each grant statement out per table. Is it possible to use a wildcard?

There are 300 tables, the user only needs access to 18 of them. The tables the user needs access to are prefixed 'vs_'.

Can I do a grant select,insert,update,delete on prod.vs_\* ?  I know prod.\* is possible but wasn't sure on the prefix of a table.


texts:
I am relatively new when working with queries so let me apologize in advance.  This is SQL Server 2012 that I will be working with.  This data has been brought into a staging table through a nightly import from Excel.  I need to move this data from this table into our main hardware table and make sure that I insert any new data while checking to make sure it updates any of the older data.  I will give as much information as I can, please bear with me.

The field names are:

|Ticket_Num | Name | Worker | date1 | Serial | Part | Description1 | Type1 | Eqmt_Type |

I need to bring them into a table that has this:

   |Event_NUM|Name|Install_Date|Pull_Date|Install_Tech|Pull_Tech| PMP1_Desc|PMP1_Part|PMP1_Serial|PMP2_Desc|PMP2_Part|PMP2_Serial|PMP3_Desc|PMP3_Part|PMP3_Serial|Motor1_Desc|Motor1_Serial|Motor1_Part|Motor2_Desc| Motor2_Serial| Cab1_Disc|Cab1_Part|Cab1_Serial|Cab2_Desc|Cab2_Part|Cab2_Serial|Dis1_Desc|Dis1_Part|Dis1_Serial|Dis2_Desc|Dis2_Part|Disc2_Serial|

Here is SQL Fiddle with an example of the data being pulled in from Excel:
SQL Fiddle for data to be inserted

Here is SQL Fiddle with an example of what I would like: SQL Fiddle example of what I would like


texts: Cross database - Table valued Stored Procedure

texts:
I have the same table type in two databases (DB1 and DB2),
DB1 has an stored proc (sp1) which has the above type as an input parameter.

Now, I want invoke this SP from DB2.

But, I am getting this error:

  Msg 206, Level 16, State 2, Procedure sp1, Line 0
  Operand type clash: t1 is incompatible with t1

How do I do this? Is there any work around for this?

Here is the script for the same.

--setup starts

Create database DB1
go

create database db2
go

use DB1
GO

create type t1 as table(id int)
go

use DB2
GO

create type t1 as table(id int)
go

use DB1
go

create proc sp1 (@p1 t1 readonly)
as
select * from @p1
GO

--script start

use DB2
GO

declare @p t1

insert into @p values(1)
insert into @p values(2)
insert into @p values(3)
exec DB1.dbo.sp1 @p
GO

--clean up

use master
go
drop database DB1
go

drop database db2
go


texts: How to merge overlapping time intervals if the interval not more than 4 minutes in PostgreSql


texts:
Let's say, I have the following tables in MS-SQL Database:

Please note: This database composition is just fictional, because I'am not allowed to share sensitive business data.

Category

Id (int, pk)
Name (varchar(50))

Game

Id (int, pk)
Name (varchar(50))
CategoryId (int, fk: Category->Id)
etc...

Participant

Id (int, pk)
Name (varchar(100))
etc...

Composition

Id (int, pk)
ParticipantId (int, fk: Participant->Id)
GameId (int, fk: Game->Id)
Date (date)
Time (time)

The problem is that, only one Game per Category per Participant is allowed for a given Date and Time.

So for example:

Tim can participate only in one game from one category at a time.

Valid composition:

Tim -> Football (Ball games) -> 2017/02/02 -> 12:00
Tim -> DOOM (Video games) -> 2017/02/02 -> 12:00
Tim -> Football (Ball games) -> 2017/02/02 -> 13:00
Tim -> DOOM (Video games) -> 2017/02/02 -> 13:00

Invalid composition:

Tim -> Football (Ball games) -> 2017/02/02 -> 12:00
Tim -> Baseball (Ball games) -> 2017/02/02 -> 12:00

My first idea was to set a unique index to Composition table ParticipantId + GameId + Date + Time, but it still allows me to add two or more games from the same Category.

Finally the question is, is it a design problem, or I just have to add a check constraint to maintain data consistency?


texts: How do you capture the output message from a successful MERGE statement when using it in a procedure?

texts:
Sorry if this sounds like a rather simple question..

When using the console, normally the statement outputs something similar to

X records merged

How do I capture this output into an out variable when using a merge statement in a procedure?


texts: How to script a table with dynamic data masking via SSMS

texts:
For a table which has dynamic data masking enabled if I try generating (create table) scripts from SSMS (13.0.1711.0) it is not scripting the MASKED function details. Is there any other way to do it?

Sample table

CREATE TABLE TestTable
(
    Sno INT IDENTITY(1, 1) PRIMARY KEY
    ,FirstName VARCHAR(50) NOT NULL
)
GO

ALTER TABLE TestTable ALTER COLUMN FirstName varchar(50)
MASKED WITH (FUNCTION = 'default()');
    GO

Currently this is the output I get via SSMS - Generate Scripts

CREATE TABLE [dbo].[TestTable](
    [Sno] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [Sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Update:

Applied CU2 for Microsoft SQL Server 2016 RTM (KB3182270) and now SQL Server 2016 build number is 13.0.2164.0
SSMS Versions is shown as 13.0.15900.1 (Even have tried downloading the SSMS once again as suggested by Aaron Bertrand and reinstalling it)

Still the original problem is not resolved - Scripting the table gives the same result as original posted.


texts: Does mysql configuration options accepts - instead of _?

texts:
Mysql 5.7 Documentation mentions that innodb_buffer_pool_instances can be set in /etc/mysql/mysqld.conf.d/mysqld.cnf as follows:

[mysqld]
innodb_buffer_pool_instances    = 16

However, an existing setup uses mysql configuration as follows (passed in /etc/mysql/mysqld.conf.d/mysqld.cnf):

[mysqld]
innodb-buffer-pool-instances    = 16

And it seems to be working.

Does mysql configuration options accept - instead of _ in variables or am I missing something here?


texts: Is there a type of database that can directly recall an element given a pointer in O(1) time?

texts:
I'm currently using levelDb, which is a key->value store database. The only data being inserted are small binary blobs (that represent nodes for a large merkle tree). When I put a value in the db, I don't really care what the key is, just that I have some key to use as a reference (from its parent nodes which are written later).

As you can see the key->value store does the job but since I don't need control over key creation, I believe it is unnecessarily slow. In order for a key->value database to lookup a key, it probably uses a b-tree. That means that finding a single value is doing O(log n) disk reads.

For my use case it should be possible to get closer to O(1) reads/writes where I put new data elements to the database and it returns something like a direct address. I can use this address as my reference.

(This can already be done in the filesystem by calculating offsets, but I need a "real" transactional ACID database)

Does such a thing exist? I have tried searching but don't know what to call it other than "pointer based database".

I do not mean to imply that I don't require a delete feature. However, this should also be possible using the same address pointer reference. I can also adhere to the additional constraint that each data item be of the same size (I do not know the fundamentals of how db deallocation is handled, but it seems like having fixed length items might help so that new items can rewrite over deleted items in a compact way).


texts: Why should I use InnoDB and MySql instead of XtraDB and MariaDB?

texts:
Why should I use InnoDB and MySql instead of XtraDB and MariaDB, except that InnoDB and Mysql happen to be installed by default on my servers?

Why isn't MariaDB and XtraDB installed by default everywhere?


texts: What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

texts:
So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate the best way to implement this. For me, I just want an easy way because SQL Server is suffering and my query tuning hasn't fully transcended across the app (and obviously adding the SET in a sp DOES NOT WORK).

In Erland Sommarskog's brilliant article about the topic, he basically suggests taking the safe approach by altering the app to issue SET ARITHABORT ON for the connection. However, in this answer from a dba.stackexchange question, Solomon Rutzky offers both an instance-wide and database-wide approach.

What ramifications am I missing here with setting this instance-wide? As I see it ... since SSMS has this set ON by default, I see no harm in setting this ON server-wide for all connections. At the end of the day, I just need this SQL Server to perform above all else.


texts: How to terminate BCP output with specific string AND newline?

texts:
I'm trying to terminate each row in BCP output with a specific string along with newline. Neither of these work:

-r"terminator_string\n"';
-r"terminator_string"+\n';

Any suggestions?

For an idea of the overall problem, see https://stackoverflow.com/questions/23329028/outofmemory-exception-when-reading-and-replacing-strings-with-streamreader-and-s


texts: Conext menu behaviour change in SSMS 18.0/18.1

texts:
I'm using the new SSMS 18.0 or 18.1 and one thing extremely annoys me -

Upon pressing right click, the context menu is open.

I used to press the "up arrow"+"Return key" to pick the Refresh option.

The circularity isn't working anymore.

Is there anyway to change it?

Thanks!


texts: How can I suppress formatting for a single SQL statement in interactive mode?

texts: I want to have the same effect as -ss, but inside my interactive mode, after I'm already there. I don't want to leave mysql, re-invoke with -ss, run my query, leave mysql and re-invoke it without -ss. Is there some directive within the interactive mode that will let me change that? I couldn't find anything in the help pages, I looked up in many different places and can't find any clue.


texts: Improving Backup Performance using SQL native methodology

texts:
I am trying to gather some stats on comparison of run time vs performance between Litespeed backups and sql native backups. As we are no longer going to use Lite Speed [even though i personally loved that SW], have to gather metrics how SQL will perform using native compression backup]

So i am looking for a way to reduce CPU usage to X % while taking native compression backups. In Litespeed we just specified this option in advanced tabs and saw everything running fine. I am aware of resource governor, but due to some constraints not able to use it. Will need to work on that. But is there a better and smarter way to do this. Please suggest

Edit.. Have to add, as per security and company policies we cannot use third party or external SP's to be created. I saw this can be achieved using OLa 's script as well, but we wont get approvals to introduce this :(


texts: SQL Server 2005 encrypted database works on new PC without restore keys!

texts:
Something tells me that it was not normal.

Let me take it from the begining.
In an SQL Server Express 2005 I needed to encrypt one column. I did the following.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='blahblah';

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah'
WITH SUBJECT = 'Password',
EXPIRY_DATE = '12/31/2099';

CREATE SYMMETRIC KEY my_symmetric_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE my_certificate;

I created procedures to encrypt, decrypt the column and everything worked OK.

I then took the database (SQL Server 2005, Mixed Mode) and restored it to a new PC with an SQL Server 2008 Express.

WITHOUT restoring KEY and Certificate, I launched my application which use the stored procedures to encrypt/decrypt and SURPRISINGLY everything worked in the new database!! I could decrypt previously encrypted data successfully.

Is this normal??

What is the use of backing-up the Key and the Certificates then?

Thanx in advance


texts: RESET MASTER; does not reset GTID_EXECUTED

texts:
I'm trying to get replication up and running between two kubernetes pods, and am getting the dreaded @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. error.

root@central-vm:~# mysqldump -u root -h 10.151.0.36
    --single-transaction --triggers --routines --events --all-databases > alldatabases.sql

root@central-vm:~# mysql -u root -h 10.151.4.69 < alldatabases.sql
ERROR 1840 (HY000) at line 33: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

So, I went in to the soon-to-be slave and tried to manually blank it out without any success.

Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961

mysql> show global variables like 'GTID_EXECUTED';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| gtid_executed | bcf87378-e0d7-11e9-a68e-4e7345ee642a:1-2616 |
+---------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> RESET MASTER ;
Query OK, 0 rows affected (0.15 sec)

mysql> show global variables like 'GTID_EXECUTED';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_executed | bcf87378-e0d7-11e9-a68e-4e7345ee642a:1-3 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

What am I doing wrong?


texts: How to change table with innodb/myisam engine to federated

texts:
In my.ini I added

[mysqld]
federated

When I type in a shell show engines; I get this result:

But when I try to change one of this tables which are on innodb engine to federated I get an error. When I try to create/alter table with sqlyog there is no federated engine

Can anyone help me? And if I change innodb/myisam table with data to federated table, will I lose any data? I dont use transactions in table


texts: Why does Publisher need to be able to connect to Remote Distributor?

texts:
I have Transactional Replication set up with a Remote Distributor and a Pull Subscription. So as I understand it, at one end I have the Publisher, and at the other end I have the Distributor, Log Reader Agent and Subscriber. Therefore, all activity is initiated from the Subscriber end. The Log Reader Agent monitors the Publisher, and the Distribution Agent monitors the Distribution database at the Distributor.

Given this architecture, why during the configuration stage does the Publisher need to be able to connect to the Subscriber / Distributor? If the Subscriber / Distributor server can "see" the Publisher, why isn't this enough, as it seems all activity is initiated at the Subscriber / Distributor end?


texts: Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query

texts:
A query like this:

SELECT *
FROM person
WHERE person.favorite_number = ANY(
    ARRAY[ 2 , 3 ]
)
;

…runs. The ANY() function tells us if the single value is found within an array of values. So, we find any person with a favorite number of 2, or of 3, but omit people who chose their single favorite number to be 0, 1, 4, 5, or so on.

➥ How to replace that hard-coded array with an array dynamically retrieved?

Imagine a second table event with a column hits of type INTEGER[] (an array of integers). I want to pull the one array from a single row and feed as the argument to ANY( ) of the outer query.

Something like this:

SELECT *
FROM person
WHERE person.favorite_number = ANY(
    SELECT hits FROM event WHERE when = DATE '2018-01-23' ;  -- Returns a single array from a table with `hits` column of type `INTEGER[]`.
)
;

That SELECT hits FROM event WHERE when = DATE '2018-01-23' ; is what stumps me. How do I get a single array resulting from one query to be the argument to a function in the WHERE clause of another outer query?


texts: How to create Non-clustered Covering index on PK while creating a table

texts:
I am trying to create a table where PK will not be a clustered index. PK will be non clustered with INCLUDE clause to add cover.

CREATE TABLE [Ref].[User](
    [UserRegisteredId] [varchar](100) NOT NULL,
    [UserName] [varchar](500) NOT NULL,
    [FirstName] [varchar](500) NULL,
    [LastName] [varchar](500) NULL,
    [PhoneNumber] [varchar](20) NULL,
    [Email] [nvarchar](500) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_UserName] UNIQUE CLUSTERED ([UserName] ASC),
    CONSTRAINT [PK_User] PRIMARY KEY ([UserRegisteredId])
)

as you can see above UserName is unique clustered index. Now I created a non-clustered index on PK with INCLUDE as below:

CREATE UNIQUE NONCLUSTERED INDEX NCI_UserId ON [Ref].[User] ([UserRegisteredId]) INCLUDE ([FirstName],[LastName])

After creating table and covering NCI on PK i noticed that there is already non-clustered index is created on PK automatically at the time table creation (Highlighted in attached image).

How can i avoid creation of two NCI on same column(PK) ? i need NCI which is created as covering index (NCI_UserId)

Is there any way that i can create covering non clustered index on PK while creating a table ?


texts: Conversion failed when converting the varchar value to data type int in SQL Server

texts:
While getting some data input, for below query, encountered Convertion failed error:-

SELECT a.ProcessInstanceAppianID,
       a.ProcessInstanceDescription,
       CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))AS KPI,
       i.KeyPersonKPILead,
       p.BestPersonName                                     AS 'Lead Person'
FROM   InternalUseOnly..processinstance a
       LEFT JOIN InternalUseOnly..ProjectInput i
              ON i.KeyProjectInput = CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))
       LEFT JOIN snledit..Person p
              ON p.keyperson = i.KeyPersonKPILead
                 AND p.UpdOperation < 2
WHERE  a.KeyProcessStream = 1827
       AND a.ProcessInstanceCompleted IS NULL
       AND a.UpdOperation < 2

It would be very aiding & adding, if anyone can guide me here.

Thanks in anticipation!


texts: Database schema for entities with two possible owner/parent types?

texts:
I'm using PostgreSQL with Sequelize as my ORM.

I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also own any number of Groups.

My third type, Playlist, can belong to either a User OR a group. What would be the best way to design a schema for this type so that it can have either one type of owner or the either?

My first pass I created both associations, but only populated one at a time. This could possibly work, but seems hacky and makes queries difficult.

Additional information

Here are my responses to the clarification requests posted by MDCCL via comments:

  (1) If a Playlist is owned by a given Group, one can say that this Playlist is related to one-to-many Users, as long as they are Members of such Group, right?

I believe this is technically true, but this one-to-many association does not exist explicitly.

  (2) So, is it possible for a specific Playlist to be owned by one-to-many Groups at the same time?

No, it should not be possible for a Playlist to be owned by one-to-many Groups.

  (3) Is it possible for a particular Playlist to be owned by one-to-many Groups and, at the same time, by one-to-many Users who are not Members of such Group?

No, because as in (2) a one-to-many from Playlist to Group should not exist. Additionally, if a Playlist is owned by a Group it is not owned by a User, and vice versa. Only one owner at a time.

  (4) What are the properties used to uniquely identify a Group, a User and a Playlist?

They each have a surrogate primary key (id), as well as a natural key (though not primary). These are slug for Group and Playlist, and username for User.

  (5) Could a particular Playlist suffer an Owner change?

Although I don't plan on this being a feature (at least initially), I suppose this could hypothetically occur.

  (6) What’s the meaning of the Group.Slug and Playlist.Slug attributes? Are their values stable enough to be defined as primary keys or do they change very often? The values of these two properties, along with User.Username must be unique, correct?

These slugs are unique, lowercase, hyphenated versions of their respective entity's title. For example, a group with the title 'Test Group' would have the slug 'test-group'. Duplicates are appended with incremental integers. This would change any time their title changes. I believe that means they wouldn't make great primary keys? Yes, slugs and usernames are unique in their respective tables.


texts: Persistent inserts in a UDF even if the function aborts

texts:
I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an operation takes place and the query itself. The UDF looks like the following then:

log function_start

log sql1
execute sql1

log sql2
execute sql2

...

log sqlN
execute sqlN

log function_end

Every log statement means inserting a new record into the following table:

CREATE TABLE backup_logs
(
  id serial NOT NULL,
  t timestamp with time zone default now(),
  sql text,
  CONSTRAINT backup_logs_pkey PRIMARY KEY (id)
)

In case of abort I'd like sql1, sql2, ... sqlN to be rollbacked, but the inserto into backup_logs to persist. Question: how can I achieve that?


texts: Compound key that only allows nulls if they are null in the referenced table

texts:
I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the referencing-field combinations should not be required to be unique. I'm working in PostgreSQL 9.4, in case that gives me a solution that wouldn't otherwise exist.

For example, let's say I have these tables:

CREATE TABLE a (
  a_id serial NOT NULL PRIMARY KEY
  ,name text NOT NULL
);

CREATE TABLE b (
  b_id serial NOT NULL PRIMARY KEY
  ,name text NOT NULL
  ,a_id integer REFERENCES a (a_id)
);

CREATE TABLE c (
  c_id serial NOT NULL PRIMARY KEY
  ,name text NOT NULL
  ,b_id integer NOT NULL REFERENCES b (b_id)
  ,a_id integer
);

Now suppose that a has data

a_id | name
-----+----------
   1 | 'First A'

and b has data

b_id | name       | a_id
-----+------------+-----
   1 | 'First B'  |    1
   2 | 'Second B' | NULL

I would like to alter c (and other tables as needed) so that the following will all succeed,

INSERT INTO c (b_id, a_id, name) VALUES (1, 1, 'First C');
INSERT INTO c (b_id, a_id, name) VALUES (2, NULL, 'Second C');
INSERT INTO c (b_id, a_id, name) VALUES (1, 1, 'Another 1-1 C');

but both of these will fail,

INSERT INTO c (b_id, a_id, name) VALUES (1, NULL, 'Bad C one');  --b=1 must have a=1
INSERT INTO c (b_id, a_id, name) VALUES (2, 1, 'Bad C two');  --b=2 must have a=NULL

If b and c did not allow a_id to be null, then I could just

ALTER TABLE b
  ADD UNIQUE (b_id, a_id);
ALTER TABLE c
  ADD FOREIGN KEY (b_id, a_id)
  REFERENCES b (b_id, a_id);

With the null a_id values though, this allows the first bad insert to succeed, because the null value in the a_id column isn't checked against the value of any rows in b.

I also tried using MATCH FULL, but this prevents the insertion of null values in the a_id column entirely, even though it is nullable, since b_id cannot also be null.

What is the best way to accomplish what I'm trying to do?


texts: Optimizing a query with ORDER BY in a derived table

texts:
The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it?

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   (SELECT consoleId,
               servertime,
               nocName,
               eventIdx,
               nocStatus,
               nid,
               site,
               machine,
               clientversion,
               timeExecuted
        FROM   temptest
        ORDER  BY tid DESC) AS x
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;

The table definition is

CREATE TABLE `temptest`
  (
     `tid`                INT(10) NOT NULL AUTO_INCREMENT,
     `eventIdx`           INT(11) NOT NULL DEFAULT '0',
     `servertime`         INT(11) DEFAULT NULL,
     `nocName`            VARCHAR(50) DEFAULT NULL,
     `site`               VARCHAR(50) DEFAULT NULL,
     `machine`            VARCHAR(64) DEFAULT NULL,
     `clientversion`      VARCHAR(20) DEFAULT NULL,
     `nid`                INT(10) DEFAULT NULL,
     `nocStatus`          VARCHAR(250) DEFAULT NULL,
     `consoleId`          INT(11) DEFAULT NULL,
     `timeExecuted`       VARCHAR(100) DEFAULT NULL,
     `machineManufacture` VARCHAR(100) DEFAULT NULL,
     PRIMARY KEY (`tid`),
     KEY `consoleindex` (`consoleId`),
     KEY `nitification` (`servertime`, `nid`)
  )
ENGINE=InnoDB
AUTO_INCREMENT=8229683
DEFAULT CHARSET=latin1


texts: strange behavior with Turkish language setting

texts:
I have database with Collation Arabic_CI_AS

when I have windows with English settings I can do

select Column from Table where Column= 'A'

and can do

select column from table where column= 'A'

I mean the naming is not case sensitive

In Turkish windows

if any of the names not as named in the DB it raise error in my windows application

I mean that in Turkish windows the names must be case sensitive

any  idea to explain that and how to solve it such that I don't face it again in any language settings


texts: MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

texts:
I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem with concurrent connections, with one access to my service, the MySQL are opening thousands connections and growing, the connection number only decreases when I restart the MySQL service, I think that isn't my server hardware because it has 32 cpu cores and 64gb RAM, I don't understand what is happening but I need to solve this quickly.
All databases and tables are using InnoDB engine, and my application are closing all connections that it opens.

Anyone had the same problem when migrate databases like this?


texts: Method to keep track of large number of sets with high cardinality

texts:
We're building an analytics service. One of our needs is to maintain ~100MM sets each with anywhere between one member to 1MM members, with each member represented as a long. We need to be able to very quickly:

-Add a member to a set, and know whether it was added

-Update a counter indicating the size of the set

We have a realtime processing loop that ingests new data and needs to be able to update some number of the sets as well as its counter.

At the moment, we have this implemented in PostgreSQL, distributed application side by set name. There's one table with two columns: the set name in the first and the value in the second, as well as an index on set name, value. Is there a better method than this? AFAIK it's not a problem with a standard solution.


texts:
This query works, but it is extremely slow, and takes a lot of system resources to complete.  Is there a different way to write this table so that speed is #1 priority?

Below is the query and sample DDL

CREATE TABLE [dbo].[Costing](
    [Store] [varchar](45) NULL,
    [TotalCost] [float] NULL,
    [SaleID] [varchar](12) NULL,
    [SaleDate] [date] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[HOLD1](
    [Store] [varchar](45) NULL,
    [SaleID] [varchar](12) NULL,
    [TimesBilled] [int] NULL
) ON [PRIMARY]

Create Table MasterData
(
    [Store] [varchar](45) NOT NULL,
    TC1 float NOT NULL DEFAULT(0),
    TC2 float NOT NULL DEFAULT(0)
)
SET ANSI_PADDING OFF

INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 1', 5800, N'1234', CAST(0xA13C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 1', 50000, N'1123', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 2', 2925, N'3311', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 3', 4000, N'4411', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 3', 100000, N'2211', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 4', 1200, N'1211', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 4', 500, N'5511', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 5', 3500, N'1314', CAST(0x9F3C0B00 AS Date))
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 3', N'4411', 4)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 4', N'1211', 2)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 5', N'1314', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 2', N'3311', 2)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 4', N'5511', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 1', N'1234', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 3', N'2211', 5)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 1', N'1133', 3)
Insert Into MasterData (Store) VALUES
('Store 1'), ('Store 2'), ('Store 3'), ('Store 4'), ('Store 5')

This is the script:

SELECT [Store]
    ,[SaleID]
    ,TPC = SUM(COALESCE([TotalCost],0))
INTO HelperTable
FROM Costing
GROUP BY Store, SaleID

Alter Table HelperTable
ADD timesbilled int NOT NULL DEFAULT(0)

UPDATE x
SET TimesBilled = y.TimesBilled
FROM HelperTable x
INNER JOIN [Hold1] y
ON x.SaleID = y.SaleID

Alter Table HelperTable
ADD TC1 As case
                when TimesBilled > 1 then TPC/TimesBilled
                else TPC
            end

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN HelperTable y
ON x.Store = y.Store

Select * FROM MasterData


texts: Unicode character replication MSSQL to MYSQL in Symmetric DS

texts:
I'm doing replication from MSSQL to MYSQL using symmetricds and it is working fine.

Now , the problem is:

In the SQL Server table , I have a column defined as NVARCHAR(20) which accepts unicode characters like Hindi language characters.

In MySQL,  I have a column defined as VARCHAR(20) CHARACTER SET utf8mb4.

Whenever I insert any Hindi character in SQL server, it is not getting replicated in the MySQL table. It is just coming as '???' in MySQL.

Any idea's on how to solve this?


texts: The message "Running the Merge Agent process would exceed the maximum number of processes allowed" appears, but it doesn't seem likely

texts:
The full text of the message is "Running the Merge Agent process at this time would exceed the maximum number of processes allowed by this publication. The process will run when resources become available." It couldn't be put on the title.

We have a merge publication on a database. While a subscriber is synchronising, the above message often appears and synchronisation stalls for several minutes and might even time out.

The publication's "maximum concurrent processes" setting is 20, and using SSMS's activity monitor I see only 4 processes that seem to be related to this publication. In any case, there is only one subscriber for the publication and so it seems unlikely it could be using 20 processes.

Am I correct in my assumption above (that one subscriber shouldn't use 20 processes) and, if I am, what could be causing this problem?

Searching Google for the message's text brings only two results.

This is a merge publication where both publisher and subscriber use SQL Server 2008 R2.


texts: Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default

texts:
We have the ApplicationIntent=ReadOnly properly routing our users to the Secondary ReadOnly replica.  Is there a way to make this the default behavior?

Apparently the default behavior seems to be ReadWrite, and if you don't specify an ApplicationIntent it will redirect to the Primary server.


texts:
I wish to create DB in Firestore. My program will have multiple user and each user will have calendar and can create an event. The user can share this event with any other user (Similar to Google calendar) but each user can add comment only visible to self on that event. So my question is how to manage that. Options:

Create table of users and each user will have collection of events and in case the user shares and event it duplicates to other users in their own collection and each user can input personal comment. But it resolves in duplication in the DB.

Same as 1, but instead of duplicating, each user will have collection to show what shred with him and only edit the comment so self.

Preferred option. Have all events in one table, and each user will have connection table to know to which event he is connected. So my question is so: if I have lets say 10M users, and each user will have 1K events, I would have table of Events with size of 10M*1K rows. Is this acceptable to have such huge table and is this acceptable to store all events of every users in one table. And what is the time retrieval for 1 row in this case?

There is possibility that the DB will be moved from Firestore to SQL, so I need more generic approach.


texts:
Context

I am trying to enable BINARY LOGs on a MySQL RDS instance.

Issue

I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error.

And using CALL mysql.rds_show_configuration says "binlog retention hours" value is 24

But SHOW BINARY LOGS says "#1381 - You are not using binary logging"

Am I missing something ?

Additional information :

using SHOW GRANTS results in GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP...
I tried rebooting the instance. It didn't work.
automatic backups are enabled with a 1 day retention

useful documents

Using a Amazon-Managed MySQL-Compatible Database as a Source for AWS DMS
AmazonRDS/latest/UserGuide/mysql_rds_set_configuration


texts:
This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question.

When creating users in mysql using grant all privileges on mydb.* to 'mydb'@'%' identified by 'password'; , the user is getting super privilege. I used to create all my mysql users in this manner so, setting read-only=1 in my slave machine have no effect as the super user can write with read-only option set. So my question here is, using the above command for creating users is dangerous? If yes, what is the correct/safe method? Or I have to manually revoke the super privilege?


texts: Postgres slave max_connections = master's max_connections?

texts:
I am using binary replication for Postgres.

Is it true that the max_connections has to be the SAME for both the master and slave in postgresql.conf?

Can they be different?


texts: Difference between a unique clustered index and a clustered primary key?

texts:
I'd like to ask two supplementary/follow-on questions, further to this previous/existing question: Is there any tangible difference between a unique clustered index and a clustered primary key?

That question starts with, "I understand that there may be a difference in meaning or intent between the two". I'm a programmer not a DBA, and this fundamental might be unclear to me: what is the difference in meaning or intent between the two?
My summary of the accepted answer, i.e. its most important statement IMO, is that it says, "I don't think there's any difference". If that's so then why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

It seems to me that a primary key (already) is a unique clustered index.

Furthermore, here's a specific problem by way of example.

Let's say I have a table of Users (with a userId as its primary key), and a second table (e.g. Items) which defines items owned by each user. A user can own many items; each item is owned by one user, and has an itemId.

So the itemId could be the primary key of the Items table; and each row in the Items table (which has an itemId) also has a userId to identify its owner.

That's a good way to define a 1-N relationship, isn't it? Assume a foreign key contraint on userId, with Users being the parent table.

At run-time I usually want to retrieve all the items owned by a user, therefore the Items table should be clustered on its userId column.

[Users]
  userId
  + plus other user-specific fields

[Items]
  userId
  itemId
  + plus other item-specific fields

I think there are two ways to define this Items table:

itemId is primary non-clustered key, and (userId,itemId) is unique clustered index

... or:

(userId,itemId) is primary clustered key, and itemId is unique non-clustered index.

Which of the above two is better or more correct, semantically and/or practically, and why?

In case it makes a difference, the itemID is an artificial key: its purpose is to disambiguate/identify the item (and/or identify the item, within the set of items owned by the user).

'Physically' the itemID is probably globally unique (or unique within the table, anyway): because databases make it easy to create a globally-unique artificial key.
'Logically' I wouldn't mind if it were not-globally-unique, but were instead only unique-within-each-user, such that I needed both userId and itemId to uniquely identify an item, i.e.

(userId,itemId) is primary clustered key and itemId is unique non-clustered index.

So I think it isn't altogether wrong to see (userId,itemId) as a composite primary key?

Apparently it's fine and normal to use "two separate attributes" as the primary key of an associative table when it's an N-to-N relationship. Is it wrong (e.g. harmful in some way, for some reason) to use two attributes as the primary key of an object in a 1-to-N relationship? Is it wrong to say that the owner ID is part of the object's identity?


texts: Does anybody know what this tool is called?

texts:
Productive work has crawled to a stop while we try and figure out what the red thing on the cover of Microsoft SQL Server 2012 Internals (2013, 0735658560) is. We think it's a surgical or electrical tool of some sort?

Thoughts?


texts: Accessing Vertica Database via a shareable file DSN on Excel

texts: I am trying to connect to a Vertica database (Vertica driver=7.02.03) on our server. I have created a .dsn file using the FILE DSN tab on ODBC Data Source Administration (64 bit).  However, when I want to connect to the database via Excel(16.0.12527 64-bit), my file DSN does not show up on the 'From ODBC' list in Excel. Any suggestions?


texts: Percona 5.6.28-76.1: optimize config for database (Invision Power Board)

texts:
At the beginning I would like to explain that I'm not a DB expert. My hosting provider is struggling with DB optimization for my website. Unfortunately seems that they are out of ideas.

I would like to ask for some help.

Main issue seems to be that MySQL server is massively creating temporary tables on disk, which is very slow.

DB size is 3,6 GB

Biggest table has 1,7 GB (~1,085,507 records), second one 1,4 GB (~1,185,706 records), all others are smaller (next one is 143,8 MB, then 92,7 MB, all other smaller than 50 MB).

I have no control over DB structure/tables/columns layout, because it is Invision Power Board (IPB) system, latest version.

All tables are in InnoDB, utf8mb4_unicode_ci.

Serwer is LiteSpeed, Percona Server (5.6.28-76.1-log - Percona Server (GPL), Release 76.1, Revision 5759e76), libmysql - 5.1.49

I've got 4 GB RAM available on webserver, currently arount 60 percent is in use.

Values marked as alert:

Aborted clients - 200
Aborted connects - 143
Created tmp disk tables - 114 997
Handler read rnd - 21 287 655
Handler read rnd next - 112 934 517 814
Innodb buffer pool pages dirty - 74
Innodb buffer pool reads - 6 025 850
Innodb row lock waits - 108
Opened tables - 745
Qcache free blocks - 2719
Qcache lowmem prunes - 969 904
Select full join - 41
Slow queries - 528
Sort merge passes - 4 757
Table locks waited - 74 365

I have all current settings from phpmyadmin, but there are a lot of them. I've uploaded all here: https://www79.zippyshare.com/v/3g75k0gP/file.html

Some important ones:

innodb buffer pool size - 1 GB (increased by hosting provider)
innodb sort buffer size - 1,048,576
key buffer size - 64 MB
max heap table size - 128 MB (increased by hosting provider)
net buffer length - 16,384
query cache limit - 1 MB
query cache size - 32 MB
read buffer size - 128 KB
read rnd buffer size - 256 KB
sort buffer size - 256 KB
thread cache size - 50
tmp table size - 128 MB (increased by hosting provider)

I'm able to run any diagnostic SQL query, just please ask what You need to check to suggest some changes in configuration, which I will be able to push further to my hosting provider...

Thanks for any help, any suggestion would be very appreciated.

Best Regards,
DJ


texts: Replacing all occurrences of domain name in emails when exporting database

texts:
I've got a mysql database that I'd like to dump and share. However, it currently contains email addresses with @mydomain and this is causing some spam when others use it and don't change all the emails to their own.
The DB is from a Drupal site so there is no single table that contains all the email addresses; they are spread around many many rows and tables. I'm hoping I can write a script that will simply replace all of these domains with @example.com.
I found a few scripts like this online but most seem to be too old or simply don't work.
Is there a way to effectively do this? My sql skills are not at the point where I'm able to come up with this on my own.


texts: MySQL, would like to get both a field and a count inside GROUP_CONCAT

texts:
I can get a list of statuses that a tracking ticket has had, as well as the id of those statuses, as follows:

SELECT
t.id, t.name, GROUP_CONCAT( CONCAT(s.id, ':', s.name) ORDER BY s.id) AS
all_statuses_history
FROM tracking t LEFT JOIN changelog c ON t.ID = c.tracking_id
LEFT JOIN statuses s ON c.changed_to = s.id

That will have duplicates (status can bounce around in the real world), so I change the GROUP_CONCAT with DISTINCT keyword:

GROUP_CONCAT(DISTINCT CONCAT(s.id, ':', s.name) ORDER BY s.id) AS .. (etc)

That's better, but what I'd REALLY like is the id, status name, and count of instances, looking something like this:

1:Requested(1), 2:Pending(3), 3:On Hold(2), 4:Completed(1)

You'd read that as "the ticket bounced around between pending and on hold a bit and was finally completed".

I can't put COUNT(*) or apparently GROUP BY inside of a GROUP_CONCAT function.  Is it possible to get all of this information inside of a GROUP_CONCAT?


texts:
What would be the best way to store several entries in a database, each containing a list of other entries? An example of this would be a table of receipts, where each receipt has some information that's always present, such as time and the total amount. In addition to this, each receipt would have a variable-length list of entries, each one consisting of a descriptive text and a price.

I would be using a mySQL database to store the information and PHP to access it.


texts:
I installed a new mysql cluster on windows (for evaluation). I created a table using command below. After I insert just over 1 million records into the table it gives me the "table datadump is full" exception. I have plenty of hard disk space and ram free, and 1 million records is well below the max number of rows for the table. Also the MySql RAm usage has not increased during inserts. I am assuming that MySql has an internal cache limit for the table that was hit. is there a config.ini change I can make to disable caching of rows in cluster table or a modification to the create table script to do the same?

create table datadump (name varchar(30) not null) engine=ndb;


texts: postgres query taking very long and not sure why

texts:
I have a table where I'm selecting the following info but it is taking VERY long (like a minute):

 select controller, action, original_url from page_views order by id desc;

I have an index set up on id here:

Indexes:
    "page_views_pkey" PRIMARY KEY, btree (id)
    "index_page_views_on_params" gin (params)

If I do an explain analyze, I get:

explain analyze select controller, action, original_url from page_views order by id desc;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using page_views_pkey on page_views  (cost=0.09..70578.83 rows=1449712 width=68) (actual time=0.025..2721.362 rows=1445610 loops=1)
 Planning time: 0.088 ms
 Execution time: 2794.296 ms
(3 rows)

The fact that this takes so long concerns me. Is this just a reflection of it streaming ~1.5 million rows over the network?

What would be the next step to see if this could be made faster?

thx

edit 1

explain (analyze, buffers, timing) select controller, action, original_url from page_views order by id desc;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using page_views_pkey on page_views  (cost=0.09..70578.83 rows=1449712 width=68) (actual time=0.077..818.684 rows=1445618 loops=1)
   Buffers: shared hit=1068173 read=60798
   I/O Timings: read=178.285
 Planning time: 0.085 ms
 Execution time: 905.648 ms
(5 rows)

and shared_buffer / table_size:

select current_setting('shared_buffers') AS shared_buffers, pg_size_pretty(pg_table_size('page_views')) AS table_size;
  as shared_buffers |  as table_size
--------------------+----------------
 244MB              | 444 MB
(1 row)

edit 2

per comment below, I ran reindex TABLE page_views - the index was same size and performance appeared to be increased like 5%:

explain (analyze, buffers, timing) select action, controller, id from page_views order by id desc;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using page_views_pkey on page_views  (cost=0.09..70576.70 rows=1445928 width=23) (actual time=0.024..818.932 rows=1445929 loops=1)
   Buffers: shared hit=1068451 read=60806 written=4
   I/O Timings: read=179.327 write=0.066
 Planning time: 0.082 ms
 Execution time: 881.825 ms
(5 rows)


texts: attach csv file on email sent from postgres

texts:
I have the following code to send emails and wanted to see if it's possible to attach a csv file?
CREATE OR REPLACE FUNCTION public.send_email_html(text, text, text, text)
 RETURNS integer
 LANGUAGE plperlu
AS $function$
    use Net::SMTP;

    my ($recipientString, $sendFrom, $subject, $message) = @_;
    my @recipients = split /;/, $recipientString;
    my $smtp=Net::SMTP->new("localhost");

    $smtp->mail($sendFrom);
    foreach(@recipients)
    {
        $smtp->recipient($_);
    }
    $smtp->data();
    foreach(@recipients)
    {
        $smtp->datasend("To: $_\n");
    }

    $smtp->datasend("Subject: $subject\n");
    $smtp->datasend("Content-Type: text/html;\n");
    $smtp->datasend("$message\n");
    $smtp->quit();

    return 1;
$function$
;


texts: User-defined function returns first character only although RETURN VARCHAR(10) is defined?

texts:
Everyone,
I cannot figure out what is missing in the below user-defined function:
CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
AS
BEGIN
    IF (@Salary < 30000) RETURN 'Low';
    ELSE IF (@Salary <= 50000) RETURN 'Average';
    ELSE RETURN 'High';
    RETURN '';
END;

SELECT dbo.ufn_GetSalaryLevel(100440)

The output is including only the first character of the 'Low'/'Average'/'High' return values:
-----------------------------------------------------------------------
H

(1 row affected)

Why is MS SQL Server Management Studio not taking the VARCHAR(10) into account?
Thanks in advance!
P.s. Please find my output below - I cannot figure out why it does not work as expected?

P.s.2: the function has a single parameter, I seem not to find another function with the same name... Below you can find the code related to the function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

@@Version results

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019
13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition
(64-bit) on Windows 10 Enterprise 10.0  (Build 18363: ) 150


texts: Moving a PostgreSQL database to a different AWS RDS instance

texts:
I have an AWS RDS instance with multiple PostgreSQL databases in there including an application database and a monitoring database. The problem is that the monitoring database is consuming lots of resources and causes performance problems in the whole instance, so I'd like to have the application database and the monitoring database in different AWS RDS instances.

I have been investigating the issue and I've found that people are interested in having all their databases running on the same instance, while what I'm looking for is the other way around, so I'm wondering if I'm missing something.

The solutions that I've found are basically as follows:

Use an AWS RDS snapshot

This should be easy to do, but snapshots are at the instance level and I don't want to copy all databases, but just a single one.
Use the AWS Database Migration Service

The database migration service looks like a good approach because it uses a replication instance to make sure that all data written to the database while the migration task is running is captured. Unfortunately, I don't see how to use it for a single database.
Create a new instances and use pg_dump/pg_restore manually.

This approach seems the easiest one, but I'd need to make sure that either the application or the monitoring service are stopped while this is running.

So far, it seems to me that the right solution is the third one for its simplicity. Do you see any advantage/disadvantage that I might have overlooked?


texts: Oracle Select Query Over Database Link Suddenly Hangs Endlessly

texts:
I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like lightning. If I remove problem table the query runs in less than a second.

Synthetically it looks like this: (The problem table is remote2)

SELECT fields
FROM local_table1, local_table2, ... (more tables)
     remote_table1@dblink remote1, remote_table2@dblink remote2
WHERE
      ...clauses to tie the local tables together with each other and remote_table1...
      remote2.primary_key = remote1.primary_key_of_remote2
      AND remote2.afield <> 'avoid this value'

It's acting like it stuck on some kind of DB lock, but neither myself nor the DBA can find it. Is there some kind of special queuing mechanism used by remote database links that is locking this up?


texts: Is there a way to setup an SSL connection to an individual database, or does it have to be done at the instance level?


texts: Query doesn't respond when adding two columns

texts:
When I add two columns to my select the query doesn't respond. The type of column is nvarchar(2000). It's a little unusual.

The SQL Server version is 2014.
There is only one primary index.
The whole records is only 1000 rows.

Here is execution plan before (XML showplan):

Execution plan after (XML showplan):

Here is the query:

select top(100)
  Batch_Tasks_Queue.id,
  btq.id,
  Batch_Tasks_Queue.[Parameters], -- this field
  btq.[Parameters]  -- and this field
from
        Batch_Tasks_Queue with(nolock)
    inner join  Batch_Tasks_Queue btq with(nolock)  on  Batch_Tasks_Queue.Start_Time < btq.Start_Time
                            and btq.Start_Time < Batch_Tasks_Queue.Finish_Time
                            and Batch_Tasks_Queue.id <> btq.id
                            and btq.Start_Time is not null
                            and btq.State in (3, 4)
where
    Batch_Tasks_Queue.Start_Time is not null
    and Batch_Tasks_Queue.State in (3, 4)
    and Batch_Tasks_Queue.Operation_Type = btq.Operation_Type
    and Batch_Tasks_Queue.Operation_Type not in (23, 24, 25, 26, 27, 28, 30)

order by
    Batch_Tasks_Queue.Start_Time desc

The whole result count is 17 rows. The dirty data (nolock hint) is not important.

Here is the table structure:

CREATE TABLE [dbo].[Batch_Tasks_Queue](
    [Id] [int] NOT NULL,
    [OBJ_VERSION] [numeric](8, 0) NOT NULL,
    [Operation_Type] [numeric](2, 0) NULL,
    [Request_Time] [datetime] NOT NULL,
    [Description] [varchar](1000) NULL,
    [State] [numeric](1, 0) NOT NULL,
    [Start_Time] [datetime] NULL,
    [Finish_Time] [datetime] NULL,
    [Parameters] [nvarchar](2000) NULL,
    [Response] [nvarchar](max) NULL,
    [Billing_UserId] [int] NOT NULL,
    [Planned_Start_Time] [datetime] NULL,
    [Input_FileId] [uniqueidentifier] NULL,
    [Output_FileId] [uniqueidentifier] NULL,
    [PRIORITY] [numeric](2, 0) NULL,
    [EXECUTE_SEQ] [numeric](2, 0) NULL,
    [View_Access] [numeric](1, 0) NULL,
    [Seeing] [numeric](1, 0) NULL,
 CONSTRAINT [PKBachTskQ] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Batch_Tasks_QueueData]
) ON [Batch_Tasks_QueueData] TEXTIMAGE_ON [Batch_Tasks_QueueData]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Batch_Tasks_Queue]  WITH NOCHECK ADD  CONSTRAINT [FK0_BtchTskQ_BlngUsr] FOREIGN KEY([Billing_UserId])
REFERENCES [dbo].[BILLING_USER] ([ID])
GO
ALTER TABLE [dbo].[Batch_Tasks_Queue] CHECK CONSTRAINT [FK0_BtchTskQ_BlngUsr]
GO


texts:
This article describes the set-up of MySQL cluster with one cluster node, two data nodes and one MySQL server. This article describes the configuration of ProxySQL where, MySQL servers are added into mysql_servers.

Are the servers added in mysql_servers the MySQL server installed in the cluster? If yes, then there will be only one entry in mysql_servers and then, how do we define query rules to point to different host groups?


texts: How accurate is the sys.partition.rows column?

texts:
The system view sys.partitions has a column "rows" that is the total number of rows in a given partition.  For a table that is not partitioned (or only has one partition depending on how you look at it) this column gives the number of rows in the table.

I'm curious how accurate this column is and if I can use it instead of a SELECT COUNT(1) FROM TableName.  I've done some experiments where create a table and add a few thousand rows, delete a few hundred, add a few thousand more etc and the count has always been dead on.  However I have one table with about 700 mil rows and several indexes.  The row in sys.partitions for the clustered index is again dead on, however the other indexes show some slight variations (+-20k).

Does anyone know how this row is calculated and if it is as accurate as it appears?


texts: How to optimize a large join to create a WHERE IN

texts:
The following sqlite database query takes about 40 seconds on my Core i7.

SELECT t.a
FROM titles t
WHERE t.a IN (
    SELECT e.id
    FROM erpubtbl e
    JOIN abstract_mesh am ON am.pmid = e.pmid
    JOIN mesh_descriptor md ON md.id = am.mesh_id
    WHERE md.id = 100
    ORDER BY e.journal_year DESC)
AND t MATCH 'test';

I'm pretty sure the slow part is the IN.  The abstract_mesh table is a million plus rows.  Any idea how to speed this up, or suggestions for how to alter the schema to be more efficient?

Here's the schema:

CREATE TABLE `abstract_mesh` (
  `mesh_id` integer NOT NULL
,  `major_topic` varchar(1) DEFAULT NULL
,  `pmid` integer DEFAULT NULL
);

CREATE TABLE `erpubtbl` (
  `id` integer NOT NULL
,  `journal_id` integer  NOT NULL DEFAULT '0'
,  `pmid` integer NOT NULL DEFAULT '0'
,  `title` text
,  `author` varchar(50) DEFAULT NULL
,  `journal_month` varchar(25) DEFAULT NULL
,  `journal_year` integer DEFAULT NULL
,  `journal_page` varchar(50) DEFAULT NULL
,  `volume` varchar(100) DEFAULT NULL
,  `issue` varchar(50) DEFAULT NULL
,  `pubmed_entry_year` varchar(50) DEFAULT NULL
,  `pubmed_entry_month` varchar(50) DEFAULT NULL
,  `medline_pgn` varchar(50) DEFAULT NULL
,  `no_abstract` integer DEFAULT NULL
,  `publication_type` varchar(250) DEFAULT NULL
,  `country` varchar(50) DEFAULT NULL
,  `citation_subset` varchar(50) DEFAULT NULL
,  PRIMARY KEY (`id`,`journal_id`,`pmid`)
,  UNIQUE (`id`,`pmid`)
);

CREATE TABLE `mesh_descriptor` (
  `id` integer  NOT NULL PRIMARY KEY AUTOINCREMENT
,  `name` varchar(250) DEFAULT NULL
,  UNIQUE (`id`)
,  UNIQUE (`name`)
);

CREATE INDEX mesh_descriptor_name_idx on mesh_descriptor (name);
CREATE INDEX erpubtbl_year_idx on erpubtbl (journal_year);
CREATE VIRTUAL TABLE titles USING fts4(a NUMBER, t TEXT, tokenize=porter);


texts: Transform/map json object array to primitive for GIN indexing

texts:
Say I have a table with a jsonb column that contains data structured like this:

{
  "records": [
    {
      "id": 1,
      "tag": "address"
    },{
      "id": 1,
      "tag": "receipt"
    }
  ]
}

I'd like to be able to index this column such that I can request "give me all rows where there is a record of type 'receipt'".

I'm trying to find an expression that would output a jsonb array that looks like

["address","receipt"]

so then I can plug that expression into

CREATE INDEX ON t1 USING GIN (expr)

I've been playing around with all different combinations of the JSONB functions, but I think I'm in over my head.


texts: Is there a way to backtrack who connects to MySQL server? Aside from show processlist;? I want to see who connects last day. Thank you.


texts:
If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending

eg:

update table1 set col1 = col1 + 1 where PKcol < 3
update table1 set col1 = col1 + 1 where PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3
update table1 set col1 = col1 + 1 where NonPKcol = 3
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

In above case PKcol is primary key in table and NonPKcol is a non-primary key.

Will updates be locked for only the rows satisfying the 'where' condition? Is it based on value or index or primary column?


texts: What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?

texts:
In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app purpose or something that I'm missing?

mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


texts: Wrong estimated number of rows using TOP condition

texts:
I guess this is a very basic question but I can't get my head around it..
I have perfect statistics in place for a column but while using a TOP condition the estimated number of rows are always a number very close to the number used for the TOP operator giving me a complete wrong number.
Is there any easy explanation for this knowing that the statistics for the InterviewerID on the application table know exactly that theID 12868 rows have more than 3K rows ? Why is telling me 13.83?

Version being used: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)

Thank you all!

How I call the SP ?
EXEC agy.sp_searchTest3 @interviewerId = 12868,@searchTerm = 'aar'

Query causing the issues :

ALTER PROCEDURE [agy].[sp_searchTest3] (
    @interviewerId INT
    ,@searchTerm VARCHAR(50) = NULL
    ,@searchApplicationId INT = NULL
    )
AS
BEGIN
    IF @searchApplicationId IS NOT NULL
        SELECT TOP 10 u.userId
            ,u.firstName + ' ' + u.lastName AS 'fullname'
            ,app.applicationId
            ,u.loginId AS email
            ,adi.URI AS imageurl
        FROM [app].[application] AS app
        INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
        INNER JOIN [usr].[user] AS u ON u.userId = a.userId
        LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
            AND ud.documentTypeId = 5 -- (Portrait pic)
            AND ud.documentStateId NOT IN (
                3
                ,5
                ) -- (Rejected, Deleted)
        LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
        WHERE app.interviewerId = @interviewerId
            AND u.isActive = 1
            AND app.applicationId = @searchApplicationId

    IF @searchTerm IS NOT NULL
        SELECT
        TOP 15 -- everytime I change the top value the estimated number of rows change as well
        u.userId
            ,u.firstName + ' ' + u.lastName AS 'fullname'
            ,app.applicationId
            ,u.loginId AS email
            ,adi.URI AS imageurl
        FROM [app].[application] AS app
        INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
        INNER JOIN [usr].[user] AS u ON u.userId = a.userId
        LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
            AND ud.documentTypeId = 5 -- (Portrait pic)
            AND ud.documentStateId NOT IN (
                3
                ,5
                ) -- (Rejected, Deleted)
        LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
        WHERE app.interviewerId = @interviewerId
            AND u.isActive = 1
            AND (
                (u.firstName LIKE @searchTerm)
                OR (u.lastName LIKE @searchTerm)
                OR (u.loginId LIKE @searchTerm)
                )
END


texts: How to speed up mysql query against a 204 million record table

texts:
For some reason my server has been finicky lately and for the life of me I can't figure out why. I monitor the memory usage by running

shell# watch -n 5 free -m

without running any activities memory used is anywhere from 4-5 GB. Once I run a query it starts increasing all the way up to 35 GB. For example, see output below

Every 5.0s: free -m                                     Fri Mar 13 10:17:28 2015

             total       used       free     shared    buffers     cached
Mem:         36196      34429       1767          0        124       3172
-/+ buffers/cache:      31132       5064
Swap:        24563          0      24563

the query I am running is

SELECT DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12
FROM db1.tb1
WHERE NOT EXISTS (SELECT * FROM db2.tb2 WHERE db2.tb2.c1 = db1.tb1.c1)
and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N'
GROUP BY c1
LIMIT 5000

All columns on both tables are indexed. Normally this query only takes me a couple of minutes. But last couple days, it has been taking 4+ hours

The db1.t1 has 360 columns and 204 million records. So it is quite large
It uses the InnoDB engine

Ubuntu  v. 12.04

MySQL 5.5

I have 36 GB RAM installed

4 core Intel Xeon processor 2.93 GHz with hyper threading enabled.

Below is my my.cnf file (im only going to post the un-commented portions of the file)

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

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 50.253.189.57
key_buffer      = 5G
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 50
myisam-recover         = BACKUP
max_connections        = 500
table_cache            = 3200
thread_concurrency     = 10
bulk_insert_buffer_size = 1G
join_buffer_size = 500M
sort_buffer_size = 5G
query_cache_limit   = 0
query_cache_size        = 0
open-files-limit = 65535
tmp_table_size = 3G
max_heap_table_size = 1G
table_definition_cache = 1024
table_open_cache = 2048
thread_cache_size = 50
max_connect_errors = 1000000
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 26G
innodb_additional_mem_pool_size = 1G
innodb_buffer_pool_instances = 2
innodb_log_buffer_size = 1G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
key_buffer      = 16


texts: Update multiple Ids when SET = (Sql Query)

texts:
I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use:

UPDATE T1
     SET A1 = (SELECT TOP 1 X FROM T2 WHERE Y = ID1)
     WHERE A2 = ID1

The IN operator works fine for the WHERE clause as it will update the correct list but not in the sub query. In the SET it will get the top 1 from all values in the ID list and I just want it for that corresponding record/ID.

Using: Microsoft T-SQL

Thank You


texts: Replication issues on the subscriber side

texts:
So here is my situation,  an old admin who moved off to some better place had setup replication.   Come that special day when the admins account was disabled, replication stopped working.   No problem, we generated the scripts.  Disabled everything and recreated replication.  It all looks to be working correctly, with the performance status of "Excellent"

Now a few weeks passed and I realize none of the tables have updated on the subscribers.

Now I tried to re-initialize the But I see things stuck with this error

  The initial snapshot for publication X is not yet available."

Been like that for the last hour.

Reading on, some people suggest to troubleshoot this. I am down for that,  anyone have any pointers as to what I can look at?

Another suggestion was to look for a corrupted snapshot.  I am very interested how to identify a bad snapshot and how to fix it. What are the steps for that?

Here are some things I have completed.
[] Attempted to Resync

Note - before re-syncing I did generated a fresh snapshot using these instructions:

Open Distribution Server
Expand Replication, expand Local Publications.
Right click the publication and select View Snapshot agent status.
Click start and watch the snapshot be generated.

The snapshot finished quickly
The results,  well things still were not syncing.

Ok another suggestion was to delete the subscription on the subscriber.  Me being scared, tested this theory.  Deleted the subscription on a test setup.  Resynced, and the subscription was recreated and had new valid entries.

Now I tried that in production,  well I am stuck with the state of:

  The initial snapshot for publication X is not yet available.

Any help is greatly appreciated!


texts:
So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger

CREATE OR REPLACE
TRIGGER usr_t_audit_user_alnd
    AFTER LOGON ON DATABASE
    BEGIN
        INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
        VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    END;

to insert some standard informations into my table. It works fine, except the logoff time inside my other trigger isn't always that correct. But my actual problem is, that I don't want to insert all user logons. For example I only want to audit some chosen users. How can I achieve such a restriction? I can't put a WHERE-clause in my trigger. Anybody got some ideas?


texts: Restore database - maintenance plan task

texts: I need to schedule a task that restores a database once a week from a backup. I have setup maintenance plans for backing up databases (Management > Maintenance Plans) but cannot find out how to make a restore task - does this need to be an Execute T-SQL Statement Task, or is there a Toolbox item for it?


texts: See indices implicitly created for foreign key constaints in MySQL

texts:
When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a constraint on does get created implicitly, but MySQL doesn't want me to tell about its existence. Is there a way to see those implicit indices anyway?

The table type is InnoDB.


texts: Resolving Queries into a List of `object name.field`

texts:
I work on a team that has an SSRS setup with roughly ~1300 (and growing) canned reports. As one can imagine, this has presented problems when a breaking change is introduced to a table. Finding all the reports that may touch a table/field is error prone at best.

I'm trying to programmatically build a dependency model. Getting the schema for Owner -> Object -> Fields and how views/tables relate is fairly trivial.

What I'm struggling with is how to get a resolved list of fields from a given query. I can pull the query from the RDL files, but interpreting those files is far less so. Queries can contain aliases for fields and tables alike, plus there are problems such as select *. I'm trying to avoid a regexp hack, and I really don't want to write a SQL interpreter...

My initial thought was to loop through the RDL files and parse the explain plan output. While technically possible, this doesn't give me a complete list of resolved fields.

Is there any method in which I can use to get the DB analyse a given query and return a list of Object_Name.Field_Name? I don't mind having to do some text processing to pull out the results if necessary.


texts: Converting a SQL_ASCII database to UTF-8

texts:
I have a postgresql (version 8.1) database that has the encoding set to SQL_ASCII, combined with several apps that some are running using UTF8 and some with LATIN1. With this scope I have a database with the data divided in LATIN1 and UTF8.

I have the difficult task to deploy a brand new server, running PostgreSQL 9.3 with the database with the UTF8 encoding. But I'm stuck!

Here is what I have tried so far with no success:

pg_dump forcing UTF8: error converting non-utf8 to utf8
pg_dump forcing LATIN1: no error during dump or restore, but the characters with accentuation are totally wrong
pg_dump forcing LATIN1 and conversion with iconv to UTF8: same error as above

Any ideas?


texts:
MySQL's SHOW GRANTS shows the permissions of the current user.

Is there a way to log in as root and show the permissions of all users?


texts: Custom location for postgis extension .control file

texts:
I am trying to install the postgis extension on my database, which resides on a web hosting service. The problem is that the control files seem to have to be found in /package/host/localhost/postgresq-9.3.2-1. I am using a hosting company where all we can access is what is in our respective home/ folders. The Postgres executables are found in the root directories which we cannot access.

I have successfully installed the PostGIS binaries, but now when I try to create the extension with CREATE EXTENSION postgis, I get the error ERROR: could not open extension control file /package/host/localhost/postgresql-9.3.2-1/share/extension/postgis.control. I cannot write anything to this directory since it is just intended to store the executables for anyone to access via some scripts the hosting provider as set up.

Can I have this .control file be somewhere else instead? Or am I just out of luck entirely?


texts:
Not sure how to start describing this so I get straight to it.

I have a SQL Server 2012 database with a sample table Table_1 with the below (simplified) design:

id: int (PK, ID, NOT NULL)
name: varchar(100) (NOT NULL)
label: varchar(100) (NOT NULL)
description: varchar(max) (NOT NULL)

on my front end form user is asked for a name and description only.

What I want to achieve from the database is to, at the point of creating new record, get the value of the name field, convert it to lowercase and replace any spaces with underscores, and then insert the resulting string into the label column.

For example if user provided name "Sample Product", I want the label to be "sample_product".

How can I achieve this and what is the best approach: triggers or the calculated field property can do this?

Thanks in advance for any help or pointers.


texts: Better way to ensure referential integrity of one column to a composite primary key?

texts:
Given a first table

CREATE TABLE table1 (
   column1 INTEGER,
   column2 TEXT,
   PRIMARY KEY (column1, column2));

does SQL have an idiomatic way to constrain column3 in a second table

CREATE TABLE table2 (
    column3 INTEGER,
    column4 TEXT,
    PRIMARY KEY (column3, column4));

such that column3 ⊆ column1?

My current solution is this kludge:

CREATE TABLE table0 (
    column0 SERIAL PRIMARY KEY);

CREATE TABLE table1 (
   column1 INTEGER REFERENCES table0 (column0),
   column2 TEXT,
   PRIMARY KEY (column1, column2));

CREATE TABLE table2 (
    column3 INTEGER REFERENCES table0 (column0),
    column4 TEXT,
    PRIMARY KEY (column3, column4));

I learned the hard way that a foreign key cannot refer to only one value of a composite primary key: https://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005.

I'm using PostgreSQL, if that matters.

Edit (2020-04-26)

One comment recommended I add a concrete example.

The table table1 contains aliases. Multiple people can be named 'Jane Doe', and any one 'Jane Doe' can also be called 'Jane Smith' and 'Jane1990'. Each person is uniquely identified by the number in column1. Either column can have duplicates, but duplicate rows are not allowed.

The table table2 lists books published by people in table1, but table1 can include people who have never published a book, and so column3 ⊆ column1. Again duplicate rows are not allowed.