texts: How can I optimize my user/password table?

texts:
For a uni project I'm looking at leaked usernames and passwords. I have roughly 275 million rows in my table.

My table:

CREATE TABLE table (
    username varchar(300),
    password varchar(300)
);

PostgreSQL 10 runs on a ubuntu 18.04 VM with 5 cores and 50GB RAM. The DB will only be accessed from 1 or two people.

Relevant parts of config file:

max_connections = 100
shared_buffers = 1024MB
work_mem = 512MB
dynamic_shared_memory_type = posix

When I try to run this query it takes more than 1h after which I cancel the query.

SELECT password, COUNT(*) AS amount FROM table GROUP BY password HAVING COUNT(*)>50000 ORDER BY amount DESC;

I'm now creating an index on username and when it's done I'll do the same for the password column.

Index creation:

CREATE INDEX pw_index ON table (password);
CREATE INDEX user_index ON table (username);

Explain result: https://explain.depesz.com/s/5KVW

What other things should I look for to optimize this table / database?

Thanks for any help or suggestions!


texts:
I am attempting to use MAX() in my WHERE clause but I am getting an error of

  Msg 4145, Level 15, State 1, Line 10
  An expression of non-boolean type specified in a context where a condition is expected, near ')'.

This is the output I expect from working syntax

Blue - 3
green - 0
orange - 1
pink - 1
black - 0
white - 2

Here is sample table structure and the query I tried - how can I successfully use MAX() in the WHERE clause?

Declare @Hold Table
(
  id int NOT NULL IDENTITY (1,1),
  name varchar(500),
  logoncount int
)
Insert Into @HOld (name, logoncount) Values
('blue', 1),('blue', 2), ('blue', 3)
,('green', NULL), ('orange', 1), ('pink', 1)
,('black', NULL), ('white', 1), ('white', 2)

Select
*
FROM @Hold
WHERE logoncount = (select max(logoncount) from @Hold)


texts: Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

texts:
I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK column because the data is frequently selected by other_id.

My tests show that the gin index type is about 10 times smaller and about 3 times as performant as the default btree index (the performance was tested using SELECT queries).

The question is are there any real-world drawbacks of using the gin index instead of the btree index? It looks like this index type isn't used much for a very common case like mine, i.e. an integer foreign key. But my tests show vast performance gains. Why then gin isn't recommended for such scenarios?

I had to execute CREATE EXTENSION btree_gin to be able to use the gin index for the integer column.

I know about the UPDATE being possibly slow due to FASTUPDATE being enabled by default: Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

I care only about the equality = operator being able to use the index (also, IN (...) with a possibly large number of values but I assume this is also equality).


texts:
I updated my mongo instance to 3.0.0.
Now i am seeing this error continuously in the replica set log

2015-03-12T15:24:23.141+0530 W NETWORK  [ReplExecNetThread-74] Failed to connect to secondary:27017, reason: errno:113 No route to host

[ReplicationExecutor] Error in heartbeat request to secondary:27017; Location18915 Failed attempt to connect to secondary:27017; couldn't connect to server secondary:27017

Got this on secondary server

[ReplicationExecutor] not electing self, we are not freshest

2015-03-12T15:15:30.030+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to primary:27017; ExceededTimeLimit Went to run command, but it was too late. Expiration was set to 2015-03-12T09:45:16.806Z
2015-03-12T15:15:30.152+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to arbiter:27017; Location18915 Failed attempt to connect to arbiter:27017; couldn't connect to server arbiter:27017 (arbiter), connection attempt failed
2015-03-12T15:16:31.792+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to arbiter:27017; ExceededTimeLimit Went to run command, but it was too late. Expiration was set to 2015-03-12T09:46:12.594Z
2015-03-12T15:27:48.228+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to arbiter:27017; ExceededTimeLimit Went to run command, but it was too late. Expiration was set to 2015-03-12T09:56:30.069Z
2015-03-12T15:27:48.228+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to primary:27017; ExceededTimeLimit Went to run command, but it was too late. Expiration was set to 2015-03-12T09:56:41.972Z

My primary and secondary servers are continuously switching in their states.

I saw this message with rs.printSlaveReplicationInfo() command

firstset:SECONDARY> rs.printSlaveReplicationInfo(); source: secondary:27017 syncedTo: Thu Mar 12 2015 17:18:59 GMT+0530 (IST) -59 secs (-0.02 hrs) behind the primary

I checked the network connections but there was no issue.
One more observation was when ever it happened i saw SAR details on servers and load factor and CPU uses wre quite high on the secondary server that time.


texts: Grant Permission to Execute Set Transaction Isolation Level

texts:
I'm a part-time accidental SQL Server 2005 DBA whose full-time job is developing business intelligence solutions, but where the BI intersects with the database, it falls in my 'stack'.

We have a number of scheduled reports that failed when another developer needed to add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in several stored procedures running against an enterprise application database.

It turns out that the login used by the reporting tool didn't have the correct permissions.  It could execute the stored procedures, but not with the SET TRANSACTION ISOLATION LEVEL added.

I was able to get around it temporarily by adding the db_owner role to the login properties for the database.  It already had db_datareader, db_datawriter, and public. But it seems like I should be able to grant a permission that would alleviate the problem without giving the login the db_owner role.

I've read through all the information on GRANT in the SQL Server books online, but can't find what permission to grant.

Can anyone help me out?  If you know of a resource, I'd love to know about it.

Thanks!


texts:
Need to access xampp mysql database remotely for replication but when I do bind 0.0.0.0 it just slows the database down completely, can anyone shed some light?

thanks :)


texts: Creating a rule increases load disproportionally

texts:
I hope the group can forgive the vagueness of the question:

In Postgres-9.3 we created a rule to be executed after an update on a table's values. The rule inserts values to another table.  We tested the rule and then deployed it in production. Within seconds the load on the database server skyrocketed to the point that we could not even drop the rule and had to restart the database in order to do so.

The weird thing is that we have deployed the very same rule on the same table for inserts only and it works perfectly.  It is that only when enabled on updates it brings the system down.

Where would one start to even begin debugging this?


texts:
I have (2) postgresql databases. I basically used the same pg_dump export file to create the (2) db's on different machines. The DDL is the same, however, I am getting the no relations message on one of them. The search_path's are set correctly.  I believe I'm missing a grant for (2) of my users to see this schema. The settings are correct on Host2. I just don't which grant to use.

Message:

mra_prd_99999=> \dt
No relations found.

HOST1:

mra_prd_99999=> \z
                                                   Access privileges
      Schema       |           Name            | Type  |          Access privileges          | Column access privileges
-------------------+---------------------------+-------+-------------------------------------+--------------------------
 mra_dev_schm_99999 | abc_bounce_category       | table | mra_prd_99999=arwdDxt/mra_prd_99999 |

HOST2:

mra_prd_99999=> \z
                                                        Access privileges
      Schema       |           Name            | Type  |              Access privileges               | Column access privileges
-------------------+---------------------------+-------+----------------------------------------------+--------------------------
 mra_dev_schm_99999 | abc_bounce_category       | table | mra_prd_99999=arwdDxt/mra_prd_99999         +|
                   |                           |       | mra_prd_99999_user1=arwdDxt/mra_prd_99999+|
                   |                           |       | mra_prd_99999_user2=r/mra_prd_99999        |

My rolenames and settings are identical on both (1,2):

 mra_prd_99999          | Create DB                                      | {}
 mra_prd_99999_user1    |                                                | {}
 mra_prd_99999_user2    |                                                | {}

All the users have the search_path set to the same schema:

mra_prd_99999=> show search_path;
    search_path
-------------------
 mra_dev_schm_99999
(1 row)

mra_prd_99999=> \dn
          List of schemas
       Name        |     Owner
-------------------+---------------
 public            | mra
 mra_dev_schm_99999 | mra_prd_99999
(2 rows)

What grants do I need to run in order for HOST1's users to have the same access privileges than HOST2?


texts: MySQL replication: "Houston, We've Got a Problem"

texts:
I ran into a problem with our replication server. Essentially, we have 2 databases (database1 and database2). Master server has both. Slave has only database1. There is a

Replicate_Do_DB: database1

set in CHANGE MASTER TO configuration.

Now what happened is - we are using code igniter, and one of the programers created database2 and started inserting info into it. Code igniter sets a default database to database1. Now the result is for every query he produced - I get an error on SHOW SLAVE STATUS\G:

Error 'Table 'database2.tbl40' doesn't exist' on query. Default database: 'database1'. Query: 'INSERT INTO `database2`.`tbl40` (`date`, `day`) VALUES ('2011-04-26', '2011-04-26')'

So essentially, I he fixed the problem afterwards, but the replication doesn't work as there is around 1000 queries that will produce that error for replication server.

My question is - is there some way to clear queries like that from the binlog?
Or I need to write a script that will do a

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

for every query that produces and error ?


texts: How to set the Tablix outer border width

texts:
In SSRS 2008 R2, a Tablix has got a "Border Width" property. I assumed that by assigning a value to this property, I can define the outer border's width. But it does nothing.

I'd like to have the outer border of the Tablix thinner than the internal grids. There are many Tablixes on our reports with many cells and it is painful to select each cell on the top, bottom, left and right borders and set the outer border weight width to 3pt.


texts:
I am doing a pretty complex query in Postgres 9.3.4:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  100
   ) p
ORDER  BY p.external_created_at DESC
LIMIT  100;

See here for more details on my Postgres db, and settings, etc.

Which is great, unless I try to do some pagination.
To see what I mean. When I do a count, with no limit set:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   ) p
ORDER  BY p.external_created_at DESC;

I get (107 rows), which is the correct number of results.

If I want to paginate, I'd use OFFSET on the subquery. If the first page has 100 posts, the second page should have 7 posts.

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  100
   OFFSET 100
   ) p
ORDER  BY p.external_created_at DESC
LIMIT  100;

but instead it returns (0 rows).

