texts: Why does simple ALTER TABLE command take so long on table with full-text index?

texts:
I have a large (~67 million rows) name-value table that has full-text indexing on the DataValue column.

If I try to run the following command:

ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;

It runs for 1 hour 10 minutes and still does not complete on a VisitorData table that contains ~67 million rows.

Why is this taking so long and not completing?
What can I do about it?

Here are more particulars about the table:

CREATE TABLE [dbo].[VisitorData](
            [VisitorID] [int] NOT NULL,
            [DataName] [varchar](80) NOT NULL,
            [DataValue] [nvarchar](3800) NOT NULL,
            [EncryptedDataValue] [varbinary](max) NULL,
            [VisitorDataID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_VisitorData_VisitorDataID] PRIMARY KEY CLUSTERED (
            [VisitorDataID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [UNQ_VisitorData_VisitorId_DataName] UNIQUE NONCLUSTERED (
            [VisitorID] ASC,
            [DataName] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
ADD  CONSTRAINT [UNQ_VisitorData_VisitorDataID] UNIQUE NONCLUSTERED (

[VisitorDataID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
    WITH CHECK ADD
        CONSTRAINT [FK_VisitorData_Visitors] FOREIGN KEY([VisitorID])
        REFERENCES [dbo].[Visitors] ([VisitorID])
GO

ALTER TABLE [dbo].[VisitorData]
    CHECK CONSTRAINT [FK_VisitorData_Visitors] GO

CREATE FULLTEXT CATALOG DBName_VisitorData_Catalog WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON VisitorData ( DataValue Language 1033 )
    KEY INDEX UNQ_VisitorData_VisitorDataID
    ON DBName_VisitorData_Catalog
    WITH CHANGE_TRACKING AUTO
GO

The wait types that are occurring during the ALTER TABLE command are LCK_M_SCH_M (schema modification), as per the query results below:

select * from  sys.dm_os_waiting_tasks

waiting_task_address    session_id exec_context_id wait_duration_ms     wait_type            resource_address       blocking_task_address   blocking_session_id blocking_exec_context_id resource_description
--------------------             ----------     --------------- --------------------              -------------------- ------------------             ---------------------            -------------------        ------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0000000000B885C8   54               0                   112695                            LCK_M_SCH_M   0x00000000802DF600 0x000000000054E478     25                            0                                         objectlock lockPartition=0 objid=834102012 subresource=FULL dbid=5 id=lock438a02e80 mode=IS associatedObjectId=834102012
0x0000000000B885C8   54               0                   112695                            LCK_M_SCH_M   0x00000000802DF600 0x00000000088AB048    23                            0                                         objectlock lockPartition=0 objid=834102012 subresource=FULL dbid=5 id=lock438a02e80 mode=IS associatedObjectId=834102012

I'm working with production servers that are running SQL Server 2005 SP 2 (soon to be upgraded to 2008 SP2).


texts: Interpret the CPU usage in SQL trace file

texts:
During our performance evaluation, we run one load simulation with SQL trace on. And while analyzing the trace file I want to calculate the CPU usage. How to do it? (Please refer the screenshot attached)

So for the physical read, we observed a CPU spike but don't know how to interpret it.

Tha value showing for 53,682,888 reads, the CPU utilized is 1,254,911. Is it in a microsecond or in cycles?


texts: How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

texts:
I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time).

As I understand it, the DATETIME is set to whatever it is set to - and the reporting comes back with the same value, no matter what time zones are in place on the server or client.

In the MySQL documentation, it sounds like TIMESTAMP values are reported in the current timezone, but stored in UTC.

In this situation then, subtracting the DATETIME value (earlier event) from the TIMESTAMP value should be possible without a lot of timezone conversion - because the DATETIME is GMT and the TIMESTAMP value is UTC. However, doing this accurately - and understanding it - is proving to be a problem.

I have situations where I need (DATETIME - TIMESTAMP) in minutes and in days. If we assume that column d is DATETIME and column t is TIMESTAMP... So far, I've tried:

DATEDIFF(d,t)
TIMEDIFF(d,t)
UNIX_TIMESTAMP(d) - UNIX_TIMESTAMP(t) - 1800 (accounting for DST)

Our time zone here is US Central Time - currently Central Daylight Time - GMT-6 for CST and GMT-5 for current CDT.

It also looks like DATEDIFF converts both values into dates then compares them - instead of comparing time between the values and dividing by the number of 24 hours in each. Thus, I don't think DATEDIFF will work directly for me anyway.

The specific questions, then are:

How do you compare a DATETIME in GMT with a TIMESTAMP value? Specifically, finding the number of minutes between the two, assuming the TIMESTAMP value was generated last.
What's the best way to get the difference in 24 hour slots between the same two values? I assume that it would be - using A1 as the answer to the above question - TRUNCATE(A1 / (24.0 * 60.0)) ...

UPDATE: Tried using the CONVERT_TZ function with the values of US/Central and GMT. However, the CONVERT_TZ function with such specifications appears to read the setting of Daylight Saving Time from the current system time and not from the time as read from the database field. This means that when the data should be taken as a date in CST, it is read as CDT instead.

I keep thinking it's got to be simple to get the UTC representation of a TIMESTAMP field - which would simplify things, seems to me.

UPDATE 2: Looks to me like I mispoke regarding CONVERT_TZ. I'm using this function and it seems to work:

TIMEDIFF(CONVERT_TZ(t),'US/Central','UTC'),d)

Even over a time zone change (from CST to CDT) it seems to work...


texts: Invalid Column Name Error after ALTER and UPDATE

texts:
I am trying to UPDATE a specific column within a table, but I get an error due to the fact that when SQL compiles, the column name IssueTimeUTC does not actually exist. Here is my sample code:

   WHILE  @startissuetime<='23:30'
        BEGIN
        ALTER TABLE Intraday_Forecast_temp
            ADD IssueTimeUTC SMALLDATETIME

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
            WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
            WHERE

DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
            END

I have read multiple similar posts, but I cannot make anything work therefore I am kindly asking you if you could help me.

The error I get is this:

  Msg 207, Level 16, State 1, Line 56 Invalid column name 'IssueTimeUTC'.

Update: So basically I was not able to find an exact solution to this specific problem but I just found a way to go ''around'' the problem instead. So this is the updated code I used.

WHILE  @startissuetime<='23:30'
    BEGIN
        ALTER TABLE Intraday_Forecast_temp
        DROP COLUMN IssueTimeUTC
        --------
        BULK INSERT.....
        --------
        ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
    END

I know that this is not probably the most elegant solution but I initially defined the Intraday_Forecast_temp table WITH the column IssueTimeUTC , then I drop it and add it again. This way, SQL stop complaining that the column does not exist upon compilation :)


texts: Insert bulk Parent and Children rows using SQL Query

texts: We receive bulk data from our customers in a spread sheet. I loaded them in a temporary table as of now. I tried to normalize the data and create a parent table and each parent to have 4 or 5 child rows. Is there a way to insert all the parents and their children using queries? I am using MSSQL


texts:
Fellow DBAs and Engineers,
I'm looking for few quality Database security metrics. Purpose of these metric is to demonstrate level of security provided to database schemas in the organisation. Specifically, I'm looking for security metrics which can show state of confidentiality and integrity provided to the DBs in the environment.
As an example:

Percentage of DBs encrypted and Redacted.
Percentage of DBs hosting PII information.

Metric request is platform agnostic but mostly pertaining to Oracle environment in hosted DB environment.
Let me know if you have any questions.
Regards
~ RS


texts: This query takes anywhere from 1 - 30 seconds depending on server load... How can it be optimized?

texts:
SELECT id FROM watch_list WHERE item_id = '16837' AND user_id = '2582' LIMIT 1;
# Time: 171025 20:41:38
# User@Host: dylanw68_actmgr[dylanw68_actmgr] @ localhost []  Id: 70246342
# Schema: dylanw68_tauction2  Last_errno: 0  Killed: 0
# Query_time: 1.268647  Lock_time: 0.000031  Rows_sent: 0  Rows_examined: 14760  Rows_affected: 0
# Bytes_sent: 91
SET timestamp=1508982098;

Each row only has 4 values so I am really confused on why this simple query takes so long.


texts:
I am looking to change the timezone in a quite large Oracle database. What are some of the issues I might run into when trying to change the timezone in this way?

I don't want to 'trash' the database and am thinking of ways that the database might get trashed by changing TimeZones.

Any suggestions?


texts: How to alter database hour and date in Oracle?

texts: How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.


texts: PostgreSQL how to get case insensitive schema name

texts:
I created a schema with uppercase letters like this:

CREATE SCHEMA "requeteSQL"  AUTHORIZATION ......;

Now when I try to GRANT some privileges like :

GRANT SELECT ON ALL TABLES IN SCHEMA requetesql  TO ....;

I get the following error :

ERROR:  schema "requetesql" does not exist


texts: Need help to compare pricing using SQL queries

texts:
Thank you for help in advance.

I have 2 sets of data that show bus journeys and there relevant price from 2 different systems that i need to compare.

Table1
-Brand
-Origin
-Destination
-Price

Table2
-Brand
-Origin
-Destination
-Price

So what i need todo is to match all the following fields and return the price from table2 which i have done using the following:

SELECT Table1.Brand ,
       Table1.Origin,
       Table1.Destination,
       Table1.Price,
       Table2.Price
FROM Table1
  LEFT JOIN Table2
         ON Table1.Brand = Table2.Brand
        AND Table1.Origin = Table2.Origin
        AND Table1.Destination = Table2.Destination

ResultTable:

Brand  |  Origin  |  Destination  |  Price  |  Table2 Price  |

So up to now I have the correct data however i need to the following which i could really do with some help:

Compare Table1.Price against Table2.Price and display the difference in the ResultTable above
Filter out all prices that are the same so all we have left is the different prices
A new query where we compare the Brand, Origin and Destination and if any are missing from table 2 we display those

Sorry for the long winded explanation but just thought i would try to make it as explicit as possible.


texts: How to selectively replicate a PostgreSQL database

texts:
I would like to keep two PostgreSQL databases with the same schema, but have one replicated from a master that only keeps a small portion of the live data.

Basically the master takes in live data and deletes old data outside of the time window, while the replicated slave should keep all the historical data (at least up to the limits of the disk). So in essence, the slave should replicate the master except its deletes.

Is this possible with any "off-the-shelf" PostgreSQL replication method?


texts:
Trying to use this method to get the rank position of $associateID. Everything works except FIND_IN_SET. It's not reading alias1 or alias2. Does FIND_IN_SET work with alias's? How can I get this to work?

$associateID="3";

$query = "
    SELECT
        associate_ID,
        COUNT(*) as alias1,
        ( SELECT COUNT(*)
          from tableA
          WHERE tableA.column='$associateID'
        ) as alias2,
        FIND_IN_SET( alias2 ,
                     ( SELECT GROUP_CONCAT( alias1 ORDER BY alias1 DESC )
                       FROM tableA )
                   ) AS alias3
    from
        tableA
    GROUP BY
        column
";


texts: Change column datatype without rewrite when data remains unchanged

texts:
For example:

timestamp(tz) to int8
date to int4
json to text

The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly and instantly.

How do I check if this is actually the case?
If it is not the case (table gets rewritten), how do I make it do it without rewriting?


texts:
I want to get statistics wal. We need to stop replication for a while so l want to know query for getting how much size of WAL produce per hour.
In Oracle it just get redo log switch count from statistics table. Is there something like this in PostgreSQL?

Thanks.


texts: In PostgreSQL, how do I make it so that either column A or column B must be non-null?

texts:
I have a table with an "e-mail address" and "P2PMail address" column. The user is required to either enter an e-mail or P2PMail address.
If I set both to NOT NULL, then both must be filled in for a record to be created.
If I allow both to be NULL, then a user would be entirely unreachable.
If I set a UNIQUE CONSTRAINT on both columns, then the combination of them must be unique, but it says nothing about what I'm trying to do... unless I'm mis-thinking here.
What is the correct way to accomplish this? I fear that the answer is something unpleasant such as "triggers" or something. (I was never comfortable using those.)


texts: Unix Epoch timestamp for now() in postgresql

texts:
I am trying to update one field as epoch from now() but I am getting as '15872897243629.2188' fraction format. What I need is the exact epoch from now() without fraction values like '15872897243629'

here the query I used

UPDATE tablename SET
column1= extract(epoch from now()),
column2= extract(epoch from now())
where column3= 'SUCCESS';


texts:
I'm working with a SQL Server with several instances. Four of my instances are at 10.50.2811.0 and the remaining two are at 10.50.1765.0

I've tried running SQL Server 2008 R2 SP1 and CU 6 to get it updated but the installer wont run. I receive the error:

  To continue, select a feature to install. If all features are already selected, these features are installed.


texts: What is the difference between SQL Server RLS and Oracle FGAC. How to decide which one to go for? We already have both the products in place and teams to support the products.


texts: What are exact benefits of using R services on Enterprise vs Standard?

texts:
I am currently using SQL Server 2016 SP standard edition and want to install R services. In the documentation is said that a stand alone installation is allowed only on Enterprise (Machine Learning Server (Standalone)).

Also, I found a post, where the guy is talking that on standard I am not able to use parallel operations and there are memory limitations:

Could anyone tell what are the exact memory/processors limitations?

I need such details as going to use heavy R calculations and the tests for now show that the implementation in the standard edition is not working as I hoped.


texts: Is it mandate to turn off huge pages in Mysql 5.6

texts:
Folks,

Is it required to turn off huge pages in Mysql 5.6 server under /sys/kernel/mm/transparent_hugepage/enabled  -> To never?

We have a heavy transactional database and calling the data segments quite often for a few particular tables by its index.

How does a memory foot-print work here? Would it help or degrade the performance.

In AnonHugePages:  109242368 kB ( I see entire innodb buffer pool size is occupied under AnonHugepages).

No specific issues found so far due to memory. But need an advise on when to keep it on and when to turn it off.


texts: 'mysql' Recognized by cmd but not PowerShell?

texts:
I'm trying to run a very simple login script through powershell.

I have already added the default directory "C:\Program Files\MySQL\MySQL Server 5.7\bin" to the PATH variable. Now I was able to call mysql from command line prompt.

However when I tried to run the same script from PowerShell it showed that " The name 'mysql' is not recognized as the name of cmdlet, function, script file......"

My computer is running on Windows 10 64bit.


texts: Creating Auto Number field with preceding number of 0's?

texts:
Scenario: I have a table with a (int4) field 'pg_recordid' that has newly inserted records auto-increment the new record id using this macro after running 'SERIAL' in the field (in Navicat)

nextval('raw_casper_pg_recordid_seq'::regclass)

The current output for the first record: 1

Is there a method to set the macro so the Auto numbers have the entire number length filled with 0's for unused digits? example: 000001


texts: does INSERT INTO ... ON DUPLICATE KEY UPDATE executes even if the row has not changed?

texts:
Let me explain :

Let us assume we have a database "user" :

ID   Name   Surname
===================
1    John   Doe
2    Foo    Bar
3    Foo    Baz

Now I wanna change all these three rows. I will throw the following insert :

INSERT INTO user (ID, Name, Surname) VALUES (1, 'John', 'Conor') ON DUPLICATE KEY UPDATE ID = 1, Name = 'John', Surname = 'Conor';
INSERT INTO user (ID, Name, Surname) VALUES (2, 'Foo', 'Bar') ON DUPLICATE KEY UPDATE ID = 2, Name = 'Foo', Surname = 'Bar';
INSERT INTO user (ID, Name, Surname) VALUES (3, 'Foo', 'Baz') ON DUPLICATE KEY UPDATE ID = 3, Name = 'Foo', Surname = 'Baz';

Question

Does the command ON DUPLICATE KEY execute itself if the row before the update was the same ? Meaning, does it will execute for the rows with ID equal to 2 and 3 even if it should not executes (because it is useless) ? I ask this question for performance issues.


texts: How to create a login and user that can only access one schema in one database

texts:
I have executed the following:

USE [MyDatabase];
GO
CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase];
CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount];
GRANT EXECUTE   ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];
GRANT SELECT    ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];
GRANT INSERT    ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];

