texts: Not the same PostgreSQL database size on master/slave!

texts:
I have a PostgreSQL database 9.4.4 on redhat 6.6 in streaming replication. The problem that I don't have the same database pretty-size on master and slave. The database size on slave is almost two times bigger than the master database.
Have you any idea about this case?

Thank you for answering. The problem that I had the same difference whith "linux command "du" and database searching informations.

I have these results:

[master:postgres:]$du -k $PGDATA
...
2387924 /pgqdata/pgambari/data
[slave:postgres:]$du -k $PGDATA
...
4059916 /pgqdata/pgambari/data

[master:postgres:]$psql -c "SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;"
 database_name | size_in_mb
---------------+------------
 pgrepmon      | 7123 kB
 postgres      | 7027 kB
 template1     | 6971 kB
 template0     | 6843 kB
 ambari        | 2007 MB
(5 rows)

[slave:postgres:]$psql -c "SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;"
 database_name | size_in_mb
---------------+------------
 pgrepmon      | 7131 kB
 postgres      | 7027 kB
 template1     | 6971 kB
 template0     | 6729 kB
 ambari        | 3645 MB --<< HERE
(5 rows)


texts: calling mysql procedure return empty result

texts:
when i call procedure it gives me empty result

   call hussian('prstate','table_1','column1,column2,column3',1)

it run but gives empty result, please help me...

 DELIMITER //

 CREATE PROCEDURE hussain(IN src_col varchar(20),
                     IN tblname varchar(100),
                     IN col_names varchar(100),
                     IN _leadid int)

 BEGIN
    IF (col_names  = 'column1' AND col_names  = 'column2'  AND col_names = 'column3')
THEN

    SET @sql = CONCAT('insert into ', tblname, '(',col_names,')', ' select ' ,src_col,',','firstname,lastname from tbllead where id = ',_leadid);
    PREPARE s1 from @sql;
     EXECUTE s1;
    DEALLOCATE PREPARE s1;
 END IF;
  END //
  DELIMITER ;

it insert data into table when i romove if condition.....


texts: Within a RHEL 6.3 + High Reliability Add-on, how do I add DB2 (v9.7) into a service group to have the service controlled by the cluster?

texts:
I have a cluster of machines running RHEL 6.3 with the High Reliability Add-on.

I have configured a Clustered Logical Volume Manager (CLVM) file system for DB2, which is working correctly.  Our DBA's have installed DB2 v9.7 on the cluster, providing me with a basic stop and start script.  However, after a month of research, I have not been able to figure out how to set up the service within the cluster.

Can anyone point me in the right direction?


texts: How to tell different types of Logins apart and how to see the users?

texts:
I have a novice question: I could see different (presumably) AD Groups and AD accounts under the SQL Server Server Logins. However, I am unable to tell whether some of these users are AD users vs AD Groups. Someone mentioned you could look at the LOGO and if it's a multiple person it's a group, single person is a user. Is this the best way to tell? That doesn't seem reliable.

Also, how can I see the USERS within an AD group? I can see the group, but I need to see the users. I do NOT have access to the AD Server.

Thank you so much.


texts:
How can I prevent my finished queries to stay in pool?

Below is the image of my database and a lot of my finished queries are staying and I want them to be removed but don't know how.

This is my code that I made to sent the data from my database using Express:

Here is my articles.js file that sends the data as json.

router.get('/articles', (req, res) => {
  let sql = "SELECT * FROM ARTICLES";
  myDB.query(sql, (error, results) => {
    if (error) {
      console.log(error);
    } else {
      res.send(results)
    }
  })
});

and this is my connection.js that creates the connection to MySQL and Express

    const mysql = require('mysql');
const myDB = mysql.createPool({
    connectionLimit: 10,
    host: 'REMOTE_DATABASE_ADDRESS',
    user: 'hmidev',
    password: 'hmidev',
    database: 'VD_NEW_WEBSITE'
});

myDB.getConnection((error, connection) => {
    if (error) {
        console.log(error);
    } else {
        console.log(`Successfully connected at VD_NEW_WEBSITE`);
    }
});

module.exports = myDB;


texts: Apparently, my CLR assembly function is causing deadlocks?

texts:
Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL Server has GETDATE() and Oracle has SYSDATE. They perform the same function but they are different words. We wrote a wrapper UDF called NOW() for both platforms which wraps  the relevant platform specific syntax in a common function name. We have other such functions, some of which do essentially nothing but exist solely for the sake of homogenization. Unfortunately, this has a cost for SQL Server. Inline scalar UDFs wreak havoc on performance and completely disable parallelism. As an alternative, we wrote CLR assembly functions to accomplish the same goals. When we deployed this to a client they started experiencing frequent deadlocks. This particular client is using replication and high availability techniques and I'm wondering if there's some sort of interaction going on here. I just don't understand how introducing a CLR function would cause problems like this. For reference, I've included the original scalar UDF definition as well as the replacement CLR definition in C# and the SQL declaration for it. I also have deadlock XML that I can provide if that helps.

Original UDF

CREATE FUNCTION [fn].[APAD]
(
    @Value VARCHAR(4000)
    , @tablename VARCHAR(4000) = NULL
    , @columnname VARCHAR(4000) = NULL
)

RETURNS VARCHAR(4000)
WITH SCHEMABINDING
AS

BEGIN
    RETURN LTRIM(RTRIM(@Value))
END
GO

CLR Assembly Function

[SqlFunction(IsDeterministic = true)]
public static string APAD(string value, string tableName, string columnName)
{
    return value?.Trim();
}

SQL Server Declaration for CLR Function

CREATE FUNCTION [fn].[APAD]
(
    @Value NVARCHAR(4000),
    @TableName NVARCHAR(4000),
    @ColumnName NVARCHAR(4000)
) RETURNS NVARCHAR(4000)
AS
EXTERNAL NAME ASI.fn.APAD
GO


texts:
I have a query that uses a CTE which contains millions of rows. I plan to call this query many times, and the CTE returned each time will have most of the same rows. Is it possible for me to cache the CTE somehow so that only new rows need to be calculated?

The (slightly simplified) query is:

WITH vals AS (
    SELECT '2013-08-01 0:00'::timestamp  AS frame_start,
           '2013-09-01 0:00'::timestamp  AS frame_end,
           '1 day'::interval             AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
                   lead(start_time, 1, frame_end) OVER (ORDER BY start_time     NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS start_time,
               frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
), market_trades_ts AS (
    SELECT time_range, td.id
    FROM intervals i
    LEFT JOIN market_trades td
    ON td.timestamp >= COALESCE(lower(i.time_range), '-infinity') AND td.timestamp < COALESCE(upper(i.time_range), 'infinity')
)
SELECT time_range, count(*) AS agg
FROM market_trades_ts td
GROUP BY time_range
ORDER BY time_range;

It would be great if market_trades_ts could be cached so that, for any intervals which it's seen before before, it can pull the previous result set, and then take its union with the new rows (for intervals which it hasn't seen before).

Is this possible? It seems like it will speed up my query dramatically.


texts: Migrating from SQL Server 2008 R2 to SQL Server 2014

texts:
I'm migrating from SQL Server 2008 R2 to SQL Server 2014. Can someone tell me if my approach will work?

Current Environment:

SQL Server 2008 R2
Windows 2008 R2

Steps to Migrate from SQL Server 2008 R2 to SQL Server 2014:

Get clean install of Windows Server 2012
Install SQL Server 2014 Standard x64
Backup and restore SQL Server 2008 R2 databases to the 2014 instance
Import SSIS packages into SQL Server 2014
Do conversion of DTS packages to SSIS packages in SQL Server 2014

Done.

In theory will this work? I don't want to do an in-place upgrade. I can't because I'm going from a physical server to a virtual server as well as doing an OS upgrade.


texts: How much memory do I need for innodb buffer pool?

texts:
I read here that

  You need buffer pool a bit (say 10%) larger than your data (total size
  of Innodb TableSpaces)

On the other hand I've read elswher that innodb_buffer_pool_size must be up to %80 of the memory. So I'm really confused how should I choose the best size for the pool. My database size is about 6GB and my total memory 64GB. Also I'm wondering if I increase the buffer pool size, I should shrink the number of maximum connections to make room for extra buffer, or these parameters are independent. Thanks


texts:
I have one column which is varchar2(4000 char). Max length of data is 3189. I have checked lenghtb also which is giving the same length 3189.

we are displaying this data in one of FE tool(Speedbase) as is . However, while traversing through pages , throwing

  ORA-01704: string literal too long

Can you please help?


texts: Need advice for table design for multi-user access

texts:
I have an inventory application that needs to support multi-user. As of right now, only one user can access and manage their items. I've gotten a lot of requests to support multiple users so they can all manage the same inventory.

I have a table called user_items that stores item information. To keep it simple, I'll include just the relevant column names.

mysql> select primary_item_id, user_id, item_name from users_item limit 1;
+-----------------+---------+-----------------+
| primary_item_id | user_id | item_name       |
+-----------------+---------+-----------------+
|             100 |       4 | Stereo Receiver |
|             101 |       5 | Couch           |
+-----------------+---------+-----------------+

I've created a mapping table to map the items to users.

+-------------+-------------+----------------+------------+-----------+
| map_user_id | map_item_id | unique_item_id | item_owner | privilege |
+-------------+-------------+----------------+------------+-----------+
|           4 |       100   |              1 |          1 |      NULL |
|          13 |       100   |              1 |          1 |      NULL |
|           5 |       101   |              1 |          5 |      NULL |
+-------------+-------------+----------------+------------+-----------+

The unique_item_id column is the item_id that's displayed to the users. So item #1 for user #4 is "Stereo Receiver." Item #1 for user #5 would be a couch. The item_owner field doesn't mean much for the time being. I'm not sure if I need it but it's there for now as I play with the schema and code.

