texts: Mysql Query Take Longer To Prcoess On My Dedicated Server Than My Local Server

texts:
One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) server. Both databases, tables, engine, columns, indexes all are same/identical on both servers.

On my development server (local) the query takes only milliseconds (less than a second) where on my dedicated server it takes 4-5 seconds.

On my dedicated Server I noticed in Task Manger that msqld is running by a command line which defined my.ini file:

And this is the my.ini file contents on that location:

Is there anything in my.ini that I can change to boost the speed?

Here are the outputs from explain as requested:

Dedicated server

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  PSCThana    ALL ThanaID,UpazillaID  NULL    NULL    NULL    509 Using where
1   SIMPLE  PSC15ALL    ref STD_ROLL,THANA  THANA   18  siamit.PSCThana.ThanaID 2648    Using where

Local Server

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  PSCThana    ALL ThanaID,UpazillaID  NULL    NULL    NULL    509 Using where
1   SIMPLE  PSC15ALL    ref STD_ROLL,THANA  THANA   18  siamit.PSCThana.ThanaID 2837    Using where


texts: "connectivity libraries needed are not found" error in IBM Data Studio

texts:
UPDATE

I am getting the following error when I try to create a new database in IBM Data Studio v3.1.1.0.

The connectivity libraries that are needed for local or remote non-JDBC operations were not found. To provide these libraries, you can install IBM data server client or a local DB2 server instance.

I have already started the instance using

db2start

command.

After searching exhaustively, I am not able to find any help on the internet regarding this error.


texts: SQL Server 2008 onward - Globalization with Date Time

texts:
My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own culture.

There are some Date, Time and DateTime columns in database. Should I store them:

based on SQL Server Date/Time?
based on Windows Date/Time?
based on Windows Date/Time converted to UTC?

I think I do not need to look into this globalization part at all as the application is standalone and databases are never going to be shared. Data will stay at same machine only, it will not be moved to central location. So, second option is best suited I guess.

I read this question and its answer. DateTimeOffset looks odd; it is generally used for row versioning. I am not sure if it is needed here. That answer is not even accepted; not sure if it really helped OP.


texts:
This started happening today for absolutely no reason. Worked perfectly last week. I didn't touch the machine.

I have an issue described here and need to downgrade in order to be able to upgrade. Currently I'm not able to enter the shell as it exits with error #4

need to upgrade database admin with pdfile version 4.22, new version: 4.5

If I run it with --upgrade I get:

ERROR: error: exception cloning object in admin.system.users system.users entry must have either a 'pwd' field or a 'userSource' field, but not both obj:{ _id: "nod$
exception in initAndListen: 2 system.users entry must have either a 'pwd' field or a 'userSource' field, but not both, terminating

I'm trying sudo apt-get install mongodb-10gen=2.2.10but it doesn't work.

Can anyone direct me to a command I can use to install version 2.2.10?


texts:
I'm running a pretty big (or maybe I should call it SELECT & INSERT-intensive) database.

Now I've heard here and there that a MEMORY-based table is better for my situation (since I now run on a RAID1 HDD array consisting of 2 HDDs and can't afford SSDs).  But I know that using the MEMORY engine is not good in case of a power outage (as the data on there will be lost). And since I'm running the server in my own house without UPS... well... you see where this is going.

So, how can I have 2 tables, 1 on the disk (InnoDB for example) and 1 in the memory, then have the memory table sync with the on-disk table once every 5 minutes so the data won't get lost completely in case of a power outage? (I realise that the data since the last sync will be gone.)

At the moment, I can use about 2GB of RAM for the database (which should not be a big deal because at the moment the table is about 300MB big with InnoDB).

EDIT:
you can find the code of the slowest page here (link to pastebin) and the page where it is running here


texts: Why have “unidirectional ‘mapping’” tables?

texts:
I've been reading about how Pinterest sharded their MySQL databases here.

One of the things they talk about in that post is having “unidirectional ‘mapping’” tables such as

 CREATE TABLE board_has_pins (
  board_id INT,
  pin_id INT,
  sequence INT,
  INDEX(board_id, pin_id, sequence)
) ENGINE=InnoDB;

and then an opposite pin_owned_by_board.

My question is, why would you have these “unidirectional tables” for “mapping” and not just one with a different index structure? I don't think it can stop duplicates due to the sequence being a unix timestamp, but the index does limit only being able to select by the board_id in this instance or the full index (efficiently anyway). So why not have separate indices on board_id and pin_id? Is this down to the fact that a pin could in theory live on a separate shard even though they try to keep them on the same one so it makes sense to make it “unidirectional”?


texts: how to get 'Queries per second avg' using SQL (not 'mysqladmin status')

texts:
When I start mysqladmin, it prints out several statistics, including "Queries per second avg":

# mysqladmin --defaults-file=../mylogin.cnf status
Uptime: 4568115  Threads: 859  Questions: 3703806462  Slow queries: 19415  Opens: 10300505  Flush tables: 247  Open tables: 2000  Queries per second avg: 810.795

Can I retrieve this "Queries per second avg" value within an SQL query? We use MariaDB 10.1.x


texts: How to rewrite a query with MAX in WHERE clause

texts:
I have a query with a max in the where clause, which is slow.

select count(*)
from   TableName tbl1
where  tbl1.id = (
                select max(tbl2.id)
                from TableName tbl2
                where tbl2.companyId = tbl1.companyId)
and    tbl1.active = 2

Please note that both tables are the same.

I rewrote it as:

select count(*)
from   TableName tbl1
inner join (
                select max(id) as id, companyId
                from TableName tbl2
                where tbl2.active= 2
                group by companyId
        ) tbl2
        on  tbl2.companyId = tbl1.companyId
        and tbl2.id=tbl1.id

but number of records is different.

Can anyone say how can I rewrite it for better performance?


texts: MySql shell command not replicated to slave

texts:
I have a shell script that looks something like that:

 #!/bin/bash
 mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv'
 INTO TABLE db_name.db_table FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'