(I am not granting any roles as I do not want this login to be able to access any other schema in this database.)

I run SSMS as MyDomain\MyAccount (run as a different user). I then try to connect to this instance. However, I am getting:

  Cannot open user default database. Login failed. Login failed for user
  'MyDomain\MyAccount'. (Microsoft SQL Server, Error: 4064)

I get the same result if I set the default database to MyDatabase in the Connection Properties.

I ran a Profiler trace and see an error 18456 state 38:

  Login failed for user 'MyDomain\MyAccount'. Reason: Failed to open the explicitly specified database 'MyDatabase'. [CLIENT: MyIpAddress]

That is followed by an error 18456 state 40:

  Login failed for user 'MyDomain\MyAccount'. Reason: Failed to open the
  database 'MyDatabase' specified in the login properties. [CLIENT:
  MyIpAddress]

MyDatabase is online and I have no trouble connecting to it with a different login. What additional permission do I need to grant to MyDomain\MyAccount? Is there something else that I am missing?


texts: Right way to get references to an object in Oracle?

texts:
I want to find all the objects referencing my object.

I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that separately)

Direct References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name

Also, on the internet, I have come across queries like the following.

Direct and Indirect References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS
       WHERE object_id IN (SELECT object_id
                           FROM public_dependency
                           CONNECT BY PRIOR object_id = referenced_object_id
                           START WITH referenced_object_id = :objectId)

However, if my understanding is correct, this query doesn't return direct references only, but also indirect dependencies.

For example if I want the references for object_A. If object_B references it, and object_C referenced object_B. This query would return object_B and object_C as references to object_A. Am I right?


texts: Looking for a scalable relational database

texts:
I'm looking for a database that achieves the following:

fully ACID.
relational with joins.
has transactions.
allows the administrator to specify sharding rules (for example: users from Europe should be located in the european data center).
the toughest requirement: upon network partition, like two datacenters disconnected from each other, each datacenter should be able to read and write to the nodes in it. Basically, I'm willing to sacrifice partial availability to partition tolerance. Sadly, most solutions will stall all reads and writes in case of a split-brain. (thus, preferring availability over partition tolerance)

I prefer an open source solution but willing to consider proprietary solutions as well.


texts: An update operation that would target 0 rows, aborted by timeout

texts:
I have an update operation that is being executed on a quite large table (postgresql@10):
UPDATE contacts_trashbin
SET op_id='\x60771801dbed2d6e021ec067'::bytea
WHERE contacts_trashbin.company_id = '\x577f0cd198d4e67e170d31e1'::bytea
  AND contacts_trashbin.deleted <= '2021-02-13T16:27:45.599860'::timestamp
  AND (contacts_trashbin.op_id = '\x000000000000000000000000'::bytea
       OR contacts_trashbin.removing IS true)

This request timeouts after 60 seconds (the hard limit we have on our server for all operations).
What's odd about this timeout is that the select with the same filter conditions:
select count(*) from contacts_trashbin
WHERE contacts_trashbin.company_id = '\x577f0cd198d4e67e170d31e1'::bytea
  AND contacts_trashbin.deleted <= '2021-02-13T16:27:45.599860'::timestamp
  AND (contacts_trashbin.op_id = '\x000000000000000000000000'::bytea OR contacts_trashbin.removing IS true)

takes only 71ms and returns 0.
There are several indexes on this table, one of them is
    create index ix_company_id_deleted on contacts_trashbin (company_id, deleted);

The total number of rows WHERE company_id='\x577f0cd198d4e67e170d31e1' is ~600k if it matters.
Any ideas why it could be happening?


texts: Is there a performance difference in the 2 temp table initializations?

texts:
I've recently come across a interesting practice where I work. I've noticed that some developers use the following way to initialize a sql server temp table:

if object_id('tempdb..#TempTbl','u') is not null
drop table #TempTbl

Other teammates will use:

if object_id('tempdb..#TempTbl') is not null
drop table #TempTbl