Anyways, this works fine, except I need multiple users to track the same item(s). Instead of offering the opportunity to share items AND track their own items, my version of "multi-user" means they have to track the same exact number of items. If user #13 adds a new item, user #4 also has access to said item.

Any suggestions? I think I shot myself in the foot by offering unique ids for each item. It is what it is so now I have to work with what I have.


texts: High availability on a subset of the data

texts:
On my site, I have a set of big databases (+6TB total) that is running on a SQL Server 2012 instance. One of these is the main DB and the other ones are archived data that are queried sometimes (unions on the main db stored procedures) but do not have to be 24/7 online. Only the main db is mirroed to another datacenter because of storage constraints.

To mantain all this there is a job that keep moving old data from the main to the archive dbs, but this is slow and ineficient. I plan to improve all that with a sliding window paritioning solution but I couldn't find a way to switch an archived partition or table to another database without data movement on the datafiles.

I can put everything on a single database but I couldn't find an HA solution that I could choose the objects that actually need the high availability.

I'm currently doing some tests with replication but it will be tricky to pull it off and it is not a true HA solution.

Any ideas on how I can parition my data and only provide HA to a subset of the whole data?

Thanks in advance.


texts:
DECLARE @yourtable TABLE (id int, [name] varchar(100), category_sort varchar(100));
INSERT @yourtable VALUES
(1, 'Campaign1','Beverage'),
(2, 'Campaign2','Beverage'),
(3, 'Campaign3','Food'),
(4, 'Campaign4','Beverage'),
(5, 'Campaign5','Hot Drinks'),
(6, 'Campaign6','Advertizing'),
(7, 'Campaign7','Marketing');

WITH x AS
(
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY category_sort ORDER BY id)
FROM @yourtable
)
SELECT id, name, category_sort
FROM x
ORDER BY rn, category_sort;

Now when i run the above code it is return :

6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
7 Campaign7 Marketing
2 Campaign2 Beverage
4 Campaign4 Beverage

But actually what i need is :

6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
2 Campaign2 Beverage
7 Campaign7 Marketing
4 Campaign4 Beverage


texts: RAID0 instead of RAID1 or 5, is this crazy?

texts:
I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or other documentation you can point me to on this topic, that would be great.

We have 3 servers in 2 datacenters that are part of an SQL cluster. They are all running SQL Server in an Availability Group. The primary has a replica sitting right next to it and another in the other datacenter. They are running synchronous replication with automatic failover. All drives are enterprise class SSDs. They will be running SQL Server 2017 or 2019.

I'm thinking that there would be multiple benefits to running them on RAID0 arrays over other methods with few, if any, real drawbacks. The only negative I'm currently seeing is lack of redundancy on the primary server, so it failing increases. As pros:

If a drive fails, rather than run in a slowed, degraded state until someone receives a notice an manually acts on it, the server will immediately fail to a secondary maintaining full operational capability. This will have an added benefit of notifying us of a failover, so we can investigate the cause sooner.
It reduces the chance of failure overall per TB capacity. Since we don't need parity or mirror drives, we reduce the number of drives per array. With fewer drives there is less total chance of a drive failure.
It is cheaper. Needing fewer drives for our required capacity obviously costs less.

I know this isn't the conventional business thinking, but is there something I'm not considering? I'd love any input either pro or con.

I'm not trying to do this for query performance gains, though if there are meaningful ones feel free to point them out. My primary concern is failing to consider or address a reliability or redundancy issue that I haven't thought of.

The OS is on a separate mirrored drive, so the server itself should stay up. One of those drives can be replaced and again mirrored. It is small and there aren't any database files other than system DB's on it. I can't imagine it taking more than minutes. If one of the data arrays fails, we replace the drive, rebuild the array, restore and resync with the AG. In my personal experience, restoring has been MUCH faster than a RAID5 drive rebuild. I haven't ever had a RAID1 failure, so I don't know whether that rebuild would be faster or not. The restores would be coming from a backup and rolled forward to match the primary, so the load increase on the primary server should be very minimal only syncing the last few minutes of logs with the recovered replica.


texts: Return counts for multiple ranges in a single SELECT statement

texts:
I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3, the number of scores between 4 and 6, and the number of scores between 7 and 10. Something like the following:

SELECT
  COUNT(*) as total,
  COUNT(
    SELECT * from foo where score between 0 and 3;
  ) as low,
  COUNT(
    SELECT * from foo where score between 4 and 6;
  ) as mid,
  COUNT(
    SELECT * from foo where score between 7 and 10;
  ) as high
FROM foo;

I tried this, but got an error with the SELECT in the COUNT statements. Any ideas how I can do this? I'm sure there's a super simple way in Postgres. I just can't figure out the correct terms to Google for.


texts: Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?

texts: I learned that innodb_buffer_pool_size makes InnoDB behave like an in-memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or memcached would be unnecessary in such a setup?


texts: How to write a query which finds all circular references when a table references itself? (II)

texts:
I have the following schema (names changed), which I cannot change:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);

That is, each record is a child of another record.

The root node is the record with Id equal to 0.

I want to run query which will find all circular references. For example, with the data

INSERT INTO MyTable (Id, ParentId) VALUES
    (0, 0),
    (1, 0),
    (2, 4),
    (3, 2),
    (4, 3),
    (5, 6),
    (6, 5),
    (7, 5),
    (8, 8);

the query should return

Id | Cycle
2  | 2 > 4 > 3 > 2
3  | 3 > 2 > 4 > 3
4  | 4 > 3 > 2 > 4
5  | 5 > 6 > 5
6  | 6 > 5 > 6
7  | 7 > 5 > 6 > 5
8  | 8

This is similar to my question How to write a query which finds all circular references when a table references itself? from last year, except for the following differences:

The root node is hard-coded to have an Id of 0.
Any self-cycle (8 in the example above) except for the root should be considered a cycle
The accepted answer runs into infinite recursion on the case 7 > 5 > 6 > 5 in the example above

I am having trouble coming up with a query to account for these. Suggestions?


texts: how to reduce the size of the datafiles of a oracle DB?

texts:
we use a oracle db (11g release2) and the undo tablespase exists of two datafiles and requires 48 GB.
But we just need this size for the boot and the import.
Currently we just use 10 % from the datafiles...

Is it possible to minimize the datafiles online to the needed size ??


texts:
Scenario is this, prod is two two node FCI with mirroring (primary/disaster DC). This is working just fine. New hardware is coming and a version upgrade to SQL Server 2012 (from 2008 R2) in the form of two two node FCI in primary with async AG to get the data over to the second FCI. (DR is all SAN mirroring with identical boxes in DR site)

Can I take the 2008 R2 primary and log ship all my databases to the SQL Server 2012 FCI primary while it is in an AG?

If not, I can leave the AG off and just log ship to do the migration, go live and setup the AG after the fact, that's not the end of the world. Just trying to see if I can skip the this setup.


texts: How to store GPS coordinate and search places in a radius from a NoSQL DBMS (like DynamoDB)

texts:
My team need a DBMS like DynamoDB to store large amount of data, principally places and coordinates.
I've considered to use some GIS-based DBMS (like PostGIS) with an index on the POINT, but DynamoDB seems great for our use.
What is the best method to store the coordinate and quickly retrieve all objects in a particular radius?
In PostGIS it's easy, something like this:
SELECT *
FROM places
WHERE ST_DWithin(coordinate, ST_GeomFromText('POINT(45.07085 7.68434)', 4326), 100.0);

How can I do something like that in a NoSQL DBMS?


texts: Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies

texts:
I'm trying to upgrade MySQL 5.1 to 5.5 using yum/rpm combo (yum to remove MySQL 5.1 and rpm to install MySQL 5.5). Most instructions I've found suggest to do first:

yum remove mysql mysql-server mysql-libs

However doing so will remove in my case lots of other packages which I definitely don't want to remove:

================================================================================
 Package              Arch        Version                   Repository     Size
================================================================================
Removing:
 mysql                x86_64      5.1.69-1.el6_4            @updates      2.4 M
 mysql-libs           x86_64      5.1.69-1.el6_4            @updates      4.0 M
 mysql-server         x86_64      5.1.69-1.el6_4            @updates       25 M
Removing for dependencies:
 cronie               x86_64      1.4.4-7.el6               @cr           166 k
 cronie-anacron       x86_64      1.4.4-7.el6               @cr            43 k
 crontabs             noarch      1.10-33.el6               @cr           2.4 k
 mysql-devel          x86_64      5.1.69-1.el6_4            @updates      388 k
 percona-toolkit      noarch      2.2.4-1                   @percona      5.7 M
 perl-DBD-MySQL       x86_64      4.013-3.el6               @base         338 k
 php-mysql            x86_64      5.3.3-23.el6_4            @updates      216 k
 postfix              x86_64      2:2.6.6-2.2.el6_1         @base         9.7 M
 sysstat              x86_64      9.0.4-20.el6              @base         807 k

Transaction Summary
================================================================================

How can I upgrade MySQL without removing those packages? Can I somehow remove MySQL 5.1 without dependencies (well, maybe besides mysql-devel) and get away with it? (without libraries mismatch). I've found one blog that suggest to upgrade MySQL in yum shell, however I'm not sure if it would change anything? :


texts: When I'm pasting text in to my query window from Office the single quotes are being changed to a backtick. I have been through all the options in both Office and SQL but I can't find the/a setting to stop this happening. Is there such an option?


texts: SQL Server - How to prevent public connections?

texts:
Today we found that two of our SQL Servers (both 2017 CU21) are accepting connections from any random domain account, even those that most definitely do not have a direct login or access via AD group membership.
On each server there is a user database that allows these random domain accounts to connect.
Based on the configuration we see, we would expect any random domain account (i.e. any that does not have a login on the instance) to get a login failure, and aside from that to also be blocked from connecting to the user database.
For these databases, the guest account is disabled, but for good measure we have executed:
REVOKE CONNECT FROM guest

