texts: Oracle extents - How can I have so few with this large size?

texts:
Note on duplication:

This is not a duplicate of "Oracle extents - How can I have so many with this small size?"  This is a separate question I entered which happens to be in the same domain.

This is a duplicate of "Oracle extents - how is it possible to get so many? [closed]" - this is a question that was closed because someone thought it was a duplicate of the above question, apparently without reading either question, and despite the fact that neither question had an answer. I see why this Stackexchange is not exactly flourishing.

Question:

This is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents are fine, I read Tom Kyte too).

I have an index with the following characteristics:

Size:        376MB
Initial Ext: 118MB
Next Ext:    1MB
Num Extents: 12

In case it's not apparent, my question is, how can I have something that size, with those extents sizes, and only 12 extents? 376 > 118 + (1 * 11)

FYI: to Justin Cave, yes, it's locally-managed.

Thank you.


texts: How to perform multiple inserts into postgresql database in one query

texts:
I have an orders, recipient and items tables.

I want to insert data into each of these tables based on the response from the previous query.

insert into recipient(name, address) values(name, address);
insert into orders(recipient_id, total_price, total_quantity) values(recipient_id, 2000, 20);
insert into items(order_id, item, price, quantity, total) values(order_id, item1, 230, 2, 260);
insert into items(order_id, item, price, quantity, total) values(order_id, item2, 500, 2, 1000);

I want to execute this in a single PostgreSQL query.

This is what I have tried

insert into users (fullname, email, phone, address, status)
  values ('Mine Essien', 'davidessienshare@gmail.com', '08167462431', 'Andela Epic tower Lagos')
  returning id as recipient_id
)

new_order AS(
  insert into orders(user_id, recipient_id, quantity, total_cost)
  values(1, recipient_id, 5, 9500)
  returning id as order_id
)
new_item as (insert into items(order_id, item, quantity, price, total) values(order_id, 'Pepper soup', 2, 2500, 5000), values(order_id, 'Ishiewu', 1, 5000, 5000), values(order_id, 'Fried yam', 2, 2000, 4000);


texts: Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect

texts:
Scenario
We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment.

From:
SQL2008R2 Standard/Windows Server 2008R2 - Primary Server/Secondary Server (Windows Fail-over Cluster) & Reports Server (Transactional Replication).

To:
SQL2019 Standard/Windows Server 2019 - Primary Server/Secondary Server (Always-On) & Reports Server (Transactional Replication)

Backups from 2008R2 were restored on 2019 and compatibility level set to latest. Always-On setup has gone relatively smooth and initial testing against current application has shown no issues with compatibility.

The issue is purely with setting up Transactional Replication. There are 2 databases that require replication, one publication each with the primary as the publisher and distributor.

The Issue
During the initialization of the subscription for each DB on the reports server it runs fine until it gets to the point of creating functions and produces the errors below.

1st Database:

  Message: The option "INLINE=ON" is not valid for this function. Check the documentation for the constructs supported with INLINE option in a function.
  Command Text: CREATE FUNCTION [dbo].[f_clienttels - Mirror Copy ce2d3663eb494f3589bd5000dad1bf1f](@ClientID [int])
  RETURNS varchar WITH INLINE = ON, EXECUTE AS CALLER
  AS
  BEGIN ......

2nd Database:

  Message: An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
  Incorrect syntax near ')'.
  Command Text: CREATE FUNCTION [dbo].[GetGroupAndDescendantGroupsSelective - Mirror Copy 46f329d5eed444428f45b052f07c7ea8](@GroupId [int])
  RETURNS TABLE WITH INLINE = ON
  AS
  RETURN (
          WITH GroupsCTE AS ( ........

They are both different errors but i believe both are to do with the "INLINE=ON" option, this option is not present in either of those functions, none of our functions use the inline option explicitly, if you remove those articles from the subscription it just gives the same error on the next function (CTE error if the function starts with a CTE and the INLINE=ON error if it does not).

So it appears replication is inserting "WITH INLINE = ON" to functions prior to replicating and then erroring on the addition it has made.

I have patched all instances to the latest CU4 update 15.0.4033.1, tested those functions on both servers (which work), validated all functions/procs within the database and all are fine. In the current 2008R2 environment I had to recreate the publication a couple months back and didn't get these errors. As a work around for now I'm manually creating functions at the subscriber and removing all function articles from the publication.

Any help with a resolution to this would be greatly appreciated, the only reference to this error (from 1st DB) is mentioning that it's undocumented (link below) and i could not find any other forum posts mentioning it.

  16203 – The option “INLINE=ON” is not valid for this function. Check
  the documentation for the constructs supported with INLINE option in a
  function.

From: Brent Ozar - What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

We don't have an active support contract with Microsoft but are trying to reach them via the supplier that provided the licences so I will give an update here if they get back to us.

I can provide more information if needed.


texts: Newly built MySQL Galera Cluster does not start up in CentOS 7

texts:
I am setting up a 3 node MySQL Galera Cluster lab environment. All 3 nodes are CentOS 7 and followed the steps in below blog to setup mysql-wsrep-5.7

  https://www.dreamvps.com/tutorials/install-mysql-galera-cluster-centos-7/

After setting up I am starting the first node by executing:

>/usr/bin/mysqld_bootstrap

But MySQL service fails to start with below error in log:

2019-02-04T13:13:10.053481Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out) at gcomm/src/pc.cpp:connect():158
2019-02-04T13:13:10.053494Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out)
2019-02-04T13:13:10.053635Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'galera_cluster' at 'gcomm://192.168.50.50:3306,192.168.50.51:3306,192.168.50.52:3306': -110 (Connection timed out)
2019-02-04T13:13:10.053647Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2019-02-04T13:13:10.053652Z 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.50.50:3306,192.168.50.51:3306,192.168.5.52:3306) failed: 7
2019-02-04T13:13:10.053661Z 0 [ERROR] Aborting

Form the log it is clear that it is not starting up as the first node in the cluster as it should do, rather it is trying to establish connection to other nodes.

I also checked /var/lib/m.ysql/grastate.dat, safe_to_bootstrap is already set to 1. But still I don't know why I get this behaviour?

How should I start up the first node in my MySQL Galera Cluster?


texts: How can I convert the first 100 million positive integers to strings?

texts:
This is a bit of an diversion from the real problem. If providing context helps, generating this data could be useful for performance testing ways of processing strings, for generating strings which need to have some operation applied to them within a cursor, or for generating unique, anonymous name replacements for sensitive data. I'm just interested in efficient ways of generating the data within SQL Servers, please don't ask why I need to generate this data.

I'll try to start with a somewhat formal definition. A string is included in the series if it only consists of capital letters from A - Z. The first term of the series is "A". The series consists of all valid strings sorted by length first and typical alphabetical order second. If the strings were in a table in a column called STRING_COL, the order could be defined in T-SQL as ORDER BY LEN(STRING_COL) ASC, STRING_COL ASC.

To give a less formal definition take a look at alphabetical column headers in excel. The series is the same pattern. Consider how you might convert an integer to a base 26 number:

  1 -> A, 2 -> B, 3 -> C, ... , 25 -> Y, 26 -> Z, 27 -> AA, 28 -> AB, ...

The analogy isn't quite perfect because "A" behaves differently than 0 in base ten. Below is a table of selected values that will hopefully make it more clear:

╔════════════╦════════╗
║ ROW_NUMBER ║ STRING ║
╠════════════╬════════╣
║          1 ║ A      ║
║          2 ║ B      ║
║         25 ║ Y      ║
║         26 ║ Z      ║
║         27 ║ AA     ║
║         28 ║ AB     ║
║         51 ║ AY     ║
║         52 ║ AZ     ║
║         53 ║ BA     ║
║         54 ║ BB     ║
║      18278 ║ ZZZ    ║
║      18279 ║ AAAA   ║
║     475253 ║ ZZZY   ║
║     475254 ║ ZZZZ   ║
║     475255 ║ AAAAA  ║
║  100000000 ║ HJUNYV ║
╚════════════╩════════╝

The goal is to write a SELECT query that returns the first 100000000 strings in the order defined above. I did my testing by running queries in SSMS with the result set discarded as opposed to saving it to a table:

Ideally the query will be reasonably efficient. Here I'm defining efficient as cpu time for a serial query and elapsed time for a parallel query. You may use whatever undocumented tricks that you like. Relying on undefined or non-guaranteed behavior is okay as well but it would be appreciated if you call that out in your answer.

What are some methods of efficiently generating the data set described above? Martin Smith pointed out that a CLR stored procedure probably isn't a good approach due to the overhead of processing so many rows.


texts: Setting READ UNCOMMITTED when reading DMVs

texts: I've seen several people call SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before reading system DMVs. Is there ever any reason to do this, assuming you aren't mixing calls to DMVs and tables in the same transaction?


texts:
I'm trying to reset my lost password for Postgres v9.1 (windows 7) like written at https://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F  :
1. edit pg_hba.conf to allow trust authorization temporarily

In pg_hba.conf I've put :

local all all trust
host all all 127.0.0.1/32 trust

2. Reload the config file (pg_ctl reload)

Done. I have also restarted the Postgres service (via services.msc)
3. Connect and issue ALTER ROLE / PASSWORD to set the new password

Then I execute 'psql.exe' but it asks me for a password !
Why ?
Thanks for your help.


texts: Is it good to create new indexes for better performance - SQL Server

texts:
Hi I have a SQL Stored Proc that used to take 45 seconds to execute.

Then I run the Execution Plan under SQL and saw some high cost steps:

Then I right click that and select:

And finally I create the indexes that the Execution Plan recommend me.

Now the Query takes 2 seconds that is acceptable.

Is that right what I did? Does it come with extra cost?

  Any advice is well appreciated


texts: Network Packet Payload Error when Trying to Create Distributed Availability Group

texts:
I am attempting to create a distributed AG on SQL 2016 SP1, but keep getting the following errors.  I have tried this in a VirtualBox (local desktop) and actual VMWare virtual environment but getting the same errors each time.

The AGs creates successfully on both WFCs, but when I try to "ALTER AVAILABILITY GROUP [Dist AG Name] JOIN" on the primary replica of the 'remote' AG, the command completes successfully, but I immediately get this error in the log:

  A connection timeout has occurred while attempting to establish a
  connection to availability replica 'AG1' with id
  [FCAA4083-D6B8-1BAC-9E8C-6AECE34513E6]. Either a networking or
  firewall issue exists, or the endpoint address provided for the
  replica is not the database mirroring endpoint of the host server
  instance.