My questions:

Is there a performance difference between the 2 initializations?
What purpose does the ,'u' piece of code serve in the first statement?

I've tried researching msdn to get more information and it doesn't seem like either statement has a real difference. They produce the same results, but I'm curious about performance or other related factors.


texts: mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

texts:
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:

Error: Unknown collation utf8mb4_0900_ai_ci

Then I went back into the sql file and replaced utf8mb4_0900_ai_ci with utf8mb4_general_ci, but then it says there are errors with various CREATE VIEW sql statements. Are there any other quick, error free fixes I can try?


texts: Can I disable an account in mysql after x failed login attempts?

texts: I would like to disable accounts after x failed login attempts. Is it possible?


texts: Will changing server_id on master break replication?

texts:
I have few MySql absolutely isolated trees, each one of them has one master and few slaves.

For purposes of automation I need to set the same server_id to all masters (this will simplify the usage of pt-heartbeat which needs server_id). This should be perfectly fine since all trees are isolated.

I am wondering, if I change server_id on master, restart master, do I damage the replication? Of course there are will be a short brake of replication, but will this break replication completely?

The question is does changing server_id break replication completely, does it change positions in bin-log?

UPDATE: actually I don't have to restart mysql, I can just change it in console with SET GLOBAL server_id=xxx, in this case I don't need restart.


texts: How to calculate differences between maximum value and current value for each row?

texts:
My sales table looks like this.
region          sales
======================
East            1200
Midwest         (Null)
North           2500
South           4000
West            2400

Expected query result:
region          sales     diff
================================
East            1200      2800
Midwest         0         4000
North           2500      1500
South           4000      0
West            2400      1600

The diff column is calculated by max(sales) - sales. I don't know well about SQL and I even don't know how I can write a search on google. Any help will be appreciated.


texts: NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008

texts:
NHibernate automatically uses sp_executesql with different paramters to execute it's data. Now what we're noticing in production is that we're beeing hit by parameter sniffing sometimes. In such a way that query performance isn't just effected it's actually causing timeouts.

I'm not a DBA so i'm somewhat winging it but to me it seems like a query is beeing optimised for a small amount of data (10 rows) and then has to query a big amount (150000 rows) which is grinding it to a halt.

Now i've foundout about the 4136 Trace which disables parameter sniffing entirely for an instance of SQL Server.

Now since we're also in the process of upgrading some of our servers i'm wondering if NHibernate behaves different on SQL Server 2008 then it does in 2005. Does it for example add the OPTIMISE FOR UNKOWN bits and pieces.

As far as i know some "tools" have been added into SQL 2008 to allow for better ways of handling these parameter sniffing issues. I'm very curious if NHibernate has taken these tools and uses them or if we change to SQL Server 2008 we'd still have to use this trace or something simular to handle these issues.


texts: What does it take to replicate the mysql, 'mysql' database correctly?

texts:
I've searched for a while and I am having trouble finding anything that even addresses my situation.

I have need to replicate the 'user' table specifically, from my master cluster to my async slave.

I can create working replication for databases besides the 'mysql' database, but when I attempt to replicate the mysql database replication appears to stop working for every database.

I have really hit a wall, especially since the 'show slave status' command reports that 'Slave_IO_Running' and 'Slave_SQL_Running' are both 'Yes'

The executed gtid set even matches when I view 'show master status'.

I believe the error is within when I configure the slave, or how I am replicating the mysql db.

I am doing

mysqldump --databases ${databases[@]} --single-transaction --triggers --routines --events --host=${MYSQL_TARGET} --port=${MYSQL_TARGET_PORT} --user=${MYSQL_BACKUP_USER} --password=${MYSQL_BACKUP_PASSWORD} > /tmp/dump.sql

mysql -u root -p${MYSQL_ROOT_PASSWORD} --socket=/var/run/mysqld/mysqld.sock < /tmp/dump.sql && break
mysql -u root -p${MYSQL_ROOT_PASSWORD} --socket=/var/run/mysqld/mysqld.sock -e "reset master;"

mysql -u root -p${MYSQL_ROOT_PASSWORD} --socket=/var/run/mysqld/mysqld.sock -e "CHANGE MASTER TO MASTER_HOST='"${MYSQL_TARGET}"',MASTER_PORT="${MYSQL_TARGET_PORT}",MASTER_USER='"${MYSQL_REPLICATION_USER}"',MASTER_PASSWORD='"${MYSQL_REPLICATION_PASSWORD}"',MASTER_AUTO_POSITION=1;"
mysql -u root -p${MYSQL_ROOT_PASSWORD} --socket=/var/run/mysqld/mysqld.sock -e "start slave;"

where databases is an array of databases I want to replicate including the mysql database.

I also include this in my cnf file, which to my understanding will replicated only the user table in the 'mysql' database, and everything in

replicate-do-db=<user database>
replicate-do-db=mysql
replicate-do-table=mysql.user

I don't see anything in the error log that suggests what I am doing is broken either.  I have ensured the replication user and password is the same on the master as the slave, so that the credentials don't change when the user table is replicated.

Anybody have any ideas?


texts: we still can't find backup archivelog in local controlfile

texts:
Oracle database is Oracle 11.2.0.3 version.
Every one hour, archivelog will backup to netbackup tape.
But it is strange, even when we use resync catalog command, we still can't find the backed up archivelog file in the local controlfile; but in nbu catalog database, we can find backup archivelog info.
Could you please check if we are doing anything wrong?
list backup of archivelog from logseq 382594 until logseq 382595;

when in local control ,list backup archivelog info is as below; no backed up archivelog found .

Recovery Manager complete.
[opreb@srebdb01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 23 10:21:38 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: REB (DBID=2063200898)

RMAN> list backup of archivelog from logseq 382594 until logseq 382595;                                    <-

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> exit

Recovery Manager complete.

Use catalog database to check if we can find backuped archivelog info;

[opreb@srebdb01 ~]$ rman target / catalog rman11g/rman11g@cat11g

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 23 10:21:52 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: REB (DBID=2063200898)
connected to recovery catalog database
list backup of archivelog from logseq 382594 until logseq 382595;                                 <-
RMAN>

List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
128764956 6.50M SBT_TAPE 00:00:09 22-OCT-20
BP Key: 128764959 Status: AVAILABLE Compressed: NO Tag: TAG20201022T215336
Handle: al_73143_1_1054504417 Media: @aaabh

List of Archived Logs in backup set 128764956
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 382594 5576720225 22-OCT-20 5576727105 22-OCT-20
1 382595 5576727105 22-OCT-20 5576733606 22-OCT-20

Then we try to  RESYNC CATALOG

RMAN> RESYNC CATALOG
2>
3> ;

starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

Event then, we still can't find backup archive log info in local controlfile.

[opreb@srebdb01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 23 10:22:26 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: REB (DBID=2063200898)

RMAN> list backup of archivelog from logseq 382594 until logseq 382595;                               <-

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> exit

Thanks for your feedback.


texts:
How to ensure a query runs only once a day in MySQL? Once it runs, returned result should be cached, and even though returned data were changed in database, my query's results should remain the same during the same day. Is that possible?

Point of my this question:

I want to put some statistics on the dashboard of my admin panel. But calculating the statistics keep long time. So, If these calculations is done only one time, it really saves lots of time of my clients and my server.


texts: Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

texts:
I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job is owned by a sysadmin (well, me) and run as the SQL Server Agent Service Account.

So, the data essentially goes source system ~> transit db ~> staging ~> NDS overnight.

The two SSIS packages I care about, handle the transit db ~> staging and staging ~> NDS parts, respectively, for a specific set of data.

A domain user (non-sysadmin) does something in the source system and that pushes the interesting data into the transit db, so I need a way to fetch this updated data during work hours to update the NDS: it was decided that the simplest way for this person to trigger that ETL, was by clicking a button in a macro-enabled Excel workbook, that connects to SQL Server via ODBC (using Windows Authentication) and executes a stored procedure.

The stored procedure looks like this:

create procedure dbo.UpdateMaterialInventory
as
begin
    execute msdb.dbo.UpdateMaterialInventory;
end

The "sister" stored procedure in [msdb] looks like this:

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
    execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

This [SqlAgentProxy] user is a Windows user I created in [msdb] off the domain user's login, to which I granted execute permission to this UpdateMaterialInventory procedure. This avoids having to grant the domain user execute permission to msdb.dbo.sp_start_job, which would be excessive.

The SQL Agent job NDS-ManualMaterialInventory is owned by the domain user and has 2 steps, each of type [SQL Server Integration Services Package], set up to Run as SSISProxy.

SSISProxy is an SQL Server Agent proxy that's mapped to the [SQL Server Integration Services Package] subsystem, using credential name SSISProxyCredentials. The domain user's login was added to the Proxy account principals.

The SSISProxyCredentials were created with the Identity of the same domain user that's running the entire SSIS ETL overnight, and its password was quadruple-checked.

Now, if I run this:

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

I get this output:

Job 'NDS-ManualMaterialInventory' started successfully.

However the job history is telling a much less encouraging story:

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

And step 1 details:

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

The job fails and nothing gets logged anywhere.

If I change the job owner to be myself, and change the steps' run as to be the SQL Server Agent Service Account, the job runs, succeeds and logs 1,067 rows to [Metadata].[dbo].[sysssislog].

Looks like there's something not right about how the proxy/credentials are set up. Which part am I doing wrong?


texts: MySQL - Advice for using two tables, one each for open and closed records?

texts:
I'm migrating an app from ASP/MSSQL Server 2008 to PHP/MySQL. In the previous app, I had an object using multiple joins and advanced search queries with hundreds of thousands of records. I split the records into 'table1' and 'table1_closed' so I could keep the open-records table speedy. This worked in speeding up the queries by a tremendous amount (the closed table is orders of magnitude slower), but resulted in some weird workarounds to allow closed records to be moved back to the open records table (manual locks and such). Needless to say it’s hacky and buggy.

My question is, in MySQL, does anyone see the need to keep doing it this way, or would you merge the two tables back to one and just use an indexed field to differentiate? Is MySQL more adept at advanced queries with joins on very large recordsets?

From comment:

Select  t1.*, t2.*, t3.*
    from  properties t1
    Join  inspection_groups t2  ON t1.id = t2.property_id
    Join  inspection_types t3  ON t2.inspection_type_id = t3.id
    where  (      t1.address = 'x'
              or  t1.city = 'x'
           )
      And  t2.inspection_type_id = 1
      And  t2.is_active = 1
    ORDER BY  t2.created_at DESC
    LIMIT  1,12


texts: SQL Server 2014 SP3 DB emailer TLS 1.2 support

texts:
I have SQL Server 2014 (SP3-CU4) 12.0.6329.1 running on Windows Server 2012R2 with .NET 3.5.30729.4926 installed

I can't get Database emailer to work with TLS 1.2

I tried everything in the article "TLS 1.2 support for Microsoft SQL Server" to no avail.
https://support.microsoft.com/en-gb/help/3135244/tls-1-2-support-for-microsoft-sql-server

Can you advise please?


texts: AlwaysOn Disaster Recovery Clarifications

texts:
Whenever I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node and test the connectivity and if everything is okay. I have two questions.

1)How to revert back the AOAGs to original Synchronous mode -- Should I Perform a Forced Manual Failover of an Availability Group to PRODUCTION Servers(Sync) from DR/BCP (Asynchronous) node ?

2)We have one extra BCP node MSDTC and  AOAGs Listener. Should I turn then online before I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node ?

