texts: How does OPTION (FAST 1) actually interact with a client?

texts:
This is question 1 of 2 related to OPTION (FAST 1);

We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it down to what I believe to be the offending statement in the vendor's code which is:

SELECT MAX(column)
FROM [table]
WHERE <condition>
OPTION (FAST 1);

The spid leaves an open transaction and takes a lock on the table, blocking all other clients. However, the calling client no longer seems to be interacting with the server to tell the server that it's received the data to end the session.

Reading the documentation on Query Hints, I saw this statement

  FAST number_rows

  Specifies that the query is optimized for fast
  retrieval of the first number_rows. This is a nonnegative integer.
  After the first number_rows are returned, the query continues
  execution and produces its full result set.

This makes me wonder if the client has somehow broken communication, would the server keep the transaction open, processing the full result set after the first n rows are returned and leave the transaction open? The process is an internal process so I can't really watch an end-user execute the session to do it, and this is not something that happens every time the internal process occurs. However, it is only ever used by the internal process.

Having read Remus' answer on SO it seems like it is overkill for the simplicity of the query. Looking at the query, if they're receiving more that one result from an ungrouped MAX then something's very fishy.

So, as I prepare to work with the vendor, I was wondering if I could begin to accurately pin our blocking issues on the fact that this query hint is being used.

Please feel free to edit/request edits as I know this may actually be unclear.


texts: Order of returned rows with IN statement

texts:
I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)

I may get this result:
16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?


texts: MyISAM table row size is bigger than the sum of all the columns size?

texts:
I have a table:

CREATE TABLE `playlist` (
  `pid` int(11) unsigned NOT NULL,
  `mid` bigint(21) unsigned NOT NULL,
  `status` tinyint(1) NOT NULL,
  `add_time` int(11) unsigned NOT NULL,
  `modify_time` int(11) unsigned DEFAULT NULL,
  `filename` char(255) NOT NULL,
  `source` tinyint(3) unsigned NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

From show table status, I got the "Avg_row_length: 788". But the sum of the all the columns is 277 bytes.So I am confused that why the real row size is much larger then the sum of all columns'size?

I hexdump the data file and find the tail of the row is padded by space char:

00000000  fd 63 00 00 00 f2 3c 08  00 00 00 00 00 01 33 f4  |.c....<.......3.|
00000010  c7 4b 33 64 c8 4b 31 32  36 39 35 37 35 37 34 37  |.K3d.K1269575747|
00000020  38 31 37 2e 6d 70 33 20  20 20 20 20 20 20 20 20  |817.mp3         |
00000030  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20  |                |
*
00000310  20 20 20 01                                       |   .|
00000314

MySQL add many spaces into the tail of the row, I don't know why there are so many additional space characters.


texts:
I have added disk into the RAC system by the following way:

I have disk /dev/sdh1.

Marked it as an ASM disk:

--On node 1

oracleasm createdisk DISK7 '/dev/sdh1';

--On node 2

oracleasm createdisk DISK7 '/dev/sdh1';

--On node 1

alter system set asm_diskstring='ORCL:*';
alter diskgroup data01 add disk 'ORCL:DISK7' name DISK7;

It showed the following error:

ORA-15032: not all alterations performed
ORA-15033: disk 'ORCL:DISK7' belongs to diskgroup "DATA01"

Then tried to drop it

alter diskgroup data01 drop disk DISK7;

ORA-15032: not all alterations performed
ORA-15054: disk "DISK7" does not exist in diskgroup "DATA01"

I don't understand why was these errors occurring :(

So I added disk by force:

ALTER DISKGROUP DATA01 add DISk 'ORCL:DISK7' name DISK7 force;

It was added and Oracle tried to make a rebalance.

After that I run dropping disk command:

ALTER DISKGROUP DATA01 drop disk DISK7;

Again tried to rebalance and when the used space reached 0.01GB it hangged.

select operation,state,power,sofar,est_work,est_rate,est_minutes from v$asm_operation;

OPERA STAT      POWER      SOFAR   EST_WORK   EST_RATE EST_MINUTES
----- ---- ---------- ---------- ---------- ---------- -----------
REBAL RUN           1       2063       2089       2199           0

Where to check what happens, why hanged and how to solve?

-----------------------------EDIT--------------------------------

I have re-added other disks successfully by the following way:

--On node1 deleted disk

1. oracleasm deletedisk DISK10

--On node2 scanned disks

1. oracleasm scandisks

--On node1 added, scanned and listed disk

1. oracleasm createdisk DISK10 /dev/sdh4
2. oracleasm scandisks
3. oracleasm listdisks

--On node2 just scanned and listed disks

1. oracleasm scandisks
2. oracleasm listdisks

--On node1 connect to the ASM and add disk to the diskgroup

$ . oraenv
+ASM1
sqlplus / as sysasm
alter diskgroup DATA01 add disk 'ORCL:DISK10';

And DISK10 was successfully added and Oracle started to rebalance.
Now, when I query the asm_operation view shows the following:

    SQL> set linesize 200
    SQL> select * from v$asm_operation

GROUP_NUMBER OPERA STAT POWER  ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ------ ------ ----- -------- -------- ----------- ----------
           1 REBAL WAIT 10

What my dropping is waiting for? Maybe for the added disk to finish rebalance , yes?

-------------------------------------EDIT2--------------------

As I have found the drop is waiting for the rebalance operation after disks are added.

On node 1 v$asm_operation shows that the operation is waiting  and on node2 v$asm_operation shows the following:

select operation , state,sofar,est_work,est_minutes from v$asm_operation;

OPERA STAT      SOFAR   EST_WORK EST_MINUTES
----- ---- ---------- ---------- -----------
REBAL RUN     1538448    2702315         102

After this jobs finishes, I will wait for a while if dropping operation will also be finished, and then will write you again question on an answer :)

I've checked v$asm_operation and it is empty. So the dropping finished :) after 6 hours.(in my case it was 6 hours and it depends)


texts: Adding an index very slow...is there a mysql cmd to get an ETA or show progress?

texts:
I'm currently running an alter query on my table (20M entries)
in order to add an index. it's running for more than 3 days already
(stuck on 'copy to tmp table').

is there a way I can see the progress of the query or in other word
is there a way I can get an estimation time of completion ?

thanks.


texts: Does Oracle Enterpise Manager 12c can be install with Oracle Database 11g? Because the Oracle Enterprise Manager 11g is no longer available.


texts: SQL Server distribution database log file grows uncontrollably after full database backup

texts:
We have a merge replication environment that is pushing to 8 subscribers. This is working fine. Our distribution database is setup in Simple recovery mode. We have a maintenance plan that will backup all database every day at 00:30. Once this process completes, the distribution log file grows over the next 30 minutes and absorbs all the remaining space on the hard drive (about 90GB)

What then happens is that the distribution database shows as "Recovery Pending" and we cannot do anything till we restart the machine. After this I can shrink the log file down to 2MB.

I have no idea why this is happening. The log file is running at about 10MB during the day. The database size is sitting at 15GB.


texts:
I'm getting through  the postgresql tutorial, I've created my db, 2 tables, weather and cities and added a few lines of data.
When I query my db with this sql statement for example:

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

I should get this:

     city     | temp_lo | temp_hi | prcp |    date
--------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

but I get this:

San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29

I'm missing the column names for all queries. Why wouldn't psql show the column names? Do I have something to configure or is this some kind of bug?


texts: Using PgBarman for PostgreSQL 9.3 backup

texts:
I'm setting up a backup strategy for a PostgreSQL 9.3 db. My job includes:

take db backups every day/week/month
use a Synology NAS as remote backup device

I'm thinking of using Postgres [base_backup + WAL archiving] + [rsync to NAS] for accomplishing the task. The db is a low activity one so I should force wal archiving via pg_switch_xlog or similar. One aspect of this is I would find myself with partially empty WAL files and I would like to reduce their size (I've been suggested to use pg_clearxlogtail).

Searching the Internet I found PgBarman which looks like a good aid in accomplishing the task but the documentation on the site assumes:

I have Postgres installed on the backup machine
I install PgBarman on the backup machine

I don't think I can install either of the two on the NAS (am I wrong?).

So I'm rather planning to:

install PgBarman on the actual Postgres server machine
use it to backup the db locally and manage eventual recovery
archive the base backup and WAL files to the remote NAS

The main goal of the backup is not failure recovery as the Postgres server is hosted by a cloud hosting provider which ensures availability and no data loss, but rather for human error recovery.

Does this strategy make sense? Can anyone point me to a good resource to find out how to implement it? Would there be a better solution?
Any suggestions on implementing it or adopting alternative approaches would be greatly appreciated.