On the primary replica of the 'local' AG, I start getting these errors in the log repeatedly:

  Length specified in network packet payload did not match number of
  bytes read; the connection has been closed. Please contact the vendor
  of the client library. [CLIENT: 192.168.56.104]

These are the bytes that show up in Event Viewer event:

0000: AC 45 00 00 14 00 00 00   ¬E......
0008: 14 00 00 00 41 00 47 00   ....A.G.
0010: 31 00 53 00 45 00 52 00   1.S.E.R.
0018: 56 00 45 00 52 00 31 00   V.E.R.1.
0020: 5C 00 41 00 47 00 31 00   \.A.G.1.
0028: 49 00 4E 00 53 00 54 00   I.N.S.T.
0030: 31 00 00 00 00 00 00 00   1.......

In both environments, I am only dealing with a single subnet and a single domain.  I've also tried removing all special characters from VM hostnames, instance names, AG names, and listener names during troubleshooting.

Wondering if anyone has ran into this before.  I don't see anything in the SP2 release notes that suggests its a bug, but I'm willing to try an SP2 upgrade if anyone thinks its worthwhile. (Unfortunately I am not in a position to be able to upgrade my prod environment for some time which is why I haven't tried it yet.)

I can also post full endpoint/AG/listener creation scripts if anyone is willing to try to reproduce or provide troubleshooting tips.

Thanks!


texts:
I have database with log backup every 15 minutes.

I believe some data in one table was changed 10 hours ago.

Is there any way I can find that query and located changed/updated data in log backup file?
If not is there any other way?

TNX!


texts: How to check the last time a table has been modified?

texts:
I regularly backup tables individually via mysqldump. To avoid unnecessary backup, I would like to check if a table has been modified since the last backup. By "modified", I mean any change (INSERT, UPDATE, DELETE, ALTER, ...).
Using the schema, we can get UPDATE_TIME like
SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'db' AND TABLE_NAME = 't1'

but that's not enough.
A practical way is to check the last modified time of /var/lib/mysql/t1.ibd via common Linux commands such as stat, ls, etc. The problem is that I should give the script running user permission to access /var/lib/mysql, which I prefer not to do so for security reasons.
Is there any way to check the last time a table or t1.ibd has been modified through MySQL (without giving permission to another user for accessing /var/lib/mysql)?


texts:
My table is simple:

CREATE TABLE `od_form_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `formid` int(11) DEFAULT NULL,
  `items` mediumtext,
  `template` mediumtext,
  `draftid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

And it only contains 180 rows:

mysql> select count(*) from od_form_items;
+----------+
| count(*) |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)

The longest string is just 26314 bytes:

mysql> select length(items) as a from qiban.od_form_items order by a desc limit 1;
+-------+
| a     |
+-------+
| 26314 |
+-------+
1 rows in set (0.01 sec)
mysql> select length(template) as a from qiban.od_form_items order by a desc limit 1;
+------+
| a    |
+------+
| 6556 |
+------+
1 rows in set (0.01 sec)

But in mysql slow_log, it took over 2 seconds to select all records:

mysql> select start_time, query_time, sql_text from mysql.slow_log order by start_time desc limit 10;
+---------------------+------------+--------------------------------------------+
| start_time          | query_time | sql_text                                   |
+---------------------+------------+--------------------------------------------+
| 2017-11-24 10:34:40 | 00:00:02   | SELECT * FROM `od_form_items`              +---------------------+------------+--------------------------------------------+
1 rows in set (0.01 sec)

How should I optimize it? What mysql parameter should I check? Thanks!


texts: MySQL: Is there a better way to do this query?

texts:
I'm not sure if this is the most appropriate place for this question, but I thought I'd give it a shot.

The scenario is wanting to retrieve records from a table where a column is null, but if an exact match is found, preference that row instead.

Here's the table structure:

CREATE TABLE TABLE_A
(
  id INT,
  S_ID INT,
  C_ID INT
);

CREATE TABLE TABLE_B
(
  S_ID INT,
  C_ID INT
);

INSERT INTO TABLE_A VALUES(1, 10, NULL);
INSERT INTO TABLE_A VALUES(2, 10, 50);
INSERT INTO TABLE_A VALUES(3, 12, NULL);

INSERT INTO TABLE_B VALUES(10, 50);
INSERT INTO TABLE_B VALUES(10, 60);
INSERT INTO TABLE_B VALUES(12, 50);
INSERT INTO TABLE_B VALUES(12, 60);

When using a c_id of 50, I'd expect records 2 and 3 to come back. If I used c_id of 60, I'd expect records of 1 and 3 to come back.

At the moment, I have this query to solve the issue, but I'm positive there is a better way to do it, so I'm asking for ideas :)

SELECT ca.*
FROM TABLE_B AS clc
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = 50)
WHERE (
  ca.C_ID IS NULL AND ca.S_ID NOT IN (
     SELECT S_ID FROM TABLE_A WHERE C_ID = clc.C_ID
  )
) OR (
  ca.C_ID = clc.C_ID
);

Unfortunately, the tables have no indexes, and no foreign keys. The table structure cannot be changed (legacy).


texts:
How do I write a query which takes the data from a "show" query and manipulate it?

In this specific instance, I need just one of the fields from "show slave status".

How do I do this?

  SELECT Slave_IO_State FROM (show slave status);

OR, how do I find out the underlying query for a "show" type command? I vaguely recall doing this in the past, but can't find any reference to it. Possibly an option in the mysql client? I tried logging the queries run, but that just showed the "show slave status".


texts: TEMPDB Round Robin is not working!! It is filled up

texts:
I have a 16 Core CPU processor and I have created 8 tempdb datafiles with initial size as 2,000 MB with 10% growth restricted to 10,000 MB. The log file is unrestricted and it is in simple recovery mode.

The tempdb is filling up the space, it is not going through the round robin face. Could you please help me here.


texts:
I will have around 600 GB of data in single table with multiple columns in MySQL. Basically this table will store user events from application.
Indexes are there on required columns.

Question: If I shard the data based on some key which will be included  in every  query,
insertion and selection will happen on limited data set. Say I have 3 nodes  and data is redistributed equally on all 3 nodes with each shard of 200 GB.
Now based on shard key in query, I will send it to specific DB only.I think it will improve the search performance. Is that right ?

Constraint : Issue is I have single node. If create three shards(of 200 GB each) on single node , will it help in search performance ?

Can MongoDB(instead of MYSQL) help here with same approach ?


texts: MYSQL query to return most recent entry as long as it is equal to XXX

texts:
OK I'll try and explain this as best as I can as I couldnt really fit it into the title.
Let's say I have a database like so:
+----------+--------------+---------------+---------+
| Agent    | Customer ID  |    date       | Answer  |
+----------+--------------+---------------+---------+
| Jon      | 1            | 2017-05-08    |     Yes |
| Jon      | 2            | 2017-05-01    |      No |
| Jon      | 1            | 2017-03-05    |   Maybe |
| Jon      | 4            | 2017-03-10    |   Maybe |
| Jon      | 2            | 2017-03-10    |   Maybe |
| Jon      | 3            | 2017-03-10    |   Maybe |
+----------+--------------+---------------+---------+

I want to create a query that searches all of Jons entries, where he has selected "Maybe" for a customer, and where "maybe" is his most recent entry for said customer. Does this make sense? So out of the above table I only want to return the results for customer ID 3 and 4.
| Jon      | 3            | 2017-03-10    |   Maybe |
| Jon      | 4            | 2017-03-10    |   Maybe |

I dont want records 1 and 2 because there is a more updated entry of "yes" and "no". I simply want it to check all of Jon's entries where the customer is still undecided, and there has been no new record added to state "yes" or "no".
I've tried using MAX(date) and then running WHERE clauses of "agent=xx" and "answer=xx" however Max does not return the entire row for the most recent entry. Just the value. It appears I am in way over my head here and I don't want to resort to grabbing data from the table and sorting through it in PHP. I'm sure there is a way to write a query that does what I am looking for?


texts:
I generated solrschema with dsetool on a table name seriesdata (its key is composed by seriesmetadata_id, initialtime; two decimal field).
These fields are defined as DecimalStrField but they are numeric valued (I have to execute range selection). I tried to change schema defining these fields as:

<fieldType class="org.apache.solr.schema.TrieLongField" name="TrieLongField"/>

but I receive this error:

  [cassandra@bigdatalin-03 ~]$ dsetool reload_core timeseriesks.seriesdata schema=schema_data.xml solrconfig=solr_config_data.xml reindex=true -l cassandra -p cassandra
      org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: com.datastax.bdp.search.solr.CassandraIndexSchema$ValidationException: Mismatch between Solr key field [seriesmetadata_id] with type {TrieLongField{class=org.apache.solr.schema.TrieLongField,analyzer=org.apache.solr.schema.FieldType$DefaultAnalyzer,args={class=org.apache.solr.schema.TrieLongField}}] and Cassandra key alias [seriesmetadata_id] with type [decimal]
              at org.apache.solr.client.solrj.impl.HttpSolrServer.executeMethod(HttpSolrServer.java:665)
              at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:303)
              at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:294)
              at com.datastax.bdp.tools.SearchDseToolCommands.createOrReloadCore(SearchDseToolCommands.java:559)
              at com.datastax.bdp.tools.SearchDseToolCommands.access$200(SearchDseToolCommands.java:59)
              at com.datastax.bdp.tools.SearchDseToolCommands$ReloadCore.execute(SearchDseToolCommands.java:209)
              at com.datastax.bdp.tools.DseTool.run(DseTool.java:126)
              at com.datastax.bdp.tools.DseTool.run(DseTool.java:51)
              at com.datastax.bdp.tools.DseTool.main(DseTool.java:186)

Why?
Thanks a lot!


texts:
I have an application that (mostly) uses only the very latest data available for a certain identifier (may consist of multiple columns/keys). However, these data may be bad or implausible. In these cases, it has to access the history of the entry to verify that value or calculate an alternative.

For the second use case I created an sorted index identifier..., time.
However, in my mind this index does not really fit the main use case, because it still have to consider n values (with different times) for each identifier.

