texts: Efficient group by on sorted CSV using PostgreSQL FDW

texts:
I have a large CSV file sorted on the first column. Here is an extremely simplified version):

a 7
a 4
b 6
c 3
c 9
c 2

I want to group by the first column and sum the second.

How do I tell the PostgreSQL foreign data wrapper (fdw) to assume that the file is sorted on the first column, thereby not scanning the entire file before producing the output?

I am using version 11.1

Update: The above example is dramatically simplified. In the real life use case the first column has a very high number of values. It is the Google ngrams data set where first column is a phrase, second is the year, third is the occurrences in literature published that year. I want phrases with total count over all years greater than say 10,000. A simple python program can stream the result efficiently. So why not PostgreSQL? FDW could simulate an index over the table if informed of the sort order, just as it simulates the table from the CSV file, so I asked if it does something like that.

My main concern is efficiency. I previously did this using Unix command line tools such as awk but I would like to avoid juggling Unix scripts which are pretty much specific hand crafted execution plans. Save me PostgreSQL!!


texts: SQL queries failing, Memory not releasing

texts:
I have a SQL Server 2008 R2 with 8GB of ram and SQL Server is limited to 6GB. It seems to not be releasing the memory its used, and at some point future queries begin to fail. This is a mirrored server and the problem is solved after being failed over. Is there a way to force the Memory to always be released, or a known issue with limiting the memory?

There was a problem with the same server long ago, before SQL Server had a memory limit where it would use and use the memory until it crashed the server.

This is a Windows Server 2008 OS.


texts: How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

texts:
I have legacy tables similar to the following:

employee

------------------------------------
|  employee_id  |   name
------------------------------------
|  1            |   David
|  2            |  Mathew
------------------------------------

payroll

-------------------------------------
|  employee_id  | salary
-------------------------------------
|   2           |  200000
|   3           |  90000
-------------------------------------

I want to get the following data, after joins and filters:

-----------------------------------------------------------
|  address_id  | employee_id  |  address
-----------------------------------------------------------
|   1          |  2           |  street 1, NY
|   2          |  2           |  street 2, DC
------------------------------------------------------------

I have the following query:

SELECT employee_id, salary, address_arr
FROM employee
LEFT JOIN payroll on payroll.employee_id = employee.employee_id
INNER JOIN
     (
       SELECT employee_id, ARRAY_AGG(address) as address_arr
       FROM addresses
       GROUP BY employee_id
     ) table_address ON table_address.employee_id = employee.employee_id
WHERE employee.employee_id < 1000000
LIMIT 100
OFFSET 0

Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.

Kindly answer:

How can we avoid the GROUP BY operation to occurs over the complete
addresses table by using LIMIT OFFSET of the outer query?
Will the condition WHERE employee.employee_id < 1000000 be applied on subquery before or after the GROUP BY operation in the inner query. If the condition is applied after the GROUP BY, how can we avoid that?

Note: There are multiple JOINs and subqueries in the actual query being used.


texts: Postgresql single base on multiple HDD partitions

texts:
Imagine a situation:

I got a Postgres database working on single server on one of HDD partitions (partition size 100GB)
Database grows up to 100GB taking whole free place on the partition
Database stops working because of none free space on HDD

What do I need to do in situation when Postgres database will occupy 100% of partition that the database is on?
Can I move the Postgres database into multiple HDD partitions which there will be similar amount of data on all of them?

If it's possible, how to do that?


texts: Add a DL as a login in azure sql managed instace

texts:
I am able to add individual AD logins as a login in azure sql managed instance, when i try to add a DL is the same way i am getting the following error.

error: Principal '' could not be found or this principal type is not supported.

Query user: CREATE LOGIN [] FROM EXTERNAL PROVIDER
GO

Want to add a DL as login so that all individuals withing the DL can access the instance, isn't  this not possible?
What am I doing wrong here?


texts: Should I put multiple related items in one field in one record or store them separately?

texts:
I'm developing a web app, something like an online dictionary.
One expression might have several different meanings, for example, the word "flight" has two different meanings: a plane trip, and the act of running away. I could use 2 entries (records) to represent them respectively.
One expression might also have several similar definitions(explanations), for example, the expression "be all about" is defined as "be focused on or interested in (a particular thing)" on lexico and is explained as "used to say what the most important aspect of something is
" on oxfordlearners
How do I represent this expression?
I can imagine two approaches, one is put those similar definitions in one entry (record), one field, something like {(definition_A, source), (definition_B, source), ...}, which seems ugly. possibly inefficient when indexing. The advantage is that multiple definitions are linked to one entry.
Another way is to use 2 entries, the question is, how do I link those two entries to one meaning.
Could someone give a hint?

The difference between my question and One field with multiple values? is every driver has a license could be used to identify them.
In the example of "square", I don't have an existing attribute to identify one meaning in "expression table" if I put its definition in another table.
Inside database, it can be done, in reality, it sometimes might not be plausible.
Even if I implement that way, bringing bad user experience by force them fill in a boring long form, I cannot import existing data, e.g. the two items above.
In other words, this method seems to need plenty of human laboring.


texts: Screwed up replication by sharing server ids

texts:
So I had my screwup for the week today.  I was adding another pair of slaves and set the same server ID on the new slave as an old slave.

The layout kind of looks like

Master
|     |
\/    |
oldS  |
      \/
      newS1
      |
      |
      \/
      newS2

So to be clear the old slave (oldS) and the first new slave (newS1) share the same server ID.

It's not circular replication so I'm hoping things will turn out okay.  I wouldn't have expected the fallout though.

Alarms started going off b/c oldS1 started falling farther and farther behind.  Looking at the logdir it was making thousands and thousands of empty relay logs.

I stopped slaving on newS1 and that seemed to clear things up in that oldS1 stopped making empty relay logs and caught back up.

Both slaves seem to be in a consistent state up to the point I stopped slaving on newS1.

Will fixing everything be as simple as bouncing newS1 with a new, unique ID be kosher, especially considering newS1 is itself slaving to newS2?
Is there anything else to be cautious about?
Why did this result in oldS spawning empty relay log after empty relay log?  I would have though oldS and newS1 had no knowledge of the others existence.
I thought relay log rolling was just determined by the slave itself.  Is the master sending some signal that it should spawn a new relay log?


texts: How can I replace constants 5,6 & 8 in sql instead of in condition

texts:
In the following query, I would like to make the values in the IN dynamic.

SELECT DISTINCT party_id
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (5,6,8)


texts: SQL Server - How is a row updated under the hood?

texts:
When SQL Server updates a row in a table, is the update always done 'in place', or is the row deleted first and then re-inserted? If the update is done 'in place', is this always the case, or are there situations where a delete is done first and then an update?

We have a developer on the team that is saying that when a table has a unique constraint on it, and it's included in replication (transactional replication), a delete is always done first and then an insert. I wanted to verify if this is ever the case.

SQL Server 2008 R2


texts: What are the possible causes for sp_reset_connection taking a long time to execute?

texts:
Why would the sp_reset_connection system stored procedure be taking longer than a few milliseconds to execute, as viewed through SQL Server Profiler?

I took a simple trace from a production system using SQL Server Profiler and then used SqlNexus to analyze it. SqlNexus indicates that sp_reset_connection has the highest cumulative duration -- 33% of the overall trace. The observed duration ranges from 0-7 seconds (12 to 6,833,270 microseconds) but averages at 0.956s.

I understand that sp_reset_connection is being called when a pooled connection gets reused. I have seen a suggestion that this can be happening due to extraneous traces, but that doesn't seem to be the case.

I have read what the server is doing when the sproc is called but I don't believe any of those would be problematic in this case -- the code is not leaving open transaction or huge temporary tables that would need to be cleaned up.

I also looked at https://serverfault.com/questions/199974/sp-reset-connection-taking-a-long-time-to-run but it wasn't helpful.

EDIT (2013-12-23):
In all cases, reads and writes are 0 and CPU is almost always 0 (only two instances of non-zero CPU, both at 16ms).


texts:
I wonder what is the best practice to recompile Oracle 10g programmable objects that become invalid due to changes in one of them.

To be more specific, I have a few packages, that use schema level types. I also have triggers in the system that call functions/procedures from packages.
From what I understand, changes in package body don't require any further actions (except recompiling the body). However, if I change package , I have to recompile bunch of triggers that use this package and other packages that have dependencies on it.

So far I just grabbed list of invalid objects and recompiled them or used DBMS_UTILITY.compile_schema.

Thank you.


texts: MySQL no longer starting "An attempt was made to access a socket in a way forbidden by its access permissions." (Windows 10)

texts:
I don't think this is another "some other process is using port 3306 rtfm" kinda question.
I have been running MySQL 8.0.x fine for a couple of months on Windows 10 for some local python development. As of this week it refuses to start (don't know the exact date of when it started, but logs say it was still running 30/10/2020).
2020-11-07T10:57:43.452460Z 0 [System] [MY-010910] [Server] C:\tools\mysql\current\bin\mysqld: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.
2020-11-07T11:01:21.048896Z 0 [System] [MY-010116] [Server] C:\tools\mysql\current\bin\mysqld (mysqld 8.0.22) starting as process 3480
2020-11-07T11:01:21.071269Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-07T11:01:21.618118Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-07T11:01:22.095397Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2020-11-07T11:01:22.174810Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-07T11:01:22.175307Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-11-07T11:01:22.177656Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: An attempt was made to access a socket in a way forbidden by its access permissions.

At first I thought maybe the 3306 port was in used, but that was not the case. Did a reboot to be sure. Stopped Docker. Checked if the exception was still present in the windows firewall. All good.
Tried port 3307 just to see what happened. Disabled Windows Defender. Same result. So my feeling is this might be caused by some Windows Update or changed security setting.
netstat shows no process listening to port 3306:
netstat -ano | find /i "3306"
<no output>

I did google a lot, but couldn't really find anything beyond SELinux or Antivirus preventing stuff.
Any pointers. Probably Windows Defender / Firewall, but don't know what setting to adjust. Mysql exception rule is in place:

Valentijn


texts: How to best store limited specifications?

texts:
I want to create a database for a specific (ecommerce) product and I'm not quite sure what the best way is to store all my specifications. I know exactly which specifications I want and how to store those (one big table since it won't likely change).

For example lets say I sell movies my table would look something like this:

Database ID
Product Title
Type
Region
Packaging

etc.

Where Type is DVD, Bluray, 3D Bluray etc.