If I do the pagination on the outer query:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   ) p
ORDER  BY p.external_created_at DESC
LIMIT  100
OFFSET 100;

I get (7 rows) which is correct, but then the query is insanely slow (see this question)

Is there a way to paginate this query properly, not slow it down, and get the correct results back?


texts: Understanding execution plans using TVPs

texts:
I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the user chooses and I have an indexed view that accounts for 84% of the cost of the query.

The execution plan is pretty large and can't be uploaded here due to the size so I have uploaded it to here

I've spent quite a lot of time trying to optimise these queries (there are 14 of them but the core of each is much the same) and would appreciate anyone's suggestions or hints in reading them. I have also implemented the query suggested by the actual execution plan and the query was 5 times slower?


texts: Recover MySQL database on windows with ibdata1, ib_logfile and mysql-bin.00000 files

texts:
I have a problem with MySQL database in a WAMP environment. I dropped my database via phpmyadmin and now I am trying to recover it. I created a new database with the same name and I imported some data to correct the database, but it was not enough.

Now I need your help to restore data from the mysql-bin or ibdata1 directories. So please help me:

How can I recover data using the ibdata1, ib_logfile1, ib_logfile0 and mysql-bin.000001 files on Windows 7?


texts: Regularly updating table with huge amount of rows

texts:
I have a table containing (in addition to some metadata) a score and a boolean flag:

CREATE TABLE scores
(
  score_id        SERIAL NOT NULL
    CONSTRAINT score_pkey
    PRIMARY KEY,
  user_id         INTEGER NOT NULL,
  score           DOUBLE PRECISION NOT NULL,
  flag            BOOLEAN NOT NULL
);

There are multiple indexes for querying the data:

CREATE UNIQUE INDEX score_pkey ON scores (score_id);
CREATE INDEX ix_scores_user_id ON scores (user_id);
CREATE INDEX ix_scores_score_desc ON scores (score desc);
CREATE INDEX ix_scores_flag ON scores (score_id) WHERE (flag = true);

The table currently contains around 120 million rows and for most of them, the score value is updated once a day. The flag column may be toggled (only from default value false to true but then never back to false) at any point of the day and independently from the score updates.

Scores are generally only updated for some users (around half to one third currently) and where the flag is set to false.

The scores are calculated on a worker machine and updated in batches (ca 3000 at a time) in a single update-query. There is no way of calculating the data on the database server. The query looks like this:

UPDATE scores as s
SET
    score = tmp.score
FROM (
    VALUES (32373477, 0.5566874822853446), (32373478, 0.5243741268418393)
) AS tmp(id, score)
WHERE tmp.id = s.score_id

The huge amount of updates causes some issues with our database as the updates require a lot of disk I/O (index updates and column rewrites), increases the queue depth and thus slowing down other queries.

My primary goal is to reduce the I/O writes, speed up the update process and reduce load on the database.

What are my options here?

It is possible for me to move the flag column to another table and do a complete rewrite of the score table. May this be more performant than updating only a selected amount of rows (~one third is updated each day)?

Alternatively, is writing all changed rows to a separate, index-less table and updating the primary table in-database a preferable solution?

Does anybody have some experience with and solutions for a similar kind of problem they are able to share?

The database server is a m4.large (2 cores, 8 GiB memory, 250 GiB storage, 750 IOPS) PostgreSQL 9.6.6 instance.


texts: what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata

texts:
I would like to organize the MySQL tables of a complex application into some groups, to avoid looking at a one big bin of tables. One big bin is harder to administrate when e.g. I would like to reset certain groups of related tables, or if I'd later wish to move a group of related tables to a different MySQL server altogether, and it is also more error prone when developing new DDL code.

I can apply a sense of grouping with my own table name prefixing "convention", but thought about using table spaces or even separate schemata. What would be the primary disadvantages of using separate table spaces or schemata for applying a sense of grouping upon my tables?


texts:
We have MySQL-server on Ubuntu with InnoDB-datafiles on a local disk. As part of an upgrade program, the company is planning to put the datafiles on a Windows fileserver where they can be mounted with SMB3.

Is it a good idea to put the InnoDB-files on a network share or is it better to keep them on a local disk?


texts: Generate random timestamp taking into account shift to daylight saving time

texts:
I want to generate random big data in MariaDB.
One of this columns is random timestamp which I generate like this:

select (current_timestamp
        - interval (rand() * 60 * 60 * 24 * 365 * 10) second
       ) as record_time;

Since the end of March there is a shift to daylight saving time / summer time, so in one day there is no time from 02:00 to 03:00. And when I inserting this data I get error like:
Incorrect datetime value: '2016-03-27 02:20:24.227322' for column db.tab1.record_time.

How to modify this part of insert script to generate correct random timestamp values while accepting daylight saving time?

Link to MariaDB info about timezones


texts:
This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated:

I created a web form, to a stored procedure, that submits userID, deptID, and a string of comma separated itemID's like so. The purpose is to map an employeeID to itemID's for different departments.:

(1, 2, '20,30,90,60')

I use a Table-Valued function to split that in to a proper table like so:

EmployeeID | deptID | ItemID
        1       2       20
        1       2       30
        1       2       90
        1       2       60

I, then, perform a MERGE between a mapping table (ID, EmployeeID, DeptID, ItemID) and the table created from the splitting function before.

MERGE [dbo].[metricEmployeeDimItemDim] AS TARGET
USING (SELECT accountID, deptID, ItemID FROM api.splitItemInts(@employeeId, @deptID, @ItemIDs, ',') ) AS SOURCE
ON TARGET.employeeId = SOURCE.accountID AND TARGET.ItemId = SOURCE.ItemID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (employeeID, deptID, ItemID)
    VALUES (SOURCE.accountID, SOURCE.deptID, SOURCE.ItemID)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

The issue is that I need the merge statement to not clean out the entire TARGET table... I just need it to affect records with matching USER and DEPT ID's (syncing entries for the ItemID)

How can I accomplish this with a MERGE statement?

Thank you,
Wes


texts: problem with query two different tables with foreign key relations

texts:
I am having a problem with querying two tables with different foreign key relations... My first query works fine but when adding another table with a different set of relations I am having a problem... Can some one point me in the right direction.

First Query works:

select `title`,`txt`,`img`
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%';

Second query that I am stuck when adding another table:

select `title`,`txt`,`img`
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%' OR (
select `firstname`,`lastname`,`email`,`mi`,`dName`,`phnumber`
from `staff`
inner join `departments`
on `staff`.`department` = `departments`.`did`
inner join `phones`
on `staff`.`phone` = `phones`.`pid`
where `staff`.`keywords` like '%test%');


texts: What exactly is overlapping candidate key?

texts:
Can some one please explain to me in simple terms what is overlapping candidate key ? What is overlapping as the name suggests?

Consider the below relation

R(L,M,N,O,P)
{
    M -> O
    NO -> P
    P -> L
    L -> MN
}

Which of the above functional dependency is bringing in overlapping candidate key in the above relation ?

Lets restrict the discussion to dependencies, I'm not interested in BCNF at the moment


texts:
I've got some empty string values being putting into my DB.
Code I've used:

CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$
      SELECT
        CASE
          WHEN length($1::VARCHAR) = 0 THEN NULL
          ELSE
            (SELECT array_agg(get_byte($1::bytea, a)) FROM generate_series(0, length($1::bytea) - 1) a)
        END;
    $$ LANGUAGE SQL;

    SELECT get_as_byte_array(name_first), * FROM ja_customers WHERE id = 8154501;

Question:
How can I find the responsible statement that is causing this issue?
Would be possible to create a trigger to do that? If so, how?

UPDATE 1: According to some users, I can create a CONSTRAINT. This is even better indeed...

Could be something like:

ALTER TABLE public.ja_customers
      ADD CONSTRAINT cc_check_empty_strings CHECK (("name_last" != '',"company" != '',"address" != '');

?

This is related to my previous question: Blank (but not null) columns - PostgreSQL 9.2


texts:
I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql manual learned about how to import by using repair by sort. This however started my problem. Although show processlist shows Repair by sort I cant see the tmp tables that it supposedly creates for the process not to mention when I check the /mysql/data directory .myd and .myi files totals the .sql file but the process does not end. I have left the process open for 2 days without any change at which time I killed it.

Could this be because the computer I have to use as mysql server is wamp or cause of the hardware limitations which has 2.gen i5 with 4GB Ram and 1 500GB HDD with 2 partitions or could the dump I have been given is corrupted?

Thnx in advance for any nudge in the right direction to this newbie about mysql and well databases in general. (Below is the parts of the my.ini with the help of percona config tool)

tmpdir = D:/temp
key_buffer_size = 2344M
myisam_file_sort_size = 80G
myisam_max_sort_file_size = 80G
myisam_sort_buffer_size=164M
join_buffer=1M
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
query_cache_size=132M
table_cache=1024
thread_cache_size=256
max_allowed_packet=156M


texts:
I am running two Windows Servers 2012 R2 with SQL Server 2014 Standard Edition. I have strange issue on mirroring.

I configured mirroring on for 15 databases, no Witness server. The Principal server show all database is Principal, Synchronized and the Mirror is Mirror, Synchronized/Restoring.

When I open Database Mirror Monitor I see Role Principal is in Mirroring State Synchronized but the Mirror's Role is not connected. I have fail over in both directions and the database came up on the mirror server normally. data is replicating for troubleshooting purpose I have done below steps.

I have seen in error log in principle nothing is related to mirroring  traced I can see most of the errors in principle are Error: 18456, Severity: 14, State: 38.
I have seen errors in mirroring server for all the databases are Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://server.company.com:5022'. Error: 1474, Severity: 16, State:
I have tried to resolve by stop and start the endpoint its was not resolved.
I have tried pause and resume it is also not resolved.
I have verified TCP/IP it is enabled on both the servers.
I have tried to configure alias it is also not resolve.
I have telnet from each other, it seems have  no communication issues
I have verified connect rights to domain level same service account for both principle and mirror.
Earlier it is SQL server 2014 sp2 ,today I have updated with latest service sp3 even though still I can see same in mirror monitor Not connected to mirror.

Please help on this issue, how to resolve this?


texts: How to express an empty relation (or table) in PostgreSQL?

texts:
Is there a way to express an empty relation in SQL without actually creating an empty table?

For testing purposes, I want to find a way to test something like:

SELECT count(*) FROM (...) my_empty_table;

where ... generates an empty table in-line. (I expect to get 0 as answer)

Is this possible?

I tried unsuccessfully:

SELECT count(*) FROM (VALUES) t;
SELECT count(*) FROM () t;

, which give syntax errors.

and

=> SELECT count(*) FROM (SELECT) t;
 count
-------
     1
(1 row)

(I'm primarily using SQL, but I'm also curious if there is standard-compliant way of writing this).


texts:
I'm building an application that will fetch messages with JSON every once in a while and store these in a postgres db. Now I want to prevent duplicates, so I calculate the sha-512 of the message and set it as the primary key. Since I want to prevent duplicates I can do two things:

Fetch all hashes from the database and see when inserting new messages if the hash already exists (checking in code)
Use an insert ignore (in postgres an update probably, since postgres doesn't support insert ignore) and let the database handle these exceptions

Considering I will probably be inserting a few messages at a time, I was wondering if method two will look up the primary key and see if exists for every insert as opposed to just storing a list in memory with method 1. What would be the most efficient?


texts:
I'm looking at a stored procedure that takes some XML from a web app and creates a SQL search with it. There are 11 variables but the execution plan says that this one line takes 68% of the whole execution time. The 68% is the cost relative to the batch. There are other similar @input.value processes that register as 0%, though the execution plan for this is more complex.

Can anyone tell me why or suggest a more efficient way of writing it?

SET @Message=@Input.value('(/Input/Message)[1][not(@xsi:nil = "true")]', 'int')


texts: Spanish characters saved incorrect in Mysql

texts:
I have googled this already, but I couldn't find what's wrong in my situation.

I have a wordpress site that has a lot of non-english users which type in words such as "español" it is printed correctly with php or sent via email, but when it's saved to the database, it appears as "espau00f1ol".

It's a MyISAM, mysql database with utf8mb4 unicode encoding. What do I need to change in order to be able to save non-English characters?


texts: database mirror - A connection attempt failed because the connected party did not properly respond after a period of time

texts:
I have database mirror set up in one of my servers and all seems to be fine, however, from time to time I get this error message in the logs:

--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Error",Null, NULL, NULL, N'desc'

  Database mirroring connection error 2 'Connection attempt failed with
  error: '10060 (A connection attempt failed because the connected party
  did not properly respond after a period of time,  or established
  connection failed because connected host has failed to respond.)'.'
  for 'TCP://MIRROR_SERVER_DB01:5022'.

--==============================================================================
-- query that shows the current state of each database in the mirroring
--==============================================================================
  SELECT   db_name(sd.[database_id])              AS [Database Name]
          ,sd.mirroring_state                  AS [Mirror State]
          ,sd.mirroring_state_desc             AS [Mirror State]
          ,sd.mirroring_partner_name           AS [Partner Name]
          ,sd.mirroring_role_desc              AS [Mirror Role]
          ,sd.mirroring_safety_level_desc      AS [Safety Level]
          ,sd.mirroring_witness_name           AS [Witness]
          ,sd.mirroring_connection_timeout AS [Timeout(sec)]
    FROM sys.database_mirroring AS sd
    WHERE mirroring_guid IS NOT null
    ORDER BY [Database Name];

question:

as it happens only sometimes, and I could not identify what else running at the same time - could have triggered this error,

what are possible things to look at while troubleshooting?

this server is hosted in the USA and I am not admin on the machine, only sysdba in the sql server world.


texts: Query to find and replace text in all tables and fields of a mysql db

texts:
I need to run a query to find and replace some text in all tables of a mysql database.

I found this query, but it only looks for the text in the tbl_name table and just in the column field.

update tbl_name set column=REPLACE(column, 'fuschia', 'fuchsia');

I need it to look in all tables and all fields: (everywhere in the database)


texts: When was second log file created for database?

texts:
Is it possible to see when a second log file was created for a database and by who?  I have found an additional log file that was created for one of our databases in our environment and I'm trying to see who and when it was created.  We are using SQL server 2012 SP2 Enterprise edition.

I'm trying to find out who and when the second ldf file was created for a database.


texts: Can Oracle databases start automatically when Windows machine is booted/rebooted?

texts:
In one of my client machines every time Windows starts we have to start the Oracle database manually.
Is there a solution/ command to start the database automatically when the machine is started/restarted?


texts:
Is there a way to completely check a SQL-Anywhere database (5.5.05) integrity?

"Validate" won't help: we had databases that were broken but "validate" always told us that everything is o.k.

I had the idea to simple read every record in every table (first "select count(*) from. ", second "select * from. ") Number of readed records must be same as the number of records reported by the "count(*)".

This works ok (I can report errors) - but I have found a database that will "hang" on the "Count(*)" (seems to loop forever, CPU-Usage 100% for >20Min (than I stopped it)

Any other ideas?


texts:
How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe?

SQL> DESC STUDENTS;
 Name                    Null?    Type
 STUDENT_NO                       VARCHAR2(10)
 SURNAME                          VARCHAR2(20)
 FORENAME                         VARCHAR2(20)

SQL> DESC MODULES;
 Name                    Null?    Type
 MODULE_CODE                      VARCHAR2(8)
 MODULE_NAME                      VARCHAR2(20)

SQL> DESC MARKS;
 Name                    Null?    Type
 STUDENT_NO                       VARCHAR2(10)
 MODULE_CODE                      VARCHAR2(8)
 MARK                             NUMBER(38)

I have:
SQL> SELECT MOD.MODULE_NAME, MA.MARK
    FROM MODULES MOD
        INNER JOIN MARKS MA ON(MOD.MODULE_CODE = MA.MODULE_CODE)
    WHERE MA.STUDENT_NO = (SELECT S.STUDENT_NO FROM STUDENTS S WHERE S.SURNAME = 'Doe');

MODULE_NAME                MARK
Databases                    80
Programming                  65
Operating Systems            50
 but cannot get it to display the lowest value.
Trying to put a MIN(MA.MARKS) in the SELECT clause or after the WHERE clause causes an error.


texts: Transactional replication of Date column from Sql Server 2012 SP4 to Azure Sql Database

texts:
We have a Sql Server 2012 SP4 where we are trying to setup transactional replication to Azure Sql Database. When starting replication is says

  Column mycolumn in object mytable contains type Date, which is not supported in the target server version, SQL Server 2005.

Since Azure Sql Database should support Date datatype Data types the problem could be that Sql Server identifies Azure Sql Database as version 2005.

Is there a way to get this working?


texts: Does shrinking log file affects the indexing in SQL Server?

texts:
In database log file grows from 32 GB to 70 GB and % of log space used become > 97%. After I took the log backup then %of log space used comes < 1% but size remains 70 GB .

Now I want to shrink the file size , but having following doubts : -

Is shrinking log file is good, to reuse empty VLF as well as reducing size of file .
How to determine that up to what % I need to shrink the file size ...
Does shrinking log file remove the indexing of database?

Any guidance please.


texts: mysql table very large size although it doesn't have a lot of records

texts:
I have drupal website, one of the tables is cache_form.  I have a cron to

DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW());

which works fine.

The size of cache_form.myd is 4.9G although I have this:

mysql>select sum(length(data))/1024/1204 from cache_form;

11.19144630 <b>==> 11 M

mysql>check table cache_form\G
Table: drupal.cache_form
Op: check
Msg_type: status
Msg_text: OK

mysql>select cid,length(data) from cache_form order by length(data) desc limit 1;
82373 ==> 80K

select * from information_schema.tables where table_name='cache_form'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupal
     TABLE_NAME: cache_form
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2681
 AVG_ROW_LENGTH: 4621
    DATA_LENGTH: 12391088 <b>==>11M</b>
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 177152
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2011-07-11 19:07:38
    UPDATE_TIME: 2011-12-26 08:40:09
     CHECK_TIME: 2011-12-26 08:09:45
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:

I have tried to repair it

mysql> repair table cache_form;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| drupal.cache_form | repair | status   | OK       |
+-------------------+--------+----------+----------+

then I got the correct size!!

Any idea of what might cause cache_form.myd file to be with this size and repair table finally removed the extra space?

Thanks


texts: How the statement based replication works internal on secondary node of mongodb?

texts:
Mongodb supports statement-based-replication.

Would like to know, how the statement based replication converts the code internally on secondary node of mongodb.

Is that working related to the mysql Row-based-replication ?


texts: Database Design - Removing ambiguity without adding loop

texts:
I'm looking for a better approach to the problem (and my solution) described below. I've attached an image of my initial design

Let's say I have a cat, and I'd like to find its owners and what vets they took it to. I can easily find out what vets the cat has been to, if I choose a specific vet, I want to know what owners have brought the cat there. I run into problems here, because the list of owners that came to the vet may include owners who own cats other than the one I've found. So now my thought is to say I've got this cat, and I'm at this vet, I want to see all owners who have taken this cat to this vet.

So I add a relationship between the cat and it's owners (this cat is owned by many people, and each of them has multiple cats i.e. many-many). Now I can say that I know these people own this cat, and I know what vets the cat has been to. I can now select all owners who have taken the cat to a specific vet.

I am of course aware that loops are not inherently bad in a database design (as long as no redundancy exists). I'm not convinced that redundancy doesn't exist here, the many-to-many relationship makes me feel that this is the only approach, but I was hoping that someone might have a better approach to this problem.


texts: Help understanding InnoDB Deadlock Detection

texts:
I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but from the status output it seems not. It would be a great help if anyone could provide an explanation of what the output meaning:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-27 16:19:43 7f4860dbe700
*** (1) TRANSACTION:
TRANSACTION 1305986614, ACTIVE 4 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 8 row lock(s), undo log entries 3
MySQL thread id 1681095, OS thread handle 0x7f48610ca700, query id 2133831389 127.0.0.1 root executing
Update Resources.ResourceAllocations as ra Set
                        ra.Act_Arr = Date_Add( ra.Sch_Arr, interval (deplate - ra.CumulativePerformanceAllowance + cpa) second ),
                        ra.Act_Dep = Date_Add( ra.Sch_Dep, interval (deplate - ra.CumulativePerformanceAllowance + cpa) second ),
                        ra.Arr_Late = (deplate - ra.CumulativePerformanceAllowance + cpa),
                        ra.Dep_Late = (deplate - ra.CumulativePerformanceAllowance + cpa),
                        ra.ActualType = 'F'
                    Where ra.ResourceTrainID = tid and (ra.Sch_Arr > pdate or ra.Sch_Dep > pdate) and  ( Select @updateMoveIDs := concat_ws( ', ', ra.ID, @updateMoveIDs ))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986614 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1305986516, ACTIVE 5 sec fetching rows
mysql tables in use 3, locked 3
36316 lock struct(s), heap size 3765800, 3874929 row lock(s)
MySQL thread id 1674629, OS thread handle 0x7f4860dbe700, query id 2133828216 10.22.33.17 stuart Sending data
Create table TrainsToRetrieve (Index (ResourceTrainID))
            SELECT ra.ResourceTrainID, ra.CIFSequence, Simplifier.ReportingLocations.SimplifierSequence
            FROM Resources.ResourceAllocations as ra
            Inner Join Simplifier.ReportingLocations ON ra.Tiploc = Simplifier.ReportingLocations.Tiploc
            inner Join Simplifier.SimplifierNames as SimplifierNames On SimplifierNames.ID = Simplifier.ReportingLocations.SimplifierNameID
            Where ra.DiagramDate >= date_sub(d, interval 1 day) and ra.DiagramDate <= date_add(d, interval 1 day) and Simplifier.ReportingLocations.SimplifierNameID = simnameid and (
                ( ra.Sch_Dep >= date_add(d, interval (SimplifierNames.StartHour) Hour) and ra.Sch_Dep < date_add(d, interval (SimplifierNames.EndDay* 24 + SimplifierNames.EndHour) Hour) ) or
                ( ra.Sch_Arr >= date_add(d, interval (SimplifierNames.StartHour) Hour) and ra.Sch_Arr < date_add(d, interval (SimplifierNames
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986516 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986516 lock mode S locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)


texts: Does Cassandra CQL provide a command to generate an execution plan?

texts:
In Oracle and other DBs you can generate an execution plan for SQL.

Is this possible for Cassandra?

I want to determine if it is possible to measure the relative complexity of several CQL statements to retrieve query data.

A Google search did not indicate that this is possible.


texts: Modelling a database for a shopping list - should I represent the scenario with a "redundant" relationship?

texts:
[I may provide some pictures later]

I'm facing this issue in modeling an entity-relationship diagram. I'll have a shopping list, where for each item the user inserts, it'll be associated with one product and many prices. Each price shall vary according to its product's brand and the place where it is available (i.e. a market or shop).

I also wish to provide the prices to other users, whose may not have access to the shopping list of who recorded it.

So I have product, item, price, brand and place as entity types, where item should be associated with many prices, and one product.

A price should be connected with one brand, one place and one product. Product is related to price, so anyone can find it, independently of the list item of others. It's also linked to the list item so a user can't relate more than one product to a list item.

Is it ok to "make" these two relationships with the product entity? Is there a better way to make it?


texts: how to count distinct value per day with start time and end time

texts:
my title may seem a bit confusing but here's the context.

I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user connects today and disconnects after 2days. So in the table it only has 1 record containing starttime which is today and endtime that contains 2days after today.

Now I want to extract a report of user connection per day. In this case when I group by on the reference code per day, it will only count as 1 for 3 days. but i want it to be counted as 1 per day. What are some ways that I can do this?

@MrBrownstone
The result of that query would be

username                 starttime         count(*)
445425760933             2019-02-05            1
445425760934             2019-02-05            1
445425760934             2019-02-06            1

This is not the result that I am looking for. Below is the result that I need:

username                 starttime         count(*)
445425760933             2019-02-05            1
445425760933             2019-02-06            1
445425760933             2019-02-07            1
445425760934             2019-02-05            1
445425760934             2019-02-06            1

Since the code 445425760933 was used until 2019-02-07


texts: CDC error - Error converting data type varchar to uniqueidentifier

texts:
I have CDC and Replication configured on an Always On cluster.
I had to patch the SQL Server on Node 1 so I failed over the instance to node 2 and started patching node1.

After about an hour one of the log reader agent jobs started failing with the below error

  "The process could not execute  sp_replcmds..."

When I looked at the start of the failure in history, it says

  "Failed to insert rows into Change Data Capture change tables."

So I queried sys.dm_cdc_errors and the error was

  Error converting data type varchar to uniqueidentifier.

I am a noob with CDC.  Looks like it has nothing to do with Replication and its a CDC error.

Looking for suggestions on how to narrow down the issue, may be to the table causing the error?

Update
Now another log reader agent is failing with the similar error.

  Procedure or function sp_batchinsert_1350295870 has too many arguments
  specified. Failed to insert rows into Change Data Capture change
  tables.


texts: How to grant drop table/function privileges to a role in PostgreSQL?

texts:
I want to grant drop privileges on all tables and functions (not only those owned by the user) in certain schema of a specific database to a specific role. However, GRANT ALL PRIVILEGES is not enough and I didn't find how to do without making the role a superuser - superuser has rights over other databases on the same server, which is not what I want. I wouldn't mind superuser privileges limited to a specific database, but I'm not sure how to do it.

My code:

CREATE USER _administrator PASSWORD 'pwd12345';
CREATE ROLE administrator NOLOGIN ADMIN _administrator;

GRANT ALL PRIVILEGES ON DATABASE "myDB" TO administrator;
GRANT ALL PRIVILEGES ON SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO administrator;

administrator is the group of myDB database administrators, _administrator is the most powerful role my client app will be able to log in as.

What I did miss or do wrong?


texts: How can I get the user not to be listed twice?

texts:
I'm using the following query to list best times in a race. This works perfectly well but someone does the best record twice (for example, they finished the race in 15 seconds twice) they being listed twice. I don't want them to be listed again.

SELECT
    *
FROM
    racerecords
WHERE
    (holder_regid, rec_time) IN (
        SELECT
            holder_regid,
            MIN(rec_time)
        FROM
            racerecords
        WHERE
            race_id = 17
        GROUP BY
            holder_regid
    )
AND race_id = 17 ORDER BY rec_time;

Additional Info: holder_regid is the registration ID of the racer and the rec_time is the record time.

So how can I achive this? I tried to use DISTINCT but I got error. Help me with the query.


texts: How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?

texts:
I have a Parent entity type with two Child entities, with a relationship cardinality of many-to-one (or M:1) from Parent to Child.

The business rules say that:

It is mandatory that the Parent entity type be connected to either of Child A or Child B in any given process, but also that…
The Parent cannot be connected to both nor can it be connected to neither during that process.

I am stumped on how to represent the optionality here. It is a mandatory relationship on the Parent side, but not mandatory in the same way on the Child side. It is necessary that at least one Child be present in the relationship at a given time, but I do not feel it is correct to call this mandatory from the Child side, nor do I feel it is correct to represent both Child entities in optional relationships with the Parent.

So:

Is each Child entity in a mandatory or optional relationship with Parent?

This is a theoretical question in my head more-so, but I've ran with the "Who is the parent's favourite child?" example in my diagram as it matches: a child can have many parents favouring them, the parent can have one of either child as their current favourite, and the parent can't have no preference toward one child at a given moment. Cruel, but it works for my example!

I haven't got as far as DB design, though I was assuming that you can set up a column in the Parent table that can accept a Foreign Key from either of Child_A or Child_B but has a Not Null constraint. Or is my assumption off, and as such would this then mean the ERD above can't be implemented like so in the database?


texts: Why in where clause, TO_CHAR is slower than cast?

texts:
For Example:
In some query, the WHERE clause in query 1 below is slower than query 2:
Query 1 - the slow query
     where to_char(field,'yyyy-mm-dd') = '2015-01-01' ...

Query 2 - the faster query
     where date(field) = date ('2015-01-01')...

note: the 'field' is a timestamp


texts:
We are on SQL Server 2012 SP3 Enterprise Edition on Windows 2012 R2.

I saw these errors in the sql logs:

  Failed allocate pages: FAIL_PAGE_ALLOCATION 1

  2016-06-14 04:28:27.44 spid175     Error: 701, Severity: 17, State:
  123.

  2016-06-14 04:28:27.44 spid175     There is insufficient system memory
  in  resource pool 'default' to run this query.

  2016-06-14 04:28:27.44 Server      Error: 17300, Severity: 16, State:
  1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

  2016-06-14 04:28:27.44 Server      Error: 17300, Severity: 16, State:
  1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

  2016-06-14 04:28:27.44 spid131     Error: 701, Severity: 17, State:
  123.

As far as I can tell, it looks like it ran out of memory at some point.
Is there a way to figure out what caused it to go out of memory?
The MEMORYCLERK_SQLQERESERVATIONS is pretty high, does anyone know what that is for?

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22599824

EDIT: We have 32 gigs of RAM on the server and 28 is allocated to the SQL Server.  The max memory setting is 28gb and min server memory is 8gb.

Here is a link to the ErrorLog output:
ErrorLog

Here is the link to the sys.dm_os_process_memory output:
Query Output

Link to the wait types:  WaitTypes

I ran it during a time when we seem to have more memory usage:

  SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

Results: memory_grants

  SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
  FROM sys.dm_exec_query_memory_grants AS mg
  CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
  CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
  ORDER BY 1 DESC OPTION (MAXDOP 1)
  Results: QueryMemGrants

  SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes >as [Bytes Used in Cache]
  FROM sys.dm_exec_cached_plans AS cp
  JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
  WHERE qp.query_plan.exist('declare namespace >n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; >//n:MemoryFractions') = 1
  order by cp.size_in_bytes desc
  OPTION (MAXDOP 1)

Results: CachedMemGrants

  Select granted_query_memory,session_id,command from sys.dm_exec_requests
  Results:dm_exec_requests

XML of query plan from one of the queries that was running:
QueryPlan


texts: How do I connect to oracle apex server through JDBC ?

texts:
I have got a workspace in apex.oracle.com , Now , I want to connect to that database through JDBC from ubuntu-14.04 machine. please explain the steps , From where should I download the drivers and what should be the arguments in

DriverManager.getConnection();

step by step please. Thank you


texts: Sum on distinct and group by and filtering

texts:
This is the next version of Sum on distinct and group by

But It goes deeper

I have this :

CREATE TABLE active_connections(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL);
INSERT INTO active_connections VALUES (0, 'web-mobile', 1);
INSERT INTO active_connections VALUES (1, 'web-desktop', 1);
INSERT INTO active_connections VALUES (2, 'web-desktop', 2);
INSERT INTO active_connections VALUES (3, 'web-desktop', 3);
INSERT INTO active_connections VALUES (4, 'web-desktop', 3);
INSERT INTO active_connections VALUES (5, 'native', 3);
INSERT INTO active_connections VALUES (6, 'native', 4);

What I'm trying to do is to group distinct user per native vs 'web-desktop' and 'web-mobile'.

I would like to get

source  user_count
native  2
web     3

I'm able to do it only if I alias the source column to something different. I don't understand why

SELECT
CASE
  WHEN source IN ('web-mobile', 'web-desktop') THEN 'web'
  ELSE source
  END AS source_2,
COUNT(DISTINCT user_id) AS user_count
FROM active_connections
GROUP BY source_2;

First why I can say END AS source? Second can I do it without using a subquery like below to be able to have source column?

WITH mapped_source AS ( SELECT
CASE
  WHEN source IN ('web-mobile', 'web-desktop') THEN 'web'
  ELSE source
  END AS web_or_native,
COUNT(DISTINCT(user_id)) AS user_count
FROM active_connections
GROUP BY web_or_native )

SELECT web_or_native AS source, user_count FROM mapped_source;

Rextester : http://rextester.com/FRB66163


texts: SQL db2 remove specific item from the query

texts:
Can you please help me out with the below query?
What I am looking for is a code to exclude items with wording '90003' from column COR_ID.
So far I have a code that extracts data with date older than today from column VAL_DT, excluding items with wordings MAN, NOSTRO, SCREV and SCRIN for which query shows also dates newer than today.

All I need is for the below code is to remove '90003' from column COR_ID

WHERE
    VAL_DT < CURRENT DATE AND COR_ID <> '90003'
    OR (
       MVT_DS = 'NOSTRO'
       OR MVT_DS = 'SCREV'
       OR MVT_DS = 'SCRIN'
       OR MVT_DS = 'MAN'
       )


texts: Replication from slave broken after table rename/swap

texts:
We run a RDS MySQL master-slave replication setup and quite regularly add another replica (replicating from the slave) for maintenance, testing, analytics, etc.

However, I currently cannot create a new instance that replicates from the slave. When it starts replicating, error 1032 occurs:

Could not execute Update_rows event on table x.y; Can't find record in 'y', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin-changelog.083465, end_log_pos 260523

When I create a new instance that replicates from the master, everything works as usual.

During recent maintenance we created a new table y_temp, filled it with a subset of rows from y, and then renamed y to y_old and y_temp to y. It seems very likely that this has caused the state we're seeing, but I don't understand how. During the maintenance, slave was replicating from master as usual and it now has the same rows in y as master.

I have not been able to find any reports that match our situation. I would prefer to have new instances replicate from slave instead of master. Should we recreate the slave instance?

This is RDS MySQL 5.6.35 and I use the RDS console to add the read replica.


texts: Why does SQL Server Profiler trace not capture SELECT operator in execution plans?

texts:
When I run SELECT queries in SSMS and choose Include Actual Execution Plan, it always includes the SELECT operator. Nice because I can see the full properties like memory grants, cached plan size, subtree cost, etc.

When I run a trace with SQL Server Profiler and grab execution plans via the Performance > Showplan XML Statistics Profile event, I get all the operators except for the SELECT. Even when I extract to a file and view the plan in SSMS, it's the same--no SELECT operator. Is this expected behavior for that particular event? Should I be using some other event to capture the SELECT operator and its details?

I'm running Profiler from SSMS version 2014, doing traces against a SQL Server 2008 R2 instance.

Also wondering how to capture stats to a trace that I'd get from running SET STATISTICS IO in SSMS. Or should that already be in the XML Showplan somewhere?


texts:
I need some help analysing some simple t-sql execution plans.
I have a table [User] with the following 3 columns:

Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50))

I have Created an Unique NonClustered Index for the Username column

The execution plan for a query filtering by FirstName:

select * from [User] where FirstName ='John'

shows a Clustered Index Scan with a total cost of 0,0033095

The execution plan for another query filtering by Username:

select * from [User] where Username = 'johndoe'

shows a nested loop containing an Index Seek followed by a Clustered Index Seek with a total cost of 0,00657038! this is twice as much as the cost of the previous query and I don't get it!

There is an index associated with the Username column and no index associated with FirstName, I would expect the second query to be faster.


texts: All SQL Server aliases linked to default instance instead of instance I assigned

texts:
I have multiple SQL Server instances(different versions) on my computer.
I try to create aliases for each instance as

First I thought I had everything settled then I found no matter which alias name I connect, it will all be led to SQL2017 instance.

I wanna provide more information but don't know where to start with.
And idea what I might do wrong?


texts: Cross Database Queries using IBM Data Studio

texts:
I'm currently using IBM Data Studio 4.1 to run queries against DB2 LUW database(s).
My understanding is that I can do cross database queries by prefixing the database(s) names.
For example, database.schema.table.
However, when I open a SQL Query window, there is a dropdown that requires the user to select the database connection. Since it only allows one database connection, how do we run cross database queries in IBM Data Studio?


texts: Pulling an Image from a Database vs pulling from a link to a directory

texts:
Possible Duplicate:
  Is it better to store images in a BLOB or just the URL?
  Files - in the database or not?

I was wondering what the benefit is of pulling a varchar out of a database that refers to picture link to display to the user on a website is.  I am a working with a developer who insists that we store and pull the picture directly in the database using a clob.  It seems as though there is no appreciable difference.  Maybe I am just an idiot, but why do one over the other?

Thanks


texts: Transfering records with foreign key referring to new value

texts:
In an SQLite DB, I have to transfer some records from two tables (old_warnings and old_acknowledges) to other two tables(new_warnings and new_acknowledges).

Transfering warnings is not a problem.

INSERT INTO new_warnings (event, comment, value)
SELECT event, comment, value
FROM old_warnings
WHERE old_warnings.event = 'one';

However, the acknowledges tables have the reference to the warning (warning_id:integer) it refers to and if I transfer the acknowledges first, I end up having the old warning_id in the table. But, if I transfer the warnings first, I don't have any reference to the new id to insert in the new_acknowledges table.

Any idea of how to handle this situation?


texts: Create relationship between two tables with composite primary key

texts:
I've created a table [t1] with two fields and a composite primary key, show in below:

create table t1(
    id int,
    name nchar(10),
    primary key(id,name)
);

I want to create a second table [t2] that has a relationship with [t1], but I get an error with the below create table script:

create table t2(
    id int references t1(id),
    name nchar(10) references t1(name),
    primary key(id,name)
);

This the error message:

  Msg 1776, Level 16, State 0, Line 1
  There are no primary or candidate keys in the referenced table 't1' that match the referencing column list in the foreign key 'FK__t2__id__33D4B598'.


texts: "Login failed for user" when using admin user in SSMS

texts:
I am trying to connect to an Azure SQL database via SSMS. The connection string supplied by Azure is:

  Server=tcp:SERVER_NAME.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

So, first of all I'm not sure if I should include ALL of

tcp:SERVER_NAME.database.windows.net,1433

in the server name when connecting via SSMS.   Also, the username is an email address.  Let's say it's:

foo@bar.com

So, this is what I tried:

ServerName: SERVER_NAME.database.windows.net
Login:      foo@bar.com@SERVER_NAME.database.windows.net
Password:   [password]

At first when I tried, I was told that the IP was not allowed.  So I added the IP to the server firewall settings.  But now, I get this:

  Login failed for user foo@bar.com

I am using the same UserID and Password that I use to log into the Azure portal.  And it's the UserID that is specified in the Access Control window.

I am working from home, but I have also tried while on my company's VPN.  Neither work.

Is there something else I'm doing wrong?


texts: Any way to optimize this query? Indexing? Re-structuring?

texts:
I'm having real issues with the performance of this query in my application:

SELECT
    DISTINCT ON (pole_prices.station_id,
        pole_prices.fuel_grade_id) pole_prices.*
FROM
    "pole_prices"
WHERE
    "pole_prices"."station_id" = 11
    AND "pole_prices"."fuel_grade_id" = 4
    AND (pole_prices.price_at < '2018-04-18 10:59:23')
ORDER BY
    station_id ASC,
    fuel_grade_id ASC,
    CASE WHEN (source_id IS NULL
            OR source_id = 2
            OR source_id = 9) THEN
        ((price_at AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/London')::date::text || array_position (ARRAY [ 2,
            9,
            NULL,
            1 ],
            source_id::int)
END DESC,
CASE WHEN source_id = 1 THEN
    price_at
END DESC
LIMIT 1000

At first I thought it might be related to the time-zone conversions, but after removing them, it seems they don't really make all that much difference.

Here's the data structure for the table, along with indexes at the bottom:

And finally the execution plan:

Here's the CREATE TABLE for those that requested it:

CREATE TABLE "public"."pole_prices" (
    "id" int8 DEFAULT nextval('pole_prices_id_seq'::regclass),
    "raw_data_id" uuid,
    "source_id" int8,
    "owner_id" int8,
    "station_id" int8,
    "fuel_grade_id" int8,
    "price" numeric,
    "price_at" timestamp,
    "created_at" timestamp,
    "updated_at" timestamp,
    "sequence" varchar,
    "category" varchar DEFAULT 'private'::character varying,
    CONSTRAINT "fk_rails_656092cf98" FOREIGN KEY ("source_id") REFERENCES "public"."adaptors"("id"),
    CONSTRAINT "fk_rails_067c5aaccf" FOREIGN KEY ("station_id") REFERENCES "public"."stations"("id"),
    CONSTRAINT "fk_rails_6412df441d" FOREIGN KEY ("owner_id") REFERENCES "public"."companies"("id"),
    CONSTRAINT "fk_rails_27035cba8a" FOREIGN KEY ("fuel_grade_id") REFERENCES "public"."fuel_grades"("id"),
    PRIMARY KEY ("id")
);


texts: Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.


texts: How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables

texts: I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on spesific tables to several developers who are in the SQLQRY AD account. How can i do that without extracting them out of the group?


texts: SQL Server "TOP 101" much slower than "TOP 100"

texts:
SQL Server shows huge decrease in performance when query is written like:

select top 101 name
from Dogs
order by name

compared to select top 100.

As far as I know, this happens for the reason that SQL Server in a top 100+ query simply orders all dataset and picks top records (while top 100 and less queries use more sophisticated algorithm).

Are there any workarounds for this?


texts: "ERROR:malformed array literal" when using json_to_record with a JSON array element in Postgres 9.4

texts:
This illustrates the issue nicely:

When column b is of type text, and not an array, the following works:

select *
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
    as x(a int, b text, d text);

 a |         b          | d
---+--------------------+---
 1 | ["hello", "There"] |

But if I define the b column as an array, I get this error:

select *
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
    as x(a int, b text[], d text)

ERROR:  malformed array literal: "["hello", "There"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

How can I convince/coerce json_to_record (or json_populate_record) to convert a JSON array into the Postgres array of the target column type?


texts:
I have configured the login audit to save only the failed ones, but I need to omit a specific login in said audit, is it possible?

I am using sqlserver 2008-r2

Thanks.


texts: How do I copy a table with SELECT INTO but ignore the IDENTITY property?

texts:
I have a table with identity column say:

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

It's well known, that this

select * into copy_from_with_id_1 from with_id;

results in copy_from_with_id_1 with identity on id too.

The following stack overflow question mentions listing all columns explicitly.

Let's try

select id, val into copy_from_with_id_2 from with_id;

Oops, even in this case id is an identity column.

What I want is a table like

create table without_id (
 id int,
 val varchar(30)
);


texts: How to take backup of multiple DB in a specific point of time

texts:
My test setup is having 3 databases in postgresql-9.6 instance, 2 DBs consumed by 2 live Web apps.
I am using pg_dump to take hot backup of all databases. In order to keep CPU load under threshold, backup script perform pg_dump in sequence.

pg_dump -U postgres -Fc <db-1> > <db-1>.dump
pg_dump -U postgres -Fc <db-2> > <db-2>.dump
pg_dump -U postgres -Fc <db-3> > <db-3>.dump

All three database share information like User details, Subscription, Task state, etc.
As the backup is taken in different time period, in the event of Data recovery, the database will end up with inconsistent data across all three DB. One of the DB size is 180GB+ and the dump time for this DB alone would take 30 min.

How to take backup of all databases from a specific time period.

I am not sure how to use the Snapshot feature in postgresql.


texts: SERVERPROPERTY('HadrManagerStatus') equating to 1 even if Always On is not configured

texts:
We have a SQL Server Enterprise 2017. I would like to get insights as to why our SERVERPROPERTY('HadrManagerStatus')returns 1 even if Always On is not configured in this instance.

I have a local instance in my work machine that is a stand alone. When I run SELECT SERVERPROPERTY('HadrManagerStatus') it returns 1 even if I have not set up Always On on this machine. Never have attempted to check the "Enable Always ON" box on the Service in the SQL server Configuration Manager.

I have setup this instance to be a publisher and a distributor for Replication. I have restored databases on this instance that the database were part of the availability databases.

I guess my question is, how does this SERVERPROPERTY('HadrManagerStatus') evaluates? What conditions does it check?


texts: SQL Server managed backup missing in ssms

texts:
Trying to configure Sql Managed backup to azure, and it seems that "Managed backup" is missing from the GUI under Management in SSMS 17.2

All guides I see kind of implies that it's already there. But if its missing, then what is needed to make it show in the Gui? An Azure backup client installation? A command?

Now it seems that you need to run a command to enable the managed backup on the instance, but if that fails, you can't use the gui, because it doesn't show.

Tried SQL Server 2016 Standard and 2016 datacenter evaluation. Using latest SSMS 17.2.


texts: How to prevent query execution after exiting the \e editor in psql?

texts: Is there an option that can change the "exit external editor -> query runs automatically" behaviour of the psql \e switch ?  (Other than intentionally mangling the query syntax).


texts: Error: Incorrect usage of UNION and ORDER BY

texts:
I have this query:

SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`))
from wp_result_plugin
WHERE Bet=2
AND month(`Date`) = EXTRACT(month FROM (NOW()))
ORDER BY MONTH(`Date`) ASC

UNION

SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`))
from wp_result_plugin
WHERE Bet=2
AND month(`Date`)
ORDER BY MONTH(`Date`) ASC