Is there a way in mongodb to create an index on something like identifier..., $max(time)? Or isn't that necessary at all because mongodbs internal query optimization is smart enough to only look at the first/last value of the index?

Environment:

MongoDB 4.0.1
Java Applications
I have plenty of (big) collections that might need this logic (many TB of data in total)
New data are added often; roughly one entry per identifier per minute, sometimes multiple entries per second
Reads occur in roughly fixed intervals (15s) for fixed subsets of ids each (only the readers knows which identifiers he uses); only the latest data are relevant
Readers might do a cold (re-)start needing to fetch the history to better predict the future/understand the current state (also happens when the reader considers the input data as logically implausible).
Network/processing sometimes introduces delays/shuffled input values.

Alternatives considered:

Use separate tables: One for live data, one for historic data

Decreases write performance as I have to write in two collections
Requires some kind of transactional behavior, as I have to make sure that entries will always be added to both collections.
Requires additional logic that makes sure that only the latest values are in the live table. Entries might change order or might arrive multiple times due to network/processing latency.

Use separate tables with some kind of insert hook

Decreases write performance as I have to write in two collections
Introduces additional delay due to the hook processing time
Requires a new tool and thus increases complexity
Requires additional logic that makes sure that only the latest values are in the live table. Entries might change order or might arrive multiple times due to network/processing latency.
Sync/hook failures are hard to detect

Use of some kind of cache (redis)

Requires a new tool and thus increases complexity
Requires some kind of transactional behavior, as I have to make sure that entries will always be added to both tools.
Requires additional logic that makes sure that only the latest values are in the live table. Entries might change order or might arrive multiple times due to network/processing latency.

EDIT

Example:

sensor_id | time    | value
1         | 12:15   | 5.12 <-- I don't need this value or any of its predecessors
1         | 12:16   | 5.15 <-- I need this value
3         | 12:16   | 1.23 <-- and I need this value
4         | 1998 AD | 4200 <-- Not needed
4         | 1999 AD | 5000 <-- but I need this value (even if is years old)


texts: Counting on Two Different Columns in the Same Table

texts:
I run a pool league and have kept stats in Excel. I want to automate via a website and MySQL will be  my primary data analysis tool, though my knowledge there is only at the beginning of a long journey.

I have 2 tables (only showing relevant tables and relevant fields)

users:
--------------------
id            BigInt
display_name  VarChar(250)

match_results:
---------------------
id            BigInt
winner_id     BigInt
loser_id      BigInt

I want to produce a result set to show as a table of standings, like this:

NAME          WINS     LOSSES
-------------------------------
User2dn        12         7
User1dn        11         8
User5dn        10         8
etc.

I can manage the ORDER BY

I have other queries where I have successfully joined these two tables to get the display names for both winner_id and loser_id by using 2 different aliases for the same user table and that worked great, but I'm totally baffled here.

What I really need is more complicated than this, since I need to report from multiple other columns in match_results, the max of high_run (as a winner or loser) and bonus points which will be a sum irrespective of winner or loser status. I wanted to keep this question as simple as I could so I could understand it, and then build up the additional complications later.  Hope that's OK.


texts: How to update a (very) large table without locking in MySQL

texts:
I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table.

Unfortunately, whoever designed our schema didn't think things through properly, and opted to use usernames to represent this relation, instead of the numerical ids for the user records. As things progressed (like they usually do), our usernames are no longer a valid, unique representation of a player so I need to convert these relations to use numerical ids.

Adding the fields without locking was easy thanks to Percona Toolkit, which offers pt-online-schema-change that can ALTER on a live table. Populating the tables, however might be trickier.

