texts: Is Oracle 18c XE good enough to start learning to be a DBA?

texts:
I don't know if the esteemed community here will find this question silly, but at the same time I thought that I would get a good answer to my question here.

As of now, I am into writing SQL queries/procedures, etc(dev work).
Additionally, in future I also plan to gain knowledge on DBA tasks(at least the basic ones).

I know that each DB is greatly different from another, but I would like to know which one is the easiest DB to learn DBA tasks.
By easiness I mean, I can install it on my PC and learn them without having to go to a school for training(As I lack time to do so).

Oracle is said to be the most widely used relation DB, but will Oracle 18c XE enable me to learn and perform basic DBA tasks(for DBs 12c and above)?

Another DB that I have on my radar is MySQL, if so, will installing the community edition help me learn things, Or do I have to go for the enterprise one?


texts: Sql Server - How to convert Audit log into a table?

texts:
I am using Sql Server 2014, Standard Edition. My objective is to trace the history of operations on the tables (insert, update, delete), and to display on the web so the users are able to tell what happened to the data in the past.

So, I came to know there is a partial support of Audit feature in the Sql Server Standard Edition, and was able to log some operations, as attached.

My question is, how to make those logs stored inside a proper Sql Table so that the records could be accessed, retrieved and displayed on the web page?

Currently, the Audit Destination allowed:
File, Security Log, Application Log
... which don't seem to be able to be retrieved directly from an ASP.NET API.

Is it possible to log into an SQL Table?

P.S.:

(Correct me if I am wrong). Many have said that the Standard Edition only allows Server-Level Audit, but NOT Database Level Audit. However, for some unknown reasons, if I added these 7 audit types (yup, 7 or none), I am able to capture and view the database level operations.


texts: How to add data to sql server using MS access front end, when user will not always have sql connection

texts:
I have a SQL Server backend and an Access Front end database which is working fine.

However, I now have a user that needs to use this database (on a tablet) for some equipment checks out in the production plant that does not have a wifi connection and of course he gets an error since he cannot connect to the SQL server.

What is the best way to handle this?  I've never run into this before.  (My other databases are used by Office staff only)  The user does need to have some information that is on the SQL server for drop down selections, etc.

Do I create a local copy on the tablet and then have him press a "submit" button to commit the record to the server (append query) when he is back in the office with wifi range?

I'll run into issues if others are entering data that is using autonum, etc.  Also, any changes made to the dropdowns by office staff would not be updated to him if it is a local copy.  Any help would be appreciated.


texts: MySQL Security - Storing lots of sensitive phone numbers securely?

texts:
Imagine a table in a database in MySQL that holds millions of telephone and cellphone numbers that relate to itemized billing for VoIP and Cellular carriers.

All of these numbers are in VARCHAR fields and they are clearly visible to anyone who has access to the database. These numbers need to be like this because they are read out to form parts of reports and because there are millions of records, it's quite resource intensive to sift through it all.

I'm in search of ways to store these numbers in a way that would be well... Safer.

Say for instance, the database got compromised and someone else gained access to this information... They would have millions of active numbers and could really make havoc out of that.

So to avoid this situation (it's not physically possible at the moment as we're on a closed network) in the future, what would be the right thing to do with all of these numbers?

I need a way to make then like a hash and then unhash them on request... The problem is the resources involved in this, any one number could have thousands of records so this would end up being quite resource intensive... There must be a more suitable solution for something like this?

I'd like to say that I am really looking for some opinions, feedback, input or guidance from some professionals with relative experience. I'm sure that in the manual there is something somewhere that covers something sort of like this... But I cannot seem to find it so I would like some insight into how to deal with this the correct way!

Thank you.

EDIT: I'd like to note that this question is not about server security, it is about information security... When i look at the database and see raw numbers it just feels wrong... Is it wrong or am i just being paranoid?


texts: Ensure N:ALL relation ship for some rows

texts:
I have a simple N:M relation ship:

Table User:

Column ID
Column username
Column is_superuser (Boolean)

Table Permission:

Column ID
Column Name

Table UserPermission:

Column ID
ForeignKey User
ForeignKey Permission

I want to ensure that if the user has "is_superuser=True" then he should have all available Permission set via N:M Table UserPermission.

Typical operations:

Update or insert on table "User": user gets set "is_superuser". Now the UserPermission needs to have all permissions for this user
A new permission gets added to the Permission table. Now the UserPermission needs to get N new rows (for each user with is_superuser=True).

How to ensure this with Postgres 9.6?

Sub-question (please leave a comment): Is there a name for this pattern?


texts: MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!)

texts:
Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, student behavior management and grading. We noticed that over-the-shelf products are unable to complete our specific tasks without major customization and costs. Hence, we decided to develop this ourselves since we have full control as to how we want our information to be reported to our education board (as per their specifications) and have control on our complex grading system (which is continuously updated to meet the dynamic benchmark needs of our education board).

We have begun using MS Access and is still in the primary stages of development. We want to know whether we should use MS Access as a front-end and SQL Server as a back-end DB since we want to accomplish the following:

Ability for 80+ employee users to simultaneously access, post, update and delete customer records within the SIS DB (on campus).
Ability for our SIS DB to have web-based functionality with a student login portal integrated within our school website so 1080+ students can simultaneously access a specific web-based DB form from their homes (by using the student login portal) for updating personal records, checking grades, and other performance summaries.
Ability for our SIS DB to be highly secure and functional 24/7 without interruptions and security breeches (we read that MS Access alone cannot accomplish this).
Ability to integrate our SIS DB with another in-house database that will provide digital learning materials to students within the comfort of their homes (also accessed through student login portal). The digital learning materials DB is another DB we are creating that should have ability to store 100 GBs worth of multi-media materials.
Ability for data to be quickly pulled from tables to the GUI without delay.

A major factor for our school in this matter is COST. They want to create the DB under USD 1,500 and would like to have many users being able to access this system at the same time. Please help me to decide how I should go about this situation, both performance-wise and cost-wise. I apologize for the long details provided. I am really confused and would deeply appreciate if any one could help me on this matter.

In addition, we wanted our forms to be professional like the ones seen in major DB systems. We noticed that Access can create forms but only to a certain extent. Is there any other system programming-friendly platforms we can use for creating highly complex and professional DB forms (lke Visual Studio, for example)? Help on this subject as well will be extremely appreciated.

Ankith


texts:
I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less interest in learning more about the subject.

I'm learning as I go, and am currently trying to figure out transaction log files.

All our databases have been set up with the simple recovery model and autoshrink. I've understood that using autoshrink is usually a horrible idea, but it's my understanding this was done in order to stop the transaction logs from growing out of control. (Does autoshrink actually shrink the log file(s) or just the DB?)

I found this about SQL Server 2012, and was wondering if it's true about 2005 and/or 2008, and exactly what it means: "When a database uses the simple recovery model, the Database Engine truncates the transaction log after a checkpoint. [...] The Database Engine triggers an automatic checkpoint under the simple recovery model when the virtual log becomes 70 percent full." Where is the virtual log size specified?

I want to disable auto shrink on all databases, but before I do that, I need to know that the log files won't grow out of control quickly.

Any help would be greatly appreciated.


texts: How to count two columns per month per country in MYSQL View


texts: How to configure/setup data at rest encryption in Couchbase 5.1

texts:
I am evaluating best database solution for a sizeable application. My basic requirements include:

Data should be encrypted at disk level
Mobile support
Scalable
Enterprise support

I realized Couchbase Server solution would be sufficient for my requirements.
I've tested some of the above with Couchbase Server and got stuck on how data encryption on Couchbase Server is applied.

I could find only few high level documentation related to this topic. Including below official documentations

Encryption at Rest

They say there are 4 possible third party encryption tools that can be used:

Linux Unified Key Setup (LUKS)
Vormetric
Gemalto
Protegrity

But they are not explaining on how to use or configure the above tools.

Question 01

What would be the best recommended encryption tool out of above?

Question 02

Is there any documentation(with samples) on how to setup encryption on Couchbase Server?


texts: How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & jobs in oracle?


texts: I wish to restore a gzipped tarball - what does mongodump --archive do? Howto do a mongorestore with --gzip?

texts:
I created a backup with

mongodump -h $RANDOM_SECONDARY_SUP -u $BACKUP_USER -p $BACKUP_PASSWD --out /data/$BACKUP_USER/sup-repl/sup_$DUMPFILE
tar cvzf /data/$BACKUP_USER/dbt/st_$DUMPFILE.tar.gz /data/$BACKUP_USER/st-repl/st_$DUMPFILE

I tried to restore that with --gzip and receive erorr

mv st_$DUMPFILE.tar.gz test_restore.bson.gz
mongorestore --gzip test_restore.bson.gz
2016-09-08T18:05:14.975+0200    checking for collection data in tgdmead2_test_restore.bson.gz
2016-09-08T18:05:15.090+0200    restoring test.test_restore from tgdmead2_test_restore.bson.gz
2016-09-08T18:05:15.156+0200    Failed: test.test_restore: error restoring from test_restore.bson.gz: reading bson input: invalid BSONSize: 1635017060 bytes

In docs I read that the gzipped tarball should be created with --archive

What does this parameter do? Howto emulate that with tar and gz?

Which format does mongorestore expect with --gzip?


texts: created_at, modified_at, status - is this a documented pattern?

texts:
I am but a lowly code monkey, so this may be a very basic question.

For the current project, our architect has required that all tables, without exception, must have at least these four fields:

`id` INT AUTO_INCREMENT PRIMARY KEY
`created_at` TIMESTAMP,
`modified_at` TIMESTAMP,
`status` INT COMMENT "-1=delete using cron, 0=disabled so only admins can see it, 1=active"

This last field is the one I do not understand. To me, it flies in the face of every normal kind of database design.

It breaks SELECT: every single select must do a full table scan on
status to see if it's 1, for every table involved in the join.
It breaks UNIQUE KEY. Any time a table has a unique key, then
deleting an entry prevents that entry being recreated until cron has
run - which he was saying could be any amount of time, even "three
months".

