texts: Making a server with multiple instances a member of two effectively independent clusters

texts:
We're in the early stages of planning a new database environment for our ERP and CRM applications (third-party). The goal is to use Always On Availability Groups for HA, rolling patches, read-only secondaries, etc. We want to use automatic failover, without the client application needing to be aware of the HA.

We've got one physical server that is quite massively specced. It's going to be running two instances of SQL Server: one for the ERP application that requires SQL Server 2016, and one for the CRM application that is better suited by 2017 (and this also allows us to give sysadmin rights to the CRM application vendor's support without affecting the ERP application).

We also have a pretty mature ESX infrastructure, and want to use some VMs as failover nodes. These won't be as decadently built as the primary server, but we will ensure acceptable performance for short outages or maintenance windows on the primary server.

My initial thought was to have two failover VMs: one for the ERP instance and one for CRM. It doesn't appear that a single server can be a member of multiple Windows failover clusters, i.e. I can't make an ERP cluster with the ERP VM and the physical server, plus a CRM cluster with the CRM VM and the same physical server.

If I were to instead build a three-node cluster, and control which nodes each instance can run on, would I be inviting any unpleasant side effects? My main fear is that quorum voting could get a bit weird if we start adding more database servers to the cluster, and we could have an instance go down unexpectedly even if all the nodes that can host that instance are still up. Is that even a risk, and are there reasonable ways to mitigate it? Are there enough possible complications that I should use a single, beefier VM that can handle both instances?

tl;dr:

Would I be shooting myself in the foot by building a three-node cluster where instance 1 can only run on servers A and B, and instance 2 can only run on servers A and C? Should I just build a two-node cluster (plus quorum member) and make sure server B can handle both SQL Server instances?


texts: How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

texts:
I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database

local_SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521))
    (CONNECT_DATA = (SID = remote_SID))
  )

and then use that in sqlplus

sqlplus user/pass@local_SID

However, in my circumstances modifying the local tnsnames is not possible. Is it possible to connect to a remote database just by using sqlplus argument without having to change tnsnames? Something like

sqlplus user/pass@remote_SID@hostname.network ;( I know, this one is not valid)


texts: Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server

texts:
I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server.

I checked this once and found that after this is run, the next DB hit takes a long time may be because of the relocation of fragments or so, but subsequent hits show kind of performance, i am not sure whether the query caching does this trick with optimization or optimization alone does this trick.

Can any one guide me with some real performance difference values if possible so that i can take up further as working with MySQL is gaining gravity in our project.


texts: Is it reasonable to mark all columns but one as primary key?

texts:
I have a table representing movies. The fields are:
id (PK), title, genre, runtime, released_in, tags, origin, downloads.

My database cannot be polluted by duplicated rows, so I want to enforce uniqueness. The problem is that different movies could have the same title, or even the same fields except tags and downloads. How to enforce uniqueness?

I thought of two ways:

make all the fields except downloads primary key. I'm keeping downloads out since it's JSON and it will probably impact the performance.
keep only id as primary key, but add a unique constraint with all the other columns (except, again, downloads).

I read this question which is very similar, but I didn't quite understand what should I do. Currently this table is not related to any other tables, but in the future could be.

At the moment I have slightly less than 20,000 records, but I expect the number to grow. I don't know if this is somewhat relevant to the issue.

EDIT: I modified the schema and here is how I would create the table:

CREATE TABLE movies (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    runtime     smallint NOT NULL CHECK (runtime >= 0),
    released_in smallint NOT NULL CHECK (released_in > 0),
    genres      text[] NOT NULL default ARRAY[]::text[],
    tags        text[] NOT NULL default ARRAY[]::text[],
    origin      text[] NOT NULL default ARRAY[]::text[],
    downloads   json NOT NULL,
    inserted_at timestamp NOT NULL default current_timestamp,
    CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

I also added the timestamp column, but that is not a problem as I won't touch it. So it will always be automatic and unique.


texts: In Oracle, will certain users only be able to see certain data in the privileges tables?

texts:
I can view data in the table_privileges view logged in as a particular user, but it seems to be missing or different information when logged in as a different user.

For the privileges views (data created when calling GRANT), will users see the data differently in these views? I seem to recall reading that this was the case, but can't confirm now.

Any specific links would be greatly appreciated.


texts: SSIS - Derive column values based on join/subselect from a non-table source

texts:
I have a flat file source that contains a lot of duplicate records, which have the same key fields but differ by their "changed date". I need to split this up into two outputs, one for the "latest" and one for "everything else."

When doing this from a table-based source I can simply add a derived column, something like:

SELECT CASE WHEN EXISTS (
  SELECT * FROM SomeTable t2 WHERE t2.KeyField = t.KeyField AND t2.ChgDate > t.ChgDate
) THEN 1 ELSE 0 END AS Historical
FROM SomeTable t

I need to do something similar from a flat-file source, but I can't figure out how to do the subselect. I tried adding a derived column transformation, but it doesn't look like you can derive column values based on other rows in the rowset. I also tried a merge join transformation (an LEFT OUTER join would accomplish the same goal) but that doesn't see mto allow self-joins. The only transformation that seemed to work on the entire rowset is the aggregate one, and that only works with a pre-defined set of aggregates.

For now, I'm creating a temporary SQL table that holds the entire data set, then using further tasks to copy them out, but I would really like to find a way to avoid that. Is there any type of transformation I can use that can join or sub-query the current rowset in memory?


texts: Is there a way to get the Actual Execution Plan for a cancelled query?

texts: Sometimes a query will be taking ages and you don't want to have to run the whole thing.  This is exactly the kind of time you may want to see the Actual Execution Plan to figure out why the query is inefficient.  However I've noticed that when you tell SSMS to include the Actual Execution Plan in the results, it only does so when the query finishes successfully and is not cancelled!  Why is this?  Presumably the plan has to be generated first, so it could be sent first by SQL Server and be displayed even for a cancelled query.  I know about the estimated execution plan but it's not the same.


texts: PostgreSQL Streaming Replication - Slave remains in recovery mode

texts:
PostgesSQL 9.3.x

I cannot run some commands on slave because:
"ERROR:  recovery is in progress"

I set up streaming without archive. Initialized with pg_basebackup. Dataset has about 180GB .
This is what I get on slave when looking at relevant processes:

23378 ?        Ss     0:12 postgres: startup process   recovering 000000010000026000000083
23379 ?        Ss     0:02 postgres: checkpointer process
23380 ?        Ss     0:00 postgres: writer process
23382 ?        Ss     0:00 postgres: stats collector process
23383 ?        Ss     0:14 postgres: wal receiver process   streaming 260/837D8000

Byte lag (as queried on master) is between 0 and 2000 bytes.

recovery.conf content:

standby_mode = 'on'
primary_conninfo = 'host=SOME_IP port=5432 user=postgres sslmode=require'

What am I missing?


texts: Reportviwer matrix show all months/weeks of year

texts:
I'm new here, and i have a situation.
I need to alter one report that i created a few time ago, that shows the planning of maintenance. But not all months/weeks have plan defined, and i want to show all months/weeks even in that situation.
The table where i go get the data, have a column Month and onother named Week, and the matrix column group is on Month, and the second group is on Week.

I show you a image of what i have done, and the result.

And here is the query:

SELECT [dbo].[Equip_PIMP].[ID_Equip_PIMP]
          ,[dbo].[Contrato].[ID_Contrato] AS [Contrato_Nr]
          ,[dbo].[Cliente].[Nome] AS [NomeCliente]
          ,[dbo].[Instituicao].[Nome] AS [NomeInstituicao]
          ,[dbo].[Servico].[Nome] AS [NomeServico]
          ,[dbo].[Equip_Marca].[Nome] AS [NomeMarca]
          ,[dbo].[Modelos].[Nome] AS [NomeModelo]
          ,CASE WHEN @DIA_OU_X = 1 THEN CAST([dbo].[Equip_PIMP].[Dia] AS VARCHAR(2))
                WHEN @DIA_OU_X = 0 THEN 'X'
           END AS [VALOR_CEL]
          ,[dbo].[Equip_PIMP].[Data_Plano]
          ,[dbo].[Equip_PIMP].[Data_Execucao]
          ,[dbo].[Rotina].[Nome] AS [NomeRotina]
          ,[dbo].[Rotina].[Tipo] AS [TipoRotina]
          ,[dbo].[Equip_Categoria].[Nome] AS [NomeCategoria]
          ,CASE [dbo].[Equip_PIMP].[Mes]
                WHEN 1 THEN 'JANEIRO'
                WHEN 2 THEN 'FEVEREIRO'
                WHEN 3 THEN 'MARÇO'
                WHEN 4 THEN 'ABRIL'
                WHEN 5 THEN 'MAIO'
                WHEN 6 THEN 'JUNHO'
                WHEN 7 THEN 'JULHO'
                WHEN 8 THEN 'AGOSTO'
                WHEN 9 THEN 'SETEMBRO'
                WHEN 10 THEN 'OUTUBRO'
                WHEN 11 THEN 'NOVEMBRO'
                WHEN 12 THEN 'DEZEMBRO'
           END AS [Mes_Nome] -- This is the MONTHNAME
          ,[dbo].[Equipamento].[ID_Equipamento]
          ,[dbo].[Equipamento].[Nome]
          ,[dbo].[Equipamento].[Marca]
          ,[dbo].[Equipamento].[Modelo]
          ,[dbo].[Equipamento].[Categoria]
          ,[dbo].[Equipamento].[Num_Serie]
          ,[dbo].[Equipamento].[Num_Inventario]
          ,[dbo].[Equipamento].[Num_Equipamento]
          ,[dbo].[Equip_PIMP].[Dia]
          ,[dbo].[Equip_PIMP].[Semana] -- This is the WEEKNUMBER
          ,[dbo].[Equip_PIMP].[Mes] -- This is the MONTHNUMBER
          ,[dbo].[Equip_PIMP].[Trimestre]
          ,[dbo].[Equip_PIMP].[Semestre]
          ,[dbo].[Equip_PIMP].[Ano]
FROM ...


texts:
I have a column named foo in a table. I have constructed queries in scripts, which use this name.

I would now like to rename the column in the table to bar. This will break the scripts (there are several, but they could be changed if necessary). The table is referenced by other tables. I'd like to rename because the context of what is stored in that column has changed.

Is it possible to have two names for a column?


texts: How to benchmark Switchable Optimizations in MySQL

texts:
I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches.
I have already tried to use sysbench and MySQL official test-suite. But I fond that they are too general and mainly for functional testing, i.e., runing these tests with/without the optimization switches, the performance are almost identical. What I want is sth like a performance test suite.
Could you please suggest me some good tools/suites to fulfill my demands?
Thanks!


texts:
In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table objects was nearly 30,000 according to the dba_tables listing. Use of the Import/Export wizard was immediately discounted and we don't have knowledge of BIML to build a SSIS-centric solution. We tried using Microsoft SQL Server Migration Assistant 7.10 for Oracle but the utility was non-responsive (probably stuck trying enumerate all the objects).

We did a crude workaround constructing SELECT * INTO <SQLTable> FROM OPENQUERY("SELECT * FROM...<Oracle Table>") queries with a linked server (painful, but effective) and were able to import all the tables.

How to move the views over, however, is our next challenge. Can we try to use the Migration Assistant to convert the views (how do we get past it choking on the object volume)? Are there meta views in the v$ schema or elsewhere that can at least export the DDL utilized so we can then edit with the newly-imported tables?


texts: MS Access: How to automatically fill in ID field based on value of another field

texts:
I have an MS Access inventory database where I imported some data about our food purchases from Excel. In Excel, the Vendors were simply listed in a "Vendor Name" column, which looks like this:

Vendor name
------
Bearded Lady
Blue Heron Bakery
Calliope Farm
Cash and Carry
...

In my Access database, I have a table called "Vendors" that looks like this:

ID  Vendor Name
1   Bearded Lady
2   Blue Heron Bakery
3   Calliope Farm
4   Cash and Carry

The "Food purchases" table in Access looks like this:

ID  Item    Vendor ID   Vendor  Orderer ID  Orderer # of units purchased    Purchase unit   Price   Price per unit
2   Agave       Earthly Gourmet     bulk (wet)  5   lbs $25.00  $5.00
3   Allspice        OGC     bulk (spices)       3
4   Anise seed      OGC     bulk (spices)       3

Right now the "Vendor ID" field is blank, and I want to use the values in the "Vendor name" field (Earthly Gourmet, OGC, etc) to automatically fill in the correct values, based on matching the name to the corresponding key in the "Vendors" table, instead of having to manually type in the correct ID number for each vendor ... How can I do this?


texts: single select vs multiple selects using limit and offset

texts:
Working on a fairly high volume site. There are some tables example - the customers table which gets hit a lot.

The problem

We are writing a piece of code that selects a bunch of customers and sends out mailers etc (nothing mission critical). These selects often result in about 20000 records. We obviously do not want the regular hits to the customer table being blocked by these select statements.

We went down the route of making these reads readUncommitted since the operations on the data are absolutely not mission critical.

However, instead of making these massive selects if in code we make a select with a limit and an offset and keep looping would that cause less locks on the db.

In other words - would 20 select statements each retrieving 1000 records be more desirable than 1 select statement retrieving 20000 records in terms of reducing locking etc.

Any suggestions would be greatly appreciated.


texts: MySQL NDBcluster 7.3 limitation on Table Joining in a single SQL statement

texts:
I am a novice user of Mysql database and recently my company is evaluating MySQL Cluster for its shared nothing architecture and synchronised replication.

However my colleagues informed me that the MySQL clusters will have limitation on the number of table joins you can do on one SQL statement that is only joining not more than 5 tables. He went to a MySQL training 1 or 2 years back, and I could not find any article or documentation on this matter.

  My web application usually query from 5-6 tables in a single SQL
  statement. A table usually has > 200 columns and not more than 3
  columns as combination Primary Key. The table tuples has more than 10
  million in extreme case. The MySQL cluster will be spanning across 2
  data centers with more than 10 data nodes. Table storage engine will be NDB for the replication capabilities.

Please let me know if more information is needed.

Any expert here is with MySQL cluster knowledge about this and able to share ? I appreciate any links or article to back the claims, thank you.

P.S.  please note the version of the mysql cluster in the title


texts: SQL Server DR, mirroring or virtualise and mirror

texts:
I have a client with a small business server 2008 Premium installation.  This obviously includes SQL Server 2008 standard on a separate 2008 server.  On the SQL we run several line of business applications.

The client has got the the size where if we were to have a failure it would cripple the business.  We have ample backups protecting the data, but it would obviously take time to get a new server up and running etc.

What we want is a stand by server in a remote office, but I need to know the best way of replicating the SQL server.  I see there are different ways, snap shot or transactional etc, but I haven't found a decent article describing the pros and cons of them.

I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates.  I don't want the primary server waiting on the secondary server to update before it commits a write.

Also the table structure and views change fairly regularly as new features/updates are added to the LOB applications.

I'm wondering if anyone has any opinion on what method may help.

Also wondering if we would be better off virtualising the server and mirroring this, which I believe is quite good with the new hyper V.

edit (OP, once you have associated your accounts, please incorporate this further information to make a single, cohesive question.)

sorry for the delay.

I suppose in terms of data loss, i suppose an hour wouldnt be the end of the world.

Hardware, wouldnt be identical as its currently on an hp ml350 g6

i was talking about hyper-v 2012s replication to a completely seperate server.

I guess i am concerned about disk io as one of the lob dbs is pretty hefty and slow, although we got the developers to re-write some of the worst bit to make it better.

I guess my biggest concern is if when writing data it has to wait for both the local and remote server to update.  Does log shipping need do this?

The remote server will be on remote ADSL connection, and i would like to avoid having to replicate it twice.

Also the SQL server is part of Small business server 2008 premium, but was brought as OEM, are we allowed to either move the second windows server license onto another machine (the main server will remain on the origional hw) or can we move just the SQL onto a new windows 2012 box?  as this would mean we can move the sql onto better hw, and use the old one as the DR box.


texts: Put in the wrong data type for pk, cannot change because it is FK for another table

texts: I'm trying to change the PK Department.DeptId to char(8), however it is an FK in employee. Doing two alters in 1 query throws an error for the first statement. Changing it in the edit window does the same. Any idea how to change it in both places so that it does not throw an error?


texts:
Goal: Maintaining a constant live backup of a postgres cluster. In case of cluster failure, backup/Secondary will contain most up to date view of Primary cluster. This is not a hot standby circumstance. When the Primary postgres machine can be fixed/switched, etc. the new machine's Postgres cluster will be restored using the backup.

Current direction: Using postgres 9.3 streaming replication to a remote postgres server. (Currently unsure whether it will be synchronous or asynchronous, depending on consistency requirements of the system.)

Question: Is this a reasonable solution?

Given this solution, is there any way to tune the Secondary/Slave postgres cluster? For instance, since the backup server won't be responding to queries it doesn't need indices. Can I somehow disable these for the Secondary cluster?

In what ways can I save in disk space and memory on my Secondary/Slave cluster given the knowledge that it's only serving as a 'dumb' backup?


texts: Why do we need to rebuild and reorganize Indexes in SQL Server

texts:
After searching the internet i couldn't find the reason for

Why do we need to rebuild and reorganize indexes in SQL Server ?
what does internally happens when we rebuild and reorganize ?

An article on a site says :

  Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

I couldn't understand this, though it says that WHEN to do this, but I would like to know WHY do we need to rebuild and reorganize indexes ?


texts: mysqld_safe version different than mysqld?

texts:
Is it a problem that mysqld socket has a different version than the mysqld server?
I noticed this in my mysqld log during startup

120420 19:30:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120420 19:30:06  InnoDB: Initializing buffer pool, size = 8.0M
120420 19:30:06  InnoDB: Completed initialization of buffer pool
120420 19:30:06  InnoDB: Started; log sequence number 0 44233
120420 19:30:06 [Note] Event Scheduler: Loaded 0 events
120420 19:30:06 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.61'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Yet, when I show variables I get this

  SHOW VARIABLES LIKE "%version%";

+-------------------------+-------------------------------------------+
| Variable_name           | Value                                     |
+-------------------------+-------------------------------------------+
| innodb_version          | 5.5.30                                    |
| protocol_version        | 10                                        |
| slave_type_conversions  |                                           |
| version                 | 5.5.30-cll                                |
| version_comment         | MySQL Community Server (GPL) by Atomicorp |
| version_compile_machine | x86_64                                    |
| version_compile_os      | Linux                                     |
+-------------------------+-------------------------------------------+
7 rows in set (0.00 sec)

Based on golimar's questions I ran a

  ps aux

and I see this

  mysql      633 31.4 33.5 11942788 5452172 ?    SNl  08:18 101:31
  /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql
  --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/host.reviewcritical.com.err --open-files-limit=4096 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306

  root     32674  0.0  0.0 108292  1576 ?        SN   08:18   0:00
  /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
  --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql


texts:
What I'd like to do is to replicate data nodes within a cluster not only for backup/failover purposes but also for scale-out reasons. Is this possible? If not: why? If yes: how do I do it?
Actually, it would be nice if someone could briefly explain how the data node replication technically works  for my better understanding :)