(\`col1\`,\`col2\`,\`col3\`);"

the problem is that when I run this script on my master server the call doesnt get replicated to the slave server (even though /srv/some_file.csv exists on both servers).

any ideas how to make shell command replicate over servers?


texts:
I have upgraded an application to a newer version, and this application runs in a new server. The old application runs on another server. The new application went live yesterday (6/7), and the old one was stopped at the same time.
The problem I face now is that I have restored the data from the old server to the new one, and it is only till 3/6. From 4/6 to 5/7, the data is not available in the new application, because it was go live only yesterday.
However the new server holds the data from 6/7, and the old server not.
So what I want to do now, is redo the backup of the old database, and restore it to the new server. So the data will be available till 5/7 (from the old database).
Is it possible to combine this old data with the data from 6/7 in the new database?
And importantly, the old version of the database is 9.2.24 and the new one 12.
Perhaps a command to dump the old database until 5/7?


texts: "Cannot find data type" restoring a SQL Server 2014 backup on SQL Server 2008 R2

texts:
I have been passed a SQL Server 2014 backup that needs to be imported onto one of my SQL Server 2008 R2 servers. The backup is valid as I can restore it onto one of Dev 2014 SQL servers.

I have done the script out task and that works fine for the most part. However one issue I face is that the SQL Server 2014 database had tables using data compression and I get errors when I try to import these to 2008 R2 such as this.

  Column, parameter, or variable #50: Cannot find data type dbo.tCompressedBinary.

What options do I have for this issue? Does anyone recommend any other approach (apart from setting up a SQL Server 2014/2016 prod box) that I could follow?


texts: MySQL 5.6: Slave_IO thread stops working while replicating

texts:
Standard replication breaks for no apparent reason.

MariaDB [(none)]> SELECT @@version, @@version_comment;
+----------------+-------------------+
| @@version      | @@version_comment |
+----------------+-------------------+
| 5.5.52-MariaDB | MariaDB Server    |
+----------------+-------------------+
1 row in set (0.00 sec)

slave is running fine.

# mysql -e "SHOW SLAVE STATUS\G" | grep "Slave"
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Id  | User        | Host                        | db       | Command | Time | State                                                                       | Info             | Progress |
+-----+-------------+-----------------------------+----------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|   1 | system user |                             | NULL     | Connect |  438 | Waiting for master to send event                                            | NULL             |    0.000 |
|   2 | system user |                             | NULL     | Connect |  120 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|   3 | u271869     | S0280I2.ba.ad.ssa.gov:59117 | mysql    | Sleep   |    2 |                                                                             | NULL             |    0.000 |
|   4 | nagiosxi    | localhost                   | nagiosxi | Sleep   |  176 |                                                                             | NULL             |    0.000 |

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: $IP
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 433
               Relay_Log_File: mysql-relay.000265
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 433
              Relay_Log_Space: 710
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1124732721
                  Master_UUID: 4412a455-e1d0-11e3-835a-5254007fe78d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl: /etc/mysql/ca-cert.pem
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
mysql replication
shareedit
edited Jul 18 '14 at 13:16


texts: After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

texts:
I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local Indexes but the problem lies with the PK index for some reason. I have the same DDL in another schema and it works fine there. Not sure what tio look for to resolve this.

I am using the Informatica (ETL tool) to load data into this table. Before loading we do truncate partition table:

SQL> ALTER TABLE owner_name.SAMPLE_PARTITIONED_TBL
   2 TRUNCATE PARTITION SMPL_201001 DROP STORAGE;

As the load starts I get the following error:

Message: Database errors occurred:
ORA-01502: index 'owner_name.SAMPLE_PARTITIONED_TBL_PK' or
partition of such index is in unusable state

Looking at the indexes's status...

SQL> select STATUS from all_indexes
  2  where INDEX_NAME like 'SAMPLE_PARTITIONED_TBL_PK';

STATUS
--------
UNUSABLE

Now after executing SQL> ALTER INDEX owner_name.SAMPLE_PARTITIONED_TBL_PK REBUILD; the local partitioned index returns to STATUS=VALID state the loading can continue without a problem.

UPDATE:

As per @Mat's observation below, I am checking if the PK Index is locally partitioned:

SQL> select * from all_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

I see SAMPLE_PARTITIONED_TBL_PK and the other indexes in the list.

SQL> select * from  all_part_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

Here I see all the indexes with LOCALITY='LOCAL'; except SAMPLE_PARTITIONED_TBL_PK is missing in the result set thus confirming Mat's observation. :)

Thanks.


texts: mysqldump: replace drop table in output with delete?

texts:
I am using mysqldump to dump some records in a table:

mysqldump mydatabase mytable --where="field=value"

this emits script that includes drop table mytable statement.

Since I use the --where option, when I replay the script, I want to overwrite only these records that satisfy the condition field=value. I want the emitted script to delete just these records:

delete from `mytable` where field=value

is there a way to achieve this?


texts:
in our environment, reports are running on the subscriber and resulting in deadlocks with MS Replication SPs, which are doing inserts/updates. What's the best way to eliminate the deadlocks?
We have several views with so many dependencies without NOLOCK.
What is the best option:

Updating all the views with NOLOCK
or

Using the READ UNCOMMITTED ISOLATION LEVEL

Or some other best option?
Our requirement is reading the current data from subscriber (with no latency from publisher). We are OK with some dirty reads. Looking for best approach before updating so many views.


texts: Alter table - existing column as primary key

texts:
I am attempting to set one of the columns in the table as primary key.

Here are the columns from the table:

Now, when I run the sql:

ALTER TABLE cb_company
ADD PRIMARY KEY(uuid);

I get the following error -

Error Code: 1072. Key column 'uuid' doesn't exist in table

Here's the output of SHOW CREATE TABLE cb_company

CREATE TABLE `cb_company` ( `uuid` text,
                            `num_employees` text,
                            `name` text,
                            `aliases` text,
                            `short_description` text,
                            `primary_role` text,
                            `funding_total_usd` int(11) DEFAULT NULL,
                            `num_investments` text,
                            `homepage` text,
                            `num_founders` int(11) DEFAULT NULL,
                            `founders` text,
                            `num_investors` int(11) DEFAULT NULL,
                            `investors` text,
                            `location` text,
                            `category_list` text,
                            `num_funding_rounds` int(11) DEFAULT NULL
                          ) ENGINE=InnoDB
                            DEFAULT CHARSET=utf8mb4
                            COLLATE=utf8mb4_0900_ai_ci


texts: Can I do a sargable first letter match on two tables?

texts:
select value
from persons p join persons2 p2
    on left(p.lastname,1) = left(p2.lastname,1)

SQL Server. Is there any way to make this SARGable/run faster? I can't create columns on persons table, but I can create columns on persons2.


texts:
I have a database and table dbo.Test_Table, with list of Users under Security -> Users. those users have access to all the tables in my database.

I would like to know how do I assign certain users to specific tables (e.g dbo.Test_Table) within the database, that only those Users can view those tables within my database.


texts:
In SSMS I would like to use shortcuts for code snippets, just like in Visual Studio:

  type 'shortcut'; hit TAB -> snippet expanded

In SSMS I need to CTRL+K, CTRL+X, mouse, mouse, find, go back, find, mouse. This is not a shortcut, nothing "short" about it.

I've added this sample snippet to my list of snippets:

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Transaction</Title>
      <Author />
      <Description>Transaction wrapper</Description>
      <HelpUrl />
      <SnippetTypes />
      <Keywords />
      <Shortcut>tra</Shortcut>
    </Header>
    <Snippet>
      <References />
      <Imports />
      <Declarations>
        <Literal Editable="true">
          <ID>TSQL</ID>
          <Type />
          <ToolTip />
          <Default>TSQL</Default>
          <Function />
        </Literal>
      </Declarations>
      <Code Language="SQL"><![CDATA[
BEGIN TRANSACTION TRAN1
$TSQL$
ROLLBACK TRANSACTION TRAN1]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

I can find it via CTRL+K, CTRL+X, but I'm unable to invoke it by tra + TAB shortcut. Am I missing something? Can SSMS even do this?

I'm using SSMS v13.0.16100.1, but database is SQL Server 2014.


texts: How to print the information of all tables using Oracle?

texts:
I'm studying DDBB and we are using Oracle. Using too much tables can be really difficult for me because I have to remember table1.content2, table3.content1, etc.

So because of that, I would like to print (in paper) the header tables to make it easier.

I know my explanation is not very good so I tried to "draw it" below:

select * from user_tables;

↓

table_name|
-----------
table1    |
-----------
table2    |
-----------
table3    |

What I want:

table1
-----------------------------
content1| content2| content3|

table2
-----------------------------
content1| content2| content3|

table3
-----------------------------
content1| content2| content3|

How can I get this in paper?

Thanks!


texts: Can't NoSQL DB ensure consistency if it is residing in a single server?

texts:
As NoSQL DBs always prefer BASE over ACID.
Everyone always says that you can't have ACID when you are working with a NoSQL DBs.
For example, we have MongoDB (a popular NoSQL DB), which is hosted within a single server. No replicas. If the client app updates a document, strong consistency should take place.
(Just curious, not expert)


texts: Looking for feedback on a design for a simple task manager

texts:
I've got tired of using notepad / excel to manage my tasks at work.
Since tasks may sometimes contain sensitive data, my boss will not allow us to use an online task management system, so I've decided to create one myself.

I'm not looking for tfs backlog style, I want to keep it as simple as possible.
my goal is that everyone can add/edit/delete tasks that is associated with them, can sort tasks by priority, hide completed tasks, etc. nothing too fancy.

Here is my initial tables design, please let me know if I'm missing something critical, or if you have any suggestions to make it better.

CREATE TABLE tblProject
(
    Project_Id int identity(1,1) primary key,
    Prject_Name varchar(300) not null,
    CONSTRAINT uc_tblProject_Prject_Name UNIQUE(Prject_Name)
)

CREATE TABLE tblUser
(
    User_Id int identity(1,1) primary key,
    User_First_Name varchar(30) not null,
    User_Last_Name varchar(30) not null,
    User_login_Name varchar(10) not null,
    User_Password varchar(6) not null,
    CONSTRAINT uc_tblUser_User_login_Name UNIQUE(User_login_Name)
)

CREATE TABLE tblUserToProject
(
    UTP_User_Id int,
    UTP_Project_Id int,
    primary key(UTP_User_Id, UTP_Project_Id)
)

CREATE TABLE tblTaskStatus
(
    Task_Status_Id int identity(1,1) primary key,
    Task_Status_Name varchar(30) not null,
    CONSTRAINT uc_tblTaskStatus_Task_Status_Name UNIQUE(Task_Status_Name)
)

CREATE TABLE tblTaskPriority
(
    Task_Priority_Id int identity(1,1) primary key,
    Task_Priority_Name varchar(30) not null,
    CONSTRAINT uc_tblTaskPriority_Priority_Name UNIQUE(Task_Priority_Name)
)

CREATE TABLE tblTask
(
    Task_Id int identity(1,1) primary key,
    Task_User_Id int,
    Task_Status int,
    Task_Title varchar(300),
    Task_Description varchar(max),
    Task_Remarks varchar(500),
    Task_Project int,
    Task_Parent int null,
    Task_Priority int,
    Task_Create_Date datetime,
    Task_Due_Date datetime,
    Task_Time_Estimate int, -- (minutes)
    Task_Actual_Time int -- (minutes)
)

CREATE TABLE TblTaskActivityLog
(
    TaskHistory_Id int identity(1,1) primary key,
    TaskHistory_Task_Id int,
    TaskHistory_CreateDate int,
    TaskHistory_Description varchar(max)
)


texts: Get the windows account which runs the query?

texts:
I'm using sql server 2008 r2.

I logged in to ssms which in turn connects to a remote sql server machine.

im writing a query which writes a file.

I need to know - which windows permissions I should grant to c:\myfolder

is there any select query which can provide me the windows account who is actually finally writes  the file ?


texts: In MongoDB, is having lots of small collections better than having one huge collection?

texts:
Huge collections have huge indexes that may not fit into RAM. Is that problem entirely solved with sharding ?

If I have lots of small collections with small indexes, what are the options for storing them ? Since only very few of them would be bigger than 200mb they wouldn't be eligible for sharding. What MongoDB database architecture can hold a huge number of fairly small collections ? Since all the queries would be to a specific collection, would this be as fast as a targeted query in a sharding environment ? Faster ?


texts: sql statement in access that group items and count their sum

texts:
I would like to write an sql statement in microsoft access that group items and count their sum

My table is like below :

id : Auto-Increment
Items : String
Quantity : Number

Eg :

id | Items | Quantity
1 | A | 10
2 | B | 5
3 | A | 10
4 | C | 20
5 | C | 20
6 | A | 10
7 | C | 20
8 | B | 5
9 | B | 5

Expected result is

id | Items | Quantity
1 | A | 30
2 | B | 15
3 | C | 60


texts: How to resolve error ORA-19802 when starting up an Oracle database instance?

texts: I tried starting up my instance and received the error ORA-19802. Cannot use db_recovery_file_dest without db_recovery_file_dest_size. How can I resolve the problem?


texts: Log shipping Primary database log backup job failing/stuck

texts:
We have set up LS to do cutover from standalone SQL server 2012 to clustered 2012 servers.

The primary database where we have set up LS is also involved in database mirroring and is about 1.8 TB in size. This is PERF env to test the actual cutover and transactions are not frequent on database where LS is set up.

Issue: I see that LS backup job on primary runs longer than scheduled [every 15 mins] or fails with below error:

  Unhandled Exception: System.IO.FileLoadException: The process cannot
  access the file because it is being used by another process.
  (Exception from HRESULT: 0x80070020)    at

There is no fixed pattern when its failing. generally it completes in 18-20secs but on some runs it fails when running over 50 mins or so.

How can i troubleshoot this further?

Edit: @ Log backups are from 100-200 MB generally every 15 mins


texts:
I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the tables:

Authors (Au_Id  PK, Au_fname, Au_lname, ...)

Titles (Title_Id PK, Title, Title_type, ...)

TitleAuthor (Au_Id PK, Title_Id PK, Au_order, ...)

In TitleAuthor, I am  not sure of what Au_order means, but I suspect it is the order in which an author's name is cited in a book with more than one author. Au_order is a TinyInt data type, btw. These queries are supposed to be introductory, i.e. no fancy joins or subqueries.

This is what I have tried:

1)

Select Au_Id from `TitleAuthor` where  Count(Auth_id)>1;

But this gives us the author name, not the book name (remember: intro query)

2) Figuring out which books/titles are mentioned more than once in TitleAuthor:

Select Title_id from Title-Author where Count(Title_Id)>1;

This gives me no results for some reason, though no error messages either. I suspect I may need grouping and having, but this is not working either.

Any Ideas?


texts: identify queries experiencing sos_scheduler_yield

texts: Is there a way to capture the queries experiencing the SOS_Scheduler_Yield wait type as it is happening.  My cpu is pegging out at night and I suspect its some xml processing that is causing it by chewing up all my processor cycles; however, the application design is such that what is coming back as having the highest worker time is a section of xml that should only be run one time per client.  So I need to catch the individual offending query each night to make my case.


texts:
I have a collection which have size : 60 GB

I want to shard this collection. Shard key is hash index and unique.

I followed this document https://docs.mongodb.com/manual/reference/limits/#Sharding-Existing-Collection-Data-Size

Avg byte size of shard key values 56287232

maxCollectionSize is coming 9.28 GB

As maxCollectionSize and Actual collection size different is huge i am unable to get what chunk size should i use

Thanks for help


texts:
I was trying to fine the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed.  Is anyone able to explain was SQL server is doing here?

Many thanks

declare @p1 int
set @p1=1073764508
declare @p2 int
set @p2=180527133
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',N'SELECT A.POPUP,A.EMAIL,A.CREATEDBY,A.BATCHJOBID,101090,B.EMAIL,B.EVENTPOPUPDISPLAYWHEN,B.EVENTEMAILALERTSWHEN,B.ID,101090,C.LANGUAGE,C.ID,101090,D.RECID,D.STATUS,D.CAPTION,D.COMPANY,D.BATCHJOBID,D.STARTDATETIME,D.STARTDATETIMETZID,D.ENDDATETIME,D.ENDDATETIMETZID,D.ORIGSTARTDATETIME,D.ORIGSTARTDATETIMETZID,D.CANCELEDBY FROM BATCHJOBALERTS A,SYSUSERINFO B,USERINFO C,BATCHJOBHISTORY D WHERE (A.CREATEDBY=B.ID) AND (C.ID=B.ID) AND (((D.ALERTSPROCESSED=@P1) AND (D.BATCHJOBID=A.BATCHJOBID)) AND ((((D.STATUS=@P2) AND (A.BATCHJOBENDED=@P3)) OR ((D.STATUS=@P4) AND (A.BATCHJOBERROR=@P5))) OR ((D.STATUS=@P6) AND (A.BATCHJOBCANCELED=@P7))))',@p5 output,@p6 output,@p7 output,1,4,1,3,1,8,1
select @p1, @p2, @p5, @p6, @p7


texts: Model separate Person types as separate tables at beginning of application's lifetime?

texts:
If I have a couple of distinct types of Person that will always have a set of disjoint fields, should I go ahead and model them with two different tables?

For example, say I'm setting up a system that connects Worker with Hiring Manager, where both are expected to be individual humans. These two different types of Person will never share members.

At the application layer, I don't expect either of these two types of people to log in, initially, but at some point in the application's life, they will have emails and passwords and so on.

What's the best approach at the beginning?


texts: pgadmin: can't insert jsonb[] values with strings

texts:
I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "two"}, {"three": "four"}}.

How to even insert nested string values like:

{ {"one", "two", "three"},
  {"four", "five", "six"},
  {"seven", "eight", "nine"} }

ERROR: invalid syntax..., token "one" invalid


texts: Filter extended event xml_deadlock_report by database

texts:
I have added an extended event log that logs blocked_process_report events and xml_deadlock_report events.

I can exclude certain databases from the blocked_process_report by adding WHERE ([database_name]<>N'XXXXXXXXXXXXXXX').

But how do I exclude a database's xml_deadlock_report events? The property "sqlserver.database_name" appears to be set to "master" for a deadlock event, no matter where the deadlock occured. I suppose this is because in theory multiple databases could be affected by the same deadlock.

The server in question contains a database which constantly has deadlocks, but which is managed by a different entity and does not concern me. I would like to prevent these events from filling up the log.

This is the script used to create the log:

CREATE EVENT SESSION [blocked_process] ON SERVER

ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([database_name]<>N'XXXXXXXXXXXXXXX')),

ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.username))

ADD TARGET package0.event_file(SET filename=N'D:\xxx\blocked_process.xel',max_file_size=(65536),max_rollover_files=(5),metadatafile=N'D:\xxx\blocked_process.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO


texts: Slow queries on billions-rows-table // index used

texts:
Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with.

My project is about collecting data from devices (up to 1000 or more devices), where every device is sending one data block every second, which makes about 3 million rows per hour.

Currently I've got one big table where I store the incoming data of every device:

CREATE TABLE data_block(
    id bigserial
    timestamp timestamp
    mac bigint
)

Because there are several types of data a data block can (or can not) include, there are other tables which reference the data_block table.

CREATE TABLE dataA(
    data_block_id bigserial
    data

    CONSTRAINT fkey FOREIGN KEY (data_block_id) REFERENCES data_block(id);
);
CREATE TABLE dataB(...);
CREATE TABLE dataC(...);
CREATE INDEX index_dataA_block_id ON dataA (data_block_id DESC);
...

It is possible that in one data_block there is 3x dataA, 1x dataB, but no dataC.

The data will be kept for some weeks, so I'm going to have ~5 billion rows in this table. At the moment, I have ~600 million rows in the table and my queries take a really long time. So I decided to make an index over timestamp and mac, because my select statements always query over time and often also over time+mac.

CREATE INDEX index_ts_mac ON data_block (timestamp DESC, mac);

...but my queries still take ages. For example, I queried data for one day and one mac:

SELECT * FROM data_block
WHERE timestamp>'2014-09-15'
AND timestamp<'2014-09-17'
AND mac=123456789

Index Scan using index_ts_mac on data_block  (cost=0.57..957307.24 rows=315409 width=32) (actual time=39.849..334534.972 rows=285857 loops=1)
  Index Cond: ((timestamp > '2014-09-14 00:00:00'::timestamp without time zone) AND (timestamp < '2014-09-16 00:00:00'::timestamp without time zone) AND (mac = 123456789))
Total runtime: 334642.078 ms

I did a full vacuum before query run. Is there an elegant way to solve such a problem with big tables to do an query <10sec?

I read about partitioning, but this won't work with my dataA, dataB, dataC references to data_block_id right? If it would work somehow, should I make partitions over time or over mac?

I changed my index to the other direction. First MAC, then timestamp, and it gains a lot of performance.

CREATE INDEX index_mac_ts ON data_block (mac, timestamp DESC);

But still, queries take >30sec. Especially when I do a LEFT JOIN with my data tables. Here is an EXPLAIN ANALYZE of the query with the new index:

EXPLAIN ANALYZE SELECT * FROM data_block WHERE mac = 123456789 AND timestamp < '2014-10-05 00:00:00' AND timestamp > '2014-10-04 00:00:00'

Bitmap Heap Scan on data_block  (cost=1514.57..89137.07 rows=58667 width=28) (actual time=2420.842..32353.678 rows=51342 loops=1)
  Recheck Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on index_mac_ts  (cost=0.00..1499.90 rows=58667 width=0) (actual time=2399.291..2399.291 rows=51342 loops=1)
        Index Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
Total runtime: 32360.620 ms

Unfortunately my hardware is strictly limited. I'm using an Intel i3-2100 @3.10Ghz, 4GB RAM. My current settings are as following:

default_statistics_target = 100
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 512MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 2GB
max_connections = 20
random_page_cost = 2


texts:
I'm trying to create a login as follows:

CREATE LOGIN [Test] WITH PASSWORD='Test123', SID=SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513')

But no matter what it looks like SID only accepts a binary input, no subqueries or anything else really.

Incorrect syntax near 'SID'.

Is there a way to create a login with SID with a string?


texts: Prefix matching while importing large CSV file

texts:
I have list of huge phone numbers to be imported to the database. And want to group each phone number by their states and timezones of the state, by identifying them from their prefix. Timezones are Eastern, Central, Pacific etc .. For example 786800XXXX is a number from Florida which is in Eastern timezone. I thought of doing this via a trigger where I check the first three digits of each number and then put them to matched state. Something like SELECT LEFT(NEW.contact , 3) INTO vAreaCode; and then
SET NEW.timezone = vTimezone; This probably is going to be resource hog on a busy server. What would be the best way of achieving this task?

The reference table look like

CREATE TABLE `timezones` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `prefix` varchar(11) DEFAULT NULL,
  `state` varchar(11) DEFAULT NULL,
  `timezone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

id   prefix  state   timezone
 1   786     Florida    EDT
 2   213     California PST

And my contacts table look like

CREATE TABLE `contacts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `contact` varchar(11) DEFAULT NULL,
  `timezone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

id   contact   timezone
 1   2147483647 EDT
 2   2248918882 CST


texts: Issues with module signing and SSIS catalog internal procedures

texts:
I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user.

I found this article which detailed creating a certificate and signing the module so that a privileged login could execute it.

When I try to run any command impersonating the login, I receive the following error:

  Cannot execute as the server principal because the principal "CrossDbCertLogin" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Here's a script to reproduce the problem:

create certificate CrossDatabaseCert
ENCRYPTION BY PASSWORD = 'passA'
WITH SUBJECT = 'Certificate for Cross-Database Database Impersonation'
GO

CREATE Login CrossDbCertLogin FROM CERTIFICATE CrossDatabaseCert
GO

backup certificate CrossDatabaseCErt to file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(
    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passA',
    ENCRYPTION BY PASSWORD = 'passB'
)
go

use msdb
create certificate CrossDatabaseCert from file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passB',
    ENCRYPTION BY PASSWORD = 'passA'
)
Go

