texts: Select rows with multiple AND conditions that override eachother

texts:
I have the following table schema:

book_id    |   attribute_id    |    attribute_value
____________________________________________________

1          |        35         |         Sci-Fi
2          |        35         |         Drama
3          |        35         |         Thriller
4          |        35         |         Romance
1          |        36         |         Author1
2          |        36         |         Author2
3          |        36         |         Author3
4          |        36         |         Author4
1          |        37         |         $300
2          |        37         |         $100
3          |        37         |         $600
4          |        37         |         $700

I tried to fetch results that have multiple conditions like in the query below from the table 'books'

SELECT book_id FROM books WHERE attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') AND attribute_id = 36 AND attribute_value IN ('Author2')

I know this is not working because I cannot have multiple AND conditions on the same column. I was looking for the following output of the query above

book_id  |
-------- |
   2     |

I tried using UNION and separating the query in 2 different queries:

SELECT book_id FROM books WHERE attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller')
UNION
SELECT book_id FROM books WHERE attribute_id = 36 AND attribute_value IN ('Author2')

But obviously this is returning the same results as using the query below (replacing AND with OR

SELECT book_id FROM books WHERE (attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller')) OR (attribute_id = 36 AND attribute_value IN ('Author2'))

I tried INNER JOINING the table on those conditions and I did not had any success. Not sure what I am missing. I am looking for a solution that would work with more attributes not only 2 (35,36). Any ideas?


texts: Sync data between 2 databases Oracle 10g

texts:
I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me.

I want to copy a table from DB_LIVE to DB_NEW and later keep syncing them. Another caveat is I only have PL/SQL developer and no SQL*Plus. Can someone please provide me pointers to it.


texts: Sync/Replication of 2 Oracle Databases with NetApp storage

texts:
I am working on a high availability setup of Oracle Enterprise Manager.  I need the back-end repository replicated (preferably in real time, or something close) to another repository, in which I could stand up a standby Oracle Enterprise Manager to point at it.  The idea is that if my primary setup goes down, I could switch to the standby without losing any monitoring or historical data.

The Oracle Databases (11.2.0.3) use NetApp storage devices.  The primary is configured as a RAC, but the standby would be a single instance.  What is the best way to accomplish this?  I would like to setup replication from the primary to the secondary in as close to real time as possible.  Also, the databases are about 500 miles away from each other...if that has any effect.  I am new to the DBA scenario, as I was given a task that I do not have a lot of familiarity with.  I could use some guidance in this area.  Thank you.

Update:  Sorry for that.  Our database is under the Oracle EE license.


texts: How to store mp3 files in MySQL database using phpMyAdmin?

texts:
I am new to databases, but I want a database for my site to store mp3 files in it, so later I play them on my site by using jplayer.

They say there are two ways to store data (in this case mp3 files) in the database:

By storing actual file itself in the database.
By only storing particular file URL, address, location or metadata, etc.

My question is what is the right way and how can I do that in phpMyAdmin?


texts:
I recently needed to setup MySQL replication across 4 servers, so that each one is a slave of another.

After several hours of tweaking I finally got this working - it was actually quite simple.  I am using MySQL 5.5.

What happens if one of the machines fails and the replication stops happening?

For instance, if there was a change on server 1, and server 3 failed during the change, the 4th machine will not know about the change.

What are possible scenarios to think about?  How can I mitigate this issue?


texts: How to import large sql file in postgres?

texts:
Trying to import sql file around 8GB using these commands

sudo -u postgres psql yourdb -f my_file.sql
sudo -u postgres psql yourdb < my_file.sql
sudo -u postgres cat my_file.sql | psql yourdb

sudo -u postgres psql
\i my_file.sql

but all of them returning out of memory error message.
Also tried configuration given here


texts:
I am trying to figure out how to get the proper results from a MySQL database.

Table Structure:  items

PN1            PN2
------------   ------------
702866-001     702866-001
738975-001     702866-001
B9C84AV        702866-001
LMT-256M6M     702866-001

The 2nd part # is a cross reference to the master item (PN1), the master item will have PN1 and PN2 duplicated.

The Select statement I needs is that I can search by any part # from either field (PN1 or PN2) and get a complete list of Distinct PN's

For example:  If I search for B9C84AV it would return all Part #'s in PN1 list since they are crossed in PN2.

Basically, what I need is a way to get PN2 for the record where PN1 is equal to the search parameter and then return a Distinct list of all records where PN1 or PN2 = PN2 (returned from the search parameter)

Any help on this would be greatly appreciated.

Regards,
Bryan


texts: scramble the value of one column and unscramble it?

texts:
I want to change all the values in one column, as it is a production database and I don't want to give the data to testers without obfuscation.

Example:

Name             Name
----     -->     ----
ABC              BCD
PQR              QRS

I have searched scrambling and other things but my issue is I want to change the obfuscated data back to their original values when needed.

I need a generic script so I can apply it to an arbitrary table if required.


texts: Mongodb Products schema with proper indexes

texts:
I'm totally new to mongodb with a MYSQL background.

I'm working on an e-commerce website with some millions of products
So we decided to migrate from MYSQL to mongodb.

So far this is the products structure that i came up with as a final schema

{
    id: 12, // filterable + sortable
    sku: 'some-sku', // filterable
    status: 'enabled', // filterable by default to get enabled products only
    createdAt: {
        date: '22-11-2018',
        timestamp: 41231312
    },
    locales: {
        en: {
            title: 'Product Title', // searchable + sortable
            description: 'Some Rich description',
            shortDescription: 'Some short description',// searchable
            category: 'Category name',
            brand: 'Brand name',
            tags: ['tag1', 'tag2', 'tag3'],// searchable
            attributes: {
                // filterable
                color: 'red',
                size: 'XL'
            },
            specifications: {
                // list of specifications
            }
        }
    },
    images: {
        main: {
            large: 'path-to-image',
            medium: 'path-to-image',
            small: 'path-to-image'
        },
        all: [
            {
                large: 'path-to-image',
                medium: 'path-to-image',
                small: 'path-to-image'
            }
        ]
    },
    categoryId: 562, // filterable
    brand: {
        id: 13, // filterable
        logo: 'path-to-log'
    },
    price: {
        original: 200,
        discount: {
            value: 40,
            percentage: 20 // filterable + sortable
        },
        salePrice: 160 // filterable with a min-max price range + sortable
    },
    rating: 4.5, // filterable + sortable
    reviews: [
        {
            customer: {
                id: 644121,
                name: 'Customer Name',
                review: 'Customer review',
                rating: 4,
                createdAt: {
                    date: '22-11-2018'
                }
            }
        }
    ],
    children: [
        // list of children products same schema
    ],
    similarProducts: [
        // list of similar products ids
    ]
}

Quick notes about the schema:

There are many fields will be used in search/filter like:

By default, Only enabled products will be displayed.
title, shortDescription and tags as full text search.
Also customer can filter products by sku category id, brand id, salePrice, rating and attributes.

Customer can sort products display using any of the following in asc/desc order:

Latest/Oldest: using the creation date
Price: High to low or Low to high
Rating: High to low or Low to high
Discount Percentage: High to low or Low to high

So Here are my questions:

1- Based on the earlier info, customer may combine many filters in same time combined with different sort based on his selection, so should i've to create indexes for all use cases?

2- Products could be in more than on category and also any product in child category will be implicitly added to its parent categories recursively

For example if we've the following category tree:

Sports >> Clothes >> Men >> Shoes

if a product in the Shoes category, it should be also in all previous categories.

In MYSQL i had a product_categories table that holds product_id and category_id so i can retrieve directly all products for the current category without looping through all children to get its ids.

Now should i add another field called categoriesList that will contain all the ids of the categories that the product is included in?

Sorry for the long talk and thanks in advance.


texts: UPDATE with subquery LIMIT 1 returns multiple rows

texts:
I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12

The query is the following :

UPDATE folder as f set locked = 'locked', date_locked = current_timestamp
from (
    SELECT * from folder as f2
    WHERE f2.locked = 'available'
    LIMIT 1 FOR UPDATE ) as folderToUpdate
WHERE f.id = folderToUpdate.id returning f.*

The id is the primary key.

It is executed through a bunch of integration tests. When I am executing only the test doing the query above, I don't have any issue. But, when I am executing all the tests, I randomly get multiple rows. Do you have any suggestions?


texts: What's better: a temporary column or a temporary table?

texts:
We have a table in our database with a Reference column. For a long time these reference values only pointed to one system thus several legacy apps assume that if it contains value, they should use it with that system.

We're updating that model to add indication which of the external systems the value refers to.

Unfortunately not all the apps caught up with that change, which is causing problems. So as temporary measure we're planning to store the reference elsewhere.

We have two options:

Add a new column to the table
Add a new table

Once all the apps are up to scratch, we'll copy the values from temp location over to the Reference column and then drop the source.

Question

Which of those two options is less messy and has less negative impact especially when it comes to cleaning it up?

Considerations

The table currently has 70k rows, 40 columns. Only rows inserted/updated by new apps will have that temporary value.


texts: Hi I want to ask about I have this DBD for my CRM I am trying to have the addresss in one table(customer  and Employee Address) and both customerID  and Employee ID are guid I have in address table FK is associate_ID to store the id (customer.ID or Employee.ID) just want to ask if is ok to do it like that ?


texts:
I just got a PEN-Test result from a web application. The tester stated they were able to use the ORDER BY clause of a dynamic SQL statement to retrieve information.
The name of the column that should be sorted is passed into the query by an app. The tester modified the value so it contains a complex statement that looks like CASE WHEN ... THEN 1 ELSE 1/0 END.
However, I built a very simple test case. I have an Oracle database and a SQLServer. Both contain the same tables. I query
SELECT * FROM users ORDER BY 1/0

When I execute this in Oracle the query executes fine. In SQLServer I get an error 8134 Divide by zero error encountered..
As the PEN-Tester was using the app on a different Oracle server than I'm using now and they report they were abusing the fact that Oracle eventually threw errors, I wonder if there's an Oracle setting that prevents the execution of ORDER BY clauses that evaluate to a division by zero. Is there such a setting? Is it a matter of Oracle Version in use? I'm on 12.1.0.2.0 where no exception is thrown. Is there such a setting in SQLServer, too?
I will of course modify the generation of the statement, so it checks if the values that are passed in are column aliases available for sorting.


texts: MySQL: select a row if that row is equal for every user

texts:
In my database I have 5 fix users, lets say user1, ..., user5.

Furthermore I have a table

month(name, finished, month, year)

where name is VARCHAR (the name of one of the five users) and the others are INTEGER. In row finished, 0 means not finished and 1 means finished (Note that finished does not mean 'the month is over' in this context). Every user has an entry for each month.
I'm struggling to find a query which returns me the months where every user has the entry finished = 1.

SELECT month FROM month

obviously returns months, where other users can have finished=0

SELECT month FROM month WHERE finished=1

returns the finished months (multiple times), while other users can have finished=0 in this same month and I would have to check in the code if there are months appearing five times (which would mean that month is finished by all users).

Now, without creating triggers or similiar constructs, is there a way to check the desired requirement directly in the SQL-query?

Addendum: I know that a table monthAll(month, year, finishedAll) would be a solution, but setting it to finishedAll=1 if all five users have finished a certain month would equally require additional code in my JavaScript due to the fact how the whole program is constructed.


texts: Move SQL Server cluster from one location to other

texts:
I am looking for ideas on how to move SQL Server 2012(standard) two node cluster installed on Windows server 2012 standard to other location which is far away from current location. The two locations are of course connected. The source and destination would be exactly same in all terms.

I am aware that the old and good approach would be to create exact same cluster on the destination and then move database by backup restore and then either change SQL server virtual name to old one or make changes in DNS or application to point to new cluster. But we cannot follow this approach because

The number of such machines are high
There is no application owner, so we are not sure where exactly changes needs to be done(a bit tricky situation).
SQL Server cluster does not supports renaming if database is replicated.

The OS on both the locations would be sitting on Vmware Esxi 5.5. So this is virtual environment. Everything would be exactly same between source and destination.

The cluster has quorum disk as witness not fileshare and shared disks are on SAN.

NOTE: We have luxury of downtime as weekends would be given to us for migration.


texts: Need help to create chat application database schema?

texts:
I am developing a chat application in asp.net using SignalR.

Client requirements are:

Admin can create different chat rooms e.g. (News Room, Sports Room, Android Room etc)
Registered users can then join different chat rooms (one at a time) to chat with other online users on selected chat room.
Each user's chat history should be stored.

These are major requirements of my client. I have developed the following database schema at the moment for managing users and chat history of users.

Table 1: User

Fields: user_id, username, firstname, lastname, email, phone

Table 2: chathistory

Fields: ch_id, user_id(FK), sendby, sendto, message, time, date

But now I am confused in creating database schema for multiple chat rooms. How should I create or manage database schema for chat rooms. Please help me out to get it done as per my client's requirements.

Thanks,

momersaleem


texts: SQL User with only access to maintain indexes and statistics

texts:
I need to have an AD user account with access to all databases on a specific instance of SQL Server 2008 R2, and the only thing that user will be doing is;

Reorganize indexes
Database integrity
Update stats

The database admin here says for this I need to give dbo access to this user on all databases; is that really true or is there a better / more secure way of giving this specific access to one user?


texts: How to (re)start Postgres on Windows command line in background?

texts:
My local Postgres server is configured to start when the computer starts. I just changed postgresql.conf file and had to restart the server. I did:

pg_ctl restart

from the command line BUT that started the server on the next command prompt:

pg_ctl: old server process (PID: 2968) seems to be gone
starting server anyway
server starting

D:\>2016-10-12 16:24:48 EDT LOG:  redirecting log output to logging collector proces
2016-10-12 16:24:48 EDT HINT:  Future log output will appear in directory "pg_log".

If I ctrl-c out of it or close the command window, that closes the server. How can I use pg_ctl to (re)start the server in the background, emulating how it works by default (as a Windows service)?


texts: Example of Logical Schema vs Physical Schema?

texts:
I've read through multiple Q&As attempting to intuitively understand the difference between logical schemas and physical schemas.
Unfortunately, I've only been able to take away the following vague definitions.

Logical refers to a high-level definition of data including tables, data types, etc.
Physical refers to the 'implementation', i.e. how data is actually stored.

Is this correct? And if so, is there a concrete example or two that can drive home these definitions?
edit: I know that this question has probably been asked many times over, but I'm specifically asking for an illuminating example.


texts: Getting the contents of a json file as a variable in plpgsql?

texts:
I have a function based this post by Sean Huber in order to get the content of a file:

CREATE FUNCTION file_read(file text)     RETURNS void AS $$
DECLARE
  content text;
  tmp text;
BEGIN
  file := quote_literal(file);
  tmp := 'tmp_table';

  EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)';
  EXECUTE 'COPY ' || tmp || ' FROM ' || file;
  EXECUTE 'SELECT content FROM ' || tmp INTO content;
    **Do some more stuff here**
  EXECUTE 'DROP TABLE ' || tmp;

END;   $$ LANGUAGE plpgsql VOLATILE;

I'm not really happy with this as it is doing so much more work than necessary. I'd prefer not to create/drop relations cause all I really want to do is run Postgres' JSON functions against the content of some .json file. Does anyone know of a better way to do this without using psql?


texts:
i want to display all employee from tbl_Employee_Master ,weather emp_Id is exit or not in  #tbl_assign_Allowance_Deduction table
I developed a query,but it fetching those record where asd.sal_ID is null,but i want to fetch all employee from tbl_Employee_Master table,when ever this query is run'
Create Table #tbl_Designation (Designation_ID int, Designation_Name varchar(50));
CREATE TABLE #tbl_Employee_Master (emp_ID INT,emp_Name Varchar(50),emp_salary int,Designation_ID

    int,emp_OT int );