Please correct me. I am asking these questions for BCP site Testing plan view.


texts: SQL Server 2012 database 3 mirroring questions

texts:
I've been looking for the answer to these three questions but just cant find a straight answer..

When mirroring with a witness, does all three SQL Server editions have to have the same name? I.E at the moment I have 3 servers labeled ABC-SERVER1, ABC-SERVER2, ABC-SERVER3. My main server is called SQL-SERVER3 on server3, would I then have to have the named instance SQL-SERVER3 running on ABC-SERVER2 and 1?
If the SQL Server instances don't have to be labeled all the same i.e they can be (SQL ABC-SERVER1), (ABC SQL-SERVER2) etc... If you are using a program for example MS Access and you connect to a server for example server3, how would access know where to go if server3 was offline?
As I understand it you can have 2 SQL Server versions running at any one time from the 1 licence, as long as only 1 instance of SQL Server is in use at any one time. To get a witness running I would have to use a SQL Server Express edition or buy another licence, is that correct?

Thanks in advance for taking your time out to read this....


texts:
I have one query with multiple execution plans, the memory granted to one plan is huge comparing to the 2nd one

based on this article https://blogs.msdn.microsoft.com/sql_server_team/addressing-large-memory-grant-requests-from-optimized-nested-loops/

  The issue occurs when the outer table of the Nested Loop join has a
  predicate that filters the result to a small input, but the batch sort
  appears to be using an estimate for cardinality that is equivalent to
  the entire outer table. This can result in a perceived excessive
  memory grant which in a very concurrent server can have several
  side-effects, like OOM conditions, memory pressure for plan cache
  eviction, or unexpected RESOURCE_SEMAPHORE waits. We have seen how a
  single query that match this pattern can actually get several GB’s of
  granted memory on high-end machines (1TB+ RAM).

  One option until now would be to disable this feature globally using
  Trace Flag 2340, as described in KB 2801413. However, in SQL Server
  2016 RC0 we have changed the behavior to maintain the advantage of the
  optimization, but now the max grant limit is based on the available
  memory grant space. This improvement also translates into better
  scalability, in the sense more queries can be executed with a smaller
  memory footprint. We are looking at back porting this behavior to an
  upcoming have ported this behavior to SQL Server 2014 Service Pack 2,
  and as usual deliver added value to in-market versions.

This is exactly what I am seeing however I am using SQL Server 2016 Enterprise.

These are the execution plans

https://www.brentozar.com/pastetheplan/?id=SJ0mYAy0b

https://www.brentozar.com/pastetheplan/?id=BJzutC1R-

My questions are

What is the reason for 2 execution plans?
The optimizer is using the top execution plan, I forced it to use the lower plan but after sometime it goes again to the top one, any reason for that?
How to fix this problem? This issue causes the application to crash (there were many RESOURCE_SEMAPHORE waits and the application become unresponsive)? Should I use the hint : DISABLE_OPTIMIZED_NESTED_LOOP or  Trace Flag 2340?

NOTE : I checked the XML and both plans have  NestedLoops Optimized="false"


texts: How to query mysql tables with regular expressions?

texts:
I'm using a correct regex, already tested with other tools. But in mysql seems not to work properly. Say this data:

And I want to select all the records which start with 2 non-digits followed by a dot, followed by one digit followed by a sequence of .00 (ex. DE.1.00.00.00.00)

\D{2}\.\d?\.0{2}.0{2}.0{2}.0{2}

The above used alone normally works. But on mysql, a simple query like the following won't work. It will return a table with all the columns and without any error, but also without any value. Why?

SELECT *
FROM V35_CATEGORIE cat
WHERE cat.CategoriaCodice REGEXP '\D{2}\.\d?\.0{2}.0{2}.0{2}.0{2}'


texts: Need help merging multiple full SQL Server Backups into single database

texts: Got a really strange issue with SQL Server database backup files I am getting backup files from the state of California for petroleum production.  Each year a database with the same name is created for the data for that year The database is always named, WellProductionInjection.  Each year the same tables with the same contents are created and populated.  The only difference is that the table names contain the year, so separate tables as supposed to exist for each year.  I can get what is labelled as a full backup file for a single year. But when I try to use SQL Server Management Studio to restore all the backup files to a single database, I get only the data for one year, or an error about the database already existing.  Any tips on how deal deal with merging multiple backups into a single database? Examples of the backup files can be put on the web if anyone is willing to try their luck with this.


texts: Backup a sample of a PostgreSQL database

texts:
Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)?

I am able to back up that database using pg_dump. In some case I need db with minimum data.

pg_dump --host=localhost --port=5432 --username=postgres --password
        --column-inserts --schema=test  testdb > test_backup.sql

How can I modify the above command to take the back up with 1000 number of  data??


texts: Download SQL Server Profiler for SQL Server Management Studio

texts: How can I profile a SQL Server 2008 database to see code that's being executed on a particular database?  I remember using the SQL Server profiler, but I don't see it in SQL Server Management Studio after downloading SQL Server 2008 R2 Express.  Where can I download that tool and install it?  Do I need the full version of SQL Server 2008 in order to see this option?


texts:
I'm running Postgresql on a Ubuntu server and need to be able to monitor its memory usage.  Currently I have scripts running in a one-minute cron job that monitor/log various statistics and need to also monitor/log the current memory usage of Postgresql.  I've searched around and haven't been able to find much, other than the fact that Postgresql makes good use of shared memory so the values given by a program such as 'top' aren't accurate.

How can I monitor what the total memory usage of Postgresql is at any given time?  This data will later be used to create graphs for analysis.


texts: How to alter column from float(8) to numeric(64,8)

texts:
I am working on a website that store lots of numbers in a very specific precisions (8 digits).
Most of the columns are using numeric(64,8), but I discovered that a handful are using float(8) instead, and it's causing a lot of computation issues.

How can I alter those float columns to numeric(64,8)? How do I cast the data?

Thanks!


texts: SQL Server: Can I log ship from SQL Server 2014 --> 2016?

texts:
SQL Server 2014 and 2016, all of them Standard Edition.

We are migrating to a new SQL Server. We normally do this via log shipping (this lets us have a small cutover window, because we just need to restore final logs, take the new db out of recovery, mod connection strings and we are done...)

We would like to migrate to SQL 2016 in this process.

Poss to log ship from 2014 to 2016?


texts: Permission error when executing a 5.6 GB sqlcmd file

texts:
I am trying to run a very large file through the sqlcmd command and it works perfectly with files of 100 MB up to 1.4 GB. This is what I have tested so far, but when I try to run a file which is 5.6 GB, it throws this permission error:

  Sqlcmd: Error: Error occurred while opening or operating on file C:\Test\Insert_Generated_File.sql (Reason: Access is denied).

The error is thrown immediately.

Are there any limitations on SQL Server Express edition when executing large files?

Just so that everything is clear, the file contains INSERT generated statements from a 4GB database and nothing more. It is saved as a *.sql file.

The version of sql I'm using is:

  Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
      Jun 10 2015 03:35:45
      Copyright (c) Microsoft Corporation
      Express Edition (64-bit) on Windows NT 6.3  (Build 10586: ) (Hypervisor)

All I want to know is why am I getting that error, is it because of Express Edition, or because sqlcmd cannot handle a file that large. Unfortunately, I do not own a Standard Edition of SQL Server so I cannot test it there.

I know of the SQL Express database limitations, yet I am scripting a database which is already part of my server. It would make sense if I did not have this data already in, but I do. And all the data is already in once. I just cannot recreate it using this file.


texts: I can not find any document regarding to shared_heap_size

texts:
I am new to Postgres and recently I ran one command

show all;

It returned me a csv file with three columns name, settings, description. There are more than 200 results and out of which one result is

name : shared_heap_size
settings : 1GB
description : Sets the size of the shared memory segment that is used
    for Aurora storage metadata and communications.

I could not find any document related to this. Can anyone please elaborate?

Output for select version();

PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit,


texts: Does MySQL or PostgreSQL lock table on deletion?

texts:
I am about to delete large amount of data from a database. I want to know if deletion of large number of records from table in MySQL or PostgreSQL, lock a table ?

Let's say my table has been adding 2 GB data per day since 2016, and I want to delete all records older than 6 months from now.

Would my table get locked and all my insert or update or delete queries fail during the deletion time ?


texts: MySQL 5.6 trigger replication to slave locking the table

texts:
Heyo good sirs.

I have a master to slave replication using Row-based replication. The master gets a few simple and fast reads but the majority of read load is directed at the slave. They sometimes have queries running for a minute or two.

Lately we needed to have a trigger to copy some new data from one table into a new one. Simple enough, we created the trigger and everything went OK. The slave did not get the trigger but the data was replicated over anyways as is the way with row-based replication.