texts: Should I use SQL or NoSQL for this specific design (surveys)?

texts:
Building a small web application to create satisfaction surveys with a default list of questions and responses. The application is for a small audience of people (5,000 max) with surveys sent out when work orders are completed.

Given the small size and relatively simple requirements, I'm thinking I could utilize either a SQL or NoSQL solution (I have easy access to both).

Types of entities

Survey Templates (probably 3 of them)
Questions (multiple questions per survey template)
Question Types (free form, specific-choice responses, etc.)
Survey Responses (the responses to the questions on a specific survey template)

Querying / Reporting Needs

display responses of individual surveys
display aggregated responses across a range of surveys by certain aspects

work order # (multiple surveys per work order)
date range of work orders
group that performed the work

Change/Growth Needs

Survey templates, template questions, and possible responses can potentially change at any given time (dictated by the customers and evolving)

could conceivably create a new template when necessary but would be great to not have to do that where applicable.

No worry about growth in terms of storage. This is currently being done with a (horribly designed) Access DB and so we're not talking anything major here.

Other Needs

Structure should be able to accommodate requiring more information / notes if the responses are below a certain threshold (E.g. 3 or below out of 5)

Stack Considerations

This is being built on the Microsoft ASP.NET stack (MVC4, WebAPI, etc.)

Personal Preferences / Thoughts

I've wanted to work with NoSQL databases more for a while
I am more familiar with SQL databases but it seems like a lot of schema and server overhead for something like this
RavenDB and CouchDB are both appealing to me for this

Raven moreso because it's built so nicely to work with .NET and via HTTP, but I'm not sure if the licensing will work out.
Would also consider MongoDB, but I was thinking straight JSON docs might be easier to work with than BSON

with a NoSQL solution (particularly RavenDB), to an extent my objects become my schema because I'm persisting them to a data store. I have to build that structure in SQL server first (unless I go with Entity Framework). Does this lend an advantage to NoSQL or is it a wash taking into consideration things like EF?

Interested in your thoughts and providing any information that would help someone make an objective decision.


texts:
On Mysql 5.1.72 I created a replication user. Right before running FLUSH PRIVILEGES; this thought passed my mind what if there are any unapplied privilege updates?

How can I flush privileges of a single user?
How can I check the
list of changes about to get applied once FLUSH PRIVILEGES; is
processed?
If things go wrong is there any way to revert the
changes?


texts: Oracle Active Data Guard - OPEN_MODE STATUS

texts:
I would to ask some question regarding Oracle Active Data Guard.

Currently, we are looking for an efficient way to get a real-time backup of Oracle Database. We tried to apply Oracle Active Data Guard on our testing servers running Windows Server 2012 R2 and an unlicensed Oracle 11g R2.

We applied these commands on the Physical Standby Server:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

We checked the OPEN_MODE status of the database:

SQL> select database_role, open_mode from v$database;

We got this as a result:

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

We tried to restart the Oracle Service (to simulate sudden power fluctuation or unintentional server shutdown). The OPEN_MODE status becomes:

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

Is it by default that the OPEN_MODE status became READ ONLY if we restart the service? Or do we really need to have a license to fully access Oracle Active Data Guard features so that the OPEN_MODE status will remain in READ ONLY WITH APPLY permanently even if we restart the Oracle server?


texts: How to find the table name from the pg_subscription_rel.srrelid (postgres logical replication)

texts:
I have postgresql logical replication setup between two servers and it seems to be working for the most part, except for one row when running SELECT * FROM pg_stat_subscription; never changes from srsubstate = d.
Is it possible to find the table that this row is referring to using the srrelid?
Or any other way to debug why this is the case?

my_db=# SELECT * FROM pg_subscription_rel;
 srsubid | srrelid | srsubstate |   srsublsn
---------+---------+------------+--------------
  209358 |  208195 | r          | D83/BED60B88
<< clipped data >>
  209358 |  208164 | r          | D83/BEC66E90
  209358 |  207290 | d          |
  209358 |  208436 | r          | D83/BED60AA8
<< clipped data >>
  209358 |  208328 | r          | D83/BED57BF8
(74 rows)

my_db=# SELECT * FROM pg_stat_subscription;
 subid  |  subname  |  pid  | relid | received_lsn |      last_msg_send_time      |     last_msg_receipt_time     | latest_end_lsn |       latest_end_time
--------+-----------+-------+-------+--------------+------------------------------+-------------------------------+----------------+------------------------------
 209358 | s_upgrade | 26880 |       | DA9/C31E5560 | 2020-11-16 16:53:35.66203+11 | 2020-11-16 16:53:35.669512+11 | DA9/C31E5560   | 2020-11-16 16:53:35.66203+11


texts: How do I determine why a MongoDB document is failing validation?

texts:
How do I determine why a MongoDB document insert is failing validation? All I get back is a writeError that says "Document failed validation", which isn't very helpful.

(This happens often, and I'd like to understand how to properly debug these, rather than ask for help with a specific example.)


texts:
I had a very long query running that was causing everything to get stuck. (was running for around 24 hours before being killed)
INSERT INTO tbl_new SELECT * FROM tbl_old

So I killed it. Now I don't really need tbl_new and it was empty before. The query is now stuck at query end stage which I assume means he's doing a rollback. Any way for me to avoid it?

If I DROP TABLE tbl_new will that stop the rollback


texts:
I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order:

IDX_E93438363DAE128B,
IDX_LIST_ID_EMAIL_ADDRESS_DELETED,
IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS,
IDX_LIST_ID_SUBSCRIBED_DELETED_FIRST_NAME,
IDX_LIST_ID_SUBSCRIBED_DELETED_LAST_UPDATED_AT

My query of this table on my medium Amazon RDS instance is taking upwards of 15 minutes:

SELECT * FROM contact
WHERE list_id = '014c7cbe-c124-11e5-b4ea-0a4287b2e8c5'
AND subscribed = 1
AND deleted = 0

Using an EXPLAIN of the query I can see that MySQL is choosing to use the IDX_LIST_ID_EMAIL_ADDRESS_DELETED key, when I'd expect most of the other keys (IDX_LIST_ID_SUBSCRIBED_DELETED_...) to be more optimal.

Should I just trust the engine? Is 17 or so minutes a realistic time to wait for a query on a table this large?


texts: Distribution Server (1) Replicating Source Server (2) To Destination Server (3)

texts:
I'm setting up replication from a source server to a destination server using a third party distribution server.  I've added the source server to the list of publishers on the distribution server (Distributor Properties => Add => Add SQL Server).  When I go to add a new publication on the distribution server, I don't see the source server's databases as options to replicate to the destination server.  Also, if I select the option "Publisher Properties" on the distribution server, to see a list of databases that are checked - either transactional or merge - none of the databases of the source server are listed.

Oddly enough, if the publisher was an Oracle server (it is not), I see the option to add that by entering the server on the distribution server, so I'm confused why I'm not seeing it for SQL Server.

Added (current answer is wrong)

If I do the steps suggested in the current answer, then look at the Job Agent step, what I get is:

-Publisher [SourceServer] -PublisherDB [DB] -Distributor [SourceServer] -Publication [NAME] [Security Related Info]

In addition sp_get_distributor also shows the wrong server as the distributor (SourceServer).

Let me explain again:

The publisher should be the source server.
The distributor should be the distributor server.
Therefore "I've added the source server to the list of publishers on the distribution server" and then creating the publication on the source server does not correctly set up the proper distributor server.

Also, to correct Microsoft's own website at this link, if I follow to the letter what they say, they are flat out wrong on step 4

  Specify a Distributor if distribution has not been configured on the
  server. For more information about configuring distribution, see
  Configure Publishing and Distribution. If you specify on the
  Distributor page that the Publisher server will act as its own
  Distributor (a local Distributor), and the server is not configured as
  a Distributor, the New Publication Wizard will configure the server.
  You will specify a default snapshot folder for the Distributor on the
  Snapshot Folder page. The snapshot folder is simply a directory that
  you have designated as a share; agents that read from and write to
  this folder must have sufficient permissions to access it. For more
  information about securing the folder appropriately, see Secure the
  Snapshot Folder. If you specify that another server should act as the
  Distributor, you must enter a password on the Administrative Password
  page for connections made from the Publisher to the Distributor. This
  password must match the password specified when the Publisher was
  enabled at the remote Distributor. For more information, see Configure
  Distribution.

In 2012, there is no option to specify a distributor.  So, if I go to their link to set one up:

  Right-click the Replication folder, and then click Configure
  Distribution.

There is no Configure Distribution option.


texts: Transactional replication between SQL Server 2014 (Publisher) and SQL Server 2012 (subscriber)

texts:
We need to set up transactional replication between two servers A and B, configs listed below:

Server A (Publisher)

  Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Enterprise Edition (64-bit)
  Single database of approx 1.5 TB( this would be the database to be replicated on server B
  CPU - 4 Intel Xeon CPU E7-8893 v2 @ 3.4GHZ

Server B (Subscriber & Distributor)

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64) Standard Edition (64-bit)
  CPU - 1 AMD Operon 6378 processor 64 bit 2.40 GHz (4 Core) (more RAM and better CPU will be added when moved to better Hyper V server)

I am new to the replication concept so would like you're expertise to confirm:

If we can set up the transactional replication between server A of version 2014 and server B of version 2012. Tried to Google out but could not find the exact documents because may be  am looking somewhere wrong. Please suggest if it can be set up?
We need to implement encryption on server A, but this encrypted data needs not to go on server B as that is SAN encrypted. Is there a way we can do this, if replication can be successfully implemented as asked in point 1?

Thanks in advance


texts:
I am trying to get the index usage statistics in a mongoDB 2.0.9.

I found a similar question in https://stackoverflow.com/questions/44813787/how-to-check-index-usage-in-mongodb but this only applies to versions 3.2 and up.

Any assistance will be very appreciated.


texts: Selecting single row based on a condition

texts:
Consider the following table structure

Table Structure

 Table "public.studenttable"
 Column  |  Type   | Modifiers
---------+---------+-----------
 id      | integer | not null
 uid     | text    | not null
 subject | text    |
Indexes:
    "studenttable_pkey" PRIMARY KEY, btree (id, uid)

select * from studenttable

 id | uid | subject
----+-----+---------
  1 | S01 | Maths
  1 | S02 | Science
  1 | S03 | English
  2 | S02 | English

My objective is to get rows which have only English as a subject which should return id = 2 row only. I have following approaches in mind

1) Return subjects as a string separated by semicolon for particular id and run a loop to match violations.