EXECUTE AS LOGIN=N'CrossDbCertLogin';
SELECT 1

Eventually I add the certificate to the stored procedure, but it seems the impersonation isn't even functioning so I'm trying to get that working first.

What am I doing wrong?

EDIT: A little more info on my procedure.

When I change it to "EXECUTE AS OWNER" (in the same form as the example I followed), I receive the following errors.

  Msg 27123, Level 16, State 1, Procedure create_execution, Line 39
  The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

  Msg 27123, Level 16, State 1, Procedure set_execution_parameter_value, Line 34
  The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

  Msg 27123, Level 16, State 1, Procedure start_execution, Line 32
  The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Inside my procedure I call three internal SSIS Catalog Procedures: Create_Execution, Set_Execution_Parameter_Value, and Start_Execution.

When I change the procedure to execute as the SQL Agent Service account, I get the following error:

  Msg 15199, Level 16, State 1, Procedure prepare_execution, Line 34
  The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

EDIT2: I ended up using a SQLCLR SProc to call my original proc, which solved all the issues.


texts: Compile Memory - Relationship between XE query_optimizer_memory_gateway and DMV sys.dm_exec_query_optimizer_memory_gateways

texts:
I’m studying and testing what happens during the compilation process of a query and how the Compile Memory is treated. You can have a look here (https://techcommunity.microsoft.com/t5/sql-server/multi-fold-increase-in-throughput-for-big-gateway-query-compiles/ba-p/384485) for further details.
I’m doing some tests hitting AdventureWorks database with a medium complex query using SQLQueryStress and checking the output of the Extended Event query_optimizer_memory_gateway and executing continuously from SSMS the DMV sys.dm_exec_query_optimizer_memory_gateways.
I expected to see a correlation between the values of the column “Threshold” of the DMV (Medium and Big gateways) and the event fields medium_gateway_threshold and big_gateway_threshold of the XE. Actually, to me, these should be the same. Instead, I do not find any of the values returned by the DMV in the values gathered by the XE.
Does anyone know the correlation, if it exists, between the above cited fields of the DMV and XE? Or does anyone explain the different meanings of these fields?
Thanks in advance


texts:
[ERROR] mysqld: Incorrect information in file: '/mysql/#sql50f_eb5128_1.frm'

Received this error while restoring old snapshot. How to confirm which table which is causing this error message? I'm sure this must be MySQLdatabase as all other databases are INNODB. I am using mariadb 5.5.


texts:
I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support).