I am seeing this error:

Incorrect usage of UNION and ORDER BY

How to make this work?


texts:
I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to make efficient queries? How is it using those statistics? Is there any better description of this whole process, than in the documentation?

I'm fully aware that my question might not be understandable, but I really don't know how to explain my problem easier. If you have questions, please ask.


texts: Error "ORA-12726:unmatched bracket in regular expression" while trying to separate a part of a string from other part

texts:
I have a string like this :

v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]'

and I need to separate sum:[] from where:[] and assign them to different variables like this:

v_sum_string:'sum(x)>1 and sum(y)>2 and sum(z)>3';
v_where_string:'n=1 and m=2';

What I've written so far is:

select regexp_substr('sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]',
                 'sum:[]+,',
                 1,
                 1)
from dual;

but it gives me error ORA-12726:unmatched bracket in regular expression and I can not understand where my problem is .
I was wondering if you could help me with this and if you have better opinion about the original string (for example it would  be easier to separate if it was parentheses) please say

thanks very much in advance


texts:
I am trying to connect to mongodb and querying data from it. Here is what I am doing:

In node js my db connection file is looks like this:

//Using mongodb drivers has to be installed using npm
var MongoClient = require('mongodb').MongoClient;
var dburl = 'mongodb://localhost:27017/meanhotel';