CREATE TABLE #tbl_Type_allowance_Deduction (DA_ID INT,DA_Name Varchar(50), DA_TYPE_ID Varchar(50));
CREATE TABLE #tbl_assign_Allowance_Deduction (assign_ID INT,EMP_ID int ,DA_ID int, Assign_amt int,Sal_ID int);

Insert into #tbl_Designation values
(1,'officer'),
(2,'Executive')

Insert into #tbl_Employee_Master values
(1001,'Irfan',10000,1,20),
(1002,'Khan',10500,2,20),
(1003,'Akhter',1200,1,20) ,
(1004,'Ali',8000,2,20),
(1005,'Jhain',3300,1,20)

insert into #tbl_Type_allowance_Deduction values
(1,'Loan','Deduction'),
(2,'Medical','Allowance'),
(3,'Rent','Allowance'),
(4,'Conveyance','Allowance'),
(5,'Advance','Deduction'),
(6,'Mobile','Allowance')

insert into #tbl_assign_Allowance_Deduction values
(111,1001,1,500,111),
(112,1001,2,500,111),
(113,1002,3,540,111),
(114,1003,4,200,null),
(115,1003,6,400,null)

 SELECT  (M.Emp_ID) , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation,
       SUM(CASE T.DA_Name  WHEN 'Loan' THEN (ASD.assign_amt)ELSE 0 END) Loan,
        SUM(CASE T.DA_Name  WHEN 'Advance' THEN (ASD.assign_amt)ELSE 0 END) Advance,
           SUM(CASE T.DA_Name  WHEN 'Mobile' THEN (ASD.assign_amt)ELSE 0 END) Mobile,
               SUM(CASE T.DA_Name  WHEN 'Conveyance' THEN (ASD.assign_amt)ELSE 0 END) Conveyance,
               SUM(CASE T.DA_Name  WHEN 'Medical' THEN (ASD.assign_amt)ELSE 0 END) Medical,
                  SUM(CASE T.DA_Name  WHEN 'Rent' THEN (ASD.assign_amt)ELSE 0 END) House_Rent,
isnull(M.emp_OT,0) as emp_OT
,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour',asd.Sal_ID
FROM #tbl_Employee_Master M
 inner join #tbl_designation D on D.Designation_ID=M.Designation_ID
   left   join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
   left outer join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
where asd.sal_ID is null
 group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,M.emp_OT,asd.Sal_ID


texts: MariaDb: Create a nested json object query

texts:
I'd like to generate an array of json objects in MySql select query.

:: Update ::

Here is a db structure for which i'd like to return the result as below format.

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(255),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `shops` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(255),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

The expected JSON output would be:

{
    "products": {
        "name": "lorium"
    },
    "shop": {
        "name": "ipsum"
    }
}

I have searched for the same but can not get the result which is related to my topic so is it possible in mysql to create a query for rendering the above result ?

MySql version: 5.5.5

MariaDb Version: 10.3.22


texts: Methods for finding new Trace Flags in SQL Server

texts:
There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, Microsoft employees, etc., what are ways to find new trace flags?

I've read through a couple recent posts by Aaron Bertrand here and here, but didn't spot anything about new Trace Flags.

I copied the data and log file of mssqlsystemresource to a new location, and attached it like a regular database to poke through system tables and views, but didn't spot anything immediately. I considered taking a list of known Trace Flags, and looping through numbers not on that list, to see which ones DBCC TRACEON would allow, but wanted to ask the question here first.

Assuming that the DBCC command to enable them has to check in with some resource to make sure the Trace Flag is valid, where does it reach out to? Is there a .dll or some other system file that holds a list?

I know the question casts a wide net, but what spurred this was reading about a Trace Flag with specific intended behavior alongside a new feature in 2016 that was not having the described effect. My initial thought was that perhaps the numbers were transposed somehow, like 7129 becoming 7219. I was hoping to get a list of valid trace flags within a range, say 7000-7999, to look for permutations. Testing them all, both as DBCC TRACEON flags and startup parameters would be quite a nuisance, combined with testing the results against the feature behavior.


texts:
Today I had to grant sysadmin rights for 2 users. ;(

As I couldn't change that, I at least want to see, what they are doing, so I am looking for a way, to audit all activities by these 2 Users.
Sadly I couldn't find anything on how to set this up, with the exception of using the Profiler (which I don't want to do).

Does anybody has an idea on how to setup the Audit function to audit all activities by these 2 users?