Now, supposed it contains two records:

1, { name: 'John', age: 30 }
2, { name: 'Jane', age: 20 }

Now, supposed I want to get the name of every person older than 25. What I have tried is:

select data->'name' as name from persons where data->'age' > 25

Unfortunately, this results in an error. I can solve it by using ->> instead of ->, but then comparisons don't work as expected any more, since not the numbers are compared, but their representations as strings:

select data->'name' as name from persons where data->>'age' > '25'

I then figured out that I can actually solve the issue by using -> and a cast to int:

select data->'name' as name from persons where cast(data->'age' as int) > 25

This works, but it's not that nice that I have to know the actual type (the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?).

I then figured out that if I manually convert to text using the :: syntax, everything works as expected, too - although we are now comparing strings again.

select data->'name' as name from persons where data->'age'::text > '25'

If I then try this with the name instead of the age, it doesn't work:

select data->'name' as name from persons where data->'name'::text > 'Jenny'

This results in an error:

  invalid input syntax for type json

Pretty obviously, I don't get something here. Unfortunately, it's quite hard to find any real-world examples of using JSON with PostgreSQL.

Any hints?


texts:
At around 10am yesterday I started seeing constant read/write activity to the TempDB database via our RedGate SQL monitor.
This is unusual because usually our disk operations are <100kb/s however as you can see from the graph it has started around 10am and continued even after a restart of the host OS.
Does anyone have any pointers for where I can look to identify what queries or transactions etc might be causing this anomolous disk IO?


texts:
I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long URLs (text data type), used application names and similar duplicate information.

Is there some PG tool, script for PostgreSQL 9.3+ which can easily for selected columns create dictionaries of distinct values to other tables and after that update original values with SmallInt identificator from that dictionary? Do I have to write SQL for that manually?

TableOriginal
1;VeryLongURLText
2;VeryLongURLText
3;LoooongURLText
4;LoooongURLText
5;LoooongURLText

TableDictionary
1;VeryLongURLText
2;LoooongURLText

TableUpdated
1;1
2;1
3;2
3;2
3;2

Thank you.


texts: Repairing corrupt SQL Server database backup?

texts:
I'm looking at a problem for someone who was backing up to a cloud-based service. Apparently the customer's server crashed while it was performing some maintenance on the cloud-based service's datastore, and this left the datastore in a corrupted state. At the same time, the customer's server was unrecoverable due to the crash.

The cloud-based service's developers had to write a script to find chunks of data that belonged to a database backup and stitch them together into a file.

This resulted in a backup (.bak) file that I can tell has data in it (I can run 'strings' on it and see a header, along with file names, table names, rows of data, etc), but won't be processed by SQL Server. I get:

restore headeronly from disk = 'c:\restore.bak'

Msg 3241, Level 16, State 0, Line 1
The media family on device 'c:\restore.bak' is incorrectly formed. SQL Server cannot
process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Both the source instance on the machine that died and the one I'm testing the restore on are SQL Server 2008 R2, so unfortunately version mismatches aren't the source of the error.

I compared with a valid backup file of another database in a hex editor (xvi32) and noticed that the valid backup file began with the string 'tape' at byte 0, but the corrupt one had some other data with no discernible meaning for 158 bytes before that. I deleted 158 bytes from the corrupt backup up to the string 'tape' and that got me a little farther. Now 'RESTORE FILELISTONLY' worked, whereas before it threw the same error as above.

'RESTORE HEADERONLY' now displays 'incomplete' instead of throwing error 3013. If I trace it in Process Monitor, I can see it tries to read 512 bytes at offset 7168, then tries to read another 512 bytes at an offset past the end of the file. The file is almost certainly incomplete; it was originally 95.8 MB according to a reporting tool in the cloud-based service, but is only 92.95 MB on my filesystem.

I tried forcing a restore on the partially repaired (?) file:

restore database corrupt from disk = 'c:\restore_partiallyrepaired.bak' with continue_after_error, move 'corrupted' to 'c:\corrupt.mdf', move 'corrupted_log' to 'c:\corrupt.ldf'

This made SQL Server crash but gave a clue:

Date        3/3/2012 9:52:06 AM
Log     SQL Server (Archive #1 - 3/3/2012 9:52:00 AM)
Source      spid55
Message
The backup data at the end of "c:\restore_partiallyrepaired.bak" is incorrectly formatted.
Backup sets on the media might be damaged and unusable. To determine the backup sets on
the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run
RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using
BACKUP WITH FORMAT, which destroys all the backup sets.

 RESTORE detected an error on page (48:538976288) in database "corrupt" as read from the
 backup set.

 Hit Fatal Error: Server is terminating

I tried various methods for 'hack-attaching' the 42.5 MB MDF that the restore produced, but it was unrepairable using DBCC CHECKDB.

At this point I'm going to send this back to the cloud-based service's support team with my findings and see if this gives any clues to them about how to extract a non-corrupt file.

I guess my questions are:

1) Is there anything else I can try here?

2) Is there anyone you know of who could help on a billable basis? For example, could MS support or some ex-MS consultants hack this backup file into a usable format?

Thanks!


texts: Most performant design for each member to manage his/her own news list?

texts:
Say a news website has news categories such as Politics, Business, Society, Education, Health, Tech, Celebrity, Lifestyle, ... etc. The number of categories may be more than 20. New categories may be added; existing categories may be deleted.

If a registered member has no desire to see the news of a certain categories, he/she should be able to block those news so that those news don't show up in the news list on the homepage. This will only affect himself/herself, of course. Other members have their own preferences.

Each member can visit the Manage News Categories page to block/unblock news categories. The page will contain a HTML <form> with <input type="checkbox">s of news categories.

What's the most performant database design for such a feature?

I'm currently not an expert of database design. My initial thought is creating an associative table called "member_category_map" and associate it with the "members" table and the "categories" table.

Not sure if I'm being naive, but I'm worrying that the "member_category_map" table might end up containing too much data. Assuming that there are 20 categories and each member only blocks 1 category. If there are 2,000,000 members, then the "member_category_map" would contain 2,000,000 * 19 = 38,000,000 data. I don't know if that's good for performance.

Advices are much appreciated. Thanks.


texts:
I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only 'value1', 'value2' and 'value3'. Default should be 'value1'

According to following syntax diagrams:

I'm getting to this point

ALTER TABLE exampleTable ADD COLUMN new_column VarChar(20) DEFAULT 'value1'

but I'm absolutely not sure how to set the allowed values.

Is it possible to make somethin like