Then things took a turn for the worse. We did yet another trigger on another table. While we did this I'm guessing a big read query was being run on the slave. Suddenly our monitoring scripts started yelling at us that the amount of queries on the slave was too high so I started checking out the process list on the slave. There was one query running, the huge read. Every other query was "Waiting for table metadata lock" and the only other query which was not a SELECT was the system user replication "connection"/"thread". That query was showing the statement as our CREATE TRIGGER... which we ran on our master. In a panic I killed the long running SELECT query and all the congestion we had just disappeared quickly.

I then checked SHOW TRIGGERS and the information_schema.TRIGGERS for any trigger, there were none. That was as it should, considering the row based replication I guess. However why was the CREATE TRIGGER query even running on the slave, and why was it blocking all our other queries?

We then tried the same when there was no long query running and everything went fine. We even duplicated this behavior on a test setup by manually starting a long query and creating a trigger on the master.

Is this behavior intended and could we do something to make sure creating triggers won't make the slave drop and/or queue our SELECT queries?

I'm sorry for the wall of text.

With best wishes,
alu


texts: Send results of autogrowth from default trace to application event log

texts:
I've been asked to generate an entry in the event viewer, as opposed to an e-mail notification, whenever there is an autogrowth event. The problem I'm having is getting data from the default trace parameters into the message part of the event viewer.

To generate the event viewer entry, I'm using:

EXEC xp_logevent 60001, @msg, error

If I try to set @msg to say EventName I get an error message:

  Invalid colmun name 'EventName'

Right now an entry is created in the event viewer, but it doesn't state where the autogrowth happened, how much, etc. Without that data, someone who gets the alerts via azure log analytics will have to log in to the server to check it out every time. Here is my current query:

DECLARE @tracepath nvarchar(500)
SELECT @tracepath = path FROM sys.traces WHERE is_default = 1

--The trace automatically finds _n files, trim off the _nnn portion of the file name.
select @tracepath = substring(@tracepath, 0, charindex('\log_', @tracepath,0)+4) + '.trc'

print @tracepath

SELECT
DBName    = g.DatabaseName
, DBFileName   = mf.physical_name
, FileType   = CASE mf.type WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 'FILESTREAM' WHEN 4 THEN 'Full-text' END
, EventName   = te.name
, EventGrowthMB  = convert(decimal(19,2),g.IntegerData*8/1024.) -- Number of 8-kilobyte (KB) pages by which the file increased.
, EventTime   = g.StartTime
, EventDurationSec = convert(decimal(19,2),g.Duration/1000./1000.) -- Length of time (in milliseconds) necessary to extend the file.
, CurrentAutoGrowthSet= CASE
     WHEN mf.is_percent_growth = 1
     THEN CONVERT(char(2), mf.growth) + '%'
     ELSE CONVERT(varchar(30), convert(decimal(19,2), mf.growth*8./1024.)) + 'MB'
    END
, CurrentFileSizeMB = convert(decimal(19,2),mf.size* 8./1024.)
, @tracepath
FROM fn_trace_gettable(@tracepath, default) g
cross apply sys.trace_events te
inner join sys.master_files mf
on mf.database_id = g.DatabaseID
and g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id AND DATEDIFF(mi, g.StartTime, getdate()) < 360
and  te.name in ('Data File Auto Grow','Log File Auto Grow')
order by StartTime desc
IF @@ROWCOUNT > 0
BEGIN
PRINT ERROR_Message()
DECLARE @msg VARCHAR(100)
SELECT @msg = ERROR_MESSAGE()
EXEC xp_logevent 60001, autogrowthEvent, error
END


texts:
Can I create event that would first TRUNCATE table and then make INSERT?
I know how to TRUNCATE and INSERT in separate events but need to do it immediately one after the other. I just don't know the syntax for events.
I can do it in myPHPadmin

TRUNCATE tbl1;

INSERT INTO
tbl1
SELECT
....

I know I could you PHP to run those two queries. I prefer event.
Is it possible to do it in single event or at least to call those two event in other event.
BTW, are MySQL events ok to use or PHP file with cron would be better?
Thank you


texts:
I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting

DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int

SELECT  @Val1 = 5 , @Val2= 2,@Val3= 200000, @Val5  = 2,@Val4 = 2014
SELECT  @Val1 , @Val2,@Val3, @Val4, @Val5

SELECT CASE

        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NULL  THEN 'ReturnValue1'
        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL THEN 'ReturnValue2'
        WHEN  @Val1 = 5 AND @Val2  = 2 AND @Val3 = 200000  AND @Val5 = 2 AND @Val4 IS NOT NULL THEN 'ReturnValue3'
        ELSE NULL
    END [TestingValue]

I'm expecting the value to be ReturnValue3 however it appears to be ignoring this whole line for some reason.

Can anyone see a problem which I'm not spotting.


texts: Dump All Databases in MySQL - Fails on mysql and performance_schema

texts:
I have had two attempts at dumping all databases but both have failed, one with the mysql database and then with the performance_schema database. The precised errors are:-

11:01:01 Dumping mysql - mysqldump: Got error: 1556: You can't use locks with log tables. when doing LOCK TABLES
11:35:23 Dumping performance_schema () - mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

Is there a way to overcome this? Basically I am trying to follw the procedure here:- https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

Many thanks...


texts: replicate data from one db to another db server using automated process

texts:
I have an scenario,
I have two DB server running on two different machines.
I have to build an automated process by which the data will replicate from one DB to another DB server on every insertion happens in first one.
My application using spring- hibernate and mysql DB.
I can go for DB side or spring-hibernate side approach.
Is it possible to do this with rabbitMQ?
I have an option to call automated process on each insertion or as a batch process or using queue.Some of the answers mention in the following link, but it is not clear to me
 1. https://stackoverflow.com/questions/33130260/transforming-data-from-one-sql-server-db-to-another
2. https://stackoverflow.com/questions/3821897/replicating-data-from-one-db-to-another
Any help would be greatly appreciated


texts: Creating an index on table A but with reference to table B

texts:
Our company uses a 3rd party application for numerous aspects of the business. One aspect is dealing with customer payments and when selecting a window within the app to list all the payments ever made, the list returns pretty quickly. However, if an user then performs a filter on the list for a specific client name, this operation can take in excess of 6 minutes and returns about 15 rows out of a table that only contains 120,000 rows.

The underlying table does not contain the client name, this is held in another table (Clients). The link between the 2 tables is the clients reference number.
I have defrag'd the indexes on the database, but to no avail.
I have come to the conclusion that there is not a suitable index in place to improve this situation.

Q - Is it possible to create an index on the payments table but references the clients name in the clients table? Is this the solution to improve this situation or should I be doing something else (apart from telling the 3rd party vendor to improve this area in a future release)

note: I am a newbie if you haven't already guessed !


texts: Multiple versions of SQL Server on the same development machine

texts:
I am consolidating some SQL Servers to simplify administration. I am thinking about a new VM with SQL Server 2005/2008R2/2012 each as a separate named instance on the same machine. This machine will only be used in a 'sandbox' environment primarily to test restores of our backups. No applications will be hitting this.

Am I likely to run into any issues with multiple versions? I remember running into issues years ago with SQL 2000/2005 on the same box. Do you folks use separate machines for restore testing?


texts: Impact of changing server machines names with SQL Server 2016 Standard in mirror state

texts:
It's an enterprise network domain. Two existing Windows server 2016 machines, one each for SQL Server 2016 Standard edition and other is also SQL Server 2016 Standard edition in a passive mirror configuration.

For some reason we need to change both server machine names from say A to B and A1 to B1. Will this have any impact on SQL Server and its passive mirror access? What precautions I need to take? I can understand the connection string with change but I am looking for impact of this change on mirroring configuration particularly


texts:
Why is it necessary to run both mongod and mongo.exe in windows? What's happening behind the scene?

What's the difference between these two?


texts: Insert Into MySQL over linked server when a medium text is the last column breaks Int fields

texts:
I have a table with

ID INT(16)
StateName VARCHAR(50)
Details MEDIUMTEXT

When I do an insert over the linked server connection:

Insert Into MySQL...tblState (ID, StateName, Details) Values (1,'Alabama','Test')

The row ID is set to 0 instead of 1.
If I flip the table structure to put the Details before the Statename, so that the MEDIUMTEXT is not the last column, and do the same insert statement.

Insert Into MySQL...tblState (ID, Details, StateName) Values (1,'Test','Alabama')

Then the insert works exactly like it's supposed to.

Is this a known bug, or is there a setting that I need to change to fix this?

I'm using the MySQL ODBC 5.1 Driver

Edit: In case this helps anyone, here are the results of a test I did.
In phpmyadmin:

CREATE TABLE `tblState` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `tblState2` (
  `StateID` int(11) NOT NULL,
  `Details` mediumtext NOT NULL,
  `State` varchar(50) NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

From SQL Server over linked server:

insert into mysql...tblState(StateID,State,Details) values (1,'alabama','test1')

(1 row(s) affected)

select * from mysql...tblState

StateID     State                Details
----------- -------------------- --------------------
0           alabama              test1

(1 row(s) affected)

insert into mysql...tblState2(StateID,Details,State) values (1,'test2','alabama' )

(1 row(s) affected)

select * from mysql...tblState2

StateID     Details              State
----------- -------------------- --------------------
1           test2                alabama

(1 row(s) affected)


texts: Update MySQL with Solaris 11 IPS Package

texts:
I'm barely new with Solaris OS and there are still a couple of things I definitely don't get it.

I want to replicate a MySQL installation that someone did in the past in a development server we have. Simple: MySQL 5.6.36 running on Solaris 11.3 installed as a IPS package with its SMF services.

I'm testing a with a fresh new install Solaris 11.3. I did a "pkg install database/mysql-56" (following this simple guide), but the MySQL IPS package installed version is 5.6.25.
How can I upgrade it to 5.6.36? Why the latest MySQL IPS Package available version is so old (in fact, I was expecting a newer version, and then try to figure out how to downgrade it)? What I'm missing?

Regards!


texts: can I add a custom SSRS e-mail subscription that forces the "TO" address

texts:
I would like to know if it is possible to add a custom delivery extension(?) in SSRS that will enable me to force a value into the "TO" address for particular subscriptions?

Big picture: I am exploring ways to schedule reports that would only permit them to be sent to a specific email address.

EDIT: Somewhat related, I would also like to know if I can create a second e-mail delivery extension without having do any programming.  i.e. I would copy the extension details from RSReportServer.config for the "Report Server Email", give it a new name, and change a single configuration value.

SECOND EDIT: In the hopes of getting some additional input and ideas, the big BIG picture is I want to create a method to create an SSRS subscription that would guarantee the recipient is a particular e-mail address.  This method should be usable by all users, but even knowing how to let one user might be workable.  Subscriptions to other e-mail addresses must still be permitted.


texts:
I need to import 10 billion rows into a database, daily.

SSIS, doesn't seem like its cutting it, and I'm starting to think that writing some kind of custom C/C++ code might be an option (I don't much know C/C++) to try and do it.

I'm working with a standard level production box, nothing fancy but decent.  SQL 2012.

Any advice is welcome.

EDIT:

Source is a flat file.
Say 20 columns.  Not sure about size.
Source data is on the same disk.  (At the moment).

EDIT 2:
While the columns are largely int/bigint, there are 5-7 VARCHAR(x) (the largest is VARCHAR(1000)).

The problem im finding with SSIS is that the read/write speeds are not what I'm hoping for. I've tried fastParse, etc, and various other techniques.

Other than tips, I'm hoping for techniques.  Tips still welcome obviously.


texts: Two composite foreign keys have identical columns which must be equal value

texts:
Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id)

I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, section, type, serial)
.The CourseEvaluations relation will contain records of the different evaluations done in a course.

Now I want to make a third relation: CourseStudentEvaluations with two composite foreign keys from both the above tables. Both the above tables' primary keys contain course_id and sem_id. How do I enforce that they are equal in a row/record so that I can ensure only those students' evaluations are entered against only those courses in which they're registered.

The CourseStudentEvaluations relation will contain records of all students' performance in each evaluation entered in CourseEvaluations.


texts: SQL Server job to use external Powershell

texts:
I have a SQL Server job which runs some Powershell code, but although when running the Powershell script in a PS editor on the machine it runs successfully, when running the SQL Agent Job with the same code it fails.

And the reason it fails is because it doesn't manage to add a few snap-ins, like the one below or include some assemblies.

Add-PSSnapin SqlServerCmdletSnapin100

This leads me to believe that SQL Server has a internal version of Powershell installed with which it runs the Powershell jobs. And that specific internal version of Powershell is older and does not have the required assemblies / snap-ins and thus fails.

Now, is there any way I can use the Powershell version installed on my machine and not the one that's built into SQL Server?

I've looked over "Proxies" in SQL Server but I don't think they're what I'm looking for.

Any help is appreciated!


texts: Reporting server configured in a separate Web server

texts:
I am new into configuring Reporting server and I did install and configured reporting server in a separate app server (lets say on app-01).
I have my database server running on a separate server, sql-01 server.

I have configured Reporting server and the application team deployed the reports. When I check the Web Service URL and Report Manager URL, I can see the reports in there. On clicking a report, the reports are running.

But when I run the report from the application (application code in app-01) it is not running.
Do I have to configure Reporting server for SSL? I know that SSL is for secure connection, but to access it from application from different systems do I have to configure SSL?

Any help on this area is much appreciated.

Thanks.


texts: PostgreSQL corrupted after running pg_resetxlog

texts:
We are using PostgreSQL version 9.3 on Ubuntu 14.04. This PostgreSQL server shared among all our application servers (Odoo), so we made it run on the separate environment.

On Saturday we found disk full issue on this DB server. On our further investigation, we found the backup server(barman) is went off. So all the archive log stay on the database server. This occupied the entire disk. Our database backup server may be stopped working a month back.