thank you very much in advance. :)


texts: Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11?

texts:
I have a database with a VARCHAR column that contains integers of varying length. I want to sort them so 10 comes after 9, not 1, and 70A comes after 70. I was able do this with PATINDEX(), a CTE, and CASE statements in the WHERE clause.

However, I was wondering if there was a collation where this would be unecessary.


texts: Using NEW on tables with generated columns

texts:
I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially this:
BEFORE UPDATE
ON public.some_table
FOR EACH ROW
WHEN (NEW IS DISTINCT FROM OLD)
EXECUTE PROCEDURE public.update_modified_column();

However, this code despite being correct does not work with generated columns, giving the following error:

BEFORE trigger's WHEN condition cannot reference NEW generated columns

So my question is this: Is there any way to use NEW with a table that has generated columns without having to specify every single column of that table that isn't generated?
It can be something that requires specifying the columns that were generated to be excluded, just not the other way around.
Also, I know a potential alternative to generated tables is views (which are not the same thing but close enough for my use case), but I can't use views, since I need something that keeps track if the columns allows or doesn't allow for null values, which unfortunately views do not do that.
Using normal columns with a before insert/update trigger to calculate the columns, along with setting the columns as NOT NULL with a Default (for my use case ofc), has essentially fixed the issue; however, I will keep the question open to see if there is any solution to the generated columns specifically, since they are a bit more of an elegant solution.


texts:
Is it possible to create an interval by string in PostgreSQL?

for example, I have a table:

CREATE TABLE x (
 id BIGSERIAL PRIMARY KEY,
 data JSONB
);
INSERT INTO x(data) VALUES('{"day_sess":"4_1"}');

-- the query:
SELECT DISTINCT (INTERVAL (LEFT(data->>'day_sess',1) || ' days')) FROM x ORDER BY 1;

it always give

ERROR:  syntax error at or near "LEFT"


texts:
I'm trying to create a simple bi tool similar to Power BI (see here).

You can see in Power BI that it allows the user to upload the CSV files (say with 10 million rows too). Once uploaded we can select a particular column in the csv files in the UI to create visualizations.

I was wondering how the database schema will look like for this particular operation? If the user could able to save 3,4,5 and n columns of csv, how the UI is so quick and response enough to get back the data? Are there any techniques for handling CSV data in Postgres sql?

Kindly help me out.

I'm a newbie to postgres and database in general.


texts: What exactly is the use of the column USER in a pg_hba.conf file?

texts:
Let's say I have a pg_hba.conf file for my Postgresql server. I know that in order to allow other IP addresses to access my Postgresql server, I need to write their entries in my pg_hba.conf file. Now, what I don't understand is the use of the column USER in the pg_hba.conf file. Lets say my file contains the following entry

host all    user123     192.168.10.27/32    md5

According to my understanding, it means that in order to access my Postgresql server from the machine with IP 192.168.10.27, someone needs to be logged in as the user 'user123'.
But, as far as i understand, in order to access my Postgresql server from 192.168.10.27, i need to create a separate connection to my server from 192.168.10.27. So even if I am logged in as 'user123', I will be anyways creating a separate connection to access my Postgresql server, which won't depend on whether I am logged in as 'user123' or someone else.
Hence, I don't understand the use of USER column.
Can someone please help me?


texts: SQL listener connection fluctuates between primary and Secondary

texts:
We have SQL Server 2017 + CU11 Always On set up with one primary and a secondary replica. Suddenly we started facing issues where SQL Server AG listener connection started fluctuating between primary and secondary.

There are no error logs on SQL Server and System events. Also we have setup the AG on single subnet.

Below are the parameters configured for listener.

PS C:\Windows\system32> Get-ClusterResource -Name "SQLListenerName" | Get-ClusterParameter

Object                        Name                          Value                         Type
------                        ----                          -----                         ----
SQLListenerName               Name                          SQLListenerName               String
SQLListenerName               DnsName                       SQLListenerName               String
SQLListenerName               Aliases                                                     String
SQLListenerName               RemapPipeNames                1                             UInt32
**SQLListenerName               HostRecordTTL                 1200**                          UInt32
**SQLListenerName               RegisterAllProvidersIP        0**                             UInt32
SQLListenerName               PublishPTRRecords             0                             UInt32
SQLListenerName               ResourceData                  {1, 0, 0, 0...}               ByteArra
SQLListenerName               StatusNetBIOS                 0                             UInt32
SQLListenerName               StatusDNS                     0                             UInt32
SQLListenerName               StatusKerberos                0

Additional information: Our servers are on Azure and SQL listener is configured with Azure ILB.


texts: Defragmenting indexes SQL Server bases on avg_page_space_used_in_percent

texts:
I am wondering if I should take into account information from

sys.dm_db_index_physical_stats

column

avg_page_space_used_in_percent

when deciding on defragmenting/not defragmenting an index.

How is it possible that I see low avg_fragmentation_in_percent value and also low value for avg_page_space_used_in_percent?

Based on Brent Ozar article I could think that I should rebuild an index as there is a lot of free space on pages so it is a waste of disk and RAM space.
But scripts as Ola Hallengren's, for example, analyze only avg_fragmentation_in_percent, don't they?


texts:
I have posted the question yesterday, then I was able to figure it out by myself. As I haven't found any complete guide, I'm writing down the steps just in case.

So the question is: "How do I install db2 express c on Mac OS X?".


texts: Why would a very simple Oracle query use a large amount of shared memory?

texts:
I have a very simple query which is as follows:

SELECT blob_column, varchar2_column
FROM table
WHERE number_column = ? AND code_column = ?

This will find one row by the primary key.

This shows as the top statement when I look for queries using high amount of sharable memory.

set pagesize 50
set linesize 250
column sharable_k format 99999
column persistent_k format 99999
column runtime_k format 99999
column total_k format 99999
column version_count format 99999
column loaded_Versions format 99999

select * from
(
    SELECT
      sql_id,
      trunc( sharable_mem / 1024 ) sharable_k,
      trunc( persistent_mem / 1024 ) persistent_k,
      trunc( runtime_mem / 1024 ) runtime_k,
      trunc( ( sharable_mem + persistent_mem + runtime_mem ) / 1024 ) total_k,
      version_count,
      loaded_versions,
      executions,
      parsing_schema_name
    FROM
      v$sqlarea
    ORDER BY ( sharable_mem ) DESC
)
where rownum < 21;

The first result is this query and the figures are as follows:

SQL_ID        SHARABLE_K PERSISTENT_K RUNTIME_K TOTAL_K VERSION_COUNT LOADED_VERSIONS EXECUTIONS PARSING_SCHEMA_NAME
------------- ---------- ------------ --------- ------- ------------- --------------- ---------- ------------------------------
fa72fbmfmq40b       6418           18        15    6452             3               2    1000354 APPUSER

My understanding is that sharable memory holds the plan.  The plan in this case is very simple.  Why is this statement taking up so much sharable memory?  This is on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.


texts: How can I optimize this mySQL table that will need to hold 5+ million rows?

texts:
The table has about 12 rows.

Indexes:

idint(11), Unique, auto-increment, primary

ATTACHMENTIDvarchar(14), Unique

MLSNUMBERbigint(20)

POSITIONint(10)

FILETYPEvarchar(32)

I'm doing an extended insert with an ON DUPLICATE KEY UPDATE clause.
Each insert has anywhere from 300-10,000 records.
The more records in the table, the longer the query takes.

With 0 rows, the query took about 5-10 seconds.
After about 300,000 rows, the query was taking 50 seconds.

What would be the best way to optimize this table? Thanks in advance!


texts: Multiple DML statements on their own vs. being grouped in a single WITH statement

texts:
Are there any reasons to prefer

BEGIN;
UPDATE [...];
INSERT [...];
DELETE [...];
COMMIT;

over

WITH
u AS (UPDATE [...]),
i AS (INSERT [...]),
DELETE [...])

and vice versa?


texts: Is it possible in Oracle to trace SQL statements that result in errors?

texts:
We have Oracle 11g in production. Application system is still under active development. It will be very handy to get SQL statements which cause any error.

Does Oracle provide a standard function to trace and log these statements and additional (debug) info?


texts: Which workarounds exists to missing relative path feature of SQLCMD :r?

texts:
I'm not the first to notice the lack of this feature.
But I wonder if there are some useful workarounds.
I want to deploy a main script and some depended scripts in the same folder.
BTW Is there a ticket on Microsoft Connect for this?
Edit:
Locking at this, it seems equivalent to the question if a script running in sqlcmd mode can determine its path in the file system.
2nd Edit:
From SO I found a way to pass the path of the scripts into sqlcmd
sqlcmd -S myServer -d myDB -U user -P password -i file1.sql -v p1="D:\myscripts"

And I can verify that it is passed to File1.sql
PRINT '$(p1)'

:setvar path1 $(p1) + '\file1.sql'

:r $(path1)

But I don't find the right way to concat the path and the filename in the second line.
3rd Edit:
I found the solution to concat path and filename in here.
PRINT '$(p1)'

:r $(p1)\file1.sql

Are there still better ways?


texts: Is it possible to take a database offline when doing a backup using an SQL job?

texts:
I have the scenario where I have to generate a backup of the database (SQL Server 2008) and restore into the new server (SQL Server 2016). While taking backup data should not be changed in any case. So I have two options to do that, but I am not sure how it will work. (Everything is using SQL Job only.)

Set a read-only database and restore into the new DB server.

→ Is it possible to restore a read-only DB on the new server? The destination server is already having a read-write (online) database by the same name.
Set an offline database and restore onto the new DB server.

→ Is it possible to restore an offline DB in the new server? The destination server already has an online (read-write) database by the same name.


texts: Creating a unique index on a materialized view with no unique columns?

texts:
I am using Postgres 9.4. I have created a materialized view from one of my tables and I want to be able to refresh it concurrently.

I know from the documentation that in order to do a concurrent refresh, I need to have a unique index on the materialized view. But currently I have no unique fields in the view.

This is the current structure of the materialized view:

Materialized view "public.vw_chemical_summary_by_ccg"
     Column      |         Type         | Modifiers