CONSTRAINT CHECK new_column IN ('value1', 'value2', 'value3)

? I must admit the search condition diagram is quite confusing me.


texts: Is there any performance reason to append an unused AUTO_INCREMENT column in MySQL 5.6?

texts:
In my work environment, MySQL has some weird stuff with performance, and one of the other devs wanted me to research this situation before dropping a column that has the AUTO_INCREMENT attribute.

Scenario

We have a table that has a FOREIGN KEY that is used to INDEX into it.  It also currently has a column with the AUTO_INCREMENT attribute that is defined as a KEY, but that KEY is completely unused.

Question

Just wanting to confirm that MySQL is not doing something weird, and if having no column with the AUTO_INCREMENT attribute on a table is going to slow things down.

Consideration

I would like to drop the unused AUTO_INCREMENTed column, and it seems highly likely this action can only speed things up for us.


texts: Postgresql: newly created database does not exist

texts:
Recently I faced strange error. I'm running Postgresql behind pgBouncer. I created new database in pgAdmin (connected through pgBouncer). It shows up on the list, but when I try to select it I get error "Database not exists". When I SSH and psql directly to db, than this DB exists. Also when I connect pgAdmin directly to the postgresql I have no problem.

Here are few details about configuration:

PostgreSQL 9.5 on Ubuntu server 16.04
pgBouncer 1.7 (I connect pgAdmit through it)
pgAdmin 1.4
I login with user name with all privileges (including superuser)

This is my first time when I use pgBouncer, so I suspect there is the problem.


texts:
I have setup a script, created from various links on the web to trace activity in a table using a trigger.

/* Create the MinUser Table */
CREATE TABLE MinUser (
    User_Id             int NOT NULL PRIMARY KEY,
    User_Name           varchar (50) NOT NULL,
    User_Password           varchar (50) NOT NULL,
    User_Email          varchar (50),
    User_Role           varchar (50),
    UNIQUE (User_Name)
) WITH (OIDS=FALSE);

/* Create the Audit Table */
CREATE TABLE MinUser_Audit (
    AuditUser_Id            SERIAL,
        operation               char(1)   NOT NULL,
        stamp                   timestamp NOT NULL,
        userid                  text      NOT NULL,
        ipaddress                   text      NOT NULL,
    User_Id             int NOT NULL,
    User_Name           varchar (50) NOT NULL,
    User_Password           varchar (50) NOT NULL,
    User_Email          varchar (50),
    User_Role           varchar (50)
) WITH (OIDS=FALSE);

/* Function that will add a row to the MinUser_audit table when an insertion/deletion/modification is made to the MinUser table */
CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
    BEGIN
        --
        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('D', now(), user, inet_client_addr(), OLD.User_Id,OLD.User_Name, OLD.User_Email, OLD.User_Password, OLD.User_Role);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('U', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('I', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$usr_audit$ LANGUAGE plpgsql;

/* Create the trigger that will use the MinUser_audit() function */
CREATE TRIGGER MinUser_auditt AFTER INSERT OR UPDATE OR DELETE ON MinUser
    FOR EACH ROW EXECUTE PROCEDURE MinUser_audit();

I understand how to get the user name of the person who inserted/modified/deleted the record using user.  Is there a function I can use to get the ip address as well?  Also, is there a reference on PostgreSQL's website that shows all the different server variables that you can use in a query?


texts: How to correctly create a table that has multiple columns self reference another row?

texts:
EmpNo |Designation     | ReportsTo | Room
10    | Director       | NULL      | 1
20    | Senior Manager | 10        | 2
30    | Typist         | 20        | (THIS GUY REFERS TO empno 10 room)
40    | Programmer     | 30        | 4

How do I correctly make EmpNo 30's room refer to his boss', but not make EmpNo 40 refer to his boss'?


texts:
Context: User foo has access to both the server its running on and the server its targeting with the package.

  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005
  Description: "The metadata could not be determined because every code
  path results in an error; see previous errors for some of these.".  An
  OLE DB record is available.   Source: "Microsoft SQL Server Native
  Client 11.0"  Hresult: 0x80004005  Description: "Login failed for user
  "foo"

I have verified the password and I also have it set to DoNotSaveSensitive for the Protection Level.  I am referencing an AD service account, which I am running it under.  I have pulled it into Visual Studio and get the same result.

Interestingly enough I have two packages within the same project and one runs while the other will not.


texts: What is the equivalent of MS Access InStr function in SQL Server 2008?

texts:
This query runs in Access 2013 but I need it converted to SQL Server 2008. The issue is that the INSTR() function does not exist in SQL Server and the query references the field generated in same query EXPR1.

How would this be changed over to SQL Server 2008?

SELECT PartID, Description, Service,
InStr(1,[Description]," ") AS Expr1,
Left([Description],[Expr1]) AS part
FROM TI
WHERE (((PartID) Is Not Null) AND ((Service)="JTP"));


texts: Where to start to understand a unknown database

texts:
So, the title sums it up.

I've got a SQL Server database with 28 tables and 86 stored procedures that must be reverse engineered. I'm pretty sure that some tables are never used and that not all procs are used as well.

The biggest problem is that all the Windows Services that were created to be used with this DB and all the software and database documentation is lost, and the person that designed the whole system is nowhere to be found.

I've already managed to create an ER diagram to help me understand the relationships, but as I am not experienced in database administration, I have no idea where should I start.

Also I'm sorry if this kind of question is not meant to be asked here.


texts: SQL Server Grant execute on all stored procedures without ddl permissions

texts:
I have some users in my database. They are member of db_datareader. They now also need to have execute permissions on stored procedures. I was just wondering what would happen if one of the stored procedures issue DDL? Would they be able to make schema changes to tables, drop etc. through that stored procedure?

Thank you


texts: MySQL create table: error 1005 errno: 150 "Foreign key constraint is incorrectly formed"

texts:
This fails when trying to create the EXCHANGE table:

CREATE TABLE BOOK
(PKACC_NO VARCHAR (20) PRIMARY KEY,
TITLE VARCHAR (50),
AUTHOR VARCHAR (50),
PUBLISHER VARCHAR (50),
EDITION INTEGER (4),
PRICE INTEGER (10));

CREATE TABLE MEMBER
(PKMEMBER_ID VARCHAR (20) PRIMARY KEY,
NAME VARCHAR (50),
TYPE VARCHAR (10),
CONTACT_NO BIGINT (10),
ADHAAR_NO BIGINT(12),
LAST_VISIT DATE );

CREATE TABLE STAFF
(PKSTAFF_ID VARCHAR (20),
NAME VARCHAR(50),
ADHAAR_NO BIGINT (12),
CONTACT_NO BIGINT(10) );

CREATE TABLE EXCHANGE
(TRANSACTION_ID VARCHAR(20) PRIMARY KEY,
FK1ACC_NO VARCHAR(20),
FK2MEMBER_ID VARCHAR (20),
FK3STAFF_ID VARCHAR(20),
DATE_OF_ISSUE DATE,
DATE_OF_RETURN DATE,
FINE INTEGER(20),
REMARKS VARCHAR(20),
CONSTRAINT FK1 FOREIGN KEY (FK1ACC_NO) REFERENCES BOOK (PKACC_NO)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK2 FOREIGN KEY (FK2MEMBER_ID) REFERENCES MEMBER (PKMEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK3 FOREIGN KEY (FK3STAFF_ID) REFERENCES STAFF (PKSTAFF_ID)
ON DELETE CASCADE
ON UPDATE CASCADE);

The last create statement returns:

ERROR 1005 (HY000): Can't create table `testdb`.`EXCHANGE` (errno: 150 "Foreign key constraint is incorrectly formed")


texts: Naming convention for automated table outputs (processed with Python)

texts:
I have several tables that I process nightly with Python.

For example, I have read-only access to an incidents table. I process the table with a specialized Python library called ArcPy. The script has a number of steps, including calculating the Minimum Bounding Geometry (a.k.a. convex hull) of like-incidents. The script processes the incidents table nightly and outputs the results to a new table.

I would like to follow database conventions and industry practices when outputting these tables, where possible. Having a distinct naming convention would allow me to easily identify master tables from output tables.

Is there a naming convention for automated table outputs (processed with python, or otherwise) that I should be using?


texts:
I have run the Database Tuning Advisor and it has suggested that some statistics are created.  Whilst this is great and I understand the purpose of statistics, when I have reviewed them it has created 5 or more with very similar columns included in them.  If I went ahead and created these statistics, if a column is replicated in more than one of these statistics and the first statistic is updated, how does this update affect the other statistics?

Thanks,


texts: MSSQL Geography STIntersects returns 1 when 0 is expected

texts:
I'm experiencing an MSSQL Geography issue I can't explain.  Assume that I'm math-challenged, but it seems like the following point should NOT intersect the following polygon:

DECLARE @t_point geography;
DECLARE @t_polygon geography;
set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 5, 5 0, 1 1))', 4326);
set @t_point = geography::Point(0, 0, 4326);

select
  @t_polygon.STAsText(),
  @t_point.STAsText(),
  @t_point.STIntersects(@t_polygon),
  @t_polygon.STIntersects(@t_point);

When I execute this, though, I get the following result:

POLYGON ((1 1, 5 5, 5 0, 1 1))  POINT (0 0) 1   1

The last two columns are both 1 where I would expect 0.  How can a point in space at 0,0 intersect with a polygon that does not touch or encompass 0,0?

-1, -1 has the same behavior, as does 100, 100.

Even if I change the point to a polygon in a completely different space, MSSQL says that they intersect.

set @t_polygon = geography::STPolyFromText('POLYGON((10 10, 50 50, 50 10, 10 10))', 4326);
set @t_point = geography::STPolyFromText('POLYGON((1 1, 5 5, 5 0, 1 1))', 4326);

I also tried STGeomFromText().

I'm sure I must be doing something wrong, but I can't imagine what that might be.


texts: Why is this seemingly simple insert trigger function causing deadlocks?

texts:
This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens.

The situation...
I have a tweet table and one of the columns receives an array[]::text[] of urls.
There is a trigger function on the table that inserts the urls into a url_starting table.

The url_starting table looks like this.

CREATE TABLE public.url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

And tweet table the trigger looks like this.

CREATE OR REPLACE FUNCTION public.create_tweet_relationships()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO url_starting (url)
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING;

    RETURN NULL;
END
$function$;

Sometimes I get deadlock errors like this.

deadlock detected
DETAIL:  Process 4540 waits for ShareLock on transaction 4709; blocked by process 4531.
Process 4531 waits for ShareLock on transaction 4710; blocked by process 4540.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (2314,101) in relation "url_starting"
SQL statement "INSERT INTO url_starting (url)
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

Shots in the dark... ♂

Could this be caused by UNNEST? Am I doing something wrong in the syntax?

Why does the error say in relation "url_starting" when there is no relationship in the table?

There are thousands of tweets committing to the database simultaneously. It shouldn't matter if set up correctly, should it?


texts: Cannot attach the AdventureWorks database

texts:
I am trying to attach the AdventureWorks database with the following query:

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

But I get the following error:

  Create file encountered operating system error 5 (access is denied.) while attempting to open the physical file blah

Note: I am using Windows auth mode.

Any pointers as to what to do?


texts: How to join sparse events for individual users on a "filled in" date ranges in Redshift?

texts:
Let's say I have a table that has a record of every change a customer has made to their subscription for an internet service:

+-----+------------+-----------+------------+
| _id | account_id | plan_name | created_at |
+-----+------------+-----------+------------+
|   1 | a          | basic     | 10-01-19   |
|   2 | b          | standard  | 10-15-19   |
|   3 | c          | free      | 10-16-19   |
|   4 | a          | standard  | 10-16-19   |
|   5 | b          | free      | 10-20-19   |
+-----+------------+-----------+------------+

I'd like to perform an analysis on this data so that I can see the count of plans for every day, from 10-01-19 through 10-20-19.

To perform this analysis (among other things) I'd like to create a query that will give me a table with a row for every date for every user from their first recorded plan_name through present day (let's say this is 10-20-19).  It might look something like this:

+-----+----------+------+-----------+
| _id |   date   | user | plan_name |
+-----+----------+------+-----------+
|   1 | 10-1-19  | a    | basic     |
|   2 | 10-2-19  | a    | basic     |
|   3 | 10-3-19  | a    | basic     |
|   4 | ...      | ...  | ...       |
|   5 | 10-15-19 | a    | standard  |
|   6 | 10-16-19 | a    | standard  |
|   7 | 10-17-19 | a    | standard  |
|   8 | 10-15-19 | b    | standard  |
|   9 | 10-16-19 | b    | standard  |
|  10 | 10-17-19 | b    | standard  |
|  11 | ...      | ...  | ...       |
|  12 | 10-20-19 | b    | free      |
+-----+----------+------+-----------+

My first test attempt at this was the following:

WITH dates AS (
        SELECT (GETDATE()::DATE - ROW_NUMBER() OVER (ORDER BY TRUE))::date AS date
      FROM pg_catalog.pg_operator
      LIMIT 500)

SELECT date,
     (case when account_id is not null then account_id else lag(account_id ignore nulls) over (order by date) end) as account_id,
     (case when plan_name is not null then plan_name else lag(plan_name ignore nulls) over (order by date) end) as plan_name,
     (case when start_date is not null then start_date else lag(start_date ignore nulls) over (order by date) end) as start_date
 FROM dates
LEFT JOIN (
    select * from customer_subscriptions
    where account_id = '98asd7gf98a7') AS a
    on dates.date = trunc(a.start_date)
ORDER BY date DESC

With the caveat that I tried it on a single account_id from my subscriptions table -- and it worked!  My thought process was generate a date range and LEFT JOIN the subscriptions table on it at the "change over" dates and then use a lag function to fill in the NULLs

However, when i remove the where account_id = '98asd7gf98a7' it no longer works and I am left with a table that simply is a joining the start dates to a date from the range, without any of the lag filler in between.

Any thoughts on another way to approach this or changes I can make to my current approach?


texts:
I'm trying to estimate the size of a tablespace using these steps: Space Estimations for Schema Objects

and I was little confused about some of the steps. In step 3 for calculating the rowsize I first need to calculate the column size and then the sum of all column sizes in the table, I was wondering how I get the column size value without having actually created the table yet do I have to create sample data and then use vsize()? or am I missunderstanding what the column size is..

Also these steps in the link are for estimating the size of a table (?) so if I wanted to estimate the size of the tablespace would I just take the sum of all the estimated table sizes?


texts: Real-time deduplication strategy for relational DB

texts:
I have a messages table with some fields like id, version, timestamp

I'm trying to insert and retrieve messages with real-time deduplication logic, implemented in stored procedures.

On insert, if a message with given id already exists in the table, I invoke a complex deduplication logic (a number of IF/ELSE statements) and then update messages's version, timestamp, etc. Otherwise I insert a new row.
On retrieve, I search for the oldest message and not just select it, but also update a few fields (changing state, setting transient_token, etc)

The problem is, it does not work as fast as I need: ~ 200 rps on insert and the same on retrieve

Separately my stored procedures work good, but when I execute both of them concurrently, they start fighting for locks under load > 30 rps, performance dramatically decreases and deadlocks appear periodically

Are there some best practices to solve my problem? I feel that the whole approach sucks, not my concrete implementation.

May be I should use some temp buffer table for all incoming messages and perform dedup logic afterwords? I would appreciate any ideas.

* I can't use nosql DB in my case (management decision), I need to gain max performance with Mysql/MariaDb


texts:
I have a table of items pairs, which links one item with another. I'd like to divide the items into groups, so that any items which are linked together, even indirectly, are in the same group. So:

person1| person2
-------+--------
john   | george
george | paul
george | ringo
ringo  | paul
brian  | mick
brian  | keith
keith  | bill
mick   | charlie

For the purposes of this, assume the table schema is just:

create table pairs
  ( person1 varchar not null,
    person2 varchar not null,
    primary key (item1, item2)
  ) ;
create table p2g (person varchar not null, groupid int);
create table group (groupid integer primary key)

It's obviously more complex than that.

So john is linked to george, and george is linked to paul and ringo, and ringo is linked to paul; these are a self-contained group. (Note that the ringo<->paul link is actually redundant here; we don't need it to make the group, but it's present anyway.) Similarly, brian/mick/keith/bill/charlie are a group. People are unique (we're using varchar names here to make the explanation clearer, but people are actually identified by a unique ID). So what I want is some way of pulling all these together, so I'm able to create output that looks like

person  | group
--------+------
john    | 1
george  | 1
paul    | 1
ringo   | 1
brian   | 2
charlie | 2
keith   | 2
mick    | 2
bill    | 2

It is obviously possible to walk through the pairs table line-by-line (and that's what I'm doing), but this is pretty slow with a lot of pairs. The algorithm I'm using is this:

 for person1, person2 in pairs:
    group1 = db.exec("select groupid from p2g where person=:person1")
    group2 = db.exec("select groupid from p2g where person=:person2")
    if not group1 and not group2:
        # invent a new group; add person1 and person2 to it
        newgroupid = db.exec("insert into group (null)")
        db.exec("insert into p2g (:newgroupid, :person1)")
        db.exec("insert into p2g (:newgroupid, :person2)")
    else if not group1:
        # add person1 to group2
        db.exec("insert into p2g (:group2, :person1)")
    else if not group2:
        # add person2 to group1
        db.exec("insert into p2g (:group1, :person2)")
    else if group1 == group2:
        nothing to do here, everything's already OK
    else:
        # they're in two different groups.
        # Put everyone in group2 into group1
        # and delete group2
        db.exec("update p2g set groupid=:group1 where groupid=:group2")
        db.exec("delete from group where groupid=:group2")

but I feel like there's a more SQLish way to do this in batches or all in one go, rather than once per row. Is there? (I'm using SQLite, if that makes a difference.)

I've tried using recursive CTEs for this, which sorta works but sorta doesn't. This single query:

with bands (member, leader) as (
    select item1, item1 from pairs
    union all
    select p.item2, b.leader
        from pairs p
        inner join bands b on b.member=p.item1
) select distinct 'group:'||leader, member from bands order by leader

gives as output

group:john  | john
group:john  | george
group:john  | paul
group:john  | ringo
group:mick  | mick
group:mick  | charlie
group:mick  | keith
group:paul  | paul
group:paul  | ringo
group:paul  | george
group:ringo | ringo
group:ringo | george

which is almost right: the john and mick groups are correct! But it also contains other "groups", started from other members, and I don't know how to not include those. This seems like the right approach, but it isn't quite there. (Additionally, if the pairs table also contains "ringo -> john", i.e., the chain of people is circular, then the above query runs forever, which is also not good.)


texts:
This whole stored procedure is to generate a csv file into the mention folder where file is getting generated but the headers are missing.
--Adding missing headers into the file----
DECLARE @columnHeader VARCHAR(8000)
SELECT @columnHeader =

'''RecordID'',''MSISDN'',''Amount'',''ReferenceID''
 ,''SessionID'',''Action'',''RequestDate'',''ResponseDate''
,''StatusID'',''Channel'',''CallBackRequestDate'',''CallBackResponseDate''
,''CallBackReferenceID'''

  Select @cmd = 'BCP '+'"Select '+ @columnHeader +' UNION ALL SELECT convert(varchar(20),
  [RecordID]),convert(varchar(20),[MSISDN]),convert(varchar(20),[Amount]),convert(varchar(20),
  [ReferenceID]),convert(varchar(20),[SessionID]),convert(varchar(20),
  [Action]),RequestDate,ResponseDate,convert(varchar(20),[StatusID]),convert(varchar(20),
  [Channel]),CallBackRequestDate,CallBackResponseDate,CallBackReferenceID From [LinkVisaCard].[dbo].
  [Transaction]"'+ ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerName
  PRINT @cmd
  EXEC master..xp_cmdshell @cmd;