By googling we found a solution, that by resetting the pg_xlog files to solve this problem. So we clean the log file using pg_restxlog command. As the forum said the disk cleared and we reboot the server. But no database found :-(. We listed using psql - list command. Nothing worked till now. We are not able to restore the backup from barman too. Then we continue our investigation and we found all the data stay safe under the base folder of the main data path of Postgres.

The steps we execute to reset the log follows.

Try to stop the database server

sudo service postgresql stop
Logged in as Postgres user

sudo su - postgres
Run the reset command.

/usr/lib/postgresql/9.3/bin/pg_resetxlog -f /var/lib/postgresql/9.3/main/
Disable the barman configuration in postgres.conf file to stop the backup process for while.
And reboot the server

File content of /var/lib/postgresql/9.3/main/

postgres@server2:~$ du -h 9.3/main/
12K     9.3/main/pg_notify
28M     9.3/main/base/2735749
73M     9.3/main/base/4172290
46M     9.3/main/base/4410494
81M     9.3/main/base/3002089
43M     9.3/main/base/4282962
47M     9.3/main/base/3377227
130M    9.3/main/base/4098067
44M     9.3/main/base/1682791
58M     9.3/main/base/3377231
4.0K    9.3/main/base/pgsql_tmp
6.1M    9.3/main/base/12030
41M     9.3/main/base/4280118
54M     9.3/main/base/3149391
45M     9.3/main/base/4202614
49M     9.3/main/base/3344071
45M     9.3/main/base/2985056
51M     9.3/main/base/2120822
18G     9.3/main/base/3655712
25M     9.3/main/base/2759574
40M     9.3/main/base/4388978
52M     9.3/main/base/2435773
53M     9.3/main/base/4236740
55M     9.3/main/base/3386464
6.2M    9.3/main/base/12035
201M    9.3/main/base/4112218
54M     9.3/main/base/1625789
635M    9.3/main/base/149656
40M     9.3/main/base/4190162
25M     9.3/main/base/4090019
150M    9.3/main/base/4338686
6.2M    9.3/main/base/1
86M     9.3/main/base/2101485
185M    9.3/main/base/3453985
48M     9.3/main/base/4244883
41M     9.3/main/base/4160039
47M     9.3/main/base/3377180
38M     9.3/main/base/4150310
8.9G    9.3/main/base/2926431
47M     9.3/main/base/1693701
28M     9.3/main/base/4153341
25M     9.3/main/base/2744130
74M     9.3/main/base/2023404
29M     9.3/main/base/3231291
28M     9.3/main/base/2749185
43M     9.3/main/base/4371923
47M     9.3/main/base/3410953
47M     9.3/main/base/4313961
50M     9.3/main/base/4399246
49M     9.3/main/base/3402258
84M     9.3/main/base/3379836
64M     9.3/main/base/2777796
30G     9.3/main/base
5.8M    9.3/main/global
88K     9.3/main/pg_multixact/offsets
256K    9.3/main/pg_multixact/members
348K    9.3/main/pg_multixact
4.0K    9.3/main/pg_xlog/archive_status
33M     9.3/main/pg_xlog
100K    9.3/main/pg_stat_tmp
4.0K    9.3/main/pg_serial
4.0M    9.3/main/pg_clog
4.0K    9.3/main/pg_stat
52K     9.3/main/pg_subtrans
4.0K    9.3/main/pg_tblspc
4.0K    9.3/main/pg_twophase
4.0K    9.3/main/pg_snapshots
30G     9.3/main/


texts: Validate and Verify Data After SAN Migration

texts:
We are preparing to migrate to a new SAN and I have been tasked with verifying/validating data after migrating a server.

Other than the DBCC commands that would tell me about data integrity, and comparing against a restored backup on a different server (on the old SAN), what would you recommend to do in order to V&V the migration?

Thanks

ps - I will have instances that range from MSSQL 2000-2008


texts: Restore ALL databases with sqlcmd in SQL Server (Express, Docker, 2017)

texts:
These documentation articles

https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-2017

and

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database?view=sql-server-2017

Tell us how to restore individual databases. I want to restore all databases in the file list. Is there a way to do this programmatically from within the sqlcmd without involving bash?


texts: Use column values in ORDER BY and LIMIT clauses

texts:
I have this table in a Postgres 9.3 database:

I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name.

The final result will be as in the following picture:

Note: Sorry if it is simple for you, but I can't solve this. All mail addresses were generated by www.mockaroo.com. If your address is in this list, don't blame me.

order and limit columns will always have same data. order may be asc or desc and limit may be any integer value (but all rows will be the same value. It came from a grouping query.


texts: How to create unique index for both cases

texts:
I have table in PostgreSQL with sender_id and receiver_id and a unique index:

add_index :my_table, [:sender_id, :recipient_id], unique: true

Table will created, if sender_id: 1, and recipient_id: 2.

I can create another table, with sender_id: 2, and recipient_id: 1. But table with this ids, (but in different order) already exists.

How can I make unique for both cases?

sender_id: 1, recipient_id: 2
sender_id: 2, recipient_id: 1


texts: What tables are affected by Change Data Capture

texts:
We're looking at using Change Data Capture in SQL 2012. We're trying to work out if there is an easy way to find out what tables have been changed by a given lsn. Is there an inbuilt function, or would we need to query each cdc table to see if the lsn is in there?

UPDATE to explain a bit more about what we're trying to do:

We're trying to build a process that looks at cdc data for all the tables in the database (think 100's). This process will run on a schedule and will know the last lsn that it processed. When it runs we want to do something with each of the cdc tables that have had a change since the last recorded lsn.

We could check the fn_cdc_get_all_changes_dbo_tablename(@lastlsn, @currentlsn, 'all'), but the problem with that is that we would have to run it 100's of times (once for each table). Is there a central place we can check?


texts: Can I use SQL Server Authentication for Central Management Servers?

texts:
I have set up a Central Management Server in SQL Server. When I try to add a new Registered Server to this Central Management Server, I only have the options to use Windows Authentication or Active Directory Integrated Authentication. Is there a way to connect to these Registered Servers using Sql Server Authentication?

This option does exist when connecting to a local server group.


texts: A table that consists of multiple FK or creating separate tables for each FK

texts:
I have tables called 'books', 'category', 'subcategory', and 'booksCategory'. Each book can have its own category and subcategories.
For example, a Sci-Fi book can belong in the 'category' table under sci-fi, but it also may be under the subcategory under 'Aliens'.

Table books -  a list of books
Table Category - a list of categories. e.g. Sci-Fi
Table Subcategory - a list of subcategories. e.g. Aliens
Table booksCategory - a list of books under categories and subcategories

I wasn't sure of the most optimal way of creating booksCategory.
booksCategory will have a FK referencing the ids of both Category and Subcategory tables.

CREATE TABLE booksCategory(
  booksCategory_id INT NOT NULL AUTO_INCREMENT primary key,
  category_id INT references category (category_id),
  subcategory_id INT references subcategory (subcategory_id)
)

so the results would be something like this

booksCategory_id | category_id | subcategory_id | books_id
----------------------------------------------------------
     1                 3            4                 1     // 3 = SCI-FI, 4 = Aliens
     2                 2            NULL              1    // 2 = Adventure, Null = does not belong to any subcategory

The problem occurs when a book falls under multiple categories. In the example above, book_id with 1 can be both SCI-FI and Adventure. Is this the best method because then I will have a NULL in subcategory?

My other option I can think of is to have separate tables for booksCategory, booksSubcategory, and if I have more subcategories, bookSubcategories2.  So basically a separate list of books that fall under Category, another list of books that fall under subcategory, and if I have more branching subcategories, a list of books that fall under that subcategory. I'm not really too fond of this method because it seems less organized/scattered.


texts: How does SQL Server handle SSIS project deployments, and what is best practice executing these packages?

texts:
I'm quite new to SSIS (actually SSDT with VS2015), and have a .sln that uses 3 packages:

Main.dtsx
ArchiveFiles.dtsx
FTP.dtsx

The 'entry' point (is this the correct way of thinking of it?) is Main.dtsx, which will execute the 'child' packages (no. 2 and no. 3) at various points in the flow.

To deploy, I simply just copied the wizard (the .ispac file in /bin/Development) which seemed to recreate my project within the Integration Services Catalogs folder on SQL Server.

I have been able to execute the SSIS solution by right clicking and executing the Main.dtsx package from SQL Server. Same with creating a scheduled job (just tell it to execute the Main.dtsx package and everything works).

I'm deploying to SQL Server 2016 and SQL Server 2012.

Questions:

Since the project seems to have been copied verbatim to SQL Server, and the code is just XML, could I edit the deployed packages in the future? (I'm using Git for source control so I would NOT want anyone editing the package directly and then someone else deploying from the source control)
Is my deployment method correct and best practice? Seems like this is too simple and intuitive for the Microsoft world... (considering the headache and pain involved in working with SSIS in general)


texts:
Is there a good way to create a "table of conditions"?

I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. The update part, I can do.

I would like for the whole thing to be user maintainable: they should be able to open a table and change, delete or add rules. They can do it with a table, but they can't edit a code.

A simplified version would be something like this:

If ColumnA = 1 AND ColumnB = 2 THEN X
If ColumnA = 1 AND ColumnB IS empty (NULL) THEN Y
If Column A = 3 AND ColumnB IS anything THEN Z

Is there a nice way to do this? I can programmatically loop through the rows and construct update queries, but it will be desperately slow, and will run as many updates as many condition rows there are. Seems wasteful.

I came up with a token for "anything": (*), and I can use this in a horrid SQL, but I was wondering if there was a better way to do this.

SELECT M.*
FROM MainData M
INNER JOIN Rules R
ON ((M.Check2 = R.C2) OR R.C2="(*)" OR (R.C2 IS NULL AND M.Check2 IS NULL))
  AND ((M.Check1 = R.C1) OR R.C1="(*)" OR (R.C1 IS NULL AND M.Check1 IS NULL))

To make matters worse this is MS Access, but I'm actually interested in the concept at this point.


texts: Is it possible to have one Sql-Server cluster node in multiple Sql-Server clusters?

texts:
Is it possible to create FAILOVER CLUSTER A with Node01 (active) and Node02 (passive) and FAILOVER CLUSTER B with the same Node02 (passive) and Node03 (active)?

I realized that SQL Server instances on passive nodes are not charged, only the OS. So sharing a node for more than one passive Sql-Instance would make sense here. I know, once cluster A and cluster B have an issue I am nipped...

But is this an allowed and/or common scenario?


texts: large SQL Performing quite slow (roughly 2 seconds for 290 rows) when having to deal with entries that can be NULL

texts:
SELECT version_stamp,
           pscdesc_id,
           psc,
           pscdesctext
    FROM   pscdesc_history
    WHERE  ( psc IN (SELECT psc
                     FROM   pso_history AS c
                     WHERE  ( c.project = 121
                               OR c.project IS NULL )
                     AND EXISTS (SELECT * FROM   pso_history AS x
                                        WHERE  x.project = 121
                                        AND x.pso_id = c.pso_id ))
              OR psc IS NULL )
              AND EXISTS (SELECT * FROM   pscdesc_history AS xyy
                       WHERE  xyy.psc IN (SELECT psc
                                          FROM   pso_history AS c
                                          WHERE  ( c.project = 121
                                                   OR c.project IS NULL )
                                                 AND EXISTS (SELECT * FROM   pso_history AS x
                                                             WHERE  x.project = 121
                                                                    AND
                                                            x.pso_id = c.pso_id))
                       AND xyy.pscdesc_id = pscdesc_history.pscdesc_id )

Maybe in prosa what I am trying to do:

Entries in the pscdesc_history table represent changes. Each entry is a change.

so a record with pscdesc_id can get changed, say pscdesctext gets changed. That gives us a new entry with the same pscdesc_id but a new version_stamp and pscdesctext the rest can be empty.
To which project these entries (crucial, since I only want to deal with one project) belong, I don't know. I have to look at the table pso_history. psc is the foreign key to a third table.

Why don't I just join? Since this is a table full of records of changes, the actual foreign key psc might be null. The same applies for pso_history where the forgein key project might be null too.

So i have to get all records of pscdesc_history that are related to a record in pso_history with a pso_history.project of 121 or that are related to a record with pso_history.project that was at some point 121.

Is there something you can see where you would think I could speed up things ? replace the AND EXISTS with something else?


texts:
I have a table name temp with column customer_id , sum and I need to add extra column customer_label (may be on view) which is 1 if customer lies in top 10% according to value of sum, 2 if lies within 10%-20%, 3 if lies within 20%-60%, 4 if lies within 60-100%. How can I do this?

Some sample data is included at sqlfiddle


texts: MySQL (ndb) Cluster and BLOB Datatype access issue

texts:
I'm currently investigating moving our INNODB database to an ndb cluster (vs buying bigger servers (again) and moving the master / slaves to these).

So far my testing on a small cluster has been fruitful, and suggests that we would benefit from this (especially in data resiliency / write access). However I have one small niggling concern. We have one large table (apx 30% of the whole database size - or 60GB apx at present) which is comprised entirely of BLOB fields (80+), and a single field at the start uniqRowID (INT AUTO INCREMENT) to identify the row. Few of these contain very much data (mainly some text/ numbers/ dates etc), many are actually empty (NULL). None of these are indexed. We add new rows constantly, and read the old rows frequently too. Updates are few and far between.

Is this likely to become an issue when running this in a cluster?

From my reading I understand that BLOB fields are held in separate tables/pages in the background, so need to be read separately to the main data. But can't see if this is like INNODB, where the first x data is stored in the main table, the rest held separately, or if the whole thing is separate.

I ran some simple tests, inserting records with similar data, (both disk based and normal), with various joins etc. which showed no differences. But I am concerned that my small test system simply isn't stressing it enough, and don't have the resources (or finances) to run a full size test yet.

Can anyone with some experience of this tell me if I am going the right way with this, or am I just walking into a nightmare?


texts: Large Table Times out - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

texts:
We have a customer who is experiencing Time-outs in our .NET application which uses SQL Server as the backend.
The table in question is the OrderLines table in a SQL Server Database that has 4155151 rows
Lately, the customer has reported, that intermittently they would get these Time-Outs during the process of updating this table.
The code doesn't have a specific Time-out set in the Connection String to the Database but on the SQL Server, the Time-out is set to 10 minutes.
The error reported is as follows:

Execution Timeout Expired.  The timeout period elapsed prior to
completion of the operation or the server is not responding.
at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
 at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)    at
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)    at
System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

We have checked the Indexes for this table and it seems ok (see image attached).
The customers IT guys have checked processes/tasks running on the server throughout the day, and did mention they recently updated to .NET 4.6.1
. Also, they mentioned they have plenty of RAM allocated and disk latency is fine and CPU is fine as well.
Any ideas on how to trouble shoot this issue further?


texts: Where can I find example databases for MySQL, SQL Server and Oracle?

texts:
I want to experiment with the 3 large database management systems.
For this purpose it would be cool to have the same database (the same tables, etc.) for each of those.

Where can I find sql scripts which create identical test databases for MySQL, MSSQL and Oracle?


texts: Migrating data from a boolean column to a varchar column

texts:
My table currently has a boolean column, let's call it the "important" column, reflecting whether a record is urgent or not. I want to transition to using a varchar column that reflects a record's urgency as one of a few possible values, like "normal", "urgent", "extreme". The column name needs to change to, so I'm planning on doing something like this: create a new column called "urgency" to hold the normal/urgent/extreme value, and selectively migrate the existing boolean date into the new column.

Is this a good approach, and are there any gotchas I should watch out for?


texts: Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

texts:
I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row.
Not every row in Orders will have a row in Deliveries (digital media, warranties, etc), but if an order does have a delivery, it will have only one. I am trying to combine data from these two tables into a third table (OrderInformation) to be used for reporting.

I can create the final table one of two ways:

Inserting all orders into the OrderInformation table first and then updating only the rows that have a matching Deliveries record in a second statement.

OR
Inserting all values into the table at once using a LEFT OUTER JOIN between Orders and Deliveries.