(I've already been suggested streaming replication many times but I don't think I need it and moreover I wouldn't know how to set that up in a NAS)


texts: Why does sql server consider a function that extracts a value from an xml parameter non-deterministic?

texts:
I want to add a computed field that extracts a value from another xml column.

The function looks like this:

create or alter function extractValue(@val xml) returns varchar(14)
as
begin
 return @val.value('(/profile/employeeNumber)[1]', 'varchar(14)')
end

When I run this:

alter table TableName Add EmployeeNumber as dbo.extractValue(ProfileXML) persisted

I get:

  SQL Server Database Error: Computed column 'EmployeeNumber' in table
  'TableName' cannot be persisted because the column is
  non-deterministic.

As far as I know a non-deterministic function is a function that can return different values for the same input. In this case if I pass the same xml the value returned is the same.

Also I cannot convert the hard-coded strings in the first and second parameters of the xml value function to parameters to have a 100% dependency on parameters.

Is there any tweak to persist the field? I want to persist it for performance reasons.

If I cannot get this to work somehow, the other option left is to use a trigger and populate the field via the trigger.

Thanks


texts: Mongodb Bulk find and remove is taking long time

texts:
I'm just making my hands dirty(handson) with MongoDB. First I did bulk insert of 1 million documents.

// Bulk insert query
var bulk = db.bulktest.initializeUnorderedBulkOp();
for(var i=0;i<1000000;i++){bulk.insert({a:i});}
bulk.execute();

It took 10 seconds to insert all the document which is comparatively good. None of the documents were missed.

However, when it comes to bulk find and removal, the performance has drastically came down and is really bad as it is removing each document in the order of 10ms.

// find and removal bulk query
var bulk = db.bulktest.initializeUnorderedBulkOp();
for(var i=0;i<1000000;i++){bulk.find({a:i}).remove();}
bulk.execute();

Has anyone done tweaking around bulk removal ?

I've only one option to drop the complete collection and I don't want to do this.


texts: How can I generate TPC-DS queries for PostgreSQL?

texts:
I'm using TPC-DS to run some benchmarks on a few versions of postgres that I have. I noticed that there is no template for generating the TPC-DS queries with the target database as postgres. The query_templates directory contains template files for ansi, sqlserver, db2, oracle, and netezza, but none for postgres.

I've tried running the queries generated by each of the previously listed targets on postgres, but they all seem to have some kind of syntax that postgres doesn't support, because most of them fail to run.

Is it possible to generate these TPC-DS queries for postgres? Or is there a postgres.tpl file that can be downloaded somewhere?


texts: Does this query always correctly retrieve the types of array parameters

texts:
The following query seems to work, but I am not sure that it is the best/always correct way:

select
parameter_name,
CASE WHEN data_type='ARRAY'
    THEN SUBSTRING(udt_name, 2)||'[]'
    ELSE data_type
END AS data_type
from information_schema.parameters
where specific_schema='my_schema'
and parameter_mode='IN'
and specific_name like 'my_proc_16831'
order by ordinal_position

Anyway, it looks kind of hacky to me. Is there a better way to retrieve, for example, numeric[] as a type if my parameter is an array of numeric?


texts: MySQL crashes daily on VPS with Ubuntu Server 12.04 - How can I find the error or conflict?

texts:
Every day for the past four days, my website server crashes at least once. (Error establishing database connection.

As long as I catch it, I simply have to do the following command:

sudo /etc/init.d/mysql start

and its up and running in about 30 seconds.

I imagine this is not enough info to diagnose, but I am sure there is an error log or something I can pull which would be more useful.

It is running on a single core w/ 512 mb memory. The whole site is funneled through CloudFlare, so even at peak traffic it never really sees an average load above 0.25 when checking htop.

One random thought - I have an embedded twitter feed that shows latest tweets. It happened today immediately after the tweet was posted. I have no idea if there is a connection and didn't pay attention the last few days when tweeting. Just thought I would throw it out there in case someone knows of a conflict.

Any ideas?

Thank you.


texts:
I have an Operations server running Windows Server 2012R2 and SQL Server 2014 Enterprise back end.  This server is used to deploy new code to other production servers via cmd file called by a SQL Agent Job.  All of the servers are on the same domain.

Server A runs Windows Server 2008R2 and SQL Server 2008R2 back end.  This server has a linked server connecting to a database on Server B, running the same OS and SQL Server version as A.  The linked server is configured with option @useself=TRUE.

All three servers use a service account enabled for delegation.
This service account has sa priveleges on all three SQL Server instances.
All three servers have SPNs configured with that account and are delegated to use Kerberos for the associated MSSQLSVC services.

I can run the following on each and "Kerberos" is returned

    SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

In addition I can telnet, ping, etc. from any of those servers to any other server without issue - everything is connected.  Code deployments have never had a problem and the linked server is referenced often and without issue...except for one scenario and I don't understand why.

Double Hop Scenario

A SQL Agent job on the Operations server is run adhoc by any sysadmin other than the SQL Agent service account and executes a cmd file, also on the Operations server.
The SQL Agent job is configured to "Run As" a SQL Agent service account, having sa priveleges.
The code that is deployed comes from a .sql file on the Operations server.
The cmd file calls SQLCMD to execute the code in the .sql file against Server A.
The code in the .sql file references the linked server and fails with error

*Msg 18456, Level 14, State 1, Server ServerB, Line 1  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. *.

The error is directed at the linked server - Server B.  If I run this code manually, using SQLCMD, from the Operations server against Server A, it runs fine.  If I logon to a box as the SQL Agent service account and run the SQL Agent job, it runs fine.  It's only when the SQL Agent job is executed by someone other than the SQL Agent service account that I get the error.

I've read through so many posts and blogs and MSDN articles regarding Kerberos, double hop, etc telling me to do what has already been done.  What am I missing?

Additional Info
I've finally been able to come back to this and located some additional info.  Using Bogdan's advice, I ran Process Explorer and verified the credentials for the first hop are from the SQL Agent Service account as expected and that TCP is being used.  Alas, that is all the useful info I was able to get out of the tool.

I dug into the Window's application logs and dug around for login information for the different instances on the machines and noticed that Kerberos isn't even being used!!!  Instead NTLM is being used.

So that's the new path I'm heading down - why is NTLM authentication being used, when Kerberos is set up and proper FQDN SDNs exist by port and instance?  Do I need to somehow specify authentication type in the cmd file or SQLCMD call?  Or do I have something misconfigured that I'm not thinking about?

The Mystery Deepens
The SQLCMD call referenced the "first hop" server via Alias.  I modified the SQLCMD server reference to the actual named instance and reran the job.  It worked!  We also use cNames for our machines and SQL instances and so I tried using that.  It also worked!  For grins and giggles I re-tried the alias...it worked???  I go check the Windows applications logs for each of these and it is still reporting authentication as NTLM!

I am completely baffled at this point and at a loss as to how to explain this behavior to fix the rest of our environment.


texts: MySQL converting column of type TEXT to TINYTEXT truncates value at non-intuitive position, why?

texts:
I recently ran into a problem where a table had a column of type TEXT inadvertently converted to type TINYTEXT (MySQL version 5.6.24). We were able to restore any truncated data, but the confusing thing was was that we only noticed the problem when certain values for the column started appearing to be truncated at seemingly random positions in the text string.

After trying to wrap my head around what happened I finally realized that each value originally >= 256 chars was truncated to a length equal to the difference between its original length and the nearest lower multiple of 256. Eg:

Original Length Truncated Length Difference
257             1                256
350             94               256
597             85               512

I then confirmed this is exactly what happens when you convert a TEXT column to TINYTEXT. It's as if it iteratively removes chunks of 256 bytes until the total length is < 256. But why wouldn't it simply just truncate the value to 255 bytes to begin with? Seems like a more efficient operation, but I have no idea about how the internals work.

I couldn't find an explanation on the MySQL documentation. I mean, either way if you have values that exceed the max length you're going to lose data; obviously one wouldn't do this on purpose, but figuring out what happened would have been a bit less confusing if all of the truncated values had length 255.

EDIT: I confirmed this behavior for both the MyISAM and InnoDB storage engines.

EDIT AGAIN: Code to reproduce scenario:

create temporary table tmp (
    text_col text
)
collate = 'utf8_general_ci'
engine = InnoDB;

create temporary table results (
    current_value text,
    length_text_col int(11),
    char_length_text_col int(11),
    text_col_type char(8)
)
collate = 'utf8_general_ci'
engine = InnoDB;

insert into tmp
    (text_col)
values
    (repeat('a', 257)), -- 1 byte/character
    (repeat('ア', 86)); -- 3 bytes/character

insert into results
select
    text_col as current_value,
    length(text_col) as length_text_col,
    char_length(text_col) as char_length_text_col,
    'TEXT' as text_col_type
from
    tmp;

alter table tmp
change column text_col text_col tinytext;

insert into results
select
    text_col as current_value,
    length(text_col) as length_text_col,
    char_length(text_col) as char_length_text_col,
    'TINYTEXT' as text_col_type
from
    tmp;

select * from results;

The above code will select:

current_value  length_text_col  char_length_text_col text_col_type
aaaaaaaaaa...  257              257                  TEXT
アアアアア....  258              86                   TEXT
a              1                1                    TINYTEXT
               2                2                    TINYTEXT


texts:
I am developing an app where user will upload multiple images along with some other data like name address etc.

I am using MEAN stack for development, So I have some questions regarding this:

How should I store image data in my db, like what information should I save in my db ?
I have read that we can use GridFS by mongodb. But my images would be of max 5MB in size. So what should I use simple fs by node or gridfs by mongodb ?
What is the need of storing file data on amazon s3, can't I just save base64 string into my database ?

I have googled more and found out that storing images on storage is more beneficial than storing them directly into db. So I think I will go with s3, but then how can I refer to the images stored in s3 from the db ?

my app consist of 200 users and each user can upload upto 5 images and upto 5mb of size, and yes it is performance critical also.


texts: Backup MS SQL AG on a non-production environment

texts:
We've created a non-production environment for SQL Server.
This environment uses the SQL Server Always-On Availability Groups as a HA-solution.

To work with SQL Server AG's you have to set the recovery model to full so the log can be synced with the other replicas, but setting the recovery model to full also implies that the log keeps on growing if you don't take backups.

Since this is a non-production environment we're not that interested in the backups of these databases.
What do you guys think is the best way to go in this environment?

Create backups to disk and delete them
Do take backups of this environment
Other suggestions...


texts: Check and remove iteratively without an index

texts:
Without sync priority or performance requirements on the task, I need to check all documents once per day and removing some of them.

The find filter is not static but can be defined within a db engine script function like function check_doc(){ }.

With .find() - no filter/condition in brackets - the function will check some non-indexed fields and decides whether the document must be removed or not.

.find().check_doc()/forEach the most effient way for iteratively checking each document, one by one, and remove justOne from there?

Furthermore, is there a variable available for checking the mongodb status, whether it is locked or sync delay (because of other queries and tasks on the DB)? If so, I'd prefer to delay the check_doc which has lowest priority. However, so far I could not find a way to decrease priority or a flag to check the status/delay.


texts: How space is compared in where condition

texts:
I've come across below query where it is column being compared the space character.

SELECT  *
FROM    company
WHERE   emp_name > ' ' -- space char
AND     emp_id = 123
AND     active_flag = 'Y';

emp_name is VARCHAR2

Here WHERE emp_name > ' ', does it consider ASCII value of the space while comparing? Could someone please explain this.


texts: Recommending movies customers haven't rented yet based on their favorite genres, according to their rental history

texts:
Disclaimer

I do NOT want any explicit queries to my question. I want suggestions regarding how I can best approach this problem. I am eager to learn SQL by experimentation, but for my sake, I'd prefer not to have my hand held. Thanks!

Database/ER model

I am using the DVD Rental database found on the PostgreSQLTutorial website. Here's the ER diagram they've provided (the zip is provided on the site):

Preliminary problem leading up to question

For my own practice, I wanted to see if I could come up with a list of customers (customer) and the genres (film_category) from which they've rented (payment and rental) movies, as well as counts for each of those genres per customer. Here is the query I wrote, which works correctly:

SELECT customer.first_name || ' ' || customer.last_name AS customer, category.name AS movie_genre, COUNT(category.name) AS num_rented

FROM customer JOIN payment ON customer.customer_id = payment.customer_id
JOIN rental ON payment.rental_id = rental.rental_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film_category ON inventory.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id

GROUP BY customer, movie_genre
ORDER BY customer, num_rented DESC;

Problem

To challenge myself further, I want to see if I can list each customer's name along with the names of movies they have not yet rented that belong to the genre from which they've purchased the most movies (or the first one, if there's a tie). For example, here's a snapshot of some of the data I get when running the above query:

And here's a preview of the first 19 rows of the film table (not joined in the above query, but it will need to be joined for this problem):

So for example, we see that Aaron Selby really likes Travel movies, so we want to list her name alongside all movies belonging to the Travel category, but only those movies she has not yet rented.

Question

Could someone please offer some guidance/hints as to where I can begin? I know I'll need to join the film table, but here are some challenges:

limiting the output for each customer to only the most common genre they've rented
based on that information, retrieving all movies from films corresponding to that genre that the customer has not yet rented


texts: Transferring Mongodb database with rsync while working, is it okay?

texts:
I need to transfer MongoDB data from another server with rsync with MongoDB running. Making a dump is not an option for me, as it takes too much time.

What problems can I face doing it? Or, if this is not an option, how can I transfer data (var/lib/mongdobd) from one MongoDB instance to another without stopping it?

I use MongoDB version 3.2.11 in production (one instance,no replicas) and I need to transfer data from MongoDB version 3.4.9


texts: Customizing OEM notification title and body

texts:
I am writing a reporting tool that accepts email notifications for incedents , alerts and events coming from the OEM.

The tool parses the email title and body to extract information from it.

I was wondering if it is possible to customize the title and body of the email by including some string in it.

How can I use the OEM12c console settings to achieve this ??


texts: How should I best name my timestamp fields?

texts: When I am looking to create some timestamp fields (or other date/time style fields), what is the best way to name them? Should I just put record_timestamp?


texts: Item Index for all items in nested XML query

texts:
I have a requirement to build an XML package with a parent record, and 1..N related child records.

The child records are to be a nodes under the parent node, and there may be one child record or several for each parent.  It's also possible to have multiple parents in the same query.

The above query I have with no issue.

I also have a requirement to give each record (parent and child) a unique 0-based index.  I can do with with ROW_NUMBER for each subquery, but can't figure out a way to get a unique index for every record in the entire query.

Fiddle here with sample data and working query.

(Note, SQLFiddle does not display XML in a very easy to read format so you may want to run this in your local SSMS.)

Working SSMS code below:

BEGIN TRY
    DROP TABLE #Child
    DROP TABLE #Parent
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #Parent
    (RecId int PRIMARY KEY NOT NULL,
     PersonName varchar(100), Age int)
CREATE TABLE #Child
    (ChildID int identity PRIMARY KEY NOT NULL,
     ParentRecId int FOREIGN KEY REFERENCES #Parent(RecId),
     SalesAmt money)

INSERT INTO #Parent
  (RecID, PersonName, Age)
VALUES
  (1, 'Aaron Bertrand', 99),
  (2, 'Paul White', 20),
  (3, 'JNK', 33)

INSERT INTO #Child
  (ParentRecID, SalesAmt)
VALUES
  (1, 10.00),
  (1, 20.00),
  (2, 15.15),
  (2, 100.00),
  (3, 0.00)

SELECT
  RecId as 'RID',
  PersonName as 'PNAM',
  Age,
  (
    SELECT
      C.SalesAmt as 'SAMT',
      (ROW_NUMBER() OVER (ORDER BY ParentRecId) - 1) as 'Index'
    FROM
      #Child C
    WHERE
      C.ParentRecId = P.RecId
    FOR XML PATH ('ChildRec'), ROOT ('ChildRecs'), TYPE
  )
FROM
  #Parent P
FOR XML PATH ('Parent'), ROOT ('Parents'), TYPE

How can I get a unique index across all parent and child records for this XML package?


texts: Database table(s) to record investor activity

texts:
I've never asked a question here before.

I am currently designing my first database by learning from the web and some books I got, using MS Access to start. I work in operations for a small hedge fund with about 100 investors and I want to upgrade from scattered Excel sheets to a proper database. Initially I plan on using the database for managing investor relations and/or keeping track of money movements.

I think I have table design, keys, and normalization figured out. I created a table with beginning balances as of 1/31/2016. What is the best model for me to use to record future transactions? For example, an investor adding or withdrawing money, profits every month from trading, a list of future investor contributions, etc. I'm not finding answers online, because "transactions" apparently means logs in this world.

Is there a good resource out there or a standard design out there, so I don't have to re-invent the wheel?

Thanks in advance.

Edit: I see that my question is too vague.

I am attaching an example of my relationships.

Did I set up the Docs, Transaction Types, and Transactions correctly?

Thanks!


texts: How to find the counts of word occurrences in a column?

texts:
I have a column containing text in postgresql. Now I want to find, for a given input word, the counts of occurrences of the word in my text column.
So I have
  date_col   |  text_col
-------------+-----------------------
 2021-04-02  | This is a test.
-------------+---------------------
 2021-03-30  | A test is a test.
-------------+---------------------
 2021-03-30  | How to test?
-------------+---------------------
 2021-04-01  | One more test

and I want this result for the word 'test':
 count | num_occurrences
-------+-----------------
   3   |  1
-------+-----------------
   1   |  2

meaning 3 times there was excactly one occurrence of "test". Once there were two occurrences of "test" in the same row.
Later, I want to be able to query a given period with the same query.
My initial take was to create a new table with a row for every word like so:
  date_col  |  word
------------+------------------
 2021-04-02 |  This
------------+------------------
 2021-04-02 |  is
------------+------------------
 2021-04-02 |  a

and do some counting and grouping. But is there a better way?


texts:
We have an interesting problem around duplicate data.  Our source files can contain multiple rows of data for a single employee benefit.  As an example, a health care benefit could be split up and reported over 3 rows (3 is just an example - it could be many more).

The data is loaded into staging with no problems and will contain a mix of single and multiple benefit rows.  What I have done thus far is to perform aggregation to find identify with multiple benefits (aggregate on employeeid and benefitid) and if it is > 1, move it to a separate staging table.

What I now need to do is analyse the staging table with multiple benefits and aggregate the data based on employeeid and benefitid.  Let's assume a benefit has 3 rows.

The employeeid and benefitid will be the key to identify those rows.  Other rows may include the benefit provider, yearly cost, eligible & benefit name (and many more but these cover the 4 types of aggregation required to output a single row).

if there are multiple providers in the list of 3, we should set the value to "Multiple" otherwise it should be the distinct value
yearly cost is simple...SUM of all 3 rows
if any of the active flags are true, set the value to true (1 could be true and 2 could be false)
if there are multiple benefit names, we just select the 1st non null value.

After the data is aggregated (3 rows into 1, applying the rules above), it is then merged with the single row benefits table.


texts: MySQL: can deadlocks occur for a query not in a transaction

texts: As title says. If a transaction is locking table X, during which another nono-transactional query tries to insert a record into X, do deadlocks or "lock wait timeout" issues still occur?


texts: Mongo authentication user can't drop database

texts:
my question is - is it possible to define user in the Mongo database who will be able to drop the database? Maximum user privilege is "dbAdmin" but that privilege doesn't allow users to drop the database, only collections can be dropped.

I know that "clusterAdmin" has rights to drop the database, but that role can't be defined in regular database, only in admin database...


texts: Does row-based replication with "binlog_row_image=minimal" cause issues when ALTER TABLE reorders columns?

texts:
Just trying to get my head around the caveats of using binlow_row_image=minimal with row-based replication in MySQL 8.0.23... To avoid silent data drift, the requirements seem to be that:

All tables need a primary key in both databases, which is identical for each pair of tables.
All tables need to have the same number and type of columns, in the correct order, in both databases.

Given the above, if an ALTER TABLE for example deleted a column, could you ever have a race condition where replication of pending binlog events which insert rows into that table would break? Would this only happen if you used something like slave_parallel_workers, or would even that be safe?
I'm trying to figure out if this is worth using or if it's more performance tweak when you don't require hard data consistency.


texts:
I have replication with PostgreSQL, so I want to set archive_command = 'cp %p /var/lib/postgresql/11/main/archive/%f' but I also want to save WAL files to another safe place: S3.

So is it possible to have multiple archive_commands? And can I put this command on the slave to push wal files to S3 instead of on the master - I want master to have less performance impact.


texts: Master-slave replication doesn't work on PostgreSQL 9.6

texts:
I tried to create a master/slave configuration with two servers and Postgres 9.6

I modified master and slave postgresql.conf in /etc/postgresql/9.6/main
Same for master and slave:

data_directory = '/srv/postgresql' # Because we wanted to store data elsewhere
wal_level = replica
synchronous_commit = local
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave001'

master-specific :

listen_addresses = ' <slave_ip>, localhost'

slave-specific :

listen_addresses = ' <master_ip>, localhost'

I then added a line on pg_hba.conf on master :

host    replication     rep     <slave_ip>/32  md5

And same on slave :

host    replication     rep     <master_ip>/32  md5

And then I restarted each postgresql services

I created a role rep on master as you can see below :

I removed existing /var/lib/postgresql/9.6/main repository on slave and then executed those commands :

service postgresql stop
pg_basebackup -D /var/lib/postgresql/9.6/main -h <master_ip> -U rep

And got this answer:

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

I then created a recovery.conf file on /var/lib/postgresql/9.6/main on slave

standby_mode = 'on'
primary_conninfo = 'host=<master_ip> port=5432 user=rep
password=<rep_password> application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

I then restarted slave postgresql and got those logs:

LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
[unknown]@[unknown] LOG:  incomplete startup packet

Last one is a bit strange but it seems to be 'normal'

but replication seems not to work, as if I create a table on master, it's not replicated

And command :
    psql -x -c "select * from pg_stat_replication;"

Gives me (0 rows)

I followed many tutorials but every time it's the same result.
My master db got some data (around 10 Go), maybe it's due to that?
What step did I miss? Is it due to an odd interaction with the fact that my data_dir is on /srv ?

My servers are on debian 9

Thanks for help, and don't hesitate to ask me for more details if needed! As I'm a beginner with Postgres, maybe I missed important informations


texts:
I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this?

(ideally returning a suitable error message if any change is attempted).

I dont have grant privileges, so i would like to understand how to write BEFORE UPDATE trigger that raises an error (using signals or issuing a statement that always fails).


texts: Restore A Live Database to Test Database containing defferent Names using SQL Server Management Studio

texts:
I have a test database with a name say TestDB and  A production/Live Database say LiveDB running on the same server.
What I want is to restore the production/Live database to a TestDB.
Below is the error I am getting;

Thanks for your help in advance.


texts: How can I correctly use the timezone format specifier in a TO_DATE call in Oracle

texts:
I am trying to obtain a DATE object with the following statement:

TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD HH24:MI:SS TZR')

and

TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD TZH:TZM:SS TZR')

But in both cases, it is telling me: ORA-01821: date format not recognized.

I got the TZR format specifier from https://www.techonthenet.com/oracle/functions/to_date.php, namely:

TZD     Daylight savings information. For example, 'PST'
TZH     Time zone hour.
TZM     Time zone minute.
TZR     Time zone region.

What is the correct way to specify a date/time with a time-zone?


texts: MySQL Master-Slave Replication additional columns on slave are being reset to their default value

texts:
I have setup a master-slave replication in MySql. I have added additional columns to one of the tables in the slave database in order to store some additional information, which should not be an issue, since it is inline with the following rules: https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

Now the issue that I am facing is that when I fill these additional columns, the data I fill them with is being lost once replication happens. By lost I mean that the data in these additional columns are reset to their default value.

Should this happen? I have not found anywhere where this behavior is documented and I can't understand why this is happening.

Does anybody can shed any light on this?

Thank you very much.


texts: How to insert json into a table and have certain columns remain json

texts:
I want to insert data into a table where the first level keys are inserted as values and not as json. Although column "p_attribute" needs to store the nested json. For example, in case of Jane Doe, I want her attribute column to look like this:

I can only achieve this by updating the column p_attribute with:

update json_table
set p_attribute =(
    '{
    "p_attribute": {
    "age": "37",
    "eye_color": "blue",
    "favourite_qoute": "I am the classic example"
    }
    }'
)

Now I want to create the above picture in one insert, like this:

insert into json_table
select * from json_populate_recordset (NULL::json_table,
    '[{
    "p_id": 1,
    "first_name":   "Jane",
    "last_name":    "Doe",
    "p_attributes": {
        "age": "37",
        "eye_color": "blue",
        "favourite_qoute": "I am the classic example"
        }
    }]';

But I execute this insert then the p_attribute column gets a null but the rest of the columns are correctly inserted:

Im using PostgreSQL 12 and my table looks like this:

create table json_table (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute json
)


texts: trigger create new database after update with old and new variables dbForge

texts: i want to create new database with old and new data by type as: id_old, id_new. how to create trigger maybe whose creatig this later? sorry for my stupidity.


texts:
When are the values for computed columns determined?

When the value is retrieved?
When the value is changed?
Some other time?

I'm guessing this is a novice question since I'm not finding anything in my searches.


texts:
I am trying to automate some things on DB2 and would like to use a batch file to do this. I create the following batch file test.bat:

db2cmd - -w "db2 -tvf test.db2"

I then try to do two commands in the test.db2 script, which work together in the CLP interactive mode:

!set db2instance=INST1;
!db2pd -db MYDB -hadr;

I then get the following output:

  Database MYDB not activated on database partition 0.
  Option -hadr requires -db  or -alldbs option and active database.

I'm using DB2 9.7 LUW on Windows 2008 R2.


texts:
I want to compare two table in SQL server and find difference.

I want Warehouse and Product combination and see the difference. I am expecting to combine and display both table together and write Null if same data is not present in other table.

For Ex:

New Jercy and IPHONE from both table and see what is difference in model and quantity.
New Jercy and IPHONE combination and comapare with San Jose and Samsung and see the difference.
San Jose and Samsung combination and compare with TableB having San Jose and Samsung.

Can anyone help me on this.

Thank you!


texts: What Mechanism does MySQL use to schedule Backups on Windows Server 2008?

texts:
When scheduling database backups on MySQL Server (v5.1.45-community) running on Windows Server 2008, what mechanism is used to trigger the backups to run?  I think it's using the Windows Task Scheduler, but I don't see any obvious tasks for these backups.  SQL Server uses a separate agent service. Is there something similar in MySQL?

ADDITIONAL INFO: The backups were setup with MySQL Administrator.  I'm aware of how to schedule the backups, but they're not running and I'm looking for insight as to where to begin trouble shooting.


texts: Can the current search_path be used in a query?

texts:
I'd like to do something like this:

SELECT *
FROM information_schema.tables
WHERE table_schema IN (search_path)

I couldn't find anything in the docs. Is it possible? How?


texts:
How can I count all decimal place up to the first zero

For example I have a column named x with an entry of numeric datatype value 1.955000000

I want to count the decimal places without the zeros. So here I want the query to make an output of 3 not 9 (with zeros)


texts: How to see unclosed PostgreSQL connection while using psycopg2?

texts:
I run python code:

con = psycopg2.connect(some_dsn)
cur = con.cursor()
cur.execute(some_sql)
res = cur.fetchone()
#con.close() <-- connection is not closed

Then I try:

ps auxf | grep postgres

or

SELECT * FROM pg_stat_activity

both does not display correspond information.
How to see that unclosed connection?


texts:
Schema

I have the following set-up in MySQL database:

CREATE TABLE items (
  id SERIAL,
  name VARCHAR(100),
  group_id INT,
  price DECIMAL(10,2),
  KEY items_group_id_idx (group_id),
  PRIMARY KEY (id)
);

INSERT INTO items VALUES
(1, 'Item A', NULL, 10),
(2, 'Item B', NULL, 20),
(3, 'Item C', NULL, 30),
(4, 'Item D', 1,    40),
(5, 'Item E', 2,    50),
(6, 'Item F', 2,    60),
(7, 'Item G', 2,    70);

Problem

I need to select:

All items with group_id that has NULL value, and
One item from each group identified by group_id having the lowest price.

Expected results

+----+--------+----------+-------+
| id | name   | group_id | price |
+----+--------+----------+-------+
|  1 | Item A |     NULL | 10.00 |
|  2 | Item B |     NULL | 20.00 |
|  3 | Item C |     NULL | 30.00 |
|  4 | Item D |        1 | 40.00 |
|  5 | Item E |        2 | 50.00 |
+----+--------+----------+-------+

Possible solution 1

Two queries with UNION ALL:

SELECT id, name, group_id, price FROM items
WHERE group_id IS NULL
UNION ALL
SELECT id, name, MIN(price) FROM items
WHERE group_id IS NOT NULL
GROUP BY group_id;

/* EXPLAIN */
+----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
| id | select_type  | table      | type | possible_keys      | key                | key_len | ref   | rows | Extra                                        |
+----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
|  1 | PRIMARY      | items      | ref  | items_group_id_idx | items_group_id_idx | 5       | const |    3 | Using where                                  |
|  2 | UNION        | items      | ALL  | items_group_id_idx | NULL               | NULL    | NULL  |    7 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL               | NULL               | NULL    | NULL  | NULL |                                              |
+----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+

However it is undesirable to have two queries since there will be more complex condition in WHERE clause and I would need to sort the final results.

Possible solution 2

GROUP BY on expression (reference)

SELECT id, name, group_id, MIN(price) FROM items
GROUP BY CASE WHEN group_id IS NOT NULL THEN group_id ELSE RAND() END;

/* EXPLAIN */
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | items | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

Solution 2 seems to be faster and simple to use but I'm wondering whether there is a better approach in terms of performance.

Update: According to MySQL documentation, this query is illegal in SQL92 and earlier and may work in MySQL only.


texts: Data inconsistency prohibition if a table refers to another via two many-to-many relationships

texts:
I have following database design (by => foreign key constraints are depicted):

Company[id]
CompanyRealm[id, company_id=>Company.id]many2many

Project[id, company_id=>Company.id]
ProjectRealm[id, project_id=>Project.id, company_realm_id=>CompanyRealm.id]many2many

Problem is that provided database design allows inconsistent data. For example:

Company1(id=1)
Company2(id=2)
CompanyRealm(id=11, company_id=1)
Project(id=33, company_id=2)
ProjectRealm(id=44, project_id=33, company_realm_id=11)

(Company can have many Realms, Project belong to Company and  may concern any number of company's Realms)

ProjectRealm refers to two different companies:

Company1 (via CompanyRealm); and
Company2 (via Project).

Is something wrong with my DB design?
If yes - which prescriptions are violated?
If no - how to prevent incorrect data insertion (via constraint? trigger with exception?)


texts: MySQL / MyISAM: Queries sometimes very slow, no waiting for lock time

texts:
I have a web application using PHP 7.0 and MySQL 5.5.54-0+deb8u1-log.

Occasionally, there are long waiting times in the application, which seem to be related to slow database queries. In the slow query log of MySQL, I can find some queries which have been executed unusually slow, though they normally are finished in milliseconds. I use MyISAM which is obviously prone for waiting for locks, but - what's strange is that the queries do not have to wait for locks at all.

Of course you can argue that the query is not optimized and all - regarding multiple joins, sorting, union all etc. But if I run the following example in the database while the application is on its load peak, it usually runs just nice and quick!

Here one example:

# Query_time: 31.449621  Lock_time: 0.000284 Rows_sent: 22  Rows_examined: 131
SET timestamp=1494921515;
            (SELECT
                table1.*,
                table2.userName,
                table2.userId,
                table2.userPictureId,
                table3.groupName,
                table3.viewOption,
                table3.force_private
            FROM table4
            INNER JOIN table1
                ON table4.userId1='192203'
                AND table4.userId2=table1.userId
            INNER JOIN table3
                ON table1.groupId=table3.groupId
                AND table3.deleted=0
            INNER JOIN table2
                ON table1.userId=table2.userId
                AND table2.class IN ('A','B')
                AND NOT table1.actionId='20'
            ORDER BY date DESC LIMIT 0,50)

            UNION ALL

            (SELECT
                table1.*,
                table2.userName,
                table2.userId,
                table2.userPictureId,
                table3.groupName,
                table3.viewOption,
                table3.force_private
            FROM table1
            INNER JOIN table3
                ON table1.groupId=table3.groupId
                AND table3.deleted=0
            INNER JOIN table2
                ON table1.userId=table2.userId
                AND table2.userId='192203'
                AND NOT table1.actionId='20'
            ORDER BY date DESC LIMIT 0,50)

            ORDER BY date DESC LIMIT 0,100;

So - what I'm looking for is not a way to make this specific query faster. Of course it wouldn't hurt, but normally this specific query is finished in about 0,01s!

If this occurs, then there are usually some more normally fast queries which are executed slowly. I see it in the slow query log.

One observation is that the execution time seems always to be around 30 seconds - and the time limit for PHP scripts is 30 seconds in my case, so I wonder if there is any relation. If all related MySQL queries are automatically canceled when a PHP script is killed, then this would explain it. By the way, I use PDO to connect PHP to MySQL.

One idea I had is that maybe the actual query execution time is not so bad, but maybe MySQL has issues while trying to hand over the resultset to PHP - for an unknown reason. But on the other hand, I'm not sure if this waiting time would be added to the query execution time in the slow query log! Does anybody know? What else could be the problem?


texts: Should log tables be "normalized" or store snapshots?

texts:
Say we have the following structure:

USER
---
USERNAME

CONTACT
---
ID
FIRSTPHONE[FK TO PHONE.ID]
SECONDPHONE[FK TO PHONE.ID]

PHONE
---
ID
NUMBER

...Now, when creating an log table logging when a user calls a contact, should it store PHONENUMBER or NUMBER?
Option A:

LOG
---
USERUSERNAME
CONTACTID
PHONEID[FK TO PHONE.ID]

The argument against this is that if a change is made to PHONE.NUMBER, then the log itself will change; basically rewriting history.

Option B:

LOG
---
USERUSERNAME
CONTACTID
PHONENUMBER [Not a foreign key]

The argument against NUMBER this is that it's "denormalized".

So, which approach is better?


texts: SQL Server Full-Text Index rebuild causing latency on other indexes

texts: I am rebuilding a full-text index on a table with a large number of rows.  The rebuild seems to be using all of the resources of the FT GATHERER threads, causing all of the other full-text indexes on the instance to report large latency.  Is there some way to throttle the resources that the rebuild is using so that the other indexes can keep up?


texts:
I'm currently building a database and adding my foreign keys constraint on phpMyAdmin but I'm struggling with one of them.
Indeed, I've looked on the internet for many possible solutions but any of them was working for me.

The fact is that I'm trying to add a foreign key contraint in my CUSTOM_TRANSLATION table on the AUTHOR, referring to the NAME attribute in my USER table with a CASCADE effect ON UPDATE and ON DELETE.
Type matches and my indexes are set, I still get a #1215 error (cannot add foreign key constraint).

I didn't pasted the whole code because I don't think it's really usefull, just pasted the interesting part, I guess.

Thank you for your time and help, fellas :)

CREATE TABLE `USER` (
  `ID` tinyint(3) UNSIGNED ZEROFILL NOT NULL,
  `NAME` varchar(20) NOT NULL,
  `MAIL` varchar(50) NOT NULL,
  `PASSWORD` varchar(150) NOT NULL,
  `RANK` enum('Admin','Translator','ViewerSDTV','ViewerCustomer','Customer','TranslatorRestricted') NOT NULL,
  `SOCIETY` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `CUSTOM_TRANSLATION` (
  `ID` smallint(5) UNSIGNED ZEROFILL NOT NULL,
  `TRANSLATION_ID` smallint(5) UNSIGNED ZEROFILL NOT NULL,
  `MESSAGE_ID` smallint(5) UNSIGNED ZEROFILL NOT NULL,
  `TRANSLATED_MESSAGE` varchar(80) NOT NULL,
  `VERSION` decimal(3,2) UNSIGNED ZEROFILL NOT NULL,
  `AUTHOR` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `USER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `MAIL` (`MAIL`),
  ADD KEY `NAME_LINK` (`NAME`(10)) USING BTREE;


texts: Converting standard_conforming_strings from off to on when importing Postgres dump

texts:
I've got a Postgres 8.1 instance which has standard_conforming_strings set to off (== handles slashes as escape characters). Recent Postgres versions have this setting on by default (for good reasons).

When importing a dump from 8.1 to 9.x, then I can set this setting to off in the 9.x DB but I don't want to, I'd prefer converting the dump into the proper format. This is a ~100GB dump.

How could I do that? Can I simply turn this setting 'on' in the 8.1 version? (Will that affect exporting existing data? Will/can that corrupt or modify existing data?)

What doesn't work: simply importing data produced by 8.1 to 9.x without setting standard_conforming_strings to off in the 9.x DB; the result is corrupted data.


texts: Send Keyboard Interrupt in PostreSQL psql console?

texts:
Is it possible to send a keyboard interrupt signal to the PostgreSQL psql console that will take me back to a new console command line?  Sometimes I'll type a command incorrectly and then hit enter and the console will just sit there.  If I hit ^c or ^d to try to interrupt the command, this will end the console session and I have to re-start the session which can be a pain if I had a lot of things going on in that session.

Thanks.


texts: Fastest way to copy data from MyISAM to InnoDB

texts:
I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.

However, every approach I try leads to a slow result.

#1 INSERT

Using a simple INSERT with VALUES from a SELECT:

INSERT INTO new_table (field1,field2,...) SELECT * FROM old_table;

Really slow and it locks the original table, which I can't have locked for much longer.

#2 IMPORT FROM SQL FILE

Creating the SQL file takes minutes, but ok. Importing the file is really slow.

#3 LOAD DATA

I dump all the data to a comma separate file, which takes about 30 seconds (quite good), and then I load it with LOAD DATA INFILE. But still very slow.

Why not drop unique keys before loading the data?

Yes, it loads faster. However, when I try to add the unique keys I get this:

  Lock wait timeout exceeded; try restarting transaction

Let's split the into several files?

I tried that. Each file contained 100,000 rows.

File 1 = 2.76 sec
File 2 = 1 min 40 sec
File 3 = 4 min 16 sec

I gave up on file 3. I'd have to do it 157 more times and on each iteration the processing time would increase. Too much time...

What is 'slow'

When I say it is slow, I mean, with unique keys off, it takes about 10 seconds to load 500,000 rows. No bad. But then I can't add unique keys because of the timeout.

With unique keys previously set, it takes almost 1 minute to insert 100,000 rows. And the throughput decreases the more rows is inserted.

The tables

MyISAM - Original table

+----------------------+-----------------------+------+-----+---------+-------+
| Field                | Type                  | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| player_id            | int(11)               | NO   | PRI | NULL    |       |
| date                 | date                  | NO   | PRI | NULL    |       |
| time                 | int(2)                | NO   | PRI | NULL    |       |
| minutes_online       | decimal(5,0)          | NO   |     | 0       |       |
| minutes_playing      | decimal(5,0)          | NO   |     | 0       |       |
| minutes_chatting     | decimal(5,0)          | NO   |     | 0       |       |
| minutes_away         | decimal(5,0)          | NO   |     | 0       |       |
+----------------------+-----------------------+------+-----+---------+-------+

InnoDB - New table

+----------------------+-----------------------+------+-----+---------+-------+
| Field                | Type                  | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| player_id            | mediumint(8) unsigned | NO   | PRI | NULL    |       |
| date                 | date                  | NO   | PRI | NULL    |       |
| time                 | tinyint(2)            | NO   | PRI | NULL    |       |
| minutes_online       | tinyint(2)            | NO   |     | 0       |       |
| minutes_playing      | tinyint(2)            | NO   |     | 0       |       |
| minutes_chatting     | tinyint(2)            | NO   |     | 0       |       |
| minutes_away         | tinyint(2)            | NO   |     | 0       |       |
+----------------------+-----------------------+------+-----+---------+-------+

Am I stuck on this? Meaning, will I have to accept the fact that it is slow and be patient? Or is there any way or tweak to load the data with an acceptable performance?

Solution
I split into small CSV files, applied the tweaks that Shlomi Noach suggested and then loaded each file.

Not the perfect. But it loaded a bit faster. Dumping the original table to a CSV file took less than 1 minute, which avoided the table from being locked during the whole process. A script did the job to load each file, which would be very boring to do manually.

In the end I just had to get from the original table the rows inserted/updated in the last hours and update on the new table.


texts:
I'm using PL/SQL Developer v9, brio intelligence 6.6 and on another computer SQL Developer v7. On version 7 everything is ok. My table objects are 'expanding' shows all avaiable databases.

In version 9 object does not develop - as if it were empty. When I use BRIO all databases are avaiable...

So there is some problem with PL/SQL Developer but I can't figure it out ;\


texts:
I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column)

I want to PIVOT that query result so that it uses the latest 'ReceivedTime' for those same filenames. And the Outcome values should be split into separate columns - with a COUNT of each occurrence.

Here is my code so far. However, this returns multiple rows rather than grouping by filename.
What do I need to change?

DECLARE @TmpTable AS TABLE
(
    Filename varchar(80),
    ReceivedTime datetime,
    Outcome int NULL
);
INSERT INTO @TmpTable
VALUES
    ('FileABC','2018/06/29 03:34', 1),
    ('FileABC','2018/06/28 11:01', 3),
    ('FileABC','2018/06/28 09:30', 2),
    ('FileABC','2018/06/28 05:23', NULL),
    ('FileABC','2018/06/28 16:21', 3);

SELECT
     Filename as 'LatestReceivedTime'
    ,[-1] AS 'NotProcessing'
    ,[0] AS 'InProgress'
    ,[2] AS 'Warning'
    ,[3] AS 'Failed'
    ,[1] AS 'Success'
FROM
(
    SELECT x.Filename, x.ReceivedTime, COALESCE(x.Outcome, -1) AS 'Outcome'
    FROM @TmpTable x

) x
PIVOT
(
COUNT(Outcome)
FOR Outcome IN ([-1], [0], [1], [2], [3], [4])
) AS PivotTable;


texts:
I'm joining a small table (1,000 rows) against a large table (8M rows) in SQL Server 2008. The join uses a nonclustered covering index on the large table, and the join can produce three possible query plans. I'm trying to figure out which plan is better, but also I want to generalize this knowledge so next time I can better know what heuristics to use when looking at SQL I/O statistics.

Plan #1 is a loop join and emits statistics for the large table like this:

Scan count 2582, logical reads 35686, physical reads 1041, read-ahead reads 23052

Plan #2 is a merge join and emits statistics like this:

Scan count 1, logical reads 59034, physical reads 49, read-ahead reads 59004

Plan #3 is a hash join and emits statistics like this:

Scan count 3, logical reads 59011, physical reads 5, read-ahead reads 59010

The covering index is ordered by (ID, Date). The query returns data for about 50% of the IDs and, for each ID, returns a contiguous chunk of the most recent 3 months of data, which is usually about 1/4 or the rows for each ID.  The query returns about 1/8 of the total rows in the index.  In other words, the query is sparse but consistently so.

My assumption is that plan #1 is awful for this workload, because moving the disk head around 2,500 times (or even 1,041 times) is far more expensive than a sequential disk scan.  I also assume that #3 and #2 have similar, sequential (and therefore more efficient) I/O patterns.

But is there a case where plan #1 is really best, where "best" means less impact on the I/O subsystem and less impact on other queries running concurrently?

Or does it really depend on many variables like the kind of disk subsystem I have, index fragmentation, etc. If "it depends" are there any rules of thumb to approach the problem?


texts: Performance difference between searching table with 350 columns and searching table with 25 columns

texts:
I am currently doing some hibernate performance test. My requirement is to search for data in a particular column of a table containing 8 million rows and 350 columns. Currently, I don't have the access to that database, however, the test database that I'm now using also contains 8millions+.

My question is, is there a significant difference in terms of scan speed when searching for a data in a table that containts 350 columns than in table containing 25 columns?

By the way, the way I check for data is :

sessionFactory.getCurrentSession().createSQLQuery("SELECT COL_5 from MY_TABLE where COL_5='" + theColValuePassedFromParameter+ "'").list();

Where in if the returned list is empty, the boolean method will return true, else false; I would also appreciate If someone can point me to a better, performant way of searching for a non-primary key data in hibernate.

Thanks in advance...


texts: Backup Database (SQL 2000 Compatibility) on SQL Server 2005 with CHECKSUM, but Page Verification TORN_PAGES Detection

texts:
I am trying to backup a 250GB database on SQL Server 2005 running in SQL 2000 compatibility mode.

The page verification is set to TORN_PAGES detection.
So, far the backup has been corrupted when we try to copy the backup to an external and restore on a different server.

The error I got in the past was as follows:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (0:0) in database "MMS.mdf" as read from the backup set. (Microsoft.SqlServer.Smo)

If I perform a backup with CHECKSUM enabled in the backup options, then does it look like the restore will work on the other server or is the database itself corrupt?


texts: How to list PDF Files names and path in a SQL Server Database

texts:
I need help on script to list PDF Files names and path in a SQL Server Database.

DECLARE @full_path VARCHAR(500)
SET @full_path = '\some\path\here'

DECLARE @FindFile TABLE
 (FileNames nvarchar(500)
  ,depth int
  ,isFile int)

INSERT INTO @FindFile
EXEC xp_DirTree @Path,1,1

SELECT FileNames from @FindFile where isFile=1

It didn't work, it only listed file names without the path?


texts: Stop rollback of anonymous DO code block?

texts: I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it began. How can I stop this functionality?


texts:
Assume I have the following DB table with 3 integer fields.

A | B | C
1 | 2 | 3
1 | 2 | 4
1 | 3 | 1
2 | 4 | 2
2 | 4 | 3

When I do:

SELECT * FROM dbTable ORDER BY A,B LIMIT 1;

I get

1 | 2 | 3

which is expected. But the 2nd record:

 1 | 2 | 4

also has the same values for dbFields A and B. Is there any efficient way to actually retrieve all records that have the same value as the top-k records? For example, when I search for the first 100 records to get instead 102 records if the last two have the same values as the 100th record? Is there any index to accelerate such queries? I do not mind if it has to be done with pl/pgsql (and not plain SQL) if the implementation is efficient.


texts: "Percona-XtraDB-Cluster prohibits use of ALTER command on a table () that resides in non-transactional storage engine"

texts:
I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via mysqldump --skip-locks -K and importing them via mysql <database> < <dumpfile> (after creating a blank database using CREATE DATABASE).
One database however is refused the import:

ERROR 1105 (HY000) at line 40: Percona-XtraDB-Cluster prohibits use of ALTER command on a table (example.example) that resides in non-transactional storage engine (except switching to transactional engine) with pxc_strict_mode = ENFORCING or MASTER

Most internet threads simply suggest temporarily disabling pxc_strict_mode. Given however that other databases import without any issues, I assume the issue should be resolvable by altering either the the flags appended to mysqldump, or modifying the statements inside the dumpfile. I attempted removing all ALTER statements in the dump, which changes the above error to ...prohibits the use of DML command....
Both, the source MariaDB instance, as well as the new XtraDB Cluster, are configured with the default InnoDB storage engine.
Would appreciate any input.


texts:
I already have system availability monitoring for all my MySQL instances in Nagios.  I'm looking to get some trending monitoring going for activity. It'd be nice to see how all the information_schema stats trend over time, to help identify sources of problems etc.

I've seen Cacti exists and seems pretty popular.

What have your experiences with Cacti been (pros/cons)?

Are there any other common solutions I should investigate?


texts: Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL

texts:
I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format.  I've stored them in a staging table in that same format, and now I would like to convert them into actual date fields in SQL Server, so I went hunting for integer-to-date conversion options, and came up with a variety of things involving CAST or CONVERT.

However, when I am testing a query in Squirrel-SQL (v3.9.1, using Java 1.8.221) using those conversion options with the date datatype (which seems appropriate since I don't have time values), the dates I get back are off by two days:

select cast('20200101' as date) ,
cast(left(20200101, 8) as date) ,
convert(date, cast(20200101 as char(10)), 112) ,
convert(date, convert(char(8), 20200101)) ,
convert(datetime, convert(char(8), 20200101)) ,
dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0) ,
convert(date,dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0))
/* results:
2019-12-30
2019-12-30
2019-12-30
2019-12-30
2020-01-01 00:00:00.0
2020-01-01 00:00:00.0
2019-12-30
*/

Note especially those last two, where the DateAdd expression explicitly produces the correct datetime, and then using Convert on it backs it up by two days again.

When I run the same query through SSMS, it produces the expected values.

I haven't found anything anywhere that offers to explain this behavior, so I am hoping that one of you can shed some light?

One other clue that may be relevant (but which has also produced no usable google results) is that SysDateTimeOffset() is not affected, but SysUTCDateTime() appears to be off by two days as well:

select cast(sysdatetimeoffset() as varchar(40)) as Sysdtoff, sysutcdatetime() as sysUTC
/* output (actual date should be 1/22)
2020-01-22 11:45:39.9878059 -06:00
2020-01-20 17:45:39.9878059
*/

The actual time on the server is correct, and it is set to the expected time zone (-6 hours), and my examination of Squirrel-SQL's settings doesn't turn up any date-related options.


texts: Table to create Ids for one table to many table relationships

texts:
I have several tables that will represent an object that can be commented on. I would like to store all the comments in one table. To relate the other tables to the comments, I was thinking of adding a CommentParentId column to each one. That column would need to be unique across all the tables that are related to the comments table, so I was thinking of creating a table, CommentParents, that would simply have an Id column in order to have the DB handle generating the Ids. For instance:

Comments

Id
Title
Body
User
Date
CommentParentId

Posts

Id
Title
Body
User
Date
CommentParentId

Images

Id
Title
User
File
Date
CommentParentId

Users

Id
Name
Email
DateJoined
CommentParentId

CommentParents

Id

So that table would simply be used to create new Ids. Is there another way to do this? What are the advantages?

I'm using SQL Server 2014


texts: Composite foreign key not verify all columns

texts:
Table1 (Num [PK NOT NULL], Dat [PK NOT NULL], ...)

Table2 (ID [PK], Num, Dat, ...)

I added this FK:

ALTER TABLE [dbo].[Table2]  WITH CHECK ADD FOREIGN KEY([Num], [Dat])
REFERENCES [dbo].[Table1] ([Num], [Dat])

But when I try to insert into Table2 the values [ Num = 1, Dat = NULL ], SQL don't give me an error and the values are inserted.

How I force SQL Server to check all columns of the foreign key?


texts: Can I add new tables to a transactional replication subscriber

texts:
I'm looking into using transactional replication in MS SQL Server 2016 to create a reporting database from our production HR system.

I'm wondering if it's possible to create new stored procedures and tables on the reporting database which will not be dropped as part of the replication sync?


texts: Is it still best practice to avoid using the default ports for SQL Server?

texts:
Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. On a server with a single, default instance, the following ports would be used by default:

SQL Server service - Port 1433 (TCP)
SQL Server Browser service - Port 1434 (UDP)
Dedicated Admin Connection - Port 1434 (TCP)

QUESTIONS:

Is this advice still relevant?
Should ALL of the above ports be changed?


texts: SQL Server Analysis Services - where do I store user logins?

texts:
If I would like to create a Business Intelligence (BI) system using SSAS, with SSRS getting data from the SSAS cube.

Which would be the best possible option to store users' credentials used with SSAS?

Active directory seems to be the only option, but unfortunately it creates the need for additional Active Directory licenses and therefore higher costs.

Is there any other solution?


texts:
I want to know if there is some thing similar to materialized view for Oracle in SQL Server .

Any ideas ?


texts:
I am trying to calculate the time since an event took place. The event timestamp will itself be the result of a MIN aggregate function. Is there a way to do this.
I have run a query to get the earliest date in my table which
SELECT MIN(date_column) FROM table

I also know that
AGE(timestamp '2015-10-5')

will give me the time elapsed since 5th Oct 2015. Is there a way to pass the result of the MIN query to the age function to find the length of time since that event?


texts: How to Split 24 hours into hour slab in Sql Server

texts:
'1-2 AM
2-3 AM
3-4 AM
4-5 AM
5-6 AM
6-7 AM
7-8 AM
8-9 AM
9-10 AM
10-11 AM
11-12 AM
12-1  PM
till
11-12 PM'

Total 24 row show.

Parameter is date like='07/06/2017'

what i am trying...

(convert(varchar,case when DATEPART(HOUR, START_TIME)%12<>0 then DATEPART(HOUR, START_TIME)%12 else 12 end) +' - '+ convert(varchar,DATEPART(HOUR, START_TIME)%12+1) + case when DATEPART(HOUR, START_TIME)>=12 then ' PM' else ' AM' end)

result is as per my needs but when any hours have no data then these hour not in output.
I want all hour slab whether that hour have data or not.

According to above output,there are shown only two hour's slot because only these hour's have a data.


texts: Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)?

texts:
We can use same logic for both types (string/bigint) to generate consecutive ids (e.g. like https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c). This way both are naturally sortable.
So, let's say we have a value 30722102503933550.
What is the real performance difference if we would leave it as bigint (30722102503933550::bigint) vs using as a string ("30722102503933550"::text) for a primary key.


texts: postgres fetch record from partitions without the check constraints

texts:
I am using postgres 9.4,
I have a messages table that has 100M records, messages belongs to one feed_id, so I decided to partition the tables by posted_at.

 Table "public.messages"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 id_str                       | character varying(255)      | not null
 feed_id                      | integer                     |
 replied_parent_id            | character varying(255)      |
 shared_parent_id             | character varying(255)      |
 message                      | character varying(255)      |
 posted_at                    | timestamp without time zone |
Triggers:
    messages_insert_trigger BEFORE INSERT ON messages FOR EACH ROW EXECUTE                              PROCEDURE create_messages_partition_and_insert()
Number of child tables: 231 (Use \d+ to list them.)
Child tables: messages_y2008_w45,
          messages_y2015_w01,
          messages_y2015_w04,
          messages_y2015_w03,
          messages_y2015_w02,
          messages_y2009_w23,
          ....

\d messages_y2008_w45
Table "public.messages_y2008_w45"
-- SAME AS messages
Indexes:
    "messages_y2008_w45_pkey" PRIMARY KEY, btree (id_str)
    "index_messages_y2008_w45_on_posted_at" btree (posted_at DESC)
Check constraints:
    "messages_y2008_w45_posted_at_check" CHECK (posted_at >= '2008-11-03 00:00:00'::timestamp without time zone AND posted_at < '2008-11-10 00:00:00'::timestamp without time zone)

Inherits: messages

There is a problem when I try to query parent messages. A parent message can have multiple child messages that posted at different date.
I fetch the child messages using the date range, and select the shared_parent_id from the child messages. Then I try to fetch the parents messages, but I don't have the date range for the parent messages, the parent messages can be outside the child messages' date range. What I can do is add a parent_posted_at in the messages table. But updating all the existing messages will take very long.
Also it's not ideal to add parent info in the child messages.

Is there any better way?

My question maybe more related to the design of the DB, should I use partition for this case or maybe partition differently?

EDIT:

Here's some example rows: I want to all 2015 May messages. So I selected [3,4,5,6]. But [5,6] are referring to [1,2].

To select the May messages, I have the date range, so it fits the check contraints, but after that I have to find the [1,2], the problem is I do not know the parent's posted_at, I only have the ids. So it will scan through all the partition tables. Maybe I should not partition by posted_at, instead I should partition by feed_id?

 id_str | feed_id | replied_parent_id | shared_parent_id |  message |      posted_at
--------+---------+-------------------+------------------+----------+---------------------
      1 |      26 |                   |                  | message1 | 2015-03-03 15:55:55
      2 |      26 |                   |                  | message2 | 2015-04-03 15:55:40
      3 |      26 |                   |                  | message3 | 2015-05-03 15:54:41
      4 |      26 |                   |                  | message4 | 2015-05-03 15:54:34
      5 |      26 |                   |                1 |          | 2015-05-03 15:54:15
      6 |      26 |                   |                2 |          | 2015-05-03 15:54:00

(6 rows)


texts:
I want to delete duplicate rows from a table. I have searched for the query online, and found the following sample:

DELETE
FROM dbo.LeadsContacts
WHERE Sr_No IN
(
    SELECT Sr_No
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC)
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

I don't understand the flow and working of the query.  Can somebody please explain it works?


texts: Take Tail-Log backup before restore option

texts:
Does the SQL server restore the tail-log that backed up after the restoration operation, if I selected the option "Take Tail-Log backup before restore"?

Thanks In Advance


texts:
I have a production deployment using local SSDs for tempDB. I have 2 SSDs in a RAID1 configuration. I am seeing average reads of 1-2ms but the average writes are showing as 1377ms on all four of my tempdb data files.

Each tempdb data file is 2GB with a 1GB growth setting (They haven't grown since deployment 5 months ago)

The tempdb log is showing average read 67ms and average write 215ms.

The SSDs are Samsung 840 pros.

The following code is what I use to get my stats

SELECT a.database_Id,
a.file_id,
db_name(a.database_id) AS dbname,
b.name,
db_file_type =  CASE
                WHEN a.file_id = 2 THEN 'Log'
                ELSE 'Data'
                END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location,
a.io_stall,
a.io_stall_read_ms / Case When a.num_of_reads = 0 Then 1 Else a.num_of_reads end AvgRead,
a.io_stall_write_ms / Case When a.num_of_writes = 0 Then 1 Else a.num_of_writes end AvgWrite,
Cast(Round(((( a.size_on_disk_bytes / 1024 ) / 1024.0 ) / 1024), 2) as float) AS size_on_disk_gb

FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id AND a.database_id = b.database_id

ORDER BY a.io_stall DESC

Below are the top 5 rows returned

database_Id file_id dbname  name    db_file_type    disk_location   io_stall    AvgRead AvgWrite    size_on_disk_gb
2   1   tempdb  tempdev Data    F:  19782846713 2   1377    2
2   3   tempdb  tempdev2    Data    F:  19782655021 2   1377    2
2   5   tempdb  tempdev4    Data    F:  19782364070 2   1377    2
2   4   tempdb  tempdev3    Data    F:  19782151571 2   1377    2
2   2   tempdb  templog Log F:  378829065   67  215 1

So my tmepdb files on SSDs are the slowest drives I have. Anything I should be looking at from a configuration/infrastructure point of view? I am currently studying the applications usage of tempdb and any memory spills but I'm not seeing anything terrible.


texts: ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES

texts: Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode?  I frequently add new filegroups/files to a 24/7 production database where killing active sessions and rolling back transactions (via SET SINGLE_USER WITH ROLLBACK IMMEDIATE) is definitely not preferred.  IMO this operation should be able to be accomplished with other active sessions on the database.


texts: Typeorm: How to make composite key in one table as foreign key in other table

texts:
I'm using Typeorm JavaScript not Typescript for PostgreSQL in Nodejs application. I have a two tables:

Resource
Passenger
In Resource Table I have id and type both are primary key and act
as composite key.
In Passenger Table I have Driver_Id and Driver_Type

I'm trying to make Resource Id and Type as foreign key to Driver_Id and Driver_Type. But when I do with Typeorm its create a table column like Driver_IDResourceID and DriverTypeResourceType.

I have tried almost everything mentioned in the Typeorm docs but nothing work for JavaScript version.

Any help will be appreciated.


texts: How to get identical values for different customers

texts:
I have a table which has different customers and each customer is having different insurance. say

Customer Id Bank Account
1           Test insurance 1
1           Test Insurance 2
1           Test Insurance 3
2           Test Insurance 4
2           Test insurance 5

I want to write a query to get below output

Customer Id   Insurance1            Insurance2              Insurance3 ---
1             Test insurance 1      Test Insurance 2        Test Insurance 3
2             Test Insurance 4      Test Insurance 5

Can anyone help me with query please.
Note: insurance for each customer is unique


texts: "could not write to log file, Bad file descriptor" error when working with huge table

texts:
I'm having a big problem with a PostreSQL database. Whenever I do something with a huge table (over 9 million rows) the whole postgres process crashes with the following entries in the log file:

PANIC:  could not write to log file 000000010000000000000022 at offset 11673600, length 909312: Bad file descriptor
LOG:  WAL writer process (PID 17056) exited with exit code 3
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
CONTEXT:  COPY fund_records, line 2088885: "..."
LOG:  PID 31820 in cancel request did not match any process
LOG:  all server processes terminated; reinitializing
FATAL:  pre-existing shared memory block is still in use
HINT:  Check if there are any old server processes still running, and terminate them.

Verbose log information for the "could not write to log file" error from version 8.4.22:

XLogWrite, .\src\backend\access\transam\xlog.c:1658

The CONTEXT is of course differnt depending on what I did. The above COPY command was logged when restoring a dump with the big table with pg_restore. Other occurrences were VACUUM (when I run vacuum on either the single big table or the whole database) or even SELECT (when I run a simple 1 row result select joining the big table).

I can reproduce this crash 100% by either running vacuum on the table or trying to restore the table data from a dump. Sometimes even simple things like selecting from the table or inserting into it cause the crash. It's always the could not write to log file x at offset y, length z: Bad file descriptor error. I've already tried disabling autovacuum during pg_resotre but it still fails.

This problem seems to be specific to one server, because the same table/dump works fine when I import it on my local machine. The affected server is a virtual (on Parallels Virtuozzo base) Windows Server 2008 SP2 x64 with 2 GB RAM. There is only a single HDD partition and nearly 40 GB of free space on it. Nothing other than PostgreSQL is running on the server, also no anti virus software. But PostgreSQL is the only application on this server that shows problems, all other operations I've tried work fine, so it seems like it's not a general problem with this machine.

I've tried PostgreSQL server versions 8.4.4 (this was running for years until it first showed the error), 8.4.22 and 9.4.4, all show the same error. I've tried both upgrading from 8.4.4 to 8.4.22 and fresh installs of 8.4.22 and 9.4.4 (there I've also completely deleted the data folder) multiple times. For the upgrade to 9.4.4 I've tried both pg_restore (crashes as already said above) and pg_upgrade. pg_upgrade works, but after that vacuum on the table in 9.4.4 still crashes.

Can someone please explain what's wrong here?

Update:

I've changed some values in postgresql.conf away from their default values and now the crash happens much rarer. Simple SQL and DML statements don't crash as often any more, but VACUUM still crashes 100%. I'm on version 8.4.22 right now. Here are the values I changed:

shared_buffers = 128MB
work_mem = 16MB
maintenance_work_mem = 64MB
wal_buffers = 4MB


texts: Why do we need to use proxies for syadmin job steps?

texts:
I read somewhere that for non-TSQL SQL Server Agent jobs, the job runs under SQL Server Service Agent account when the job owner is the sysadmin.

I just don't get why we need to use proxies even the owner of the step is a member of sysadmin fixed server role.


texts:
We're doing some testing and this involves adding 10.000 rows every 10seconds in a table that has 4 int columns and one of them is clustered index.
When we run the app the first bulkcopy takes around 1 minute to complete. The second takes 40 seconds to complete and after about 8 bulkcopy the time to complete the transaction is 1-2 seconds. Every transaction has 10.000 rows with random numbers. Is there anyway to prepare the database for fast bulkcopy from the first transaction?


texts: How do I change SQL Server 2012 Availability Group DNS subdomain?

texts:
Questions: how can I get my availability group to be registered as ag-ewgtest.sql.company.com given that my server thinks of itself as server.company.com.

Setup: my SQL Server  2012 availability group is on multiple subnets, similar to what's described at in this MSDN blog article. When I go into Failover Cluster Manager, it shows that name resolution is not yet available:

I then right-click "Name: ag-ewgtest" and choose properties. I check off "Publish PTR records" and it shows me that the full name is ag-ewgtest.company.com.

The problem I have is that I'm only being allowed to update the DNS zone sql.company.com. If I change the DNS name to be ag-ewgtest.sql.company.com then the full name becomes ag-ewgtest.sql.company.com.company.com.

I've tried updating my NIC as:

But hasn't made a difference. How can I change the subdomain that my availability group tries to register itself in?


texts: Excessive memory grants from indexed foreign key cascade delete

texts:
Each day, stores can enter sales information into our OLTP application.  The app calls a stored procedure in SQL to save this information.  Based on the users's activity, the application sends a code indicating whether the proc should perform an insert, update, or delete.

This save stored procedure is receiving memory grants of 60 GB for deletes to one row.  To duplicate the issue, I ran an ad-hoc delete query between a begin tran and rollback and captured the actual plan below:

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

The schema is like follows:

Daily_Item_Sales_Headers -- ~100 million rows on this system
========================
DlyItmSlsHdr_Key decimal(15,0) primary key nonclustered,
DlyItmSlsHdr_PaperworkBatch_Key decimal(15,0), -- FK to parent batch of data that contains other types of data
UK_DlyItmSlsHdr_PaperworkBatchKey_Key clustered, unique, unique key located on PRIMARY (DlyItmSlsHdr_PaperworkBatch_Key, DlyItmSlsHdr_Key)

Daily_Item_Sales -- ~790 million rows on this system
================
DlyItmSls_Key decimal(15,0) primary key nonclustered,
DlyItmSls_DlyItmSlsHdr_Key decimal(15,0), -- FK to header table, cascade delete,
UK_DlyItmSls_DlyItmSlsHdrKey_Key clustered unique constraint on (DlyItmSls_DlyItmSlsHdr_Key, DlyItmSls_Key)
[columns about sales data]

The query I ran is simple:

delete Daily_Item_Sales_Headers where DlyItmSlsHdr_Key = 1

The plan shows the header deletion is correctly cascading to the child sales rows.  The plan also shows an index seek on the clustered index of the child table.  However, this clustered index seek to the child table has an estimated 790 million rows.  The actual number of rows is ~100.  The high estimated rows is causing a memory grant of ~60 GB.

Using dbcc show_statistics on the child table indexes, I was able to see that the statistics were updated last night with a 2% sample size.  The histogram shows between 1 and ~33,000 rows estimated per parent key.  So the statistics appear to show the estimate should be much lower.

Why is this delete query generating such a large memory grant?

I saw this question about excessive sort memory grants that appear to be caused by a bug, but it looks different to me, because there are no sorts in this plan. Maybe it is the same bug applying to the table spools when cascading to the child table?

Excessive sort memory grant

Because of the foreign key cascading, I do not think I can work around the memory grant by deleting the child rows first before deleting the parent rows.  This is an OLTP system with up to 10,000 stores working at once, so I cannot drop the foreign keys on demand for a single delete.

EDIT 2/28/2019 1:13 PM CST

The SQL instance has about 400 GB memory allocated to it.

The application has the following trace flags enabled:

1222: deadlock tracing
4199: query processor fixes
2312: use 2014 cardinality estimator
2453: @Table variable cardinality

Disabling trace flags yields different estimates for the child table cascade delete:

Trace Flag 2312    Trace Flag 4199   Row Estimate
===============    ===============   =============
      on                 on            790 million rows
      on                 off           608 rows (very accurate)
      off                on            1 row
      off                off           1 row

Adding a querytraceon for the flag 9130 mentioned in the linked question makes no difference.

A coworker found this interesting article about a memory bug in SQL 2014.  The linked resolution was to add option (MAX_GRANT_PERCENT = 1) to the query.

https://www.theregister.co.uk/2016/02/09/microsoft_sql_server_2014_bug/

EDIT

The exact SQL Server version is SQL Server 2014 SP2 CU 12.

The database is compatibility level 110 - SQL Server 2012.  We are unable to change the compatibility level for a while.


texts: Did Microsoft change query optimizer regarding number of files and parallelism? Does the optimizer no longer factor in the number of files to determine the degree of parallelism for a query? If so, does anyone know when the change was made? If not, can anyone provide a link to Microsoft documentation that discusses the topic (current documentation for either SQL Server 2014 or 2016)?


texts: When does SQL Server follow the Minimum Password Age policy?

texts:
According to this SQL Server 2008 whitepaper, SQL Server should only pay attention to the Minimum Password Age policy when the Check_Expiration (Enforce Password Expiration) option is turned on.  Page 7 in the whitepaper specifically says "CHECK_EXPIRATION uses the minimum and maximum password age part of the Windows Server 2003 policy, and CHECK_POLICY uses the other policy settings".  However, that's not what I'm finding.  It looks like you only need Check_Policy (Enforce Password Policy) turned on.

For example, I have a SQL Login (using SQL Server authentication) with Enforce Password Policy turned on, but Enforce Password Expiration turned OFF.  My domain's password policy has Minimum Password Age set to 1.  I can use sp_password to change the password once, but when I try to change it a second time, I get this error:

  Server: Msg 15114, Level 16, State 1, Line 1
  Password validation failed. The password for the user is too recent to change.

Is the documentation in this whitepaper simply wrong?  Why is this happening when Check_Expiration is off?  I also found this site which also says that Minimum Password Age should only be enforced when Check_Expiration is on.  I think this is true for MAXIMUM Password Age, but I'm finding that Minimum Password Age is still getting enforced when Check_Expiration is off.

Is there something funky about my configuration?  Should Minimum Password Age really be enforced when Check_Expiration is off (but Check_Policy is on)?


texts: How to Sync a MySQL DB with a Oracle DB?

texts: I have access from 5 views of an Oracle DB located outside of my organization. I need to sync my db that is a mySQL DB with the oracle DB (Oracle views will be the master, my db will be the slave). I was reading about replication on MySQL DB but nothing about replication from a Oracle DB. There's anyway to I keep the tables equals? PS: I don't have access to the original tables, so I can't create a trigger there :(


texts: Kimball Modelling One Column Linking to Many Dimensions

texts:
We are conducting Quality inspections, Appraisals, and Ratings on furniture. Many different types of furniture exist, chairs (1), shelves (2), couches (3), table (4). The respective DimProductType is 1,2,3,4. We actually have 100+ product types.

If measuring facts for brown tables, or chairs with 6 six legs, or Couches with  polyester fabric type, how would I model the Fact table ?

In FactInspection, there are two proposed methods.
What is best Kimball design for this?

DimProductTypeProductId linking to many dimension tables below in one column, Or have denormalized columns for each furniture with nulls (with 100+ product types)

What is the official Kimball way to model this?

Method 1:

(this will join, on DimProductTypeProductId Composite)

create table dbo.FactInspection
(
    FactInspectionId int primary key identity(1,1)
    DimInspectionDate int not null,
    ValueAmount numeric(10,2)not null,
    StarRatingOutof10 int not null,
    DimProductTypeProductId null
)

Method 2:

create table dbo.FactInspection
(
    FactInspectionId int primary key identity(1,1)
    DimInspectionDate int not null,
    ValueAmount numeric(10,2) not null,
    StarRatingOutof10 int not null,
    DimChairId int null,
    DimShelfId int null,
    DimCouchId int null,
    etc..
)

Dimension Tables:

create table dbo.DimChair
(
    DimChairId int primary key identity(1,1),
    ProductType int,
    ChairNaturalKey int,
    ProductTypeAndNaturalKey as (ProductType + '|' ChairNaturalKey) persisted
    Color varchar(55),
    LegNumber int
)

create table dbo.DimShelf
(
    DimShelfId int primary key identity(1,1),
    ProductType int,
    ShelfNaturalKey int,
    ProductTypeAndNaturalKey as (ProductType + '|' ShelfNaturalKey ) persisted
    Length float,
    Height float,
    NumberofShelves varchar(55)
)

create table dbo.DimCouch
(
    DimCouchId int primary key identity(1,1),
    ProductType int,
    CouchNaturalKey int,
    ProductTypeAndNaturalKey as (ProductType + '|' CouchNaturalKey) persisted
    FabricType varchar(255),
    NumberofSeats int
)


texts: Too much memory is being used during backup

texts:
I have mongodb in ubuntu server. DB version : 3.2.8

CPU : 4 Core

Memory : 16 GB

Disk : 50GB

Normally server memory status:

Running backup script:

DIR=`date +%m%d%y`
DEST=DBBackup/MongoDB/$DIR
mongodump -d onedb -o $DEST
mongodump -d twodb -o $DEST
mongodump -d threedb -o $DEST
mongodump -d fourdb -o $DEST
mongodump -d fivedb -o $DEST

Script finished and server memory status:

The cache remains the same. If I repeatedly a backup for five days, all memory will be used. The memory is not flush automatically. What should I do?


texts: Question about Oracle's interpretation of "host cpu utilization" in v$sysmetric view

texts:
I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization"

It says in the metric_unit as % busy/(idle + busy), but what is busy?  Is that the sum of usr + sys time?  I tried comparing the query results from v$sysmetric against output from mpstat but it didn't seem to match.


texts: MS SQL Server 2012 Log Shipping Questions

texts:
I have a few questions regarding Log Shipping in SQL Server 2012.

If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?
Once the primary server comes back online, is it a difficult process to switch back to the primary database server?
Will changing the Recovery model to Full (from simple) have any issues?
Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?