-----------------+----------------------+-----------
 processing_date | date                 |
 pct_id          | character varying(3) |
 chemical_id     | character varying(9) |
 items           | bigint               |
 cost            | double precision     |
Indexes:
    "vw_idx_chem_by_ccg_chem_id" btree (chemical_id)
    "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)

The processing_date and pct_id and chemical_id fields should be unique together, but I haven't enforced that anywhere.

So my question is, how can I sensibly create a unique index so that I can do concurrent refreshes?

I guess I could add a new integer column and make that unique, then index it - would that be the most sensible approach?


texts:
I'm new with Oracle. This is something simple that I'm just not getting.

I'm in a situation where I need to split a string with values separated by commas (i.e. 25,27) and "decode" it (example: if 25 = 'Bird. ' and 27 = 'Elephant. ', then 25, 27 should return Bird. Elephant.).

I've followed this and manged to get values I want, but how do I place it into a select statement as a subquery?

The code so far:

declare
    l_input varchar2(50) := '25,27';
    l_count binary_integer;
    l_array dbms_utility.lname_array;
    finalText varchar2(50);
    placeholderText varchar2(50);
begin
    dbms_utility.comma_to_table
    ( list   => regexp_replace(l_input,'(^|,)','\1x'),
    tablen => l_count,
    tab    => l_array
    );

    finalText:= '';
    for i in 1 .. l_count
    loop
        placeholderText := '';
        case substr(l_array(i), 2)
            when '25' then placeholderText:= 'Bird.';
            when '27' then placeholderText:= 'Elephant.';
            else placeholderText:= substr(l_array(i), 2);
        end case;
        finalText:= finalText || ' ' || placeholderText;
    end loop;
    dbms_output.put_line(finalText); --I want this as the column 'value'
end;

What I would like (or something similar):

select
    Id,
    Name,
    (declare
        l_input varchar2(50) := ItemColumValue;
        l_count binary_integer;
        l_array dbms_utility.lname_array;
        finalText varchar2(50);
        placeholderText varchar2(50);
    begin
        dbms_utility.comma_to_table
        ( list   => regexp_replace(l_input,'(^|,)','\1x'),
        tablen => l_count,
        tab    => l_array
        );

        finalText:= '';
        for i in 1 .. l_count
        loop
            placeholderText := '';
            case substr(l_array(i), 2)
                when '25' then placeholderText:= 'Bird.';
                when '27' then placeholderText:= 'Elephant.';
                else placeholderText:= substr(l_array(i), 2);
            end case;
            finalText:= finalText || ' ' || placeholderText;
        end loop;
        dbms_output.put_line(finalText); --I want this as the column 'value'
    end;) as "Some Column Name"
from
    some_table

Is this possible?

Thank you.


texts: How to rename columns names in dynamically pivot table from other table?

texts:
I have two tables pointscategory and points:

    CREATE TABLE  pointcategory (
     categoryID int(20) NOT NULL AUTO_INCREMENT,
     categoryname varchar(50) NOT NULL,
      PRIMARY KEY (categoryID)
    )
    CREATE TABLE points (
    pointID int(11) NOT NULL  AUTO_INCREMENT ,
    value int(50) NOT NULL,
    categoryID int(10) NOT NULL,
    studentId int(10) NOT NULL,
    PRIMARY KEY (pointID),
    FOREIGN  KEY (categoryID) REFERENCES pointcategory (categoryID)    )

I have created pivot table from points table by this procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pivot`()
    SQL SECURITY INVOKER
BEGIN
   SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when categoryID = ''',
      categoryID,
      ''' then value end) AS `',
      categoryID, '`'
    )
  ) INTO @sql
FROM  points;
SET @sql = CONCAT('SELECT date(date), ', @sql, '
                  FROM points
                  GROUP BY date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

The output is:

Now I want to rename the columns names (categoryId) in the above table by categoryname from pointscategory table.

  from date,1..6 to date,categoryname1 ... etc


texts: Which version of linux OS has support for MongoDB installation?

texts:
I would like to learn how to install MongoDB in my Windows machine. Currently I have a Windows 8 64 bit OS laptop and I installed MongoDB 64 version for Windows.

My question is: which version of linux (64 bit) supports my machine to install MongoDB.
I known Ubuntu linux only. Is it suitable for installing on my machine? Currently my machine has 64 bit version.

Thanks in advance for clarifying my doubt.


texts: Semantic version type on PG 11.5 deployed on RDS. ltree?

texts:
I've got some semantic version details to store, and figured a dedicated type would be a bit help with searches and sorts. At least one implementation exists:

https://github.com/theory/pg-semver

This is not supported on RDS. why we're on RDS. Is there an alternative that's RDS enabled? I'm considering either ltree or simply using a text or citext and grinding out the necessary queries and sorts, as needed.

Thanks


texts: mysql 8 high availability with drbd, corosync and pacemaker

texts: I would like to implement High Availability for MySQL8, is this version supported by drbd, heartbeat, corosync, pacemaker? I've looked around online to that regard but there isn't much information about MySQL8. What is the ideal HA cluster for MySQL8?


texts: What filter to use in SQL Profiler for socket write error

texts:
Can someone please help with intitial SQL Profiler filter setup to narrow down the settings and reduce the noise when I'm trying to find out the reason behind socket write error ?

Basically the problem is that certain integration software is dropping xml files along the way from one application to the end application but some go through. There are the errors in the log. After we restarted the sql and app server first two days no messages were dropped.

I need to figure out the possible reason. We will probably have to run the profiler for 24 hours because the error appear only occasionaly so if there is too much data it becomes useless. Any help appreaciated. Thank you.

jdbc:jtds:sqlserver://ASSET-DB-02;instance=test;DatabaseName=MonitorEngine'
I/O Error: Connection reset by peer: socket write error
java.sql.SQLException: I/O Error: Connection reset by peer: socket write error


texts:
I have a full backup dir and a bunch of hourly (incremental) backup directories taken with mariabackup, which is a port of the XtraBackup.

How does one restore the MariaDB up to a specific minute (or second)? The general form applies all events within the incremental snapshot to the base, but what if we need to be more granular than that? I was hoping to be able to set an upper bound on the LSN or datetime.

Ref: https://mariadb.com/kb/en/library/incremental-backup-and-restore-with-mariabackup/


texts: Log files in Always on Disaster Recovery Situation

texts:
In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node.
Two replicas in the same datacenter have sync replication for HA and the other two replicas in the other datacenter are async for DR purposes.
My company wants to perform a Disaster recovery test in the form of simulating one datacenter going down from Friday night to Sunday afternoon.
When the primary datacenter has gone down I can manually fail the AGs from the primary to the secondaries, we ave good latency so any data loss will be very negligable.
However the one thing that concerns me is how to handle the logs on replicas that are up and running in the DR site, which will grow and grow as the secondary replicas will be down and the log files cannot be backed up.
I had planned to just let the logs grow for the period of time (friday night to Sunday afternoon) and when the secondary replcias were brought back up let the databases sync up again and fail back.  There is a decent amount of space on the disc drive for the logfiles.
I am not sure if I can let this situation happen for this long amount of time, the only other option I can see is removing the secondaries that are offline and then recreating and reseeding the databases when the Primary site is back.  But I really dopn't want to have to do this as it will be a substantial amount of Database and will take quite a bit of time.
Is there any way I can handle it better than just letting the log files grow?


texts: INSERT AFTER trigger failing with multiple rows

texts:
I have a trigger (AFTER INSERT) that has been working in single inserts, and I believed it would work for multiples but its not. I researched it and am currently stuck as what I'm reading implies it should work. Any advice on the below would be appreciated: -

ALTER TRIGGER [dbo].[Trig_Tb_Users_Created]
    ON  [dbo].[Tb_Users]
    AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CreatedBy INT

IF (SELECT COUNT([ID]) FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME()) = 0
    INSERT INTO [Tb_Users] ([ADUsername]) VALUES (SUSER_NAME());

SELECT @CreatedBy = (SELECT TOP 1 [ID] FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME());

UPDATE [Tb_Users] SET [CreatedBy] = @CreatedBy WHERE [ID] in (SELECT [ID] FROM inserted);

INSERT INTO [Tb_Permissions] ([LnkUserID]) VALUES ((SELECT [ID] FROM inserted));

END;

I referred to this article (Create DML Triggers) and to my understanding I am following the syntax correctly.

Bu I get the below error message when trying to insert multiple rows in to Tb_Users that refers to this trigger saying: -

Msg 512, Level 16, State 1, Procedure Trig_Tb_Users_Created, Line 28 [Batch Start Line 0]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

This is in MS SQL Server 13.0.4522.0


texts:
I'm using SQL Server 2008 R2.  Absolutely new to DBA role, but I've worked with SQL and VS for a while.

I've installed the 2 aforementioned softwares on my home computer.  If I have VS2010 open and try to open the SQL Server database so I can make changes to something, I'm getting the error:

  The process cannot access the file because it is being used by another process.

Now, at work I can definitely have VS2010 open and edit stuff in my database, so I'm guessing there's a setting that allows you to do this?  Can anyone tell me where this setting is, and how to adjust it so that I don't have to exit VS2010 every time I need to write a new Stored Procedure in SQL Server?


texts: SQL 2012: Import DB changes original data scheme

texts:
I'm migrating data from SQL 2008 R2 Servers to SQL 2012. Therefore I'm using SSMS Import Data Task on the SQL 2012 machines.

As a test I copied the data files of one of the DBs, did an "Attach Database" and a schema compare with the latest SQL Server Data Tools.

The diff from the attached vs. the imported-one seems just wrong.
As an example:

Attached:

CREATE TABLE [dbo].[DbEventMessages] (
[EventID]         INT            IDENTITY (1, 1) NOT NULL,
[EntityType]      NVARCHAR (MAX) NOT NULL,
[ContactId]       INT            NOT NULL,
[Action]          NVARCHAR (MAX) NOT NULL,
[DateTime]        DATETIME2 (7)  NOT NULL,
[InnoVersionType] ROWVERSION     NULL,
CONSTRAINT [PK_DbDbEventMessages] PRIMARY KEY CLUSTERED ([EventID] ASC)
);
GO

Imported:

CREATE TABLE [dbo].[DbEventMessages] (
[EventID]         INT            NOT NULL,
[EntityType]      NVARCHAR (MAX) NOT NULL,
[ContactId]       INT            NOT NULL,
[Action]          NVARCHAR (MAX) NOT NULL,
[DateTime]        DATETIME2 (7)  NOT NULL,
[InnoVersionType] ROWVERSION     NOT NULL
);
GO

The imported scheme differs in

the IDENTITY property on the key column
the ROWVERSION was changed from NOT NULL to NULL
the primary key constraint is missing

What is wrong here? Or is it working correctly?
Thanks for any comments..


texts:
I wonder what are the GDPR constraint on a MySQL DB for a multitenant SaaS application.
While a solution with a single DB per each multitenant customer and restricted  access will probably be fine, what about a single DB?

How do I guarantee a proper insulation of data for single tenants?
Pure applicative solutions seems weak to me (...where tenantID=xx for each query).

Will I be forced to create restricted views on each table or for most queries?
How well will MySQL 6.7 handle this? I've read about indexing views and performance, I'm not sure what will happen to my application if I switch every table to a view.

Have new versions of MySQL improved? Should I use a db proxy like MaxScale?
Or maybe it's reason enough to switch to postgres or other db?


texts: Bi-directional replication for the same MySQL table

texts:
AppA stores/retrieves data from dbA.tableA
AppB stores/retrieves data from dbB.tableA

tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA (assuming both had 5 rows).

row6 was created by AppA (say primary key 6)
row7 was created by AppB (say primary key 7)

I would like row7 to be copied to dbA.tableA and row6 to dbB.tableA

Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time.
If the primary key is an auto-increment, would it be possible to maintain integrity of data or is there a possibility that there would be collisions on the primary key.


texts: MySql date range query not showing all records

texts:
I am working on mysql query right now. I write a query from fetch data from march 2018 to march 2019. There are more than 5000 records for this year. I write a query with date range from march 2018 to march 2019 but its showing me only 150 records

One more thing here. The date column in database is varchar. I know its not good but it was taken accidently during creation of database and I am not able to change this to date type because of lots of data in database

Here is the query I created

SELECT  a.*,b.*
FROM OrderCalculation a
right join crm_order b on a.orderid = b.orderno
WHERE str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR
AND str_to_date(b.Date,'%Y-%m-%d')  <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')

Please make this query correct if you think something wrong in it

Thank you


texts: Postgresql update slow on a relative small/medium table

texts:
I'm trying to run an update on a table with 10 million rows (I just need to update a few), but the query is taking more time than I would like.
This is the table deinifition:
CREATE TABLE det_price_list
    (
      price_list_id     BIGINT NOT NULL
        CONSTRAINT fk_det_list_precio_venta_2
        REFERENCES price_list
        ON UPDATE RESTRICT ON DELETE RESTRICT,

      det_price_list_id BIGINT NOT NULL,
      product_id        VARCHAR(25),
      account_id        BIGINT,
      price             NUMERIC(22, 8),
      currency_id       BIGINT,
      last_updated      TIMESTAMP,

      CONSTRAINT pk_det_price_list
      PRIMARY KEY (price_list_id, det_price_list_id),
      CONSTRAINT fk_det_list_precio_venta_1
      FOREIGN KEY (product_id, account_id) REFERENCES product
      ON UPDATE RESTRICT ON DELETE RESTRICT,
      CONSTRAINT fk_det_price_list_4
      FOREIGN KEY (currency_id, account_id) REFERENCES currency
    );

With this index:
CREATE UNIQUE INDEX indice_det_lista_item
  ON det_price_list (product_id, price_list_id);

CREATE INDEX ix_det_price_list_5
  ON det_price_list (price_list_id, account_id, product_id);

CREATE INDEX ix_det_price_list_1
  ON det_price_list (price_list_id);

CREATE INDEX ix_det_price_list_2
  ON det_price_list (product_id);

CREATE INDEX ix_det_price_list_6
  ON det_price_list (account_id, product_id, price_list_id, last_updated);

CREATE INDEX ix_det_price_list_3
  ON det_price_list (account_id);

CREATE INDEX ix_det_price_list_4
  ON det_price_list (last_updated);

CREATE INDEX det_price_list_account_id_price_list_id_currency_id_i
  ON det_price_list (account_id, price_list_id, currency_id);

And this is the query:
update det_price_list d
    set last_updated = current_timestamp
  where d.account_id = 994
  AND   price_list_id = 4338
  and d.currency_id= 2;

Also here is the query plan using EXPLAIN (ANALYZE, BUFFERS):
QUERY PLAN
Update on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=1806.823..1806.823 rows=0 loops=1)
  Buffers: shared hit=1040857 read=1704 dirtied=8839
  I/O Timings: read=1.814
  ->  Index Scan using det_price_list_account_id_price_list_id_currency_id_i  on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=0.034..28.386 rows=26776 loops=1)
        Index Cond: ((account_id  = 994) AND (price_list_id  = 4338) AND (currency_id = 2))
        Buffers: shared hit=2740 dirtied=621