My question is how do I store this Type? I could save this as plain-text / enum (method 1) or alternatively in a new table with all the different types and store the ID of DVD (method 2).

The new table is I think the better way to do it but it creates a lot of tables and messy code because I would also need to do this for Region and Packaging. Also it's not very likely that my data will change and that I need to add more Types and Regions.

When do I create a new table (method2)? Only when its 10+ data types or when it frequently changes or something?


texts:
I am analysing a relatively large (25GB compressed MySQL dump) static data set.  Some of the queries I'm writing are several tens of lines long, which makes them difficult to understand, modify, and reuse.  Also many query parts are reused in multiple queries.

I would like to break the processing into smaller parts, so that I can individually write and debug each part and reuse its results in other queries further down the road.  I could break the processing into views, but this would mean that each view would be evaluated multiple times (MySQL doesn't offer materialized views) making later queries very slow.  Alternatively, I could create intermediate tables (and suitable indices).  However, this would force me every time I modify a query, to find and recreate tables that depend on its results; a time-consuming and error-prone process.

I am thinking of automating and managing my workflow with make.  I would write one SQL query to create or recreate a table with its results (and suitable indices) in a separate file.  A make rule would create a dummy file every time the query gets evaluated, thus creating a timestamp, which will be used to determine which queries need to be executed or re-executed.  Dependencies between tables would be recorded in the Makefile, ensuring that all dependent tables get correctly updated.  Dependencies could be even automatically extracted from the queries by looking at the right of (suitably formatted) FROM and JOIN keywords.  This approach would provide me with modularity (one small query per file) and time efficiency (only the minimal set of queries will get evaluated when I change something).  Note that space is currently not a concern in my setup.

Given that the problem I'm facing must be fairly common, I wonder whether I'm missing another established solution.  I've read the How to Eat a SQL Elephant in 10 Bites blog post, and I've also searched for similar questions here, but I can't say I've found something that addresses my requirements and concerns.


texts: Run job once a minute, every day, except during a certain time

texts:
I have a Sql server 2012 agent job which currently runs every minute, every day, etc.

I'd like to modify it so that it will run every minute, every day except on Saturdays between 7:00 pm and 11:30 pm. Is this possible with the job scheduler?

I tried using a daily frequency and I can change the time on a per day basis that way, but not for a specific day.

Current schedule:


texts:
I have a table edges, which describes relations in a graph:

CREATE TABLE IF NOT EXISTS edges (
   src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
  ,tgt INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
  ,rel TEXT NOT NULL
  ,rel_type INT NOT NULL
  ,PRIMARY KEY (src, tgt, rel)
  ,UNIQUE (src, tgt, rel)
  );

After inserts:

select * from edges;
 src | tgt |    rel    | rel_type
-----+-----+-----------+----------
   1 |   2 | 5.4.2.2   |        2
   2 |   3 | 5.3.1.9   |        2
   ...
   5 |   6 | 2.7.1.2   |        1
   5 |   6 | 2.7.1.147 |        1
   6 |   2 | 5.3.1.9   |        2
   6 |   3 | 5.3.1.9   |        2
   ...

I am using rel_type to specify edge directionality (0: undirected; 1: source to target; 2: bidirectional).

Hence, inserting (3, 2, '5.3.1.9', 2) is redundant with respect to the second entry above (for example) -- which already expresses the reciprocal relationship 2 --> 3 and 3 --> 2.

How can I add a constraint that prevents the insertion of those redundant, reciprocal relations -- ideally ON CONFLICT DO NOTHING?

Basically, something like (these don't work: first due to syntax; second due to other issues):

ALTER TABLE edges ADD CONSTRAINT duplicate_rel_check CHECK ((src, tgt) <> (tgt, src) WHERE rel_type = 2);

or

CREATE UNIQUE INDEX ON edges ( greatest(src, tgt, rel_type=2), least(tgt, src, rel_type=2) );


texts: Efficient way to see what pattern matches a single word

texts:
I'm creating a function to find out what search pattern would best fit a single string.  The string to match will be compared against a table of what could be possibly hundreds of possible patterns to see if it matches any of them.

I've got a not-so-elegant function working right now that will read all patterns into a memory table and iterate though them to see if each matches.  It works but makes me sick to my stomach to think of using this in a production environment, especially if the pattern table grows to thousands of patterns.

Example string to match: 'Master'

Possible patterns:

m%r
abc%
xyz%
a%bcd

The outcome would be a match to pattern #1.

In the scenario, I need to be able to match potential part numbers, brands, and other possibly misspelled phrases.  I'm incorporating Double Metaphone but when it comes to other items that only a pattern will catch, I need to know what pattern was the match among many. The "StringToSearch" would only return 1 matched pattern.

Does anyone have theory or other code types that can help me with this reverse pattern matching process?


texts:
I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned.
I am finding that I am getting stuck on PK's and FK's - and really  how to go about it altogether.

So far, I believe I need to create 3 tables (Employees, Skills that will be separated into 4 general areas, and Skill Level which will be 5 different options).

With that, I am at a loss for how I can connect them in a way that each employee can be attached with multiple skills that also shows at what level they're at.

Not necessarily looking for someone to do the work for me, but if someone can clarify whether or not this logic will work and how to get on track - I probably can research the sql code for it.

Thanks!

---EDIT----
Someone raised a good point. A many-to-many relationship seems like the best, but the skills table is something I am not understanding.

If I create a table/entity [Skills] that has the four attributes (financial, technology, creative, other) with specifics listed in each column (i.e. financial would have accounting, recon, etc. and so on), would the skill level be another table?
OR
Would there be separate tables for the four skills and the specifics listed as columns with the skill levels under each?
I'm going crazy trying to figure out the best way to go out this.


texts: Using expressions in the SELECT list to thin down a CASE statement

texts:
I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this:

select
    Column1 =
        case when (Something = 2) and (SomethingElse is null) and (AnotherThing in (1, 2))
        then 1
        else 0
        end,
    Column2 =
        case when (Something = 3) and (SomethingElse is not null) and (AnotherThing in (2, 3))
        then 1
        else 0
        end,
    ...

With particular note to the "when <expressions> then 1 else 0" logic common across all of them.

It seems really bloated and wordy, a heavy-handed use of the case statement - my programming brain is telling me this could be achieved much more succinctly with some basic binary &ing logic, removing the case entirely:

select
    Column1 = (Something = 2) & (SomethingElse is null) & (AnotherThing in (1, 2)),
    Column2 = (Something = 3) & (SomethingElse is not null) & (AnotherThing in (2, 3)),
    ...

Unfortunately whilst SQL Server is able to handle something like select 1 & 0 (returns 0), it seems to choke on parsing expressions in the select list (Incorrect syntax near '=') - is it possible to do something like this? Some kind of evaluation function, maybe?


texts: Move a node from cluster to be a slave of the cluster in Galera

texts:
I have 5 Node in Galera cluster.  MariaDB Server 10.1.21-MariaDB-1~trusty.

I will remove 2 nodes from it but one of them will be a slave. So from 5 nodes 5th one will be completely removed, 4th one will be a slave of 3rd one.

I don't want to rebuild a backup from 3rd one. I just wanted to make 4th out of cluster and become slave of 3rd one.

May I know the procedure for it?

Also why is gtid_current_pos is changing for one node and not on other nodes?

Let me know if you need any clarifications.


texts: Does a non-clustered index make any guarantees about row order?

texts:
I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-clustered index.

By changing the index from clustered to non-clustered, does this make any guarantees about the order in which rows would appear in the table?

This question is mostly for my curiosity; I am going to suggest using an identity column instead, but this request got me thinking. Timestamp could be used but there is a chance rows can be inserted simultaneously.

Thanks in advance for your help.


texts: How to display database structure in phpMyAdmin?

texts:
Is there any way to generate a report of my database structure using phpMyAdmin?  What I would like is a table format like the following, and an Entity Relationship table.

Table name: List
Description: Subscriber list information

Field           Type        Null        Description
ListID          Int         N
ListName        Varchar     N
ListFromName    Varchar     N           Default person represent for the mail
ListReplyTo     Varchar     N           Default return address
ListSubject     Varchar     N           Default subject title of mail
ListRemindSub   Int         N           Whether notify when people subscribe
ListRemindUnSub Int         N           Whether notify when people unsubscribe
ListCreateDate  Date        N

Table name: Campaign
Description: Mail campaign information

Field           Type        Null        Description
CampID          Int         N
CampReplyTo     Varchar     N           Specific return mail
CampFromName    Varchar     N           The person represent for the mail
CampSubject     Varchar     N           Subject title of mail
CampFb          Int         N           Whether include Facebook reminder
CampGA          Int         N           Whether include Google Analytic


texts: Unknown Windows SID Mapped to Proxy During SSIS Package Execution

texts: I'm running SSIS packages stored in a SQL Server 2014 catalog via scheduled SSA jobs. I'm reviewing my SQL Audit capture and noting calls to SSISDB are coming from the proxy I created when looking at the value for [session_server_principal_name], but the SID showing up in [server_principal_name] doesn't correspond to the Active Directory SID. My proxy has an AD SID prefixed with the typical 'S-1-5...' but the audit is returning a SID with a prefix of 'S-1-9...'. Is that indicative of a contained user for SSISDB dynamically created at job runtime to execute the SSIS task?


texts: Duplicate all stored procedures with a new name

texts:
I want to take all stored procedures and create them under a new name. My problem is with procedures that are called from other procedures .

To rename a procedure is no problem for me. But to change/duplicate the procedures that still reference them by their old names is a problem.

For example:

CREATE PROCEDURE dbo.getdata
AS
BEGIN
   EXECUTE dbo.up_updatetable ;
END;


texts: Remove scientific notation from an XML column

texts:
I have a XML column as shown below:

<Root>
  <Row>
    <Rowid>1</Rowid>
    <date>2013-05-06</date>
    <Balance>1.0002E7</Balance>
  </Row>
</Root>

When I query to get the balance, I get it as 1.0002E7. But I want to get it like this: 10002000.

How can I do it?

Can I make any changes so that the XML column itself is stored as I want it without scientific notation?


texts: Dictate edition during unattended install?

texts:
I am creating an automated build of SQL Server installation which is run from a batch file. Is there any way to change the registry, or other method, of deciding which SQL Server edition (Either Enterprise or Standard) will be installed, or are the two versions still separate builds?

If they are separate builds, I guess I could get around it, by having a "master" batch file call an appropriate "child" batch file, but this would mean having a folder which contains both sets of binaries, which I rather avoid.


texts: Dropping unused indexes - Assessing the unexpected dangers

texts:
We have a very large database with hundreds of unused indexes according to DMV statistics, which have been accumulating since the server was last rebooted in July. One of our DBAs made the following cautionary statements, which don't make sense to me:

Before we drop an index we need to make sure if it not enforcing a
uniqueness constraint, as the query optimizer may need this index to
exist.
Whenever an Index is created, statistics related to that index are
also created in SQL Server. A query might not be using the index but
it might be using its statistics. So we may run into a situation ,
after dropping an index a particular query performance goes really
bad. SQL Server does not keep the usage stats of statistics.
Although we have “Auto Create Statistics” feature enabled on our
database, I don’t know which all parameters have to be met
internally before the query optimizer will create the missing
statistics.

Regarding #1, it seems to me that SQL Server would actually do a seek on the index to determine uniqueness before an insert / update is done, and therefore, the index wouldn't show as being not used.

Regarding #2, is this really possible?

By the way, when I say an index is not used, I mean no seeks and no scans.


texts:
What behaviour would PostgreSQL display if for example the script below were called

BEGIN;
SELECT * FROM foo;
INSERT INTO foo(name) VALUES ('bar');
BEGIN; <- The point of interest
END;

Would PostgreSQL discard the second BEGIN or would a commit be implicitly decided on and then run the BEGIN END block at the end as a separate transaction?


texts:
I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-5% of the table. It is frequently updated. The table can be big (>1B rows)
I have frequent queries for numCol=0 and boolCol is false, so I created a partial btree index:
myindex(numCol,boolCol) where numCol = 0 and boolCol is false.
Although the query performance is good, this index will have a single value, and I think that btree might consume more disk space than necessary.
Is there a more suitable index type for this case? What is the best practice in such cases?


texts: How to completely get rid of replication subscriptions?

texts:
I have restored a database in another server. I don't want any of the transactional subscriptions of the former database.  I already called

exec sp_removedbreplication 'MyRestoredDB'
go

And also

exec sp_cleanupdbreplication
go

And

exec sp_replicationdboption 'MyRestoredDB','Publish','False',1
go

But when I try

truncate table dbo.sample

I get the following error:

  Msg 4711, Level 16, State 1, Line 7 Cannot truncate table 'sample'
  because it is published for replication or enabled for Change Data
  Capture.

I verified that the database isn't enabled for CDC.  What else I can do to perform the truncate?  Delete isn't an option for reasons not relevant to the question.


texts:
I'm currently learning SQL Server 2008 and have come across schemas and there is one thing I don't understand about them that they have to have an owner; why can't they own themselves?

My background is mostly with Java and it sounds like a schema is like a package which does not have an owner.


texts: point in time recovery not working with blackhole engine tables involved

texts:
The basic problem summary:

I am trying to do a point in time recovery on a specific table. To speed things up I made every other table use the blackhole engine. Unfortunately this runs into errors like

  ERROR 1032 (HY000) at line 88970: Can't find record in 'a-blackhole-engine-table'

Meanwhile I was able to recover the table without using blackhole engine tables, but this took ages. I would like to avoid this in the future. Therefore I'm still looking for a solution, but I'm running out of ideas. Hope someone can help me here.

EDIT: I found this bug: BLACKHOLE replication with RBR is broken
but it was fixed in 5.1.29. I'm using 5.6.27. Upon inspection this must be a very special statement. Other ROW format statements worked before I got the above error. When I find out, what exactly the issue is, I will of course write a bug report. I'm still hoping though that someone has a workaround of some sort. From my experience bugs in MySQL can take years until they are fixed.

The exact steps I did for this PITR:

The backup was taken with xtrabackup. I restored the backup in a VM as usual with

# xtrabackup --decompress --target-dir=$BACKUPDIR
# xtrabackup --prepare --export --target-dir=$BACKUPDIR

Then I restored just this table with

mysql> CREATE TABLE foo (...);
mysql> ALTER TABLE foo DISCARD TABLESPACE;
# # copied the .frm, .ibd, .exp and .cfg files to the appropriate place
mysql> ALTER TABLE foo IMPORT TABLESPACE;

This worked nicely, so I dumped this table to restore it later.

From the backup I took the binlog file and position.

# cat xtrabackup_binlog_pos_innodb
mysql-bin.006329        206749567

From the live server I dumped the schema without data.

# mysqldump schemaname --single-transaction --no-data --triggers --routines > no_data_dump.sql

To restore this dump with blackhole engine tables:

# sed -f dump2blackhole.sed no_data_dump.sql | mysql schemaname

The dump2blackhole.sed file looks like this:

s/ENGINE=InnoDB/ENGINE=BLACKHOLE/g
/^ \+UNIQUE\|^ \+KEY\|^ \+PRIMARY/s/(\([^,]*\).*)/(\1)/g

The second line is necessary to overcome this bug: Blackhole : Specified key was too long; max key length is 1000 bytes
So there are no more compound indexes. I haven't found another nice way to overcome this.

Now I restored the dumped table so that only this table uses InnoDB.

Finally I tried to do the PITR with

# mysqlbinlog mysql-bin.006329 mysql-bin.006330 -d schemaname --start-position=206749567 --stop-position=865393430 | pv | mysql schemaname

And here comes the above mentioned error.

What I tried to solve this:

First I checked of course which statement in the binary log is causing trouble. The binlog_format is set to MIXED. The statement in question is in ROW format.
Then I tried to replace the table where the error appears with its InnoDB counterpart. Then the same error appears with another table. So I replaced this, too. Finally I ran into duplicate key errors, which happened because I haven't used the table from the backup. My mistake, didn't have enough space. Dropping the primary key also didn't help. Anyway, this can't be the solution to start all over again and again until you have replaced x tables and end up with half of the schema with InnoDB tables again.
I made sure that the data between master and slave is consistent. I checked this with pt-table-checksum. There's no difference between master and slave.
I wanted to make sure, that the table definition is not the problem. The above mentioned bug should have been fixed in MySQL 8.0.11. I already commented in the bug ticket, that it's still not working in 8.0.12. I also tried to overcome this bug with another sed script by making every varchar column shorter but keeping compound indexes. But then it's running into errors when applying the binary log.

That's pretty much it, if I didn't forget something. Has anyone else a good idea how to do this with blackhole engines? Or what is my mistake?

Thanks in advance.


texts: Can SQL Server 2008R2 SSIS packages access SQL Server 2012 databases with driver updates only?

texts:
We have two products running on SQL Server that we will be upgrading to SQL Server 2012 on a staggered schedule. Product B uses SSIS packages (using ODBC drivers) to extract data from Product A's database.

The scenario would be to move Product A to SQL Server 2012 first, with product B to follow in the coming months.  Product B would continue to run in a 2008R2 environment while Product A would be moved to a new SQL Server 2012 environment.

The hope is that there would not be a need for package changes and that installing the new   ODBC Driver (Microsoft ODBC Driver 11) on the 2008R2 SSIS environment would allow the packages to execute against Product A.

EDIT - The package would remain in the SQL Server 2008 format and would be using the SQL Server 2012 Database as it's source.

Does anyone know if this is in fact viable or are there known issues that would require package changes?

Thank you for any help you can provide.

Matt


texts: Postgres: Encrypted User Function. Is it possible?

texts:
is it possible to create a function on PostgreSQL server, which routines can't be seen by any Postgres-user on the server?

The function gets some string as a parameter and outputs a boolean value. But nobody should know, how the calculation of the boolean value works.

Why do I need it: in the function's text is a private key. Maybe there is some other possibility to "hide" the private key or salt securely.


texts: Sql Server Standard 2014: Reindex job locked by other transactions

texts:
On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am.

We have noted that tonight the reindex job got stuck for like 45minutes on a alter index on a particular table because it was locked by another transaction (an UPDATE statement issued by a user, a very common transaction for our system).

The guy responsible for the night check promprly killed the transactions (reindex included) since we were already receiving complaints from the users; when he killed it the current ALTER INDEX statement was blocked by 45 minutes, but the entire REINDEX job took at that moment almost 4 hours (he killed it at 7:51 am).

Is there a way to avoid the REINDEX job to get blocked by other transactions?

EDIT: the fragmentation levels we noted (< 30%) showed that we could probably go for a REORGANIZE instead of a REINDEX thus eliminating the chance of other transactions being locked by the reorganize, but the reorganize itself could still be blocked.


texts: Speed up query that have joined tables and function calls

texts:
I have a Bootstrap Table that use server side pagination to display results from a query like this:

SELECT
    Item.a,
    Item.b,
    Item.c,
    Info1.Name,
    Info1.Name,
    Info2.Name,
    Info3.Name,
    Info4.Name,
    Info5.Name,
    Info6.Name,
    Info7.Name,
    Info8.Name,
    Info9.Name,
    dbo.CalculateSomeText1(Item.Id) AS Text1
    dbo.CalculateSomeText2(Item.Id) AS Text2
    dbo.CalculateSomeText3(Item.Id) AS Text3
    dbo.CalculateSomeText4(Item.Id) AS Text4
    dbo.CalculateSomeText5(Item.Id) AS Text5
    dbo.CalculateSomeText6(Item.Id) AS Text6
    dbo.CalculateSomeText7(Item.Id) AS Text7
    dbo.CalculateSomeText8(Item.Id) AS Text8
    dbo.CalculateSomeText9(Item.Id) AS Text9
FROM
    Item
    LEFT JOIN Info1 ON Item.Id1 = Info1.Id
    LEFT JOIN Info2 ON Item.Id2 = Info2.Id
    LEFT JOIN Info3 ON Item.Id3 = Info3.Id
    LEFT JOIN Info4 ON Item.Id4 = Info4.Id
    LEFT JOIN Info5 ON Item.Id5 = Info5.Id
    LEFT JOIN Info6 ON Item.Id6 = Info6.Id
    LEFT JOIN Info7 ON Item.Id7 = Info7.Id
    LEFT JOIN Info8 ON Item.Id8 = Info8.Id
    LEFT JOIN Info9 ON Item.Id9 = Info9.Id

The table offers a 'master search' that allow user to enter some text and then retrieve records that have that text in any column. (And then there's a filter for each column).

Anyway, the table was fairly large (800K rows) and the raw query above took about 4 minutes.

Is there any trick I could put in here to speed up the thing? If not, do I have to do the search again everytimes user click [next page]? (I'm afraid caching the whole resultset may kill my application)

I figured that searching on function returns requires the function to be executed for each rows which make it worse. But I guess it could not be helped, the function may not be deterministic.

Info1,.. Info9 were just examples. The point is the table Item has a lot of 1-Many references.

The main table is Item, the query above is the raw that retrieve the data, I planned on doing the pagination in application code rather than in SQL (it doesn't support SKIP anyway). If you have any suggestion on paging with SQL, please, I'm all ears.

The main table definition is

Item(
    Id int PK,
    i1 int,
    i2 int,
    ..
    i9 int,
    s1 nvarchar(50),
    s2 nvarchar(50),
    s3 nvarchar(50),
    ...
    s20 nvarchar(50),
    Id1 int FK -> Info1,
    Id2 int FK -> Info2,
    ...
    Id9 int FK -> Info9,
)

The other Info table is fairly simple. They're just for reference. The point is, the main table is large with many text fields, none of them has index (except the keys). And the query is supposed to get all of the field, I was trying to simplify the query for easier understanding.
Most of the function calls involve SELECT on other tables. These functions belong to the legacy system that I have no power over. Too long, too complicated to touch anyway.

What I try to achieve is a way to speed up this query. A general idea is fine. I hope it's clear enough.

I'm trying out an indexed view for this, once I get it going I'll show you guys the result if it actually helps at all.


texts: Ansible: How to change MySQL server root password by reprovisioning the server

texts:
I have provisioned my server with Ansible playbook. I've used the root/bedrock-Ansible playbook.

One of the tasks was to set up mysql server together with mysql root user password.

Now I urgently need to change this password. The steps I took:

I updated variables for Ansible roles
I executed the command ansible-playbook -i hosts/staging server.yml in order to reprovision the server

All tasks were executed as expected (no changes), but the script failed at
[mariadb | Set root user password] with this message:

msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

My guess is that once MySQL root password has been set, reprovisioning the server cannot change this password.

Is it possible at all to change MySQL root password by reprovisioning the server with Ansible? What are my options?


texts:
Is it possible to log each SQL query statement received from clients for a Sybase ASE server 15.7 ,  same as SET general_log = 1; command for mysql.
Thanks


texts: Parallel updates on header and line item tables in SSIS

texts:
This is my scenario

I have 2 tables - a header and a line item table which join on 2 columns contract number and contract version
First, I update the header table to set the status code as 'T' for whatever records have an expiration date in the past on the header table. The query looks like this:

Update Contract_Header
set status_code = 'T'
where status_code = 'A'
and expiration_date < getDate()
Then, I update the line item table to set status code as 'T' for all active line items having an expiration date in the past, and are corresponding to those contract numbers and versions in the HEADER table with a status code of 'A' (The logic might sound a bit crazy but that's how it was designed in the first place)

Update C
set C.Status_Code='T'
FROM Contract_Line_items C
JOIN Contract_Header H
ON C.contract_nbr = H.contract_nbr AND C.contract_version = H.Contract_Version
WHERE H.status_code='A'
AND C.status_code='A'
AND C.expiration_date is not null
AND C.expiration_date < getDate()
Both these queries are put in a Container task in SSIS, and are unconnected data flow tasks as shown in the below image

Technically, only one of those tables - either the header or the line item can be set to 'T' for a contract number and version combination, because by the time the line item table gets updated, the header status for that record would already have been set to 'T'. But, due to some reason, last week, both of them were set to 'T' for the same contract number, version combinations. In the subsequent execution, the issue got resolved by itself and only the header was set to 'T', and not the line items
We think this is some sort of a race condition, but would linking the tasks to execute after each other help the purpose? Is this really a candidate for a race condition? Any other suggestions are also appreciated

Thank You!


texts: Query without precision brings different result

texts:
I have pasted a formula from Excel and pasted onto SQL Server Management Studio.

Once I execute it, I get an unexpected result:

SELECT (((10320238-2092189)/2092189)*100.0)/4.0 --returns 75.000000

But after adding precision to each value within query, it returns the correct result:

SELECT (((10320238.0-2092189.0)/2092189.0)*100.0)/4.0 --returns 98.31866289250000

Why the first query won't bring 98% as result?
What is the inner data type of 10320238? Is it integer?

The engine is

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
    Oct 20 2015 15:36:27
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


texts:
I have a database that is not going to be used anymore but that needs to be kept around for a long time. I'd like to remove it from the production system. My goal is to save storage space when archiving that database. I need to preserve it at full fidelity, so a BCP export is not good enough. I also would not trust generated SQL scripts to preserve it fully.

Two options:

Take an uncompressed backup and compress it using 7-Zip.
Detach the database and compress the MDF and LDF files using 7-Zip.

Is option 2 safe? Which one is preferable? What are important considerations here?


texts:
I have a table in MS Access that has the following fields/data:

School number  Traditional Forms  Sparring         Traditional Weapons  Combat Weapons
-------------  -----------------  ---------------  -------------------  ---------------
2598
3286           2nd                Competed         Did Not Compete      2nd
1642           Competed           Did Not Compete  3rd                  Did Not Compete
1642           3rd                3rd              2nd                  Competed
3116           1st                1st              1st                  1st
2598           Competed           2nd              Did Not Compete      3rd
2396           Did Not Compete    Competed         Did Not Compete      Competed
3286           Competed           Competed         Competed             3rd
3116           1st                1st              1st                  1st
3169           Did Not Compete    Did Not Compete  Competed             Competed

I have a query that will count individual entries by school as follows:

SELECT [tourney report].[School Number], COUNT([School Number]) as Entries
FROM [tourney report]
WHERE (Sparring IN ('1st','2nd','3rd','Competed') OR [Traditional Forms] IN ('1st','2nd','3rd','Competed') )
GROUP BY [School Number]

This gives me the total number of entries in either Sparring or Forms per school. I can repeat this for each of the other categories (weapons/combat weapons). What I would like to get is a combination query that shows total entries per school, which is a total of each of the forms/sparring (Forms and sparring count as one entity), weapons, combat totals. Example:

School number   Forms/Sparring  Weapons Combat  Total Entries
1234                22            15      20       57

My further attempts resulted in putting the following query together, which gives me most of what I need:

SELECT [tourney report].[School Number],
       SUM( IIF( ([tourney report].[Traditional Forms] = "Did Not Compete" OR LEN([tourney report].[Traditional Forms]) = 0) AND ([tourney report].[Sparring] = "Did Not Compete" OR LEN([tourney report].[Sparring]) = 0) , 0, 1) ) AS [Total Form and Sparring],
       SUM( IIF( ([tourney report].[Traditional Weapons] = "Did Not Compete" OR LEN([tourney report].[Traditional Weapons]) = 0), 0, 1) ) AS [Total Weapons],
       SUM( IIF( ([tourney report].[Combat Weapons] = "Did Not Compete" OR LEN([tourney report].[Combat Weapons]) = 0), 0, 1) ) AS [Total Combat]
FROM [tourney report]
GROUP BY [School Number]

This gives me the totals for each of the categories that I want, but not the total. I tried the following:

SUM( [Total Form and Sparring] + [Total Weapons] + [Total Combat]) as [All Entries]

That, however, gives the error

  Subqueries cannot be used in the expression (SUM( [Total Form and Sparring] + [Total Weapons] + [Total Combat]) as [All Entries])

How can I get the total of all the sum columns as a fourth total?


texts:
I have two table that table 1 contains Number, code, ID and table 2 contains Name, Family name, Number, ID, code, Phone number,...

Now I want to make a query that contains table 2 fields but the query should check that if for example table2 contains  table1.Number, show the table 2 fields that contains Table1.Number and if table2 doesn't contains the Table1.Number, show the table 2 fields but just fill the fields that table1 and table 2 have them and just for example put "-" in the filed Phone number because table1 doesn't has that but it has code field and table 2 has it too so it should be fill it with the table 1 parameter
And this proccess should be continue for all of the codes of table 1

For example:

Table1 has this data

Table 2 data

Query example:

Can someone help me with this please?


texts: Convert table from MyISAM to InnoDB and keep MyISAM rows order

texts:
I'm currently rewriting old project and I've got this strange scenario.

It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part after the id- in the img column is the same for every row for pid.

I want to migrate this table to InnoDB, but keeping this order. InnoDB orders by id by default.

SELECT * FROM products_gallery WHEREpid= :productID

This is MyISAM

There is no particular order, probably they are stored in the way the are inserted, altrought if that was the case they should be ordered by id. I don't know that is really happening, but I think MyISAM has it's own order then no ordering is specified. I want to migrate this to InnoDB and keep this same order.

The only solution coming to my mind is to go trough product images before changing the storage engine and record the position rows are returned. After that I'll add new column to the table position and update it with the recorded position and sorting by position after migration.

So my question is there any easier solution?


texts:
Short version: Running a RESTORE VERIFYONLY throws one of these errors:

  During restore restart, an I/O error occurred on checkpoint file '\Server\Backup\SQLDirectBackup\DatabaseName\RestoreCheckpointDB24.CKP' (operating system error 5(Access is denied.)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

Or

  During restore restart, an I/O error occurred on checkpoint file '\Server\Backup\SQLDirectBackup\DatabaseName\RestoreCheckpointDB11.CKP' (operating system error (null)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

More information:

Using Ola's SQL Server Maintenance Solution
The file share is on a Dell DR4300 device
Has occurred on multiple servers using this device for backup storage
Different servers, service accounts, versions of SQL Server have all thrown this error
Not consistent or reproducible as far as I can tell (happened Monday & Wednesday but not Tuesday)
Subsequent runs of the backup jobs have succeeded without error
Time of day does not seem to be relevant, it's occurred at all hours
The error is not immediate, it occurs minutes into the execution of the command
Default backup directory points to \\Server\Backup\SQLDirectBackup\DatabaseName\
Hasn't happened with an actual restore yet... but I'd like to avoid that. Hence the post.

I've never seen this error before, and all my research is coming up empty. It is possible that this has been happening for a long time but I am new to the company and only recently added Severity 16+ alerting so I cannot be sure.

Anyone have any ideas? Safe to ignore? Potential time bomb? I'm stumped.


texts: Loop through all users in MySQL and add REQUIRE SSL flag

texts: How do I loop through all users (we have > 3000) in a MySQL database, and add the REQUIRE SSL flag to the every account? I don't want to modify the existing users permissions, host, or password, simply add REQUIRE SSL


texts: Code creating clustered columnstore index while maintaining row order

texts:
I want to convert a rowstore table to a columnstore table by creating a clustered columnstore index. There are three columns in the table: id,time, and value.

The table is ordered by id and time before creating columnstore index; however, after creating columnstore index, the row order is messed up. I thought it might be due to the parallelism and added the maxdop = 1 option, but that didn't fix the problem. Can anyone help me with this?

Here is the code creating tables and indexs:

-- creating rowstore table
drop table if exists tab1_rstore
select id, time, value
into tab1_rstore
from tab0
order by id_loan, period
option(maxdop 1)

-- creating clustered index on rowstore table
create clustered index idx on tab1_rstore (id,time)

-- creating columnstore table
select *
into tab1_cstore
from tab1_rstore
    option(maxdop 1)

-- comparing the first two rows from these two tables
    select top 2 *
from tab1_rstore

    select top 2 *
from tab1_cstore

The screenshot of the query results:

-- creating clustered columnstore index
create clustered columnstore index idx on tab1_cstore
with (maxdop = 1)

-- comparing the top two rows again
select top 2 *
from tab1_rstore

select top 2 *
from tab1_cstore

The screenshot of the query results with columnstore index:

My understanding is that the order of rows is determined by the compression algorithm and there is nothing we can do about it, see the limitation and restriction in the document here with the following quote:

  Cannot include the ASC or DESC keywords for sorting the index.
  Columnstore indexes are ordered according to the compression
  algorithms. Sorting would eliminate many of the performance benefits.

I'm using SQL Server 2016 Developer Edition on Windows 10 64bit.


texts: Multiple BIT datatype columns in the same table

texts:
Consider the following information coming from Microsoft SQL docs pages:

  The SQL Server Database Engine optimizes storage of bit columns. If
  there are 8 or fewer bit columns in a table, the columns are stored as
  1 byte. If there are from 9 up to 16 bit columns, the columns are
  stored as 2 bytes, and so on.

See: https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017

So, if I create a new table like this:

CREATE TABLE MyTable (
    Id int PRIMARY KEY,
    Value1 bit,
    Value2 bit,
    Value3 bit,
    Value4 bit,
    Value5 bit,
    Value6 bit,
    Value7 bit,
    Value8 bit)

...then all the bit columns (Value1, Value2, ... Value8) in one row should take up one byte. Or?

When we take a closer look at it, Value1 field of a row can have values 0, 1 or NULL inside. This means that field is actually of a 3-state value, or a ternary value. 2 such values can represent dictionary of 3*3=9 values. 8 such values are a dictionary of 6561 values.

If we're thinking about the memory footprint of such a row, 8 bits should take up one byte. This means that this one byte encodes 6561 values instead of 256 values. This is obviously wrong.

So, either MS SQL docs are misleading or we're talking about some quantum computing here. Of course it's the former, but what I'd really want to know is the answer to this question: how are nullable bits (and of course that SQL Server bits are nullable by default) really stored in SQL Server underlying structures?


texts:
I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key...

If I create view:

CREATE OR REPLACE VIEW view_image_by_uploaded AS SELECT
  i.*,
  ( SELECT COUNT(l.id) FROM imglike AS l WHERE l.imageid = i.id ) AS likes,
  ( SELECT COUNT(d.id) FROM download AS d WHERE d.imageid = i.id ) AS downloads,
  ( SELECT COUNT(c.id) FROM comment AS c WHERE c.imageid = i.id) AS comments
FROM image AS i WHERE i.verificationid IS NOT NULL;

Then

EXPLAIN SELECT * FROM view_image_by_uploaded ORDER BY id DESC;

Returns this:

+----+--------------------+------------+------+------------------+------------------+---------+------------------+------+----------------+
| id | select_type        | table      | type | possible_keys    | key              | key_len | ref              | rows | Extra          |
+----+--------------------+------------+------+------------------+------------------+---------+------------------+------+----------------+
|  1 | PRIMARY            | <derived2> | ALL  | NULL             | NULL             | NULL    | NULL             |  200 | Using filesort |
|  2 | DERIVED            | i          | ALL  | FKimage421797    | NULL             | NULL    | NULL             |  209 | Using where    |
|  5 | DEPENDENT SUBQUERY | c          | ref  | FKcomment319401  | FKcomment319401  | 8       | scalablegfx.i.id |    1 | Using index    |
|  4 | DEPENDENT SUBQUERY | d          | ref  | FKdownload898850 | FKdownload898850 | 8       | scalablegfx.i.id |    1 | Using index    |
|  3 | DEPENDENT SUBQUERY | l          | ref  | FKimglike89127   | FKimglike89127   | 8       | scalablegfx.i.id |    1 | Using index    |
+----+--------------------+------------+------+------------------+------------------+---------+------------------+------+----------------+

And I am confused about that filesort.

But if I call directly this:

EXPLAIN SELECT i.*,
  ( SELECT COUNT(l.id) FROM imglike AS l WHERE l.imageid = i.id ) AS likes,
  ( SELECT COUNT(d.id) FROM download AS d WHERE d.imageid = i.id ) AS downloads,
  ( SELECT COUNT(c.id) FROM comment AS c WHERE c.imageid = i.id) AS comments
FROM image AS i WHERE i.verificationid IS NOT NULL
ORDER BY i.id DESC;

I receive:

+----+--------------------+-------+-------+------------------+------------------+---------+------------------+------+-------------+
| id | select_type        | table | type  | possible_keys    | key              | key_len | ref              | rows | Extra       |
+----+--------------------+-------+-------+------------------+------------------+---------+------------------+------+-------------+
|  1 | PRIMARY            | i     | index | FKimage421797    | PRIMARY          | 8       | NULL             |  209 | Using where |
|  4 | DEPENDENT SUBQUERY | c     | ref   | FKcomment319401  | FKcomment319401  | 8       | scalablegfx.i.id |    1 | Using index |
|  3 | DEPENDENT SUBQUERY | d     | ref   | FKdownload898850 | FKdownload898850 | 8       | scalablegfx.i.id |    1 | Using index |
|  2 | DEPENDENT SUBQUERY | l     | ref   | FKimglike89127   | FKimglike89127   | 8       | scalablegfx.i.id |    1 | Using index |
+----+--------------------+-------+-------+------------------+------------------+---------+------------------+------+-------------+

Which is without filesort


texts: Is it safe to remove an entry in a "config.mongos" MongoDB collection?

texts:
There are old entries in my "config.mongos"; they reference hosts which previously had "mongos" running on them but no longer do.

Can I safely remove those entries?

e.g.,:
# Old entries
{ "_id" : "silicon:27117", "ping" : ISODate("2012-12-27T19:51:04.560Z"), "up" : 9301, "waiting" : true }
...
# Current entries
{ "_id" : "app1:27017", "ping" : ISODate("2013-06-07T17:12:02.856Z"), "up" : 5593404, "waiting" : true }
{ "_id" : "app2:27017", "ping" : ISODate("2013-06-07T16:17:49.812Z"), "up" : 5590271, "waiting" : false }
{ "_id" : "app4:27017", "ping" : ISODate("2013-06-07T17:12:02.110Z"), "up" : 5593413, "waiting" : true }
{ "_id" : "app3:27017", "ping" : ISODate("2013-06-07T17:12:02.854Z"), "up" : 5593578, "waiting" : true }

Update:

One reason why having these old entries in config.mongos is a problem is that making changes to the shard takes a long time, while we wait for old mongoses to time out.

For example, stopping the balancer takes a long time (several minutes):

mongos> sh.stopBalancer()
Waiting for active hosts...
Waiting for active host magnesium:27117 to recognize new settings... (ping : Thu Dec 22 2011 03:08:14 GMT+0000 (UTC))
Waited for active ping to change for host magnesium:27117, a migration may be in progress or the host may be down.
Waiting for active host Boron:27017 to recognize new settings... (ping : Tue Jan 31 2012 21:48:12 GMT+0000 (UTC))
Waited for active ping to change for host Boron:27017, a migration may be in progress or the host may be down.
Waiting for active host Beryllium:27017 to recognize new settings... (ping : Wed Feb 01 2012 17:34:15 GMT+0000 (UTC))
Waited for active ping to change for host Beryllium:27017, a migration may be in progress or the host may be down.
Waiting for active host helium:27017 to recognize new settings... (ping : Mon Jun 10 2013 19:07:41 GMT+0000 (UTC))
Waited for active ping to change for host helium:27017, a migration may be in progress or the host may be down.
Waiting for active host boron:27017 to recognize new settings... (ping : Sun Jun 02 2013 08:46:57 GMT+0000 (UTC))
Waited for active ping to change for host boron:27017, a migration may be in progress or the host may be down.
Waiting for the balancer lock...


texts: How to find Sum of dynamically generated time attribute in sql server 2012

texts:
I have a table with two columns (TASKID and Dateadded). I am trying to find difference between timings and would like to calculate the sum of the diff (time) attribute.

 select taskid,dateAdded,CAST((dateadded -lag(dateadded) over (order by dateadded)) as time )as diff
 from xyz where taskid=21240923

with this statement got the difference between times but I am unable to sum the dynamically generated time column diff.

Could any body please suggest to achieve this goal?

https://www.blogger.com/blogger.g?blogID=1341056964785354138#editor/target=post;postID=5797398234800649517;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname


texts: Postgres 11 declarative partitioning - using large number of partitions

texts:
We are running a system using Postgres 11 and we implemented partitioning with ID which is most convenient to us but which produces many partitions (currently over 1000 with expected total of 10000 within next 5 years). We generate 30 million rows per month with heavy indexing. Partition rows are never updated and our queries always target single partition.

Now we have run into a problem that we exhausted maximum allowed locks (cca. 25000). Is increasing max_locks_per_transaction and max_connections a viable solution with expected rise in partition number? Or should we switch to hash or date-range partitioning? And if so, what would be the optimal partition size and count?


texts: I need help using hierarchyid datatype and requiring a unique constraint on a varchar field

texts:
I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had.

    CREATE TABLE [dbo].[tbl_server_group]
    (
        [refid]     [int] IDENTITY(1,1) NOT NULL,
        [refhid]    [hierarchyid] NOT NULL,
        [reflvl]    AS refhid.GetLevel() PERSISTED,
        [refdate]   [datetime] NOT NULL DEFAULT GETDATE(),
        [refname]   [varchar](50) NOT NULL
    )

My issue is that I need to be able to require that the refname is unique but only within the container that it is in. Being that I am using this to represent servers, I wanted to emulate a DNS tree of sorts but add other layers to it as well. My example being the following:

    ROOT
    | something.com
    || www
    ||| index.html
    || staging
    ||| testbed.php
    | someotherthing.com
    || test
    ||| index.html

I want to make sure that something.com only has one "www" and that www only has one "index.html".

Is there anyway to do this with constraints? If not, is there an efficient way to force uniqueness?

Thanks


texts: Any better way out of MySQL InnoDB log "in the future"?

texts:
I've got this InnoDB error in MySQL 5.0.  Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward.  Now after a clean startup, InnoDB has done its "crash recovery".  I went through the innodb_force_recovery=4 business, repaired a hung MyISAM table, and now replication is ready to go, apart from this.  Big numbers commified:

111116 15:49:36  InnoDB: Error: page 393457 log sequence number 111 561,760,232
InnoDB: is in the future! Current system log sequence number 70 3,946,969,851.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.

This is on a slave server.  The above error spews by the hundreds.  I found this answer: "insert and delete > 64 GB worth of data, so that the log sequence number becomes inflated big enough".

http://forums.mysql.com/read.php?22,50163,50163#msg-50163

That magic number of 64GB comes from 4GB*16 where that guy's innodb log "major number" needed to increase from 0 to 15.  Mine's going from 70 to 111 = 164 GB.  This will take 5 days.  I'll keep working on speeding up my script, and running it in parallel to speed this up.  In the meantime, I'm hoping someone else has a better answer.  This is silly.


texts: 'accidentally' set 'CHANGE MASTER TO' on wrong server

texts:
while setting up the replication I accidentally did the 'CHANGE MASTER TO' on the master server instead the slave.

When I noticed the mistake I just did a 'STOP SLAVE' on the master and setup the slave then correctly, replication is working.

I am just wondering if there is a need/way to remove this 'change master to' on the master server again, I havent found anything in the Documentation and 'reset master' seems to be something else.

I can't see any issues currently, so maybe I don't need to bother about it. If possible I'd stil; like to know how to correct it.

Thanks


texts: Measure Performance for INSERT, UPDATE and DELETE commands on DB2

texts:
I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day.

I insert rows to this table via java-JDBC INSERT commands and they usually take less than a second to complete but sometimes (I'm still not sure what is the pattern) it can take up to a minute to complete the INSERT command and since I don't have a timeout configured on the JDBC driver, it waits until the command is completed and then carries on.

These INSERT commands are executed thousands of times per day but this only happens on about 5 or 10 of them almos every day. The table has some indexes and a trigger attached on AFTER INSERT event.

Is there any tool or technique I can use to measure what is taking so long to execute on my INSERT command? I want to know if it is the indexes, the trigger or, for any reason, the query itself.


texts: SQL Server 2017 on RHEL Linux with IDM for AD authentication

texts:
We are using Red Hat IDM to authenticate AD users to SQL Server. Users can login successfully to SQL Server with SSMS and SQL CMD with AD Authentication. But running some elevated commands from SSMS or SQL CMD can run only once in session and it kick out AD authenticated users from SQL Server with the following error:

Error 15404 ‘Could not obtain information about Windows NT group/user '%ls', error code 0x80090304'


texts: using *tables* as Table-Valued Parameters (TVP)

texts:
MS SQL 2008 supports TVP: a useful feature for bulk uploading data to a stored proceedure for processing.

Rather than create a user-defined type, is it possible to leverage an existing table definition?  For example, is it possible to create a stored proceedure with the following signature?

CREATE PROCEDURE usp_InsertProductionLocation
@TVP **LocationTable** READONLY

The documentation seems to suggest that this is not possible.

SAMPLE CODE

/*
Sample code from:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
*/

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

/*
The following is not part of the original source code:
*/

CREATE TABLE LocationTable(
 LocationName VARCHAR(50)
, CostRate INT );
GO


texts:
Is there a way to "roll up" duplicate records such as time-series data in MySQL? I'm currently doing this in the application code after retrieving full time-series but the transmission of mostly redundant rows seems a nuisance. Here is an example:

Original data:

 date | state
..30  | A
..29  | A
..28  | A
..27  | D
..26  | D
..25  | A

Resultset with "rolled up" consecutive duplicate records:

 date | state
..28  | A
..26  | D
..25  | A

Thanks!


texts:
I am working on a POC of using Solr with SQL Server.

I have a very complex data model with SQL server which required a bunch of joining and scalar functions to strip some mark up and other stuff.

This is turning out to be performance bottleneck. To address this issue, we have considered NoSQL (MogoDB) or Solr with SQL Server as our options.

Using MongoDB, we attach a replication events for all CRUD operations which will carry over the data to MongoDB as well after successful insert, update, delete on SQL Server. And when we have to perform a search we do the search directly on Mongo Collections.

This sounds very cool as we have 32 tables joining in this search, which can convert to 2 collections in MongoDB

On the other note we are also exploring our options using Solr with SQL Server with DataImport

My concern is, based on this article http://www.codewrecks.com/blog/index.php/2013/04/29/loading-data-from-sql-server-to-solr-with-a-data-import-handler/ I have to do a import for each entity

How does the joining search works with Solr? Should I import each table from SQL to Solr and then write a join login against Solr APIs
Can I Import Multiple Entities at once? Should I create a view for expected result set (denormalized) and import that view to Solr?
Will these imports have to be done on regular intervals? After a import
if there is new data, does Solr API reflects that change? or I have
to do a import first and then do the search against Solr API

Finally can I compare Solr with MongoDB, if anyone has done this kind of evaluation please share your thoughts.


texts:
I am running 10.3.18-MariaDB-log MariaDB Server but for one application it require I run mysql 5.7, so I am installed that to run under another port which works fine.

However for the software that requires mysql 5.7 I cannot seem to pass this as my mysql client version still shows up as mariadb's 5.5 mysql client.

This is my.cnf for the mysql 5.7 version

[mysql]

# CLIENT #
port                           = 3307
socket                         = /var/lib/mysql2/mysql2.sock

[mysqld]
port = 3307
socket                         = /var/lib/mysql2/mysql2.sock
datadir = /var/lib/mysql2
pid-file=/var/lib/mysql2/mysqld2.pid

is there someway to specify what mysql client binary I can run ? I am not sure what I am doing wrong?

/usr/local/mysql2/bin/mysql -V
/usr/local/mysql2/bin/mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

/usr/local/mysql2/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 99552
Server version: 5.5.5-10.3.18-MariaDB-log MariaDB Server


texts:
I've inherited a flat table of data consisting of around 2 million records.  Looking at the data its already in 1NF, i'd like to get this into 3NF.

The data is Australian so suburb can't determine state as suburb names exist in more than one state, plus one postcode often covers more than one suburb.  Meaning suburb, state, postcode should be a super key?

There are multiple companies at different sites, some have the same phone number (freephone 1300 or 1800) and others have distinct phone numbers, every record has a fax number and most have a phone number.   ContactName is mostly blank so phone and fax numbers relate to companies not contacts.

Selection of records by users is typically RLIKE on the field BusinessTypeDescription coupled with geographic criteria which of course means a full table scan on every query and therefore super slow results.

Is the proposed table structure below in 3NF and is it practical?

EXISTING TABLE
-----------------------
ID (PK)
CompanyName
Address
Suburb
State
Region
Postcode
Country
ContactName (this field is mostly empty)
Phone
Fax
Mobile
Email  (this field is mostly empty)
Website  (this field is mostly empty)
PremiseType
BusinessTypeDescription
ANZSICCode
ANZSICDescription
RecordSource

This is the proposed structure...

COMPANY
---------------
ID (PK)
CompanyName
Address
LocationID (FK)
PhoneID (FK)
FaxID (FK)
MobileID (FK)
ContactName
PremiseType
BusinessTypeID (FK)
ANZSICCode (FK)
Email
Website
RecordSource

LOCATION        COUNTRY         REGION
--------------  --------------  ------------
ID (PK)         ID (PK)         ID (PK)
Suburb          Country         Region
State
Postcode
RegionID (FK)
CountryID (FK)

PHONES
------------
ID
Phone

MOBILES
------------
ID
Mobile

FAXES
------------
ID
Fax

BUSINESSTYPE
----------------------
ID
BusinessTypeDescription

ANZSICS
----------------------
ANZSICCode
ANZSICDescription


texts: How can I efficiently query products with many-to-many ingredients and nested products?

texts:
I currently have three tables: product, ingredient, and product_ingredient (m2m relationship).

A product has many ingredients and nested products through product_ingredient. The nested products themselves have many ingredients and nested products.
How can I efficiently query these relationships so that a single query can select a product's ingredients through multiple levels of nested relationships?

The product ingredients should be ordered by their position int, with respect to its product_id.
Each level of nested ingredients need to be grouped and ordered by the product they're related to.
The entities are read more frequently than written, so read optimized would be preferred if there are any cache-based approaches (like materialized views).
The actual structure has foreign keys on the ingredient table which are aggregated in different ways.

For example:
> select * from product;
 id | name
----+------------------------
  1 | chocolate
  2 | chocolate chip cookies

> select * from ingredient;
 id | name
----+--------------
  1 | cocoa
  2 | cocoa butter
  3 | flour
  4 | eggs
  5 | baking soda

> select * from product_ingredient;
 id | product_id | ingredient_id | nested_product_id | position
----+------------+---------------+-------------------+----------
  1 |          1 |             2 |            (null) |        0
  2 |          1 |             1 |            (null) |        1
  3 |          2 |             3 |            (null) |        0
  4 |          2 |             4 |            (null) |        1
  5 |          2 |        (null) |                 1 |        2
  6 |          2 |             5 |            (null) |        3

My ideal query would produce the following output:
> select ...;
 product_id |           product_name | ingredient_id | ingredient_name | position
------------+------------------------+---------------+-----------------+----------
          2 | chocolate chip cookies |             3 | flour           |        0
          2 | chocolate chip cookies |             4 | eggs            |        1
          1 |              chocolate |             2 | cocoa butter    |        0
          1 |              chocolate |             1 | cocoa           |        1
          2 | chocolate chip cookies |             5 | baking soda     |        3

Note that the sort order is maintained using position through the nested relationships.
Additionally, how do I prevent a circular relationships, where a product_ingredient references a nested_product_id that is a parent or grandparent relation? Is there a CONSTRAINT that I can use?


texts: can sql generate a good plan for this procedure?

texts:
I have read that procs that do not always do the same thing per se, will not always have a good plan generated.  That is (correct me if I'm wrong), if I have a proc that if the day is even it reads from table X otherwise it reads from table Y and the first time it executes is even, then the plan generated will be optimized for reading from table X and not Y, and sql will use that plan even when reading from Y, that imho is simple to understand and try to avoid, but what about procedures that act on the same table.

Like the sample below (obvioulsy a very simple example), if the first time it runs, and the item does exist, and subsequent runs generally do not (or vice-versa), would I end up with a "not so great" plan which would affect performance?

CREATE PROCEDURE dbo.MyProc
(
@data    VarChar(25),
@name    VarChar(25)
)

AS

    IF NOT EXISTS (SELECT * FROM dbo.MyTable WHERE myname = @name)
    BEGIN
        INSERT dbo.MyTable (myname, mydata)
        VALUES (@name, @data)
    END
    ELSE
    BEGIN
        UPDATE dbo.MyTabel
        SET mydata = @data
        WHERE myname = @name
    END


texts:
i wanted ask about gMSA and MSA accounts and overall do you create a single account or multiple for each service (Engine, Agent, SSIS, SSAS, SSRS).
Basically i am trying to figure out if I should follow the same design as i have when using Domain accounts.
In additions, would you still use gMSA for a single instance or would you than go for MSA accounts and reserve the gMSAs for Always ON AG?
The reason i am asking about gMSA and MSA is that security at my company is cracking down on service accounts that have non expiring passwords and I am trying to save my self from the calls when the account expires and the servers are down.


texts:
I have a table in a local MS SQL Server database that has 72 columns and over 8 million records.
Most of the columns are floats except for the primary key which is an INT Identity column.
The table also has 3 indexes.

These records are the saved output from a utility I wrote that processed around 100 Billion calculations on different target audiences. I have saved the best and worst results for each target audience for future lookup.
I would now like to move all these records into my staging server's database.

What is the best way to copy transfer these records?

I tried Red Gate, and it just dies and says out of disk space.
My disk has 22 GB free.
Thanks.


texts:
I wish to upgrade my mongo from 2.4 to 3.6. As i see in the official documents, and googling, that i need to do an upgrade for each 0.2 version, meaning doing 6 (!!!) upgrades in order to upgrade my db to 3.6.

Is this in any way avoidable? this process is messy as it requires doing so on many servers and on many of our clients.

Furthermore, i cannot seem to make any upgrade work. i have downloaded the 2.6 version and tried to make an upgrade but all the available executable's from mongo's official website have resulted in an unknown exception.

Any advice will be welcomed as im a bit lost in this process.


texts:
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit column on original table with corresponding result. Conditions are that it has to have happened in the past (before current row date) and I only want the last 2 records and column for 'rush_order' has to have value 'yes'.
Right now my query gets all records but I only want last 2. I tried to use LIMIT in each of the 4 subqueries and also tried OFFSET 0 and FETCH FIRST 2 ROWS ONLY but can't get it to work. If there is a way to use the OFFSET it could eliminate the need to use the comparison of date < current_row.date as the table will always be ordered by ascending date.
Second, if there is advice on how to use CASE here I think it would be much faster. I am running 4 subequeries for every row as it is; there is too much repetition.
Third, I think another way around this is to stack the name1 and rating1 on top of the name2 and rating2 on top of each other. Not necessary but am open to another way of solving this.
Thank you
CREATE TABLE IF NOT EXISTS fruit (
   fruit_id INTEGER  NOT NULL ,
   name1 varchar(50) NOT NULL,
    name2 varchar(50) NOT NULL,
   rating1 integer NOT NULL,
    rating2 integer NOT NULL,
     delivered_on date NOT NULL,
     rush_order varchar(3) NOT NULL,

   PRIMARY KEY  (fruit_id)
 )

INSERT INTO fruit (fruit_id, name1, name2, rating1, rating2, delivered_on, rush_order) VALUES
(1, 'Apple', 'Banana', 5, 4,'2015-10-01', 'yes'),
(2, 'Pear', 'Banana', 3, 4,'2015-10-27', 'yes'),
(3, 'Apple', 'Pear', 5, 4,'2015-10-29', 'no'),
(4, 'Banana', 'Apple', 2, 5,'2015-11-27', 'yes'),
(5, 'Banana', 'Apple', 4, 1,'2015-11-29', 'yes'),
(6, 'Apple', 'Banana', 4, 2,'2015-12-02', 'no'),
(7, 'Pear', 'Apple', 3, 5,'2015-12-20', 'yes');

SELECT *,
    COALESCE(
        (SELECT sum(f2.rating1)
    FROM fruit f2
        WHERE f1.name1 = f2.name1 AND f1.delivered_on > f2.delivered_on),0) +
    COALESCE(
        (SELECT sum(f3.rating2)
    FROM fruit f3
        WHERE f1.name1 = f3.name2 AND f1.delivered_on > f3.delivered_on) ,0)
            AS fruit1_rating,
    COALESCE(
        (SELECT sum(f4.rating2)
    FROM fruit f4
        WHERE f1.name2 = f4.name2 AND f1.delivered_on > f4.delivered_on),0) +
    COALESCE(
        (SELECT sum(f5.rating1)
    FROM fruit f5
        WHERE f1.name2 = f5.name1 AND f1.delivered_on > f5.delivered_on) ,0) AS fruit2_rating
FROM fruit f1;

Expected result:


texts: Very slow Insert and update on 20 core 250 Gb RAM dedicated MYSQL 5.5 server on Ubuntu

texts:
Most of the data is in tbl_user_ads. Its a huge table.

Many concurrent processes (over 1000) try to select, insert or update this table.

The CPU utilization is just around 2%.

As I increase the number of processes which access this table the performance drops further.

I am getting lots of deadlocks.

Thanks for your time and help.

innodb status

my.cnf customizations:

innodb_buffer_pool_size=220G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size=2048M
innodb_log_file_size = 1024M
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_locks_unsafe_for_binlog = 1
innodb_lock_wait_timeout = 100


texts: how do I configure Oracle Net Listener on a server with two network connections

texts: I have a server that I have virtualized and as such it has two network connections, one to simulate a network between several virtual machines and the other to connect to the host physical network. when this server starts, oracle net listener does not start automatically. My understanding of this issue is that there is a conflict in host address configuration in my .ora file seeing as during initial install, net config assistant sets it up with a host address. Can someone advise me on how to correctly configure this Listener to have it startup automatically?


texts:
I want to write a script to backup a table using COPY.

psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > backup.csv.bz2

Now I want to log how many lines are copied to zip file? I want to calculate it while copying, not with another command.


texts: Index creation in extremely slow in Mysql Aurora 5.7

texts:
I have a table in mysql Aurora 5.7 version .

The db instance has very good configuration 488 GB ram (db.r4.16xlarge).

Data base is static data base no application is running on this .

I have 1.5 Billions record approx 2TB size in the table .

The table does not have any index except primary key .

Table has 35 Columns .

This table does not have any partition

Now i need to create Index on nine other columns and here i hit hard .
It takes 5 Hours to create one index and when i run command to create all 9 index at same time it took almost 10 hours and then i cancelled .

Is there any way i can make this index creation faster ?
Is creating 10 partition will help creating Index faster ?

Why i need to create Index on 1.5 Billions records is because i have migrated my table from Orcale Source to Mysql using AWS DMS service .

Before migration i need to drop index at target and then have to migrate so that migration will be faster  and it is actually very fast .

Without Index migration took 10 hours where as with Index it is taking 25 Hours and some time it fails as well .


texts: Required free Disk Space for Index rebuild offline Operations

texts:
I have SQL Server 2017 Standard Editon with two Databases.

The current total index size of Database1 is 111 GB and for Database2 is 122 GB.

Since I'm on Standard Edition I can only rebuild the Index offline.  We have split the Database-Files on Disk D: and Log-Files on E:. Free disk space on D: is 290 GB and on E: 35 GB.

How can I calculate the required free disk space for offline index rebuild on D: and E:?

For online Index rebuilds, I've found a Microsoft article stating that total space required is approximately 1.2 times the total index size. Is the same estimate true for offline rebuilds?  Do I need this free disk space for Database-Files and for Log-Files?


texts: How to replicate MongoDB to a remote location?

texts:
I'm working on a migration of MongoDB Replica Set from on premises to AWS. It's 300 GB of data. I've evaluated the following options:

MongoDB replica sets. It's really slow, initial sync takes 4/5 days.
Rsync. Rsync while MongoDB is running, then stop it, and run Rsync again. It doesn't work as expected, Rsync for some reason always takes the same amount of time.
Rsync + Replica Set. Stop a secondary member, rsync, start mongo, and then wait MongoDB replication to sync the latest updates.

I have questions for the first two options.

I do not understand why initial sync is so slow.
I do not understand why rsync is copying all the data always. I'm using rsync -a.

Option 3 should work, testing it. But if you have better options, it would be great.


texts:
So, I'm currently trying to wrap my head around worker threads for availability groups, more specifically, the number of threads used by each secondary database. The information I've read is different to what I'm seeing. For example:
I'm using the sys.dm_hadr_db_threads and sys.dm_hadr_ag_threads DMVs.
"Each Secondary replica uses 1 redo thread for each secondary database" - but my secondary replica databases are using either 3 or 4 threads:

It's a very inactive AG so I wouldn't expect to see any active threads (again from what I've read, after 15 seconds or so, the threads are released for re-use) I don't see any active threads on the primary which is what I would expect:

It's not causing an issue (yet), it's just something I'm curious about. Have I mis-understood how it works? I just don't want to it be over consuming threads as eventually there's going to be quite a large number of databases added into the AG, and I'm just trying to figure out if we'll have enough worker threads for the AG and daily database usage.
Config:
SQL Server 2019
1 AG
1 primary
1 secondary


texts: SQL Database size doesn't match with the total table sizes in the database

texts:
I have a database that is size about 104GB.

When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces.

Why the DB size is too large?


texts: accessing DB2 database connection by squirrel sql client

texts:
I am Ramashankar actually I am using Ubuntu OS . When I try to connect with DB2 database through Squirrel sql client it shows the connection is successful but no database table shows after connecting and in logs the error which is coming

  Error: [jcc][t4][10335][10366][3.67.26] Invalid operation: Connection
  is closed. ERRORCODE=-4470, SQLSTATE=08003 SQLState:  08003 ErrorCode:
  -4470

I don't know what it is, can someone help me on this


texts: Copy views from one database to other database

texts:
I want to copy views from one database to other database I had near around 70-75 views.
How can it be possible to do?


texts: Huge MEMOBJ_LOCKBLOCKS memory consumption

texts:
I have run into very odd SQL Server memory consumption.
The problem reproduces on standalone (without any production load) test server (24 cores, 64Gb RAM; SQL Server 2012 Enterprise version 11.0.5058.0 on Microsoft Windows NT 6.3 (9600)). Server itself is KVM virtualized with virtio drivers on top of Ubuntu 16 server.

Database is around 1.8T in size.

I need to update a lot of rows in that DB (around 300 million).
To do that, I made a script and run it via SSMS, that looks like:

set nocount on
go

alter index [...] on [...] disable
...
alter index [...] on [...] disable
go

alter table [...] drop constraint [...]
...
alter table [...] drop constraint [...]
go

-- several such selects transfering around 10M rows each
select ...
into [...] -- that table already exists
from ....
go

update top (5000000) [...]
set [...] = up.update_id
from [...] t
inner join #to_update_part up on t.id = up.id
go

At some point, script stops working.
Checking active commands shows that its locked with RESOURCE_SEMAPHORE wait type.

sys.dm_os_memory_objects shows that 20+G of memory occupied by MEMOBJ_LOCKBLOCKS and 10G+ by MEMOBJ_LOCKOWNERS.

sys.dm_tran_locks shows nothing suspicious.

I can not use sys.dm_exec_query_resource_semaphore (there is no such view on my server - it looks like it was added in some SQLServer version above mine).

There are no other active transactions (this is clean server without any load).
Stopping script and closing SSMS does not help (I can not proceed with script further, it waits the same lock).
Dropping database and restoring it from backup does not help.

So, there is only one thing that frees that memory - server reboot.

As far as I understand, that memory is consumed by locks that are taken by update commands; but there are no active transactions. Why it did not get free?

What's wrong?


texts:
I have a trigger:

CREATE TRIGGER [dbo].[UpdateTbl]
   ON  [dbo].[TableB]
   AFTER INSERT

AS
BEGIN

       DECLARE @role varchar(20)
       select @role = u.[role] from inserted i join users u on u.id = i.creator_id

       if @role = 'client'
              update TableA
              set updated_date = i.created_date from inserted i where TableA.id = i.trans_id

       if @role = 'tech'
              update TableA
              set updated_date = i.created_date from inserted i where TableA.id = i.trans_id and i.[public] = 'true'

END
GO

But it doesn't work as expected. The updated_date is still updated with the value of i.created_date even if i.[public]='false'. Any idea?


texts: patching SQL Server with Availability Group and Secondary Replica

texts:
Scenario: SQL Server 2014 CU6. 1 AG with 2 databases (compatibility level 100). Failover Cluster with 3 nodes on Windows Server 2012 R2. Primary Availability Replica on node 1 and node 2. Secondary Availability Replica on node 3 (no votes). Async commit, manual failover, readable secondary. Node 3 is used to run reports on these 2 databases (out of 8 databases running outside AG on node 1 and 2).

Problem: Looking to patch these from SQL 2014 to SP1 and latest CU. Not my build and no experience with AG or FC so far. My "test" environment is also used for development, so no room for mistakes.

Questions:

What's the best order to patch the 3 nodes ?

Do I first have to remove the databases from AG on the node I'm patching ?

Do I need to take the Secondary out when patching it ?

No experience on this, and I can't find clear answers for my scenario (maybe not supported?) in the docs: https://msdn.microsoft.com/en-us/library/dn178483(v=sql.120).aspx


texts:
Our DB2 authentication is integrated with active directory, so the allowed passwords are controlled by it's policy. We have some users that are not being able to connect to the database through Command Editor or the Query screen in Control Center. The error is the following:

SQL0104N  An unexpected token "!" was found following "".
Expected tokens may include:  "NEW".
 SQLSTATE: 42601
 sqlcode: -104

They are able to connect through Command Line Processor as far as they don't include the password in the connect statement.
The problem is affecting all users with some special characters in their passwords. We had them try issuing the connect statement without the password in Command Editor but it didn't work.
The exact version of the database is DB2/AIX64 9.5.4
Is there a way for them to use this tools other that to have them change their passwords?
Thanks


texts:
In our company we have a test server on which Oracle RDBMS 11g is installed.
My question is related to reclaiming wasted space.

I ran the following query:

select
   table_name,round((blocks*8),2) "size (kb)" ,
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from
   dba_tables
where
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

...which shows me the total wasted space for each table. So we have approx. 84 GB of wasted space overall in our database.

I want to know how to reclaim this wasted space without being able to use Enterprise Manager, because I don't have access to EM.

I have also noticed that the indexes on the database were last rebuilt in 2010. Could rebuilding the indexes solve the wasted space problem?


texts: `greatest` function makes the query run much slower

texts:
I have a query which has the following WHERE clause (note that this is a simplification. In the actual case the second parameter would be passed from code. But the following explanation applies to when executing from sql developer).

WHERE O_DATE>= greatest(sysdate-1, sysdate - 30)

which takes about 100 seconds, whereas

WHERE O_DATE>= sysdate-1 takes very little time.

I would assume both to take the same amount of time because greatest(sysdate-1, sysdate - 30) should resolve to sysdate-1.

Can someone tell me why the difference in execution time? And a way to work around it?

To add more context I am using the logstash jdbc plugin and trying to use the sql_last_value


texts: DB2 - Inserting a timestamp operation result in a timestamp field

texts:
I got a some javascript Dojo code assembling a query from JSON variables and I have been trying to insert a timestamp in it.

This is my test table (schema.mdummy):

k and v are varchars
n is an integer
t is a timestamp

I found out these statements work fine:

INSERT INTO mschema.MDUMMY (k, v)
VALUES ((SELECT k FROM mschema.MDUMMY WHERE k LIKE 'xp3'), '33');

SELECT TIMESTAMP('1965-07-27', '15:30:00') + 59 SECONDS FROM SYSIBM.SYSDUMMY1;

But this one does not work as expected.

INSERT INTO mschema.MDUMMY (k, n, t) VALUES (
 '70',
 70,
 (SELECT TIMESTAMP('1965-07-27-15.30.00') + 59 SECONDS FROM SYSIBM.SYSDUMMY1)
);

Is it some error on the syntax or is it impossible to perform in DB2?

DB Server OS is the latest Fedora server.


texts: How to convert MySQL master-slave to master-master

texts: I've searched around, but can't find the exact details to point me in the proper direction.  I currently have 2 MySQL instances under 5.5.41 running as a master-slave setup.  I followed these directions to set it up.  Since both of these nodes are on the same VM storage I'd like to migrate one to a different datacenter as a master-master config then use JDBC load balancing where the current master is primary and the slave is converted to a secondary master with replication enabled between both.  Can anyone provide the steps I'd need to perform on the MySQL nodes?  I believe I now the correct JDBC line to use for my app connection.


texts: Group by unknown ID, select all related rows where Column X = Y and Column X = Z

texts:
I have a table that logs interactions from users with the following structure.

SELECT [SessionID]
      ,[Question]
      ,[Response]
      ,[DateCreated]
      ,[Terminal]
FROM [Quiz]
where Teminal in ('A1','A2','B1')

Output:

SessionID Question  Response  DateCreated              Terminal
--------- --------- --------- -----------------------  -------
123       Q1        A         2020-02-16 10:10:15.200  A1
123       Q2        A         2020-02-16 10:10:15.560  A1
123       Q3        B         2020-02-16 10:10:16.120  A1
124       Q1        B         2020-02-16 16:01:34.205  A2
124       Q2        B         2020-02-16 16:01:38.999  A2
124       Q3        A         2020-02-16 16:01:44.435  A2
125       Q1        D         2020-02-16 16:02:01.013  B1
126       Q1        A         2020-02-17 07:49:55.098  B1
126       Q2        A         2020-02-17 07:49:57.101  B1

A user reports they had issues during their interaction "the other day" but cant tell me the date or time, only it was one of those 'Terminals' (worked out to be A1, A2 or B1).

They also know they answered 'A' for 'Question 1' and also 'A' for 'Question 2'.

Is there away I can query the original 'Quiz' table (without creating a temporary table) and end up with an output something like the following that collates the response to each question into a related column on the one row per SessionID?

SELECT [SessionID]
    ,[Q1]
    ,[Q1DateTime]
    ,[Q2]
    ,[Q2DateTime]
    ,[Q3]
    ,[Q3DateTime]
    ,[Terminal]
FROM Quiz

Output:

SessionID  Q1     Q1DateTime               Q2     Q2DateTime               Q3     Q3DateTime               Terminal
---------  -----  -----------------------  -----  -----------------------  -----  -----------------------  -------
123        A      2020-02-16 10:10:15.200  A      2020-02-16 10:10:15.560  B      2020-02-16 10:10:16.120  A1
124        B      2020-02-16 16:01:34.205  B      2020-02-16 16:01:38.999  A      2020-02-16 16:01:44.435  A2
125        D      2020-02-16 16:02:01.013  null   null                     null   null                     B1
126        A      2020-02-17 07:49:55.098  A      2020-02-17 07:49:57.101  null   null                     B1

Or

SELECT [SessionID]
    ,[Q1]
    ,[Q1DateTime]
    ,[Q2]
    ,[Q2DateTime]
    ,[Q3]
    ,[Q3DateTime]
    ,[Terminal]
FROM Quiz
WHERE Q1 = 'A'
  and Q2 = 'A'

SessionID  Q1     Q1DateTime               Q2     Q2DateTime               Q3     Q3DateTime               Terminal
---------  -----  -----------------------  -----  -----------------------  -----  -----------------------  -------
123        A      2020-02-16 10:10:15.200  A      2020-02-16 10:10:15.560  B      2020-02-16 10:10:16.120  A1
126        A      2020-02-17 07:49:55.098  A      2020-02-17 07:49:57.101  null   null                     B1


texts: Should I pause mirroring when I reboot the witness server?

texts: So I know that when patching the two main database servers (SQL Server for the record) you do the mirror, failover and then when it's all caught up do the new mirror, but what about the witness box? Is it safe just to patch and then restart it and (in theory) it will just pick up again?


texts: What is Atomic Relation in First Normal Form

texts: I have read the definition of 1NF which is, "If each attribute of relation is atomic". Please tell me what is Atomic.