Any time I do a check against, for example, username existence, for
someone creating a new username, I must check all data including
deleted entries, rather than just active data, because the unique
key would prevent creation even though there's no valid record using
that value.
It breaks ON DELETE. When something is marked "disabled" or
"deleted", all its cascadable items must have that status cascaded
through code: the database can't do it for us.
It seems to violate normalization, as it gloms a few different
concerns (admin vs user visibility; garbage collection) into a single
field.
It is also a criminally poor choice of field name, far too ambiguous. What does it mean?

And that's just the problems I've found so far.

The main assumption here seems to be that DELETE is "expensive", and must be optimized for. But deletions on this system will be extraordinarily rare (<1/month), and the overwhelming majority of operations will be SELECTs (several/second) or INSERTS (several/min).

While looking for other questions on this, I did find
What could be causing strange query timeouts between PHP and MySQL? that suggests that there are people using these fields for some tables at least.

But all tables? Including many-many link tables?

The advantages he cites are that it allows things to be easily dis/enabled, and undeleted if they were deleted by accident.

Except that it doesn't. None of the connected systems are able to handle something being temporarily disabled: either it's active, or it's deleted. The deletion of any entity is typically accompanied by API calls to notify other systems of the deletion, and so cannot easily be resurrected just by changing the value of a field on our own DB.

So my question is - is this a standard DB architecture decision? If so, what's it called, and is it documented anywhere, so I can wrap my head around why such an apparently-broken design pattern would ever be useful?


texts: Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error

texts:
Using MariaDB 5.5.36. Here's what I got while trying to start MariaDB:

# syslog
Dec 14 09:40:52 vps1497 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Dec 14 09:40:52 vps1497 mysqld: 141214  9:40:52 [ERROR] mysqld: File '/var/log/mysql/mariadb-bin.index' not found (Errcode: 13)
Dec 14 09:40:52 vps1497 mysqld: 141214  9:40:52 [ERROR] Aborting
Dec 14 09:40:52 vps1497 mysqld:
Dec 14 09:40:52 vps1497 mysqld: 141214  9:40:52 [Note] /usr/sbin/mysqld: Shutdown complete

# ls -alh /var/log/mysql/
-rw-rw----  1 mysql adm    9023947 Dec  8 03:01 mariadb-bin.002905
-rw-rw----  1 mysql adm       2550 Dec  7 03:01 mariadb-bin.index

Permissions seem fine, trying to start mysql with root doesn't work. Any clue?


texts: SQL Server Management Studio: how to parse stored procedure code and check if table columns exist?

texts:
I'm afraid that SSMS won't parse table columns within a stored procedure.

When you click in Parse option

SSMS says that parse is completed, even when a procedure is selecting a column that does not exists in a table.

When you run your script, SSMS shows what the Parse should warn about:

Is there a way to parse a script in order to check if table fields really exist before creating or altering procedures?


texts: How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?

texts:
I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio  Maintenance Plan Wizard.

AFAIU, the penultimate task "Back Up Database Task" will be executed only on the success of all preceding tasks. Since the constraints (arrows containing tasks) are all the same (taken from default options creation):

and use "Logical AND"

Now if I change in the preceding constraint the "Logical AND" to "Logical OR", the backup task still will fail if one of the preceding tasks fail.
Correct?

How can I make the backup task to run in any case?

UPDATE
After accepting the answers, I am still in doubt about the term "completion":
Does failed task considered to be run to completion in SQL Server maintenance plan terminology?


texts:
I have a MySQL master-slave replication setup. When I checked show slave status, I got the result as Slave_Sql_running:No and last error as Duplicate entry 'x' on query.

But when I tried to delete the entry from that table result shown as 0 rows affected.

I tried to fetch the record using select statement. Even then the result shown as 0 rows affected.

Then I checked slave status again. The same state persists and the same error appears.


texts: troubleshooting missing entries using log files

texts:
Something somewhere (most likely the app) is causing rows to disappear.
I'd like to find if there is a query that would point me in the right direction as to what's going on.

It looks like my bin log was not turned on so I've updated my.conf to include the log

log_bin                 = ON
general_log_file        = /tmp/mysql.log
general_log             = 1

but this is only going to update it from this point forward.

Is there any other way of being able to go back to see older queries?


texts:
EDIT:
Both answers are great but I think I must clarify my question.

NOTE: I must indicate that in the following, by executed, I mean logically. I understand that the actual execution made by the particular DBMS may use a completely different approach. So what I will ask is not about the actual execution but rather about the evaluation logic of a correlated subquery.

What I particularly want to know is:

"Why a correlated subquery is executed in this way?" For example, for the example query I have given, why isn't it executed in the following way:

  "Return the rows of the cities_stores relation whose store_type is
  equal to some row's store_type in stores relation. If the
  resulting relation has at least 1 element, the value of EXISTS
  clause is TRUE. Otherwise, its value is FALSE."

but rather in this way:

  "For each row processed in the outer query (let's call this row as
  "current row"), process the inner (correlated) subquery, using the
  current row's values for the attributes not present in the subquery's
  FROM clause."

and from where can I learn to think about correlated subqueries in the latter method, instead of the former one?

I hope this is not an unwise question to ask but exactly this is what I don't understand.

Correlated subqueries are highly confusing me.

Example query from here:

SELECT DISTINCT store_type FROM stores
  WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type);

Initially, I thought that EXISTS clause would have a value that doesn't change for each row of the outer query and that value would be calculated in the following way:

"Return the rows of the cities_stores relation whose store_type is equal to some row's store_type in stores relation. If the resulting relation has at least 1 element, the value of EXISTS clause is TRUE. Otherwise, its value is FALSE."

Had the EXISTS statement evaluated in this way, it would have a value that does not change among various rows for the outer relation and hence, the query would be equivalent to the following:

SELECT DISTINCT store_type FROM stores
  WHERE TRUE; -- Or FALSE, if the subquery did not return any rows.

And of course this made me think how is an EXISTS statement is useful at all?

But apparently, that isn't the way a correlated subquery is executed. Apparently, a correlated subquery is executed in the following way:

"For each row processed in the outer query (let's call this row as "current row"), process the inner (correlated) subquery, using the current row's values for the attributes not present in the subquery's FROM clause."

So finally, what I don't understand:

Why is this the way it happens? I mean, why not it doesn't happen in the way I initially thought? (That is: Why it doesn't return the rows of the cities_stores relation whose store_type is equal to some row's store_type in stores relation?) From exactly where am I supposed to interpret that it is how SQL works?
In various sources around the internet, I constantly see the following about the correlated subqueries:

  In a correlated subquery, the subquery is evaluated once for each row
  processed by the outer query.

But most curiously, a statement similar to this does not exist in MySQL's page about correlated subqueries. So:

Is that an incompleteness in MySQL manual, or is such a statement simply not required? If it is not required, from where am I supposed to understand that it the way how a correlated subquery is evaluated? Or, as another possibility, is the quote I have given simply a vague statement that is not completely correct? If so, how am I supposed to interpret a correlated subquery?


texts:
I have a large table that I want to spread across multiple disks/LUNs.  It will contain blobs and I want to have it separated out from the other data.

How do I go from new disks (lets call them sdb1 and sdc1, the OS, etc. is on sdba1) attached to a (virtual) Linux database server to putting the parts of the database, really one table spread across a couple dedicated devices.

Have looked at the Partition information but its unclear how I change the location of the partitions so everything just doesn't end up in the same data directory.


texts: Linked Server : Syncing two tables on two SQL Servers

texts:
Suppose these database structure :

--On main server(server)
TicketRequest (id, date, count, start, end/*,and some other columns*/)
Ticket (id, reqId, blockedDate, usedDate, userId/*,and some other columns*/)
--and some other tables

--On linked server(gate)
--Just this table
Ticket (id, userId, blockedDate, usedDate)

A Ticket may use on gate, so on the gate just its usedDate may change.

At server, a Ticket may blocked, deleted; or after importing gate's data, set its usedDate.

I want to sync them online.

I wrote sp_server_export , sp_gate_import for transport data from server and  sp_gate_export , sp_server_import for transport data from gate, to sync them via a file(a server_export file goes to the gate, and server_import file come back to the server).

I used MERGE and its performance is really good! Now when I'm trying to find a solution for syncing server with gate, after some searches UNFORTUNATELY found Microsoft says : MERGE not working on remote table as target!

So, how I can sync with a transaction from server?!

NOTE : As our plan, syncing must only execute from server! Gate operator cannot change DB at all!

IMPORTANT NOTE : Consider that concurrency is my first concern! I wanna sync while a user may delete a Ticket! Or a Ticket may block at the same time! Or at the gate a Ticket may use!

UPDATE :

Our syncing logic is :

1 - delete old tickets(which returned with a file syncing and have returnDate and have modificationDate > one month later timestamp to ensure delete used tickets from gate)

2 - update gate.ticket where usedDate is null set blockDate = server.blockDate;

3 - insert new rows from server

4 - set transferDate for Tickets transfered to gate ( i mean new/updated tickets in step 2 and 3 )

5 - update server.Ticket.usedDate from gate.Ticket where gate.Ticket.usedDate is not null. ( this will send used tickets from gate back to server )

6 - insert a transferLog ( in transferLog table on server )


texts: Restrict access for Windows administrator in PostgreSQL

texts:
Is there a way to protect PostgreSQL from user with Windows administrator rights?
Like this question but for Postgres.


texts:
I am on OS X 10.9.5 and I did a reinstall of mysql2 and I provided a password during installation but it is not working when I run the command mysql -uroot -p. The result I get is: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Does someone know how I can reset the mysql password? I have been searching posts all day on how to fix my issues but nothing has helped.


texts: Trigger function taking column names as parameters to modify the row

texts:
I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet):

  CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass)
    RETURNS trigger AS $func$
  BEGIN
    NEW.target := ST_SetSRID(ST_Point(NEW.source[1], NEW.source[2]), 4326);
    RETURN NEW;
  END;
  $func$ LANGUAGE plpgsql

In this case, target is a column of type geometry and source is an array of decimals.

When a row is inserted with a coords array, I want to convert that to a point. The above would work if I just hard-coded column names, but I want to do it for different tables and different pairs of columns using the same function. And I don't have direct control over the INSERT itself.
Here's some of my experimentation: http://sqlfiddle.com/#!15/dddcd/1