select id,(array_agg(subject),';')
from studenttable
group by id

2) The second approach is to do a self join on studenttable to make sure that id  = 1 has only one subject.

I am not able to form the final query. Also is there any alternative?


texts:
I am using SQL Server Management Studio.I have tried several max date options to return the latest records and notes but keep getting multiple entries instead of just the latest one.  can you advise where I have gone wrong and suggest better alternative

SELECT     [Pupil ID], [Event Date],[Header Note], [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],[Header Note],[Module ID]
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]

  This gives the error... Msg 306, Level 16, State 2, Line 4

The text, ntext, and image data types can not be compared or sorted, except when using 'IS NULL' or 'LIKE' operator.

So proceeded onto this 2nd attempt and used CAST, this got rid of the 306 error but is still not returning the maximum entry, ie for Pupil id 59775, only 1 line should be returned showing the entry for 2008-01-08

SELECT     [Pupil ID], [Event Date], CAST([Header Note]AS VARCHAR(100))NOTE, [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],CAST([Header Note]AS VARCHAR(100)),[Module ID]
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]

also tried

SELECT     [Pupil ID], MAX(CAST([Event Date] AS DATETIME)), CAST([Header Note]AS VARCHAR(100))NOTE, [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],CAST([Header Note]AS VARCHAR(100)),[Module ID]
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]


texts:
Please help i dont know what i'm doing wrong.

CREATE DEFINER=`root`@`localhost` PROCEDURE `setState`(IN `statename` VARCHAR(100), IN `countryID` INT(11))
    MODIFIES SQL DATA
BEGIN
      SET @statename := `stateName`;
      SET @countryID := `country_idcountry`;

    INSERT INTO `state`(
        `stateName`,
        `country_idcountry`
    )
    SELECT
        `stateName`,
        `country_idcountry`
    FROM
        (
        SELECT
            @statename AS `stateName`,
            @countryID AS `country_idcountry`
    ) AS tmp
    WHERE NOT EXISTS
        (
        SELECT
            stateName
        FROM
            state
        WHERE
            stateName = @statename
    )
    LIMIT 1;
END;

When i execute the statement with

call ccc.setState('Region Maritime', 8);

it returns

Error Code: 1054. Unknown column 'country_idcountry' in 'field list'

But my STATE Field is shown below :

the 'country_idcountry' is a foreign key from the country table.


texts: SQL Vulnerability Assessment Error in PowerShell

texts:
I am running Vulnerability Assessments against my SQL Servers and databases using the PowerShell cmdlets Invoke-SqlVulnerabilityAssessmentScan and Export-SqlVulnerabilityAssessmentScan, however, I am hitting an error on one database.

I receive the error

  Scan size: 50864310 is greater than 10485760 bytes.

The scan JSON file is nearly 50 MB in size, whereas the other scans for databases on this server are < 10 MB in size, which seems to be a limit for the PowerShell cmdlet to export.

SSMS is able to run the scan and export the file without any issues. Is there any way around this scan result size limitation using the PowerShell cmdlets?


texts: Is there a date modified timestamp for steps in a job on msdb?

texts:
I need to figure out which steps have been modified since the beginning of the month. I can find which jobs have been modified, but some of the jobs have 20+ steps, so knowing which specific step was modified would be very useful.

This is where I found the job/step msdb info: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/


texts:
I have a database column with multiple names separated by line breaks like this:

jones, sue
jones, mark

I want to generate a query that keeps the first line and discards the rest, no matter how many lines there are...

I though I was getting close with this:

SELECT
    name1
FROM
    email
WHERE
    name1  REGEXP '.*(\r\n)?'

but not quite...


texts: How to pass a table type with an array field to a function in postgresql

texts:
i have a table called book

CREATE TABLE book
(
  id smallint NOT NULL DEFAULT 0,
  bname text,
  btype text,
  bprices numeric(11,2)[],
  CONSTRAINT key PRIMARY KEY (id )
)

and a function save_book

CREATE OR REPLACE FUNCTION save_book(thebook book)
  RETURNS text AS
$BODY$
DECLARE
myoutput text :='Nothing has occured';
BEGIN

    update book set
    bname=thebook.bname,
    btype=thebook.btype,bprices=thebook.bprices  WHERE id=thebook.id;

    IF FOUND THEN
        myoutput:= 'Record with PK[' || thebook.id || '] successfully updated';
        RETURN myoutput;
    END IF;

    BEGIN
        INSERT INTO book values(thebook.id,thebook.bname,thebook.btype,
        thebook.bprices);
        myoutput:= 'Record successfully added';
    END;
 RETURN myoutput;

    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

now when i call the function

SELECT save_book('(179,the art of war,fiction,{190,220})'::book);

i get the error

ERROR: malformed array literal: "{190"
SQL state: 22P02
Character: 18

i dont understand because i dont see any error in the format of the array, any help?


texts: What's wrong in this SQL Query? I am trying to update a column value

texts:
I would like to know what is wrong in below stored procedure as I am not returning any error but at same time I am unable to update my Watermarkvalue column. it returns NULL.

ALTER PROCEDURE [dbo].[usp_UpdateWatermark]
 @tableName nvarchar(500)

AS
BEGIN

 DECLARE
 /* ============= Variables ============= */
  @watermarkValue  nvarchar(MAX)

  DECLARE
 /* ============= Variables ============= */
  @tabName  nvarchar(MAX)

  Set @tabName ='SELECT '+ @watermarkValue +'= MAX([LOAD_DATE]) FROM '+ @tableName

 Execute sp_executesql @tabName

 UPDATE dbo.WatermarkTable
 SET  WatermarkValue  = @watermarkValue
 WHERE TableName = @tableName
END

Here my WatermarkValue always return NULL. The watermark table has columns as TableName and WatermarkValue and LOAD_DATE is a column in another table in database


texts: sp_Blitz, >1000 plans for CREATE PROCEDURE

texts:
I'm using Brent Ozar's sp_Blitz, and one of its results is this:

Many Plans for One Query
1146 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.

The link in the result has this query:
SELECT q.PlanCount,
q.DistinctPlanCount,
qs.query_hash,
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY q.PlanCount DESC, q.query_hash;

...which shows the queries with the highest number of plans.
When I run this, one of the top results I get (with about 1150 plans for the same query hash) baffles me:

Maybe it's a bit hard to recognize on the screenshot - this is a complete CREATE PROCEDURE definition including comments, like this:
-- =============================================
-- Author:      my username
-- Create date: 14.09.2017
-- Description: blah
-- =============================================
CREATE PROCEDURE [dbo].[spCalcSomeStuff]
    @Orders OrderList readonly
AS
BEGIN

    -- do stuff (see below for more details what the SP does)

END