So I tried to use above query inorder to add missing headers where i am getting below error:
error:
SQLState = 22008, NativeError = 241
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date
and/or time from character string.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to resolve column level collations
please do the needfull
   ALTER PROCEDURE [dbo].[GenerateCDR]
   AS
   Begin
   DECLARE @sqlCommand varchar(8000)
   DECLARE @filePath VARCHAR(125)
   DECLARE @fileName VARCHAR(125)
   DECLARE @todaysDateFormatted VARCHAR(30)
   DECLARE @ServerName varchar(50)

   -- generate the dynamic file name based on the date and time
   SELECT @todaysDateFormatted = CONVERT(VARCHAR, GETDATE(), 112) + '_' + CAST(DATEPART(HOUR, GETDATE())
   AS VARCHAR) + '_' + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_' + CAST(DATEPART(SECOND,
   GETDATE()) AS VARCHAR)
   -- ALTER the file name
   SET  @fileName ='Tansactions_'+  @todaysDateFormatted + '.csv';

   SET @ServerName = 'TRUSTACCTEST';

   DECLARE @cmd varchar(8000)

   -----Copy the table records into TransactionsData folder with .csv file----
   Select @cmd = 'BCP '+
   '"Select * From [LinkVisaCard].[dbo].[Transaction]"'
    + ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerName
   PRINT @cmd
   EXEC master..xp_cmdshell @cmd;
   PRINT @cmd

   ------Adding missing headers into the file----------
   DECLARE @columnHeader VARCHAR(8000)
   SELECT @columnHeader =
 '''RecordID'',''MSISDN'',''Amount'',''ReferenceID'',''SessionID'',''Action'',''RequestDate'',''ResponseD

 ate'',''StatusID'',''Channel'',''CallBackRequestDate'',''CallBackResponseDate'',''CallBackReferenceID'''

      Select @cmd = 'BCP '+'"Select '+ @columnHeader +' UNION ALL SELECT convert(varchar(20),
      [RecordID]),convert(varchar(20),[MSISDN]),convert(varchar(20),[Amount]),convert(varchar(20),
      [ReferenceID]),convert(varchar(20),[SessionID]),convert(varchar(20),
      [Action]),RequestDate,ResponseDate,convert(varchar(20),[StatusID]),convert(varchar(20),
      [Channel]),CallBackRequestDate,CallBackResponseDate,CallBackReferenceID From [LinkVisaCard].[dbo].
      [Transaction]"'+ ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerName
      PRINT @cmd
      EXEC master..xp_cmdshell @cmd;
      END