This made no difference.
So two questions:

How can we turn off / prevent these public connections from succeeding?
What TSQL statement could show us the problematic configuration?

Things we have looked at so far:

The 'public' server role seems to have the default configuration, same as all our other servers where this is not an issue
The guest user definitely shows as disabled on all databases & servers
The 'public' database role has just the default permissions we would expect to see
It is only 1 user database (plus master & msdb & tempdb) on each server that is accessible to these random accounts -- all the other databases behave as normal, i.e. the account cannot connect to them.
The database Containment Type setting is 'None' is all cases.
There is no login present on either server that covers all domain users

Some context: the two servers are related -- they are the DEV & PROD servers for the same application. It is very likely this configuration was done deliberately for some historical reason -- but no-one working here now knows why, so we would like to disable this public access.


texts:
I need to calculate a value by using it's previous value * ColumnX

Here is the formula:

DividendReinvested = "Previous DividendReinvested" * (1+ValuationPercChange/100)

Can I do this without a loop? CTE possible? Using SQL Server 2008 R2.

Here is sample data. I use RowNum as unique identifier to set current row from previous row.


texts: Aggregate function that picks any/first/last/random value

texts:
I run a query which looks like below

WITH src AS (
    ...
)
SELECT MAX(field1), array_agg(field2)
FROM src

I have knowledge that field1 is the same across all rows returned from src so I really don't really care whether the aggregate function used with it is MAX or MIN. In my case field1 is an array so I am curious if there is a more efficient way to grab any value of field1 which doesn't include overhead of comparing it with the previously saved MIN/MAX. If there was a function to grab the first / retain the last value of field1 that would be ideal.


texts: Can sp_BlitzFirst pass the @ExpertMode parameter to sp_BlitzWho

texts:
When running sp_blitzFirst @expertmode = 1 the first result set(result set of sp_blitzwho)does not return nearly as many columns and it does in Brent's video. With a little troubleshooting it was uncovered that the reason this is happening is because the calling stored procedure is not passing @expertMode = 1 to sp_blitzWho.
To prove that i can run sp_blitzWho and pass @expertMode = 1 in which case the etire result set is returned.

So my question is: Was there a change to latest version of these stored procedures?
Where can i find the version that work like the one Brent displays in his video?
Thank you for looking in to this.
Best Regards.


texts: Xquery, insert, Trigger and no place to squeeze stored procedure

texts:
I have a Stored procedure, which is, basically, xquery pulling data from xml and inserting it into multiple tables. Once it's inserting into last of the mentioned tables, the trigger fires up and then this data is used to do more data manipulation.

it all goes nicely up to a point where I have to do some serious string cutting & splicing. By serious I mean adding to one string client surnames and first names selected from multiple rows and columns from a table. previously it was done by way of 3-rd party software, which shredded xml and called SQL Server stored procedure with said string manipulation for each row from dataset it derived from xml.

Now that I'm using Xquery this is unavailable - insert from select from xquery will not allow to either re-use the said stored procedure and I can't use the bit of code to do the manipulation in xquery.

Sample code for string manipulation:

select top 100 @string = @string + Surname +' & '
from usr_bie
where ref = @ref and code = @code and type = 'Client' order by mc desc
Select @string = left(@string,len(@string)-2) + ', '
Select top 100 @string = @string + FirstName +
(case
 when middlename is not null
 then ' ' + Middlename
 else '' end
) + ' & '
from usr_bie
where ref = @ref and code = @code and type = 'Client' order by mc desc
Select @string = left(@string,len(@string)-2)

I need to assign the result of the above to value in xquery:

Insert into a (col1, col2,...)
Select
t.c.value('(../Ref)[1]','varchar(50)') as col1
....
from @xml.nodes(...)t(c)

I understand that Xquery inserts multiple rows, so there's no easy way to call the old stored procedure for each row, but OTOH I also can't do my @string manipulation as above.

Is there a solution I'm not familiar with?

I converted the string manipulation code to function (again), and this time it works. But there still is work to be done on the other values from xml... So question is: is there a way to do RBAR with xquery, so I can manipulate data in various ways as I need? As it is right now, Insert is being done on multiple rows, so I can't do do simple condition: if @variable = somevalue begin..., where @variable is passed as a stored procedure parameter? And Is there any sense in my sticking to the slow way (rbar), instead of - like @Duffy suggested - going to CTE?


texts: How to reference column of DAX variable?

texts:
I have a DAX variable that contains a table. How can I reference a particular column in that variable?

For example, in the below command, the EVALUATE returns an error. But it works if I replace table1 with FactInternetSales (which is the name of the table which contains that column)

define var table1=FactResellerSales
EVALUATE ROW("a",COUNTBLANK(table1[SalesAmount]))


texts: Subselect of MAX(Version) takes many minutes, though there are only ~20K records

texts:
I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain.

Mainlines -> Releases -> Overlays -> Calibrations <- Parameters

Calibrations, then, is the child of the the Mainline-Release-Overlay chain, and Parameters. This is the set I want to return.

Now, the complexity is that -- to save space -- we are storing the base Mainline's calibrations, and then just the DIFFERENCES when changes happen at the Release and Overlay levels. This produces a "base" set of Calibrations of about 16K rows, and then a few hundred changes each Release, and perhaps just a few changes each Overlay. Deletions of parameters happen. To keep track of this, Calibrations has a Status field (tinyint) which is set to 1 for a deletion.

Overlays get versioned successively. So, to get a complete "calibration," we need to query the Calibrations table for the latest version of a Parameter's data, up to a particular Overlay version number. Parameter metadata may change, but the names stay the same, so these Calibrations may refer to differing versions of Parameters, albeit with the same name.

Up till now, the following has worked perfectly, and instantaneously (with the following caveats):

SELECT c.Parameter_ParameterID, p.Designation AS Name, c.Data, o.Version
 FROM Calibrations c, Parameters p, Overlays o, Releases r
 WHERE r.Mainline_MainlineID = 9
    AND o.Release_ReleaseID = r.ReleaseID
    AND c.Overlay_OverlayID = o.OverlayID
    AND c.Parameter_ParameterID = p.ParameterID
    AND o.Version =
        (SELECT MAX(o1.Version)
            FROM Parameters p1, Calibrations c1, Overlays o1, Releases r1
            WHERE r1.Mainline_MainlineID = 9
            AND o1.Release_ReleaseID = r1.ReleaseID
            AND c1.Overlay_OverlayID = o1.OverlayID
            AND c1.Parameter_ParameterID = p1.ParameterID
            AND p1.Designation = p.Designation  -- New condition
            AND o1.Version <= 68)
    AND c.Status != 1 -- Changed from boolean to tinyint
ORDER BY Version DESC, Name

The problem has come in since we have started (properly) tracking Parameter versions, so the inner match for the Parameter can no longer by on the ParameterID, but rather the Designation (i.e., Name). At first this wasn't a problem, but it is now that Calibration Status is no longer a boolean for deletion. Now it can also be set to 2 for newly introduced, and 3 for reinserted.

Adding these two changes have caused this query to take 10-15 minutes to run! How is that even possible? Even if I contorted the query to connect every row to every other row, it shouldn't take this long!

There are two maddening things here. One is that, when this initially became a problem, I put an index on the Parameters Designation field, and it made it work again. Now, deleting and recreating it doesn't make a difference. Also, there's still a special case, in that, if I have exactly two Mainlines in the database, it still runs instantaneously. If I have only one or more than two, it takes forever.

I've tried variously commenting out sections, like the o1.Version <= condition and the c.Status != condition, and sometimes, it works again. I can't get a handle on the differences in the various cases, and I'm at the end of my rope. I know I'm a tiny trick away from getting it to run fast again, but it's apparently beyond me, and this is a show-stopper for my beta plans next month.

I've been programming for 35 years, but I've never had to deal with SQL like this before, and SSMS's tools for showing explain plans mean nothing to me. I would like to understand the fundamental issues and get a fix.

Execution plan for the long query.
Execution plan for the query when commenting out c.Status != 1


texts: Error creating memory optimized filegroup in SSDT

texts:
I am attempting to add a memory optimized filegroup to a SQL server database project in SSDT. This is for SQL Server 2017, using Visual Studio 2017.

However compiling the project (pressing F5 to build) is resulting in an error. This error does not occur when deploying (via Deploy).

The filegroup is created as normal (with the SQLCMD variable as scripted by SSDT):

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA

However this results in an error:

  The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.

However, database settings show that auto close is indeed disabled:

The deployment script generated by SSDT for some reason is creating the database, creating the filegroup, and then only setting AUTO_CLOSE off afterward. It is possible this is resulting in the error:

CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
PRINT N'Creating [MemoryOptimizedFilegroup]...';

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [MemoryOptimizedFilegroup_69323650], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_MemoryOptimizedFilegroup_69323650.mdf') TO FILEGROUP [MemoryOptimizedFilegroup];

GO
USE [$(DatabaseName)];

GO
IF EXISTS (SELECT 1
    FROM   [master].[dbo].[sysdatabases]
    WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL,
            CURSOR_CLOSE_ON_COMMIT OFF,
            AUTO_CREATE_STATISTICS ON,
            AUTO_SHRINK OFF,
            AUTO_UPDATE_STATISTICS ON,
            RECURSIVE_TRIGGERS OFF
        WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CLOSE OFF
        WITH ROLLBACK IMMEDIATE;
END

I am not certain whether AUTO_CLOSE is enabled or disabled by default after CREATE DATABASE. If ON by default, it seems that SSDT is generating the deployment script in the wrong order. If OFF by default, then I don't understand why the error exists.

Has anyone had success creating a memory optimized file group in an SSDT project?


texts:
I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32).
There will only be one query run against table.

Select (column 1),(column 2) from mytable where column 3= xxxxx

Table will be dropped once a month and recreated with fresh data. Other than that, this table will be read only, and only queried with the above query.