Plus, they're all the same.
I copied QueryText from multiple rows into text files and made diffs, and they're all 100% identical.
Any idea why this happens?
Our database objects are in source control, so I know that this particular SP was last changed about two months ago.
Even if we would delete and recreate it multiple times a day (which we don't do), I still don't understand why SQL Server creates that many plans for identical queries.

There's nothing special about this SP, except that it's one of the very few we have which uses Table-Valued Parameters.
Here's a simplified version of what the SP does:
create table #tmp
(
    [...]
)

insert into #tmp (...)
select ...
from tbOrders o
inner join @Orders x on o.Col1 = x.Col1 and o.Col2 = x.Col2

-- about 15 updates like this one (but more complex),
-- getting stuff from lots of different tables:
update t
set foo = o.foo
from #tmp t
inner join OtherTable o on t.bar = o.bar

-- and a few very simple updates:
update #tmp set ordertype = 'A' where producttype = 4
update #tmp set ordertype = 'B' where producttype = 2

select * from #tmp

When I run Aaron Bertrand's modified query, it returns the two simple UPDATE statements at the end.
I.e. I still get ~1150 rows for the same query hash, and half of them have this query text:
update #tmp set ordertype = 'A' where producttype = 4

...and the others have this one:
update #tmp set ordertype = 'B' where producttype = 2


texts: Rolling back transaction with trx_mysql_thread_id of 0 create deadlocks on update


texts: mysql 5.6 flushing all dirty pages on ib_logfile rotation creating checkpoint stall

texts:
Server version: 5.6.23-72.1-log Percona

I have been trying to track down why our mysql server is sharp checkpointing and it seems to correlate with rotating of the ib_logfile.

I inherited this Percona 5.6 mysql server and it was pausing for the sharp checkpoint / full dirty flush once a day with innodb_log_file_size of 1GB. I thought perhaps it was insufficient logfile size and bumped it up to 25GB. Now we just get the same behavior less frequently. It was a sort of mystery to me until I was able to verify it happened when the logfile switched.

Mysql 5.5 docs: https://dev.mysql.com/doc/refman/5.5/en/innodb-checkpoints.html: "...when InnoDB starts to reuse a log file, it has to make sure that the database page images on disk contain the modifications logged in the log file that InnoDB is going to reuse. In other words, InnoDB must create a checkpoint and this often involves flushing of modified database pages to disk."

Mysql 5.6 docs omit this bit and say all it should do is fuzzy checkpointing in normal circumstances. https://dev.mysql.com/doc/refman/5.6/en/innodb-checkpoints.html: "InnoDB implements a checkpoint mechanism known as fuzzy checkpointing. InnoDB flushes modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would disrupt processing of user SQL statements during the checkpointing process."

Is there any way to minimize or get out of this behavior if it is unexpected?

| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_buffer_pool_size | 46170898432 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | inserts |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_doublewrite | ON |
| innodb_empty_free_list_algorithm | backoff |
| innodb_file_format | Barracuda |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponential_backoff |
| innodb_io_capacity | 3000 |
| innodb_io_capacity_max | 6000 |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 26843545600 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_lru_scan_depth | 2048 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 50 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 200 |
| innodb_max_purge_lag_delay | 0 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 384 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sched_priority_cleaner | 19 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.23-72.1 |
| innodb_write_io_threads | 8 |


texts: Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

texts:
I need to dig into a logs table with a schema similar to this:
CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

Column data stores a XML text received from a web service in this format:
This is a reduced version
<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

When I try to cast this text to XML I get next error:

XML parsing: line xx, character 48, illegal xml character

It can be solved by removing the <xml> tag, or at least, the encoding attribute.

NOTE: It works fine if there is no special characters like ó, even if I don't remove <xml> tag.

Question
Is there a way to convert it to XML without replacing or removing <xml> tag?
CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>fiddle here
UPDATE
The server collation is: Latin1_General_BIN
But even if I try to change the collation to my usual servers collation, it doesn't work.
SELECT
  id,
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;


texts: Oracle:How to determine the size of available shared memory of operating system while setting MEMORY_TARGET parameter for oracle instance?

texts:
According to the oracle document:"The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer."Oracle document also suggest that we can use the command df –k /dev/shm to get the available shared memory.
I got a little confuse of of shared memory while I am reading the definition of the shmall parameter: This parameter sets the total amount of shared memory pages that can be used system wide , it means that the total amount of shared memory available on the system equals to (shmall*PAGE_SIZE)
So when I am going to set the value of  MEMORY_TARGET, which should I go for, shmall*PAGE_SIZE or  df –k /dev/shm


texts: how to handle so much multiple value strings

texts:
Possible Duplicate:
  Multiple values in lookup fields

i have a table has about 27 fields are just strings
and every one of them have multiple values
for example itemType Field have more than 10 possible values and the user may insert more

i made a tables called properties with those fields (Id, Key, Value) to store those values
but now i'm confused wither i should use just the (Id)s and reference them in the original table (i think it's no good to put 27 relationships) or doing something else which i don't know what is it exactly

any better ideas or is it ok to continue like this

thanks


texts:
I have set up a 2-node PG 9.4 system with BDR. Replication works great.

Now, let's suppose that one of the two nodes needs to be replaced. In fact, let's be more specific. Let's say that the node on which I ran the command to create the group : SELECT bdr.bdr_group_create() has to be replaced.

What is the procedure to do this?

Thanks


texts: Loop gets terminated in case of many SQL Insert Commands

texts:
I am executing SQL Insert commands inside a loop that runs for 1000+ iterations.  The loop gets aborted without any error/exception in between after executing 700+ inserts.

If I comment the DB execute command the loop never breaks but with the execute command sometimes it executes 704 sometime it executes 728. Its very random.

I am facing this problem on MySQL with Joomla by calling a PHP method via Ajax.

It looks complete weird to me. Need your assistance on the below.


texts: Query Amount of entries per column from multiple tables + provide table name

texts:
I have five tables with all having the same column (objart):

I queried for the number of entries and combined the result with UNION, which worked well.

Two questions:

1) Is there a shorter/ better way to do it? Other possibilities than UNION?

2) The selection provides me the "objart"-entry and how many rows of this entry are in the table(s). How can I add the table name from which table the specific "objart" entry is derived? (Up to now I don't know whether a "objart" entry occurs in just one OR in more tables)

SELECT objart, COUNT(*) AS "Number of objects"
FROM "SchemaA".table_1
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".table_2
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".tanle_3
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".table_4
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".table_5
GROUP BY objart
ORDER BY objart;


texts: Reattaching MDF files recovered from RAID 5

texts:
We had been running a SQL Server 2005 server for many years at our company when just recently, we ran into some trouble.  The server was configured with RAID 5 and had one hot spare disk in the array.  One of the disks failed, and then a second one failed shortly thereafter.  Unfortunately we did not have backups of much of this data (please spare me the "should have had backups" comments -- I am aware of the importance of a solid backup plan).

So we are two disks down, we have no official DBA, and the database is inaccessible.  One of our IT personnel attempted to recover the RAID array and we believe the RAID began rebuilding but he was ordered to shut it down because there was concern for potential data loss during the RAID rebuild.

We sent the entire server to a data recovery company.  They spent two weeks working on the server, first by imaging all the drives and then by reconstructing the data from the RAID.

I received a hard drive that contains all the "recovered" files.  This is a collection of mostly database files (MDF) and trans logs (LDF).  Each database that was on the server consisted of only a single MDF and a single LDF.  I do not currently have access to the original server, so I have been attempting to attach these databases to another server running SQL Server 2005.  However, when I try to reattach any of the databases I run into problems.  Some of the various errors I have received are shown below.  I have had no luck restoring any of these files.  I followed the steps outlined in this article, and even then was unable to restore any of the files.

We paid a large sum to the data recovery company for the recovery of the data from this server and I have been tasked with proving without a doubt that the data are or are not useful to us.  I simply don't have the experience to know for certain, so I put the question to you, dba.stackexchange:  Is there any way at all to restore these files?

  SQL Server detected a logical consistency-based I/O error: incorrect
  pageid (expected 1:134; actual 0:0). It occurred during a read of page
  (1:134) in database ID 17 at offset 0x0000000010c000 in file
  'G:\Data\Data201309.mdf'.  Additional messages
  in the SQL Server error log or system event log may provide more
  detail. This is a severe error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

  The header for file 'G:\Data\Data201112_log.ldf' is not a valid
  database file header. The PageAudit property is incorrect. (Microsoft
  SQL Server, Error: 5172)

  G:\Data\Data201005.mdf is not a primary database file. (Microsoft SQL
  Server, Error: 5171)

  An error occurred while processing the log for database 'Data201008'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe9b50f3a; actual: 0x162a0f84). It occurred during a read of page (1:511232) in database ID 17 at offset 0x000000f9a00000 in file 'G:\Data\Data200803.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
  Could not open new database 'Data200804'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)


texts: Do stored procedures have a "signature", and how is that defined?

texts:
I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is SQL Server 2012.

When the stored procedure is a simple select, the third-party tool receives the result set, but when it accesses a temporary table first, it does not.  For example, the following two stored procedures return the same result from SSMS, but only the first can be called successfully from the tool.

What I don't understand is how these two functions are different from an external perspective. They both take the same parameters, and the last query yields the same result.

Where is the stored procedure executed?

--This one works when called both ways
CREATE PROC [dbo].[sp_GetUsers]
AS
    Select Top 1000 userId
    from Users

--This only works from SSMS.
CREATE PROC [dbo].[sp_ComplicatedGetUsers]
AS

    If OBJECT_ID('tempdb..#TMPUsers') IS NOT NULL
    BEGIN
        DROP TABLE #TMPUsers
    END

    Select Top 1000 userId
    INTO #TMPUsers
    from Users

    Select * from #TMPUsers


texts: How to use timestamp column for filtering records in where clause in mysql

texts:
Query :
SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE event_time > ('2016-08-04 10:50:27')
AND user_host LIKE 'vareity_1_1%'
OR user_host LIKE 'db_tvm%'

The query is working but am records which doesn't satisfy the where clause.
Here in the image you can see the results got


texts:
When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error:

Is there a way of simplifying what I am doing? Why do SQL PRINT and SQL EXEC deliver these two very different result sets?

DECLARE @TableName as NVARCHAR(250), @SQL as VARCHAR(MAX);

DECLARE @TableCursor as CURSOR;
SET @TableCursor = CURSOR FOR
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
AND name like 'HISTORY_MasterList_%'
ORDER BY sobjects.name