Found this related blog post which I'm having a really hard time parsing.

I could live with this running after insert/update instead of before, if that makes it easier to code.


texts: Viability of surrogate key based from existing column as primary key

texts:
I'm designing the schema for a database and have a question about primary key choice. There aren't any natural keys available in the tables we're looking at now although there's interest from my team in using more "readable" keys to see relations between tables a bit easier.

The current proposed solution is to have a primary key that is an existing column + a random number. For example: we have a PERSON table that has a a Name column. The PersonID is the primary key with it being the Name + some number. So the person named Alex has the PersonID Alex1.

We're looking at potentially several thousand entries. Will this hinder performance using a string as a primary key? Is there any reason to use or not to use a more "readable" key?

It's probably worth noting that most systems our team is used to working have no more than a small handful of tables where bouncing around and seeing relations directly isn't too difficult. This project we're mapping out know will have a dozen to potentially several dozen tables. Also, we use MySQL and often enough team members use PHPMyAdmin to enter data directly which I think is the main reason there's a push to have readable keys.


texts:
Here is my query:

[SuggestedRetailPrice] > 0 AND
    (ISNULL(
        CASE WHEN [AdEnd] < GETDATE()
            THEN [AdPrice]
        END, [BasePrice])
    )/[SuggestedRetailPrice] > 0.8

The query performs as expected, however it is picking up a few items that have a valid Ad (AdEnd < GETDATE()). The purpose of this query is to determine the correct price with which to calculate the discount.

The discount is Price / SRP. When AdEnd < GETDATE() it means the ad has expired, and we should use the BasePrice. Otherwise, use the AdPrice. Logically the query makes sense to me however I can't seem to figure out where these extra results are coming from that don't meet the criteria. Any idea?

Edit: The records in question have an AdEnd date of 1/6/2015 8:59:59 PM. Does GETDATE() literally only get the date (10/30) or is it a datetime stamp kinda thing?


texts: Patching after upgrade (windows 11gr2 -> 12cR2)

texts:
I have a following scenario where I had a patched oracle 11gR2 DB (applied Windows Bundle and OJVM patches) and then it was upgrade to Oracle 12cR2. 12cR2 was a clean installation (no patches applied, just a download).

In the case above then do I need to apply “same” patches again to my upgrade 12cR2 DB?

I am talking about patchset bundles. I did apply to 11gR2 a Windows Bundle patches and OJVM PSU.

So just to be clear: To have a "same" level of patching then I need to install corresponding 12cR2 patch? For example I had a 2018 jul patch installed to 11c and now I would need to install "again" 2018 jul, but for 12c (I would install again Windows BP and OJVM PSU)? I understand that 11c patch might have fixed a bug, which is in 11c and 12c, but it does not fix a bug in 12c. As I was saying then I need to be sure..

For the future then I should apply patches before I would do the upgrade?


texts: SQL Server restore sequence from multiple full, diff and log backups

texts:
Suppose I have a lot of backups (may include full, differential and log backups) of a SQL Server database. I want to write a program that can generate a SQL script to take the database to its most recent state. Now I have difficult in finding the correct restore order of these backup files. I read some pages in MSDN and got some basic rules. They are:

Full backups not only include data, but also include enough log records. This makes the full backup can be used by itself (after restore, use the log records in the backup to roll forward the database to a consistent state)
Every backup file (include full, diff and log) has some common property

first lsn: specify the first log record that included in the backup
last lsn - 1: specify the last log record that included in the backup
checkpoint lsn: specify the log record that corresponds to the most recent checkpoint operation
database lsn: specify the log record that corresponds to the most recent full backup

The log backups that need to be restored consecutively may have overlap log records, that is to say, if log backup A needs to be restored just before log backup B, A.last_lsn may be greater than or equal to B.first_lsn
You need to restore the most recent full backup (we have just one for sure), then the most recent diff backup (we may or may not have this), last, restore the logs backups that were taken after the full or diff backup in turn

Now my question is, how to determine which full and diff backups is the most recent one? How to determine which log file should start to restore after the full or diff restore? I guess these information can be found from those lsns, but I'm not sure of the algorithm. Can anyone help to write a piece pseudocode to describe the algorithm? Thanks.


texts: Why does a query on 4 times more data take 300 times more processing?

texts:
To score the price preference of our individual customers in, we run the below query.

Basically it is a weighted average of a product score. The score depends on the product group and the packaging type. The weight depends on the product type and the customer type.

SELECT SUBQRY.customerNo,
       SUM(WGT.weight * SUBQRY.avg_score) / SUM(WGT.weight) AS score
FROM
  (SELECT SALE.customerNo,
          SALE.ProductType,
          SALE.CustomerType,
          AVG(SCORE.score) AS avg_score
   FROM mySchema.Retail_sales AS SALE
   LEFT JOIN mySchema.product_scores AS SCORE
        ON (SALE.ProductGroup = SCORE.ProductGroup AND
          SALE.Packaging = SCORE.Packaging)
   GROUP BY SALE.customerNo,
            SALE.ProductType,
            SALE.CustomerType
   ) AS SUBQRY
LEFT JOIN mySchema.product_SOW AS WGT
        ON (SUBQRY.CustomerType = WGT.CustomerType AND
          SUBQRY.ProductType = WGT.ProductType)
GROUP BY SUBQRY.customerNo
ORDER BY SUBQRY.customerNo

We have two reatail chanes, one with 4 times as much sales and product Groups (16k vs 4k) as the other.
How can I explain the larger one takes 300 times more time to execute and how can I cure that?

PS: We currently have no indexes on tables within mySchema. Creating an index on mySchema.product_scores.ProductGroup does hardly help.


texts:
Using SQL Server 2008, I have found that the database size can be obtained using

exec sp_spaceused

This procedure returns two tables:

database_name | database_size | unallocated space

reserved | data | index_size | unused

I would like to create a query where the only output of the SQL call is the value returned for database_size.  I do not want any extraneous output (no table headers, column names, etc).

Is there a simple way to do this?


texts:
I have 3  SQL servers  running SQL Server 2012 Standard Edition, called A , B and C. Server B is a mirror of server A. There is transactional replication between server A and Server C.

How can I ensure replication continues to C regardless of which server is the primary server in the mirrored pair?


texts: deleting large volumes of rows from table efficiently in SQL Server

texts:
I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system performance during the delete, etc...

Is there an efficient way for me to create a job that deletes records in small batches to over hampering other access/performance and prevents problems with the transaction log?

This process can be quite slow is that makes a difference

For additional context, the delete criteria will be based on something like... where x like '%blah%'. Also, there is one clustered index and 5 non-clustered indexes.


texts: Is it possible to create a foreign key with a condition?

texts:
I have a table site that has a field categoryid_root and a table category which has the fields id and parentid I want to create a relationship between:

site.categoryid_root and category.id but with the condition that category.parentid is null is this at all possible in MySQL?


texts:
Table 1

 ID |COL2|COL3|COL4|COL5|
ROW1|  1 |  1 | 1  | 1  |
ROW2|  1 |  3 | 2  | 6  |
ROW3|  1 |  1 | 1  | 1  |
ROW4|  1 |  1 | 1  | 1  |
ROW5|  1 |  1 | 1  | 1  |
ROW6|  1 |  1 | 1  | 1  |

Problem

After updating table 1, I have a new row with changed values:

 ID |COL2|COL3|COL4|COL5|
ROW1|  1 |  1 | 1  | 1  |
ROW2|  1 |  0 | 4  | 5  |
ROW3|  1 |  1 | 1  | 1  |
ROW4|  1 |  1 | 1  | 1  |
ROW5|  1 |  1 | 1  | 1  |
ROW6|  1 |  1 | 1  | 1  |

I want to select the row from table 1 that was changed into multiple rows based on the column name.

Desired output

ID  |OLD|NEW|
ROW2| 3 | 0 |
ROW2| 2 | 4 |
ROW2| 6 | 5 |

My attempt

I have created a trigger like:

select d.col2 as old, i.col2 as new
from inserted i
inner join deleted d
    on i.id = d. id
where not exists
(
    select i.row2
    intersect
    select d.row2
)

...but it just returns one row.

How I can I achieve the output I want? I am using SQL Server 2008.


texts:
Please help me on below:

Below is my Environment:

Windows Server 2012 R2 Standard

SQL Server 2014 SP2 GDR

Availability Groups 2014 with 3 Node (2 Sync and 1 BCP async)

I have 7 Databases with FileStream enabled.

Each 7 DB Filestream data folders is around 500 GB.

Due to security policy I need to enable both Transparent Data Encryption (TDE for structured data) and Encrypting File System (EFS on FileStream folders).

While enabling EFS on FileStream folders I am getting below error.

(NOTE : I am doing it by turning the SQL Servers offline and before taking services offline , I am failing over AG to next available Synchronized AG Node)

I cannot Ignore the error and move on , because of which please advise on below:

I even tried turn off anti virus and Firewall . No Luck

What is the root cause of this issue and how can I perfectly enable EFS for the 7 DBs FS Folders .
Can I try enabling the EFS on multiple DB FS Folders keeping in mind their sizes (500 GB each) ?

Kindly advise. Thanks


texts: SQL Query :Is it possible to count / group based on date with a sum over the group

texts:
I have the following question:
To determine the progress of a production process the following records are stored:

+-------+-------------------------+-------+-------+
| Order | Date                    | Count | State |
+-------+-------------------------+-------+-------+
| X     | 2018-02-10 00:00:00.000 | 10    | 0     |
| X     | 2018-02-11 00:00:00.000 | 4     | 1     |
| X     | 2018-02-12 00:00:00.000 | 2     | 2     |
| X     | 2018-02-13 00:00:00.000 | 3     | 1     |
| X     | 2018-02-14 00:00:00.000 | 1     | 3     |
+-------+-------------------------+-------+-------+

The result of the query should be:

+-------+-------+-------+
| Order | Count | State |
+-------+-------+-------+
| X     | 3     | 0     |
| X     | 5     | 1     |
| X     | 1     | 2     |
| X     | 1     | 3     |
+-------+-------+-------+