The tables looks like this (I've stripped the creates of non-relevant fields), with the two unpopulated fields being player_id and target_id:

CREATE TABLE `player_targets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player` varchar(20) NOT NULL,
  `player_id` int(10) unsigned DEFAULT NULL,
  `target` varchar(20) NOT NULL,
  `target_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=58000000 DEFAULT CHARSET=latin1;

CREATE TABLE 'player_uuids' (
  `id`int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=600000 DEFAUL CHARSET=latin1;

I was planning on populating the two new fields with a query like this one:

UPDATE player_targets t
INNER JOIN player_uuids u1
  ON u1.username = t.player
INNER JOIN player_uuids u2
  ON u2.username = t.target
SET
  t.player_id = u1.id,
  t.target_id = u2.id
WHERE
  t.player_id IS NULL
  OR t.player_id IS NULL;

With the table storing relations being MyISAM, my interpretation of the documentation is that the UPDATE-query will lock the table until it's finished with all the rows. As the table is large, this will likely not work very well in a live environment.

What would the best approach for this be? writing a script to iterate over batches of relations? Changing the table engine to InnoDB (the table is read-heavy, which I believe is the reason for it being MyISAM)?


texts:
I have the following query that outputs some good information from MDW data collectors regarding database space usage. I am wondering with the following columns (db size, reserved space, unused space, unallocated space, collection date) how can i properly calculate database growth trend? I am trying to keep whether a db shrinks or grows into consideration so I cannot just take "max size" and go from that. Here is the full query so far...

DECLARE @ServerName VARCHAR(MAX);
DECLARE @SelectedDatabaseName VARCHAR(MAX);
DECLARE @snapshot_id INT;

SELECT TOP 1
        @snapshot_id = snapshot_id
FROM    ( SELECT DISTINCT TOP 100
                    d.snapshot_id
          FROM      snapshots.disk_usage d ,
                    core.snapshots ss
--WHERE ss.instance_name = @ServerName
--AND ss.snapshot_id = d.snapshot_id
ORDER BY            d.snapshot_id DESC
        ) AS q
ORDER BY snapshot_id ASC;

SELECT  database_name ,
        ss.instance_name ,
        CONVERT (DATETIME, SWITCHOFFSET(CAST (d.collection_time AS DATETIMEOFFSET),
                                        '+00:00')) AS collection_time ,
        d.snapshot_id ,
        ( ( CONVERT(DEC(15, 2), d.dbsize) + CONVERT(DEC(15, 2), d.logsize) )
          * 8192 / 1048576.0 ) AS 'database_size_mb' ,
        'reserved_mb' = ( d.reservedpages * 8192 / 1048576.0 ) ,
        'data_mb' = CONVERT(DEC(15, 2), d.pages) * 8192 / 1048576.0 ,
        'index_mb' = ( d.usedpages - d.pages ) * 8192 / 1048576.0 ,
        'unused_mb' = ( ( CONVERT(DEC(15, 2), d.reservedpages)
                          - CONVERT(DEC(15, 2), d.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'unallocated_space_mb' = ( CASE WHEN d.dbsize >= d.reservedpages
                                        THEN ( CONVERT (DEC(15, 2), d.dbsize)
                                               - CONVERT (DEC(15, 2), d.reservedpages) )
                                             * 8192 / 1048576.0
                                        ELSE 0
                                   END )
FROM    snapshots.disk_usage d ,
        core.snapshots ss
WHERE   --database_name =@SelectedDatabaseName
/*AND*/ d.snapshot_id >= @snapshot_id
--AND ss.instance_name = @ServerName
        AND d.snapshot_id = ss.snapshot_id
ORDER BY d.database_name ASC ,
        collection_time;

Hopefully at the end of it I will be able to see in a year from now how much every instance, database and server grows per year.


texts: OLAP cube designing: runtime generated measures

texts:
I'm facing a requirement that I'm not entirely sure that can be achieved by pure OLAP designing (anyway, my OLAP knowledge is SO small).
I have a dimension table such as
DROP TABLE IF EXISTS TMP_DEBUG_OLAP_44_BUILDING;
CREATE TABLE TMP_DEBUG_OLAP_44_BUILDING AS
SELECT 1 AS building_id,'Building 1' AS building_name
UNION ALL
SELECT 2 AS building_id,'Building 2' AS building_name
;

and a table fact (fact-1) such as
DROP TABLE IF EXISTS TMP_DEBUG_OLAP_44_FACTS;
CREATE TABLE TMP_DEBUG_OLAP_44_FACTS AS
SELECT 1 AS building_id,10 AS budget_amount,11 AS estimate_amount
UNION ALL
SELECT 2 AS building_id,12 AS budget_amount,13 AS estimate_amount
;

and a factor-conversion fact table (fact-2) such as
DROP TABLE IF EXISTS TMP_DEBUG_OLAP_44_FACT_FACTORS;
CREATE TABLE TMP_DEBUG_OLAP_44_FACT_FACTORS AS
SELECT 'DK' AS country_id,1.0 AS FACTOR
UNION ALL
SELECT 'MX' AS country_id,1.5 AS FACTOR
;

I've been asked to generate a measure per tuple [Measures].[Budget amount]_vs_country, so that it adds, per tuple in fact-2, a measure, multipling [Measures].[Budget amount] * factor_value.
Example: fact-table-1 first row is building_id=1, I need 3 measures: [Measures].[Budget amount], budget-amount1.0(DK) and budget-amount1.5(MX).
Now, if another country is added into the tables, there's a new tuple, hence, a new measure.
Is there any way this could be achieved without modifying the cube?
Alternatively, if there was a way to pass input parameters to get a single measure, with the factor applied to the chosen country, shall suffice. Yet, my explotation platform is JPivot, which lacks the ability of user-input.
Thanks and regards!


texts: How to select bytea data as binary data, inserted as hex-encoded from binary

texts:
The post How to insert (file) data into a PostgreSQL bytea column was helpful.

However, when I select these columns, I get the same hex encoded data.

For instance:

> select substring(smallblob,0,64) from longdata where blobi d=1;
\xd0cf11e0a1b11ae1000000000000000000000000000000003b000300feff09000600000000000 00000000000010000006a0000000000000000100000670000
(1 row)

How can I SELECT this data in binary format?  CAST' andCONVERT` do not appear to support a 'binary' option.


texts: How to create oracle user for more than one version

texts:
I have  server under RHEL 6.3 installed on that server Oracle 11gr2 and 12c, I want to create user lets say Ahmad but I want this user to be sysdba on both servers, for single server I edit .bach_profile to be like the following:

11g:

ORACLE_HOSTNAME=oracledev; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/oracle11gr2/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

PATH=$PATH:$HOME/bin
unset USERNAME
cd $ORACLE_BASE
export PATH

and if I want to make him sysdba for 12c I use the following:

export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=oracledev
export ORACLE_UNQNAME=cdb1
export ORACLE_BASE=/oracle12c/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=cdb1

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

My question is it possible to marge those parameters together without making a conflict, if yes  please advise


texts: How to refresh reference to objects in Visual Studio SSDT?

texts:
I have an issue related to missing reference to tables or views which exist in Visual Studio SSDT. For example I have two SSDT projects. One for database X second for database Y. In project X I've created a reference to database Y and a stored procedures to tables from project Y. Everything worked well till last time I've created next table "abc" in Project Y. Now when I'm trying to create a stored procedure in Project X with this "abc" table from Project Y I cannot resolve its name. On the dropdown list in the Project X (FROM clause) I can see all tables from project Y except the last one and I get the blue underscore and unresolved reference warining.

Is there a way to refresh this dropdown list with tables somehow (maybe similar like I'm refreshing IntelliSense cache in SSMS ?


texts: What Method / Formula does a Nested Loops Operator use for row estimation?

texts:
The following, simple query in AdventureWorks:
SELECT  *
FROM    Person.Person p
        JOIN HumanResources.Employee e
            ON p.BusinessEntityID = e.BusinessEntityID

Gives the following execution plans:
New estimator plan
If I look at the above plan, I can see the index scan and index seek both (correctly) estimate 290 rows, however, the estimated loops operator that joins the two, estimates 279 rows.
Old estimator
The old estimator also correctly guesses 290 rows out of both the seek and the scan but the nested loops estimates 289 rows which in the case of this query is a better estimate.
Is it true then that in the case of the new CE the optimizer estimates that when it is joins 290 rows from the index scan and 290 from the index seek, there will be 11 rows that do not match?
What method / formula does it use to make this estimate?
Am I correct in saying whatever said method is, that it has changed from the earlier CE version as that has made a different estimate?
I realise the "bad" estimate of the new CE is not significant enough to detriment performance, I am just trying to understand the estimators processing


texts: Name of the product price, line-item database pattern?

texts: What is the database pattern called where (for example) the current price of a product is stored in one table, and when someone makes a purchase the current price of the product is copied to a line item table, and is redundant until the price changes?


texts: Mirror failure after windows updates - possible cause?

texts:
Over a dozen SQL Server mirrors had windows updates installed this morning - 2 of which developed serious faults afterwards.

The windows updates installed where:

KB2939576
KB2957189
KB2957509
KB2957689
KB2957503

The 2 servers effected are running Windows Server 2008 Standard & SQL Server 2008 R2 (10.50.4000)

The fault consisted of 20% through 60% constant CPU usage on the mirror (even when the principle server was idle at 0%), constant mirror state changes (from disconnected to synchronising & vica versa).

When looking into the PID's in question we noticed the following blocked event against msdb:

WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue)

The "Application" listed against this was "Microsoft Windows Operating System" & the status was "SUSPENDED", this was being blocked by a whole chain of system PID's which all seemed to be notification related (for mirror state changes).

As a hunch we tried disabling the WMI service - this wouldn't stop - its almost as if the windows updates have caused WMI to hang and its taking SQL with it - uninstalling the windows updates didn't solve the problem, nor did dropping of all SQL Server Agent Alerts that where making use of WMI events.

Rebooting the servers made no difference - CPU usage was still excessive for no reason, Mirrors where still alternating between Synchronising & Disconnected - on the rare occasion that they did sync, transactions on the principle suddenly became deadly slow (often causing timeouts in client applications) until the mirror dropped again.

We're currently formatting the mirror, re-installing the OS with R2 in an attempt to solve this asap.

Has anyone had problems with these windows updates? Is there any known issues with WMI notifications causing SQL to hang?

I should probably add that the mirror servers in question had been working perfectly before these windows updates for years, CPU usage never goes above 5% really & the mirrors had been synchronised for months - only drops where when servers where deliberately rebooted.

Edit: When we tried dropping all SQL Server Agent alerts (just in-case they where causing the problem) (They fired an email when a mirrored database state changed via WMI) we got the message below suggesting there was something wrong in the notification system & the queue was not clearing down at the normal rate.

Unable to post notification to SQLServerAgent (reason: The maximum number of pending SQLServerAgent notifications has been exceeded.

Edit 2: We also noticed lots of the following in the SQL ERRORLOG:

Failure to send an event notification instance of type 'DATABASE_MIRRORING_STATE_CHANGE' on conversation handle '{5A821186-76A4-DD11-9F30-00188B481C62}'. Error Code = '8429'.

Edit 3: The chain of PID's that where blocking the WAITFOR command above where nearly all of type "BRKR TASK" - often flagged as Head Blocker & consuming the most CPU - SQL is doing something internally - is there any way to determine what a non user PID is doing?

Edit 4: sp_WhoIsActive shows no rows (which makes sense since the server is idle) - yet with the CPU constantly at 28% with sqlsvr.exe being the only process using CPU we've ran it with @show_system_spids = 1 instead:


texts: How are Postgres database files updated?

texts:
I'm curious how the /base files are updated for a Postgres database. Do the segments correspond to segments of each table, so that only a few segment files are updated after an update to subset of the table? Or will the update to the table cause changes to all or a majority of the segment files?

Basically, I'm interested in the reliability of these updates to the /base files for backup purposes. Given the usage of WAL for PITR, I have a feeling that the files are not updated in any coherent way for my purposes.

According to the documentation:

  When a table or index exceeds 1 GB, it is divided into gigabyte-sized
  segments. The first segment's file name is the same as the filenode;
  subsequent segments are named filenode.1, filenode.2, etc. This
  arrangement avoids problems on platforms that have file size
  limitations. (Actually, 1 GB is just the default segment size. The
  segment size can be adjusted using the configuration option
  --with-segsize when building PostgreSQL.) In principle, free space map and visibility map forks could require multiple segments as well,
  though this is unlikely to happen in practice.


texts: Two servers, different execution plans. Several orders of magnitude difference in run time

texts:
Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my development server the query executes in < 30 milliseconds.
On Live server the query takes > 20 minutes.

Examining the execution plans shows that they are drastically different. For one thing, on my development environment the query is parallelised whereas on the live server it is not.

I have rebuilt the indexes and regenerated the stats.
I cannot account for the massive discrepancy.
The servers' DOP are the same.
The Live server's CPU is getting hammered 100% during the execution of the query.

I'd appreciate any insights into the cause or how to best go about diagnosing the problem.

DECLARE @geoBoundary geometry;
SET @geoBoundary = geometry::STGeomFromText('POLYGON((407439.5 108792.25, 408022.5 108792.25, 408022.5 108575.75, 407439.5 108575.75, 407439.5 108792.25))', '0');

SELECT
     ogr_geometry.ToString() AS strGeometry
    ,ogr_geometry
FROM inspire as geo
WHERE geo.ogr_fid IN
(
    SELECT
        geo.ogr_fid
    FROM .inspire as geo
    WHERE
    (
        (@geoBoundary.STContains(geo.ogr_geometry) = 1)
    )
    UNION
    SELECT
        geo.ogr_fid
    FROM .inspire as geo
    WHERE
    (
        (@geoBoundary.STOverlaps(geo.ogr_geometry) = 1)
    )
)

The cost threshold for parallelism on both servers is 5 (the default).
The development instance has 4 physical cores, 8 logical; the live instance has 8 virtual cores.
max server memory is the same on both.
Development instance is SQL Server 12.0.4100.1; Live is SQL Server Web 12.0.2100.60
There is a huge disparity between actual and expected rows. However, this remained after rebuilding the statistics.
I cleared the plan cache. It keeps compiling the same plan.
Execution plans can be downloaded from here.


texts: Find duplicate subscriptions in SQL Server

texts:
I have these table:

create table Customers
(
    Id bigint identity(1,1) primary key not null,
    Name varchar(100) not null
)

create table CustomerActions
(
    Id bigint identity(1,1) primary key not null,
    [Date] datetime not null,
    CustomerId bigint not null
    [ActionId] bigint not null
)

create table Actions
(
    Id bigint identity(1,1) primary key not null,
    Name varchar(100) not null
)

and I have this view:

create view CustomerActionsView
as
select
    CustomerActions.*,
    Customers.Name as CustomerName,
    Action.Name as ActionName
from CustomerActions
inner join Actions
on CustomerActions.ActionId = Actions.Id
inner join Customers
on CustomerActions.CustomerId = Customers.Id

I have two actions: Subscription, and Cancellation.

So basically this is a simple design to store when a customer subscribes, and when he cancells.

A sample data might look like this:

[Date]-CustomerName-Action

"2018-04-24 17:44:20.000"-Rose-Subscribed
"2019-01-11 10:37:45.000"-Rose-Cancelled

Now I'm stuck at finding customers who have subscribed twice (or more than once). That is, they've subscribed when they were already subscribed. Or they've cancelled when they were already cancelled.

In other words, I'm trying to find consecutive actions, ordered by [Date] descending.

I've tried this:

select *
from
(
    select [Date], CustomerName, ActionName, row_number() over (partition by CustomerName, ActionName order by [Date] desc) as RowNumber
    from CustomerActionsView
) as Temp
where RowNumber > 1

If I exclude ActionName then row_number() won't be useful. If I include ActionName then it preceeds [Date] column.

I'm stuck at this point. How can I get repetitive consecutive actions?

Update: SQL Server version using select @@VERSION returns this value:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)   Feb 20 2014 20:04:26   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts: Stored Procedure Performance Issue in Sharepoint Report

texts:
We have a report stored procedure that runs in about 1 minute in production; however, when executed from the Sharepoint it populates, it rus for over 20 minutes, with exactly the same parameters.  We've done everything to avoid parameter sniffing problems: used WITH RECOMPILE on the procedure declaration; added OPTION OPTIMIZE FOR UNKNOWN on the parameters within queries; even created local variables and assigned the parameter values to them and used them throughout the procedure.  None of these has had any effect.  I know that the 1-base minute run time is a big red flag, and we're working on pre-aggregating this data into our BI platform, but that's months down the liine and this is needed for month-end close every month.

Other relevant information:
Sharepoint 2013 on SQL Server 2012
Data Source is a SQL Server 2008 R2 database
Report definition created in Visual Studio 2008


texts:
I want to know limitations or disadvantages of enabling 'Require distributed transactions for server-to-server communication' on SQL SERVER 2012 please?
My SQL database Server 2012 have a subscriber database for Transnational replication config and there are some other production databases as well.


texts: Full Text Search only performs 'starts with' searches

texts:
Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one?

For example, from the examples in the documentation:

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);
INSERT Flags VALUES ('France', 'Blue and White and Red');
INSERT Flags VALUES ('Italy', 'Green and White and Red');
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');
SELECT * FROM Flags;
GO