Planning time: 0.157 ms
Execution time: 1806.873 ms

The query is using the index. I've already tried to vacumm, vacumm full, reindex and analyze but i can't reduce that time. I can't copy into another table and drop the old one because it is not a complete update and also the table is used constantly.


texts:
Is there any tool or configurations for mongodb server to protect against brute force attacks on the user credentials?
I found only authFailedDelayMs param witch Available in MongoDB Enterprise only (not my case)
Thanks


texts: Refresh a PostgreSQL materialized view automatically without using triggers

texts:
I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this with triggers but it didn't work.

Is it possible to refresh a materialized view automatically without using triggers?


texts: Which is better MAX function or ORDER BY column_name DESC with LIMIT 1

texts:
I have one scenario where I want to have max value on date column,

eg. for table

CREATE TABLE IF NOT EXISTS `sample_table` (
  `id` TINYINT UNSIGNED NOTNULL,
  `name` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARYKEY(`id`))
ENGINE = InnoDB;

So I used a query as below:

SELECT MAX(created_at) AS latest FROM sample_table;

But I was suggested to index created_at column and use following query for faster result

SELECT created_at FROM sample_table ORDER BY created_at DESC LIMIT 1;

I am clueless which is best way to do so? If second query is better, than why there is inbuilt MAX function


texts: How to determine which statement is blocking an update in SQL Server

texts:
We have an UPDATE statement running in a large J2EE application that is getting blocked by some other statement. The update is using the primary key, so it should affect only one row. The isolation level being used in the application should be READ UNCOMMITTED, so it should only be another modification that is causing the block, but we can't see where.

We have obtained a report of All Blocking Transactions and this indeed shows a session blocking an update. Using the DBCC INPUTBUFFER command shows the last statement executed by the blocking session, but this is an SELECT on another table than the UPDATE, so I think it can't be the cause. I presume it must be a statement earlier in the transaction.

At this point, my SQL Server knowledge runs dry, and I also have problems reproducing the issue; it occurs intermittently.

What steps should I know take to find the blocking statement?


texts: Enable GTID on one node in Percona/Galera

texts:
Im having 3 node percona cluster. (5.6)

Server ID is enabled on each node but all are different IDs.
Binlog and log slave updates are enabled.

Now I want to enable GTID on the third node alone. I have tried it in a two node test server, and nothing has affected. So I want to implement this on the production which has 2k transaction on sum of all three nodes.
Questions:

If I enabled on only one server, will if affect anything in percona replication (Reference)?
What will happen for my running transactions while restarting the Percona MySQL? (will be kill and roll back the uncommitted transactions)?
I have different Server ID, any problem with that?


texts:
I have a table for which a update stored procedure has been created.
For this table I created an update trigger. But when I either execute the stored procedure or execute an update query I am getting this error message:

  Msg 8152, Level 16, State 14, Procedure OperationHistory, Line 95
  String or binary data would be truncated.
  The statement has been terminated.

Create Trigger OperationHistory on [dbo].[Employee]
For Update
AS
Declare @EmployeeID int;
Declare @Employeename varchar(100);
Declare @SSN bigint;
Declare @Phonenumber bigint;
Declare @JoinDate Date;
Declare @OperationHistory varchar(100);
Declare @Operationtimestamp Datetime;

select @EmployeeID=i.EmployeeId from inserted i;
select @Employeename=i.Employeename from inserted i;
select @SSN=i.SSN from inserted i;
select @Phonenumber=i.Phonenumber from inserted i;
select @JoinDate=i.JoinDate from inserted i;

If Update (EmployeeId)
Set @OperationHistory='updated employee id';
If Update (Phonenumber)
Set @OperationHistory='updated phonenumber';
If Update (SSN)
Set @OperationHistory='updated SSN';
If Update (Employeename)
Set @OperationHistory='updated employeename';
If Update (JoinDate)
Set @OperationHistory='updated JoineDate';

Insert into Employee_History
Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Go


texts:
On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), older version of MySQL (5.7) and Ubuntu 16.04 everything is much faster. I copied the config in /etc/mysql/mysql.conf.d/mysqld.cnf from the faster computer to the slow one but nothing changed.
How can I trace from where is this problem coming?

For example

CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) ENGINE='InnoDB';

finished for roughly 0.12~0.90 seconds and from

SHOW PROFILE for query 7;

I get

'starting', '0.000132'
'checking permissions', '0.000025'
'Opening tables', '0.000877'
'creating table', '0.092163'
'After create', '0.007109'
'query end', '0.000024'
'closing tables', '0.000007'
'freeing items', '0.000019'
'cleaning up', '0.000013'

Which is very slow compared to my work machine where the same query executes for 0.01~0.009. Any ideas on how can I see where is the problem coming from?


texts:
I have a question.

c stands for Commit, a stands for Abort, T is a transaction (db Transaction)

Is the following sentence true?

either c or a belong to T

In my opinion the sentence is true since if you have Abort you cannot Commit...and also the opposite is true.

So if you have c you cannot have a, a mutual exclusion.

Sorry to bother you but my teacher said I made a mistake

Thanks


texts: why pt-table-checksum lock my whole table?