10 items are initially booked on state 0
On 2018-02-11 4 items are progressed to state 1, resulting in 6 remaining items on state 0 and 4 items on state 1.
On 2018-02-12 2 items are progressed from state 1 to state 2, resulting in 6 items on state 0, 2 items on state 1 and 2 items on state 2.
On 2018-02-13 3 items are progressed from state 0 to state 1, resulting in 3 items remaining on state 0, 5 items on state 1 and 2 items on state 2.
On 2018-02-14 1 items progressed from state 2 to 3, resulting in 3 items remaining on state 0, 5 items on state 1 and 1 item on state 2 and 1 item on state 3.

It is not possible to skip a state.

Sample script:

USE [tempdb]
GO

CREATE TABLE [dbo].[OrderItemState](
    [Gid] uniqueidentifier not null
            constraint PK_OrderItemState primary key nonclustered
            constraint DC_OrderItemState default newid(),
    [Order] nchar(16) not null,
    [Date] datetime not null,
    [Count] int not null,
    [State] int not null
)
GO

insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-10', 10, 0)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-11', 4, 1)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-12', 2, 2)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-13', 3, 1)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-14', 1, 3)
GO


texts: How can I drop and create postgresql views in dependency order?

texts:
I found the following https://stackoverflow.com/a/9712051/61249 which is good but still too much manual labor for me. I need to recreate the views in the right order how would I go about doing that?

@dbenhur talks about the following:

  To be more complete, one could figure out how to query which views
  depend on the table(es) you're modifying and use that query instead f
  enumerating view names. Gonna need to understand pg_rewrite | pg_rule
  to do that, I think

I am unsure what that means exactly but I'll tell you want I need and you tell me if it is possible.

I work on a rails application where I've tried to maintain the views and functions as part of the rails migrations unfortunately this became a real mess and I dumped our views and functions into separate files for each and one of those. There are two ways forward as I see it. I could either use a single file for these which makes it cumbersome but I guess the order of the DDL would be managed by pg_dump.

OR I could when I dump the views and functions also query and create a dependency tree that I later use for recreating them. The reason is that it has become too complex to do manually for just changing the name of a column in a view.

How do others maintain their DDL? Anyone ever done anything like what I want to do?


texts: Why aggregate query with lookup is extremely slow?

texts:
I have a mongodb query that works but takes too long to execute and causes my CPU to spike to 100% while it is executing.  It is this query here:
  db.logs.aggregate([
    {
      $lookup:
      {
        from: 'graphs',
        let: { logId : '$_id' },
        as: 'matched_docs',
        pipeline:
          [
            {
              $match: {
                $expr: {
                  $and: [
                    { $eq: ['$$logId', '$lId'] },
                    { $gte: [ '$d', new Date('2020-12-21T00:00:00.000Z') ] },
                    { $lt: [ '$d', new Date('2020-12-23T00:00:00.000Z') ] }
                  ]
                }
              }
            }
          ],
      }
    },
    {
      $match: {
        $expr: {
          $and: [
            { $eq: [ '$matched_docs', [] ] },
            { $gte: [ '$createDate', new Date('2020-12-21T00:00:00.000Z') ] },
            { $lt: [ '$createDate', new Date('2020-12-23T00:00:00.000Z') ] }
          ]
        }
      }
    },
    { $limit: 5 }
  ]);

This query looks for all records in the db.logs collection for which they have not been transformed and loaded into db.graphs.  It's analogous to this SQL approach:
WHERE db.logs._id NOT IN (
        SELECT lId FROM db.graphs
        WHERE db.graphs.d >= @startTime
        AND db.graphs.d < @endTime
    )
    AND db.logs.createDate >= @startTime
    AND db.logs.createDate < @endTime
)

The db.logs has over 1 Million records and here are the indexes:
db.logs.getIndexes();
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_"
        },
        {
                "v" : 2,
                "key" : {
                        "createDate" : 1
                },
                "name" : "createDate_1"
        }
]

And db.reportgraphs has fewer than 100 records with indexes on every property/column.
In my attempt to analyze why the mongo query is so slow and CPU intensive, I suffixed my mongo query with a .explain().  But mongo gave me the error saying db.logs.aggregate(...).explain() is not a function.  I also tried adding , {$explain: 1} immediately after { $limit: 5} and got an error saying Unrecognized pipeline stage name $explain.
So I guess I have two questions:

Can someone give feedback on why my  mongo query is so slow or possible solutions?
Is there a way to see the execution plan of my mongo query so that I can review where the performance bottle necks are?

UPDATE
A possible solution I'm considering is to have a property db.logs.isGraphed:boolean .  Then use a simple db.logs.find({isGraphed:false, createDate:{...date filter...}}).limit(5).  Wasn't sure if this is the approach most people would have considered in the first place?


texts: Can combine five mysql statements into just one statement?

texts:
Can I combine all the five statements into just one statement?

update mytable set myfield1 = replace(myfield1, '…' ,'-') ;
update mytable set myfield1 = replace(myfield1, '‖' ,'|') ;
update mytable set myfield1 = replace(myfield1, '”' , '"') ;
update mytable set myfield1 = replace(myfield1, '‘' , '`') ;
update mytable set myfield1 = replace(myfield1, '〃' ,'"') ;


texts: Can you control what tags are used in an XML column?

texts:
We allow our developers to use XML columns to store data.  We want to provide standard names to XML tags.

As a DBA, can we prevent the users from adding XML tags that are not predefined?


texts: Get durations from time series table where some condition holds consecutively

texts:
Given a table where each row has a timestamp and some measurement, how can I get all of the intervals during which the measurement consecutively satisfies some condition?

Here's a SQLfiddle (not sure if there's a better site for that nowadays).

So in this case, I'd like to to get all of the durations during which status is true. The starting point should be the first true point chronologically. The end point can be the first false point or the last true point, whichever is easier.

I looked to window functions first. I could come up with separate queries, one that obtained the starting points and one that produced the end points, but it felt like I was going about it the wrong way.


texts: Is it more performant to use a JOIN or a when using LIMIT 1?

texts:
I have a products table. I want to select all fields (of a given row) and the most profitable discount that the user can claim (depends on ordered quantity of the product).

When selecting only one value only, is it more performant to use a JOIN, like this:

SELECT p.*, qd.price AS qd_price

FROM products AS p

-- Find all applicable QDs
LEFT OUTER JOIN quantity_discounts as qd
    ON p.id=qd.product_id
    AND qd.min_amount <= ?
    AND (
        (qd.user_id IS NULL OR qd.user_id = 0 OR qd.user_id = ?)
        OR (qd.group_id IS NULL OR qd.group_id = 0 OR qd.group_id = ?)
    )

WHERE p.id=?

-- Get the lowest QD price
ORDER BY qd.price ASC
LIMIT 1

or a subquery, like this:

SELECT p.*,
(SELECT price FROM quantity_discounts
WHERE product_id=p.id
AND (
    (user_id=? or user_id=0 or user_id IS NULL)
    OR (group_id=? or group_id=0 or group_id IS NULL)
)
AND qd.min_amount <= ?
ORDER BY price LIMIT 1) AS qd_price
FROM products as p
WHERE p.id=?


texts: Add previous column alias to calculate sum/total of last column

texts:
I have a query which I need to optimize. It is using 3 subqueries 2 times each. Below is the query.

select i.itemid, b.itemname, i.batchno,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent < '2018-12-26 00:00:00') as openingBal ,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent >= '2018-01-01 00:00:00'
            and dateinvent <= '2019-12-31 00:00:00'
            and qty>0
            ) as inputQty,
        (select sum(qty) from inventtrans
            where itemid = i.itemid
            and batchno = i.batchno
            and inventlocationid = 'locationId'
            and dateinvent >= '2018-01-01 00:00:00'
            and dateinvent <= '2019-12-31 00:00:00'
            and qty<0
            )as outputQty ,
        (
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent < '2018-12-26 00:00:00'),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent >= '2018-01-01 00:00:00'
                and dateinvent <= '2019-12-31 00:00:00'
                and qty>0),0)
            +
             COALESCE((select sum(qty) from inventtrans
                where itemid = i.itemid
                and batchno = i.batchno
                and inventlocationid = 'locationId'
                and dateinvent >= '2018-01-01 00:00:00'
                and dateinvent <= '2019-12-31 00:00:00'
                and qty<0),0)
        ) as bal,
            i.location,
            -- split_part(i.inventsizeid, '/+[0-9]', 2) as size
            i.inventsizeid as size,
            i.configid as color
from inventtrans i, view_inventbatchtrans b
where
i.batchno = b.batchid
group by i.itemid,b.itemname,i.batchno,i.qty,i."location",i.inventsizeid,i.configid;

check the query for openingBal, inputQty, outputQt.
I need to add these 3 columns to get bal column. Here is the query which I optimized.

select i.itemid, b.itemname, i.batchno, i.openingBal, i.inputQty, i.outputQty, i.bal, i.location, i.inventsizeid as size, i.configid as color
from (select i.*,
  (select sum(qty) from inventtrans
    where itemid = i.itemid
    and batchno = i.batchno
    and inventlocationid = 'locationId'
    and dateinvent < '2018-12-26 00:00:00') as openingBal,
    (select sum(qty) from inventtrans
        where itemid = i.itemid
        and batchno = i.batchno
        and inventlocationid = 'locationId'
        and dateinvent >= '2018-01-01 00:00:00'
        and dateinvent <= '2019-12-31 00:00:00'
        and qty>0
      ) as inputQty,
      (select sum(qty) from inventtrans
          where itemid = i.itemid
          and batchno = i.batchno
          and inventlocationid = 'locationId'
          and dateinvent >= '2018-01-01 00:00:00'
          and dateinvent <= '2019-12-31 00:00:00'
          and qty<0
        )as outputQty, (COALESCE(i.openingBal, 0) + COALESCE(i.inputQty, 0) + COALESCE(i.outputQty, 0)) as bal
      from inventtrans i
    ) i, view_inventbatchtrans b
        where
        i.batchno = b.batchid
        group by i.itemid,b.itemname,i.batchno,i.qty,i."location",i.inventsizeid,i.configid;`
 Where I run optimized query it throwing an error as

  SQL Error [42703]: ERROR: column i.openingbal does not exist
        Position: 937 which is at `(COALESCE(i.openingBal, 0) + COALESCE(i.inputQty, 0) + COALESCE(i.outputQty, 0)) as bal