The column that is used in the query is not unique or null.
I assume that I would add a regular index to that column. Would there be any benefit to adding indexes to the other 2 columns?

The max number of rows would be around 5,000

Also, which table engine would be best?


texts: Yes/No columns in logical database design

texts:
I have an assignment where I have to design a logical model.

I have a relation NURSE that has "nurse_id" and "certification". The certification attribute has yes/no values, so, should I move it to another relation, or is it okay to keep it in the same NURSE relation?

And is the suitable data type for that attribute (CHAR)?

Thank you,


texts: SQL Server database backup file is double final size until backup completes

texts:
I've just completed writing a backup statement for a reasonably large database (1.3 TB), and I've been testing it out using a much smaller database (10 GB + 10GB log file). The backup writes to three destinations. During the testing I noticed that whilst the backup is running each backup file is about 1.2 GB in size, but once the backup process has completed the files shrink to less than 600 MB.

I'm interested in knowing:

why the files are bigger during the backup process and then shrink?
whether there is a way to prevent this,
how big the files are likely to be when I back up that 1.3 TB database - if the files are again going to be double their final size then I may have to ask for some more disk space!

Here's the backup statement that I'm using:

BACKUP DATABASE @DatabaseName
TO  DISK = @backupMedia1,
    DISK = @backupMedia2,
    DISK = @backupMedia3
WITH COMPRESSION, RETAINDAYS = 0, NOFORMAT, INIT, NOSKIP, NAME = @DatabaseName


texts: Breaking point for SQL server database based on connections from app to SQL server

texts:
We have been asked to confirm on some capacity test for one of our SQL server.

Application team is building their new app servers with better infrastructure to connect to SQL server.

Currently with X app servers connecting to our SQL server with over 500 Gb of RAM and 80 logical processors Numa enabled. Below is the current load

Total sessions:

used and not sleeping - on avg 100;
used and sleeping - 5000;
not used and sleeping around 500.

Above shows 40K batch requests / sec followed by approx 10k transactions per sec during the peak day. The described load consumes between 70-80 percent of CPU through peak time on SQL Server.

How can I calculate the max load in terms of additional app servers
this SQL Server can handle without causing any outages or breaking
like slow SQL Server causing issues?
Does it matter if volume remains same and just more user
connections are made or that's never going to be the case?
How can I calculate the max capacity of user connections this
SQl Server can handle without breaking apart?


texts: mariabackup from remote mariadb10.3 galera | '/var/lib/mysql/' (Errcode: 2 "No such file or directory")

texts:
The following setup applies:

3 node galera cluster in docker
backup container that should run mariabackup to get the data out of the galera cluster
network is fine. backup container can ping galera
mariadb/mariabackup version 10.3
the backup container only has mariabackup installed.

The command that i used

mariabackup --backup --host=<service_name> --port=3306 --user=root --password=<pass> --target-dir=/backup

When trying to backup from galera-cluster from within the backup-container i get the following error

00] 2019-09-15 23:38:34 Connecting to MySQL server host: mdb_mariadb, user: root, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
[00] 2019-09-15 23:38:34 Using server version 10.3.18-MariaDB-1:10.3.18+maria~bionic-log
[00] 2019-09-15 23:38:34 Warning: option 'datadir' points to nonexistent directory '/var/lib/mysql/'
[00] 2019-09-15 23:38:34 Warning: MySQL variable 'datadir' points to nonexistent directory '/var/lib/mysql/'
[00] 2019-09-15 23:38:34 Warning: option 'datadir' has different values:
  '/var/lib/mysql/' in defaults file
  '/var/lib/mysql/' in SHOW VARIABLES
mariabackup based on MariaDB server 10.3.18-MariaDB debian-linux-gnu (x86_64)
[00] 2019-09-15 23:38:34 uses posix_fadvise().
mariabackup: Can't change dir to '/var/lib/mysql/' (Errcode: 2 "No such file or directory")
[00] 2019-09-15 23:38:34 my_setwd() failed , /var/lib/mysql/

What i tried.

researching various gists for clues
researching the mariadb scripts provided by the server install
trying different option combinations, but always the same error.

Question:

Has anyone made remote backups and can tell me how you succeeded?
Is the approach wrong and is there a better way to make hot backups once per day and incremental during the day?


texts: reject queries based on specific client_app_name and nt_username

texts: With a surge of applications that can be used to pull information, my sql server is constantly getting tapped, and there are a couple of users that keeps running refresh. Is there a way to reject query based on specific client_app_name and nt_username? Alternatively, is there a way to add the combination of the user and the app to security to decline access to SQL? i.e. Approve the user access if client_appname is excel but decline if the appname is 'Mashup Engine'.


texts:
I have a string of numbers that I need to trim a portion from using TSQL.

The string of numbers will always start with 101 then it will have a set of 0s and a set of random numbers. Example:

1010000123456

I need to trim the 101 and the set of zeros.  This is probably simple but I'm having all kinds of issues because I don't have a specific character to reference to using a CHARINDEX and the possible combination of a 001 when the random numbers start that I need to keep is giving me issues using a PATINDEX with a SUBSTRING.


texts: I killed a session and it is still running?

texts:
I have Oracle 9.2

I view my running session with the query:

SELECT S92700.SYSUSR_VW."NAME", SID,SERIAL#, AUDSID
FROM v$session, S92700.SYSUSR_VW, S92700.SECUSR
WHERE S92700.SYSUSR_VW.SESSIONID = AUDSID AND S92700.SECUSR."NAME"=S92700.SYSUSR_VW."NAME"

Result:

 NAME      SID      SERIAL#    AUDSID
 prot6      9        6503       78239

And i run the follow query to kill the session

alter system kill session '9,6503' immediate

SQL Command execution

  Executed successfully in 0 s, 0 rows affected. Line 1, column 1

  Execution finished after 0 s, 0 error(s) occurred.

The session still running

The same query in other schema is working


texts: script for restore of postgres database localy from dump given on stdin - ambiguous redirect

texts:
Runing this command:

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -U foo some_db"

runs perfectly fine. It returns on stdandard output the dump of db.

Then I would like to restore new_db based on dump redirected to stdin

 psql -U foo -d new_db < `sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -U foo some_db"`

I got error: ambiguous redirect


texts: AWS RDS MySQL database created but cannot create indexes or change collation

texts:
AWS RDS MySQL 5.6.x issue:

Created a MySQL database on the AWS RDS platform.  Created a dozen tables, imported data to all tables and can SELECT from all tables.

Granted all privileges to user on database schema.

grant all privileges on mysqldb.* to 'username'@'%';

However, cannot create indexes or change collation.  The following commands just hang and never return:

alter schema mysqldb default character set utf8 default collate utf8_general_ci;
create index ix_mytable on mytable(mycol);

And there are no error messages reported in the error log on the AWS RDS dashboard or from within MySQL Workbench.

I have a local MySQL database (from which I exported the table data and moved the data to AWS RDS) on which the above-mentioned SQL works without issues.

Any ideas?


texts: Performance benefits using gRPC to communicate with Postgresql from Web?

texts:
Standard REST server is rather simple to understand and get up and running. But a gRPC server that do the same thing seems to be more complicated.

Are there many that uses the concept web > gRPC > Postgresql?
Will there be any performance difference in the real world?


texts: MySql trigger having null variables during bulk inserts

texts:
I have an SQL trigger. (mysql).

when am doing bulk inserts the summed values become null,

PS. single inserts work.bulk inserts don't.

DROP TRIGGER IF EXISTS  `calculate_balances`;

CREATE TABLE  IF NOT EXISTS temp(account_no VARCHAR(60),amount DECIMAL  (10,2));

CREATE TRIGGER `calculate_balances` AFTER INSERT ON `transactions` FOR EACH ROW
  BEGIN

SET @credit = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_to);
SET @debit  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_to);
SET @credit_balance = @credit - @debit;

SET @credit2 = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_from);
SET @debit2  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_from);
SET @debit_balance = @credit2 - @debit2;

#     UPDATE `accounts` SET account_balance = @debit_balance WHERE account_no =  New.account_from;
#     UPDATE `accounts` SET account_balance = @credit_balance WHERE account_no =  New.account_to;

INSERT INTO temp VALUES(New.account_from,@debit_balance);
INSERT INTO temp VALUES(New.account_to,@credit_balance);

END

So i need to figure out why that happens and fix it.


texts: What are practical hurdles in making protein interaction database?

texts:
Following are some protein interaction databases:

http://string-db.org/

http://dip.doe-mbi.ucla.edu/dip/Main.cgi

As a newbie what are the practical aspects of database that I must understand before attempting any such project on a small scale with a data-set of about 20,000 proteins? What difficulties I must expect during design and implementation of such a project as an administrator?


texts: How Can I Move Column Definitions From One Table to Another (psql PostgreSQL)?

texts:
I have added many new columns to a table in my database in my local environment with ADD COLUMN statements, over 30.  My application is split into multiple environments (UAT, STAG, PROD) and we are NOT using a DB toolkit like RedShift or SQAlchemy or DjangoORM.  I need to get those 30 columns (no data, just the blank column definitions) added to the same table in the databases on the UAT environment, the staging environment, and the production environment.

Is there an easy way to do this?  I could of course write 30 ALTER TABLE xxx ADD COLUMN xxx statements and run those statements in each environment, but isn't there a less tedious way?  Perhaps I can dump the table in my local environment as ADD COLUMN statements and then just copy and run the statements corresponding to the new columns I added in each of the environments?


texts: How to connect to sql server with dynamic IP Address

texts:
I have installed sql server express edition 2014 on my machine. And I am connecting to it with CRM using my local machine's IP address.

The problem here is my IP address allocated to my machine is dynamic and changes every day when I log in, which makes it impossible for me to connect to CRM (as the IP address changes obviously).

What to do in that case? Any help will be appreciated.


texts: SSMS 2014 installation - "Invalid license data" when opening ASP.NET files