var _connection = null;

var open = function() {
MongoClient.connect(dburl, function(err, db) {
if (err) {
console.log("DB connection failed",err);
return;
}else{
_connection = db;
console.log("DB connection open");
}
});
};

var get = function() {
return _connection;
};

module.exports = {
open : open,
get : get
};

And the node js code which I am using to query data from the database is like this:

var dbconn = require('../data/dbconnection.js');

var hotelData = require('../data/hotel-data.json');

module.exports.hotelsGetAll = function(req, res) {
//
var db = dbconn.get();
var collection = db.collection('hotels');
var docs=collection.find();

collection
.find()
.toArray(function(err,docs){
console.log("Found hotels",err);
res
.status(200)
.json(docs);
});

If I visit the URL

  http://localhost:3000/api/hotels

Nothing is coming out.

Also in terminal I am getting this output.

Found hotels { [MongoError: not authorized on meanhotel to execute command { find: "hotels", filter: {} }]
name: 'MongoError',
message: 'not authorized on meanhotel to execute command { find: "hotels", filter: {} }',
ok: 0,
errmsg: 'not authorized on meanhotel to execute command { find: "hotels", filter: {} }',
code: 13,

codeName: 'Unauthorized' }

I am using this for launch the mongo shell:

mongo localhost:27017/admin -u user -p pass

Also if I want to dump data to the database I am using this.

sudo mongodump --db meantest -u user -p pass --authenticationDatabase
admin --gzip

What could be going wrong?


texts: SQL Server Stretch database, and archiving table to SQL Azure

texts:
SQL Server 2016 Standard Edition

We have a generic need: A large, growing table.

For performance reasons, we want to move obsolete/old data out to an archive table.

SQL Azure would be the perfect home.

The question is:  Should we use a table in a "stretch" db (new in SQL 2016), or should we simply manage this in application code?


texts: Adding non-nullable NEWSEQUENTIALID() column to existing table

texts:
In SQL Server 2008, I am adding a non-nullable column to an existing table, and want to make the default NEWSEQUENTIALID().

ALTER TABLE MyTable
ADD MyColumn UNIQUEIDENTIFIER NULL
CONSTRAINT DF_MyTable_MyColumn DEFAULT NEWSEQUENTIALID()

UPDATE MyTable SET MyColumn = NEWID()

ALTER TABLE MyTable
ALTER COLUMN MyColumn UNIQUEIDENTIFIER NOT NULL

Is it safe to populate the column with NEWID() as shown above, or is there a better way to use a sequential ID value?

My concern is that NEWID() will generate a new guid that may be close to the sequential guid.  Then when the sequential guid hits that value from NEWID(), it will run into issues.


texts: Optimize SELECT statement for mysql database

texts:
I have this query:

SELECT COUNT(*) AS "Other"
FROM events
JOIN triggers ON events.objectid = triggers.triggerid
WHERE events.name REGEXP "No data from SAM0056(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline"
AND events.value = 1
AND triggers.value = 1;

In total I need to execute 32 variants of these query depending on the REGEXP. The part "SAM0056" ranges form "SAM50" to "SAM0082". So the pattern is "No data from SAM00XX(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline" where XX ranges from 50 to 82.

I also need to execute these query every 15 seconds. So I have a lot of select statements.

These are the DDL for events and triggers:

CREATE TABLE `events` (
  `eventid` bigint(20) unsigned NOT NULL,
  `source` int(11) NOT NULL DEFAULT '0',
  `object` int(11) NOT NULL DEFAULT '0',
  `objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `acknowledged` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  `name` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `severity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`eventid`),
  KEY `events_1` (`source`,`object`,`objectid`,`clock`),
  KEY `events_2` (`source`,`object`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

and

CREATE TABLE `triggers` (
  `triggerid` bigint(20) unsigned NOT NULL,
  `expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `description` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `url` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  `lastchange` int(11) NOT NULL DEFAULT '0',
  `comments` text COLLATE utf8_bin NOT NULL,
  `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `templateid` bigint(20) unsigned DEFAULT NULL,
  `type` int(11) NOT NULL DEFAULT '0',
  `state` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `recovery_mode` int(11) NOT NULL DEFAULT '0',
  `recovery_expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `correlation_mode` int(11) NOT NULL DEFAULT '0',
  `correlation_tag` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `manual_close` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`triggerid`),
  KEY `triggers_1` (`status`),
  KEY `triggers_2` (`value`,`lastchange`),
  KEY `triggers_3` (`templateid`),
  CONSTRAINT `c_triggers_1` FOREIGN KEY (`templateid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

An explain-statement of the query shows this:

+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref                    | rows | Extra       |
+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+
|    1 | SIMPLE      | events   | ALL    | NULL               | NULL    | NULL    | NULL                   | 8397 | Using where |
|    1 | SIMPLE      | triggers | eq_ref | PRIMARY,triggers_2 | PRIMARY | 8       | zabbix.events.objectid |    1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+

And an insert-statement into these 2 tables (events and triggers):

insert into events (eventid,source,object,objectid,clock,ns,value,name,severity)
values (4985807,0,0,22281,1585805538,856070799,1,'No data from SAM0106-FTG, FortiGate offline',3)

INSERT INTO triggers (description,expression,recovery_mode,type,url,priority,comments,manual_close,status,correlation_mode,recovery_expression,correlation_tag,templateid,triggerid)
VALUES ('CPU-Temperature to high','{29157}>35','0','0','','3','','1','0','0','','','22515','22541')

So how can I optimize these query?
I use mariadb version 10.1.44 on a Ubuntu version 18.04.4.


texts: Compression setting not effective for differential backup maintenance plan?

texts:
We are taking backups (Full, Differential, and Transaction Log) of databases using the maintenance plans feature in SQL Server.

While taking the backup, I have chosen the compress option to compress the backup files, and I have enabled "backup compression default" also.

Full backup maintenance plans are compressing the backup file; file size is 4 times less than the database size. But when we comes to differential backup, I think the compression setting is not working.

I have observed today's backup differential file. Yesterday 4 tables in a database got populated with 10GB of data each, so differential backup file size crossed 40GB. I am certain these were the only changes, because it is a fresh database, and there were no maintenance activities run on that day.

The .ldf file size grew to 500 GB on the day the backup was performed. This might cause an issue for big size, but database size (.mdf) is less than 50GB.

Please look at the below image for additional info:

I want to confirm that compression will not work for differential backups, or perhaps there is something else I missed?

If it does not work, based on Paul Randal's database percent change script, I may decide to take full or differential backup.

Version

select @@version;

Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64)
    May  4 2015 18:48:09
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Size Info

select type,
       backup_size,
       compressed_backup_size
from msdb..backupset
where backup_start_date >= '20170927'
and database_name = 'yourDB'

type    backup_size      compressed_backup_size
I       213,473,351,680  42,147,293,093


texts:
I am searching if there is a wildcard assigned to the \ meta-commandsSee description - meta-commands in Postgresql.

for example..

postgresql=> \copy scheme.table FROM 'D:\path\to\%.csv' DELIMITER ';' CSV ENCODING 'LATIN1';

Make notice of the % in the example provided - A known declaration of wildcards could be _, %, . or *. But I do not seem to find documentation of wildcards in /meta-commands for Postresql.


texts: How to set the grow rate of database in SQLserver? My database have huge size about 42 GB but the default auto grow size is 1 M. I think it is very inefficiency for the grow size of the Database.


texts: How can I improve this nested MySQL query to avoid redundant WHERE statement?

texts:
I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question):

tbl_users:
ID | email  (all subscribers to our email list)

tbl_mailings:
ID | mail_title (each marketing email)

tbl_sends:
ID | user_id | mailing_id (each user who was sent each mailing)

tbl_opens:
ID | user_id | mailing_id (each user who opened each mailing)

tbl_actions:
ID | user_id | mailing_id (each user who took the target action on each mailing)

I inherited this query that I have been using to get the open rate and action rate for each mailing:

SELECT subq_opens.numopens/subq_sends.numsends as Open_Rate,
       subq_actions.numactions/subq_sends.numsends as Action_Rate,
FROM
(SELECT COUNT(DISTINCT sends.userID) AS numsends
    FROM tbl_sends WHERE mailing_id = 5694) AS subq_sends,

(SELECT COUNT(DISTINCT opens.userID) AS numopens
    FROM tbl_opens WHERE mailing_id = 5694) AS subq_opens,

(SELECT COUNT(DISTINCT actions.userID) AS numactions
    FROM tbl_actions WHERE mailing_id = 5694) AS subq_actions

Not too elegant, but functional, even though I have to change the mailing ID in multiple places every time.
However, now I need to change that WHERE statement to something a great deal more complicated, and I don't want to have to repeat the WHERE statement so many times.

Is there a way I can reformulate this without the redundant WHERE?
I have tried various nesting schemes, but can't seem to find a way to get it right. I can't change anything about the table structure; that is set in stone. Thank you in advance for your help.


texts: Weird SQL Server instance crash on casting to numeric

texts:
While working with C# Entity Framework I noticed a crash of my SQL Server instance.

I was able to track it down to this statement:

SELECT * FROM dbo.[TestTable]
where mpnr in (1099059904,
1038139906,
1048119902,
1045119902,
1002109903,
1117109910,
1111149902,
1063149902,
1117159902,
1116109904,
1105079905,
1012079906,
1129129904,
1103059905,
1065059905,
1091059906,
1110149904,
1129149903,
1083029905,
1080139904,
1076109903,
1010019902,
1058019902,
1060019903,
1053019902,
1030089902,
1018149902,
1077149902,
1010109901,
1011109901,
1000119902,
1023049903,
1107119909,
1108119909,
1106119909)

The table looks like this:

CREATE TABLE dbo.[TestTable]([MPNR] [numeric](9, 0) NOT NULL)

The crash occurs every time I start the query. If I reduce the number of values within the IN clause, it works. (It returns no rows, of course.)

I'm aware that the values in the IN clause are 10-digit numbers and the column has only 9-digits, but that should not lead to a crash of the whole SQL Server instance.

My SQL Server's version is 2008 R2 on a Windows Server 2003 32-bit.

Is this a known Bug?
Is there a Patch for SQL Server?


texts: Why does an SQL Server Agent job try to create a procedure

texts:
I am using SQL Agent to schedule the exeuction of a procedure.

The first time that the procedure is executed, it runs without problems.
The second time the job (step) executes, it does not finish.

A variation of the following SQL statement tells me that the running statement is create procedure xyz... (where xyz is the name of the procedure that the job is supposed to run).

select
   prc.loginame,
   prc.program_name,
   sql.text                                           sql_text
from
   sys.sysprocesses     prc                 cross apply
   sys.dm_exec_sql_text(prc.sql_handle) sql

I don't do any DDL in my procedure (or otherwise) and am wondering what causes the job to try to create the procedure and why the job hangs?


texts: How to detect compatibility errors - SQL Server 2012

texts:
I am changing a database's compatibility mode from 90 (SQL Server 2005) to 110 (SQL 2012) and I am wondering at what point would I see errors from breaking changes if there are any.

I ran Upgrade Advisor 2012 which spotted several Stored Procedures that needed updating. An  example was:

"In SQL Server 2005 or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias."

However, when I run this stored procedure in SQL 2012 (without having made changes) it doesn't show any errors. Also, when I changed the database compatibility level from 90 to 110 there were also no errors. Nor when I restored the database.

If I am not seeing errors anywhere, I am hoping that Upgrade Advisor caught everything. Is there any other ways I should check for compatibility errors?

Also, how is it possible that this stored procedure ran successfully even though Upgrade Advisor told me it would fail? Thanks :)