CREATE FULLTEXT CATALOG TestFTCat;
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;
GO

SELECT * FROM Flags;
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;

Works fine, and so does:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;

But this does not:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

Should I be using a different query, or is what I'm looking for not possible?


texts: Optimizing query. FOR XML PATH or some alternatives?

texts:
Is there any way to optimize the FOR XML PATH statement? Or maybe I should use another approach?

Current approach is not acceptable at all. It takes minutes. I know view is a really big union and it takes time to consume that, but maybe is another way...

Here is the query:

SELECT t.serialNumber as TVM
  ,[issuanceDate] as transactionDate
  ,ioy.tvmTransactionId as TVM_TRANS_ID
  ,STUFF((SELECT ', ' + stv.carrierSN
    FROM HERMES.wts.v_SaleTransactionView as stv
    WHERE stv.tvmTransactionId = ioy.tvmTransactionId
    and stv.serialNumber = 'M040'
    FOR XML PATH ('')), 1, 2, '') as [serial_numbers]
  FROM [hermes].[wts].[IOYLog] ioy
  left join Hermes.hermes.Terminals t on ioy.tp_terminalId = t.tp_terminalId
  left join hermes.hermes.POS p on t.tp_POSId = p.tp_POSId
  left join [Hermes].[wts].[IOUPaymentStatus] [is] on [is].[tp_paymentStatusId] = ioy.status
  WHERE [is].includeInReports = 1
  and (issuanceDate BETWEEN '2017/09/01' AND '2017/09/08') and (t.serialNumber = 'M040')
  ORDER BY transactionDate

Execution plan: https://www.brentozar.com/pastetheplan/?id=Sk7i98Wjb


texts: Cannot restart or repair mongodb database after the crash (***aborting after invariant() failure)

texts:
After an unexpected crash, I can't restart or --repair the mongodb database anymore.
While restarting the server with systemctl restart mongodb I am getting the following:

2020-09-16T20:25:23.835+0200 E STORAGE  [initandlisten] WiredTiger error (-31802) [1600280723:835687][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_block_read_off, 302: sizeStorer.wt: fatal read error: WT_ERROR: non-specific WiredTiger error Raw: [1600280723:835687][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_block_read_off, 302: sizeStorer.wt: fatal read error: WT_ERROR: non-specific WiredTiger error
2020-09-16T20:25:23.835+0200 E STORAGE  [initandlisten] WiredTiger error (-31804) [1600280723:835692][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_panic, 523: the process must exit and restart: WT_PANIC: WiredTiger library panic Raw: [1600280723:835692][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_panic, 523: the process must exit and restart: WT_PANIC: WiredTiger library panic
2020-09-16T20:25:23.835+0200 F -        [initandlisten] Fatal Assertion 50853 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 420
2020-09-16T20:25:23.835+0200 F -        [initandlisten]
***aborting after fassert() failure
2020-09-16T20:25:23.851+0200 F -        [initandlisten] Got signal: 6 (Aborted).

When I am trying repair with mongod --dbpath **/** --repair I am getting the following:

2020-09-16T20:05:09.921+0200 I STORAGE  [initandlisten] repairDatabase admin
2020-09-16T20:05:09.921+0200 I STORAGE  [initandlisten] Repairing collection admin.system.users
2020-09-16T20:05:09.921+0200 I STORAGE  [initandlisten] Verify failed on uri table:collection-0-4776197172926482174. Running a salvage operation.
2020-09-16T20:05:09.922+0200 F -        [initandlisten] Invariant failure rs.get() src/mongo/db/catalog/database_impl.cpp 228
2020-09-16T20:05:09.922+0200 F -        [initandlisten]
***aborting after invariant() failure
2020-09-16T20:05:09.929+0200 F -        [initandlisten] Got signal: 6 (Aborted).`


texts: What could be the reason for shrinking the transaction log file before and after the database backup?

texts:
I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who originally set things up are no longer available.
Server version: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
Jun 15 2019 07:56:34
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)
One of the first things that came to my attention when I looked at the server was an SQL Server Agent job that makes a backup of the main database. The database is in Simple recovery mode. The job has three steps:
-- step 1
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

-- step 2
declare @disk varchar(100) = replace(N'G:\DB_Backup\DWH_Main_&d&.bak','&d&', convert(varchar, getdate(), 112))
--print @disk
;
BACKUP DATABASE [DWH_Main]
    TO  DISK = @disk
    WITH  RETAINDAYS = 2
        , NOFORMAT
        , NOINIT
        ,  NAME = N'DWH_Main-Full Database Backup'
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , COMPRESSION
        ,  STATS = 10
GO

-- step 3
WAITFOR DELAY '00:05'
;
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

The data file size is about 400GB, the log file size right now is about 50GB.
CREATE DATABASE [DWH_Main]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'DWH_Main', FILENAME = N'E:\Data\DWH_Main.mdf' , SIZE = 411114496KB ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'DWH_Main_log', FILENAME = N'F:\Log\DWH_Main_log.ldf' , SIZE = 53941184KB ,
  MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

I can see A LOT of Log File Auto Growth events, which is no surprise. DBCC LOGINFO returns 858 rows, i.e. 858 fragments, almost each of them 64MB.
What should I say to my boss about this situation? I don't understand why anyone would want to shrink the transaction log file every day after a full backup, especially for a database in a simple recovery mode.
My first reaction was to simply remove the DBCC SHRINKFILE commands from the script and then re-configure the initial size of the transaction log file. Grow it manually in 8GB chunks to ~64GB to reduce the fragmentation, but maybe I'm missing something?


texts: need help building a query to detect duplicates on multiple columns

texts:
I have the following table
create table catalog_product_entity_varchar
(
    value_id     int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id     smallint unsigned default 0 not null comment 'Store ID',
    entity_id    int unsigned      default 0 not null comment 'Entity ID',
    value        varchar(255)                null comment 'Value',
)

I want to find all entity_ids where attribute_id = 86 for the same store_id where the value is the same.
For example for the columns attribute_id, store_id, entity_id, value
86, 1, 1, mypath
is duplicate with
86, 1, 2, mypath
but is not duplicate with
86, 2, 1, mypath
The result would be entity_id, store_id, value
Any help is appreciated.


texts: The current security context cannot be reverted

texts:
Msg 15199, Level 16, State 1, Line 29 T he current security context
  cannot be reverted.

  Please switch to the original database where Execute As was called
  and try it again.

execute as login='juno'
REVERT

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT  [THE_SERVER]= @@SERVERNAME
        ,[DB_NAME] =DB_NAME()
        ,[@USER]=@USER
        ,[SUSER_SNAME()]=SUSER_SNAME()
        ,[SYSTEM_USER]=SYSTEM_USER
        ,[USER_NAME()]=USER_NAME()
        ,[CURRENT_USER]=CURRENT_USER
        ,[ORIGINAL_LOGIN()]=ORIGINAL_LOGIN()
        ,[USER]=USER
        ,[SESSION_USER]=SESSION_USER

What if I can't remember the original database I was when I executed the execute as login?

Is there a way to find out where I was?


texts: Checksum of data imported from mysqldump not equal to the original checksum?

texts:
I'm tring to create a mysql replication instance.
So I mysqldump --complete-insert --skip-dump-date -uuser -ppass db table > master.sql.

Also I've imported the master.sql to the slave instance without starting slave.
But now I find the checksums not the same.

Dumped with the same mysqldump options, diff master.sql slave.sql only prints out:

5c5
< -- Server version 5.6.30-0ubuntu0.14.04.1-log
---
> -- Server version 5.6.30-0ubuntu0.14.04.1

Any ideas? Why the checksums not equal?

Thanks in advance.


texts: How to output query to file using SQLite3

texts: Is there an equivalent to Oracle's spool for sqlite3?


texts:
I am having problems setting up a mongo replica set.  I have 2 servers and want one to be primary and one to be secondary.

On primary machine in mongod.conf:

replSet=rs1

Then in the console I type:

rs.initiate()

Which works great.  rs.config() shows:

{
  _id: "rs1",
  version: 1,
  members: [
    {
      _id: 0,
      host: "myhost1:27017"
    }
  ]
}

This is where things get messy.  What am I supposed to do on the second machine?  I have modified the mongod.conf to point to the same replica set name 'rs1'.  I start the sever and all is well.

Am I supposed to do an rs.initiate() on the second machine?  If so both instances now think that they are the 'PRIMARY'.

If I try to do an rs.add(...) on the first server to add the second server:

rs.add("myhost2:27017")

I get an error:

{
    "errmsg" : "need most members up to reconfigure, not ok : myhost2:27017",
    "code" : 13144,
    "ok" : 0
}

if I do an rs.initiate() on the second machine and then an rs.config() I get:

{
  _id: "rs1",
  version: 1,
  members: [
    {
      _id: 0,
      host: "myhost2:27017"
    }
  ]
}

Any ideas?

EDIT:

Here is my /etc/hosts file in case I messed something up there:

host1:

127.0.0.1   myhost1
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost4.localdomain6

host2:

127.0.0.1   myhost2
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost4.localdomain6


texts:
I am working on a fitness app that is going to deliver notifications to the user. I intend using MySQL events to populate the necessary tables, and then using an Ajax timer or Websockets or whatnot to deliver them to the User FaceBook style.

The problem is that I am going to have a whole class of notifications eg. "Spongebob you have not taken a class in 2 weeks, stop slacking", and "You are xyz% away from reaching your goal weight, don't stop now!" etc etc.

I was thinking of a structure like this:

**Notification**
--Id
--Message

**UserNotification**
--Id
--UserId
--NotificationId
--ProcessedMessage
--Seen?

My question is, so I will have placeholders in the Notification message eg. "{{name}} you have reached your goal weight! Congratulations", but these placeholders will be different depending on the message.

I was thinking of "compiling" (via string replace) the messages with the MySQL event when I have all the relevant data on hand e.g. I will create an event for getting users who have not done any exercise in 3 weeks and so on, and then saving the value in UserNotification. This lets me update the message with time, whilst keeping history intact, but I lose the ability to fix spelling errors etc. in one place only and having it automatically propagating.

If I don't compile the message in the MySQL event, then I will have to do a custom columns solution in UserNotification so I can generate the message later.

Is there a better way to doing this?


texts:
Apologies for the long post but i'm stumped on getting Pgpool to work for me.

Running Centos7 i have two instances of Postgres running on different ports in master replication mode.

I have successfully installed pgpool-II.

Starting pgpool i get the output :

[root@PLHSISRV035 pgpool-II-96]# pgpool -n
2018-02-07 16:21:54: pid 118373: LOG:  Setting up socket for 0.0.0.0:9999
2018-02-07 16:21:54: pid 118373: LOG:  Setting up socket for :::9999
2018-02-07 16:21:54: pid 118373: LOG:  find_primary_node_repeatedly: waiting     for finding a primary node
2018-02-07 16:21:54: pid 118373: LOG:  find_primary_node: checking backend no 0
2018-02-07 16:21:54: pid 118373: LOG:  find_primary_node: primary node id is 0
2018-02-07 16:21:54: pid 118373: LOG:  pgpool-II successfully started. version     3.7.1 (amefuriboshi)

So from here i should be able to connect using :

psql -l -p 9999 (default settings)

Instead i get the standard not running Postgres error:

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.9999"?

Can anyone help. ?

thanks,


texts: Storing multiple tables by type vs one table

texts:
I want to store a lot of rows in a table, but I will often want to select only some of them, depending of their type. So I could just add a column type to each row, but then, if I want to select only the rows of one type, I'll have to make a query like SELECT * FROM MyTable WHERE type = 1 for example. But does it mean that the DBMS will have to fetch all the table, even if only a few rows have the type that I want?
Is it better to have a table per type, so I could just SELECT * FROM MyTableType1, without having to use a WHERE?


texts: Deadlock killing update statement when collided with select

texts:
In my recent issues.. I have noticed a dead lock killing an update statement rather than a select command which was locked in it.

The SELECT's isolation level was at the database default, which is read committed snapshot isolation.

The SELECT is part of a transaction in our ETL.


texts: How to store international phone numbers in PostgreSQL?

texts:
What is the correct way to store phone number data after applying a new mask for user input (example for UK; 44 07400 123456) if the type of the existing column is character varying(32)? Does storing it as it is generated by mask has a drawbacks (especially space characters)?

In other words, what are some pros and cons of storing it as formatted or unformatted might be better to asking for my case, since I'm not dealing with the type of the column and I'm going to stay with the same type. Especially, what I'm really care about is that is, what would be best practice? +4407400123456 or 44 07400 123456 (with spaces).


texts: SQL Server database is too large because available space allocated 70% mdf space

texts:
My database is 825 GB on disk, but unallocated space is about 500 GB (825GB * 55%).

After I shrank the database and files in SQL Server Management Studio, it had no improvement to reclaim the total .mdf file size. But I observed the unavailable space increased to 600 GB, total size of the .mdf file still was 825 GB (before shrink, I rebuilt the the index of tables which used to full text index search).

Do I have approach or configuration to reduce or reclaim the unallocated space (available space)?
Some detail:

Recovery mode: Simple
SQL Server 2016 CU15


texts:
I have a column that contains numbers:

Value
-----
123
452
021
111
...

I want to get the sum of each value's digits.

Answer should look like

Value  SumValue
-----  --------
123    6
452    11
021    3
111    3
...    ...

How can I do this?

I have tried this:

select value
     , case
           when len(value) = 2 then convert(int, left(value, 1))
                                  + convert(int, right(value, 1))
           when len(value) = 3 then convert(int, substring(convert(varchar(1), value), 1,1))
                                  + convert(int, substring(convert(varchar(1), value), 2,1))
                                  + convert(int, substring(convert(varchar(1), value), 3,1))
           else value
       end as SumNum
from get_number (000, 999);

When I get to three digits, it does not calculate correctly, and it seems to me like there's got to be a better solution.


texts:
How would I exclude duplicate 'Clients' who are enrolled in other 'Programs'? Example- Client A is enrolled in Program 36 and 37. I only need them in the list for one of those programs.

Here is the query I am running-

SELECT DISTINCT
  LastFirstName AS Client,
  DOB,
  LastFirst AS Worker,
  DATEADD(DD,839,DOB),
  DATEADD (DD,1058,dob),
  Name AS Program,
  EndDate

FROM Intakes INNER JOIN
        Client ON Intakes.ClientId = Client.ClientId INNER JOIN
        ProgramLkp ON Intakes.Program = ProgramLkp.Code INNER JOIN
        Employees ON Intakes.CaseMgr = Employees.StaffId INNER JOIN
        CaseWorkerHist ON Intakes.KeyId = CaseWorkerHist.IntakeKey

WHERE (Program = 36 OR
      Program = 37 OR
      Program = 9) AND
      EndDate = '2079-12-31'

Thanks.


texts:
I need help  what statement or function i can use to run a query in all database using one script , i want to run the below script at the same time to retrieve output in all databases

   select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like '%Janice\SQL%DTS%'.


texts: Make MySQL dump from parts of each table

texts:
I need to export some data from different tables. I tried this query

select * into outfile 'C:\Users\\User\\Desktop\\ololo.sql' from db.users where id % 2 = 1;

After this query, the SQL file was created. When I use id i in the the same query to export data from other table. Thus, an error happens. I need additional data from the other table. So, the result must be not dump of all database but part of it. Thanks for your answers.


texts: Stored procedure prepared statement no error and not working

texts:
Here is my stored procedure:
BEGIN
#Procedure:     insertMetaTagIfNotPresent
#Parameters:    bkID    ID to find or NULL to create automatically
#               vcTag   Tag name to assign, must be unique
#Notes:         Creates metatag table if it doesn't exist
#History:       2021/05/04  Written by Simon Platten

    DECLARE txtSQL TEXT;
    #Create SQL insert string
    SET txtSQL := 'INSERT INTO metatags (';
    #Has a primary key value been supplied?
    IF NOT biPK IS NULL THEN
    #Yes, use it!
      SET txtSQL := CONCAT(txtSQL, '`biPK`,');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '`vcTag`) VALUES (');
    IF NOT biPK IS NULL THEN
        SET txtSQL := CONCAT(txtSQL, biPK, ',');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '\'', vcTag, '\');');
    #Create prepared statment
    SET @SQL := txtSQL;
    PREPARE stmt FROM @SQL;
    DEALLOCATE PREPARE stmt;
    SELECT txtSQL;
END

Its very much a work in progress and not finished.  biPK (parameter 1) is optional and if NULL then it isn't inserted into the query.
I've added SELECT txtSQL so I can see if the final SQL created is valid.  No error occurs and the SQL generated is valid, but no record is inserted into the table.
I have copied the generated insert SQL and pasted into HeidiSQL and it works, however calling the stored procedure doesn't end up with any new record being created.
This is running on MariaDB version 10.5.9.0 on Windows version 10.0.19041.928
What am I missing?
[Edit] modifications, but still doesn't work:
BEGIN
#Procedure:       insertMetaTagIfNotPresent
#Parameters:      _bkID ID to find or NULL to create automatically
#                 _vcTag      Tag name to assign, must be unique
#Notes:           Creates metatag table if it doesn't exist
#History:         2021/05/04  Written by Simon Platten

      DECLARE txtSQL TEXT;
      #Create SQL insert string
      SET txtSQL := "INSERT INTO metatags (";
      #Has a primary key value been supplied?
      IF NOT _biPK IS NULL THEN
      #Yes, use it!
        SET txtSQL := CONCAT(txtSQL, "`biPK`,");
      END IF;
      SET txtSQL := CONCAT(txtSQL, "`vcTag`) VALUES (");
      IF NOT _biPK IS NULL THEN
            SET txtSQL := CONCAT(txtSQL, "'", _biPK, "',");
      END IF;
      SET txtSQL := CONCAT(txtSQL, "'", _vcTag, "');");
      #Create prepared statment
      SET @SQL := txtSQL;
      PREPARE stmt FROM @SQL;
      DEALLOCATE PREPARE stmt;
      SELECT txtSQL;
END


texts: Mysql trying to drop table and how to recover data?

texts:
I have on lamp setup where suddenly I see all the database files are missing and gone and when I checked the error logs I can see few of this lines.
InnoDB: Error: table `com`.`#sql` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from

What I can see now is the ibdata1 and the log are they. So based on my googling I have setup another server and I am going to setup mysql on it and then shut it down and trying to delete its ibdata1 and logs and replace with this machine ibdata1 and log. I would like to get opinion is this a right way to do to recover it or should I use any other mechanism to help recover the data? The current version of the mysql is 5.6 and I guess the new machine must be same version ?
Updated I have managed to move the ibdata1 to a new server and start it using recovery as 1 and just added one of the dB folder and it's relevant frm file but when I run mysql dump it keep saying no such file or directory


texts: CNAME alias from named SQL instance to default instance

texts:
I know I can use CNAME alis to point from one SQL Server to another for upgrade or such log shipping, but what if one is named instance and other is default instance?

Do I need SQL alias?

Do i need such like this answer?

Create an alias for a SQL Server named instance pointing to sql default instance

.thanks


texts: Linking first and second level composite primary keys

texts:
I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not match:

Here a simplified Structure of the sql query:

CREATE TABLE a(
    a_ID serial PRIMARY KEY,
    a_name char(100)
);

CREATE TABLE b(
    b_ID serial PRIMARY KEY,
    b_name char(100)
);

CREATE TABLE c(
    c_ID serial PRIMARY KEY,
    c_name char(250),
);

-- First refering table
CREATE TABLE b_c(
    b_ID INT references b ON UPDATE CASCADE,
    c_ID INT references c ON UPDATE CASCADE,
    PRIMARY KEY (b_ID, c_ID)
);
-- Second higher level combining
CREATE TABLE full(
    b_c_ID INT references b_c ON UPDATE CASCADE,
    a_ID INT references a ON UPDATE CASCADE,
    PRIMARY KEY (b_c_ID, a_ID)
);

The creation of the last table fails.
Am I missing something here or is it impossible to refer to a composite primary key in just another composition?
Any comments?


texts: How to fix SQL Agent Job using Windows Security after password change

texts:
I have some sql agent jobs that are using Windows NT Integrated Security to run (as specified in the Maintenance Plan's Manage Connections window)...eg. Translog backups.
While logged in as this account, I changed its password. Now the maintenance job is failing.
reason: Error authenticating proxy DOMAIN\Account, system error: The user name or password is incorrect
How do you fix this?
What I've tried:

Logging out of the server and back on
Per this article, Changed the connection to use SQL Authentication and then restarted SQL Agent...it still fails, but the error still is the same showing the same DOMAIN/Account that failed, so it is still using the Windows authentication instead of the new SQL login provided.


texts: Automatic insertion of calculated values to another

texts:
I am creating an application, so I need trigger or function that will help automatically enter data in the database as data is entered into the system considering the other table's primary key.

I currently have AddItems, Sales and Products tables. The AddItems table is where all the invoice data is stored. What I need is to be able to add the quantity of products together according to the Item_Code. Once the sum is found the data should be stored on the Products table. This should happen every time a product product is added in the AddItems table.

AddItems

Item_ID (PK, IDENTITY)
Date
Supplier_Name
Track_NO
Quantity
Item_Code
Item_Description
Vat
Amount
Total_Amount

Products

Product_Id (Identity)
Item_Code (PK)
Item_Description
Available_Quantity

Query I managed to come up with needs to be manually ran to insert the values. Below is the statement used.

INSERT INTO Products
(Item_Code, Item_Description, Available_Quantity)
SELECT Item_Code, Item_Description,
    Sum(Quantity)
FROM AddItems
Group By Item_Code, Item_Description;

(Asked the same question but i couldn't merge my account with the other)


texts: How do I get the some of the values from 2 foreign keys in a table?

texts:
I've spent a good amount of time on this but, still have no idea as to the sql query I can use to get the student name, and class name from the payments_per_student table.

edit: This using JPA work with this DB. Knowing the JPA way as well SQl way would be interesting. I'm currently researching the JPA way.

payments_per_student:

PAYMENTS_PER_STU_ID
STUDENTS_STUDENT_ID
FAMILY_ACCOUNT_FAMILY_ID
REASON_FOR_PAYMENT
AMOUNT
DATE_PAID
CLASSES_CLASS_ID
ACTIVE_IN_CLASS
NOTES

The second is, Classes:

CLASS_ID
CLASS_NAME
DAY_OF_CLASS
TIME_OF_CLASS
DURATION_OF_CLASS
PRICE_PER_CLASS
PRICE_PER_TERM
PRICE_PER_YEAR
NOTES

The third table is, Students:

STUDENT_ID
FIRST_NAME
LAST_NAME
FAMILY_ACCOUNT_ID
SEX_TYPE
NAME_PREF
DATE_OF_BIRTH
PERFORMANCE_STU
HEALTH_ISSUES
NOTES


texts: Unable to show binary logs how to remove logs from mysql?

texts:
Server version: 5.1.50-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show binary logs ;

PROBLEM

Data directory is full
I want to remove space
Purge is not making more space.

Any idea what to do to clear relay logs and Bin file .


texts:
Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.

Form How to determine if an Index is required or necessary I have learned, that there are DMV views, which can be used to defer information about actual index usage as well as missing indexes.

The script from Jason Strate only uses the current information from that views, while Fun for the day - Automated Auto-Indexing! saves some information from those views into tables.

DTA on the other side requires planning when to run the profiler, select what to profile and has some impact on performance while running.

My impression is that using DTA as first step tuning tool is simply a waste of time and as its results don't cover the complete database usage, are hard to interpret by novices and can lead to adding too much indexes with negative impact on write performance, while Data collection via DMV requires little preparation and covers nearly the whole usage of the database since the last restart.

My question focuses on which strategy to propose to the management.
I want to focus on evaluation the DMV views in the first step and ignore DTA completely.


texts: Complex Delete Cascade Including Self-Referencing Table

texts:
Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I would like to use if it were possible.

All types have properties in common, including columns that show position in the hierarchy (Parent and Index) and type of element. Those common columns are stored in the ElementBase table.

Each type of element also has unique properties which are stored on the the corresponding tables based on element type.

Each of the rows in AData, BData and CData reference a unique main row in ElementBase. "A" and "C" elements also each reference a "B" element. "B" elements can have 0 or more "S"s.

My problem is: how can I maintain reference integrity and support something like cascade deletion?

I want to be able to delete a row from ElementBase and have the corresponding row in either AData, BData or CData also be deleted. If, for example, a "B"-type element gets deleted from ElementBase, firstly the corresponding row from BData should be delete, then every "C"-type element that references it needs to be deleted in both the ElementBase and CData tables, and all "A"-type elements need to have their references set to NULL in AData.

And the cherry on top: if the element I delete has children of any type, I want this same logic to run recursively down the hierarchy.

Since ElementBase is self-referencing, I can't use the simple ON DELETE CASCADE feature in that table. I also can't use that on AData or CData because they both reference BData which could then result in "multiple cascade paths" which are apparently evil in SQL Server.

One alternative I've found is INSTEAD OF triggers. The problem with that is this behavior has to be recursive and I can't quite figure out how to enable them to be recursive and also eventually do the original delete at the end.


texts: High latency with SQL Server 2014 Synchronous AlwaysOn

texts:
I've built a SQL 2014 cluster in our development environment that consists of 2 nodes. Non-Shared disks

Specifications:

Storage is Nimble Storage Array with dedicated LUN's presented to the hypervisors, each drive on each guest (data, logs, tempdb) has a dedicated LUN.
The LUN's are cached on SSD's and latency on the array and LUN's is under 0.25ms for both reads and writes.
Entire dataset of this test is < 350MB
vSphere 6.0U1
2 identical VM's (Windows Server 2012R2 STD)

10 vCPU
64GB Ram
Storage is on dedicated VMDK's - pVSCSCi, all VMDKs are THICK-EAGERZERO.
VMXNET3
SQL Server 2014 SP1-CU5

AG name is AG02
Primary node is SQL2K14-02
Secondary node is SQL2K14-01
Synchronous mode
Automatic Failover

A copy of the production database showed 1000ms delay for inserts, so the same test has been run using HammerDB (www.hammerdb.com) in the following scenarios:

HammerDB - Node1, no AG
HammerDB - Node2, no AG
HammerDB - AG1, Sync  (Node1+Node2)
HammerDB - AG1, Async (Node1+Node2)

Findings:

The performance of test #1 and #2 are the same, so we know that we don't have a node that is slowing us down.
Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.
Using AG1 with Async we have 0 latency, but that is something we can't use.
I was able to rule out networking latency as both VM's are on the same VLAN. Doing a constant ping + tcpping + iperf simultaneously showed latency of less then 0.3ms while both servers were pushing > 3Gbit/s over TCP.
Below are graphs of the relevant metrics for AG.
During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.
When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

Question:

What is happening and why is there a whole second delay ?
I was thinking I'm doing something wrong, but didn't find anything in the documentation.

Thanks,
Idan.


texts: Do Statement-level AFTER triggers occur asynchronously after the statement returns?

texts:
I'm using an AFTER INSERT OR DELETE OR TRUNCATE ... FOR EACH STATEMENT trigger on a table to refresh a materialized view, and I'm concerned that it will significantly affect the performance of those statements.

Would an INSERT , for example, wait for the materialized view refresh to complete before returning?


texts:
I have 2 tables

EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and
CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period).

Primary key for both tables are (StudentID, SubjectID, SchoolYear, Period).

When a student wants to take an exam,  then we write one row in EXAM table.
But if a student passed an exam and is not satisfied, then he wants to cancel the exam and we need to write one row in table CANCELED_EXAM.

My question is: How to select just non-canceled exams from table EXAM.


texts: Procedure completes prematurely after converting comma-delimited value strings to XML

texts:
I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked permissions, which doesn't seem to be the issue. Then I ran the procedure through Profiler and it seemed to complete after the 'CAST AS XML...CROSS APPLY' statement, which would explain why nothing is returned. I can't figure out why this would be though. Any ideas?

Here is the bulk of the procedure, with a few table variable declarations removed

DECLARE @allprodsandpacks TABLE (
  packageID varchar(20),
  Name varchar(800),
  RN tinyint,
  productID varchar(20)
)

INSERT INTO @allprodsandpacks (packageID,Name,RN,productID)
SELECT  packageID,
        Name,
        RN,

        REPLACE(LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))),'|','') AS productID
        FROM

        (
           SELECT productID as packageID,
           ROW_NUMBER() OVER (PARTITION BY productlist ORDER BY productID) AS RN,
           Name,
           CAST('<XMLRoot><RowData>' + REPLACE(productlist, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
           FROM   TBOnline.dbo.Packages
        ) AS t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

--Profiler registers SP:Completed here!!

INSERT INTO @prods (productID)
SELECT DISTINCT productID
FROM @allprodsandpacks
WHERE RN > 1

INSERT INTO @packs (packageID,Name)
SELECT DISTINCT
  packageID
, Name
FROM @allprodsandpacks
WHERE productID IN (SELECT productID FROM @prods)

SELECT
  packageID
, Name
FROM @packs
ORDER BY Name


texts: SQL Server: Failed to monitor external signals

texts:
This program has encountered a fatal error and cannot continue running.
The following diagnostic information is available:

       Reason: 0x00000007
      Message: Failed to monitor external signals
      Process: 26596 - sqlservr
       Thread: 26600 (application thread 0x1000)
  Instance Id: 4065d66f-ef73-4bb1-bfe3-5a4ef091cdf0
     Crash Id: 71fa707f-be4b-4b39-9269-ec697e7b5baf
  Build stamp: 9a4c738f0e0d015cbf851ead55d593dc0c806bb1b05452f58289c247307d420d
*********** PAL PANIC CORE DUMP GENERATION FAILED **********
Unable to locate handle-crash.sh. Error 2.
*********** PANIC CORE DUMP GENERATION FAILED **********
Attempt to launch handle-crash.sh failed with error 0x00000002
Failed to connect to bus: No such file or directory
Attempting to start the Microsoft SQL Server service failed.

If I manually run, /opt/mssql/bin/sqlservr I get

Capturing core dump and information...
cat: /proc/27493/maps: No such file or directory
cat: /proc/sys/vm/max_map_count: No such file or directory
/opt/mssql/bin/crash-support-functions.sh: line 399: (100*0)/: syntax error: operand expected (error token is "/")


texts:
I have the following table that holds the purchase order

po_id
supplier_id
transaction_type  -- either cash or credit

and other table that holds the items

po_id
product_id
quantity
unit_price

Now, how should I handle payment transactions, as payments can either be cash, or credit. If credit, the payment can be made in multiple transactions. and if Cash, there should not be any outstanding payments for this particular purchase order. How can I design a schema that identifies outstanding amounts per purchase order and reflect that in a double entry accounting schema.


texts:
I manage development teams that support a legacy app with a SQLServer backend.  The app is deployed on-premise so our customers maybe run any version SQLServer (minimum 2008).

We have an on-going challenge of certifying that our app runs on new versions of SQLServer or any new service pack.  It's very time consuming for our QA to run a full regression of our suite of products so I'm wondering if anyone faces the same challenge and has found another way to certify new releases of SQLServer as they come out.

We've though of turning on profiler and then hammering the app to capture SQL and just replay that on a new SQLServer instance with the new version. Is there a better way?

BTW, we use a lot of SQL features such as triggers, procs, views, defaults etc.

Thx


texts: Indexes getting highly fragmented during normal usage of system

texts:
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.

Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.

What can I do in such situation? It is rather impossible for me to get additional maintenance window(s).
I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.


texts: Hidden operation of rebooting a SQL server database

texts:
i want to know what operations will be execute in the reboot of SQL server?

The scenario is, the transaction log is almost full, one of my client felt the system is very slow (in fact queries are not executable, and he traced the server CPU & memory usage is very high), thus he choose to reboot the entire server.

Afterwards, he said the operation ran smooth again.

I would like to know, what operations are done in the reboot operation of sql server particular to the transaction log

for example, is a reboot could force the SQL server executing a checkpoint? or even a backup? so that reboot can somehow free the occupied space?


texts: Running Sql Analysis server on different server

texts:
i have server running a database on it, and i have another server which i want to run Analysis server on it, so people can connect the analysis server as they wish to create their own reports for some purposes, but the problem is i could not connect Analysis services to database server which Analysis server can cube the data, according to my research database server and analysis server could be separated, but i could not find any information about how to connect analysis server to database server, the features i install on analysis server are

  Detailed results:
  Feature:                       Analysis Services
  Status:                        Passed

  Feature:                       SQL Browser
  Status:                        Passed

  Feature:                       SQL Client Connectivity SDK
  Status:                        Passed

  Feature:                       SQL Client Connectivity
  Status:                        Passed

How can i connect Analysis Server to database server which is a different server.
any help is appreciated


texts:
We have a number of tables that will process a large amount of inserts each day.  For this reason, the tables are archived on a weekly basis using a labour intensive process of backing up the database and restoring to another server and then inserting the data from this backup into another archive database.

My plan is to introduce AlwaysOn for High Availability and use Table Partitioning to switch the archive partitioned data to another table.  Has anyone any experience of using table partitioning in this way with AlwaysOn?  Does it present any issues with AlwaysOn traffic to the Secondary when switching?  Has anyone any other recommendations other than Table Partitioning?

Thanks!


texts:
After googleing a bit, it's plainly clear you cannot run vacuum from a function:

Postgres mailing - Vacuum behaviour in plpgsql function

  You can't run VACUUM from inside a function because of memory-management
  limitations.  In current sources there is an error check to prevent you
  from trying.

Stackoverflow - VACUUM cannot be executed from a function or multi-command string

  Once the data is loaded, I would like to "vacuum analyze" the affected tables, both to recover the space from the deleted records and to accurately reflect the new contents. [...] When I run this, I get:

  ERROR:  VACUUM cannot be executed from a function or multi-command string

But I really need to "automatically" run vacuum whenever a certain function is run: this function updates a lot of records (if not all of them) so the number of dead rows increases quite fast and other query performance decrease a lot.
The problem is the function is called from another program.
Any best practice?
Should I manually add the vacuum statement into a separeted piece of code after the function execution, someting like

sql = SELECT my_function()
<program.execute(sql)>
sql = VACUUM [FULL | ANALYZE] my_updated_table
<program.execute(sql)>


texts: Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0?

texts:
We have a suspicion that sometimes there could be non-unique data even with a UNIQUE index. Question is, can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the presence of UNIQUE_CHECKS=0, so there is no reason to check it?

Edited to add: If it is possible to have non-unique index entries for a UNIQUE index in InnoDB (even with cheating), how is such a test-case created?

Or is it like foreign key constraints and InnoDB, where that guarantee can be broken by setting FOREIGN_KEY_CHECKS=0, so that if we want to be sure, we should check it with SELECT statements?

The documentation for UNIQUE_CHECKS:

  Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.

documentation for Bulk Data Loading for InnoDB Tables:

  If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

  For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

These seem to imply that UNIQUE_CHECKS=0 can be used to break the uniqueness guarantee.

MariaDB Enterprise documentation for unique_checks:

  Set to 0 to speed up imports of large tables to InnoDB. The storage engine will still issue a duplicate key error if it detects one, even if set to 0.

This seems to imply that the guarantee holds, even with UNIQUE_CHECKS=0.

This example SQL demonstrates UNIQUE_CHECKS=0 is ignored and uniqueness seems to be enforced still:

DROP TABLE IF EXISTS `FOO`;

CREATE TABLE `FOO` (
    `foo` VARCHAR(255) NOT NULL,
    UNIQUE INDEX (`foo`)
) ENGINE=InnoDB;

INSERT INTO FOO (foo) VALUES ('A');

SET UNIQUE_CHECKS=0;
-- This generates the error:
-- ERROR 1062 (23000) at line 11: Duplicate entry 'A' for key 'foo'
-- even though UNIQUE_CHECKS=0
INSERT INTO FOO (foo) VALUES ('A');

(Edit: Simplified example to only demonstrate that UNIQUE_CHECKS=0 is ignored.)

So which is it: Can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the presence of UNIQUE_CHECKS=0, so there is no reason to check it?

We're using MySQL version 5.7.23.


texts: Connection string works with dbping but not with mlsrv

texts:
I'm trying to start a Mobilink synchronization server, and my connection string works with dbping...
dbping -d -c "uid=xxx;pwd=yyy"

...but fails to work with mlsrv
mlsrv -c "uid=xxx;pwd=yyy" -dl

The error is:
<Main> [-10002] Consolidated database server or ODBC error:  ODBC: [SAP][ODBC Driver][SQL Anywhere]Invalid user ID or password (ODBC State = 28000, Native error code = -103)


texts: Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016

texts:
Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this server.

It was decided we build a new server and migrate the ReportServer Database. Unfortunately we don't have the Encryption Key. Because the service is down I can't even take the back up now, its too late.

I tried backing up the ReportServer and ReportServerTempDB databases and then restoring those to the new servers. Restore went well, I even changed the database from the reporting services configuration manager.

But, When I tried to go to the web portal URL, It shows the error , The report server isn't configured properly. Check the report server trace log for details. I found out that logs shows the scale out deployment errors. I believe since I don't have the encryption key file and password I am receiving this error.

What can I do to get reporting services up and running...?

What should be my approach..? Can I restore the encryption key some how or create a new one with rskeymgmt..? If not then can I delete it (if it is possible) and try to reconfigure all the reports again..? (There are about 350 different reports on this server).

Please help.


texts:
When syncing a SQL Server 2008 subscriber with a publisher, the sync fails at around 98% with this error message:

  The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.

We have 30+ subscribers -- all of which are working except this one.

Is there any harm to other subscribers to restart the agent?

Bonus Points:

b) Is restarting the agent a good solution, or should I be looking elsewhere when I see this message?

This error message is also featured in Issue with SQL Server 2008 replication


texts: MySQL - can't get the result of non-matching records

texts:
I am trying to get the result of a record which has a missing entry in the other table. I already tried using NOT IN and NOT EXIST but I cannot get the desired result. The output should be Kevin and US Visa since "Kevin" has no US Visa on the table tbl_personnel_documents but I always get an empty row. It should be like this:

name  | documentName  |
----------------------
Kevin   US Visa

Note: I am trying to get the result by using only the vessel_id and lastJoinedVsl which in this case is id number 4.

Here's my code and sample data: http://sqlfiddle.com/#!9/46790e/1

Please help. Many Thanks


texts: How to query for address "predictions" while applying constraints

texts:
Let's say I have this simplified database scheme.

The scheme consists of three tables, street, city and zipcode that all have a many-to-many relationship to each other.

What I want to do is a prediction that queries the database for possible matches as well as predicting missing data. To put it more into an example:
Let's say the user starts typing in a city, perhaps "Wash". He already had a street typed in, for sake of me not knowing any streets, let's call it "New Street Avenue". This is a constraint, meaning it must be fulfilled in our prediction. Now I want the city to query for possible finishers of "Wash" as well as supplying missing data, in this case, zipcodes, that have a street called "New Street Avenue"

Here's how the naive approach would be:

Filter street for all keys whose name match "New Street Avenue"
Filter street_city for all the street keys and return the city keys
Inner join the city keys on city_zipcode, and then Inner join those onto zipcode

The issue with this approach: The knowledge between street and zipcode is completely ignored. That means we could end up with zipcodes and city pairs, where we know the city contains the street we seek for, but the zipcode doesn't necessarily. This would be an invalid prediction to return, because the address doesn't exist!

That means I need to "persist" the information about street further down the line.

My approach would be this:

Filter down street for the keys that fulfil our condition.
Inner Join street onto city for the keys from #1. Keep both key columns.
Inner join onto city_zipcode keeping the zipcode keys as well
Filter out all the street and zipcode pairs that are not present in street_zipcode

(On a sidenote: to "hopefully" improve performance, after each "prediction" I would put a LIMIT 10 (or similar) command since we only need a sensible amount of predictions).

This approach should work. It might not be the best optimized, but it would return the correct results. However this not only feels very dirty to me, but also opens up another problem:

I'm pretty sure it scales horribly, in table size as well as in additional tables

There is a good likelyhood there will be another table that has Many-to-Many relationships towards, for the sake of argument, street and zipcode. Now if I wanted to search for a city with a street constraint, I need to do what I did above, and add even more to also filter out any relationships between street and the new table.

I feel like I'm going into a dead end with this approach. I'd love someone to help me out here on how to approach this problem better.