texts: How to "cascade delete" children without deleting parent?

texts:
I have a table, let's call it parent:

CREATE TABLE parent (
    id          TEXT PRIMARY KEY,
    name        TEXT,
    description TEXT,
    -- other parent attributes
    created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

It has a number of child tables, let's say child_1, child_2, ... child_n. They all look something like:

CREATE TABLE child_n (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parent_id TEXT NOT NULL REFERENCES parent (id) ON DELETE CASCADE,
    -- other columns of child_n attributes
);

Some of these children may even have child tables of their own.

When I update a row in parent, I often want to also replace all of its descendants (ie: its children, their children, and so on recursively), as they are only relevant to the older version of that parent row.

My plan was to rely on that REFERENCES parent (id) ON DELETE CASCADE, but that only works if I actually delete the parent. I know I can delete and insert in a transaction, but this makes preserving the values of columns (like created_at) more awkward.

Is there a way to trigger the cascading delete of children without actually deleting the parent row?


texts: Set a check constraint for a set of conditions which aren't universally true

texts:
I need to make sure my database is self-consistent. There are two columns, which share a relationship when there is some specific value the other should be not null. I tried using constraint check (status = 2 and PatientId is not null), but it returns

SQL Error [4025] [23000]: (conn=20) CONSTRAINT `pId_status` failed for `prod`.`sch`

since I need an specific check for every value of status and some of them aren't neither for null or the PatientId column. I'm using mariadb and I'm aware that checks are not ignored as mysql.

Other status values may or may not accept patientid to be null, so this check should first make sure that status is 2 and then if patientid is not null. Status is not nullable. The above is just the simplest check that should conform business logic, since that status always have a patientid.


texts: Effect of over allocating shared_buffers

texts:
I have a 9.6 PostgreSQL RDS instance that I am tending to, but did not initially configure, and have been struggling with two different symptoms:

the system (a db.r3.2xlarge w/ 61GB memory) would experience "cliffs" in freeable memory, dropping 20-30GB almost instantaneously, followed by -
an auto-recovery event after some amount of time (could be minutes, could be hours).

On investigation, I found shared_buffers set abnormally high, actually exceeding physical memory on the machine. I have reset to a more sane level, but would like some more insight into the expected behavior.

The workload is modest - about a dozen or so concurrent users, manually triggering some large queries (e.g., aggregates on 300+ million row tables). My guess is that those queries trigger the cliffs. My question is about the after effects:

Would over-allocating shared_buffers explain the stability problems? E.g., PostgreSQL tries to allocate more buffer than the OS has left, the OS says "nope" and things go south from there.
Is shared_buffer memory ever released back to the OS? For example, I would frequently see a couple idle backends consuming 20-30GB, terminate them, but never see a rise in freeable memory.


texts: MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

texts:
I've set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I've reloaded all database tables and it still doesn't search for one character words.

My table:

CREATE TABLE IF NOT EXISTS event (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description TEXT,
time TIMESTAMP DEFAULT 0,
FULLTEXT(name)
);

My query:

SELECT event.id AS eid,
    event.name AS ename
FROM event
WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE)

And event name I'm searching for is W.W.W. + Pavel Fajt + Vladimir 518 @ Sono

What else could cause the problem please?

EDIT: I'm using InnoDB, sorry I haven't mention it before.


texts: Always On Failover Clustering vs Always On Availability Groups

texts:
I have some concerns over my understanding of SQL Server Always On. Kindly can you correct me (conceptually) where needed:

Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?
To create an Always On cluster - we have to launch the installer and choose the "New SQL Server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node". The cluster (with many node) operates as a single unit in terms of IP address so applications point to only one IP and if there is instance crash, then the failover is handled by the Clustering technology and no application IP changes are needed. Also, the users/logins are in sync and will continue to work in case of failover. This does not protect against disk failure as all nodes share same disk.
Assuming we have an SQL Server instance already (say server A), then to create Always On Clustering for this we need to follow the same steps as above (create a new failover cluster installation) and then add Server A as a node. Right?
Assuming we have a fresh SQL Server instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node. Then right click on the SQL Server service and enable "Always on Availability Groups". Then on the server instance we create the AG and configure databases into the group(s). This ensures availability at database level, however if there is failover then logins wont work any more (orphaned). There is no shared disk and hence disk crash of primary server wont cause any issues for the secondary server database. Also, the client applications will be pointing to the Listener IP and the Listener will ensure that the appropriate working server is utilized by the applications. Right?
In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.
If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?
What is difference between "SQL Server failover cluster installation" and Windows clustering?


texts: What is the difference between `ORDER BY` and `PARTITION BY` arguments in the `OVER` clause?

texts:
These queries below both give me exactly the same results, which I assume is because of my dataset rather than how the arguments work.

When using an OVER clause, what is the difference between ORDER BY and PARTITION BY.

On a slightly different note, why not use the term GROUP BY instead of the more complicated sounding PARTITION BY, since it seems that using partitioning in this case seems to achieve the same thing as grouping. I know you can alter these inner partitions and that those changes then reflect in the table. Is that the reason?