texts: SQL Server 2017 installation unauthorized error

texts:
I'm getting the below errors while installing SQL Server on a Windows machine.
I'm using my admin account which has permissions (I’m in Administrations group. Verified here -> Computer management -> Groups)
Does it ring any bells based on the below errors to anyone? Are there any known workarounds. Spent a day without any good solutions. This is a fresh installation btw. The worst part is I can't even uninstall it as it complains about the same unauthorized error.


texts: Time stored using now()::timestamp function stores wrong value

texts:
I'm using AWS RDS for a PostgreSQL 11 database with PGAdmin. I store creation date of every row using now()::timestamp to store date and time without time zone. The problem is the time stored is never the value I want (UTC+05:30).

If I set parameter timezone as UTC in RDS, then the time stored is 5:30 hours behind India's time but if I set UTC+05:30, it stores either 11 hours behind time or 1 hour more, this happens randomly in random rows. I haven't changed anything else, just this timezone parameter.

I searched a lot but didn't find any similar answer.


texts: ORA-01950 error during the execution of insert statement

texts:
SQL> insert into DEPT values(101, 'RAMESH');
insert into DEPT values(101, 'RAMESH')
        *

  ERROR at line 1:

  ORA-01950: no privileges on tablespace 'USERS'

I cant sort the above problem?