I tested this out on my own data and the 'LEFT OUTER JOIN' query consistently outperformed the 'UPDATE' query by 200%, which surprised me.

Which option would perform better in general? And why does it perform better that way? Is performance contingent on join conditions, the type of data involved, etc?


texts: How to retrieve the distinct domains of a compound domain from DQS using SSIS?

texts:
MS Data Quality Services (DQS) has a concept of Compound Domains, which lets you turn say "'99 Chevy Maliboo" into {year:1999, brand:Chevrolet, model:Malibu}

SSIS has a "DQS Cleansing" component. It only seems to let you retrieve one column back, not the distinct fields of the compound domain. Is there a way to do this?

If not, is there a reasonably simple way to do this with a Script Component?

Version info:

Microsoft Visual Studio 2013 Shell (Integrated)
Version 12.0.40629.00 Update 5
Microsoft .NET Framework
Version 4.6.00081

Installed Version: IDE Standard

Visual C# 2013   06154-004-0430007-02752
Microsoft Visual C# 2013

SQL Server Data Tools   12.0.50730.0
Microsoft SQL Server Data Tools

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 12.0.2430.0


texts: Does this error message indicate a SQL injection vulnerability?

texts:
I just took over management for a webservice and today I got a user report about this error. Now I'm no sql injection expert but does this error make this attack possible?

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-community-nt] You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the right
 syntax to use near '') ORDER BY TCOL_date desc' at line 1

I'm asking because for some reason I'm not given access to the code and the previous guy insists that the site is safe so I need to come with some proof or facts that it's actually vulnerable.


texts: Can I use SQL Server Management Studio 2012 on a 2008 server?

texts:
My school is using SQL Server 2008. Last time I used it I only installed (at home) SQL Server Management Studio 2008 part, and used VPN to connect to school's server.

Now I want to install SQL Server Management Studio again, but this time the 2012 version is available.

Will SQL Server Management Studio 2012 work on the school's SQL Server 2008? Or do I need the 2008 version?


texts:
I have a table with SELECT fName, lName FROM person
It returns 3 results
I want to return a concat(fName, ' ', lName) and 2 "and"s in between each concat.

Jon Snow and Margaery Tyrell and Peter Griffin

But I also want it to be dynamic to scale if the result grows. So if it is 6 it will have the names concat with 5 "and"s.

I am trying to output this to a VARCHAR variable.
Using MS SQL Server 2014.


texts: How can I keep ASM groups in Oracle RMAN Duplicate?

texts:
Oracle 11.2.0.4
Whenever I issue a DUPLICATE on a Oracle backup, it always places all the files in to the default ASM diskgroup.  I usually have the same ASM diskgroup structure at source and destination.  I know I can SET NEWNAME for individual files to control it, but I'd really just like the files to go in the same group they came from.
Is there a RMAN DUPLICAE parameter to put them in the same ASM diskgroup name that they started from?


texts: Permanent solution for Fixing buffer size

texts: I have installed MySQL 5.7 on windows server 2016 and set the buffer pool size to 4 GB through SQL query but when the server is restarted buffer pool size getting reduced resulting in the slowness of application hosted on it ... need suggestion so as to permanently fix the buffer pool size.....


texts: How to match on a longer word using MySQL Full Text Search?

texts:
I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter)

When I search something, it will first search in the company table and then search the query in the tags field.

I wrote the query but I have a problem. When I search "emlak", everything is fine but if I search "emlakçı" it can't find anything. Do I have to enter "emlakçı" as a keyword in tags field or is it possible to solve this with an another way?

Here is my query :

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC


texts: Aborted connection on an Update statement only

texts:
I have node-mysql client that gets "ER_NO_SUCH_USER" whenever it does an UPDATE query. All other type of query are successful but UPDATE fail.

Meanwhile in the server's log I get the standard:
"[Warning] Aborted connection ... (Got an error reading communication packets)"

The client's DB user has the following permissions:
GRANT SELECT, INSERT, UPDATE, DELETE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON . TO ''@'%' IDENTIFIED BY PASSWORD ''


texts: Don't show local databases in SQL Always-On AG

texts:
We're installing a SQL Server Always On AG solution(SQL Server 2016) in a test-enviroment at our organisation.
When testing the features we notice that all databases from a node are listed when we connect to a listener.
Is there a way to only show the databases that are part of the AG and not the databases from other AG's or local DB's on the node that hosts the listener?

Thanks in advance!


texts:
The TRUSTWORTHY setting can be rather dangerous if you aren't careful and except for specific circumstances the recommendation is to keep it turned off.  However by default the MSDB database has TRUSTWORHTY set ON by default.  I'm curious why?

I've read this entry in BOL

  Note By default, the TRUSTWORTHY setting is set to ON for the MSDB database. Altering this setting from its default value can result in unexpected behavior by SQL Server components that use the MSDB database.

But I'm curious about specifics.  Why specifically does MSDB need TRUSTWORTHY turned on? What functions use it?


texts: I have tables stored in a Trucrypt partition and I would like to disconnect that if the users not using the mysql. There is the INFORMATION_SCHEMA.PROCESSLIST but I would like to know the users last activation time. For example, if nobody run a mysql command within 10 minutes, then I can unmount the Trucrypt. How can I check that?


texts: Not authorized on test to execute command

texts:
Here is my code:
mongoose.connect(consts.database, {
    useNewUrlParser: true,
    useUnifiedTopology: true,
    sslCA: consts.databaseCert,
});
//...
const user = await db.userModel.findOne({
    username: usernameLowerCase
}).exec();

Here is my DB connection string (anonymized):
mongodb://myUser:userPW@SG-staging-111.servers.mongodirector.com:27017,SG-staging-43334.servers.mongodirector.com:27017?replicaSet=RS-staging-0&ssl=true&authSource=stagingDB

I'm getting this error:
MongoError: not authorized on test to execute command {
    find: "users",
    filter: {
        username: "bob"
    },
    projection: {},
    limit: 1,
    singleBatch: true,
    batchSize: 1,
    returnKey: false,
    showRecordId: false,
    lsid: {
        id: UUID("0a9400e3-83e3-429c-b8c9-92ade2ff210e")
    },
    $clusterTime: {
        clusterTime: Timestamp(1613200171, 1),
        signature: {
            hash: BinData(0, FED473B580D13E7E5073756DB5140981AADB2985),
            keyId: 6928615819992774977
        }
    },
    $db: "test"
}

DB user's info:
myUser  [{"role":"readWrite","db":"stagingDB"}]

I have no clue why I am getting this error not authorized on test to execute command, and in the return string $db: "test" I don't even have a database named test. What could I be doing wrong? I just recently added this new user myUser, but now I'm getting this error. Does this error mean that the user is not authorized to "test" commands? Or, does it mean that I am trying to (somehow) connect with a DB named "test"?


texts: I am using a SQL Server database. If NULL value is inserted to a VARCHAR datatype column, what will be the memory size used for the column?


texts: Foreign Keys to tables where primary key is not clustered index

texts:
I'm focusing on SQL Server here, but I think this is a database agnostic question, really.

I have a table whereby a Guid is logically the primary key.  However, uniqueidentifiers apparently make terrible clustered indexes, so I'm planning on creating a dummy key as an identity column and making that the clustered index for the table.

When it comes to foreign keys referencing this table however, should the foreign keys reference the clustered index or the primary key?  I'm thinking it should be the primary key still, right?  And that's because the clustered index doesn't technically HAVE to be unique, whereas the primary key does...?

Also, just to double check, if I make another column the clustered index, that doesn't mean I need to add a non-clustered index to my primary key column, does it?  In other words, does my column automatically have an index on it just by being the primary key?


texts: How to export large table data into different files in oracle?

texts:
I have a table which contains 1 million lines and need to export into different files which containing 100k lines each.
sample_01.sql ,sample_02.sql and so on
How can we take export like this in oracle?


texts: How to aggregate only unique and not null values from column into a JSON array?

texts:
Suppose I have a table like this:
| id | linked_id | another_id |
|----|-----------|------------|
| 1  | 1         | 1          |
| 1  | 2         | null       |
| 1  | 3         | 1          |

I would like to aggregate results by id - collect linked_ids and another_ids into a JSON objects. So in the end what I would like to get is this:
id: 1
linked_ids: [{ id: 1 }, { id: 2 }, { id: 3 }]
another_ids: [{ id: 1 }]

Notice how in the case of another_id only unique ids were aggregated and null got ignored. I'm able to achieve this with regular arrays by using:
array_agg(distinct another_id) filter (where another_id is not null).
However I need to get JSON array of objects in the end, not regular one. Any ideas?


texts: Create user with only SELECT and INSERT permissions

texts:
I'm working on a project that requires a database user to have only SELECT and INSERT permissions for all tables in a database.

The following question provides a solution but I'd have to run the script every time I add a new table. Seems like it's specific to SQL Server 2000.

How do I create a user in SQL-Server that only has access to one table, and can only insert rows

There must be an easier way?


texts: MySQL can't connect using private IP address

texts:
I have a fresh install of MySQL 5.7 on my Windows machine. When I make a connection using root@localhost, I am able to connect, but when i try to make a connection root@192.168.1.10 (the private IP address of my server) I  get the following error:

mysql 5.7 access denied for user 'root'@'192.168.1.10'.

I opened my my.ini file and added the line bind-address=0.0.0.0. I saved the file and restart my MySQL instance, but still not luck the same error appears.

I then tried:

**GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;**

This did not work either. I then tried to change the password for root to make sure I was using the correct password:

UPDATE mysql.user SET authentication_string=PASSWORD('password')  WHERE  User='root';

But no luck.


texts: SQL Server - What access do I have during a transaction rollback?

texts:
As the title says, do I have read/write access to my entire database while it is rolling back a transaction?

Or read/write access to only the tables that the transaction did not write to? What is the locking behavior?

If I do have read access, what happens when I am reading a table where a transaction rolling back wrote to (does the query fail or just wait)?

Sorry for the questions, I have tried reading online (ROLLBACK TRANSACTION (Transact-SQL)), but it seems to not touch on this level of depth.