Could you tell what is it that I'm doing wrong. Can we use alias column for this purpose?


texts: design DB review and check constraint for postalcode help?

texts:
Hi. I created this database to just to sharpen my skill. I have one Question - when I create I make the FOREIGN KEY personID in Addresses table but I changed it to be AddressID in Persons table because I can have a list of Addresses. When I create program  is that fine? I create check constraint to check the canadian postal code is my  constraint right ?

CREATE TABLE dbo.Addresses(
  AddressID int IDENTITY(1,1) PRIMARY KEY,
  Street varchar(255),
  City varchar(255),
  Province varChar(255),
  PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9')
)

CREATE TABLE dbo.Persons (
  PersonID int IDENTITY(1,1) PRIMARY KEY,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255) NOT NULL,
  MiddleName varchar(255) null,
  DateOfBirth date NOT NULL,
  Gender char(1) NULL CHECK(Gender='M' OR Gender='F'),
  AddressID int FOREIGN KEY REFERENCES dbo.Addresses(AddressID)
)


texts: sql web-application Connection String for member of a domain group

texts:
In my testing environment, I have a local sql express (2008R) server instance.  I've created a domain group and given it a login in the database.

But now, how do I consctruct the connection string for my web-application?  Do I give it the username and password of one of the members of the group?

And, what do I put for a "user" in the Database security section?  The group itself doesn't have a password.

Do I have to add the service account from the application pool to the AD group?

Here is what I have for a connection string; but this allows all connections, it seems:

<add name="development" connectionString="server=myserver\sqlexpress;
    database=mydatabase;Persist Security Info=True;Integrated Security=SSPI;
    Trusted_Connection=True; Pooling=True" providerName="System.Data.SqlClient"
/>


texts: Minimum user rights for connecting MS Sql database remotedly

texts:
I have followed this tutorial for getting connected to a Microsoft SQL Server. I connect with ping functionality of Glassfish Server and thus I have to give some user rights to be able to even ping the Database.
My problem is that probably my user has not rights to take external connections to the database or even more sad, not any permissions. What kind of user permissions there should be applied before I can connect to my database with my user?
(I suppose there are needed some GRANTS before accessing the database, because making that on Mysql needed quite many of them. Now the actual problem is I could not apply the same queries here 'cause the SQL scripts are incombatible)


texts: backup postgresql database to submit with project

texts:
I have a final year project written in Matlab in conjunction to a postgresql database called 'hm'.

I asked in which way could I submit the database and I was told to perform a back up of the database using pgadmin3.

What is the recommended format? I want the person to be correcting the assignment to be able to use my own database in his own postgresql version. Which format and options should I check to ensure that they can work with the backup?

I am using postgresql 9.4.


texts: Pivot time series into date period columns with array aggregate cells

texts:
Given a time series of events where each event has a successful or unsuccessful outcome, how do I pivot the ratio of events by entity and time period columns with an aggregate array cell value? I suspect this can be done with crosstab query and array_agg.

Akin to uptime status reports, I'm trying to calculate something like this in SQL:

The volume of data is small enough that I could do the reduction client-side in a general purpose language, but it would be nice to do this efficiently in the database for a larger amount of data.

Time Series Example

+-----------------------------------------+
|  DateTime             Entity    Result  |
+-----------------------------------------+
| 2016-01-01 11:00...  :a        :success |
| 2016-01-01 17:00...  :a        :success |  -- two events for :a on same day
| 2016-01-01 11:01...  :b        :fail    |
| 2016-01-01 11:03...  :c        :success |
| 2016-01-01 13:00...  :d        :success |  -- only one event for :d
| 2016-01-02 11:00...  :a        :success |
| 2016-01-02 11:01...  :b        :fail    |
| 2016-01-02 11:03...  :c        :success |
| ...                                     |
+-----------------------------------------+

Desired Aggregate

Where each value cell after the key column is an array with shape [cnt_total cnt_success cnt_fail]:

+-----------------------------------------------+
| Entity     2016-01-01  2016-01-02  2016-01-xx |
+-----------------------------------------------+
| :a            [2 2 0]     [1 1 0]         ... |
| :b            [1 0 1]     [1 0 1]         ... |
| :c            [1 1 0]     [1 0 1]         ... |
| :d            [1 1 0]     [0 0 0]         ... |
+-----------------------------------------------+

To keep things simple, this report will never need more than 10 date window columns and I can dynamically template the SQL pivot output.

If I had to decompose this transformation:

Aggregate time series by date window (hour/day/week/month/quarter/year) and Result.
Accumulate the counted aggregate into some data structure like a hash-map or array of [count_total count_success count_fail]
Return the accumulated two-dimensional result as [entity period1 period2 ...] for displaying % on the client.


texts: How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

texts: I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating at a point of time?


texts:
Trying to understand Couple of things in the above query plan.

Why is there a Stream Aggregate in the query when I dont have a group
by.  I am guess it has something to do with the join being merge and
its doing a sort?
Secondly and more importantly why does an estimate    of 13 going
into the stream aggregate comes out as an estimate of    24,595,900?
This is causing the secondary problem of Object4 getting    Clustered
index scanned instead of a nested loop.  I had to split the query into two queries instead of using a OR and the join turns into a nested loop and seeks into Object4.

Query Plan

https://www.brentozar.com/pastetheplan/?id=BJk2eFLul


texts: Why is != not working? how to compare with null values?

texts:
I am having a hard time understanding why the following query is not working?

select id,sold_dealer_id from myi_corporate where sold_dealer_id != 36;

When I do the following I am getting the result

select id,sold_dealer_id from myi_corporate where sold_dealer_id = 36;

However for != I am not getting the opposite results.

I am returning to psql after a long time after using MongoDB and might have forgotten the basics. Any suggestion is much appreciated


texts:
One of our database table tblpages seems to be corrupted. When I select the table, its says

show full fields from `favc`.`tblpages`
Table 'tblpages' doesn't exist

So I thought repairing it will be possible. I run a repair on the table and got this unsuccessful message.

tblpages                    repair  Warning   InnoDB: Tablespace is missing for table 'tblpages'
tblpages                    repair  Error     Table 'tblpages' doesn't exist
tblpages                    repair  status    Operation failed

I don't have any latest copy of this table.
Please help with any advise you may have. Thanks!


texts:
I don't understand how this query works:

1    SELECT
2        EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'SubtypeFieldInfo/DomainName') AS domain_name
3    FROM
4        SDE.GDB_ITEMS_VW i
5        JOIN SDE.GDB_ITEMTYPES it ON
6            i.Type = it.UUID
7            ,TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'))) definition_xml

What does the comma at the start of line #7 do?

My guess is that whoever wrote the query used some sort of SQL short-hand. Perhaps if someone could help re-format the query in 'beginner SQL' it might help.


texts: Can I install SQL Server 2012 on a machine with 2008 R2 already?

texts: I have been using SQL Server 2008 R2 for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in installing SQL Server 2012 on a machine that has SQL Server 2008 R2 running?


texts:
I have declare a variable in SQL Server: Declare @variable

How can I drop or enable the variable when I don't need it?


texts: Dropping empty partitions in Oracle 11g DB

texts:
I have task to drop empty (0 rows) partitions and subpartitions from data warehouse.

I'm preparing script that:

Will count the rows of partition/subpartition
When row count will be 0 the partition should be dropped (and confirmation message)
When row count is different than 0, message that this partition
was not empty

To check number of rows I created SELECT 'partition_name', COUNT(*) FROM user.table PARTITION (partition_name);
I was advised that count will be better way than basing on NUM_ROWS column of DBA_TAB_PARTITIONS or DBA_TAB_SUBPARTITIONS (however from those 2 I will extract the list of potential candidates with other data to excel for later analysis).
I was also advised not to use dbms.output.

There are millions of potential candidates (10M+ on some dbs).On analysis stage I plan to exclude 'MAXVALUE' partitions and HASH type using Excel filters and then use above script.

Does someone have similar script? I need pattern for one partition and then I will generate rest in Excel.
Are there more things to exclude from drop list on analysis stage?


texts: The most efficient method to obtain the total entries into a mysql table

texts:
I have a table that has a very high usage (insert/update/select) - one of the operations is
to obtain a count of the number of rows.
So far I've been using the classic select count(pk) from table but I'm thinking to read it from information_schema instead of doing the count.

select table_rows from tables where table_name = 'my_table'

Is this reliable enough ?

If there is a different method please let me know.

thanks!


texts: Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

texts:
I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries.

The first with the non-clustered index inserting only the key field into a temp table (so no key lookup is performed) and the second using that temp table to join back to the original table to filter it down on the key and then select the fields I need.

I'm typically querying tables with hundreds of millions to tens of billions of rows when I notice this. I'm not sure if it can be related to the fact that I'm eliminating the key lookup when the table is first loaded into memory and instead I'm inserting the key into a temp table so that the subsequent field lookup query occurs between two tables already in memory?

The difference in time I'll see is usually significant too, e.g. on the order of minutes.


texts: Why does OS login not exist in the user table, but I can still login to MySQL?


texts: How do I optimize large table so queries that target only recent data perform optimaly?

texts:
So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved.

I am open to changing design or if there is mechanism in MsSql to solve this. I have limited options to try different solutions as database is in production and its hard to reproduce.

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created, -- index2 NONCLUSTERED INDEX
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active
[Modid] [timestamp] NOT NULL
)


texts: SQL Server error 5105 cannot bring db online

texts: I took a database offline to make read/write, but now I can't bring it back online. I get the following error message:


texts:
I'm trying to add a CmdExec as task in a job, on MS-SQL 2019 on Centos8.
However, the task always fails with "running the requested program is not allowed".
I have tried specifying both /bin/true and true as the program, but these also fail.
Is there a list of authorised programs for CmdExec, and is this is editable?
I found that MS-SQL Linux 2017 doesn't support CmdExec, but I think this has been fixed in 2019


texts: How to make Postgres autovacuum not impact performance?