texts:
I have been trying to change the port number of a SQL 2012 cluster with little success.  I had thought that I could just do this the same way as normal through configuration manager however, even with the services down in the cluster and changing the port on both nodes the old port number remains.

I have started reading about the need to remove and then add in checkpoints for the cluster but have been unable to find anything relating to SQL 2012.

I am assuming I need to use the PowerShell commands for Remove-ClusterCheckpoint and Add-ClusterCheckpoint but if I am honest I am not clear what parameters I should be using.

Has anyone done this for SQL 2012 on Windows 2012 R2?


texts: Establishing referential integrity in a generic table?

texts:
Current Schema:

 - DropDown:
     DropDownID PK,
     Name nvarchar(25)

 - DropDownOption:
     DropDownOptionID PK,
     DropDownID FK,
     Name nvarchar(25),
     Description nvarchar(155)

 - Estimate:
     EstimateID PK,
     JobDifficultyID FK(DropDownOption),
     JobTypeID FK(DropDownOption),
     etc.

 - Note there are 5+ Tables With links to DropDownOption similar to Estimate.

How can I establish referential intregity (i.e. JobDifficultyID can only point to DropDownOptions that are apart of JobDifficulty DropDown) without creating separate tables (i.e. Instead of DropDown & DropDownOption having JobDifficultyTypes, JobTypeTypes, etc)

Alternative Schema:

- JobDifficultyType:
    JobDifficultyTypeID PK,
    Name nvarchar(25)

- JobTypeType:
    JobTypeTypeID PK,
    Name nvarchar(25)

- Estimate:
    EstimateID PK,
    JobDifficultyTypeID FK,
    JobTypeTypeID FK,
    etc.

This would result in at least 13 new tables. Is this the appropriate way or is there a robust way to combine all the miscellaneous types into a single table while maintaining referential integrity?


texts:
I have query :

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

It's working and everything but it need about 20 seconds to execute.
Is something wrong in my query?
What can I do to speed up process?


texts: How to get an actual execution plan from the linked server?

texts:
There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
    Jun 15 2019 07:56:34
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

There is a linked server defined on the main server. The linked server is:
Microsoft SQL Azure (RTM) - 12.0.2000.8
    Feb 20 2021 17:51:58
    Copyright (C) 2019 Microsoft Corporation

I can't connect directly to that remote server via SSMS, but I can run all sorts of queries against it using OpenQuery. For example, I got its version by running this:
select * from OpenQuery([LinkedServerName], 'select @@version');

There are a number of complex legacy queries that are run on the remote linked server via OpenQuery.
I wanted to analyze them and see if there are any obvious/easy ways to improve their performance. To do that I wanted to get their execution plans. If I try to get an execution plan on a local server all I get is one Remote Scan operator that takes 35 minutes without any details.
I know that there is a SET STATISTICS XML ON statement that returns an execution plan of a query.
Unfortunately, when I tried to put it into the OpenQuery, it didn't work.
Let me explain.
I can run the actual query:
select * from OpenQuery([LinkedServerName],
'
--SET STATISTICS XML ON;
select TOP(10) * from bms.digitalbookinglinezone;
--SET STATISTICS XML OFF;
');

This returns me 10 rows as expected. When I uncomment the SET STATISTICS XML lines I get the following error message:

Msg 11527, Level 16, State 1, Procedure
sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0] The
metadata could not be determined because statement 'SET STATISTICS XML
ON;' does not support metadata discovery.

At the same time I can run the following query just fine:
select * from OpenQuery([LinkedServerName],
'
SELECT * FROM sys.dm_exec_describe_first_result_set
(N''select TOP(10) * from bms.digitalbookinglinezone'', null, 1);
')

And I'm getting valuable information about all columns in the remote table.
Is there any other "T-SQL" way of getting the execution plans?
Something that I could use via the OpenQuery?


texts: Should I define a FOREIGN KEY column as NULLable?

texts:
I've been developing some database structure to cover the following business rules:

An invoice has many invoice_items.
An invoice may have many refunds
A refund has many invoice_items.
All items should be from the same invoice.
A refund should always have items.

So I came up with the entity-relationship diagram (ERD) shown bellow:

I'd like to see which invoice_items are included in the refund.
However, in this case, it would not be possible to add a FOREIGN KEY (FK) (InvoiceItems => refunds). The reason is that an invoice may or may not have refunds.
So I modified the diagram as follows:

Here I could add the FK (refund_invoice_items => invoice_items)
Questions
I think this can work around the FK problem, so: Do you think this is an appropriate approach or have any ideas about it?
The main question is that if I could store NULL marks in the FK column, the first example would be better? (In my case I always add NOT NULL constraint to columns defined as FKs)


texts: Remote query (openquery) to linked server fails after upgrading to SQL Server 2016

texts:
Problem started after upgrading to SQL Server 2016 Enterprise edition.
Linked and local servers both were upgraded to ver.13.0.4435.0

I've tried the following:
 Drop/create the linked server after the upgrade to 2016. The problem persists.

This is the code snippet where error occurs:

SELECT t.NAME
    , t.Disabled
FROM openquery([Linked_Server], 'select t.Name, t.Disabled
    from [Linked_Server].[Database-1A].[dbo].[My_tbl] t where t.ID=232627') t;

And the error message:

  OLE DB provider "SQLNCLI11" for linked server "Linked_Server" returned message "Deferred prepare could not be completed.".
  Msg 5000 Could not find server 'linked server' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


texts: How can this table possibly be converted to a MySql table?

texts:
I got a TABLE in Excel format like:

This is a STEN TABLE containing FACTORS (A,B,C,D,E....etc)
Each Factor is like a separate test.
Each Factor i.e. Factor A has a [Raw Score] and a [Sten]
Supposing Factor A, had a question like:
-----------------------------------

Why do humans have Eyes?
The answer options could be like:

a) To Watch movies =  [Raw Score] -> 10,
b) To Read Novels  =  [Raw Score] -> 5,
c) To close them while sleeping = [Raw Score] ->0

So if they Chose a) then, the system will go to the STEN TABLE to get the STEN Equivalent under Factor A, in this case, the sten equivalent will be 4. (See Factor A->Row score 10-> Sten column)
What could be the most practical way to have this STEN TABLE with Factors and their Raw Scores and Stens created?
Smothing like
              **STEN TABLE**
                         |
                         |
                **FACTORS (A,B,C,D...)**
                      /     \
                     /       \
                    /         \
                   /           \
                  /             \
            **[Raw Score]      [Sten]**

EDIT 1:

To See a larger image, please click here: http://ctrlv.in/459785

Please note that, the stens are not equal for all Factors, though the Raw Scores are the same. i.e. in Factor A, [Raw Score]->3 = [sten]->2 but in Factor C  [Raw Score]->3  = [sten]->1 and in Factor F  [Raw Score]->1 [sten]->2 whereas in Factor E  [Raw Score]->1 = [sten]->1.
Thank you.
Any Suggestion is highly honored.


texts: Calculate Running Sum of Each Row From Start, Even When Filtering Records


texts: Is it recommended to use Identity as an alternative to primary key?

texts:
We can declare an Identity like id_num so that id_num will have an increment of unique numbers.

CREATE TABLE new_employees
(
  id_num int IDENTITY(1,1),
  fname varchar (20),
  minit char(1),
  lname varchar(30)
)

Is it recommended to use Identity as a alternative to Primary key since Identity provided a unique number for each row?


texts: Subquery gives no error for a non-existing column with the same name as in the outer query

texts:
I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.

I run this query:

select * from t1 where c1 in (select c1 from t2);

The above query should give an error as c1 is not present in t2. Instead, it returns all the rows from t1. Another version of the above query with delete which can be much more disastrous:

delete from t1 where c1 in (select c1 from t2);

The above query deletes all the rows from t1 when it is just supposed to give an error.

I have noticed this behavior occurs only when the column in the subquery has the same name as the outer one. Meaning,

select * from t1 where c1 in (select c3 from t2);

will throw an error as expected:

 ERROR 1054 (42S22): Unknown column 'c3' in 'field list'

By the way, I have checked for the same issue on PostgreSQL 9.6.3 and the behavior is exactly the same. Any explanation for this strange behavior?


texts: When to use full backup or full backup and then differential backup

texts:
There is a SQL Server 2012 database that has the following configurations

Recovery mode : Simple
Backup Schedule : Full backup at 01:00 hours daily as well as differential database backup 13:00 hours daily

Once the differential backup of this database failed at 14:00 hours, the database then failed.

I need to restore this database and ensure the data loss is minimal.

Shall I choose

Restore the latest full backup, or
Restore the latest full backup, then restore the latest differential backup


texts: Join results and sum of likes in a different table

texts:
I have two databases, articles and article-likes.
I need the query to select everything from the article and add a row which is the sum of likes (we have -1 for dislike and +1 for like) from the table article-likes by searching for their ID.

Articles table:

 Article-Likes table:

For this instance, the article with ID Lorem-Ipsum should have 2 likes. On the other hand, Sit-Amet should have 1 like (1 + -1).


texts:
I have 50 lac rows (i.e. 5 million rows) in student table.

I fire select * from student where s_name like('%something word%') limit 0,5.

I define indexing on s_name column.

That query takes 2 minutes or more sometimes.

I want quick response within 5 seconds or faster for that type of query.

How...?

I tried the below query also:

SELECT * FROM (select * from student  ORDER BY s_name LIMIT 0,10) as s
WHERE `status` = 'active' AND ( `s_name ` LIKE ('%a%') or `f_name ` LIKE ('%a%'))
ORDER BY s_name LIMIT 0,5

Code taken and adjusted from: search for something in the first 1000 records in the table


texts:
I am regularly backing up the database using the SQL Server UI (Tasks --> Backup) with Backup Type:Full. Each time I backup, the .bak file gets bloated. If I delete the .bak file and do the backup, then 50 MB .bak file is created instead of the 500 MB file that I deleted.

What is the reason behind this?


texts: High CPU usage on Azure SQL Server ~1.6m rows

texts:
I'm currently running into an issue with one of our databases in Azure where 1 table is forcing us to scale the entire database up to cope with the CPU requirements. The basic structure of the table is as follows:

column_a is unique and not nullable. column_b can be null.
This table has ~1.6 million rows in it so it's not a large table but it is used throughout the system we have built. We have several feeds pushing data in which requires an entry in this table so for each new request we receive there is a check for an entry and then if that doesn't exist one is created and the id returned. Once that data is received, we have subscribers which receive that data and use this table to link other information the system holds in a nice interface.

The table above shows the most expensive queries being run on this database over a 24 hour period. The one highlighted in yellow is SELECT TOP (1) id FROM TableName WHERE column_a = @param1.
Is this just bad database architecture or are we missing a trick to optimize for the number of reads we are performing on the table? Unfortunately, the number of reads being performed is only going to increase as we are adding new data feeds every month and we are aiming to have 5 - 10x the number of feeds by the end of this year.
Any help is much appreciated and my apologies if any of the above is unclear.
Edit
Create Table query
CREATE TABLE [dbo].[TableName](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [column_a] [varchar](50) NOT NULL,
    [column_b] [varchar](50) NULL,
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [AK_Column_A] UNIQUE NONCLUSTERED
(
    [plate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Edit 2
Please understand that I have had to redact the names.


texts: How to rollback after wrong update statement in sql server 2008R2?

texts:
select * from aa

update aa set City='chennai',LastName='vinoth';

ID  FirstName  LastName  City
29  Abcrdrr    vinoth    chennai
1   John       vinoth    chennai
2   Joe        vinoth    chennai
35  raja       vinoth    chennai
38  Johsdfgn   vinoth    chennai

I have wrongly updated Lastname, City columns in all the rows. Now I want to rollback to the old table rows.

Using SQL-Server 2008R2.


texts:
I have some problems with my mysql database.

I configured a server with the database and other services (apache, php) and put run multiple applications.

There are two problems.

Every day at the same time mysql fails, and I must go and restart it.
Every time I reload the website many times continuously mysql falls, and again and need to restart it.

Mysql logs show:

140107 0:09:29 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use$
140107 0:09:29 [Note] Plugin 'FEDERATED' is disabled.
140107 0:09:29 InnoDB: The InnoDB memory heap is disabled
140107 0:09:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140107 0:09:29 InnoDB: Compressed tables use zlib 1.2.3.4
140107 0:09:29 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
140107 0:09:29 InnoDB: Completed initialization of buffer pool
140107 0:09:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140107 0:09:29 [ERROR] Plugin 'InnoDB' init function returned error.
140107 0:09:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140107 0:09:29 [ERROR] Unknown/unsupported storage engine: InnoDB
140107 0:09:29 [ERROR] Aborting
140107 0:09:29 [Note] /usr/sbin/mysqld: Shutdown complete

What's wrong with my database?


texts: CTE returns an empty set even though anchor is non-empty

texts:
I'm got an adjacency list consisting of two tables:

CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY);
CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL,
    UNIQUE KEY (parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE);

When I run the following CTE I get an empty set:

WITH RECURSIVE cte (parent_id, child_id)
AS
(
  (
    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763
  )
  UNION ALL
  (
    SELECT recursive.parent_id, recursive.child_id
    FROM cte, permission_graph recursive
    WHERE recursive.child_id = cte.child_id
  )
)
SELECT cte.parent_id, cte.child_id
FROM cte

But if I run the anchor condition:

SELECT anchor.parent_id, anchor.child_id
FROM permission_graph anchor
WHERE anchor.child_id = -32763

I get:

[parent_id = -32767, child_id = -32763]
[parent_id = -32768, child_id = -32763]

Why is the CTE returning an empty set when the anchor result is non-empty? Shouldn't the CTE result contain the anchor result?


texts:
I need a query that returns something in this format:

pk  id  v1 v2 v3 v4 v5
----------------------
...
pk1 id1 A1 A2 A3 A4 A5
pk6 id2 B1 B2 B3 B4 B5
...

My current data looks like this

CREATE TABLE foo(pk,id,value)
AS VALUES
    ( 'pk1' , 'id1',  'A1' ),
    ( 'pk2' , 'id1',  'A2' ),
    ( 'pk3' , 'id1',  'A3' ),
    ( 'pk4' , 'id1',  'A4' ),
    ( 'pk5' , 'id1',  'A5' ),
    ( 'pk6' , 'id2',  'B1' ),
    ( 'pk7' , 'id2',  'B2' ),
    ( 'pk8' , 'id2',  'B3' ),
    ( 'pk9' , 'id2',  'B4' ),
    ( 'pk10', 'id2',  'B5' )
;

The source table has ~160,000,000 rows. All columns are indexed (btree).

Currently I'm doing 5 (the real number is dynamic and maybe around 30, but for example above it's 5) different queries to extract:

1:

P1 id1 A1
P6 id2 B1

2:

P2 id1 A2
P7 id2 B2

3:

P3 id1 A3
P8 id2 B4

etc

and then join these queries. Something like this:

SELECT q1.pk,q1.id,q1.v1,q2.v2,q3.v3,q4.v4,q5.v5 FROM (SELECT pk, id, value FROM table WHERE id=1) AS q1, (SELECT pk, id, value FROM table WHERE id=2) AS q2, (SELECT pk, id, value FROM table WHERE id=3) AS q3, (SELECT pk, id, value FROM table WHERE id=4) AS q4, (SELECT pk, id, value FROM table WHERE id=5) AS q5 WHERE q1.id=q2.id and q2.id=q3.id and q3.id=q4.id and q4.id=q5.id;

But as I mentioned above number of joins are much more (~30) and it runs really slow on Postgres. I examined the planning and disabled nestloop and it got much faster but it's still too slow. If I fetch all these queries to memory and join them programmatically (using python for example) it runs in ~1 second. But Postgres takes too long (~30 seconds for joining 110 rows).
Any ideas? I think there must be a better way than making 30 subqueries and joining them. Another approach would be grouping rows based on id column and do some magic (for example with RowNumber()) to generate the desired table.

P.S. It's not applicable to pre-generate the result table. Everything must happen runtime.


texts: Two-column foreign key constraint only when third column is NOT NULL

texts:
Given the following tables:

CREATE TABLE verified_name (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  UNIQUE (name, email)
);

CREATE TABLE address (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  verified_name_id INTEGER NULL REFERENCES verified_name(id)
);

How can I add an additional constraint that when address.verified_name_id is not NULL, the name and email fields on the address must match the those on the referenced verified_name?

I've tried adding the following to address:

FOREIGN KEY (name, email) REFERENCES verified_name(name, email)

...but that constraint is being applied even when verified_name_id is NULL.

I'm looking for something similar to the partial index syntax with a clause like WHERE verified_name_id IS NOT NULL, but simply appending a clause like that to the FOREIGN KEY constraint doesn't work.

Current undesirable solution:

I can add the following constraints to verified_name:

UNIQUE (name, email),
UNIQUE (id, name, email)

And the following constraint to address:

FOREIGN KEY (verified_name_id, name, email) REFERENCES verified_name(id, name, email)

...but that creates an extra constraint on verified_name that I'd prefer not to have (it's a valid logical constraint, but it's also superfluous and there are minor performance implications).


texts: Creating a View using a query created by sp_executesql

texts:
I posted a question about creating a dynamic sql statement and got the following code which fulfills this task:

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR
    select name
    from sys.tables
    where name like 'DDOS_WAF_ACCOUNT_%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @TableName
    FETCH NEXT FROM cursor1
    INTO @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

Print @DynSQL
exec sp_executesql @DynSQL

Now executing this code displays the table which I want to store for later use. I was told that I should use a view that stores these results but I dont know how to do that. Can I create a view that is populated by this dynamic sql statements and sotre it for repeated use?
Thank you all.


texts: Defragmentation - Rebuilding Indexes SQL Server 2005

texts:
I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for:

ALTER INDEX ALL ON mytablename
REBUILD WITH(ONLINE = ON)

When I pull the info from sys.dm_db_index_physical_stats, I see the percents are very high - 70, 80 and 90 percent (!). So that tells me I want a REBUILD, and not a REORGANIZE.

I have a few question before executing these REBUILDS.

Can it be ran while processes are using the database? (ONLINE = ON
tells me yes, but I want to confirm it won't crash anything.) Or is
it better to run when it's not in use?
I read REBUILD makes things run slower. Is that just while the
indexes are being rebuilt? (or forever after)
How long will it take to rebuild all indexes / or rather each one?
Are there any side effects or other info that I need to be aware of? This is production/live database.

EDIT:
And finally, what is the best way to go about rebuilding it? Looping through all the objects with a percentage of greater than 30? Or?

Thank you!


texts: will table partitioning and compression help heavily used OLTP database?

texts:
We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to heavy blocking, we can't purge data while the database is online.
My boss is saying by partitioning and table compression will help the disk space and overall performance. He is from OLAP DW area. But I highly doubt this will work in OLTP. How do I convince him this will not work? Any articles?


texts:
I am trying to set up a simple database for the first time using MySQL Workbench. I'd like to update the foreign keys of the child table based on the values of parent_child.csv. Here are the current MySQL tables I have right now.

parent table

id_num  parent_id
------------------
33      parent_1
29      parent_2
46      parent_3
17      parent_4
...     ...

child table

id_num  child_id  parent_id_num
--------------------------------
22      child_1   NULL
13      child_2   NULL
52      child_3   NULL
76      child_4   NULL
...     ...       ...

And here is what parent_child.csv looks like:

parent_child.csv

parent_id  child_id
--------------------
parent_1   child_3
parent_1   child_4
parent_2   child_1
...         ...

Here is what I'd like the updated child table to be:

child table

id_num  child_id  parent_id_num
--------------------------------
22      child_1   29
13      child_2   NULL
52      child_3   33
76      child_4   33
...     ...       ...

Assume that not all parent_id and child_id's are in parent_child.csv. So not all child_id's get updated.

How would I go about doing this? Would it require me to create a parent_child table in MySQL?

Here's what I want to do in a nutshell:

for every child_id in parent_child.csv:

get child_id's parent_id
find parent_id's id_num using parent table
replace child_id's foreign key (referred to as parent_id_num, set currently to NULL) with the found parent id's id_num


texts: Test MySQL credentials from Linux command line?

texts: How do you test MySQL credentials from the command line on a Linux server?


texts: Mirror log empty on primary but fine on the mirror

texts:
We have dozens of principle servers and corresponding mirrors, most are working fine, however, 1 of them is acting strange. Specifically, the sp_dbmmonitorresults proc returns all columns as NULL for a given principle database whereas sp_dbmmonitorresults on the mirror returns valid information:

Now, i have a rough idea what the cause was; when the principle and mirror servers where setup, the regional settings where incorrect on the primary (the time was correct but was using a +4 region (America etc), the time region was corrected to GMT +0 AFTER the mirroring was setup (school boy error i know, but c'est la vie!)

Edit: 19/12/2012

Today i removed the mirroring, deleted the copies on the mirror and re-setup the mirroring, however this did not solve the problem! I'm going to pop a bounty on this question to try and get to the bottom of this.

To confirm, if i run EXEC sp_dbmmonitorresults @database_name = 'ProScript' manually on each server it returns NULL for most values on the primary but returns fine on the mirror, as shown:

When Ran on primary:

Zoom: Click for full size version

When Ran on the mirror:

Zoom: Click for full size version

As you can see, the times are current and the database is synchronised but the log_generation_rate, unsent_log, send_rate etc vare all NULL on the primary?

Both servers have the montior job running every minute with exec sys.sp_dbmmonitorupdate as a job step etc.

Looking at the source of sys.sp_dbmmonitorupdate it grabs these values from sys.dm_os_performance_counters - so if i run the following on the mirror:

SELECT  counter_name ,
        cntr_value
FROM    sys.dm_os_performance_counters
WHERE   instance_name = 'ProScript'
        AND counter_name IN ( N'Log Send Queue KB', N'Log Bytes Sent/sec', N'Redo Queue KB', N'Redo Bytes/sec', N'Transaction Delay', N'Log Bytes Flushed/sec', N'Transactions/sec' )

I get good results:

However, if i run the same SQL on the primary there are no rows!

This would suggest that the sys.dm_os_performance_counters table isnt being populated for the mirroring? What would cause that???


texts:
If at all File stream option is enabled.

Will sp_helpdb show the database size which includes even FileStream Data.
Or only .mdf and .ldf sizes ?


texts:
Is there a upper bound for an array column?

I am getting this error when inserting into the array field -

PG::Error: ERROR:  index row size 3480 exceeds maximum 2712 for index "ix_data"

Here's my table definition -

create table test_array(id varchar(50), data text[]);

ALTER TABLE test_array ADD PRIMARY KEY (id);

CREATE INDEX ix_data ON test_array USING GIN (data);

I need an index on array field, since I am doing some lookups on it.


texts: Identify cached data for a specific query in data cache

texts:
I'm trying to identify, if possible, how long it takes for data being cached by a stored procedure to be taken out of the cache.

I know there is the PLE counter in sys.dm_os_performance_counters, but I think in my scenario that won't help me very much.

Disclaimer, all of this was handed down to me to improve:

I have a stored procedure which returnes about 250 Mb of data into the cache, when being ran by one user. The procedure returns user-specific information, so the procedure is called with a @userID input parameter.

From the data returned by this stored procedure (about 3000 rows) it displays row-specific information, for one row at a time. There is a functionality that when a user presses a "Next" button in the application UI it goes and gets the row-specific information for the next row in the result and so on.

Due to the data already being in cache, when testing with a single user the response time was ~1 second, because all of the 250 Mb would fit in cache without any problem and not being cleared very fast.

I've tested the procedure with a cold cache and the execution time was about 10 seconds to return the data.

Now, I have about 30 users running the stored procedure in the same time interval and they have reported experiencing about a 10 second delay when pressing the "Next" button.

Surprisingly this is the same exec. time when pushing data into the cache and because there are 30 users, my conclusion at this point is because the total amount of data being pushed into cache is about 7Gb (total server memory is 16Gb), the data gets pushed in and out of cache very often.

This is all just a theory at this point, but I'm trying to identify some metrics that directly point at this being the issue. I know PLE could be an indicator, but is there anything that could be more specific, for a query for example.

My recommendation would be to re-write the stored procedure in a more efficient way, but that would take lots of time and business is not very interested in spending time for that. If I can prove, with this theory of mine, that there is no way, then they might allow for a re-write of the procedure.

Oh, and crazy enough, they're not very happy about increasing server memory either. Talking about not giving me options..


texts: Is Oracle more demanding environment for a DBA than SQL Server?

texts: I don't know Oracle at all but I have heard Oracle DBA's saying that working with Oracle (as a DBA) is "harder" and more difficult and more demanding than working with SQL Server. Does this statement have a basis?


texts: sys.dm_db_index_usage_stats showing incorrect figures for last_user_update

texts:
As the title suggests, I seem to be getting unexpected readings from the last_user_update column in the index_usage_stats DMV. It seems to update the datetime value everytime I run a script inserting values to a table. The script however has a left join to the target table and excludes any values which exist.
Whenever I run the script the rows affected for the tables in question always returns 0 and the row counts in the table do not change. Yet at anypoint at which I run the script, last_user_update for some reason seems to change despite no rows being modified. Is there a reason for this?

SELECT
     DB_NAME(database_id) DBName
     OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id)
    ,last_user_update
FROM
    sys.dm_db_index_usage_stats stats
WHERE
    database_id = DB_ID()


texts: What are the ports and directionality of the TCP connections used in MySQL replication?

texts:
I wanted to know how replication works in mysql.

Both slave and master have mysql server running say on port 3306.
Slave acts like  a client and master acts like a server.

Does slave always bind to 3306 when it makes a tcp connection to send the request? When the response is sent back by server the dest port will be 3306 and since slave mysql is listening to that port it will process the response?

Not sure if my understanding is correct.

Also I am interested in knowing ingress and egress rules that should be added for replication to work.

For TCP connection from slave will it pick any available TCP port or is it always 3306?

Any help is appreciated!

Thanks!


texts:
I'd like to produce the generic quarter end date for a given date.

Ex:If I have 2010-01-01, I would like to return 2010-03-31, and so on.

I can get the quarter number and year:

select to_char(date_trunc('quarter',  current_date)::date, 'yyyy-q');

Which returns 2017-3 since today is 2017-07-14

How do I get the quarter end date nicely?

I can get the answer, but its very ugly:

select to_char(date_trunc('year',  date '2015-01-01'),'yyyy') || '-' ||case
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 1 then '03-31'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 2 then '06-30'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 3 then '09-30'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 4 then '12-31'
    else '?'
    end

Which returns 2015-03-31 since I put in 2015-01-01.

Is there a better way?


texts: Error configuring mongodb replicaset with inmemory

texts:
I'm trying to configure a MongoDB replicaset with 3 replicas. The primary and another use the in memory storage engine, and the third uses wired tiger. I am using this configuration.

When I restart the cluster the data does not persist.

Server has startup warnings:

** WARNING: This replica set node is running without journaling enabled but the
**          writeConcernMajorityJournalDefault option to the replica set config
**          is set to true. The writeConcernMajorityJournalDefault
**          option to the replica set config must be set to false
**          or w:majority write concerns will never complete.
**          In addition, this node's memory consumption may increase until all
**          available free RAM is exhausted.

** WARNING: This replica set node is using in-memory (ephemeral) storage with the
**          writeConcernMajorityJournalDefault option to the replica set config
**          set to true. The writeConcernMajorityJournalDefault option to the
**          replica set config must be set to false
**          or w:majority write concerns will never complete.
**          In addition, this node's memory consumption may increase until all
**          available free RAM is exhausted.

And my configuration.

rs0:PRIMARY> cfg = rs.conf()
{
        "_id" : "rs0",
        "version" : 102744,
        "protocolVersion" : NumberLong(1),
        "writeConcernMajorityJournalDefault" : false,
        "members" : [
                {
                        "_id" : 0,
                        "host" : "mongodb-mongodb-replicaset-0.mongodb-mongodb-replicaset.default.svc.cluster.local:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "mongodb-mongodb-replicaset-1.mongodb-mongodb-replicaset.default.svc.cluster.local:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "mongodb-mongodb-replicaset-2.mongodb-mongodb-replicaset.default.svc.cluster.local:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : true,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : -1,
                "catchUpTakeoverDelayMillis" : 30000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5e736baa5c51ff3e198dd880")
        }
}

The writeConcernMajorityJournalDefault option IS set to false!

Thanks in advance!


texts: SQL Server 2012 In-Place Upgrade and Timing of Database Version Change

texts:
We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases can no longer be moved back to a 2008R2 instance).

Does this change to the databases occur during the upgrade itself or the first time that the databases are brought online when the server restarts?


texts: best column type for hashed data in mysql

texts:
In my use case I have a table with a column containing hashed (sha256) IDs. I wonder which data type is better considering that It must be unique and I need to search on it.

The sha algorithm produce a binary stream out of the box. I can transform it hexadecimal or base64 before storing it. I wonder if it is convenient or necessary for my needs


texts:
I wonder what are the optimal Linux kernel parameters for oracle 11g db on 64-bit system.

I didn't find any detailed, good or full recommendations or best practices for this issue. Just found one recommendation that on 64-bit system it's good to set shmax half of the RAM.


texts:
I was wondering if there is a free tool to monitor state of SQL Server 2000. I would prefer a web tool.

I would like to see if some database is accessible, or are there any problems on server, etc. Also I would like not to put to much extra load on SQL Server.

If anybody knows tool like this, please help

Thanks


texts: postgres simple number to string mapping

texts:
I have a an Enum at the programming language level which is stored as a simple integer on the table. Think:

APPLE = 1
GOOGLE = 2
MSFT = 3
AMAZON = 4
 ... (100s more)

I just wanna query the table and instead of the number return the corresponding string value. Is there an easier way of doing this without using case statement or a temp table:

SELECT
  CASE WHEN type = 1 THEN "APPLE"
  CASE WHEN type = 2 THEN "GOOGLE"
  CASE WHEN type = 3 THEN "MSFT"
  CASE WHEN type = 4 THEN "AMAZON"
  ...
  ELSE "UNKNOWN"
FROM t

Basically it's just a key look-up in a dictionary.