Query 1:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(ORDER BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

Query 2:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear


texts: How can I know whether postgres is currently running synchronous or asynchronous replication?

texts:
I see from ps that:

postgres: wal sender process replicator X.X.X.X(38681) streaming 0/17AF5438

But is there any way for me to know whether it is synchronous or asynchronous? I made the relevant changes in postgresql.conf to switch from synchronous to asynchronous and restarted the postgres service, but I wanted to double check that the changes were parsed correctly and I am in fact running asynchronously.


texts: What is the Cassandra setting for coordinator timeouts for schema change queries?

texts: In my testing, I've found that the write_request_timeout_in_ms setting does not have any bearing on the timeouts for CREATE TABLE or ALTER TABLE statements in CQL. For instance, I have it set to 10000ms but I've found that some CREATE TABLE statements can take around 12 seconds to complete. So what's the correct setting?


texts:
First I executed the following query:

 BEGIN TRANSACTION
        UPDATE MOVIES
        SET Rating = '777'
        WHERE Title IN
        (SELECT TOP 1 Title
           FROM MOVIES
           ORDER BY Title DESC);

Then the simple query to see the change I made to the table:

SELECT * FROM MOVIES;

There are only 18 rows and 3 columns but it's been executing for 15 minutes now. Before that I cancelled and tried executing again but it just won't execute.


texts: Is there a way to get the cardinality between tables, from a MySQL database?

texts:
I am working on a project where I have to analyze a MySQL database. I can find every relationship between tables in information_schema.tables, but I need to get the cardinality from each relationship.

  In the relational model, tables can be related as any of "one-to-many"
  or "many-to-many". This is said to be the cardinality of a given table
  in relation to another.

Is there a way to get that information?


texts: Replication from Amazon's RDS to Postgres via DMS errors out

texts:
I have a transactional replication from SQL Server 2008 to RDS SQL Server 2016 in 2014 compatibility mode. This works okay. I now want to replicate this RDS database via DMS to Postgresql. This errors prematurely, the Cloudwatch entries:

  Database instance is not enabled for REPLICATION: Applying
  enablement... (sqlserver_log_queries.c:2752) 2018-08-02T13:09:29
  [SOURCE_CAPTURE ]E: Failed to execute statement: '' [1022502]
  (ar_odbc_stmt.c:4422) 2018-08-02T13:09:29 [SOURCE_CAPTURE ]E:
  RetCode: SQL_ERROR SqlState: 42000 NativeError: 21089 Message:
  [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Only members of
  the sysadmin fixed server role can perform this operation. Line: 1
  Column: -1 [1022502] (ar_odbc_stmt.c:4428)

The user used for replication has all the possible rights on the database and RDSinstance. Any idea What to try next?


texts: how to set a column to fetch the date now?

texts:
hi i have a sample table named datenow and used the following to create my table on flamerobin to have a column that gets the date today automatically when an entry is added.

CREATE TABLE "DATENOW"
(
  ID Char(8) NOT NULL,
  "Start_Date" Char(10),
  "Client_Name" Varchar(30),
  BALANCE Integer,
  select cast('Now' as date) from rdb$database
  CONSTRAINT PK_datenow PRIMARY KEY (ID)
);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON "datenow" TO  SYSDBA WITH GRANT OPTION;

also tried inserting these sql statement

select date 'Now' from rdb$database
select time 'now' from rdb$database
select timestamp 'NOW' from rdb$database

but i cannot generate a table and cant determine where will i put the sql statement or what to do in order to have a column that gets the date today automat


texts: how to query nested json objects in mongodb?

texts:
{
    "_id" : ObjectId("58c7da2adaa8d031ea699fff"),
    "title" : "sdsddfasssdsdfsdffdf",
    "viewData" : [
        {
            "_id" : ObjectId("58c7f9d03ef6c93ce964d19a"),
            "widgetData" : {
                "pageIndex" : 5,
                "pageLength" : 5,
                "fetch" : 239,
                "granularity" : "day",
                "threshold" : [
                    {
                        "temperature" : {
                            "between" : {
                                "critical" : "40",
                                "warning" : "10"
                            },
                            "notification" : "Email"
                        }
                    },
                    {
                        "dewPoint" : {
                            "between" : {
                                "critical" : "40",
                                "warning" : "10"
                            },
                            "notification" : "Email"
                        }
                    },
                    {
                        "visibility" : {
                            "between" : {
                                "critical" : "40",
                                "warning" : "10"
                            },
                            "notification" : "Email"
                        }
                    }
                ],
                "measures" : [
                    "visibility",
                    "dewPoint",
                    "temperature"
                ],
                "dimensions" : [
                    "country",
                    "name",
                    "lat",
                    "lon"
                ],
                "hierarchies" : {
                    "country" : {
                        "lon" : [
                            "-3.056",
                            "-0.502",
                            "-2.1955",
                            "-2.6"
                        ],
                        "lat" : [
                            "53.497",
                            "53.031",
                            "49.44",
                            "49.2079"
                        ],
                        "name" : [
                            "CROSBY",
                            "CRANWELL",
                            "JERSEY",
                            "GUERNSEY"
                        ],
                        "country" : [
                            "ENGLAND",
                            "CHANNEL ISLANDS"
                        ]
                    }
                },
                "aggregation" : {
                    "temperature" : "MIN",
                    "dewPoint" : "MAX",
                    "visibility" : "AVG"
                },
                "widget" : {
                    "mode" : "absolute value",
                    "widgetType" : "MonitorWidget",
                    "title" : "England",
                    "description" : ""
                },
                "pinned" : true,
                "DataSource" : "MetadataDataSource",
                "mid" : "589414f51163d131285563e2"
            }
        }

how to find widget title value?

i tried

db.getCollection('mobiledashboards').find({"_id": "58c7da2adaa8d031ea699fff" },{ viewData: { $elemMatch : { "widgetData.widget.title": "England" } } })

but not working, how to solve this one.


texts: Import from Access .accdb file to SQL Server

texts:
How to import data from Access .accdb file to SQL Server? What should I choose in SQL Server Import and Export Wizard?

Suppose we choose OLE DB Provider for SQL Server Integration Services, then if that is a correct direction, what should be choose next from the following window? What to type in fields Data Source and Location, which mean more or less the same?

I followed instructions described here:
Easiest way to import access db (.accdb) into SQL Server 2016?
I have installed Microsoft Access Database Engine 2010 Redistributable

However I was hopeless in seeing Microsoft Office 12.0 Access Database Engine OLEDB

Update

If I choose Data Source as Microsoft Access (Microsoft Access Database Engine) I end up getting error message the one shown on picture below. Moreover I think that this data source is tailored for files mdb, not accdb.


texts: Merge two rows letting the second one override the first in PostgreSQL

texts:
Say that you want to build an append only table in Postgres, where each modification is actually just a new row added to the table. Say that you have the below simple, and disregard all questions about performance, table of users.

create table user_revisions (id uuid, name text, password text, version bigserial);
insert into user_revisions (id, name, password)
values ('743ccdf0-e9d8-4268-b6d7-0645eb70feb9', 'Bengan', 'nthusaeo');
insert into user_revisions (id, name, password)
values ('743ccdf0-e9d8-4268-b6d7-0645eb70feb9', 'Bengan', 'åäö');

Getting the latest version of a user would look something like:

select * from user_revisions e1
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9'
order by version desc limit 1;

But then you come to the part where you want to start modifying the users. Each request to the database always has the uuid of the user being modified and some subset of all the fields that are otherwise present in the user_revisions table.
You want to make an insert with the last revision of the user as a set of default values?  That is, do the equivalent of the Clojure code (merge a b) or Python {**a, **b}, where the two associative data structures a and b are merged and if there are any keys in a that also exist in b the values held in b will override those of a.

Below are some failed attempts at writing a select that produces the output that we in turn wants to insert as the latest revision.

with temp (password) as (values ('snthsnth'))
select * from user_revisions e1 natural left join temp
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9';

with temp (password) as (values ('snthsnth'))
select * from user_revisions e1 natural inner join temp
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9';

with temp (password) as (values ('snthsnth'))
select * from user_revisions e1 natural right join temp
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9';

The desired output would be:

 password  |                  id                  |  name
-----------+--------------------------------------+--------
 snthsnth  | 743ccdf0-e9d8-4268-b6d7-0645eb70feb9 | Bengan

This output would then be inserted into the table as the latest version of the user with the id 743ccdf0-e9d8-4268-b6d7-0645eb70feb9.


texts:
I want to see all the rows from one table and then do a count on the second table to see how many match, but want to see it all even if there are 0 matches in the second table.

My query:

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON a.area_id = b.i_area
WHERE YEAR(b.i_date) = YEAR(CURRENT_DATE()) AND MONTH(b.i_date) = MONTH(CURRENT_DATE())
GROUP BY a.area_name

Example desired output:

area 1 | 1
area 2 | 0
area 3 | 5

What I currently get is:

area 1 | 1
area 3 | 5

I tried CROSS and RIGHT join as well, just to make sure, but I don't get the desired effect.

What am I missing or doing wrong?


texts: postgres ERROR: target lists can have at most 1664 entries SQL state: 54000

texts:
In my query, I want to have more than 1700 columns but I got this error:

"ERROR: target lists can have at most 1664 entries SQL state: 54000"

I'm using postgres10 on Windows.

Has anyone come across this before? Any ideas for a solution?


texts: SQL Server database tries to restore itself automatically (and gets stuck)

texts:
I currently run into an issue, that a SQL Server Database has the state 'recovery pending' every morning.
No one has started a database recovery, the server itself decides to do it.
The recovery process gets stuck and the SQL Server stops working, as its memory limit is reached.
Temporarily, I can resolve it by restarting the MSSQLSERVER process and then taking the database offline and back online. However, as this seems to be happening every day now, this is an unacceptable workaround.
I am eager to know:

What are the possible reasons, that a SQL Server automatically starts such an automated restore process
Are there any Logs / Events I can look for, to find the reason.
How can I solve it :)

Any help or hint will be highly appreciated.

Update: Database was in recovery pending, not in restore pending . The SSMS is installed in german language and didn't show me the english status.


texts: Mysql replication trouble with event_scheduler

texts:
I'm running replication Master-Slave in MySQL 5.5. One day, my replication is broken. The show slave status given me 1032 error:

  Last_SQL_Error: Could not execute Update_rows event on table
  db.banner_optimize; Can't find record in
  'tvc_banner_kpi_optimize', Error_code: 1032; handler error
  HA_ERR_END_OF_FILE; the event's master log mysql-bin.004055,
  end_log_pos 20805222

I did some google, and found some solutions pointed that disable event_scheduler in slave can solve this error.

In slave, event_scheduler was disabled:

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| DISABLED          |
+-------------------+
1 row in set (0.00 sec)

I stop the slave, set global sql_slave_skip_counter = 1, then start slave. The Replication is running again but the delay time is very big, about 10 000 seconds and keep increasing.

For a period of time, the 1032 error still occur. Can anyone tell me what is really causes this problem?

PS: In master, event_scheduler is ON.


texts: Snapshot Backups for SQL Server Databases

texts:
We are researching the direction of using Snapshot backups for SQL Server, rather than more traditional backup methods. We are using CommVault (and would be for snapshot too..)

I am trying to understand, better, how they work compared to traditional backups, or agent backups using CommVault. As I am not really a storage guy, I don't quite understand how the restore processes work from a snapshot backup for a SQL Database.

I do understand the absolute speed benefit, but would like to hear more about the pro's and con's of utilizing snapshot backups for SQL Server.


texts: How to Get the Second Sequence of Characters using Regular Expressions

texts:
I have a field with an address in it (ex. 68 TIDAL BREEZE DR) and this regular expression gets everything before the last sequence (ex. 68 TIDAL BREEZE):

substring (address from '(.*) ')

My question is, how do I modify this expression to get everything after the first sequence (ex. 68) and everything before the last sequence (ex. DR) like so: TIDAL BREEZE?

I'm using PostgreSQL 9.5.


texts: Methods for tracking processing time for long running ADD INDEX call in MySQL

texts:
I've set off index creation on a very large table in MySQL and while I expected it to take a long time, I'm 5 days in and wondering if there's any way to debug potential issues or simply let it run.  I don't have a precise row count but to estimate, it's in the 100s of billions of rows and the table is ~400GB on disk.  Neither memory or CPU usage appears to be overly taxed (mem ~8GB (out of 16GB total)).

The call I made from within MySQL is as follows:

alter table prices add index(dataDate, ticker, expDate, type), add index(s
ymbol), algorithm=inplace, lock=none;

Running show processlist from within a different MySQL instance shows the call with State 'altering table' so the call doesn't appear blocked.  Anything else I can check to gauge progress?

For reference I'm working with MySQL 8 and within Ubuntu 18.04.

Edit:  Indexes eventually completed, took a little over 9 days.


texts: Does MariaDB support native JSON column data type?

texts:
I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version?

CREATE TABLE example (names JSON);

As far as I know, it doesn’t but I’m still not sure since there’s a lot of topics talking about JSON support on MariaDB for ages, but none said it’s finally implemented.

-- Update --

Just found three issues still open on MariaDB Jira about JSON data type support, which means it's still not yet implemented, right?

https://jira.mariadb.org/browse/MDEV-9056
https://jira.mariadb.org/browse/MDEV-9144
https://jira.mariadb.org/browse/MDEV-6632


texts: Delete from table where not in another table using two columns

texts:
I am trying to get my head around this delete statement I am trying to accomplish.  I understand how to delete from table where column not in ( subquery to table2)

I have two tables with multiple columns, and each table has two columns making up the primary keys. I think you have to do a join maybe?  I would like something in the lines of the following:

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1_PK1 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

I am trying remove rows from tbl1.  Does this look close to correct?

Edit to add:

tbl1
PK1, PK2, Col1

tbl2
PK1,PK2, DateStr

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1.PK1,tbl1.PK2 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

What is left after running sqlfiddle should be:

tbl1
('T', '222','asdf')

tbl2
('T', '222','9/26/2014')


texts: capability and performance of tags implemented as array vs text vs full text

texts:
In terms of search capability and performance, what are the pros & cons of implementing tags as an array or text or full text field?

I am seeing limitations of capability in choosing to implement tags with an array, because it seems to me that if an array contains ['juicy fruit'] then someone searching for 'fruit' will not find that record (for example, tags && ARRAY['fruit'] will not find it). The person who created that record would have had to enter something more like ['juicy fruit', 'juicy', 'fruit'] to have their record found with just a search for 'fruit' or 'juicy'. Whereas if I implement tags as text, then a search for 'fruit' will find 'juicy fruit', and further still, if I implement tags as full text then I will find 'juicy fruit' when searching with the string 'fruits' (plural). Furthermore, I think there will be no performance loss in going for full text search. Thoughts?

But maybe the whole point of tags is exact match.


texts: MariaDB 10.0.36: Table './mysql/servers.MYI' is read only

texts:
Trying to upgrade MariaDB using Generic Linux package from https://downloads.mariadb.org/mariadb/10.0/#os_group=linux_generic on a Debian 8 server:

kernel version 2.6.32-49-pve
OpenVZ/Virtuozzo container

Have no issue with MariaDB 10.0.35. The SQL server starts, no issue found. Hence it can not be an issue with files/folders permissions. None of master/slave replication is enabled.

But MariaDB 10.0.36 does not start due to the following errors:

180909 17:04:07 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180909 17:04:07 [ERROR] Can't open and lock privilege tables: Table './mysql/servers.MYI' is read only
180909 17:04:07 [Note] Server socket created on IP: '127.0.0.1'.
180909 17:04:07 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/host.MYI' is read only
180909 17:04:07 mysqld_safe mysqld from pid file /usr/local/mysql/data/mysqld.pid ended

That's weird, if I roll binaries back to MariaDB 10.0.35 and starts fine again.

The server have selinux disabled, and AppArmor does not seem to be enabled.

root@hosting:/etc/apparmor.d# service apparmor status
● apparmor.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
root@hosting:/etc/apparmor.d#

A fresh installed MariaDB 5.5.x fails the same way even after:

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/

it installs all the tables and fails to start afterwards.

So we can't start any other MariaDB version, but 10.0.35.

What else can it be? What do I miss?

Regards,
Alex.


texts: Are regular VACUUM ANALYZE still recommended under 9.1?

texts:
I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?

If the answer is "it depends", then:

I have a largish database (30 GiB compressed dump size, 200 GiB data directory)
I do ETL into the database, importing close to 3 million rows per week
The tables with the most frequent changes are all inherited from a master table, with no data in the master table (data is partitioned by week)
I create hourly rollups, and from there, daily, weekly and monthly reports

I'm asking because the scheduled VACUUM ANALYZE is impacting my reporting. It runs for more than 5 hours, and I've had to kill it twice this week, because it was impacting regular database imports. check_postgres doesn't report any significant bloat on the database, so that's not really an issue.

From the docs, autovacuum should take care of transaction ID wrap around as well. The question stands: do I still need a VACUUM ANALYZE?


texts: Oracle ram usage higher than memory_max_target

texts:
To begin, while I've worked with Oracle for many years, I'm far from a DBA.  (and it'll take probably a few weeks before I can get one assigned to look at this.)

I've got an Oracle instance that is consuming way more ram then is set with the memory_target and memory_max_target parameters.

SQL> show parameter memory_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
memory_target                        big integer 8000M

SQL> show parameter memory_max_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
memory_max_target                    big integer 9024M

However Oracle is taking up about 11GB of ram at the moment.

AMM is turned on

SQL> show parameter pga_aggregate_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
pga_aggregate_target                 big integer 0

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
sga_target                           big integer 0

But oracle is still being a memory hog

When I reduced memory_target and memory_max_target to the current values (down by 2GB each) the only thing that changed was the primary ORCL instance reduced it's memory footprint, but the RCAT instance took up the available freed ram.

The machine has been rebooted with no change in memory consumption.

I'm assuming RCAT here is the recovery catalog, but what would cause it to consume higher memory than is assigned via the memory target params?

Is there another area I should be looking at? (i.e. is memory for the recovery catalog instance controlled by another parameter?)

What additional information would be helpful in narrowing down the issue?

Windows 2012R2
16GB assigned ram
Oracle 18C
Approx 130 sessions with very little activity (basically idle)


texts: How does PolyBase collect statistics from external tables?

texts:
Books online has this to say about query optimisation in PolyBase:

  The query optimizer makes a cost-based decision to push computation to Hadoop when doing so will improve query performance. It uses statistics on external tables to make the cost-based decision. Pushing computation creates MapReduce jobs and leverages Hadoop's distributed computational resources.

My question - how and when does the optimizer collect these statistics?

Inside the SQL Server relational engine statistics can be created implicitly or explicitly, they age as writes occur, and can be refreshed explicitly or during table maintenance. Are there similar strategies employed in PolyBase?

Given the data may be loaded into Hadoop/ Azure blob without SQL Server seeing it on the way in, and data volumes will be large (most likely) run-time ad hoc statistic creation through sampling or similar seems an unlikely strategy to me.


texts: Way to find when system health file is rolling over

texts:
Is there a way to find the events when system health extended event files are rolling over rather than manually monitoring for the events?

For my medium load server they stay upto 2-3 days. But for heavy loaded servers these files are rolling every 15 mins or so but no fixed pattern or timings. We know the reason why and working to filter out unwanted events or ones reported as issues.

I am curious if there is a way we can query at what time would the roll-over of files is happening. I do not see much documentation on MS docs as well but cant find this info?

Please suggest if its possible and how?


texts: Necessary to use a checksum on a backup and not solely the 'verify backup' option?

texts: If want to ensure the integrity of a backup, would it be a necessity to use a checksum on all of my backups and not just the 'verify backup' option?


texts: query processor ran out of internal resources and could not produce a query plan

texts: This is showing up in the logs several times a night.  How do I find the query causing the issue?  SQL Server 2008 R2 Sp1.


texts: SQL Server Reporting Services: Generate Scripts Programmatically

texts:
I'm looking to export SSRS reports from SSRS 2005 into SSRS 2017. I have around 100 reports to move but in total there are around 500 subscriptions across all of these reports.

The Script to file tool in SSMS 2005 would save a large amount of time however is there a way to programmatically call the Script to file function?

I know you can do something similar with Powershell to export databases.


texts: "ORA-00936: missing expression" setting up Oracle Publisher in SQL Server

texts:
I'm trying to set up an Oracle publisher in SQL Server so I can perform replication. I've gotten connected to Oracle, the drivers work great (I have other linked servers), I can select an article I want to publish, and then I can get to the very end of the "Add Oracle Publisher" wizard.

However, when I click "Finish" and it attempts to actually add the article, I get the following error from the remote server:

  OLE DB provider "OraOLEDB.ORACLE" for linked server "MYLINKEDSERVER"
  returned message "ORA-00936: missing expression ORA-06512: at
  "MYUSERNAME.HREPL", line 1722 ORA-06512: at line 1". Error: 7215, Sev:
  17, State: 1, Msg: Could not execute statement on remote server
  'MYLINKEDSERVER'.

I can't find this script anywhere (it seems like it's being generated by SQL Server and then executed) and I can't see what line 1722 actually contains, thought it's clear it contains a syntax error of some kind (at least from Oracle's perspective). Selecting a different table to publish gives me the exact same error on the exact same line. I can find a few posts online that reference this error (this is one of them), but they're all abandoned with no resolution.

I'm hopeful that somebody has set up successful Oracle -> SQL Server replication and can shed some light on the error I'm getting as I set it up.

Per a request in the comments, I scripted the steps and it fails on the final step of adding the actual article to the publication - this statement gives the error I listed above:

use [distribution]
exec sp_addarticle @publication = N'Orcl_NativeToSQL_Publication',
                   @article = N'TCUSTMER',
                   @publisher = N'SNR',
                   @source_owner = N'OGGATE',
                   @source_object = N'TCUSTMER',
                   @type = N'logbased',
                   @description = null,
                   @creation_script = null,
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000000000083,
                   @use_default_datatypes = 1,
                   @destination_table = N'TCUSTMER',
                   @destination_owner = N'dbo',
                   @vertical_partition = N'false',
                   @ins_cmd = N'CALL sp_MSins_OGGATETCUSTMER',
                   @del_cmd = N'CALL sp_MSdel_OGGATETCUSTMER',
                   @upd_cmd = N'SCALL sp_MSupd_OGGATETCUSTMER'
GO


texts:
I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows.

wal_level = archive
archive_mode = on
archive_command = 'copy "%p" "C:\\Users\\Suniel\\Desktop\\archive\\%f"'
archive_timeout = 3600 //for every hour
max_wal_senders = 1

Then, I took the base backup.

pg_basebackup -U postgres -D C:\Users\Suniel\Desktop\Postgresql_backup\pg_basebackup -F t -x

which gives the replication of existing data directory in the given location.

Then i created the recovery.conf file with following parameters.

recovery_target_time = '2016-07-05 04:00:00'
recovery_target_timeline = 'latest'
restore_command = 'copy "C:\\Users\\Suniel\\Desktop\\archive\\%f" "%p"'

I stopped the postgresql service and placed the recovery file under data directory of installed postgres location.

Then I started the postgres service which changes recovery.conf to recovery.done but when i view the tables records, i saw no changes.

But my log files seems like this with some error(i think).

2016-07-06 12:15:49 NPT LOG:  database system was shut down at 2016-07-06 12:15:08 NPT
2016-07-06 12:15:49 NPT LOG:  starting point-in-time recovery to 2016-07-05 04:00:00+05:45
2016-07-06 12:15:49 NPT LOG:  consistent recovery state reached at 0/19000098
2016-07-06 12:15:49 NPT LOG:  invalid record length at 0/19000098
2016-07-06 12:15:49 NPT LOG:  redo is not required
2016-07-06 12:15:49 NPT LOG:  selected new timeline ID: 19
2016-07-06 12:15:50 NPT LOG:  archive recovery complete
2016-07-06 12:15:50 NPT LOG:  MultiXact member wraparound protections are now enabled
2016-07-06 12:15:50 NPT LOG:  database system is ready to accept connections
2016-07-06 12:15:50 NPT LOG:  autovacuum launcher started

I must be wrong somewhere in these steps. What did i miss? Am i wrong in these procedures?

Thanks in advance.


texts: How to specify the primary key number space for a postgres table?

texts:
I have a postgres table that contains data where the id has been set by the code that injected the data. So lets say the id is 0..49.

How can I instruct postgres to start with 50 on the id column for the next insert?


texts: Do any databases other than SAP ASE support key custodians?

texts:
SAP Sybase ASE supports the notion of a key custodian. This allows for a setup where the trust can be split between two roles: the database administrator and a key custodian. The database administrator has the privileges to create user accounts and give them privileges on tables and accounts, whereas the key custodian only has the ability to create and assigns encryption keys to users. Hence neither of these roles by themselves can create a full set of credentials that would allow access the data stored in the database. This capability is wonderful in low trust environments.

I get the sense that SAP ASE is no longer a priority at SAP. This might be totally wrong, and is only based on the fact that I am predominantly seeing HANA being pushed these days. I am therefore looking for other database systems that have an equivalent capability.
So far I haven't been able to find any database where it isn't the admin that in one way or another has the task of creating encryption keys as well.

Are there other database servers with an equivalent functionality that I have missed? I would really appreciate any guidance and tips!

Thank you!


texts:
Our MySql production server has two different databases in it.
For reports I want the user to only be able to see reports database and not main database. I created A user  'username@%' for the reports plugin in the server and gave it vie,select,execute privileges on reports db.
 Now when the developer connects to database using those credentials from reports plugin, they report that they are able to see the other database and all it's tables as well.
What should I do to prevent them from seeing the other database. In short: when developer logs in from reporting plugin, they should only see the report database.


texts:
We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some queries are dropped because they cannot acquire a lock within the default 50 seconds limit (I realize we can raise this limit).

The table in question has only one index, the primary key itself which is a mediumint value.
The table has about 1 million rows.
All UPDATE's in this context involve a single row. Usually 4-5 columns from that row are affected in every query.

Current my.cnf is pasted below. Do you see anything which could particularly cause a bad UPDATE performance for innodb?

[mysqld]
set-variable=local-infile=0

datadir=/db/mysql/data
socket=/var/lib/mysql/mysql.sock
#log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-locking
key_buffer = 1G
query_cache_size = 256M
thread_cache_size = 128
table_cache = 2048
max_connections = 400
query_cache_limit = 1024M
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
skip-bdb
skip-locking
skip-name-resolve

innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

UPDATE:
innodb_log_file_size: 5242880
have_innodb: YES
"WHERE" clause always looks only for one column, which is the primary key.

UPDATE - July 26, 2012:
We upgraded our database to mysql 5.5. Now innodb updates are pretty fast, less than 0.010 seconds in our specific case. And, variance is pretty low. My take from this is: innodb should be used with real caution on mysql 5.0.


texts:
Installed two node Oracle RAC. On node 1 opened pdb1 and shows open mode as read and write but on node 2 it's still in mounted state?.

On Node1

   rlwrap sqlplus / as sysdba

        SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 14 04:29:47 2019
        Version 19.3.0.0.0

        Copyright (c) 1982, 2019, Oracle.  All rights reserved.

        Connected to:
        Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
        Version 19.3.0.0.0

        SQL> show pdbs

            CON_ID CON_NAME                       OPEN MODE  RESTRICTED
        ---------- ------------------------------ ---------- ----------
                 2 PDB$SEED                       READ ONLY  NO
                 3 PDB1                           READ WRITE NO

        SQL> select CON_ID,DBID,NAME,OPEN_MODE,RESTRICTED from v$pdbs;

        CON_ID        DBID NAME       OPEN_MODE  RES
        ------ ----------- ---------- ---------- ---
             2  1968208496 PDB$SEED   READ ONLY  NO
             3  1212646563 PDB1       READ WRITE NO

SQL> select instance_number,instance_name,instance_mode from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    INSTANCE_MO
--------------- ---------------- -----------
              1 cdbrac1          REGULAR

On Node2

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

SQL> select CON_ID,DBID,NAME,OPEN_MODE,RESTRICTED from v$pdbs;

CON_ID            DBID NAME            OPEN_MODE  RES
------ --------------- --------------- ---------- ---
     2      1968208496 PDB$SEED        READ ONLY  NO
     3      1212646563 PDB1            MOUNTED

SQL>  select instance_number,instance_name,instance_mode from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    INSTANCE_MO
--------------- ---------------- -----------
              2 cdbrac2          REGULAR


texts: Is database record access sequential after retrieving the indexed page from disk?

texts:
This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer.

What I understand about databases that if a queried or requested record is found in the disk page, the whole page is loaded to main memory.

For example, if the content of table stored in disk page like this:

+-------+------------+----------+-----------+--------+
| rowid | EmployeeId | Lastname | Firstname | Salary |
+-------+------------+----------+-----------+--------+
|   001 |         10 | Smith    | Joe       |  40000 |
|   002 |         12 | Jones    | Mary      |  50000 |
|   003 |         11 | Johnson  | Cathy     |   4000 |
|   004 |         22 | Jones    | Bob       |  55000 |
+-------+------------+----------+-----------+--------+

Assume the indexing is done on EmployeeId.

After loading the page in main memory, how the record is searched? (sequential or some other method)

I want to know how this is carried out in Oracle and maybe some other such as Microsoft SQL Server.


texts: how to batch update remark records atomatic in multi instance applicatoin in MySQL 5.7

texts:
I want to update a remark with multi records in MySQL 5.7, in multi instance, I want the workflow like this:

update sync field from 0(ready for sync) to 2(syncthing),maybe multi rows(maybe multi instance query the records in the same time, so we should make the update and query atomatic)
execute sync logic in application
update sync field to 1(sync success)

if there is one record, I could using this sql to archive this atomatic in multi instance:
    set @update_id := 0;
    UPDATE r_room_seat SET status = #{record.status,jdbcType=INTEGER},
    id = (SELECT @update_id := id),
    user_id = #{record.userId,jdbcType=BIGINT},
    robot_flag = #{record.robotFlag,jdbcType=TINYINT}
    WHERE room_play_id = #{record.roomPlayId,jdbcType=BIGINT}
    and status = 0
    and online = 1
    LIMIT 1;
    select
    <include refid="Base_Column_List"/>
    from r_room_seat where id = @update_id;

what should I do with multi rows?


texts: Which method would be more secure and feasible in the long term?

texts:
The concern here is a confusion between two alternative methods for storing data. Our product migrates records from any service A to any service B. These services define their own records and the structure of this record is dependent on the service. We store the records that travel through our system so that we can perform "diffs" to verify updating only what's changed.

Customers of ours create these migrations between services in a logical "environment." So each customer can have any number of environments that they can then perform these migrations within the context of.

Our current storage method is that a table for each customer and environment is created dynamically and this table houses all records migrated in this environment (regardless of service). There is more complexity such as columns generated for all the fields and some Reflection in the language that is probably ancillary to this question. This process was chosen to ensure data integrity, such that no record from Cust A Env A would every be pushed into Cust A Env B and vice versa (and cross customer).

The competing design that has been proposed thus far is that we eliminate the need for dynamic tables and use one table for records and add necessary environment and customer keys to the record rows. This prevents a "dual database hit" scenario where we have to first look up table names, then query those tables as we could just simply query and perform a join - or maybe not even have to do that if we have the IDs already. The concern here is the possibility of data accidentally being applied to environments which it does not belong.

What are the pros and cons of both situations, and which will be more efficient (both space and growth wise) when the amount of data stored increases. I see an extremely large number of tables if we continue to spawn tables and each table would most likely hold a few 100,000 records (when relatively small) while one table would grow to severl million or 10's of millions of rows rather quickly which could affect performance of queries negatively where multiple tables may alleviate that concern.

Given that the question is (or was) on hold for being opinion based I would like to make it clear that I'm only interested in the factual pros and cons - not preferences or opinions for either angle.


texts: Dilemma of Generating tables dynamically to improve performance

texts:
Background: Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An
  interviewer goes to House 1 and interviews two members Member 1 and
  Member 2. He asks questions like. What is this house address?, What is
  your name? and What is your age?. The questions are created by
  supervisors or survey experts

The way the data is stored for this application is as given below

The questions are saved in a table like below
+------------+--------------+----------+
| QUESTIONID | QUESTIONTEXT | SURVEYID |
+------------+--------------+----------+
|            |              |          |
| 1          | Name         | 311      |
|            |              |          |
| 2          | Gender       | 311      |
|            |              |          |
| 3          | Age          | 311      |
+------------+--------------+----------+
Results table (It has all the submissions)
+----------+----------+----------------+
| RESULTID | SURVEYID | SUBMITTED DATE |
+----------+----------+----------------+
|          |          |                |
| 90       | 311      | 11/12/2017     |
|          |          |                |
| 91       | 311      | 11/13/2017     |
+----------+----------+----------------+
Chunks table (where all the answers are stored)
+---------+----------+------------+----------+
| CHUNKID | RESULTID | QUESTIONID |  ANSWER  |
+---------+----------+------------+----------+
|         |          |            |          |
| 1001    | 90       | 1          | John Doe |
|         |          |            |          |
| 1002    | 90       | 2          | M        |
|         |          |            |          |
| 1003    | 90       | 3          | 18       |
|         |          |            |          |
| 1004    | 91       | 1          | Jane Doe |
|         |          |            |          |
| 1005    | 91       | 2          | F        |
|         |          |            |          |
| 1006    | 91       | 3          | 26       |
+---------+----------+------------+----------+

And the result after some Joins and Pivots will be like

| RESULTID | SUBMITTED DATE |   NAME   | GENDER | AGE |
+----------+----------------+----------+--------+-----+
|          |                |          |        |     |
| 90       | 11/12/2017     | John Doe | M      | 18  |
|          |                |          |        |     |
| 91       | 11/13/2017     | Jane Doe | F      | 26  |
+----------+----------------+----------+--------+-----+

Now the problem arises when we have really big surveys ( Some of them may go as far as 500 questions ). Normally the submissions exceed 20k. With this huge data, it takes forever to reach the final result.

So the solution I have in my mind is to generate a table dynamically when a survey is created.
 like

Survey_311

+----------+----------------+----------+-----+-----+
| RESULTID | SUBMITTED DATE |   Q_1    | Q_2 | Q_3 |
+----------+----------------+----------+-----+-----+
|          |                |          |     |     |
| 90       | 11/12/2017     | John Doe | M   | 18  |
|          |                |          |     |     |
| 91       | 11/13/2017     | Jane Doe | F   | 26  |
+----------+----------------+----------+-----+-----+

And to insert rows each time an answer is submitted. But as I am not really a DBA and do not have much experience with Databases. I would like to know if this approach is a bad practice in this scenario, Any solutions other than paging that will improve the performance is also welcomed


texts:
I've got an issue with estimated cost and join predicate :

SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500
OPTION (RECOMPILE)

Table_A has 50k rows, Table_B is empty, Table_C has 2M rows
The PK and FK indexes exists and the stats are up to date.

But for some reason, SQL Server use a Clustred Index Scan on Table_C.
https://www.brentozar.com/pastetheplan/?id=r1G_LnpeU

This is a sample my problem, the prod tables are fare bigger and use too much estimated grant memory because of this issue.

Using a FORCESEEK or change the join to LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL resolve the issue but it's a query (mutliple queries in fact) generate by Entity Framework so I don't have so much control on it.

Is there a way to seek Table_C without changing the query here ?


texts: Performance issue with larger resultsets SQL Server

texts:
I currently have a stored procedure in SQL Server where I execute a SELECT statement multiple times based on the variables I pass to the stored procedure. The stored procedure counts how many records are going to be returned for every filter a user can enable. The stored procedure isn't the issue, I transformed the select statement from te stored procedure to a regular select statement which looks like:

DECLARE @contentRootId int = 900589
DECLARE @RealtorIdList varchar(2000) = ';880;884;1000;881;885;'
DECLARE @publishSoldOrRentedSinceDate int = 8
DECLARE @isForSale BIT= 1
DECLARE @isForRent BIT= 0
DECLARE @isResidential BIT= 1
--...(another 55 variables)...

--Table to be returned
DECLARE @resultTable TABLE
(
    variableName varchar(100),
    [value] varchar(200)
)

-- Create table based of inputvariable. Example: turns ';18;118;' to a table containing two ints 18 AND 118
DECLARE @RealtorIdTable table(RealtorId int)
INSERT INTO @RealtorIdTable SELECT * FROM dbo.Split(@RealtorIdList,';') option (maxrecursion 150)

INSERT INTO @resultTable ([value], variableName)
SELECT [Value], VariableName FROM(
    Select count(*) as TotalCount,
        ISNULL(SUM(CASE WHEN reps.ForRecreation = 1 THEN 1 else 0 end), 0) as ForRecreation,
        ISNULL(SUM(CASE WHEN reps.IsQualifiedForSeniors = 1 THEN 1 else 0 end), 0) as IsQualifiedForSeniors,
        --...(A whole bunch more SUM(CASE)...
    FROM TABLE1 reps
    LEFT JOIN temp t on
            t.ContentRootID = @contentRootId
            AND t.RealEstatePropertyID = reps.ID
        WHERE
            (EXISTS(select 1 from @RealtorIdTable where RealtorId = reps.RealtorID))
            AND (@SelectedGroupIds IS NULL OR EXISTS(select 1 from @SelectedGroupIdtable where GroupId = t.RealEstatePropertyGroupID))
            AND (ISNULL(reps.IsForSale,0) = ISNULL(@isForSale,0))
            AND (ISNULL(reps.IsForRent, 0) = ISNULL(@isForRent,0))
            AND (ISNULL(reps.IsResidential, 0) = ISNULL(@isResidential,0))
            AND (ISNULL(reps.IsCommercial, 0) = ISNULL(@isCommercial,0))
            AND (ISNULL(reps.IsInvestment, 0) = ISNULL(@isInvestment,0))
            AND (ISNULL(reps.IsAgricultural, 0) = ISNULL(@isAgricultural,0))
            --...(Around 50 more of these WHERE-statements)...
            ) as tbl

    UNPIVOT (
        [Value]
        FOR [VariableName] IN(
        [TotalCount],
        [ForRecreation],
        [IsQualifiedForSeniors],
        --...(All the other things i selected in above query)...
        )
    ) as d

    select * from @resultTable

The combination of a RealtorId and ContentRootID gives me a set default set of X amount of records. When I choose a combination which gives me ~4600 records, the execution time is around 250ms. When I execute the sattement with a combination that gives me ~600 record, the execution time is about 20ms.

I would like to know why this is happening. I tried removing all SUM(CASE in the select, I tried removing almost everything from the WHERE clause, and I tried removing the JOIN. But I keep seeing the huge difference between the resultset of 4600 and 600.

EDIT: I also tried to use a temporary table for @resultTable instead of the table variable.

EDIT2: The execution plan of the one with many results: https://gist.githubusercontent.com/anonymous/8c91631b2ecf6e42d787583da20b2a7e/raw/f8097765814486b35e8e8ba90ea0a0611703690c/gistfile1.txt and with few results: https://gist.githubusercontent.com/anonymous/734cc06417204b1c2bf24d6eff075c67/raw/87ab011776d71659e27cdd58fc25c3414d6c5309/gistfile1.txt I don't think the plans differ too much


texts: Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher

texts:
In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication)

Yes, there are easier ways to query from Distributor but I need a query from Publisher side.

Remote Distributor model (if it helps)

Thanks,


texts: How to get rid of "maximum user connections" error ?

texts:
I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than 'max_user_connections' active connections.

I tried already to close all connections but this does not improve the situation.

Is there a way to know exactly what connections are open at any particular moment or any other useful data that can help me resolve this issue ?

BTW, I'm using PHP 5.2.17 and MySQL 5.1.


texts: Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

texts:
I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will only join to rows in B_2012-12-05, and rows in A_2012-12-06 will only join to rows in B_2012-12-06, etc. Is there a way to write an intelligent query so that MySQL won't go looking for rows in B_2012-12-06 to join with rows in A_2012-12-05?

Edit by RolandoMySQLDBA 11:17 EDT

Please insert the output of these into this section:

They all look pretty much like this:

CREATE TABLE `mrg_31_session` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phpsessionid` varchar(32) NOT NULL,
  `start_datetime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_phpsessionid` (`phpsessionid`),
  KEY `ix_startdatetime` (`start_datetime`),
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 UNION=(`session_20121205`,`session_20121204`,`session_20121203`,`session_20121202`,`session_20121201`,`session_20121130`,`session_20121129`,`session_20121128`,`session_20121127`,`session_20121126`,`session_20121125`,`session_20121124`,`session_20121123`,`session_20121122`,`session_20121121`,`session_20121120`,`session_20121119`,`session_20121118`,`session_20121117`,`session_20121116`,`session_20121115`,`session_20121114`,`session_20121113`,`session_20121112`,`session_20121111`,`session_20121110`,`session_20121109`,`session_20121108`,`session_20121107`,`session_20121106`,`session_20121105`)


texts: How to run analysis on a 16M row table faster

texts:
I have two tables students with 6k records and transactions with 16M records.

I have a query similar to this:

select section, count(s.students) from students as s
where upper(name) in
    (select upper(name) from transactions as t
        where (length(transaction_id) < 43 or
            lower(transaction_name) = 'NA' or
            lower(transaction_type) = 'food')
            and upper(name) not in ('JOHN DOE'))
group by section
order by section asc

The query plan for this is similar to below:

 GroupAggregate  (cost=2828274.40..2828291.32 rows=6 width=12) (actual time=101295.078..101301.568 rows=6 loops=1)
   Group Key: section
   ->  Sort  (cost=2828274.40..2828280.02 rows=2247 width=25) (actual time=101294.727..101300.274 rows=2414 loops=1)
         Sort Key: section
         Sort Method: quicksort  Memory: 285kB
         ->  Hash Join  (cost=2827697.15..2828149.32 rows=2247 width=25) (actual time=101278.618..101292.861 rows=2414 loops=1)
               Hash Cond: ((s.name)::text = (t.name)::text)
               ->  Seq Scan on students s  (cost=0.00..412.16 rows=5716 width=25) (actual time=0.007..2.887 rows=5720 loops=1)
               ->  Hash  (cost=2827669.07..2827669.07 rows=2247 width=21) (actual time=101278.578..101278.578 rows=2445 loops=1)
                     Buckets: 4096  Batches: 1  Memory Usage: 159kB
                     ->  HashAggregate  (cost=2827646.60..2827669.07 rows=2247 width=21) (actual time=101274.023..101276.849 rows=2445 loops=1)
                           Group Key: (t.name)::text
                           ->  Gather  (cost=1000.00..2804729.14 rows=9166982 width=21) (actual time=97.170..100735.029 rows=449343 loops=1)
                                 Workers Planned: 2
                                 Workers Launched: 2
                                 ->  Parallel Seq Scan on transactions t  (cost=0.00..1887030.94 rows=3819576 width=21) (actual time=130.428..100660.838 rows=149781 loops=3)
                                       Filter: (((name)::text <> 'JOHN DOE'::text) AND ((length((transaction_id)::text) < 23) OR (lower((transaction_type)::text) = 'food'::text)) OR OR (lower((transaction_name)::text) = 'NA'::text))))
                                       Rows Removed by Filter: 4897554
 Planning time: 10.027 ms
 Execution time: 101301.982 ms


texts: How to count boolean values in PostgreSQL?

texts:
How to count, how may true and false for the field public in postgresql user table
 i have tried this query

select
sum(case when false then 1 else 0 end) as false,
sum(case when true then 1 else 0 end) as true
from  public.user;

but am not getting any value and if i remove public from query then i will get correct counts only i have value true

table : name|   DOB     | public
values : bb | 20/2/1991/| true

op : true = 1 and false  = 0

but am getting the same answer when i make public as false

table : name|   DOB     | public
values : bb | 20/2/1991/| false

op : true = 1 and false  = 0

so someone please help me to solve this


texts: Always On Availability Groups on single VM

texts:
Is there any way to implement Always On Availability Groups using SQL Server 2019 Enterprise Edition on a Single Virtual Server.
The goal is not to have any HA or DR capability, but merely Read-Scale as a method to distribute read operations to a second replica.  The thought is to have two or more instances on the same virtual server, with each instance having a portion of the RAM assigned based on load experienced for reading vs. writing.  The memory for each SQL instance would be less cluttered, and less locking etc would occur, and performance would be expected to improve even with a single server.
Attempting to do this with two instances on the same server, SQL complains in the AG Creation Wizard that the name of the replica node has the same base server name included, even though there are two separate instances.  Essentially, it is interpreting the base server name of the instance name as the 'node' (even though the setup for read-scaling does not require WSFC node setup), and thus the replica is not allowed to be created on the same node.
Is there a way around this?
Thanks!
Script I tried without success when instances were on same server:
CREATE AVAILABILITY GROUP [TEST_AG1]
WITH (
 AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
 DB_FAILOVER = ON,
 DTC_SUPPORT = NONE,
 CLUSTER_TYPE = NONE
)
FOR DATABASE [Northwind]
REPLICA ON N'ROBSWS2019\SQLA' WITH (
 ENDPOINT_URL = N'TCP://ROBSWS2019.AUSTINLANE.local:5022',
 FAILOVER_MODE = MANUAL,
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
 BACKUP_PRIORITY = 50,
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
 N'ROBSWS2020\SQLB' WITH (
 ENDPOINT_URL = N'TCP://ROBSWS2020.AUSTINLANE.local:5022',
 FAILOVER_MODE = MANUAL,
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
 BACKUP_PRIORITY = 50,
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);

GO


texts:
I'm trying to use the following code to execute a DELETE statement on a linked server. I'm trying to delete all records from the remote server where there is a match on a certain ID field in both the table on the linked server and a local table. Any records showing up in the INNER JOIN should be deleted from the linked server table RemoteTable.

The following isn't allowing me to use an alias ("RemoteTable") for my remote table. Am I on the right track here with what I'm trying to accomplish? Thanks!

DELETE FROM OPENQUERY(LINKEDSERVER_MYSQL,'SELECT * from PrimaryInquiry_T2') AS RemoteTable
INNER JOIN Forms.dbo.PrimaryInquiry_TLOCAL AS LocalTable
ON LocalTable.PrimaryInquiry_TLOCAL_EntryID = RemoteTable.PrimaryInquiry_T2_EntryID


texts: Why Azure SQL Server table used space is too high?

texts:
I have two tables in my SQLServer database (azure managed), and the structure of two tables as below (for easy reference renamed them)
dbo.TABLE1
CREATE TABLE [dbo].[TABLE1] (
  [ID]             VARCHAR (50)  NOT NULL,  -- PK index
  [S_NUMBER]       VARCHAR (50)  NOT NULL,
  [MAKE]           VARCHAR (12)  NULL,
  [MODEL]          VARCHAR (40)  NULL,
  [C_ID]          VARCHAR (50)  NOT NULL,
  [C_CODE]         REAL          NULL,
  [C_DESCRIPTION]  VARCHAR (50)  NULL,
  [S_CODE]         REAL          NULL,
  [S_DESCRIPTION]  VARCHAR (50)  NULL,
  [LS_TIME]       DATETIME2 (7) NOT NULL,
  [LE_TIME]        DATETIME2 (7) NOT NULL,
  [YEAR]           DATE          NULL,
  [MONTH]          DATE          NULL,
  [WEEK]           DATE          NULL,
  [DAY]            DATE          NULL,
  [HOUR]           DATETIME2 (7) NULL,
  [WEEK_DAY]       VARCHAR (12)  NULL,
  [F_USED]         REAL          NULL,
  [F_USED_UOM]     VARCHAR (12)  NULL,
  [DIST_TRAVE]     REAL          NULL,
  [DIST_TRAV_UOM]  VARCHAR (12)  NULL,
  [DURATION]      REAL          NULL,
  [DURATION_UOM]   VARCHAR (12)  NULL,
  [WEIGHT_MOVED]   REAL          NULL,
  [WEIGHT_STORED]  REAL          NULL,
  [WEIGHT_UOM]     VARCHAR (12)  NULL,
  [CREATED_TIME]   DATETIME      NULL,
  [MODIFIED_TIME]  DATETIME      NULL,
  [OPER_ID]        VARCHAR (12)  NULL,
  [OPER_TYPE]      VARCHAR (12)  NULL,
  [START_TIME]     DATETIME2 (7) NULL,
  [END_TIME]       DATETIME2 (7) NULL,
  [UTC_DAY]        DATE          NULL,
  [UTC_WEEK]       DATE          NULL,
  [UTC_MONTH]      DATE          NULL,
  [UTC_YEAR]       SMALLINT      NULL,
  [UTC_HOUR]       TINYINT       NULL,
  [LOCAL_HOUR]     TINYINT       NULL,
  [EFFICIENCY]     DECIMAL (5)   NULL,
  [JS_ID]          BIGINT        NOT NULL,
  [L_ZONE]         NVARCHAR (50) NULL,
  [D_ZONE]         NVARCHAR (50) NULL,
  [SHIFT_TYPE]     NVARCHAR (12) NULL,
  [SHIFT_DATE]     DATE          NULL,
  [D_CATEGORY]     NVARCHAR (50) NULL,
  [REC_CATEGORY]   NVARCHAR (12) NULL,
  [LSERIAL_NUMBER] VARCHAR (50)  NULL,
  [GPS_TIME]       DATETIME      NULL,
  [GPS_STATUS]     VARCHAR (12)  NULL,
  [A_ID]           VARCHAR (50)  NULL,
  [E_ID]           VARCHAR (50)  NULL,
  [TAG_GUID]       VARCHAR (64)  NULL,
  [LZ_ID]          VARCHAR (64)  NULL,
  [DZ_ID]          VARCHAR (64)  NULL,
  [LZ_VERSION]     INT           NULL,
  [DZ_VERSION]     INT           NULL,
  [Z_ID]           BIGINT        NULL,
  [SHIFT_ID]       NVARCHAR (64) NULL,
  [LI_TIME]        DATETIME2 (3) CONSTRAINT [S_CREATED_TIMESTAMP] DEFAULT (sysdatetime()) NULL,
  [PC_ID] INT NOT NULL DEFAULT 0,
  [L_MAKE]               VARCHAR (32)    NULL,
  [TV_MOVED]        DECIMAL (18, 2) NULL
);

dbo.TABLE2
CREATE TABLE [dbo].[TABLE2](
  [ID] NVARCHAR(32) NOT NULL,  -- PK index
  [C_ID] NVARCHAR(64),
  [DURATION] DECIMAL(18,2),
  [P_COUNT] BIGINT,
  [LS_TIME] DATETIME,
  [LE_TIME] DATETIME,
  [S_AGG_ID] NVARCHAR(32)   -- FK index
)

dbo.TABLE2 is subset of dbo.TABLE1 with only 5 columns, and the row count is same. We capture only 5 columns from dbo.TABLE1 and insert in dbo.TABLE2. But the size(space used) of dbo.TABLE2 is almost 3 times the dbo.TABLE1.
Indexes: Apart from PK, FK indexes we have one additional index on dbo.TABLE1 with include columns [JS_ID, MAKE, S_NUMBER, TAG_GUID, LE_TIME]
Why the space used is too high for smaller table even though row count is same as its source table?


texts: MySQL Is there a performance difference between col = val and col IN (val)?

texts:
Is there a significant performance difference, or any other reason why I shouldn't write, for example:

SELECT * FROM table WHERE col IN (val);

Instead of:

SELECT * FROM table WHERE col = val;

I know I can test the performance difference myself to some degree, but just wondering if there is an internal difference for MySQL that I should be aware of.


texts: Multiple foreign keys with shared columns for weak entities

texts:
this is my first question at stackexchange.

I have a parent table (P) with two child (CC and CD):

CREATE TABLE P
(
    A CHAR(3), B CHAR(2),
    CONSTRAINT PK_P PRIMARY KEY(A, B)
)

CREATE TABLE CC
(
    A CHAR(3), B CHAR(2),
    C CHAR(1),
    CONSTRAINT PK_CC PRIMARY KEY(A, B, C),
    CONSTRAINT FK_CC_P FOREIGN KEY(A, B) REFERENCES P(A, B)
)

CREATE TABLE CD
(
    A CHAR(3), B CHAR(2),
    D CHAR(1),
    CONSTRAINT PK_CD PRIMARY KEY(A, B, D),
    CONSTRAINT FK_CD_P FOREIGN KEY(A, B) REFERENCES P(A, B)
)

I want a table (T) with optional references to CC and CD:

CREATE TABLE T
(
    TId INT AUTO_INCREMENT PRIMARY KEY,

    -- more fields

    CurrentA CHAR(3), CurrentB CHAR(2),
    CurrentC CHAR(1),
    CurrentD CHAR(1),

    CONSTRAINT FK_T_CurrentCC
        FOREIGN KEY(CurrentA, CurrentB, CurrentC)
        REFERENCES CC(A, B, C),

    CONSTRAINT FK_T_CurrentCD
        FOREIGN KEY(CurrentA, CurrentB, CurrentD)
        REFERENCES CD(A, B, D)
)

So that if both records Current CC and Current CD are present, they must refer to the same parent P record.

This is the first option, but I intuitively think it may create some problems which I'm not really sure about.

I'm also considering a second option:

CREATE TABLE TAlternative
(
    TId INT AUTO_INCREMENT PRIMARY KEY,

    -- more fields

    CurrentCCA CHAR(3), CurrentCCB CHAR(2), CurrentCCC CHAR(1),
    CurrentCDA CHAR(3), CurrentCDB CHAR(2), CurrentCDD CHAR(1),

    CONSTRAINT FK_TAlternative_CurrentCC
        FOREIGN KEY(CurrentCCA, CurrentCCB, CurrentCCC)
        REFERENCES CC(A, B, C),

    CONSTRAINT FK_TAlternative_CurrentCD
        FOREIGN KEY(CurrentCDA, CurrentCDB, CurrentCDD)
        REFERENCES CD(A, B, D),

    CONSTRAINT CHK_CurrentCC_CurrentDD_Same_P
        CHECK
        (
            -- Parent not specified
            (
                (CurrentCCA IS NULL AND CurrentCDA IS NULL)
                AND
                (CurrentCCB IS NULL AND CurrentCDB IS NULL)
            )
            -- Same parent
            OR
            (
                (CurrentCCA = CurrentCDA)
                AND
                (CurrentCCB = CurrentCDB)
            )
        )
    )

Which would be better?

EDIT: I want the T table to be another entity with optional references to CC and/or CD, not a table to optimize (see the --more fields comment). Think about CC and CD being two kind of parts of a machine P that cannot exist without P (for example CC defines the positions inside the machine, and CD defines other kind of zones inside the machine), and T being a final product positioned in a position and/or zone.


texts: Which database for a web application with similar requirements like file sharing/storage services?

texts:
I am currently working on a web application that allows manufacturers to upload files (technical drawings of products), which they can then share with suppliers so that they can produce the corresponding parts.

As of now, I use the database to store user/company account related information. Furthermore, whenever a file is uploaded a reference, in which AWS bucket the file is stored, is saved in the database (in other words, I use the database to query from where to retrieve requested information). Due to the nature of the application much more requests will made to read data than to write it.

Not knowing much about database I started to build the back-end of my web app using a no-sql (mongoDB) database. As I am expanding the functionality of the back-end I am introducing a great deal of dependencies and therefore queries. Which data can be accessed depends on which company a user belongs to and the relation of that company to other companies, etc. Hence introducing all these queries will probably slow down my application significantly as the user base scales - please correct me if I am wrong.

Hence I want to ask which database you would recommend using for such an application?

Thank you in advance!

Best regards, Samuel


texts: MySQL: how to replicate users in master-master configuration

texts:
I'm new to MySQL and have set up MySQL master-master replication according this tutorial:
https://support.rackspace.com/how-to/mysql-master-master-replication/

I have created some test users on the first DB and would like to know how can I replicated those users to the second master DB.

As far as I understood the administrative database, which is called 'mysql' must be replicated, so the users will be replicated.

Is it enough if I add the line below into my.cnf?

replicate-do-db mysql

Are there any other possibilities to replicated users?

The closest answer I found was mysql-db-in-replication-but-users-created-on-master-are-not-replicating-on-slave

Any help would be appreciated.


texts: "Could not find login" failures for remote LocalSystem that exists

texts:
I imagine security changed in some way for Windows 2016, but I'm unable to determine what exactly.

For context: We have a variety of remote clients that handle data processing within a (non-SQL) data source as well as ETL to SQL instances. All of those processes are managed by services that may be running as LocalSystem or an AD user. The remotes are running a mix of Windows 2008 and 2012.

What we're finding is that after the remotes migrate to Windows 2016, the services running as system accounts no longer can connect to SQL Server 2016; logging errors that a login could not be found for "DOMAIN\MACHINE$", even though the login was present beforehand and still is. The new machine takes on the name of the old one, and the old-server services' registry keys are copied to the new remote to make sure nothing gets changed inadvertently.

This has been resolved by changing the service to run as the AD user; what I currently consider ideal seems to be required. In order to best push for the process changes to use the AD user all the time I'm trying to understand what about Windows 2016 System Accounts would "trick" SQL Server into thinking there's no login.


texts: How to determine MAXDOP setting when using DTU in Azure SQL

texts:
I have previous experience of setting MAXDOP settings for on premise OLTP & OLAP servers when you know the number of cores at your disposal. e.g. if I knew I had 16 at my disposal I'd set my MAXDOP to 2 or 4 for my OLTP system.

I just joined a project and noted the core production database is set to MAXDOP of 1, prohibiting any parallel operations.

It is a dedicated server (so no elastic pool) Premium P11 server so DTUs abstract the exact amount of CPU, memory capability. Without being able to tell how many cores you are dealing with, is there a sensible starting config? finding it a little tricky to port on prem best practices to Azure SQL.

The test servers are a different config with a shared elastic pool, so no easy way to test specific execution plans there either (especially as everything is written through Entity framework ORM)


texts:
I have data that fits an entity-attribute-value structure but with very few entries (same attributes but different values) per entity.

The main characterizations of my data are as follows:

~700 different entity types, but only 3-4 entries  for each type.
~1400 attributes, but only 2 including the id are used for every type.
~75% of the attributes are unique to a type, but a substantial number are used for many types.
The data will be very static, very rarely requiring changes.
Queries will primarily consist of filtering on attributes.

Based on my use-case and the fact that there will only be a few thousand entries total, my initial thought is to use a single table inheritance model.  That said, the sparsity of the table leaves me wondering if jsonb or hstore or something else I haven't thought of might be a better solution.

I'm using Postgres 9.5


texts: Optimize IN clause in where query with order by - MySQL

texts:
I am trying to optimize  a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query:

SELECT *
FROM `test`
WHERE user_id = 9898
AND status IN (1,3,4)
order by id
limit 30;

Here is the result of explain, as you can see the query is filesort

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  test    range   user_id     user_id     8   NULL    3   Using where; Using index; Using filesort

Here is my table structure

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `status` int(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `user_id`, `status`) VALUES
(5, 9797, 2),
(6, 9797, 3),
(4, 9898, 0),
(1, 9898, 2),
(2, 9898, 3),
(3, 9898, 4);

How can I optimize the query? In my real table I can see the following information in error log:
# Query_time: 26.498180  Lock_time: 0.000175 Rows_sent: 100  Rows_examined: 4926


texts:
I wanted to ask what is the proper way to run the index maintannace ola script.
Do you include @UpdateStatistics = 'Y', and @OnlyModifiedStatistics = 'Y'?

The reason I ask is that wherever I read, everyone says not to update statistics after an index rebuild.  From what I am seeing, with those two options highly fragmented or indexes that could not be reorganized, that were rebuild would have their stats updated (excluding column stats) and than updating stats again would update the stats.

Would it be better to update stats in a different job, ran at a different time/day or is it fin to include those options?


texts: Why my oracle BKP script keeps going to $ORACLE_HOME\dbs

texts:
I have this script to make a lvl0 BKP of my Oracle database. Also I'm forcing to allocate in a directory it keeps going to the $ORACLE_HOME\dbs and not to the one I pointed in the configuration disk parameters:
allocate channel d1 device type disk format '/backup/Rman/LIFE/FULL/%U.bkp';
crosscheck archivelog from time 'sysdate -1';
crosscheck archivelog all;
crosscheck backup of database;
backup incremental level 0 noexclude check logical tag Historico filesperset 15 keep until time 'sysdate+180' logs database format 'db_L0_%d_%s_%p_%T_%U.rman';
backup current controlfile tag Historico keep until time 'sysdate+180' logs format 'Ctl_%d_%s_%p_%T_%U.rman';
backup spfile tag Historico keep until time 'sysdate+180' logs format 'Spfile_%d_%s_%p_%T_%U.rman';
release channel d1;
}
list backup of archivelog  from  time 'sysdate-4';
list backup of database summary completed after 'sysdate -5';

thanks!


texts:
I came up with an interesting Oracle-db problem and keep thinking about a feasible solution to solve this task. Lets say we got three tables (bold columns are PKs):

A (Name, GroupID, versionID, Column1, Column2)
B (Name, GroupID, versionID, Column1, Column2)
C (versionID, date_created)

So it works as follows:

When a new batch of data is imported into the db, each record is tracked with a new unique versionID in table C
Table B holds all imports / changes while Table A only has one record (the newest)

What I'm trying to do right now, is get a sort of diff / changelog for the different batch uploads. This includes any differences in:

Name, GroupID, ColumnA, ColumnB, ColumnC between two specific dates.

I do this with two inner joins over A,B with Name&GroupID and then B and C over versionID and two dates. This works fine if the PKs Name & GroupID stay the same, see output 1:

|A.Name(new)|B.Name(old)|A.GroupID(new)|B.GroupID(old)|A.Column1(new)|B.Column1(old)|A.Column2(new)|B.Column2(old)|date_created(new)|date_created(old)
|Test1      |Test1      |37            |37            |Dog           |Cat           |Mouse         |Mouse         |2015-08-10       |2015-08-20
|           |Test3      |              |38            |              |Cat           |              |Mouse         |2015-08-10       |2015-08-20
|New record |           |38            |              |Mouse         |              |Mouse         |              |2015-08-10       |

output - A.Column1 changed from Cat to Dog
output - a record gets deleted
output - new record is added which did not exist before

My output works well for output scenario 1, however if the name or groupID gets changed / deleted, or a new record is insert this change obviously won't show up because the join does not work anymore. Does anyone have an idea to deal with this problem. My output should work as shown above in output 1 & 2 & 3.
I am sadly not able to implement any triggers to the db and my goal is to try to solve this via sql queries.

Thanks in advance for your input


texts: SQL for Inserting the system date and a fixed time (not the system time) in a column

texts:
I have a query like this:

Insert into UNITRIP (START_TIME, END_TIME)
   (TO_DATE('10/20/2015 06:05:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/20/2015 07:34:06', 'MM/DD/YYYY HH24:MI:SS')

I require a sql where the start_time is of this type: system-date 06:05:00 (fixed date is to be replaced by sysdate).

How to write a query for this?


texts: Event replication on slave and Slave_IO_Running:NO

texts: I have one master and four slaves. Whenever I created an event on master it gets replicated on all slaves.However, i am running a script on one of the slaves that takes a snapshot of the machine and saves it somewhere else. During taking a snapshot i kept the Slave_IO_Running: NO  in the script and when the snapshot gets completed the script makes Slave_IO_Running: YES and the replication start again.I just want to know that after taking a snapshot at that one slave when the Slave_IO_Running turned to YES by the script, The event I created disappears from that slave but remains there on the other slave and when I delete the event on master it produces error on that slave at which snapshot is being taken.Please just let me know the reason and the effect of Slave_IO_Running: NO on slaves.


texts: When a table is locked but SHOW PROCESSLIST shows nothing

texts: We had some slowness in our application. When I checked I figure out that one particular table was locked so all transactions were rejected by Lock wait timeout exceeded. When I did SHOW PROCESSLIST; I was expecting to see  some processes with Waiting for table level lock state but it shows nothing !? only some queries INSERT/UPDATE on the same table taking exactly 120 seconds before time-out and others processes on sleeping state (see picture):


texts: Mysql - Examine last query run by thread in sleep state

texts: I am seeing some threads in the "sleep" state for longer than normal. Normal maybe being 1-3 seconds with the abnormal being up to 60 seconds or longer. It makes sense that this is an application issue so my question is if it's possible to see the last query run by a thread that is in a sleep state. From there I can hopefully track down the code that is leaving these connections open.


texts: Trying to open service master key in powershell but failed

texts:
I recently add script to log ship manually in sql server 2014 via powershell.

so I can read header without creating temp table!
and I recently try to add password support for this script.
here is the scripts

$masterdb=$server.databases["master"];
$masterkey = New-object -TypeName Microsoft.SqlServer.Management.Smo.MasterKey -argumentlist $masterdb;
$masterkey.Import($mkeyinfo.filefullname,$mkeyinfo.password,$mkeyinfo.password,$true);

$bdi=new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($FileFullName, 'File');
$restore.Devices.Add($bdi);
$restoreHeaders=$restore.ReadBackupHeader($server);

I already restored certificate with private key.So before I read header info of backup All i need is just open the service masterkey with password.
But it always fail with message like following...

masterkey 'Microsoft.SqlServer.management.smo.objectkeybase' fail...

I don't see any detail information of fail.. somebody helps!!


texts: MySQL: initialize the Data Directory (--data-dir) for Operating System user other than mysql

texts:
I have installed mysql 5.7.19 using rpm package in RHEL, my requirement is to initialize the data directory using mysqld, the MySQL server with using some other user 'SAM' rather than mysql user. i have created my.cnf file and created a symbolic link ln -s etc/my.cnf .my.cnf, Following are the contents of my.cnf
    [client]
    port=3307
    socket=/tmp/mysql.sock
    host=localhost
    default-character-set=utf8

[mysqld]
port=3307
socket=/tmp/mysql.sock
datadir=/home/data/db
log-error=/var/opt/mysql/mysql-error.log
sql-mode=""
default-storage-engine=MyISAM
long_query_time=5
max_allowed_packet=16M
# Accept 4.0.x style passwords
# old-passwords=ON
character_set_server=utf8

[mysqldump]
quick

I'm getting following logs in file(mysql-error.log) while running command and mysql server is not getting up mysqld --defaults-file=/home/etc/my.cnf --basedir=/usr/sbin/.. --pid-file=/var/opt/mysql/mysql.pid

18-06-06T05:02:31.933483Z 0 [Note] InnoDB: Foreign key constraint system tables created
2018-06-06T05:02:31.933518Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-06-06T05:02:31.935391Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2018-06-06T05:02:31.935417Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2018-06-06T05:02:31.936921Z 0 [Note] InnoDB: sys_virtual table created
2018-06-06T05:02:31.937905Z 0 [Note] InnoDB: 5.7.19 started; log sequence number 0
2018-06-06T05:02:31.938825Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2018-06-06T05:02:31.938932Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-06-06T05:02:31.939424Z 0 [Note] Salting uuid generator variables, current_pid: 12598, server_start_time: 1528261351, bytes_sent: 0,
2018-06-06T05:02:31.939459Z 0 [Note] Generated uuid: 'd1916718-6946-11e8-9f90-000c29429204', server_start_time: 3546021758129118237, bytes_sent: 50645584
2018-06-06T05:02:31.939475Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d1916718-6946-11e8-9f90-000c29429204.
2018-06-06T05:02:31.941093Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-06-06T05:02:31.941184Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-06-06T05:02:31.941191Z 0 [Note] Server hostname (bind-address): '*'; port: 3307
2018-06-06T05:02:31.941207Z 0 [Note] IPv6 is available.
2018-06-06T05:02:31.941211Z 0 [Note]   - '::' resolves to '::';
2018-06-06T05:02:31.941258Z 0 [Note] Server socket created on IP: '::'.
2018-06-06T05:02:31.942764Z 0 [Warning] Failed to open optimizer cost constant tables

2018-06-06T05:02:31.942851Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2018-06-06T05:02:31.942892Z 0 [ERROR] Aborting

can someone please answer is it possible to initialize of mysql server using other users rather than mysql

Note:  This is a fresh install, not an upgrade.  (This fact copied from a now-deleted Answer.)


texts: SQL sort by sum of two different where clauses in a join

texts:
table1

ID | Name
1    ID1
2    ID2
3    ID3

table2

ID | table1ID | Values
1    1          3
2    2          3
3    3          2
4    1          3
5    2          1
6    3          2
7    1          1
8    2          3
9    2          2
10   1          1
11   2          1

I need as columns, table1s name, table2s Count of Values being 1, values = 2 and values = 3. These also need to be sorted by the total count of values = 1 followed by total count of values = 2

So Basically

Name | 1s | 2s | 3s
ID2    2    1    2
ID1    2    0    2
ID3    0    2    0

My existing code is

$sql2 = "SELECT * FROM table1
INNER JOIN table2 b ON
(table1.ID= b.table1ID)
WHERE b.Values = '1'
GROUP BY b.table1ID
ORDER BY count(*) DESC";

Works for the single sort by but not the second.


texts: Concurrent SELECT and UPDATE in multiprocessing python

texts:
I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure

CREATE TABLE de_sim_points_end
(
-- Inherited from table de_sim_points:  id integer NOT NULL DEFAULT nextval('de_sim_points_id_seq'::regclass),
-- Inherited from table de_sim_points:  parent_geometry character varying(12),
-- Inherited from table de_sim_points:  used boolean DEFAULT false,
-- Inherited from table de_sim_points:  geom geometry(Point,900913),
  CONSTRAINT de_sim_points_end_pkey PRIMARY KEY (id)
)
INHERITS (de_sim_points)
WITH (
  FILLFACTOR=50,
  OIDS=FALSE
);
ALTER TABLE de_sim_points_end
  OWNER TO benjamin;

CREATE INDEX de_sim_points_end_geom_idx
  ON de_sim_points_end
  USING gist
  (geom)
  WITH (FILLFACTOR=100);
ALTER TABLE de_sim_points_end CLUSTER ON de_sim_points_end_geom_idx;

CREATE INDEX de_sim_points_end_parent_relation_idx
  ON de_sim_points_end
  USING btree
  (parent_geometry COLLATE pg_catalog."default")
  WITH (FILLFACTOR=100);

CREATE INDEX de_sim_points_end_used_idx
  ON de_sim_points_end
  USING btree
  (used)
  WITH (FILLFACTOR=50);

The SQL Query and Python Code used to match the rows can be found at https://github.com/boerngen-schmidt/commuter-simulation/blob/master/code/builder/process_point_mass_matcher.py (I will not post it here due to its length). But basically what I do is:

SELECT rows, randomly ordered from start point table
JOIN rows, randomly ordered from destination point table
Take the result, iterate over it and update the points in start and destination table as used
Save the result in another table (done by another thread)

When I was using multiple processes with SELECT ... FOR UPDATE I ran into deadlocks (currently I just use one process, so the FOR UPDATE statement is currently omitted). So far I have read a bit about SERIALIZABLE and so forth locking mechanisms, but yet I fail to fully grasp them.

Is it possible to make the matching multiprocessing compatible? How whould the statements look like?


texts:
My node worked normally, and I could use nodetool status normally, but when I changed endpoint_snitch to Ec2Snitch I started getting the error:

  nodetool: Failed to connect to '127.0.0.1:7199' - ConnectException: 'Connection refused'.

Although it worked fine when endpoint_snitch was SimpleSnitch

The node private address is 10.0.5.247 and attached to security group that has 7199 open for inbound and outbound for the range that covers 10.0.5.247

So, any idea?


texts: Article table has a lot of null. What is the best way to prevent null in MySQL

texts:
I'm optimizing table schema.

My case is like.

User can generate an article.

It has a lot of item. like name, age, sex, address, hobby...

name, sex is a required entry.

but age, address, hobby is not required entry.

Table has a lot of null currently.

Sometimes null is changed attribute domain value when user edit article.

  name   |   age   |   sex   |   address   |   hobby
  aa         null      male        null        soccer
  bb         17        female      null        null

So I'm thinking 2 case.

1.

Table: article
Column: id, name, sex
Primary key: id

Table: article_sub
Column: article_id, type(0:age, 1:address, 2:hobby), value
Primary key: article_id, type

This case have to set varchar data type to value.

Because age is integer, address is string... so size is demerit.

2.

Table: article
Column: id, name, sex
Primary key: id

Table: article_sub_integer
Column: article_id, value
Primary key: article_id

Table: article_sub_string
Column: article_id, type(0:address, 1:hobby), value
Primary key: article_id, type

This case I have to refer 3 table.

Which way is the better?

If you have other idea Please tell me.

Thank you.


texts: Query works manually, not from stored procedure - Safe update error

texts:
I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure.  The stored procedure in question:
CREATE PROCEDURE `sp_UpdateGoal`(
    IN UpdateGoalUserName varchar(45),
    IN nGoalDate DATE,
    IN nAmount DECIMAL(9,2),
    IN nActiveGoal tinyint,
    IN nOriginalAmount DECIMAL(9,2),
    IN nChangeLog VarChar(100)
)
BEGIN
    UPDATE
        salesgoals
    SET
        Amount=nAmount,
        OriginalAmount=nOriginalAmount,
        `active`=nActiveGoal,
        ChangeLog=concat(ChangeLog,"\r\n",now()," ",nChangeLog)
    WHERE
        salesgoals.username=UpdateGoalUserName
        AND salesgoals.goalDate=nGoalDate;
END

The call to the stored procedure I'm using:
CALL sp_UpdateGoal( 'lauren', "2020-01-01",150000,1,150000,"Testing sp_UpdateGoal");

which returns
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
I would prefer not to disable safemode.
The manual query that works:
   UPDATE
        salesgoals
    SET
        Amount=150000,
        OriginalAmount=150000,
        `active`=1,
        ChangeLog=concat(ChangeLog,"\r\n",now(),'Testing manual query')
    WHERE
        username='lauren'
        AND goalDate='2020-01-01';

SalesGoals is a simple table:
CREATE TABLE `salesgoals` (
  `username` varchar(45) NOT NULL,
  `GoalDate` date NOT NULL,
  `Amount` decimal(9,2) NOT NULL,
  `ChangeLog` varchar(500) NOT NULL,
  `Active` tinyint DEFAULT '1',
  `OriginalAmount` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`username`,`GoalDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It looks to me like when I call the stored procedure, it's ignoring my WHERE clause. I've been staring at this for a crazy long time and don't see anything wrong. What am I missing?
Thanks!


texts: List table sizes for all tables on all databases

texts:
Is there a simple way of listing the size of every table in every database on a MSSQL server?

I have used a query on sys.tables to get results for a single database, but we have >100 databases per server, so a way of getting the same results but for all databases would be great.

Currently I'm having to create a temporary list of databases from master.sys.databases, and then iterate over that with a cursor, building a query and inserting the results into a temp table with EXEC sp_executeSQL @SQLString.


texts: SQL Server - Is it possible to forbid creation of default constraint name?

texts:
I was having a looking on this article about default constraints and I have a question. Is there a configuration in SQL Server 2008 that forbid naming creation of new default constraints declared without a proper custom name?

In other words, can SQL Server block this:

CREATE TABLE Test1 (
   Val1 int DEFAULT -1,
   Val2 int DEFAULT -2)
  /*the fields above will generate a strange suffix in default constraint name such as DF__Test1__Val1__32E0915F*/

but allow this:

CREATE TABLE Test1 (
   Val1 int Test1_Val1_DF DEFAULT -1,
   Val2 int Test1_Val2_DF DEFAULT -2)
  /*the fields will use proper custom name in default constraint name*/


texts: Store equations/formula in mysql db table

texts:
I want to know what is there a way for storing equations or formula in a DB column.
Below is the table where I am storing the machines details(master table). But the last column is what I am worrying.

Columns and values for the table would be

id,name,col1,col2,col3,col4,formula
1,mc1,546,459,1.24,3.9,(col1 * col2 * 0.0016)/(col3 * col4)

The formula changes for each entry in the table. Currently I am hard-coding the formula's in my program like

if(macid == 1) {
 // apply formula1
} else if(macid == 2) {
 // apply formula2
} .....

Now if any new machine gets added in the table I need to make changes int the code. So I want to store the formula in the table itself, so that I can provide UI interface to change the formula.

How can I achieve this. Plese help.


texts: Password reset for database for VCenter Install

texts:
I'm trying to reinstall vCenter SSO and it is prompting me for the "sa" password.  I set it up previously, but cannot remember what I used.  I uninstalled SSO (as well as the vCenter Server, and Inventory Service), and I'm running through setup again.  I can't get past setting up the database without the password.

How can I reset this?


texts: Limit total size of multiple databases in MSSQL

texts:
I am looking for a way of limiting the total size a defined set of databases can grow to.

Take a case where you have a group of lets say 10 databases and I'd like to ensure that the combined size used by these dabases does not exceed 20GB. Distribution of data within these databases is neither even nor known to the DBA, so there is no sensible way of pre-configuring database size limits.

Update 1: To elaborate the scenario: The "groups" are used independently by differnt divisions. They each know the limit and it is their own responsibility to stay below this limit, otherwise databases will not be available any more.

It is my intention to have a "hard limit" per group which - in case it it reached - will forcefully prevent increasing the size, even if this means database changes or even reading from the databases is no longer possible.

An even distribution of DB limits is not feasible as the DBA does not know where the majority of data will be placed.

As detailed in the comments it is certainly a possibility to create seperate block devices per group (e.g. VHDs), but this would introduce a certain administrative overhead that I hoped to prevent.