texts:
I am running into problems where Postgres autovacuum processes are interfering with the overall performance of my database. It is making my select, insert, and update queries slower than usual. What are some settings, and optimization strategies that will make autovacuum have less of an impact on my database performance?

Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?


texts: Limiting Generated Sequence Values in Multi-table Insert

texts:
In Oracle, given the following objects:

create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;

Suppose I populate table a as follows:

insert into a (x) values (1);
insert into a (x) values (2);
insert into a (x) values (3);
insert into a (x) values (4);

Using the values in a, I want to populate tables b and c as follows: when a.x is even, generate a new number from the sequence and insert my_seq.nextval into b.val and c.val; otherwise, insert my_seq.currval (the most recently generated value but not a new one) into c.val.

What I currently have is an insert all statement that looks like this:

insert all
    when mod(x,2) = 0 then
        into b (val) values (my_seq.nextval)
        into c (val) values (my_seq.currval)
    else
        into c (val) values (my_seq.currval)
select x from a

Since Oracle's treatment of sequences in an insert all statement is to generate a new value for each row returned in the subquery, I'm getting 4 newly generated values inserted into c. How can I attain the behavior I want, 2 newly newly generated values inserted once into b and twice into c?


texts: SQL Server 2014 All Administrator Accounts Disabled

texts:
I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) and sa account on the production server! Please tell me a way that there is a way to access the server with administrator privileges! :(

Is it possible from an installation or some kind of command line etc?

Again, Administrator account IS disabled for SQL Server (but I have remote connection to server, the account itself is working...)

The Administrator account of the SERVER is AAAAA\Administrator, and the instance is SOMETHING\SOME_INSTANCE.

When I am trying to log in from the remote desktop (on the server itself) with the AAAA\Administrator account, it says its disabled:


texts: Availability Groups Failover Readinness "No data loss" and rollback of nonqualified transactions

texts:
I have Availability Groups with Synchronous Replication and Automatic Failover setup with SQL Server 2016 SP2 in my production environment.

I want to do a manual failover for patching and I have done this in my dev environment several times. Right now my AG Dashboard shows, Failover Readiness "No Data Loss".

However whenever I do a failover, I still get notifications (a manually setup alert) from SQL Server telling me there were nonqualified transactions rolled back on the server, just like I did when I had Mirroring.

So, is this truly no data loss? It doesn't sound like it. And what exactly are nonqualified transactions vs. just transactions?


texts: How to create a column with rows in postgres?

texts:
I want to create a table that withholds an array of values inside one column in table.

Example in JSON form:

 "trainNumber": 1,
 "departureDate": "2020-01-26",
 "operatorUICCode": 10,
 "operatorShortCode": "vr",
 "trainType": "IC",
 "trainCategory": "Long-distance",
 "commuterLineID": "",
 "runningCurrently": false,
 "cancelled": false,
 "version": 266667265768,
 "timetableType": "REGULAR",
 "timetableAcceptanceDate": "2019-11-08T10:34:22.000Z",
 "timeTableRows": [
   {
     "type": "DEPARTURE",
     "scheduledTime": "2020-01-26T04:57:00.000Z",
     "causes": []
   },
   {
     "type": "ARRIVAL",
     "scheduledTime": "2020-01-26T05:02:00.000Z",
     "causes": []
   },
   {
     "type": "DEPARTURE",
     "scheduledTime": "2020-01-26T05:03:00.000Z",
     "causes": []
   },
   {
     "type": "ARRIVAL",
     "causes": []
   },
   {
     "type": "DEPARTURE",
     "cancelled": false,
     "scheduledTime": "2020-01-26T05:05:00.000Z",
     "causes": []
   },

As show in example "timeTableRows".

How do I create this?


texts: SQL Server 2012 Resource Governor Enhancement

texts:
In SQL Server 2012 what specific enhacement was made regarding controlling memory usage by using Resource Governor. Up until SQL Server 2008 R2 resource governor was only able to control memory related to query memory grant.

I looked in SQL Server 2012 BOL however it does not specifically states anything clearly.


texts: Query to retrieve SQL Server login password length

texts:
I've been asked by corporate security to review our Azure SQL Databases and find out if there are any SQL Logins which have "short" passwords. I am not aware of a way to achieve this - I know I could alter the login and force them to use the windows password policy - but wouldn't this have the side effect of disabling the logins and making our applications fail ?
Does anyone have a suggestion - TSQL or Powershell preferably


texts:
I have this pretty straightforward SQL query

SELECT count(date_mdy) AS unique_date_count,
  county,
  date_s
FROM (
  SELECT date_mdy,
    county,
    date_s
  FROM air_quality2
  GROUP BY county, date_s ,date_mdy
  ORDER BY county, date_s
) AS t1
GROUP BY county, date_s
ORDER BY date_s;

this returns

I want the county names to be the column headers. I know there is a crosstab function and I have seen the many posts on here where they use it but I cannot get it to work on this query.


texts: Partitioning a large table has not improved performance, why?

texts:
In SQL Server 2014 I have partitioned one of my large tables weekly and  defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next week.

This is the result:

This is for an AVL System (Vehicle Tracking). I have partitioned on PositionDate (datetime).
All our queries have PositionDate in the WHERE clause and in many cases we have VehicleId in the WHERE clause too. So I created two aligned indexes on VehicleId (int):

one on (PositionDate,VehicleId);
one on just (VehicleId).

But in every query that contains VehicleId in its WHERE clause neither of these two non-clustered indexes is used (according to the query plan).

I have a performance problem now.

I compared the query plans between partitioned and non-partitioned table for queries like below:

Select * from MyNonPart_Table Where PositionDate between '2016-05-01' AND '2016-06-01'

Select * from PartitinedTable Where PositionDate between '2016-05-01' AND '2016-06-01'

and awesomely I see that the first query costs 30% but the second, 70%.

I have one file group with two files for the partitioned table.

My questions:

Is the number of rows in each partition more than the optimal number of rows for partitioning? If I partition by day and hold last 60 days' data live, will that help me improve performance?
Are my non-clustered indexes well defined or I should remove them? We have PositionDate in the WHERE clause of all queries and VehicleId in many of them.
Am I misusing partitioning for this scenario? If I define good indexes on my non-partitioned table and move oldest data (more than 2 month old) to the archive table, will that work well for my case?

DDL for my indexes:

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_Primary] PRIMARY KEY CLUSTERED
(
    [PositionDate] ASC,
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-VehicleId] ON [dbo].[MyTable]
(
    [VehicleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NCIX_VehicleId_PositionDate] ON [dbo].[MyTable]
(
    [VehicleId] ASC,
    [PositionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

This is an example, my queries are in SPs that receive datetime type parameters.


texts: Updating SQL Server 2008 R2 using slipstreamed source

texts:
I have a requirement to create a SQL Server 2008 R2 SP1 setup media. I have done this using slipstream method as recommended by Microsoft. I can only use this to create new installation. But is there a way I can use this media to update an existing SQL Server 2008 R2 instance?

Thanks in advance,

Dheena


texts: In MySQL, how can I have non-nullable timestamp column without a default value?

texts:
I want a non-nullable timestamp column where the inserter is required to specify what the value should be.

The following configuration

CREATE TABLE test (
  t timestamp not null
);

results in the default CURRENT_TIMESTAMP value, while

CREATE TABLE test (
  t timestamp not null default null
);

results in

  ERROR 1067 (42000): Invalid default value for 't'

I want the same behavior that, for example, the int column below has:

CREATE TABLE test (
  i int not null
);


texts:
I've got two database tables:

Persons
id   user   product
1     1       3
2     1       5
3     1       7

products
id   kilo
3     8
5     15
7     3

I'm wondering how to count values from a database, in this case I want to get the total kilo's of products for user 1. The result should be '26'. Is this possible?

SELECT kilo FROM `products` WHERE `id` IN (SELECT `product` FROM `person` WHERE `user` = '1')


texts: How does the SQL Spec specify literals with a decimal should be typed?

texts:
Different databases implicitly type numeric literals differently,

MySQL types 1.0 as decimal(2,1)
PostgreSQL types 1.0 as numeric

Which one of these is correct?


texts: A trigger making a timestamp of when another column was changed

texts:
I've beeen trying to create a trigger, that'll make a timestamp, of when our account was "deleted" which we do by setting deleted = 1. Below code creates a trigger for that purporse, however it has compilation errors and doesn't work.

So i wanna update the column DELETEDTIME with the the current sysdate timestamp of when deleted was set equal to 1, however i can't see where i've gone wrong in the code below

CREATE OR REPLACE TRIGGER trg_deletiontime
AFTER UPDATE
OF DELETED ON VTIGER_CRMENTITY
FOR EACH ROW
WHEN (old.DELETED = 0 AND old.DELETEDTIME = NULL)
BEGIN
new.DELETEDTIME := sysdate()
END;

Error from sys.user_errors says "PLS-00103: Encountered the symbol "END", however no amount of ; or / seems to fix this


texts: Compatibility mode change does not reflect on secondaries

texts:
I was of belief that changing compatibility mode on primary database in database mirroring would make the change for mirror database.

But it proved to be wrong. I queried using sys.databases on Primary where i changed compatibility mode to 120 , however on its mirrored database compatibility level was still 100. Why?

Am i using the incorrect fn here sys.databases?

Also if it does not change what does it mean, do i need to do failover/failback to reflect?

What if i had log shipping secondary database in read only as well where this change needs to reflect? Do i need to rebuild log shipping then?

Thanks


texts: Performance of an In-Memory Table is worse than a disk-based table

texts:
I have a table in SQL Server 2014 that looks like the following:
CREATE TABLE dbo.MyTable
(
[id1] [bigint] NOT NULL,
[id2] [bigint] NOT NULL,
[col1] [int] NOT NULL default(0),
[col2] [int] NOT NULL default(0)
)

with (id1,id2) being the PK. Basically, id1 is an identifier to group a set of results (id2, col1, col2), whose pk is id2.
I'm trying to use an in-memory table to get rid of an existing disk-based table which is my bottleneck.

The data in the table are written -> read -> deleted once.
Each id1 value has several (tens/hundreds of) thousands of id2.
Data are stored in the table for a very short amount of time, e.g. 20 seconds.

The queries performed on this table are the following:
-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
  SELECT @fixedValue, id2, col1, col2 FROM AnotherTable

-- READ:
SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1

-- DELETE:
DELETE FROM MyTable WHERE id1 = @value

Here's the current definition that I used for the table:
CREATE TABLE dbo.SearchItems
(
  [id1] [bigint] NOT NULL,
  [id2] [bigint] NOT NULL,
  [col1] [int] NOT NULL default(0),
  [col2] [int] NOT NULL default(0)

  CONSTRAINT PK_Mem PRIMARY KEY NONCLUSTERED (id1,id2),
  INDEX idx_Mem HASH (id1,id2) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Unfortunately, this definition results in a degradation of performance with respect to the previous situation with a disk-based table. The order of magnitude is more or less 10% higher (that in some cases reach 100%, so double time).
Most of all, I was expecting to gain a super-advantage in high-concurrency scenarios, given the lock-free architecture advertised by Microsoft. Instead, the worst performances are exactly when there are several concurrent users running several queries on the table.
Questions:

what is the correct BUCKET_COUNT to set?
what kind of index should I use?
why the performance are worse than with the disk-based table?

A query of sys.dm_db_xtp_hash_index_stats returns:
total_bucket_count=131072
empty_bucket_count=0
avg_chain_len=873
max_chain_length=1009
I changed the bucket count so the output from sys.dm_db_xtp_hash_index_stats is:
total_bucket_count=134217728
empty_bucket_count=131664087
avg_chain_len=1
max_chain_length=3
Still, the results are almost the same, if not worse.


texts: Why is it not a good idea to use MariaDB's extra_port for replication?

texts:
MariaDB has an extra_port for administrative access that helps in cases the thread pool or main port is locked up.
MariaDB's documentation says it's use case is primarily administrative access and it can be used to make sure monitoring systems always have access.
To me "always have access" sounds like something you want for replication but it's not mentioned and I could not find anyone writing about using the extra port for replication.
The paragraph When to Use the Thread Pool in the documentation says:

Thread pools are most efficient in situations where queries are
relatively short and the load is CPU-bound

Replication runs forever (fingers crossed) and is IO-bound. This sounds like another indication to use the extra port (although replication is not a query).
My Questions:
Can you explain me why it's not a good idea to use the extra port for replication?
If it actually is a good idea, why is it not widespread (as indicated by not finding anything substantial when googling for "mariadb extra_port replication")?
Do replication connections get some special treatment and/or priority?


texts: Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table

texts:
First, sorry for the long title.

The setup

I reduced the example to the minimum, to make it clearer, so there is no meaningful semantic anymore.

DBMS: Azure SQL Database V12.

Assume there is following table:

CREATE TABLE [dbo].[MuchDataTable](
    [SmallDateTimeColumn] [smalldatetime] NOT NULL
)

GO
CREATE CLUSTERED INDEX [IX_MuchDataTable_SmallDateTimeColumn] ON [MuchDataTable] ([SmallDateTimeColumn])

Now I want to get aggregated data specific for a range of an arbitrary amount of days. As the table contains a lot of rows (over 150 Mio.) I created an indexed view, which looks basically like following:

CREATE VIEW [dbo].[AggregatedDateView]
    WITH SCHEMABINDING
AS
    SELECT CONVERT(DATE, [SmallDateTimeColumn]) AS [DateColumn], COUNT_BIG(*) AS [Count]
    FROM [dbo].[MuchDataTable]
    GROUP BY CONVERT(DATE, [SmallDateTimeColumn])

GO
CREATE UNIQUE CLUSTERED INDEX [IX_AggregatedDateView_Date] ON [dbo].[AggregatedDateView] ([DateColumn])

For my day grouping I convert each DateTime to a Date. Then I create an clustered index over the view.

The problem

Executing a simple SELECT * over the view scans the index of the view. That's fine:

However, if I query with a WHERE constraint on the computed date column, a clustered index seek on the original table is performed:

The question

My understanding is, that there should be a B-Tree consisting of all converted dates for the indexed view. Why does SQL Server choose to scan the clustered index of the underlying table? Is this not possible with an indexed view and I have to build a solution atop of triggers and a normal table?

Edit: for more clarification, it becomes a real problem, once a range of dates is scanned, so something like

SELECT * FROM [AggregatedDateView] WHERE [DateColumn] > '2016-05-20' AND [DateColumn] < '2016-08-20'

Here the index over 3 months is scanned, which is in my case already a lot.


texts: MONGODB primary became secondary after servers restart

texts:
I had this config before restart:

{
    "_id" : "rs0",
    "version" : 5,
    "members" : [
        {
            "_id" : 0,
            "host" : "Ubuntu12041vanilla:27017"
        },
        {
            "_id" : 1,
            "host" : "slave-ubumaster-9mnltsm2.srv.ravcloud.com:27017"
        },
        {
            "_id" : 2,
            "host" : "slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017"
        }
    ]
}

first node was primary...
After the restart of all servers, all of them became secondaries.
So I am trying to push the first one back to be primary, but it is not working.

cfg = rs.conf()
cfg.members[0].priority = 0.5
cfg.members[1].priority = 2
cfg.members[2].priority = 2
rs.reconfig(cfg, {force:true})// the force is because I am secondary

So I am getting this error:

{
    "assertion" : "need most members up to reconfigure, not ok : slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017",
    "assertionCode" : 13144,
    "errmsg" : "db assertion failure",
    "ok" : 0
}

And I understand why: this host (slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017) is not functioning anymore because of a network problem.
So I tried to change the hosts: cfg.members[1].host = "slave:27017"
but I get this error:

{
    "assertion" : "assertion db/repl/rs_initiate.cpp:119",
    "errmsg" : "db assertion failure",
    "ok" : 0
}

So I am stuck in a loop where I cant bring up the primary and I cant change the hosts to fix the network problems.

Do you see any chance to get out of that loop before giving up the replica set?

rs.status()
{
    "set" : "rs0",
    "date" : ISODate("2014-02-14T16:34:00Z"),
    "myState" : 2,
    "members" : [
        {
            "_id" : 0,
            "name" : "Ubuntu12041vanilla:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "optime" : {
                "t" : 1392323075000,
                "i" : 1
            },
            "optimeDate" : ISODate("2014-02-13T20:24:35Z"),
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "slave-ubumaster-9mnltsm2.srv.ravcloud.com:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "t" : 0,
                "i" : 0
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : 0,
            "errmsg" : "socket exception"
        },
        {
            "_id" : 2,
            "name" : "slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "t" : 0,
                "i" : 0
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : 0,
            "errmsg" : "socket exception"
        }
    ],
    "ok" : 1
}

Thanks


texts: How to get the sum of a particular item id in a column

texts:
Item         ItemName        PackageID  Specifics    brandname  GRNQty  UnitPric GRNValue
102814742801 ALL BRAN FLAKES 1PKT       Bran.        KELLOGS    7       15.9    111.3
102814742801 ALL BRAN FLAKES 1PKT                    KELLOGS    50      15.9    795
100314860301 ALMOND FLAKED   1PKT       Alm.         HYSON      20      52      1040
100314860301 ALMOND FLAKED   1PKT                    HYSON      30      52      1560
101814899665 ALMONDS ROASTED 1KG                     HOMELINE   250     59.5    14875
101426707397 ANCHOVY FILLETS 1TIN       Anch.        VARIOUS    4       34      136
101426707397 ANCHOVY FILLETS 1TIN                    VARIOUS    8       34      272

I'd like to get the sum of a particular Item in the column. I want to do Sum of GRNQty as well as GRNValue of Same Type Item in the table.


texts:
I have a server application that's using MySQL INNODB databases to store its data. I know that this application has worked as is in the past, but I don't know anything about the database server configuration it was using. Now while interacting with said server application using its respective client application I get the following MySQL error:

Aborted connection XX to db: '...' user: '...' host: '...' (Got an error reading communication packets)

The server application meanwhile has this to say:

[ERROR] Database Execution Error - query '{ call sp_x8(?,?,?) }, message '[MySQL][ODBC 5.2(w) Driver][mysqld-5.6.19]Lost connection to MySQL server during query'
[ERROR] 08S01 : try to reconnect : 08S01
[ERROR] Database Execution Error - can't execute stored procedure '{ call sp_x8(?,?,?) }'
[ERROR] Database Error - query '{ call sp_x8(?,?,?) }', param : 1(int64), 0(int), -1(int),
[ERROR] Database Execution Error - query 'rollback', message '[MySQL][ODBC 5.2(w) Driver][mysqld-5.6.19]MySQL server has gone away'
[ERROR] 08S01 : try to reconnect : 08S01
[ERROR] Database Execution Error - can't execute stored procedure 'rollback'
[INFO] UnhandledException

This happens at the same spot every time. It runs other stored procedures before just fine, but the one where the error happens is part of a larger transaction that runs several stored procedures. From the general log file:

23 Query    set autocommit = off
23 Prepare  call sp_x1(?,?,?)
23 Execute  call sp_x1('1','1','0')
23 Close stmt
23 Query    commit
23 Query    set autocommit = on
46 Query    set autocommit = off
46 Prepare  call sp_x2(?,?)
46 Execute  call sp_x2('1','')
46 Close stmt
46 Prepare  call sp_x3(?)
46 Execute  call sp_x3('1')
46 Close stmt
46 Query    set autocommit = off
46 Prepare  call sp_x4(?,?,?)
46 Execute  call sp_x4('1','10','0')
46 Close stmt
46 Query    commit
46 Query    set autocommit = on
46 Query    commit
46 Query    set autocommit = on
45 Query    set @@sql_select_limit=DEFAULT
45 Query    set autocommit = off
45 Prepare  call sp_x5(?,?)
45 Execute  call sp_x5('1','1024')
45 Close stmt
45 Prepare  call sp_x6(?,?,?)
45 Execute  call sp_x6('1','1','-1')
45 Close stmt
45 Prepare  call sp_x7(?,?,?)
45 Execute  call sp_x7('1','1','0')
45 Close stmt
45 Prepare  call sp_x8(?,?,?)

This is the entire log for this session, since after that last prepare the connection is lost. I doesn't matter if I make it not run that SP either, it will fail on the next one coming after it then.

First I tried running the applications with the latest MySQL servers and ODBC connectors, but that actually resulted in this and other errors happening even earlier. Since the application was developed around 2014~2015 I downgraded to ODBC 5.3.4 and MySQL server 5.6.19, which got me this far.

Now the internet is full of tips about how to solve this error message, but the usual solutions are tweaking the various timeout and max_allowed_packet settings. However, changing these settings made no difference to me, the error kept happening in the same spot. Right now these settings are as follows. I know that they're ridiculous high, but I was just hoping for some kind of change that never came as I raised them further and further.

max_allowed_packet         = 1024M
interactive_timeout        = 600000
wait_timeout               = 600000
connect_timeout            = 600000
net_read_timeout           = 600000
net_write_timeout          = 600000
innodb_buffer_pool_size    = 2G
innodb_log_buffer_size     = 64M
innodb_log_file_size       = 1024M
innodb_log_files_in_group  = 3

I should also mention that all this happens in the span of seconds, so even default timeouts of 30 seconds shouldn't cause these errors I believe, because that entire general log snippet above takes place over the span of maybe 5 seconds.

I'm at my wits' end and I'm hoping someone might have an idea what else could be causing this error, or which versions the application might've targeted.


texts: MongoDB Using String-Type Shard Key for Zone Ranges

texts:
Mongo lets any field type be used to set ranges for Shard Zones, but doesn't specify how analyses are conducted.

My goal is to point queries and writes at the exact right server, WITHOUT using a compound shard key (for various execution complexities).

I want to embed a unique user alpha-numeric ID, plus a latitude and longitude value into a single string, and use this as my shard key.

Ex: (ID.lat.lon) "4kjaj29.48.-89"

Would fall within the range:

"0000000.45.-100" to "zzzzzzz.50.-80"

Mongo uses utf8 strings, https://www.utf8-chartable.de/unicode-utf8-table.pl, in which for alpha-numeric characters 0 is the "least" and z is the "greatest".

So I have to imagine this is how range inclusion is computed? Does anyone know off-hand if this approach is correct? And if not, can you point me in the right direction to accomplish this?

There is definitely a way to accomplish this, since some kind of range inclusions calculation is occurring with string types, but just requires knowing the analysis strategy.


texts: What are the most relevant similarities between T-SQL and MDX?

texts:
I have used T-SQL (CTEs, windowed functions, recursive CTEs, large data, table-valued functions, APPLY, PIVOT, stored procedures, etc.), but have never queried a Microsoft SSAS cube.

Are there similar concepts in MDX for the following T-SQL concepts?

PIVOT
UNPIVOT
Windowed functions
Recursive Common Table Expressions

Are there any pitfalls for a T-SQL user using MDX?

I am hoping there are some common analogies between T-SQL and MDX, that will help someone good at T-SQL transition to MDX.

It would also be great if there was a guide for common mistakes that are made when coming from T-SQL into MDX.


texts: Big size difference in ibdata file from live to staging

texts:
I have a mysql server running on AWS Instance. The server has a schema of around 8 GB which is the main schema.

I have two environments staging and live. The live has only one schema i.e. live_schema where as the staging database has multiple schemas with different time stamp like live_copy_03207... which are copy of live. So the total database size on staging is around 50 GB.
I am using InnoDB engine for the database.
Here's the issue. The live ibdata file is 4.2 GB where as the staging ibdata files is only 187 MB.

What could be the reason for this huge difference between ibdata size from live to staging? I know it only grow and there no shrinking, I know to shrink/cleanup the file I am required to backup the database and delete everything and restoring the database.


texts:
Within the key-value, hash-table DynamoDB we can only query for partition keys and optionally for a sorting key.

My Question is: If a Table A has items with primary partition attribute aId and I've been given a list of ids [aId], which I want to exclude from my result, what would be the best way to retrieve N items of table A, which do not contain ids of my id list [aId]?


texts:
I've been working with SQL Server for about 6 years and have completed a couple of certification exams so I consider myself reasonably knowledgeable.

I am however, about to spec and build my first SQL Server failover cluster. I know how it works, I've read about it over and over...

My question to you lot is: What advice/warnings would you give to someone in my position?

Did you find certain aspects of setup more tricky than others? Would I be best advised to upgrade from SQL Server 2008 r2 to 2012?

My loose plan is to use my company's existing HP blade enclosure to house 2 x HP servers with plenty of RAM/Cores - just a 2-node cluster with either a mirror or AOAG for added redundancy.

Thoughts?


texts:
We're getting slow performance (hours for a couple GB database) when trying to do a backup/restore of an aurora database. We essentially want to clone the database so we've got two side-by-side copies we can compare. To potentially speed this up, I'm looking into mysqlpump (notice, not mysqldump) as a way to export the data with parallel threads. However, after mysqlpump runs for a bit, I get the following error:

mysqlpump: [Error] (1457) Failed to load routine mysql.rds_import_binlog_ssl_material. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

The trouble is, I've tried --skip-routines and --exclude-tables mysql.proc but neither flag is helping. Screenshot below.


texts: Postgres 8.3 correct deletion of files in pgsql_tmp

texts:
We are talking about the PostgreSQL 8.3 RDBMS here. So, no pg_terminate_backend() is possible with this version.

Sometimes we have to kill running processes at Operating System level (kill -9 PID) to solve issues related to max_connections value reached. In such cases, we targeted long running SELECT queries to kill.

As a result, we found out that our filesystem grows at 98% and fills up quickly, showing 1500+ files in the pgsql_tmp directory.

Some orphan files are the expected result of this kind of maneuver, since Temp files should be deleted during proc_exit processing and aggressively terminating running processes are not the best option.

So, to get rid of this "trash", what is our best option:

Conduct a postmaster restart and expect the RDBMS will run around and clean out all the temp directories by itself; or
stop the postmaster, manually delete the files in $PGDATA/pgsql_tmp/ and then, start postmaster again; or
without stopping the server, manually delete the files in $PGDATA/pgsql_tmp/ which are older than the current day.

Please justify your answer(s).


texts: Mongodb is taking up 7GB of hard drive space on VM, how do I fix?

texts:
I am running Debian on a VM. I installed mongodb and it is taking  up A LOT of space. It is really slowing me down in college, with work and projects ... etc. I have a disk usage analyzer and I've found that 7GB are used up in my root/data folder. Does anyone know how I can get the space back? I've seen multiple answers, and they're not working. I can run mongod --smallfiles and it gives me an error (check below). I checked in my /etc/mongod.conf and I uncommented the smallfiles = true, as well as no journals = true. What else can I do? I don't think I should be taking up this much data.

Thanks

UPDATE I navigated into my /data/db/, which is where my mongo is, and my entire vm operating system is duplicated within that folder. I have no idea why. Does anybody know how this happened? Or a guess?

Thanks

mongod --smallfiles error:

    2014-07-22T12:30:45.730-0600 [initandlisten] MongoDB starting : pid=10423 port=27017 dbpath=/data/db 64-bit host=courtney-debian
2014-07-22T12:30:45.730-0600 [initandlisten] db version v2.6.3
2014-07-22T12:30:45.731-0600 [initandlisten] git version: 255f67a66f9603c59380b2a389e386910bbb52cb
2014-07-22T12:30:45.731-0600 [initandlisten] build info: Linux build12.nj1.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2014-07-22T12:30:45.731-0600 [initandlisten] allocator: tcmalloc
2014-07-22T12:30:45.731-0600 [initandlisten] options: { storage: { smallFiles: true } }
2014-07-22T12:30:45.743-0600 [initandlisten] journal dir=/data/db/journal
2014-07-22T12:30:45.743-0600 [initandlisten] recover : no journal files present, no recovery needed
2014-07-22T12:30:45.744-0600 [initandlisten]
2014-07-22T12:30:45.744-0600 [initandlisten] ERROR: Insufficient free space for journal files
2014-07-22T12:30:45.744-0600 [initandlisten] Please make at least 422MB available in /data/db/journal or use --smallfiles
2014-07-22T12:30:45.744-0600 [initandlisten]
2014-07-22T12:30:45.744-0600 [initandlisten] exception in initAndListen: 15926 Insufficient free space for journals, terminating
2014-07-22T12:30:45.744-0600 [initandlisten] dbexit:
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: going to close listening sockets...
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: going to flush diaglog...
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: going to close sockets...
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: waiting for fs preallocator...
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: lock for final commit...
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: final commit...
2014-07-22T12:30:45.745-0600 [initandlisten] shutdown: closing all files...
2014-07-22T12:30:45.746-0600 [initandlisten] closeAllFiles() finished
2014-07-22T12:30:45.746-0600 [initandlisten] journalCleanup...
2014-07-22T12:30:45.746-0600 [initandlisten] removeJournalFiles
2014-07-22T12:30:45.749-0600 [initandlisten] shutdown: removing fs lock...
2014-07-22T12:30:45.749-0600 [initandlisten] dbexit: really exiting now


texts:
We have been using SQL Server 2014 for our project as a backend source. We have been performing various joins on the tables. We came across a question in one to many and many to one relationship in joins.

Say for example: I have a table named Industry and another table called Products. Industry holds the list of Industry in market and Products holds that industry's multiple products.

Industry

Id
IndustryName
Owner

Products

Id
IndustryId
ProductId
ProductName

From the above tables, I am performing two queries as shown below:

1 to many

Select *
from Industry
inner join Products
    on Industry.Id = Product.IndustryId

many to 1

Select *
from Product
inner join Industry
    on Product.IndustryId = Industry.Id

Among the above which will be more efficient performance wise?


texts: Check attribute table referenced foreign key

texts:
I have two tables a and b like this:

a: id, type
b: a_id, ...

There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values.

Is it possible to include a CHECK for an attribute of the referenced table or do you have a better design idea?


texts:
I have this query

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000;

Which have these stats

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |        2429 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |          97 |
+--------------+---------------+---------------+-------------+

I wonder about the IX, which is an Intent Lock. What does that mean, and why does there exist one on the table it self? As I understand, it is not a true lock but more something SQL Server use (or is set by the transaction?) to indicate that a lock might occur.

Is the above right?


texts: What are the key points to avoid table locks?

texts: MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software -Java-)