OPEN @TableCursor;
FETCH NEXT FROM @TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='select '''+ @TableName +''', 0
 Union All
select All ''Server Count'',count(1) from ['+ @TableName +']
 Union All
select All ''Server Cores'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
 Union All
select ''Production Servers'',count(1) from ['+ @TableName +'] where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
Union All
select ''Production Cores'', sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
 where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
 Union All
select ''Production Server Count after filtering out passive/failover servers'', count(1) from
(select  distinct m.ServerName
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Production Server Cores after filtering out passive/failover servers'', sum(convert(decimal(18,0),cores)) from(
       select  distinct m.ServerName, m.Cores
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Non-Prod SQL Instances downgraded to Developer Edition'',count(1) from ['+ @TableName +'] where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod SQL Instance Core Count downgraded to Developer Edition'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']  where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod VMs moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''VM Moved/right sized from Prod Env to NonProd''
 Union All
select ''Non-Prod VMs TO BE moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''Sent for V2V - non prod split''
'

EXEC @SQL

FETCH NEXT FROM @TableCursor INTO @TableName;
END
CLOSE @TableCursor;
DEALLOCATE @TableCursor;


texts: How to get current time in Oracle, and use in interval partition?

texts:
I'm trying to create an interval partitioned table in Oracle where the first partition is bounded to a week from now, but I just can't seem to figure out the syntax.

Here's what I have so far..

CREATE TABLE TRANSACTION
(
....
, TRANSACTION_DATETIME TIMESTAMP(6) NOT NULL
....
)
PARTITION BY RANGE(TRANSACTION_DATETIME)
INTERVAL (NUMTODSINTERVAL(7,'day'))
(PARTITION p_first VALUES LESS THAN (SELECT SYSDATE+7 FROM DUAL));

I've tried that and many other variations, such as..

(PARTITION p_first VALUES LESS THAN (SYSDATE+7));  //OR
(PARTITION p_first VALUES LESS THAN (SYSDATE+7 FROM DUAL));

But none of them seem to work


texts: Required hardware to build a development environment in Sql Server

texts:
We have a bunch of applications that depends on about 10 different databases servers (all sql server) in our network.

We are looking to build a development environment where we could have a mirror of the production databases.

Our plan is to have one server and put all the 10 databases inside it as separate sql server instances. All the 10 databases currently sum up to 1TB of disk space.

We won't have many users connected to these databases and won't do any kind of heavy use, just development.

What kind of hardware do I need to run this and not have a too bad performance? We came up with a Dell machine that is in our budget with 2x Xeon Quad Core + 32GB Ram + 8TB of disk (SATA 7200RPM) configured in RAID. Will that do?

Thanks!


texts:
I prefer index calculation and comparison be executed on the GPU.

I have stumbled upon PGStrom.

It seems one could infer that it will calculate index comparisons, but the claim doesn't seem to be made explicitly, and I stopped assuming years ago.

As an example, would PGStrom calculate this where condition:

SELECT * FROM some_parent, some_child
    WHERE some_parent.bigint_id = some_child.parent_bigint_id

Does PGStrom calculate relational comparisons?


texts: Execute of job failed. See the history log for details

texts:
I am learning SQL Server administration and setting up log shipping for the first time. SERVER1 is primary database and SERVER2 is secondary database. I was able to setup log shipping successfully from SERVER1 and database has been created on SERVER2, however, when i am trying to manually run the Job it is giving me below error:

After checking the logs in text file, i noticed that it is giving me this error:

Initially, SERVER2 was named as SERVER, i thought this might have been the problem, so i rename the server using sproc sp_addserver and sp_dropserver. However, it did not do the trick. Then i noticed that under regedit Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\machines, name was still SERVER so changed it from there to SERVER2 and restarted the machine. Tried running the job manually again it still gave me the same error. Turned off the firewall of SERVER1 completely, issue still persists. Also, under the shared folder on SERVER2, db backup file is not generated. I am wondering now what is this SERVER exactly here in log referring to. Any help or suggestions would be appreciated!!


texts:
I have this table which is used to generate second table with the result:

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

But I need to delete the old data before I make insert. Is there a way with one SQL to delete first the table data starting from some date and them to insert a new one?


texts: Question about MySQL Semisynchronous Replication

texts:
I was researching about MySQL Semisynchronous Replication, my understanding about the workflow is the follow:

Master Receive a successful transaction from a client session.
Master write it to his binaty log
Master wait for rpl_semi_sync_master_timeout milliseconds to receive an acknowledgment from at least one slave
Slave pick this successful transaction, write it to his relay log, flush it to disk
Slave return an acknowledgment to master
Master return successful to his client session.

My question is, at any point, this type of replication ensures that the transaction is executed on slave(SQL_TREAD applies it). Having in mind that it will add a delay on each transaction and in a case of master failure, is not 100% guaranteed that the slave will be consistent with master, what is the benefit(s) of using this type of replication?


texts: Determining if auto stats update is in progress

texts:
In our production system, queries sometimes 'stall'. Whilst stalled, no incrementing resource use (CPU, Reads) is shown in sp_whoisactive, and there is no blocking.

In retrospective diagnosis, we can see that sys.dm_db_stats_properties shows last_updated around the time the query was 'stalled'.

What we would like to do is - when we see a stalled query - then determine what auto stats updates are in progress.

Because we want to do this ad-hoc, and also because we don't want to impact production performance, using profiler is probably not an option for us.

(If there is no way of doing an ad-hoc determination, then maybe we'll have to consider Extended Events, or some other lower-impact pre-emptive tracking).

Our version is 2014, however answers for later versions would also be useful.


texts: Return List of Database Users and Server Logins

texts: How can I return a complete listing of all server logins and all database users across my enterprise? Need to include the remote server name, database name, AD account and SQL users.


texts:
I want to perform a point in time restore on MySQL.

I want to change log_bin value and ensure that log files are created.

I am following this article and am working on a Windows platform.

Log files are not created in the directory I specified? What is wrong?


texts: How do I synchronize parallel imports with COPY?

texts:
I have ~20 tables that get built at the same time but using different psql processes (via bash script) at the first stage with something like:

create table foo_1 as (select ....); -- via psql process 1
create table foo_2 as (select ....); -- via psql process 2
create table foo_3 as (select ....); -- via psql process 3
...
create table foo_20 as (select ....); -- via psql process 20

Once they are built a new stage should start. Is there any good strategy to coordinate this multiple build? I mean, sync the two stages by discovering that all tables are built first from stage 1.


texts: How do I force SQLite to materialize a subquery?

texts:
Because SQLite sometimes evaluates functions like random() more than once if they appear in a subquery, which may be a bug, eg:

select op, op from (select abs(random())%10 as op from (select 1));
3|1

I need an easy way to force SQLite to materialize the subquery so that random() is only evaluated once

this is a simplified example for demonstration - my real world query does not have from (select 1) but selects from a view


texts:
I have some text like xml -code in a field of table. I want to export the data from my field into file without any changes.

I've tried to use a lot of variants but all of them changes the text. The most successful variants were:

COPY (SELECT alll FROM super.a_a ) TO 'D:\alll.txt' CSV ;

But there appeared extra quotes "".

COPY (SELECT alll FROM super.a_a ) TO 'D:\alll.txt CSV QUOTE ' ' ;

But there appeared extra spaces.

In my field I have such date like : name="firsname" and when I use any of varients I get something like that : name=""firstname"" or name=\n\r"firstname\n\r""


texts: Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF

texts: I have no idea where to begin. Can you please help me?


texts: Need to replace only first occurrence of string

texts:
I hope I worded the question right. Here are the specifics:

This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I can solve the problem by inserting the <!--More--> tag into each post at a specific point. I need to do a search and replace with the following parameters:

Search each post_content field for the first occurrence of </em>
Insert the <!--More--> tag right after the </em>

Unfortunately each post has multiple <em> tag pairs, and if a post has more than one <!--More--> tag in it, WordPress just ignores all of them. So I need to so some sort of search and replace, but I have had no success after two days of trying every possible code snippet, SQL query and plugin I could find.


texts:
I had to reinstall the default server instance on one Windows 2008 R2 server.

I checked that the sql groups were cleaned and the registry keys cleaned before reinstalling.

After reinstalling, the sqlagent refused to start.

The only way to start the agent successfully was to add the agent user (domain user) to the group MSSQLUSER$<SERVER_NAME> $MSSQLSERVER

I attempted a repair. all the repair attempts failed with the following message:
 - and removed the user above from the SQL group.

  The following error has occurred:

  The process cannot access the file
  'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll'
  because it is being used by another process.

  Any further update fails due to the repair error (cumulative hotfix update 4).

The SQL server log shows the following:

  Error: 18456, Severity: 14, State: 11
  Login failed for user ''. Reason: Token-based server access validation failed with an infrastructure error. Check for previous
  errors. [CLIENT: ]

The user <user> is a domain user used both for the SQL Server database engine and SQL Server Agent services. The password never expires.

I tried to give the user SQL sysadmin rights, then Windows local admin rights without success. So I'm stuck. Looked over the web and didn't find any solution that match

Also tried to : kill the wmi processes locking sql server agent, and stop wmi services (failed).

Any suggestion would be greatly appreciated.

Christophe


texts: Firebird: show tables command shows no result in different environment

texts:
I'm using firebird with docker with the image jacobalberty/firebird:2.5.8-sc
I use it in local and in the remote server. The problem is the database seems unreadable in the remote server, it says no table in this database even though in the local environment it works fine.
I use isql command inside the container to run the query
root@8978428b8a97:/usr/local/firebird/bin# ./isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> CONNECT "/firebird/data/data.GDB"
CON> user 'guest' password 'guest';
Database:  "/firebird/data/data.GDB", User: guest
SQL>show tables;
There are no tables in this database

I thought maybe the database file is corrupted, but I already uploaded the database file several times using filezilla, and checking the md5sum then compare the hash, it's already the same.
Update
The database is originally from Windows 10 64 bit, it's from another application (probably 32bit application) my employer used. this is the result running gstat /firebird/data/data.GDB -h
Database "/firebird/data/data.GDB"
Gstat execution time Fri Jan  4 08:02:15 2019

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              211656
        Page size               4096
        ODS version             11.1
        Oldest transaction      211394
        Oldest active           211546
        Oldest snapshot         211546
        Next transaction        211549
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      736
        Implementation ID       16
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           May 26, 2010 16:09:32
        Attributes              force write, no reserve

    Variable header data:
        Sweep interval:         20000
        *END*

In Windows I use flamerobin to read the database directly and used it in node.js application using the same user and password, and it works.


texts:
I have this table:

| id | user_id | status |
|  a |       1 |      p |
|  a |       1 |      p |
|  a |       1 |     np |
|  a |       1 |     pp |
|  b |       2 |     np |
|  b |       2 |     np |
|  c |       3 |     pp |
|  c |       3 |      p |
|  d |       4 |      p |
|  d |       4 |      p |

I want to fetch those records who don't have the same status records with group by user_id:

| id | user_id |
|  a |       1 |
|  c |       3 |


texts:
Environment detail:

  SQL Server 2008 R2.

I have applications running on JBoss that access the DB using JDBC driver.

Issue: My online DB server was experiencing high CPU usage.

Using the following query I was able to determine there are 2 queries (Select command) in suspended status (wait type - CXPACKET) for nearly 10 minutes and going on. I believe this is the cause of high CPU usage.

USE MASTER GO

select scheduler_id, runnable_tasks_count, pending_disk_io_count from
sys.dm_os_schedulers where scheduler_id < 255

select qs.percent_complete,qs.session_id, scheduler_id, blocking_session_id,
qs.status, command, wait_time, wait_type, last_wait_type, wait_resource, st.text,
host_name, program_name /* ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE
QS.statement_end_offset END - QS.statement_start_offset )/2 ) + 1) AS statement_text
--, qp. from sys.dm_exec_requests qs left join sys.dm_exec_sessions es on
(qs.session_id = es.session_id) CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
-- CROSS APPLY sys.dm_exec_query_plan(QS.sql_handle) as qp where qs.session_id >= 50
and qs.session_id != @@SPID

SELECT CASE WHEN Requests.sql_handle IS NULL THEN ' ' ELSE SubString (
Statements.text, (Requests.Statement_Start_Offset+2)/2, ( CASE WHEN
Requests.Statement_End_Offset = -1 THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2
ELSE Requests.Statement_End_Offset END - Requests.Statement_Start_Offset ) / 2 ) END
AS StatementText, QueryPlans.query_plan AS QueryPlan, Statements.Text AS Batch_Text,
Sessions.Session_ID, Sessions.Login_Name, Sessions.Host_Name, Sessions.Program_Name,
Sessions.Client_Interface_Name, Requests.Wait_Time, Requests.Cpu_Time,
Requests.Total_Elapsed_Time, Requests.Reads, Requests.Writes, Requests.Logical_Reads,
Requests.Row_Count, Requests.Granted_Query_Memory*8/1024 AS Granted_Query_Memory_MB,
LEN(Statements.text) AS Batch_Text_Length, Requests.Statement_Start_Offset/2 AS
Statement_Start_Offset, CASE WHEN Requests.Statement_End_Offset = -1 THEN
LEN(CONVERT(nvarchar(MAX),Statements.Text))*2 ELSE Requests.Statement_End_Offset END /
2 AS Statement_End_Position, ( CASE WHEN Requests.Statement_End_Offset = -1 THEN
LEN(CONVERT(nvarchar(MAX),Statements.text))*2 ELSE Requests.Statement_End_Offset END -
Requests.Statement_Start_Offset ) / 2 AS Statement_Text_Length FROM
sys.dm_exec_sessions Sessions INNER JOIN sys.dm_exec_requests Requests ON
Sessions.session_id
= Requests.Session_ID CROSS APPLY sys.dm_exec_sql_text(sql_handle) Statements CROSS
APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans WHERE Sessions.Session_ID !=
@@SPID

select * from sys.dm_exec_requests where blocking_session_id != 0

Any suggestions on how I can kill these queries in suspended state?


texts:
I noticed MySQL deadlock errors in my application logs and for investigation, I queried InnoDB status and got the below mentioned Latest
detected deadlock

*** (1) TRANSACTION:
TRANSACTION 19959057532, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 112 lock struct(s), heap size 13864, 1320 row lock(s)
MySQL thread id 39377662, OS thread handle 0x2ad01bbd5700, query id 1863218139 ec2-13-229-237-243.ap-southeast-1.compute.amazonaws.com 13.229.237.243 prod_zefo_rw Sending data
SELECT `sale_products`.`id`, `sale_products`.`created_at`, `sale_products`.`updated_at`, `sale_products`.`product_id`, `sale_products`.`model_id`, `sale_products`.`estimated_original_price`, `sale_products`.`middle_price`, `sale_products`.`sale_price`, `sale_products`.`sale_id` FROM `sale_products` WHERE (`sale_products`.`sale_id` = 4530 AND `sale_products`.`product_id` = '208422') FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6467 page no 7773 n bits 248 index `PRIMARY` of table `sale`.`sale_products` trx id 19959057532 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 19959057530, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
37 lock struct(s), heap size 6544, 1114 row lock(s), undo log entries 1
MySQL thread id 39377661, OS thread handle 0x2acd1ac9d700, query id 1863218141 ec2-54-255-206-98.ap-southeast-1.compute.amazonaws.com 54.255.206.98 prod_zefo_rw update
INSERT INTO `sale_products` (`created_at`, `updated_at`, `product_id`, `model_id`, `estimated_original_price`, `middle_price`, `sale_price`, `sale_id`) VALUES ('2019-01-09 23:02:56.787354', '2019-01-09 23:02:56.787388', '231112', 'sony-32-inch-hd-ready-led-tv_gently-used', 28900, 11999, 10799, 4529)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6467 page no 7773 n bits 248 index `PRIMARY` of table `sale`.`sale_products` trx id 19959057530 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6467 page no 6936 n bits 1224 index `sale_products_sale_id_f698c377_fk_sales_id1` of table `sale`.`sale_products` trx id 19959057530 lock_mode X locks gap before rec insert intention waiting

I can see that transaction 2 is holding an exclusive record lock on page 7773 for which transaction 1 is waiting but there's no mention of transaction 1 holding any lock so how come is it a deadlock?
MySQL version - 5.6

Show create table sale_products:

CREATE TABLE `sale_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `product_id` varchar(100) NOT NULL,
  `estimated_original_price` int(11) NOT NULL,
  `middle_price` int(11) DEFAULT NULL,
  `sale_price` int(11) NOT NULL,
  `sale_id` int(11) NOT NULL,
  `model_id` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sale_products_sale_id_f698c377_fk_sales_id1` (`sale_id`),
  KEY `product_id_index` (`product_id`),
  CONSTRAINT `sale_products_sale_id_f698c377_fk_sales_id1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`)
)


texts: Replica Set in MongoDB by using IPs instead of domains?

texts:
I want to use MongoDB in replica-set mode over 2 servers by IPs.But when I deployed the secondary one on other server, I faced the issues about connecting between 2 instance. For example, the primary located on server 192.168.1.1:27018 with hostname COM1 and the second located on server 192.168.1.2:27018 with hostname COM2. Despite of configuring like this

net:
    bindIp: 0.0.0.0, COM1|192.168.1.1, COM2|192.168.1.2
    port: 27018

But in MongoDB's log, it responses that cant hear from COM1:27018 or can find COM2:27018, I don't know how to map these addresses with these IPs. Please help me, thanks!


texts: Avamar SQL Agent running against live SQL Server databases

texts:
We use a large company as our service provider.

The tech people from that service company are pushing very hard to install something called the Avamar SQL Agent which purportedly will be used to replace my old reliable full, differential, and transaction log backups.  I am told that it constantly monitors the mdf, ndf, and ldf files for any changes and then writes the changed items (pages, ???) somewhere (???).

I am all for new technology once it is proven but I am skeptical of this approach that I will no longer need to take SQL Server native compressed backups (again full, diff, and tlog).

So far I have taken the stand that I am not going to bless this adventure unless and until they can prove to me through testing that it does not foul up my mdf, ndf, and ldf files and that a restore is still possible.  Here again I don’t know what or who would do the restore per se… is it some derivation of SQL Server restore, some Avamar restore, some combination, etc.

Does anyone have any experience with this product?


texts: SQL Server 2016 Express: System databases

texts: As per recommended practice, on a new server that I have created with SQL Server 2016 Express SP1, I am trying to move the system databases OFF the C: drive onto E: and F: drives (the MDF's onto E: and the LDF's onto F:). Is this possible with the EXPRESS version of SQL Server?


texts: Are there any reasons not enable SQLCMD-mode as default in SSMS?

texts:
I don't consider "just to ensure that sqlcmd-mode is not required by a script" a valid reason.

Any things to think of before giving the order to enable this setting to my coworkers?


texts: Good explanation of cascade (ON DELETE/UPDATE) behavior

texts:
I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.

For example, if I have two tables - Parent and Child - with a foreign key on Child that references Parent and has ON DELETE CASCADE, which records trigger a cascade and which records get deleted by the cascade? My first guess would be the Child records get deleted when Parent records are deleted, since Child records depend on Parent records, but the ON DELETE is ambiguous; it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?

I wish the syntax was ON PARENT DELETE, CASCADE, ON FOREIGN DELETE, CASCADE or something similar to remove the ambiguity. Does anyone have any mnemonics for remembering this?


texts: How to query transfers for single source to single destination?

texts:
This should probably be easier than I'm making it, but I'm scratching my head coming up with an efficient way to query the results I want.  I'm working with a table that has a source field and a destination field (along with some other unimportant fields).  What I'm looking for are unique source-destination pairs, but only ones where exactly one source matches to exactly one destination.  The basic rules involved in the selection process are:

One source must match to exactly one destination
One destination can have only a single source
A destination must not be a source for another group
There may be more than one record containing the desired source-destination pair (but we only want to return a distinct pair)

So, given this dataset:

Source   Destination
Grp10    Grp20
Grp11    Grp21
Grp11    Grp22
Grp12    Grp23
Grp12    Grp23
Grp13    Grp24
Grp24    Grp25

I would want the following results:

Source   Destination
Grp10    Grp20
Grp12    Grp23

I've gotten something close with a single query, using multiple levels of SELECT DISTINCT sub-queries joining back on the original table, but the performance began to drop off drastically (> 1 second for less than 200 rows).

I'm wondering if it would more appropriate to do grouping on Source, then, on Destination, store those in temporary tables, and join on those to reduce the data to the results I'm looking for?  Some feedback would be great in case I'm missing some really trivial way to get the results I'm looking for.


texts: SQL-SRS create a report that is evaluated every day

texts:
Is there any functionality in the SQL Server Reporting Services to run a query every day and graph the result or do i need to use a seperate table and cronjob to run the query and save the result plus timestamp?

as an ordinary example i want to monitor some numbers, generated by a query like this select count(id), count(distinct foobar) from T_MYTABLE and show the result per day or even per hour.


texts: PostgreSQL: ... LIMIT 1 much slower than fetching all rows?

texts:
If I fetch only one row, the query takes much longer: 1433 ms vs 23 ms

Is there a work around?

Slow:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
 "modwork_ticket"."email_sender_id" = 'foo@example.com'
 ORDER BY "modwork_ticket"."date_created" ASC limit 1;

--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..542.14 rows=1 width=1146) (actual time=1433.128..1433.129 rows=1 loops=1)
   Buffers: shared hit=1466151 read=4661
   ->  Index Scan using modwork_ticket_date_created on modwork_ticket  (cost=0.43..606714.36 rows=1120 width=1146) (actual time=1433.125..1433
         Filter: ((email_sender_id)::text = 'foo@example.com'::text)
         Rows Removed by Filter: 1705251
         Buffers: shared hit=1466151 read=4661
 Planning time: 2.504 ms
 Execution time: 1433.218 ms

Fast:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
  "modwork_ticket"."email_sender_id" = 'foo@example.com'
  ORDER BY "modwork_ticket"."date_created" ASC;

--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4335.83..4338.63 rows=1120 width=1146) (actual time=23.637..23.794 rows=584 loops=1)
   Sort Key: date_created
   Sort Method: quicksort  Memory: 732kB
   Buffers: shared hit=544 read=4
   ->  Bitmap Heap Scan on modwork_ticket  (cost=25.11..4279.11 rows=1120 width=1146) (actual time=20.479..22.595 rows=584 loops=1)
         Recheck Cond: ((email_sender_id)::text = 'foo@example.com'::text)
         Heap Blocks: exact=538
         Buffers: shared hit=541 read=4
         ->  Bitmap Index Scan on modwork_ticket_email_sender_id_like  (cost=0.00..24.83 rows=1120 width=0) (actual time=20.388..20.388 rows=
               Index Cond: ((email_sender_id)::text = 'foo@example.com'::text)
               Buffers: shared hit=3 read=4
 Planning time: 0.173 ms
 Execution time: 23.974 ms

Version:

PostgreSQL 9.6.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit

I run "VACUUM ANALYZE" before running above queries.


texts: How to handle large sized oracle databases in a windows server

texts:
My Production database is having 188 GB size. The server handling the db is having C Drive with a space of 250 GB.

My database is growing rapidly.I don't think this database is having much size compared to other production databases. I can't simply increase the size of my C drive according to increase of my production db.

Can anyone suggest me the professional way of handling large databases with more than 300 GB size.

Any help appreciated.


texts:
I have created a SSIS package which runs every hour from my Machine. First part of my Package runs a Execute SQL Task and result set (single row, single column) is a comma separated list of Unique Record Ids which have been updated in last hour. (i.e. FJ1,FJ2,FJ3) I capture this result in a variable and when I User script tasks to display the value of this property, it appears as it should be.

I pass this Variable to Execute SQL Task on different ODBC Server using a Stored Procedure

My SQL Statement is as Following

call DeleteRowsById(?)

And then I have assigned my Variable (as SQL_Longvarchar) to Parameter 1 in Parameter mapping Section. (ParameterSize is -1) but it doesn't delete the records from the table. (I ran the SP manually in Workbench and it works as expected) Then I had an idea and as part of my SP I started making entries in a Temp table just to see whats coming up as variable. Insert in the table shows only first character from the variable and which is why it's not deleting any records.

Can someone please point me to the right direction as I can not find why my variable is being truncated.


texts: Backup of Databases not present in Availability Groups

texts:
How can I take backups of databases that are not present in Availability Groups?

Can I use a single maintenance plan for both AG dbs and non AG dbs?

Should the copy_only option be enabled for non AG dbs too? I know Ola Hallengren's script has an option to include non-AG databases, but I want to stick with the in-built plans as of now.

The databases that are not in the AG are on the primary node. Should I create a different maintenance plan for them? All databases are on the primary including the non-AG dbs.

I understand copy_only option allows us not to break the LSN chain. This is a  2 node setup with a primary and secondary replica.

These are vendor based databases, so the business is fine with a backup of at least a day earlier from the backup schedule.

I checked the job history of the non-AG databases. There are only 2 of them, and I see the backup files being copied to the backup drive. But the job history still shows that "The job failed at step 1 - job was invoked" . I am not sure what's wrong. I can see the backup files in the drive.

This is the message I see in the history:

  The job failed.
  The Job was invoked by Schedule 1100 (DailyBackup_NonAAG).
  The last step to run was step 1 (Subplan_1).

I am also adding the creation start and finish days of a database to give you a clear idea:

database_creation_date  backup_start_date   backup_finish_date
2016-12-22 20:31:43.000 2017-04-05 00:08:34.000 2017-04-05 00:34:31.000


texts: Unable to start MongoDB with NUMA interleave

texts:
When I login to Mongo Shell in my production server, running Oracle Linux v7, I see the message:

  WARNING: You are running on a NUMA machine.

To address this, I have used the init script as suggested here : https://github.com/mongodb/mongo/blob/master/rpm/init.d-mongod.

I have restarted mongod as well as the machine, yet it looks like my mongod does not start with the --interleave=all setting and I see the same warning on start up. Any help?


texts: Deleting all the content from the database

texts:
We want to clean a database (delete all the records) with around 25 tables and a size of around 650+ GB in production. We are currently using MS SQL 2016 Standard Edition with AlwaysON HA.
Is truncating all the tables the best option, if not what is the best and fastest way to do this?
What is the potential time it is going to take for cleaning approx 650 GB of data?


texts: Toad For MySql Give error on new connection

texts: Recently i installed Toad For MySQL latest version but when ever i create new connection i got this error.


texts:
We have a table:

user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.)

Now, we have a user search that search across three columns first_name,last_name and work_place.
If a user enters a query like: "Bill Gates" we have to search following conditions
either

First name, last name or work place is "Bill Gates" OR
first name is bill, last name gates
first name bill, workplace gates
..
..
so on

My query is:

SELECT * FROM users WHERE active=1
AND ((first_name = 'Bill Gates' OR last_name = 'Bill Gates' OR work_place = 'Bill Gates')
OR ((first_name = 'Bill' OR last_name = 'Bill' OR work_place = 'Bill')
AND (first_name = 'Gates' OR last_name = 'Gates' OR work_place = 'Gates')) );

and I have a composite index on (first_name,last_name,work_place) but the query is extremely slow. Takes about 3 minutes to execute.

Any idea how to speed this one up ? I have tried unions but its still slow


texts: UPDATE column to MIN of a SELECT result set - what went wrong?

texts:
I've got the following scenario:

I've got

a table of assets with standard columns
a table of hardware assets with extension columns
a history table where all changes to an asset are stored with asset ID, field name, date, new value
a contact table

I want to set a field in hardware assets called "first_usage" to the earliest date of change of the contact field (called "resource_contact") when the new value was a contact with a user-ID (i.e. no technical user or group).

One difficulty was that no ID is stored in the "new value" but  a combined string of first, middle and last name (in case change of contact).

My first try of a query looks like this:

UPDATE  dbo.asset_z_hardware
SET     first_usage = (
        select min(history.com_dt)
from    dbo.nr_com              history,
        dbo.ca_contact          contact
where   history.attr_name = 'resource_contact' AND
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' +
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
        )
)

This seemed to work quite OK, though only some assets were updated with a date (probably I need to check the string-comparison and contacts which changed meanwhile).

But then I realized that I only should update only active assets! The "inactive" flag is in the main asset table called "ca_owned_resource".
Unfortunately, after adding the new condition, the first usage values were all set to the same date!!! How come? I did not find my mistake.

This is the second query with "inactive" flag and link between hardware and main asset:

UPDATE  dbo.asset_z_hardware
SET     first_usage = (
        select min(history.com_dt)
from    dbo.nr_com              history,
        dbo.ca_contact          contact,
        dbo.ca_owned_resource   res
where   history.attr_name = 'resource_contact' AND
        res.inactive = 0 AND res.own_resource_uuid = own_resource_uuid AND
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' +
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
         )
 )

I'd appreciate any help and suggestions for the only-one-date-mistake, also for improving the rest, if any ideas.


texts: Oracle 11g: increasing buffer cache with AMM

texts:
On a 2-node RAC we have enabled Automatic Memory Management. The advisor views indicate that increasing the buffer cache by 4 GB will half the physical reads, and we have 4 GB to give.

How must we do this? By increasing memory_target and letting Oracle grab more memory for the buffer cache? Or by explicitly setting a value for db_cache_size (which now is zero)?


texts:
I have a .bup file which is supposed to be a Progress database file.
My question is, how can I open/restore it and look at the data?