texts: Are there ways to understand which password entered when connecting to my server ?


texts: PostgreSQL - Design database to avoid frequent update on large dataset

texts:
We are trying to optimize out the performance on a database with these numbers and query requisites:

200-400k network segments identified by a unique ID
each network segment have a state with a limited number of dynamic attributes (i.e. an average speed). A single state of dynamic attributes could be stored in 8 bytes
the segment state will change every 3 minutes, H24, 7/7
Is it possible to query for the state of a group of segments (sometimes all segments) in a particular date range or just the actual situation.
It could be requested a spatial query to find all segments "around me" in a particular date (normally "at now")

With these requisites, we arrived at this solution (with a strong drawback, explained below).

[A] TABLE main_segments_history(
      id_segment integer NOT NULL,
      day date NOT NULL,
      day_slices bigint[],
      CONSTRAINT main_segments_history_pk PRIMARY KEY (id_segment,day)
)

[B] TABLE current_segment_release_state(
      id_segment integer NOT NULL,
      release_date timestamptz,
      ... all other attributes ...
      CONSTRAINT currsegm_release_state_pk PRIMARY KEY (id_segment,release_date)
)

Explaining the [A] table:

It is partitioned on field "day" with partition_manager (pg_partman). Each partition is one month
The day_slices array is a one-dimensional array of 480 elements, representing the granularity of each 3-minute-slice of the full day