texts:
I have just installed SSMS 2014 Express on a brand new Windows Server 2012 (x64) so I connect to the SQL Server 2014 instance running on a different Windows Server 2012.  This is working without issue and I can connect to the instance no problem.

The installation also appears to have included Visual Studio 2010 Shell (Isolated) which has made file associations with all my ASP.NET files (.aspx, .ascx, web.config, etc).

Now when I try and open any ASP.NET files via Windows Explorer, I get the infamous error message from "Visual Studio 2010 Shell" ...

  Invalid license data. Reinstall is required.

I've seen hundreds of messages on the internet about this infamous message, but I cannot find a single straight answer about how to fix it.

The most prominent one is about how this can happen on when you upgrade from a "higher beta to a lower RC"... such as this MSDN Blog.  But this is not the case for me, as this is a vanilla installation.

I have also tried installing the SQL Server Data Tools as per this post on Experts-Exchange, but that did help either, and just cause me more confusion.

I have uninstalled/reinstalled SSMS 2014 3 times now (which can't be a good thing in itself).

Can anybody please point me in the right direction?

If this is not strictly a DBA question, please let me know where I should have posted it


texts: Why would I not specify ROWLOCK to get rid of page locks?

texts:
I have a query that goes something like this:

SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC;

This answer says that it's hard to say for sure but it will probably obtain a page level lock. I'm now profiling my database (actually just staring into sys.dm_exec_requests periodically) and it looks like when this query is executed from multiple clients the multiple instances of the query "get into a line" - like the earliest one locked something and all the others are waiting till that something is released.

I'd like to try add ROWLOCK to enforce obtaining a row level lock but it's kind of risky in the production database and I cannot fully reproduce the same load in my test database (at least until I understand all the subtleties).

Why would I not use ROWLOCK hint here? What could likely break?


texts: MySQL: message "Incorrect key file for table" "try to repair it"

texts:
I'm using MySQL 5.7.10
I have a table like this, with 100M rows and a size of 16GB .
CREATE TABLE `my_table` (
    `id` DOUBLE NOT NULL AUTO_INCREMENT,
    `entity_id` DOUBLE NOT NULL,
    `concept_id` VARCHAR(50) NOT NULL COLLATE 'utf8_spanish_ci',
    `value` DOUBLE(15,6) NOT NULL,
    `increment` DOUBLE(10,6) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `IDX_concept` (`concept_id`),
    INDEX `IDX_entity` (`entity_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=118166425

Once a month, I execute:
ALTER TABLE my_table ENGINE=InnoDB;

My intention is to defrag the table, so the data are put together and the size keeps as low as possible.
This time, it failed and the failure message is: "Incorrect key file for table 'my_table'; try to repair it".
I have made the following steps:

Create a table like this: my_table2.
Use mysqldump to dump my_table data in a file.
Replace the create and the inserts to be done in "my_table2".
Execute the file. my_table2 is created and each row in my_table exists in my_table2.
Execute ALTER TABLE my_table2 ENGINE=InnoDB;

And it failed too, with the same message "Incorrect key file for table 'my_table2'; try to repair it".
How could I fix the error? Thank you.
EDIT 1: I have executed CHECK TABLE for both tables, and the result is status  OK, for both of them.


texts: Management tool for IBM Db2 cloud database

texts:
I will have to administer a couple of small IBM Db2 cloud instances.
The web based administration seems somewhat limited.
Would there be a lightweight alternative to IBM Data Studio which is a 1.5GB download from 2014? If not lightweight, at least comparable to SSMS?


texts:
I'll explain my situation here, I hope I'm posting this on the correct site, otherwise I'm sorry and I'll move this to somewhere else. I've built a Java program in order to control sales, inventory, prices, etc of a small business. The program communicates with a SQL database inside the WAMP folder.

It works fine. 100% fine. But here is where the problem begins. It would be better if I could access the database from more than one computer. I should be able to host it online, but one of the computers wouldn't always be online and the database has to be updated when the offline computer accesses it. For example, if I insert a new row on a table using one of the computers while accessing the database, when I'm not online I need to have that row there.

Could I use a CD, a flash drive, or should I download it every time I need to access it offline? Maybe there is a simple solution and as I'm a beginner in all this I hope you can help me out.


texts: Is possible to restore BD SQL Server without key encryption from 2008 R2?

texts:
I have a database that has an encryption. The encryption key was lost.
This database is on a SQL Server 2008 R2 server, and my mandate is to migrate this database on a more recent server.
Is there a means to perform the restoration if I do not know the encryption key?


texts: mysql says "too many open files" when importing large *.sql file

texts:
I have a large database dump I want to import with MariaDB / MySQL.

mysql -u DBUSER DBNAME < DUMP.sql

The process goes on for a while, and then I get this:

ERROR 23 (HY000) at line 11304: Out of resources when opening file './DBNAME/' (Errcode: 24 "Too many open files")

When this happened to me before, I simply split the file in half and imported both halves separately. But this seems like a poor workaround.

System: Linux Mint 17.3 Rosa


texts: Database design suggestions for a data scraping/warehouse application?

texts:
I'm looking into the database design for a data warehouse kind of project which involves a large number of inserts daily.  The data archives will be further used to generate reports.  I will have a list of users (for example a user set of 2 million), for which I need to monitor daily social networking activities associated with them.

For example, let there be a set of 100 users say U1, U2, ..., U100.

I need to insert their daily status count into my database.

Consider the total status count obtained for a user U1 for period June 30 - July 6, is as follows

June 30 - 99
July 1 - 100
July 2 - 102
July 3 - 102
July 4 - 105
July 5 - 105
July 6 - 107

The database should keep daily status count of each user, like for user U1:

July 1- 1 (100-99)
July 2- 2 (102-100)
July 3- 0 (102-102)
July 4- 3 (105-102)
July 5- 0 (105-105)
July 6- 2 (107-105)

Similarly the database should hold daily details of the full set of users.

And on a later phase I envision taking aggregate reports out of these data, like total points scored on each day, week, month, etc; and to compare it with older data.

I need to start things from scratch.  I am experienced with PHP as a server side script and MySQL. I am confused on the database side.  Since I need to process about a million insertions daily, what are all the things that should be taken care of?

I am confused on how to design a MySQL database in this regard.  Which storage engine to use and which design patterns should be followed, keeping in mind the data could later used effectively with aggregate functions?

Currently I envision the DB design with one table storing all the user id's with a foreign key and separate status count table for each day.

Does MySQL fit my requirement?  2 million or more DB operations are done every day.  How are the server and other things to be considered in this case?

EDIT:

Queries Involved:

INSERTION QUERIES

Insertion queries should be capable of inserting 1-2 million inserts every day.  (We don't have update here.)

RETREIVAL QUERIES

1.Sum of statuses for the whole set of users.

2.Sum of statuses for set of users under a geographic location.

3.Comparing status counts with days/weeks/months.

-->I believe some kind of indexes are needed in this case, but I read indexes could slow down insertion.

-->Also I have heard MyISAM would be a better choice than InnoDB considering speed aspects.

Please advise?


texts: Using IF clause or while clause in OUTER APPLY

texts:
I want to select dates between @startdate and @enddate but I get an error when a month doesn't have 31 days.

DECLARE @StartDate date, @EndDate date
set @StartDate = '2019-04-01'
set @EndDate = '2019-04-30'

(select T1.dday
from [@SALESORDERFORECASTH] T0
    INNER JOIN (SELECT t.*
FROM [@SALESORDERFORECASTD]
OUTER APPLY
(
    VALUES
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-01' as varchar)),120),U_Delivery_1),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-02' as varchar)),120),U_Delivery_2),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-03' as varchar)),120),U_Delivery_3),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-04' as varchar)),120),U_Delivery_4),
        .
        .
        .
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-30' as varchar)),120),U_Delivery_30),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-31' as varchar)),120),U_Delivery_31)
    ) t (DocEntry,dday,U_Delivery)) T1 ON T0.DocEntry = T1.DocEntry
    AND T1.dday <= @EndDate
group by T1.dday)

In this code I want to select T1.dday between 2019-04-01 to 2019-04-30 but in VALUE have 31 days. Leads to this error:

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Can I use If or while or when clause to check date in this VALUES?


texts: How exactly does one configure master-slave replication for PostgreSQL 9.6?

texts:
I have configured master node the way you would configure it on 9.4, but I am getting this error on the slave node:

WARNING:  pg_stop_backup still waiting for all required WAL segments to be archived (120 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup can be canceled safely, but the database backup will not be usable without all the WAL segments.

I did go in and enable archiving on the master node which was not something you had to do on 9.4 and I cannot seem to find a good guide on how to do master slave replication for 9.6.

I have not setup streaming replication. This is what I know how to do from working with PostgreSQL 9.4:

[root@li832-123 ~]# cd /var/lib/pgsql/9.6/data/

[root@li832-123 data]# vim postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

Now this part below in 9.4 you had the option of changing it to hot_standby but in 9.6 you do not get that option, the closest would be replica:

wal_level = replica                     # minimal, replica, or logical

This part below is the same as 9.4:

max_wal_senders = 1             # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 100         # in logfile segments, 16MB each; 0 disables

same here:

synchronous_standby_names = 'postgres_clone'    # standby servers that provide sync rep

On the Slave server, I stopped Postgres:

systemctl stop postgresql-9.6

Then:

su - postgres

listed the databases on the slave to verify that there was nothing there from the master server.

cd /var/lib/pgsql/9.6/data

then:

rm -rf *

to remove everything in the data directory on the slave node so the primary configuration can be pulled forward. Then I went back in as the postgres user and performed the following command:

pg_basebackup -D /var/lib/pgsql/9.6/data -h <ip-of-master-node> -U replica

so I almost got it, until on the slave server I tried to

systemctl start posgresql

and I got this error:

The result is failed.
Jan 04 03:39:06 li1033-198.members.linode.com systemd[1]: Unit postgresql.service entered failed state.
Jan 04 03:39:06 li1033-198.members.linode.com systemd[1]: postgresql.service failed.
Jan 04 03:39:06 li1033-198.members.linode.com pg_ctl[5144]: pg_ctl: could not start server
Jan 04 03:39:06 li1033-198.members.linode.com pg_ctl[5144]: Examine the log output.
Jan 04 03:39:06 li1033-198.members.linode.com polkitd[3207]: Unregistered Authentication Agent for unix-process:5134:368663 (system bus name :1.43, object pat
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: Invalid user admin from 49.231.153.151
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: input_userauth_request: invalid user admin [preauth]
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): check pass; user unknown
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=49.231.153.1
Jan 04 03:39:13 li1033-198.members.linode.com sshd[5151]: Failed password for invalid user admin from 49.231.153.151 port 41322 ssh2
Jan 04 03:39:13 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): check pass; user unknown
Jan 04 03:39:15 li1033-198.members.linode.com sshd[5151]: Failed password for invalid user admin from 49.231.153.151 port 41322 ssh2
Jan 04 03:39:16 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): check pass; user unknown


texts: Both Primary and Foreign Key Needed on Table?

texts:
Someone recently asked this question:

  There are two tables: Deal and DealCategories. One deal can have many
  deal categories.

  So the proper way should be to make a table called DealCategories with
  the following structure:

 DealCategoryId (PK)
 DealId (FK)
 DealCategoryId (FK)

  cont ...

Can someone explain why DealCategories table has

DealCategoryId (FK)

on it?


texts: MySQL server refuses remote connection when under high load

texts: When my MySQL server is under high load from the local machine it doesn't accept any remote connections. When load is normal it connects without any issues, but under high load neither a MySQL nor telnet connection works, while local connections are fine. How can I fix this?


texts: How can I achieve clustering of rows without the exclusive lock and logging overhead of the `cluster` command?

texts:
The CLUSTER command on a big table can take a long time, and blocks both reads and writes to the table while it runs.

I don't need the data in my table to be strictly sorted in index order, I just want rows that are commonly queried together to be more likely to be in the same database blocks than scattered evenly through the table (which is the distribution they naturally have due to the nature of the way date is inserted to the table).

It can make a big difference. In the example below, the only difference is that the insert has an additional order by mod(g,10) so that the test data is pre-clustered by host_id. Far fewer blocks need to be read when getting all the data for one particular host_id.

Is there some way of achieving this kind of clustering without the exclusive lock and logging overhead of the cluster command?

create schema stack;
set search_path=stack;
--
create table foo(host_id integer, bar text default repeat('a',400));
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g;
create index nu_foo on foo(host_id);
explain analyze select count(bar) from foo where host_id=1;
/*
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=30188.66..30188.67 rows=1 width=404) (actual time=1129.858..1129.859 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=919.27..30066.46 rows=48883 width=404) (actual time=253.149..1110.013 rows=50000 loops=1)
         Recheck Cond: (host_id = 1)
         Rows Removed by Index Recheck: 320257
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..907.04 rows=48883 width=0) (actual time=251.863..251.863 rows=50000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 1129.893 ms
*/
--
drop table foo;
--
create table foo(host_id integer, bar text default repeat('a',400));
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g order by mod(g,10);
create index nu_foo on foo(host_id);
explain analyze select count(bar) from foo where host_id=1;
/*
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7550.20..7550.21 rows=1 width=32) (actual time=24.397..24.397 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=47.80..7543.95 rows=2500 width=32) (actual time=3.988..16.189 rows=50000 loops=1)
         Recheck Cond: (host_id = 1)
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..47.17 rows=2500 width=0) (actual time=3.649..3.649 rows=50000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 24.437 ms
*/
--
drop schema stack cascade;


texts: Why does implicit conversion from sql_variant (basetype decimal) not work well with float

texts:
Why is ResultFloat = 0 in the query below?

Am I missing a point within the cast/convert, sql_variant docs?

declare
    @v sql_variant = convert(decimal(28,8), 20.0);

select sql_variant_property(@v, 'BaseType') as BaseType,         -- 'decimal',
       iif(convert(int, 10.0)     < @v, 1, 0) as ResultInt,      -- 1
       iif(convert(decimal, 10.0) < @v, 1, 0) as  ResultDecimal, -- 1
       iif(convert(float, 10.0)   < @v, 1, 0) as  ResultFloat,   -- 0 !
       iif(convert(float, 10.0)   < convert(float, @v), 1, 0) as  ResultFloatFloat,  -- 1
       iif(convert(float, 10.0)   < convert(decimal(28,8), @v), 1, 0) as  ResultFloatDecimal;   -- 1

Sql Server 2012


texts: SQL Server 2012 - password for services during silent installation

texts:
I am a database developer and I have a task to prepare silent installation of SQL Server 2012 using configuration file.
I have a problem with passwords for server services like: RSSVCACCOUNT="NT Service\ReportServer"

During normal installation with user interface there is no need to specify passwords for services. I assume that setup generates random passwords or they are handled by Windows virtual accounts.

However when I use configuration file then setup expect to provide a password either in the file or in the command prompt.

My question is why do I have to provide a password with silent installation while it is no needed during normal installation.
Is there a way to silently install SQL Server without specifying passwords for services?


texts: Group by Multiple columns and then count different value of same column

texts:
I want to achieve the result which tells me the number of males and females of each disability types in each district.
each district can have multiple disabilities.
So far i have reached  the following query :
  SELECT
  DistrictId,
  fb.DisabilityTypeId,
  SUM(
    CASE WHEN GenderId = 1 THEN 1 ELSE 0 END
  ) AS Male,
  SUM(
    CASE WHEN GenderId = 2 THEN 1 ELSE 0 END
  ) AS Female
FROM
  Districts d
  LEFT OUTER JOIN FormAddresses a ON d.Id = a.DistrictId
  INNER JOIN PeopleForms pf ON a.PeopleFormId = pf.Id
  INNER JOIN FormBeneficiaries fb ON pf.Id = fb.PeopleFormId
  INNER JOIN FormPersonalInfos fp ON pf.Id = fp.PeopleFormId
where
  a.IsDeleted = 0
  AND pf.FormTypeId = 2
  AND d.CityId = 3
GROUP BY
  DistrictId,
  fp.GenderId,
  fb.DisabilityTypeId

which gives the following result  :
DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     0
    3       |       2               |   0     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     0
    5       |       20              |   0     |     1

But i want to achieve the following result :
DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     1

i somehow managed to get the expected result, but that with some complex sub-queries in the select clause  of each gender which i didnt like and was not sure about the performance.
how can i write an efficient query for the desired result that i want?
Thanks.


texts: Counting number of occurrences, how to "group by"

texts:
I have the following query, which works as intended. Database is Sybase SQLAnywhere, which follows t-sql syntax for the most part.
I'm trying to add a column to COUNT the number of times each "referred_link" occurs on the table called "cases".
Num_refs is the total number of times a specific "cases.referred_link" exists on the cases table, for each referrer.
Expected output:
casenum:    casetype:    date_opened:    client:    referrer:    num_refs:
30293       MVA           1/1/2021       Joe Smith   Maggie G      3
10293       SF            1/4/2020       Ben R       Simon A       4

Here's the query:
select casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'repeat client' as reason
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff'
and party.our_client='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN
  (select
    party_id
    from
      (select
        count(casenum) as casecount,
        party_id
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff'
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
UNION ALL
select casenum,
matcode,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'personal referrer' as reason
from cases
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id
where names.person='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and party.our_client='Y'
and party.role='Plaintiff'
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null)
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
order by date_opened asc

And here's how I'm trying to do it, entered after line 5:
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  and date_opened&gt;='##STARTDATE##'
  and date_opened&lt;='##ENDDATE##'
) as num_refs,

but I'm getting an error "Function or column reference to 'casenum' must also appear in a GROUP BY"
Can anybody help me straighten this out?


texts:
I have a Firebird database with a table foo that have a field called WHEN. Is there a way to perform a query using the field name in SELECT?

This works:

SELECT * FROM foo;

This not:

SELECT WHEN FROM foo;
SQL error code = -104
Token unknown - line 1, column 18
WHEN

Any hints?


texts: Considerations for Adding Indexes to a SQL Server Database

texts:
I'm a .NET developer. So, I know enough about databases to be dangerous, but do not have the expertise of a proper DBA.

We have a slow running stored proc and one of the devs here ran a query optimization tool which suggested the addition of some indexes.

It suggested 9 Non-clustered indexes and 78 statistics.

I know this is a pretty huge area and I know enough to know that I don't know much. So, given that I don't have access to a proper DBA to review the indexes and statistics, would it be a reckless move to just proceed with those suggested changes?

Rewriting the Stored Proc is another option we are not too keen on, as it is returning the correct data right now and we are reticent to mess with that.

But if there is a big gotcha that I am unaware of, I'd really like to find out about it. So, advice would be greatly appreciated.

I was going to post the most expensive query plan of the batch to Pastetheplan.com . Alas, I was unable to get permission from my manager. I can't see how a bit of random SQL will have any security implications etc., but the business is more paranoid than me. I realise this would have helped a lot and am disappointed I cannot post to it. The 1 index I added has helped a lot and the advice here has been much appreciated. Choosing an answer is tough and I'm still working on that.


texts: Delete query end state lasts tens of seconds in innodb cluster

texts:
I have an InnoDB cluster with three nodes running with 4GB ram and 80GB SSD on Linode.
I am trying to delete around 240000 of rows from MySQL InnoDB cluster in one go. However, it stuck at query end state for more than 100 seconds then it timeout at MySQLWorkbench side. The worst thing is sometime after timeout master node MySQL started to use all memory and swap then crashed.

If I delete by small chunk with below 20000 rows it worked but still took 60s. I know I can just delete in a small chunk and that's how people suggest typically. But I think 20000 rows is too small. It may indicate other potential problems as most of the query time goes to query end state.

The query is like this

delete A
from A left join B on fk_Btable_id=Btable_id
left join C on fk_Ctable_id=Ctable_id
where col1 = 'value';

The profile from delete around 20000 rows in one delete query.

sending data: 15.68s.
query end: 45.5s

When more than 30000 rows the delete query timeout at 180s at MySQLWorkbench side. But it still able to complete after some time. On slow query log, it said total 1488 seconds to complete the query. That's ridiculously long. Delete query itself completed within 20sec. Residual time goes to query end.

To test without a cluster, I spinned up another instance on Linode with same data, ran the same delete query and it completed within 20s. So my guessing is because of InnoDB cluster, probably network or some kind of sync mechanism but I can't find any proof for this.

Any idea would be helpful...either guess or the direction I can look into. Thanks a lot.


texts:
I want to receive a mail when a query is running for more than x seconds. I know I can set up a job running on the database but, is there any way to do the same using SQL Alerts? What kind of alert should I use?
   Thanks.


texts: Query to check where database growth was?

texts: I have a database that grew very significantly overnight and I'd like to find out why. I've restored yesterdays backup to another location. Is there a query I can run to query the size of tables or indexes so I can compare the two?


texts: I am just getting started with Accumulo and NoSQL databases and I am looking for some discussion on table design.  I get the key value structure that is seen in the manual.  However, if I am trying to recreate a relational database, I am not sure how relationships work.  Can someone explain to some degree how to setup and "Hello World" database (i.e., manager-employee database).  I want to use key-value implementation.


texts: ORDER BY and comparison of mixed strings of letters and numbers

texts:
We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be sequences of letters then numbers alternating. The number of these segments and the length of each could vary, though.

We'd like the numeric portions of these to be sorted in numeric order. Obviously, if I just handle those string values directly with ORDER BY, then "P12B3" is going to come before "P7B18", since "P1" is earlier than "P7", but I'd like the reverse, as "P7" naturally precedes "P12".

I'd also like to be able to do range comparisons, e.g. @bin < 'P13S6' or some such. I don't have to handle floating point or negative numbers; these will strictly be non-negative integers that we're dealing with. String lengths and number of segments could potentially be arbitrary, with no fixed upper bounds.

In our case, string casing isn't important, though if there's a way to do this in a collation-aware fashion, others might find that useful. The ugliest part of all this is I'd like to be able to do both ordering, and range filtering in the WHERE clause.

If I were doing this in C#, it would be a pretty simple task: do some parsing to separate the alpha from the numeric, implement IComparable, and you're basically done. SQL Server, of course, doesn't appear to offer any similar functionality, at least as far as I'm aware.

Anybody know any good tricks to make this work? Is there some little-publicized ability to create custom CLR types that implement IComparable and have this behave as expected? I'm also not opposed to Stupid XML Tricks (see also: list concatenation), and I've got CLR regex matching/extracting/replacement wrapper functions available on the server as well.

EDIT:
As a slightly more detailed example, I'd want the data to behave something like this.

SELECT bin FROM bins ORDER BY bin

bin
--------------------
M7R16L
P8RF6JJ
P16B5
PR7S19
PR7S19L
S2F3
S12F0

i.e. break the strings into tokens of all letters or all numbers, and sort them either alphabetically or numerically respectively, with the leftmost tokens being the most significant sorting term. Like I mentioned, piece of cake in .NET if you implement IComparable, but I don't know how (or if) you can do that sort of thing in SQL Server. It's certainly not something I've ever come across in 10 or so years of working with it.


texts: Security for Application Developers doing PL/SQL work in Oracle

texts:
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need few restrictions. However, there seems to be a big gaping hole in the architecture for programmers doing development with a front end application and PL/SQL in multiple schema. Here are some of my options with their downsides:

Make each programmer do development in their own schema. The DBA will grant object level privileges to programmers needing them. Any package development must be done by a DBA. The major downside is that programmers will use the database like a bit bucket to the detriment of database performance. I want the programmers to develop in the database, but this method would greatly discourage it.
Give each programmer the username/password for the dozen or so schema they need to do development in. Grant these application schema permission to create procedures, tables, etc. Some of the disadvantages with this approach are that programmers have to maintain multiple logins and are seldom logged in as themselves. Cross schema development is also difficult.
Grant programmers proxy authentication privileges on each schema they need to do development for.  This keeps them logged in as themselves without having to grant them privileges other than the proxy privilege.  Disadvantages include programmers having to maintain separate connections for each schema they proxy for, cross schema development is more cumbersome as connections have to be constantly changes, and packages using public database links with passed authentication won't compile inside proxy connections.
Give each programmer DBA privileges. – The downside here is security. No schema programmer can be kept out of any schema and any programmer can impersonate any other programmer (DBA).

There seems to be a missing option to grant each programmer SELECT/INSERT/CREATE/etc. privileges on the schema they need to do development in. They login as themselves to do their work using one connection. New objects in the schema they have access to are immediately available.

Am I missing something? How do you handle application programmers that do PL/SQL development?


texts: RMAN deletes all conrtolfile autobackups

texts:
We have Oracle 18c and RMAN with:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA';

We have incremental backups also. There was no 'backup current controlfile' in a backup script, but control file was backed up every time we did backup.
Could someone tell why "DELETE OBSOLETE" deletes all copy of autobackup controlfiles (except lastest one) when they still needed for database recovery? Like, WTF? If controlfile is redundant why RMAN does not delete related backup set, for example. Can't get it. And yes - we have tons of so called "backups" with no controlfile.
TSPITR fails with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/29/2020 17:39:13
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore


texts:
I have a laravel application which must insert/update thousands of records per second in a for loop. my problem is that my Database insert/update rate is 100-150 writes per second . I have increased the amount of RAM dedicated to my database but got no luck.

is there any way to increase the write rate for mysql to thousands of records per second ?
please provide me configurations.

My storage engine is InnoDB


texts: Free some space on a full Express Edition instance

texts:
Using Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Express Edition (64-bit)
I have a database that reaches the maximum space (10Gb) every month.
In order to free up some space I do a bacpac, create a new database with the bacpac and shrink it 2 times.
The database goes from 10Gb to 6Gb, and the application using this database gets faster (I think the bacpac refreshes the indexes).
Is there a SQL command to do this? Can I do this in a production database?


texts: Why does adding a column to a MySQL table take incredibly long?

texts:
I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with the following commands

ALTER TABLE outputs add spent BOOL;

that query took 6 days and 9 hours to complete. There was no index added, because it is a column with only two possible values. Does anyone have clues as to why this relatively simple query would take so long? FYI The outputs table has a primary key column and two other columns which are indexed. It has a total of 9 columns. I can edit this post if more information about my mysql configuration is required, but I'd need to know which parameters are important.


texts: How to find index details on Temporary tables in SYBASE

texts:
I am a layman in SYBASE world.

In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures.

Say, one proc A  has an insert into temporary table X. so I have created index IDX after insert statement.

Proc B is invoking Proc A in a loop. In this case, index IDX on temporary table X will try to get created at each call. That’s where I get duplicate index has already create on table X with index name IDX.

To avoid that, I developed code piece(below) to check if the above IDX on temporary table A created or not. If not exists, I create index IDX on temporary table A. This will avoid duplicate index error when invoked proc A in a loop. It's not helping me.

I need to understand how to find if an indexes created on temp table pro-grammatically to restrict duplicate index error.

OPEN CUR_VAR_1
FETCH CUR_VAR_1 INTO @VARIABLE
WHILE (@@sqlstatus=0)
BEGIN
INSERT INTO #TABLE_X
SELECT * FROM TABLE Y

IF NOT EXISTS(SELECT 1 FROM sysindexes where  id=OBJECT_ID('#TABLE_X') and
name ='IDX')
BEGIN
CREATE INDEX IDX on #TABLE_X(COL_1)
END
End
CLOSE CUR_VAR_1

Error message:

  Msg 1913, Level 16, State 1: Server 'ABCDE', Procedure 'PROC_A', Line
  1155: There is already an index on table '#TABLE_X' named 'IDX'

A quick help is really appreciated as I have to complete this no later than 04/16/2018.


texts: Exclusively access a table and prevent other queries, even read queries

texts:
I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application:

Get unprocessed messages (using the IsProcessing flag)
Set their IsProcessing to true (in RAM) and update their IsProcessing status
Do the business
Set their IsProcessing to false (in RAM) and update their IsProcessing status

This works fine when runs sequentially. But when I run multiple instances of my application (concurrency), I see that some messages are processed twice or thrice. Here's what happens:

Instance A gets some unprocessed messages
While instance A is setting the IsProcessing to true in RAM, instance B gets some messages, and chances are that it fetches one or more of the messages which are already fetched by instance A

And this is what I've done in hope of preventing it:

Begin transaction (serializable)
Get unprocessed messages (using the IsProcessing flag)
Set their IsProcessing to true (in RAM) and update their IsProcessing status
Commit transaction
Do the business
Set their IsProcessing to false (in RAM)
Begin transaction (serializable)
Update their IsProcessing status
Commit transaction

I don't know why, but during steps 1 to 4, other instances can still perform read queries. This is not desired. I want to exclusively prevent anything, even read queries from being executed on messages table during step 1 to 4.

How can I do that? What am I missing in my design? The goal is to make sure that while a message is queued for processing, no other instance would process it again.


texts:
I am using log shipping as DR for my organisation.

For the particular time like morning 4 to 5'o clock .wrk files are falling instead of .trn file and that particular file take too much time to copy.

I configured log shipping for 570 databases in 4 instances in DR server.

Please suggest.


texts:
I'm in the process of tuning a query in SQL Server 2012 that is spilling to tempdb due to an incorrect cardinality estimation when using the DATENAME() function on a column to check if the data is weekend or weekday. Due to the the use of the function, the query is non-sargable and mis-estimates the number of rows (est 1700, actual 38000).

The where clause was simply:

WHERE DATENAME(WEEKDAY, vqc.DateRecorded) NOT IN ('Saturday', 'Sunday')

I was hoping to use a Persisted Computed Column and then index it:

ALTER TABLE <table_name> ADD IsWeekend AS DATENAME(WeekDay, DateRecorded) PERSISTED;

However get the error:

  Computed column 'IsWeekend' in table  cannot be persisted
  because the column is non-deterministic.

According to BOL, a large number of Date functions cannot be persisted because they are non-deterministic.

  This is because the results depend on the LANGUAGE and DATEFORMAT
  settings of the server session. For example, the results of the
  expression CONVERT (datetime, '30 listopad 1996', 113) depend on the
  LANGUAGE setting because the string '30 listopad 1996' means different
  months in different languages. Similarly, in the expression
  DATEADD(mm,3,'2000-12-01'), the Database Engine interprets the string
  '2000-12-01' based on the DATEFORMAT setting.

There was an interesting way around this issue for the MONTH() function using a CASE statement that was posted on Stack Overflow, but this doesn't work for DATENAME due to SET DATEFIRST and the LANGUAGE setting.

Aside from joining to a calendar table in my query and then filtering the weekends from there, is there a way of determining whether a date is the weekend so it can be persisted and then indexed?

Or am I trying to overcomplicate the situation?


texts: Installing SQL Server 2017, I get E: Unable to locate package mssql-server

texts:
I'm using Ubuntu 17.10, but after adding the Microsoft repo for 17.10

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -sr)/prod.list)"

and trying to install SQL Server, I get

# apt-get install mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package mssql-server


texts: What should I ask at a MySQL DBA interview?

texts:
I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are:

Slower queries and performance due to Hibernate.
Database management (backups, tuning, patches, security).
Scalability due to increase in data from new data sources and accumulation of older data.
We plan to start data mining and data warehousing in the future. Not sure how but that is the direction.

We usually have programming cases where we ask developers to build something for an interview, but it's a bit hard to do a DBA interview in the same fashion.

Can you give suggestions on how the interview should be conducted?


texts:
WARNING:  database "dbname" must be vacuumed within 5463629 transactions
  HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
  You might also need to commit or roll back old prepared transactions.
  VACUUM

I have run vacuumdb --full -h 127.0.0.1 -U postgres dbname
It spits out that message galore.

I have checked active queries and there is nothing active.

It is a cluster with many DBs on Linux (postgres 9.5.10) but only this one small DB is giving this warning.

Any suggestion would be helpful.


texts: Can I make sure two columns dont have the same value

texts:
If I have a table that looks like this

CREATE TABLE foo (
   id INT NOT NULL AUTO_INCREMENT,
   aa INT NOT NULL,
   bb INT NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (aa, bb),
   CONSTRAINT aa_ref FOREIGN KEY (aa) REFERENCES bar (id),
   CONSTRAINT bb_ref FOREIGN KEY (bb) REFERENCES bar (id)
)

Is there a way to make sure that aa != bb besides using application level logic or forcing a trigger to fail on BEFORE INSERT?


texts: Is there a function which the opposite of NULLIF?

texts:
I am aware of the NULLIF function which compares two values and returns NULL if they match.

Is there a function which will compare two values and return NULL if they don’t match? That is filter out the other values.

I know I can do something like that using CASE:

CASE column WHEN value THEN 1 END

I also know that I can write a function.

Perhaps there is a subtle trick to using NULLIF that I am unaware of.

I have listed a few versions of SQL in the tags, but I think NULLIF is generic. I am looking for a solution which is also generic, so that it can be applied to any standard database.


texts: Why Oracle 19c generating error while installation?

texts: I am trying to install Oracle 19c Enterprise Edition.I am using Windows 10 Home.For any additional required information please tell me in comments.


texts:
In our database we have multiple entries with caron/hatschek.
Now our users want to find entries including caron/hatschek when they search for entries without. I'll show this by a simple example:

In our database we have the entry (contact with name)

Millière

so this name is correct in the country the person lives in.

In our country we do not have any characters with caron/hatschek, therefore our user searches for Milliere. No results come up, as è does obviously not match e.

I have no idea how this could be realized as é,è,ê and many more are available (and this is only an example for letter e...).

(The other way would be much easier, as I could simply string replace all letters with caron/hatschek with the basic one. Obviously, our users do want the correct version of the name in the database, not the crippled one.)


texts:
The excellent pgBadger has an interface to reviewing specific queries - the slowest, by host, by user etc.

Is it possible to see all queries processed, perhaps in a given timeframe.

If not available out of the box, does anyone have pointers on how to modify the application to do this?

http://dalibo.github.io/pgbadger/


texts:
I would like to aggregate results of a column into a single row using MS SQL.

I have create a sql fiddle which showcases the issue:

http://sqlfiddle.com/#!3/384d5/2

Basically I would like all 'Tag' results on the same line, grouped by the different Tags

So in the example results above, I would expect the results to be like so:

Id | Tag      | OptionText
1  | IsMobile | Yes
1  | Mission  | I am buying this, I am using this

The closest I have got is by using a STUFF function but I can't seem to get it correctly as it outputs all the OptionText values on each row:

select Id, Tag, STUFF(CAST((
    SELECT [text()] = ', ' + [OptionText]
    FROM testing
    FOR XML PATH(''), TYPE) AS
VARCHAR(max)), 1, 2, '')  OptionText
  from testing
  group by Id, Tag

Here are the results for that query: http://sqlfiddle.com/#!3/384d5/5


texts: Standalone MongoDB to MongoDB Shard Setup

texts:
I'm learning MongoDB currently so maybe the title of this question is worded poorly, but I'm looking to understand the scaling of Mongo better. I did some research on what DB would be best for my current needs and currently a standalone instance of Mongo fills that, but I'd like to plan for the future.

I'm trying to figure out the process of scaling up MongoDB. In my case the issue I foresee is an extremely large number of documents requiring bigger servers. I began to look up information on ways to solve this and the best way I could see was using Mongo's sharing capability. The only issue though is I don't understand the process of going from standalone to shards. Is there a document that can explain the process or is there a better way to handle such scaling?

Any help is appreciated while I'm learning this.


texts: i am trying to get total amount from this query

texts:
SELECT ( ( `salary_log`.`emp_id`
          ,`salary_log`.`basic`+`salary_log`.`da`+`salary_log`.`allowance`)
        -( (`salary_log`.`deduction`-`provident_fund_info`.`pf_own_value`)
          +`salary_log`.`adjustment`+`salary_log`.`fine`
          +`salary_log`.`tax`+`provident_fund_info`.`pf_own_value`
         )
       )
FROM `salary_log`
       INNER JOIN `provident_fund_info`
          ON `provident_fund_info`.`emp_id`=`salary_log`.`emp_id`
WHERE `salary_log`.`generate_date`='2017-12-01'
  AND `salary_log`.`statement_type`='salary';

  1 queries executed, 0 success, 1 errors, 0 warnings

  Query:

SELECT ( ( `salary_log`.`emp_id`
          ,`salary_log`.`basic`+`salary_log`.`da`+`salary_log`.`allowance`)
        -( (`salary_log`.`deduction`-`pro...

  Error Code: 1241

  Operand should contain 1 column(s)


texts:
I need to encrypt some columns in the postgresql for privacy reasons. I would like to use the pgcrypt and encrypt the columns with an asymmetric key pair and the passphrase.

Now you can display currently executed queries. There you will see the private key and the passphrase. Is there a way to protect the executed / active queries from view?


texts: SQL Server, I can execute sqlcmd with trusted connection but not sqlpackage

texts:
We have some deployment scripts that use a combination of dacpacs through sqlpackage and straight scripts using sqlcmd.

When I execute the sqlpackage dac\sqlpackage.exe /p:BlockOnPossibleDataLoss=False /p:BackupDatabaseBeforeChanges=True /a:Publish /sf:.\somedeploy.dacpac /tcs:"Data Source=MyDB;Initial Catalog=MyCatalog;Trusted_Connection=True;"

I get an error message *** Could not deploy package. Unable to connect to target server.  I have login auditing turned on in sql and the message I am getting is Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 111.222.223.224]

However, I can run our sql scripts through sqlcmd no problem by using the -E argument.
sqlcmd -i Permissions\CreateSqlUser.sql -S MyDB -E

I am running from a commandline that is set to "Run As Administrator" and am currently signed in as the admin.  This is running on a Windows Server 2012 VM.  I am using sql 2014.  We have other installs that haven't had problems but this is our first 2014 install so we are trying to determine if something changed or if we are missing something and if so what.

If I run from profiler I don't see any audit log error messages.  I checked the filters and what events I'm listening for and they all look good.  Inside sql when I run exec sys.sp_readerrorlog 0, 1, 'Login Failed' I do get the messgaes to show up.  (I might be doign something wrong in profiler).  Also they show up in the system event logs.  I'm not seeing anyplace where it tells me anything about the user attempting to login though.

If don't use trusted connection for sqlpackage and instead use a username and password it works fine.  However, part of what our deployment script does is create the users so our preference would be to not do this.


texts: Insert result of a parallellized select query into a table, in Postgresql

texts:
According to https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html,

  "Even when it is in general possible for parallel query plans to be
  generated, the planner will not generate them for a given query if any
  of the following are true:
      The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within
  a CTE, no parallel plans for that query will be generated. This is a
  limitation of the current implementation which could be lifted in a
  future release."

Indeed, when I try to insert result of a parallel SELECT query into a table (either by SELECT.. INTO either by WITH..SELECT..INTO, the query is not executed as parallel query.

My question is: Is there any way to trick the Postgresql so that a SELECT query is executed as parallel query and then its result inserted into a table?