texts: Use Powershell to query SSAS connection string

texts:
I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report.

I've found that Powershell allows me to drill into SSAS like a directory and I can see what I'm after, but don't know how to query it.

in Powershell I can drill down to here:

PS SQLSERVER:\SQLAS\LAP123\Default\databases\EnvironmentalAnalysisService\Datasources>

And I can see the details I want:

Name                      Isolation     Max Connections Connection String
----                      ---------     --------------- -----------------
Environmental Data Mart   ReadCommitted              10 Provider=SQLNCLI11.1;Data
                                                        Source=LAP123;Integrated
                                                        Security=SSPI;Initial
                                                        Catalog=EnvironmentalDataMart

In this case Server; LAP123 & Database: EnvironmentalDataMart

How can I get the items I want from Powershell like a query?


texts: How to solve "Can't connect to local MySQL server through socket" error?

texts:
I kept getting this following error every time I tried to restart MySql. It was working fine before I changed the default data directory.

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib                                                                                /mysql/mysql.sock' (111)

I know there are other solutions posted by others but none of them worked for me.


texts: would it be possible in postgres to get a count of sql hits on a select statement

texts:
Say I have the following table in Postgres 9.4:

items
id |  name             |   tag_ids
1     tire                  -1--2-
2     wheel                 -1--3-
3     transmisson           -3-

I'd like to do something like:

select id, tags from items where tag_ids ilike '%-1-%' or tags ilike '%-3-%';

but if it hit twice (like id 2), return a count of hits. Would this be possible? Or possibly have it come back
as two records and then process in application code.

edit

ok, so assuming we had a second table like this:

items_tags
item_id  tag_id
1         1
1         2
2         1
2         3
3         3

Is there a way to join them and then group by the number of "hits" - I guess just selected rows and then order in reverse? And give a represntation of the hit tag_ids

select i.name from items inner join items_tags using (id) where items_tags.tag_id in (1,3);

but how would I do a count and order. I'm assuming a group by but this is beyond me.

edit 2

so I can't get this second query to work. It seems like it should but isn't:

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int as hits
from items order by  hits desc;

saying ERROR:  column "hits" does not exist

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int
as hits from items where hits > 0 order by  hits desc;


texts: Can a witness server be a witness for more than one pair of mirrored servers?

texts:
In the interest of saving time and money (licenses).

For example:

We have a mirrored SharePoint backend as well as a mirrored Lync SQL backend.

I want to use the witness for the Lync servers to be the witness for the SharePoint mirrored pair at the same time.


texts: Best way to model the relationship of unique pairs

texts:
I have two tables; one for storing thing and one for storing the relationship between two thing objects.

dbfiddle example

Assume:

AB == BA.  Storing both would be redundant
A != B.  The relationship of one thing to itself is not useful
Calculating the relationship between AB is expensive but idempotent

CREATE TABLE thing (
    id INT PRIMARY KEY
);
CREATE TABLE relationships (
    thing_one INT REFERENCES thing(id),
    thing_two INT REFERENCES thing(id),
    relationship INT NOT NULL,
    PRIMARY KEY (thing_one, thing_two),
    CHECK (thing_one != thing_two)
);

In order to ensure we don't INSERT AB and BA:

CREATE UNIQUE INDEX unique_pair_ix
    ON relationships (
        least(thing_one, thing_two),
        greatest(thing_one, thing_two)
    );

Is there a better or more efficient way to store/model this data than the example?

EDIT: There are multiple DBMS being considered for the greater application.  They include PostgreSQL, MariaDB, and MySQL.  PostgreSQL is the current preference.


texts: How to handle/separate big database with blobs

texts:
We have a specialized business software (transportation) running on SQL Server.
The software company decided to store all sorts of files (invoices, scans, pdfs, emails, pictures, ...) that have to be retrieved as blobs in the database. Most of the data becomes readonly after e.g. a year or so, so for general performance and manageability reasons I forced them to switch for all new files to filestreams.

But still our databases are filled with 90% ancient data. We will migrate the server to new instances (from sql/win 2008R2 to sql/win 2016). For easier management and (worst-case), faster restore times, I would like to create some kind of primary server, that holds all the data, that is not older than a year, and a secondary server, that holds all the old stuff.
The application itself is designed to use many databases. If a database is full, they set it to readonly, and create a new one (manually, through support. no hard limits anywhere). Can I (ab)use this behavior to link the old databases to another (the secondary, archive) SQL server with regular SQL Server capabilities?

Another way could be to strip all the old blobs out and save them into a remote files system. Is this possible? If one could do this, the archive server could be windows server with a data partition and a share...

I hope I was not too confusing :)


texts:
I've noted that key-value values may be unquoted when numeric in TSQL JSON strings, but it seems the key component must always be quoted.

select 1, isjson(''), 'empty string' union
select 2, isjson('{}'), 'empty braces' union
select 3, isjson('{1:2}'), 'unquoted both, numerals both' union
select 4, isjson('{1:"2"}'), 'unquoted key, numerals both' union
select 5, isjson('{"1":2}'), 'unquoted value, numerals both' union
select 6, isjson('{"1":"2"}'), 'quoted both, numerals both' union
select 7, isjson('{a:b}'), 'unquoted both, alpha both' union
select 8, isjson('{a:"b"}'), 'unquoted key, alpha both' union
select 9, isjson('{"a":b}'), 'unquoted value, alpha both' union
select 10, isjson('{"a":"b"}'), 'quoted both, alpha both'
order by 1
;

Results:

1   0   empty string
2   1   empty braces
3   0   unquoted both, numerals both
4   0   unquoted key, numerals both
5   1   unquoted value, numerals both
6   1   quoted both, numerals both
7   0   unquoted both, alpha both
8   0   unquoted key, alpha both
9   0   unquoted value, alpha both
10  1   quoted both, alpha both

The above demonstrates this but my questions are:

Must this always be so? (Is there a configuration that can override this behaviour?)
Is this behaviour specified by JSON or SQL Server?
What is the rationale behind this design decision?
Would there be a performance benefit if SQL Server automatically cast unquoted numerals as integers?


texts:
We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here.

The problem is that we are having issues with the SQL Server slowing down after a few days which causes us to restart the service. I did some stats with the SQL Profiler but after review I didn't see anything that stuck out. This is why I am here now. I looked around online and came across ASYNC_NETWORK_IO. So, I am trying to follow this track now.

This is what I've done:

I cleared out the wait stats table.

DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR')

Results for the last 15 minutes:

ASYNC_NETWORK_IO    46798     34953     812     1953
CMEMTHREAD          28300     42328     1187    6437
CXPACKET            361571    7048515   38984   48859

From what I've read ASYNC_NETWORK_IO has to do with SQL waiting for the client application.

2 questions:

1) Is my assumption correct that these numbers seem high for 15 minutes of monitoring?

2) Where do I go from here? What should be my next step?

I'm trying to learn how this stuff works and I understand this is something I'm going to have to keep digging in to.

A push in the right direction would be great.


texts: MySQL - Set trigger BEFORE INSERT for new ID is safe?

texts:
How to create customize primary key in MySQL?, example i have table and the table name is X, I have a table field as ID,Code,Name.

  I am afraid if I have 1000 users and when they input together will
  result in destruction

and i want to :

INSERT INTO `X` (`ID`,`Code`,`Name`) VALUES
('P3K','Alex'), // this primary key is "P3K-1"
('SOS','Force'), // this primary key is "SOS-1"
('P3K','Bash'), // this primary key is "P3K-2"

Right now, i using TRIGGER (BEFORE INSERT) for this, like this one:

SET NEW.`ID` = CONCAT(NEW.`Code`,'-',IFNULL(SUBSTRING_INDEX((
  SELECT `x`.`Code` FROM `X` WHERE
    X.`Code` = NEW.`Code` and
ORDER BY X.`Code` DESC
LIMIT 1 ),'-',-1),0) + 1))

I did not try this code, but my point is:

User insert
Before insert I checking LAST Primary
IF Null then i set 0, else i cut the symbol (-) and take the last part
I increments (using [+ 1])
Final, i concat CODE and New Number.

am i misguided? LOL, and if true, how to create like this one?
(I THINK) We can do it and maybe no one knows about this, how does AI in MySQL work so perfectly?


texts: How to grant access for multiple tables to a user in MySQL DB?

texts:
I have mysql db named "greats"  with four tables as below

+---------------------+
| Tables_in_greatstat |
+---------------------+
| log                 |
| sitedet1            |
| siteindex           |
| user                |
| whois1              |
+---------------------+

Here i have decided to create an mysql user "test" and i want to give the access only for specific table of the db "greatstat".
So, May i know how to grant specific table perm for an user in MYSQL ?


texts:
Running an sql script in SQLplus on Solaris
Any idea why script hangs in line 9 '/' and not executed?

declare
 dummy number;
begin

 select 1
 into dummy from dual ;

end;
/


texts: As I was reading article I came across IBM's site about object Server. What actually is Object Server and what are it's uses? Can this be explained for a beginner?


texts:
I need your help on some replication matter I guess.
I'm actually not a dba, but I need to do some work on my db:
I have a remote db, with table1, table2 and a local db with table3 and table4 in local db.
table1 and table3 have the same column names and number of columns, but table4 has an id column in addition to table 2 columns.

my objective is to keep data synchronized in both dbs which means have same data on table 1 and table 3, and same common columns data on table 2 and 4.

what I did so far is to create a dblink between local and remote db and merge scripts, but I need to do it manually everytime there are changes on remote db, is there any better way, I thought of a trigger but don't know where to create it and how it will detect changes on remote db to replicate them in local.
I appreciate any help or advice even the RTFM ones :D


texts: What PUBLIC access means on tables in Oracle?

texts:
I recieved a communication from my DBA that they want to revoke PUBLIC access and they shared attached screenshot. What does this mean by PUBLIC access on these tables? And what happen if they "Revoke" the access.

I don't know the query which result is this. Can anyone help to understand this resultset. I want to understand the impact on my application if they revoke this access.


texts: LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

texts:
I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE.

But we have 1GB each day we want to update in a batch process using LOAD DATA INFILE. Unfortunately LOAD DATA INFILE blocks users from querying against that table.

Is there a way I can get around this?

For example:

Can I load the new data into another table and insert into from that table or something similar to allow loading the data and read queries against the primary table at the same time?
Can I do some kind of partitioning with new tables added daily, joined by a view? Would this be a bad idea with such a large table?


texts: Help me understand sp_server_diagnostics

texts:
i have a SQl 2016 AlwaysOn cluster that has almost nightly issues. I've narrowed it down to it going down due to errors reported in sp_server_diagnostics.

This is a warning right before the cluster failed last nigh before there are health check timeouts and other messages saying always on is unhealthy

<system spinlockBackoffs="0" sickSpinlockType="none"
 sickSpinlockTypeAfterAv="none" latchWarnings="2"
 isAccessViolationOccurred="0" writeAccessViolationCount="0"
 totalDumpRequests="2" intervalDumpRequests="0" nonYieldingTasksReported="0"
 pageFaults="1882" systemCpuUtilization="35" sqlCpuUtilization="7"
 BadPagesDetected="0" BadPagesFixed="0" LastBadPageAddress="0x0"/>

Is there a specific entry I should look at first?


texts: Installing MySQL server 4.1 on Ubuntu 11.04

texts:
how do I need to install mySQL server 4.1 on Ubuntu 11.04 server for a legacy app to run (it breaks on mySQL 5).

sudo apt-get mysql-server-4.1

doesn't work - "package is missing, obsoleted or available from another source".

Background:

I'm trying to migrate from a Windows Server to Ubuntu Server. I have a MS Visual Foxpro app connecting to mySQL server 4.1 on Windows.

Just trying to replicate that on Ubuntu for now.

So far, I've got it to the point where the app is already up and running from a remote machine (on mySQL 5.1). However, I have tested and verified that some functions don't work well and it is because of the difference in mySQL versions.

Invariably, someone would suggest that I tinker with the code. I can't do that yet as I didn't develop the app and am still negotiating for the source code.

In the meantime, my goal is just to get this thing to run in Ubuntu with as little hassle as possible and that requires mySQL server 4.1.

Thanks.


texts: access columns returned by psql from bash

texts:
I want to use value returned by psql in bash. This is the script:

psql "connection parameters" -c "SELECT pg_database_size('dbname');"

The output is like this:

 pg_database_size
------------------
          5773072
(1 row)

But I only want the 5773072 so I can use it in logging. Can anyone help?


texts: MariaDB: granting access to a DB before creating the DB

texts:
I've just discovered that I can create a user and grant right to it on a given database even if that DB doesn't exist yet (it will be created in a second step).

MariaDB [(none)]> CREATE USER foo2@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE ON  DBNotCreatedYet.* TO 'foo2'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Do you think this procedure can cause troubles and it'd be better to create the DB and THEN granting rights to a user on it? I'd prefer not to put a constraint about what operation to do first but I don't want to run into troubles.


texts:
In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those columns.

The table will ultimately have as many as hundreds of millions of rows, so it was pointed out that an auto-increment PK might be better and my multi-column PK would be less performant. Inserts can be several per second.

E.g. please ignore questions about columns, this is just an example.

CREATE TABLE records(
  record_code VARCHAR(20) NOT NULL,
  record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user VARCHAR(30) NOT NULL,
  other_data VARCHAR(100),
  other_data VARCHAR(100),
  PRIMARY KEY (record_code, record_date, user)
) ENGINE=InnoDB;

Or:

CREATE TABLE records(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  record_code VARCHAR(20) NOT NULL,
  record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user VARCHAR(30) NOT NULL,
  other_data VARCHAR(100),
  other_data VARCHAR(100),
  PRIMARY KEY (id),
  KEY record_code (record_code)
) ENGINE=InnoDB;

It made sense to me that an AUTO_INCREMENT surrogate PK would save disk/memory space and allow faster inserts, but another commenter disagreed, so I am wondering if it's OK to use the multi-column PK, is it just as fast? Is there not much difference?

NOTE it is possible at some point that may need to start doing lookups by both the first AND second columns, so in the second example above the second index would be KEY record_code_date (record_code, record_date) so inserts are doing almost as much work as with just using the multi-column PK(?). Does this change the advisable approach?


texts: Set or limit number of connections a single pymongo client will open

texts: Is there a way to set or limit the number of connections to Mongo a single pymongo client instance will open? I don't want to modify Mongo config to do this, because I don't want to affect other applications.


texts: Setting up a listener for sql server alwayson availability groups without computer object rights in the AD

texts:
Is there a way to create a sql server alwayson listener in an active directory (AD) environment where I don’t have create computer object rights? The AD admin created a computername for me. Is there a way to connect it to the Listener?
The following TSQL fails because I don’t have create objects rights in the AD.

USE [master]
GO
ALTER AVAILABILITY GROUP [VG-AdventureWorks2014]
ADD LISTENER N'DNSCOMPUTERNAME' (
WITH IP
((N'XXX.XXX.XXX.XXX', N'255.XXX.XXX.XX')
)
, PORT=1431);
GO

Company policy does not allow AD rights for the DBA.


texts: Can independent applications cause roll back to each other's transactions?

texts:
I have a database built on SQL Server

Application-1 [Legacy App]:

Written in C#, and runs transactions on the above database.

Application-2 [New application being developed]:

Written in Python, and runs transactions on the above database.

Considerations:

These apps access the same set of tables and perform similar actions.
We are trying to transition from older app to the newer one over time.
I am finding several deadlocks and rollbacks happening in the system.

Questions

I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application.
Are these scenarios handled by the DBMS itself internally or have to be handled at the application level ?


texts:
Where can I find out what the different "sql_severity" codes mean in the sysjobhistory table?

Eg what does 0 mean?

I've inherited a report that excludes ones with a 0 as "0 is not a real error"?

I googled and looked at MSDN etc etc.. but no real luck.


texts: Snapshot on Always On Availability Group in suspect mode

texts:
Our configuration is 2 SQL Servers 2016, enterprise edition, 13.0.5026.0, running on Windows Server 2012 R2.
Created availability group with one database in it.
Database has 3 data files in it, all together around 2,5 TB in size
We are creating 2xsnapshots on the database on secondary replica.

After some time our snapshots are turning into suspect state with following errors recorded in the error log:

Error: 3420, Severity: 21, State: 1.
G:\Snapshots01\Data\Data1.ss: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
Error: 17053, Severity: 16, State: 1.
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x0000a8c4c60000 in file 'G:\Snapshots01\Data\Data1.ss'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I've checked snapshot file sizes with following query:

SELECT  DB_NAME(sd.source_database_id) AS [SourceDatabase],sd.name AS [Snapshot],
mf.name AS [Filename],size_on_disk_bytes/1024 AS [size_on_disk (KB)],mf2.size/128 AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd ON mf.database_id = sd.database_id
JOIN sys.master_files mf2 ON sd.source_database_id = mf2.database_id AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1AND mf2.is_sparse = 0

and it seems that snapshot is broken when the largest snapshot file reaches around 60GB.
Snapsot drive is 1,35 TB, formatted with NTFS 64KB.

What can we do to avoid errors like this?
Will changing to ReFS help or perhaps are there any other NTFS settings that can be ammended?


texts: Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE

texts:
Am running below query

Query

Alter table dbname.tablename
add column column1 varchar(50),
add column column2 text ,
ALGORITHM=INPLACE, LOCK=NONE;

It takes more time to execute.

Ideally running this without ALGORITHM=INPLACE, LOCK=NONE takes 30mins to execute.

Query am running for table size=24.4G and records-count=97lkahs.

Any suggestions on why does it take more time to execute?

Is there any way that i could achieve better on the same?

Version= 5.6.40
, Engine = Innodb
, Buffer pool size = 44.8 GB