Explaining the [B] table:

It's just the current release state for each segment

There is a back-end process that elaborate the network.
It inserts or updates the states of each segment, every 3 minutes.
In other words this process will insert new rows on day start and will update the inner array every 3 minutes.

The advantages of this solution:

A limited number of rows for each month partition table
Good performance when joining the static data of the segment (i.e. the geometry)
The little redundancy of the current release is very good to respond at real-time requests
Space-safer: only ~12 GB of stored data for each partition (1 month) -

The drawbacks:

constraint_exclusion. When querying on date range there is a need of using that feature/parameter of PostgreSQL. That is using constant values in a precompiled query that spans over multiple partitions. Example:

constraint exclusion OK (will only search on February and March 2017):

SELECT * FROM main_segments_history
         WHERE day BETWEEN '2017-01-01' AND '2017-02-03'

constraint exclusion KO (will search on all partition tables):

SELECT * FROM main_segments_history m
         JOIN sometable s ON s.id=m.id_segment
         WHERE day BETWEEN s.day_from AND s.day_to

The UPDATE is evil.

We turned OFF the autovacuum for performance issues, doing it with a batch every night.
Consider that in this way we have almost between 90M and 190M UPDATES each day, this is also the number of rows completely rewritten by postgreSQL (as you certainly know an UPDATE will flag the row deleted and then a NEW row is inserted) every day.

More, the UPDATE is a great time-consuming operation creating often a delay on the writes.

We first investigate at the possibility of using a LINK-TO-DATA design, using for example a table as a container of segments id with a data_id to a BIG TABLE of segment states, but we discarded when we just count the number of rows each month to handle with: ~2.880.000.000 with an amount of space of ~3GB a day. Not so good.

What do you think about? Do you have any solution to optimize this system?


texts: AAD users lose access to database after import

texts:
I googled for the last few days but I got a bit confused and I do not know how to proceed further. Also, I'm not a DBA and I was just faced with this situation for the first time.
Using sqlpackage.exe I'm exporting a bacpac from a PRD DB and import it on another server to refresh the lower environments. The original database is contained. After I'm importing the new database, the contained database users and the AAD external ones lose access to the new database.
I'm fixing the contained database user by updating its password with
ALTER USER [RandomUser] WITH PASSWORD='randomPassword'

but I am not sure what to do with the AAD users. They have an external user created for them (the aad users are placed inside a group). So far my option is to drop the said user and recreate it
CREATE USER [sthRandom] FROM EXTERNAL PROVIDER

but in order to do this, you have to be connected with an AAD user
Principal 'sthRandom' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

I have to automate this whole process so I could use a service principal like this. I'm creating the service principal inside all the databases, but after refresh the user won't exist anymore, or I create it in the PRD database too, but I can not use it to login anymore.
What other options do I have? Thanks


texts: Where does Attunity's CDC services write error data?

texts:
We use Attunity's CDC services to move data from Oracle to SQL Server.

Using the Oracle CDC Designer snap-in tool, I can see the current status of the service and an error message if a problem has occurred.  I can click 'Collect Diagnostics' in the tool to gather messages for a date range and write to a file.

I want to find out where the diagnostic information is stored so I can read it directly through powershell from a monitoring server.

I have looked through the event logs and I can see some status messages but the diagnostic report has much more data.


texts: MySQL join two large tables is very slow

texts:
I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url.

The following query groups the URLs by the number of repetitions during the past hour.

SELECT COUNT(history.url) as total, history.url
FROM history
WHERE history.time > UNIX_TIMESTAMP()-3600
GROUP BY history.url
ORDER BY COUNT(history.url) DESC
LIMIT 30

The query above takes about 800ms to execute, not fast enough, but acceptable,

however, when joining it with the cache table, the new query takes about 25s to execute, which is very slow.

SELECT th.total, th.url, tc.url, tc.json
FROM (SELECT COUNT(history.url) as total, history.url
      FROM history
      WHERE history.time > UNIX_TIMESTAMP()-3600
      GROUP BY history.url
      ORDER BY COUNT(history.url) DESC
      LIMIT 30
) th
INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
    ON th.url = tc.url
GROUP BY th.url
ORDER BY th.total DESC
LIMIT 30

I think that this might be happening because in 'tc', the whole cache table is being loaded, and it contains 1million+ entries.

When I use the first query, and then programmatically iterate the results and then run a SELECT query from cache for each result, it is much faster. Is there anyway to speed up my second query?

P.S. I'm using InnoDB

UPDATE
The output of the second query with EXPLAIN

The structure of 'history' table

The structure of 'cache' table


texts: MS Access: Issue with Autonumbers in the export

texts:
I have a question that might be trivial to solve, but I have spent some time on it and didn't manage to solve it.

I am working on a database in MS Access, in which I keep track of the specifications of mechanical parts such as valves, pipes, and instruments. These components are grouped in tables, each one containing a class of components.
The single components can be described by parameters such as diameter, media used, and other fields. These values are fixed (e.g. the media will always be water, air, hot gas etc.), so they are stored in auxiliary tables, that are called once the user is filling the form using a combo box.

So far, everything good: the user sees the media in the combo box and I can select the same combo box from the view in the database table.
Once I export the file in Excel, I have a problem: the file is not reporting the "Description" field in the Auxiliary table - such as "water" - but its Autonumber.
How can I solve the problem? I would like to have an exported file that is showing the information displayed in the auxiliary table, and not the AutoNumber connected to it.
I have tried to solve the issue in this way:

The existing Relationship was between the Autonumber, as Primary key, and the field in the table, as foreign key. I deleted the relationship, set the "description" field as Primary key and made a new Relationship between the description in the auxiliary table and the foreign key.
The problem in this case is that the new fields are stored correctly, but the old fields remain as numbers. I wouldn't like to reinsert manually all the values, if it is possible to avoid it.

How can I convert all the information that is currently stored as number in its correspondant value in the auxiliary table?

Thank you very much in advance!


texts: I am relatively new to Access and have a question. I am creating a database for an assisted living community, regarding Dietary Restrictions. My question is: I would like to create a front end user form to add "New Menu Items". I have all tables normalized, but the issue is some "New Menu Items" may have zero restrictions, and some may have many. How do I do this?


texts: Index for a Postgres query with a sort and an equality

texts:
This query is very slow:

EXPLAIN (ANALYZE, buffers) SELECT *
FROM
    "Follows" AS "Follow"
INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id"
WHERE
    "Follow"."followerId" = 169368
ORDER BY
    "Follow"."createdAt" DESC
LIMIT 1000;

Here is the explain:

Limit  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.776..239.289 rows=262 loops=1)
  Buffers: shared hit=750 read=673 dirtied=1
  ->  Nested Loop  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.774..239.148 rows=262 loops=1)
        Buffers: shared hit=750 read=673 dirtied=1
        ->  Index Scan using follows_followinglist_followerid_createdat_idx on "Follows" "Follow"  (cost=0.43..681.88 rows=170 width=41) (actual time=0.377..52.687 rows=262 loops=1)
              Index Cond: ("followerId" = 169368)
              Buffers: shared hit=149 read=115
        ->  Index Scan using "Users_pkey" on "Users" followee  (cost=0.43..8.45 rows=1 width=773) (actual time=0.559..0.709 rows=1 loops=262)
              Index Cond: (id = "Follow"."followeeId")
              Buffers: shared hit=601 read=558 dirtied=1
Total runtime: 239.545 ms

I have the following indexes (currently, it is using the first one):

CREATE INDEX follows_followinglist_followerid_createdat_idx ON "Follows"
  ("followerId", "createdAt" DESC)
CREATE INDEX follows_followinglist_followerid_createdat_idx2 ON "Follows"
  ("createdAt" DESC, "followerId")
CREATE INDEX follows_followerId_fk_index ON "Follows" ("followerId");
CREATE INDEX  "follows_createdat_index" ON "public"."Follows" USING btree("createdAt" DESC);

Before I added the first two, the cost was slightly lower (~2000) using follows_followerId_fk_index, but still too slow.

I'm wondering if there's a way to optimize this further.


texts: "Log file for database is full" running an INSERT statement

texts:
I'm getting the following error message

  The log file for database 'scratchdb' is full. Back up the transaction log for the     database to free up some log space.

when I'm trying to insert new records into my temp table (there is ~1.3 mil rows) to check that it does not exist.

INSERT INTO SCRATCHDB..members_temp
SELECT M.*
FROM SCRATCHDB..members_temp D (NOLOCK) RIGHT OUTER JOIN
(SELECT * FROM PROD..Member (NOLOCK)
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()) AS M
ON M.MemberNo <> D.MemberNo

Is there a better/efficient way to do this?