PostgreSQL 9.6 pg_rewind - takes a long time to replicate I use Streaming Replication,and Replication works normally.
Two servers are HA and PostgreSQL DB is replicated.
The data in the DB is about 40G.
When a failover occurs, the slave is successfully promoted to master, master becomes slave, and tries to replicate new master data.
But, It takes a long time to replicate data from the new master server.
(using pg_rewind / success / 38G - 6minutes).

Please let me know if there are other ways to save time.

This is the command I ran:

pg_rewind --target-pgdata="targetdir" --source-server="sourceserver"

This is the output:

connected to server
servers diverged at WAL position 35/DD0D2260 on timeline 37
rewinding from last common checkpoint at 35/DC4E94F8 on timeline 37
reading source file list
reading target file list
reading WAL in target
need to copy 39193 MB (total source directory size is 77268 MB)
698400/40134372 kB (1%) copied

pg_rewind connects to the new master and locates the latest checkpoint it shares with the old master. Then it examines local WAL to find all blocks that have been modified since and copies these blocks from the new master.

So the procedure is slow if

promotion happened a long time ago, so pg_rewind has to dig through many WAL files

or

many blocks have changed since the promotion

From the output of pg_rewind it becomes obvious that half of the blocks in the database cluster have been modified since the slave was promoted. So the problem is that you waited too long after promotion. Immediately after failover, pg_rewind would be much faster.


What's the easiest and most accurate way to visualize parallel thread usage in SQL Server? Following up on my other question here, I'd like an easy way to visualize the answer.

I've tried using a query like this, but the number of "running" threads can exceed DOP sometimes.

SELECT    ost.session_id,
          ost.exec_context_id,
          ost.scheduler_id,
          qp.node_id,
          w.worker_address,
          qp.physical_operator_name,
          ost.task_state,
          qp.row_count
FROM      sys.dm_os_tasks AS ost
JOIN      sys.dm_os_workers AS w
    ON ost.worker_address = w.worker_address
LEFT JOIN sys.dm_exec_query_profiles AS qp
    ON w.task_address = qp.task_address
WHERE     ost.session_id = 57
AND       ost.task_state = 'RUNNING'
ORDER BY  ost.exec_context_id, ost.scheduler_id, qp.node_id, w.worker_address;

I've also tried using the query_thread_profile Extended Event, but for a plan with multiple parallel branches, it shows the same N number of Thread IDs. This seems contrary to the answer given, since DOP doesn't explicitly limit the total number of threads used. For a query running at DOP 4, it looks like this. I'm sure that the same four threads (plus coordinator) don't get used for all branches and operators.

So like, running a query like this at DOP 8 gives me three parallel branches.

SELECT COUNT(DISTINCT t1.Data)
FROM dbo.t1
JOIN dbo.t2
ON t1.Id = t2.f_Id
JOIN t3
ON t1.Id = t3.f_Id
OPTION(FORCE ORDER, HASH JOIN);

But sometimes my query returns this:

And sometimes it returns this:

Here's some sample data:

CREATE TABLE dbo.t1 ( Id INT IDENTITY PRIMARY KEY CLUSTERED, Data INT NOT NULL );

CREATE TABLE dbo.t2 ( Id INT IDENTITY PRIMARY KEY CLUSTERED, f_Id INT NOT NULL );

CREATE TABLE dbo.t3 ( Id INT IDENTITY PRIMARY KEY CLUSTERED, f_Id INT NOT NULL );

INSERT dbo.t1 WITH (TABLOCKX) ( Data )
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 2000
FROM (
SELECT TOP 1000000 1 AS num
FROM master..spt_values AS sv
CROSS JOIN master..spt_values AS sv2
CROSS JOIN master..spt_values AS sv3
) AS nums

INSERT dbo.t2 WITH (TABLOCKX) ( f_Id )
SELECT TOP 15000000 t.Id
FROM dbo.t1 AS t
CROSS JOIN dbo.t1 AS t2

INSERT dbo.t3 WITH (TABLOCKX) ( f_Id )
SELECT TOP 25000000 t.Id
FROM dbo.t1 AS t
CROSS JOIN dbo.t1 AS t2

Thanks!

I've tried using a query like this, but the number of "running" threads can exceed DOP sometimes.

The join from dm_os_tasks to dm_os_workers is one to one, because a worker thread runs exactly one task. The join from tasks/workers to dm_exec_query_profiles is one to many, because each thread may run multiple plan nodes.
You need to look at the number of unique tasks/execution contexts/schedulers that are associated with RUNNING status:
SELECT
    COUNT(DISTINCT DOT.scheduler_id)            -- or task_address / exec_context_id
FROM sys.dm_os_tasks AS DOT
WHERE
    DOT.task_state = N'RUNNING'
    AND DOT.session_id = 72                     -- specify the session id
    AND DOT.parent_task_address IS NOT NULL;    -- additional workers only

I've also tried using the query_thread_profile Extended Event, but for a plan with multiple parallel branches, it shows the same N number of Thread IDs.

The extended event description for this debug channel event is imprecise:

The documentation for sys.dm_exec_query_profiles gives a better description of the thread_id:

Distinguishes the threads (for a parallel query) belonging to the same query operator node.

Within each branch, each thread is given an ID, similar to the per-thread breakdown in showplan. At DOP x, there will be thread ids from 1 to x for each branch, but it is simply a sequential number (think row number).
You can tell the threads apart in the extended event by collecting task/worker/system thread id. If you are interested in thread ids for the whole executing plan, the best proxy for that is execution_context_id. Each execution context is associated with exactly one thread.
Visualization
Visualization of parallel execution can be done in many different ways, depending on which particular aspect is under investigation. The DMVs can be joined and ordered in many ways.
For example, the following shows what each thread did most recently, and what, if anything, it is currently waiting on:
DECLARE @session_id smallint = 72;

SELECT
    DOT.task_state,
    DOT.scheduler_id,
    DOT.exec_context_id,
    DEQP.physical_operator_name,
    DEQP.node_id,
    DEQP.thread_id,
    DEQP.row_count,
    DOWT.wait_duration_ms,
    DOWT.wait_type,
    DOWT.resource_description
FROM sys.dm_os_tasks AS DOT
OUTER APPLY
(
    -- What each thread did most recently
    SELECT *
    FROM sys.dm_exec_query_profiles AS DEQP
    WHERE
        DEQP.session_id = DOT.session_id
        AND DEQP.request_id = DOT.request_id
        AND DEQP.task_address = DOT.task_address
    ORDER BY
        DEQP.last_active_time DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DEQP
OUTER APPLY
(
    -- Longest wait, if any
    SELECT *
    FROM sys.dm_os_waiting_tasks AS DOWT
    WHERE
        DOWT.session_id = DOT.session_id
        AND DOWT.exec_context_id = DOT.exec_context_id
    ORDER BY
        DOWT.wait_duration_ms DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DOWT
WHERE
    DOT.session_id = @session_id
    --AND DOT.task_state = N'RUNNING'
ORDER BY
    DEQP.node_id,
    DOT.exec_context_id;

Example output for the demo query:


How to gracefully handle MySQL deadlocks involving SAVEPOINT? I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause

Here is a reproducible sample:

CREATE TABLE U ( -- user
    id int not null primary key,
    name varchar(222)
);

CREATE TABLE O ( -- object
    id int not null primary key,
    name varchar(222)
);

CREATE TABLE OU ( -- user <-> object
    id int not null auto_increment primary key,
    object_id int,
    user_id int,

    -- (object_id, user_id) should be UNIQUE (not enforced by a constraint)
    CONSTRAINT fk_object_id FOREIGN KEY (object_id) REFERENCES O(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES U(id) ON DELETE CASCADE
);

INSERT INTO U VALUES (1, 'foo'), (2, 'bar');

-- Start 2 concurrent transactions (A, B)
BEGIN; -- A
BEGIN; -- B
INSERT INTO O VALUES (1, 'a'); -- A
INSERT INTO O VALUES (2, 'b'); -- B
SAVEPOINT s_a; -- A
SAVEPOINT s_b; -- B
SELECT * FROM OU WHERE object_id = 1 FOR UPDATE; -- A
SELECT * FROM OU WHERE object_id = 2 FOR UPDATE; -- B
INSERT INTO OU(object_id, user_id) VALUES (1, 1); -- A
INSERT INTO OU(object_id, user_id) VALUES (2, 1); -- B

one of the transactions will fail with a Deadlock found when trying to get lock; try restarting transaction error.

The problem is that it rolls back the whole transaction without any way for me to gracefully handle the error.

In that example, the object 2 is lost. I would really like for it to still persist and handle the second insert a few seconds / minutes after another transaction.

The way the code is currently designed does not allow fine tuning of the transaction block (I cannot commit after the first insert then start another transaction)

Is there any way to tell MySQL to not rollback on that deadlock error, or to rollback specifically to the savepoint?

edit here is the innodb engine status

*** (1) TRANSACTION:
TRANSACTION 3727668996, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 20145497, OS thread handle 0x7f4fdba2b700, query id 15334836096 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.33 user1 update
INSERT INTO `Tablename` (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655988, 1, 26399, NULL, '2017-04-20 19:04:21')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668996 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 3727668998, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 20145647, OS thread handle 0x7f5d6d253700, query id 15334836100 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.35 user1 update
INSERT INTO `Tablename` (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655991, 1, 26399, NULL, '2017-04-20 19:04:21')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668998 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668998 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

First, some observations/comments/requests:

Please provide SHOW ENGINE INNODB STATUS;, in case it sheds any further light on the issue.
I usually find that SAVEPOINT can be avoided by rethinking the entire transaction.
Since SAVEPOINT seems to be critical to the problem, and it seems to be "broken", please file a bug at http://bugs.mysql.com .
Deadlocks will happen, no matter what you do to avoid them.  It is best to "live with them".  That is, catch them, then rerun the entire transaction.  (SAVEPOINT complicates the code for such.)

On the constructive side, I would recommend improving the many:many OU table by the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table -- In particular, its id is both wasteful and possibly contributing to the problem.


Setup AlwaysOn with 2 Secondaries and not adding all the databases for 2nd Secondary I have a situation where we have AlwaysOn Setup in SQL Server 2016 Enterprise editions with primary in region A and secondary in region B setup in Asynchronous-Commit mode. We have 4 databases in participating in this AG. Now we would like to add all another secondary in same cluster in Region A to different server. Out of 4 databases we only want to add 3 databases to 2nd secondary which will be in region A. Last database is heavy write application which grows quickly and we do not want to sync it between 2 secondaries. Is it possible to achieve and is there any performance effect of it?

You cannot exclude databases from replicas in an availability group.  There may be a way to accomplish this with a hack, but it would be an unsupported configuration.

In order to accomplish what you are specifying, you need two availability groups.  Availability group A would have all three servers as replicas, and would contain only the databases that you want to be replicated to all three servers.

Availability group B would have two servers as replicas, and would contain the one database that you want to be replicated to only those two servers.


Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL? There are two tables in PostgreSQL:

//has 2.2 millions rows
create table account (
    account_id text primary key,
    email text not null,
    created_at timestamp not null
);

//has 1.8 millions rows
create table account_intercom (
    account_id text primary key references account(account_id),
    intercom_id text not null
);

It's easy to notice ~400k rows difference between these tables.
I want to select these 400k account_id and perform some operations with them in order to eliminate this "gap".

I tried this query:

SELECT account_id, email
  FROM
    account NATURAL LEFT JOIN
    account_intercom
  WHERE intercom_id IS NULL;

But it runs extremely slow.

What is the way to speed up this query?

QUERY PLAN
---------------------------------------------------
 Hash Left Join  (cost=75252.40..207091.91 rows=1 width=46) (actual time=30056.787..87284.881 rows=417195 loops=1)
   Hash Cond: (account.account_id = account_intercom.account_id)
   Filter: (account_intercom.intercom_id IS NULL)
   Rows Removed by Filter: 1799858
   ->  Seq Scan on account  (cost=0.00..47840.67 rows=2221867 width=46) (actual time=0.010..13484.228 rows=2217053 loops=1)
   ->  Hash  (cost=36919.51..36919.51 rows=1800551 width=48) (actual time=30018.683..30018.683 rows=1799858 loops=1)
         Buckets: 65536  Batches: 64  Memory Usage: 2722kB
         ->  Seq Scan on account_intercom  (cost=0.00..36919.51 rows=1800551 width=48) (actual time=0.008..9801.883 rows=1799858 loops=1)
 Planning time: 0.333 ms
 Execution time: 88794.797 ms

You should write it like this with a NOT EXISTS statement

SELECT account_id, email
FROM account AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM account_intercom AS i
  WHERE a.account_id = i.account_id
);

Make sure your stats are uptodate and your table clean,

VACUUM FULL account;
VACUUM FULL account_intercom;

Paste the updated EXPLAIN ANALYZE


restricting users to rows they created To educate myself in PHP and mysql, I am making a browser based rpg game. This game will allow any number of users to register an account and, once registered, they can add, edit or delete champions into my database. The problem is that they should ofcourse only be able to edit and delete champions that they made themself.

Now since this is just a practise project, I don't forsee large quantities of data or any serious attempts to hack it, but all the same I'd like to do it right so I can use this experience if I ever do get to work on large serious projects.

Should I
a) Let a new database be created for each user?
b) Let a new set of tables be created for each user?
c) Put the data of all users together in the same 3-5 tables and track for each row which user created it?

I think that C is the cleanest solution with the least data consumption, but perhaps the one with the least security. Is this correct?

And how would you suggest I implement solution C? Is there a privalege that restricts a user from only editting / deleting rows that he/she created him/herself? Or should I include this restriction in the PHP level? Would having this restriction in the PHP level not create a security risk since someone might be able to connect to my database without my php scripts?

And should a new mysql user be made for each player? Or can they all share a user account and make the distinction between players with an ID field or something like that?

I would do it like this:
 - Create a table users, where you store your users.
 - Create a table champions, where you store the champions and also in a column champions.users_id the id for that user who created this champion.
 - If a user wants to edit a champion, you check if he belongs to him by checking if champions.users_id is the same as the users ID.

And by the way, no one should be able to connect to your DB. You can define, that you can only grant access to the DB, if the IP is right. For example only localhost or whatever. Your queries should all run through one mysql user.

This are my thoughts about this topic. Maybe possible that there are better ways. But i would go on like this.


What does "Sending data" mean in the mysql process list? I'm seeing a query getting stuck in the process list for long period of times with the Status of "Sending data". What does this status mean?

If someone knows more, please do fill in the gaps.

TL;DR "Sending data" seems to mean we are sending data about the query to the function that gathers that information together. It is NOT sending data to the MySQL or MariaDB client. This probably has something to do with the assembly of a JOIN command.

Finding very little good clarification on the Internet, I started looking through the MariaDB 10.2 code for "Sending Data" (yea, open source). Take all this with a some caution as I am not a C++ hacker.

You can find that in Line 10155 of sql/mysqld.cc where it is associated with stage_sending_data. This is found in sql_select.cc Line 3561 where it is at the end of the JOIN::exec_inner function. This function is called from the function JOIN::exec(). JOIN::exec is called from the function mysql_select(). This is called from handle_select(). It appears that "Sending data" should only show up on queries with JOINs.

I think I see mysql_select() set the status "Init" or stage_init. Then "Executing" or stage_executing in JOIN::exec_inner() while it does some checks for various things that I assume break JOINs or take special attention.

Then it sets that status to "Sending data" or stage_sending_data. In the next few lines after this status is set, it runs do_select(this, procedure);. At this point it appears to actually start gathering the information needed to provide an answer to the query. Contrary to my expectations, it is pretty clear that "Sending data" is not sending data to the client (over the network or otherwise), but to another function. It isn't yet ready to give a response. do_select() proceeds to do things like sorting temporary tables, evaluating conditions on HAVING, WHERE and stuff. It also removes locks. All that is after "Sending data" status is set.

It might be the last Status setting before actually returning the data. I haven't dug enough to state that clearly. A lot happens in do_select() so I expect the status could get changed in there somewhere, but it probably is dependent on various factors. At the end of mysql_select it does set the status to "end" or stage_end.


Size of data in secondary exceeded primary after its initial resync from primary in MongoDB I step down the primary node, deleted its data and performed the initial resync from new primary. But when i checked the size of data after the completion of initial resync, I found that the size of data in this node was 409G whereas size of data in other node from where this node has synced its data was 326G. Hence the size was increased by 83G after the resync.

Earlier also i have done such initial resync, and every time I have found that the size of data decreases after the resync. But in this case its opposite.

Recently I have upgraded the version of cluster from 2.4.9 to 2.6.7. This was my first initial resync after the version upgrade. So is this increase in size is because of the new MongoDB version. Does the same data takes more storage space in version 2.6 than in version 2.4 ?

Does the same data take more storage space in version 2.6?

As noted in the MongoDB 2.6 release notes, the default storage allocation strategy changed from using exact-fit allocation with padding factor to usePowerOfTwoSizes. The powerOfTwoSizes allocation may take up more initial space, but generally results in less storage fragmentation and better reuse of space from deleted documents.

One notable exception is if you are using GridFS and have data saved with older drivers. The original GridFS chunk size was 256KB (an exact power of 2) which resulted in rounding up to 512KB (the next power of 2). Drivers were updated to lower the GridFS chunk size to 255KB, but this is a common reason your database size to grow if you resync with the powerOf2 allocation (see SERVER-13331 in the MongoDB issue tracker).

Changing the allocation strategy

In MongoDB 2.6 you can change the allocation strategy on a collection level using the collMod command, or on a server level with the newCollectionsUsePowerOf2Sizes configuration parameter. If you change the allocation strategy for an existing collection, this will only affect new documents created or moved in storage after the change. You would have to resync, compact, or repair the database to rewrite all documents with new allocations.

If you are using GridFS and have older documents with the 256K chunk sizes, it would make sense to change the allocation strategy for any gridfs chunks collections to not use PowerOf2Sizes. Similarly, if your data use case for a collection happens to be insert-only (or does not grow in size via updates) then the exact fit allocation will be more efficient.

Otherwise, the powerOf2Sizes allocation is recommended.

WiredTiger storage engine supports compression in MongoDB 3.0+

If storage size on disk is a concern, you should also consider testing the new WiredTiger storage engine in MongoDB 3.0, which includes support for index & data compression. Upgrading to MongoDB 3.0 & WiredTiger will require you to use updated drivers & tools, and as with any major version upgrade you should review the upgrade procedures relevant for your deployment as well as any compatibility changes.


Missing "MSVCR120.dll" during the Installation of MySQL Workbench 6.3 CE When I install MySQL Workbench 6.3 into my Windows 8.1, I encounter the following System Error :

I already tried to redownload and reinstall the program but this error dialog appears every time. How to fix this?

I am getting the same error and I installed using MSI.

Downloaded the VC redist packages for x64 and it fixed the issue:

Visual C++ Redistributable Packages for Visual Studio 2013

Strange that the error message has python.exe as it's title.


Physical Address Schema I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked).

Store the address inline with the source record
Have an address table and add a foreign key to the entity
Have an address table with a foreign key that links to the entity

I'd like to steer clear from storing it inline for normalisation reasons, but I'm unsure as to which of the other options to go for. It's possible at some point down the line that we want additional entity types to have addresses on them, which makes option 2 look like the best one. This option (as far as I can see) has the potential to leave unlinked data around though.

Short answer:

Either option 1 or option 2:

In-line (one address per source record)
Child table with an FK linked to source record (allows >1 address per source)

Longer...

A separate address table will normally have an FK to some parent record.
Each address will maybe have a type (billing, delivery, etc)

Addresses are not shared or reusable or finite: that is, each address for a source entry has little in common with other entries.

different source records wouldn't have shared addresses (eg many-many in the middle between source and address)
separate address table with FK(s) can't have multiple parents either
having the FKs on source records to some address table would be unusual.

Finally, consider how you use addresses. You add or update them. You don't look for an existing address and link to that. Each address is per source only.


calculate average of a class where students offer different number of subjects I am trying to calculate the class average of a class where students can hold different amounts of subjects (e.g.: two students in a class of which one takes 3 subjects and the other 2). How do I get the class average that applies to all students? I already have their total term score in a table. I know the formula for calculating the average is simply the total averages of individual students divided by the number of students, but I can't seem to get it working. Can anyone please help me write a query that will calculate and print out this class average?

Here is what I have tried so far:

SELECT student_id, SUM(CA_total)/count(term_total) AS average
FROM score_entry
GROUP BY student_id

BUT I want the final result to be the sum of 25 + 26.5 which will be 51.5 then divided by the 2 students in the class should give 25.75. this is the class average. I want the query or code that will echo out the final answer 25.75. thanks
THIS IS SAMPLE OF THE EXACT DATABASE

SUM(..)/count(..) is close.  Instead:

SUM(sums) / SUM(counts)

That is, each subtotal (student?) has a sum of the scores and a count of the number of items (subjects)


Is any tool for taking MySQL Dump Automatically with a particular time? I am using VS2010 and MySQL for my project. My client wantw to take mysqldump every day like a daily backup. I want to give my client a tool for taking mysqldumps on a particular time schedule.

Do you know any tool that can fulfill my requirement?

I assume you are using Windows (because of you mention VS2010).

Simply create a "scheduled job" either through the Windows UI (e.g. start control schedtasks) or the at command. This taks should run mysqldump at the desired time.

Here are some links that explain how to create a scheduled task in Windows:

Task Scheduler: Management Services | Microsoft Docs
List, add, edit, delete Windows Scheduled Tasks from the command-line | Server Fault
http://support.microsoft.com/kb/814596
http://support.microsoft.com/kb/308569


SQL Server 2012 SQL Log "the log file is not using Unicode format": On only one server of many, the SQL log is blank and shows a message "the log file is not using Unicode Format" when viewed via SSMS. The log is in the right location as specified in the service properties and is readable with notepad. Log looks normal, just won't show up in Log File Viewer in SSMS. Any ideas why?

SQL Server 2012 in active passive Windows failover cluster on Windows 2012. SQL Server ver 11.0.3368

This instance is the SQL Server for MS SCCM. It has trace flags -T8295 and -T4199 as service startup parms. These were added by SCCM when its DB's were installed.

Thanks

I had an idea which worked. I forced creation of a new log using sp_cycle_errorlog and the new log can be read fine in SSMS. This server recently had a bad crash due to a SAN connection failure. I think that the SQL log file may have bcome corrupted as a result of that.


How to match all words of one column against words in another column I have two tables.

Table A:

Company_name
-------------------------------
Tata consultancy
Infosys tech
Atm service
Air force firm

Table B:

Company1_name                    |  Id
--------------------------------   -----
Atm and service.                 |   1
Honey tech.                      |   2
Tata & consultancy.              |   3
Graft soft.                      |   4
Atm, service.                    |   5

I want this result:

Id
----
1
3
5

Description:

  For each Company_name in table A, find a row in table B whose company1_name value has all the words from table A's value, and return the matching row's Id.

How to design a query for this in MySQL?

For a quick and dirty solution - not 100% accurate due to some full-text search specifics but will get most of the matches - add a FULLTEXT key to A and then use a join with be with MATCH AGAINST as the join condition. Since MATCH AGAINST cannot use a a non-constant argument, you will have to simulate the join with a cursor in a stored procedure. Below is a fully-functional tested example:

create table a (id int not null primary key,
company_name text, fulltext key(company_name)) engine=myisam;
create table b (id int not null primary key, company_name1 text);
insert into a values(1,'dog kitten'),(2,'spoon fork'),
  (3,'fish crab'),(4,'dog mouse'),(5,'noise mouse'),
(6,'kitten dog'),(7,'noise one'),(8,'noise two'),
  (9,'noise three'),(10,'noise four'),(11,'noise five');
insert into b values(1,'dog mouse kitten'),
(2,'spoon knife fork'),(3,'fish sea crab');

drop procedure if exists ft_match;
delimiter //
create procedure ft_match()
language sql
deterministic
sql security definer
begin
    declare v_id int;
    declare v_company_name1 text;
    declare v_finished int;
  declare c cursor for select * from b;
    declare continue handler for not found set v_finished=1;
  delete from results;
  open c;
c_loop: loop
    fetch c into v_id,v_company_name1;
    if v_finished then
        leave c_loop;
    end if;
    insert into results select v_id,v_company_name1,a.id,a.company_name
     from a where match (a.company_name) against (v_company_name1 in boolean mode);
    end loop c_loop;
    close c;
    select * from results;
end//
delimiter ;

create table results (a_id int not null, a_company_name text,
b_id int not null, b_company_name text);
call ft_match();

More info on full text keys in the MySQL manual at https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

This is suitable for relatively small tables and in circumstances where high latency is acceptable. For better performance on large datasets  and perfect accuracy, you will need to implement some form of external full text indexing.


How can I design a query to pause / sleep / wait for a specified amount of time? I am testing a monitoring application against a SQL Server 2000 instance. How can I write a T-SQL query that takes an amount of time that I specify? For example, most scripting languages have something like the SLEEP command which allows you to pause script execution for a specified amount time. I am looking for something similar that is compatible with SQL Server 2000. This is intended to test the long-running query threshold settings of the monitoring application.

If you are looking for a function to block the execution batch for a specified amount of time, you can use WAITFOR in SQL Server 2000. This function is similar to SLEEP function in Oracle. Usage examples:

WAITFOR DELAY '00:00:30';     -- wait for 30 seconds
WAITFOR TIME '13:30';         -- wait until server time is 13:30


ORDER BY on aggregate field on a closure table giving unexpected results I am updating my companies product database and I'm looking into different ways to do design our faceted navigation "selector" tables.  The idea of faceted navigation might look like this:

It's hierarchical data.  I have been reading through Bill Karwin's slides on different design patterns for hierarchical data.

I'm trying out the "Closure Table" pattern.  I have these two tables that look like this:

CREATE TABLE Selector (
  SelectorID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Name VARCHAR(32),
  SortOrder INT
);

CREATE TABLE SelectorPath (
  AncestorID INT NOT NULL,
  DescendantID INT NOT NULL,
  PathLength INT NOT NULL,
  PRIMARY KEY(AncestorID, DescendantID),
  FOREIGN KEY (AncestorID) REFERENCES Selector(SelectorID),
  FOREIGN KEY (DescendantId) REFERENCES Selector(SelectorID)
);

I have been testing this concept on SQLFiddle.com here: http://sqlfiddle.com/#!2/881b8f/8

An ERD for SQLFiddle is:

The problem I'm running into is the ability to sort siblings.  I'm not sure the ERD file will show up very big but the idea is that at a given node, there might be an arbitrary sorting pattern implemented by the marketing team.  They may want "white" to be the last color on the list or "Bamboo" to be the first item in the material column.

In the ERD, the yellow-ish nodes (at depth of 2 where "root" is at depth of 0) would be the category names such as "material" or "color" and the leaf nodes (green on ERD) would be the values in those categories.

The second ERD picture shows the leaf nodes in red that have been properly sorted based on the sort value given for that node.

The way I'm trying to do this sorting is by using GROUP_CONCAT on the sort value like so:

  GROUP_CONCAT(s.SortOrder separator '') AS SortPath

This creates a table with a path string of integers.  Below is the original inserted table structure as well as the desired sorted order.

My first attempt was to simply use ORDER BY SortPath.  This gives results I get are simply bizarre to me.  The ordering of the aggregate field ends up changing the actual SortPath value.  Here are bizarre results next to the original table:

According to this SO answer, it would seem like this should work.

Instead, I have to use a derived table and sort on the derived table to get the results I expect.

My ultimate question is, "Why is my SortPath field being changed when using ORDER BY?"

I speculate that ORDER BY actually happens before the aggregation, but I'm not finding much and I'm not sure if there might be something else in my query that's throwing things off.

Any additional comments on this design idea of sorting a Closure Table would be appreciated as well.  The current database structure is an Adjacency List.

All three SQL statements (table, sorted table, derived sorted table) are in my SQL fiddle.

My biggest concern is that of performance of doing a derived table.  It appears to take 8ms to do a derived table while only 4ms to sort the table.

I will be long term caching DB results at the application level, so in some ways the point is moot.  However, I'm looking to learn and experience what's going on here.

Thanks!

Your sortpath isn't technically being "changed" by the ORDER BY.  The correct values are all there, just not coming up in the order you anticipated.  In SQL, if you don't explicitly sort a set of data, the order in which it comes back to you is undefined... and the fact that it originally appeared to be in the anticipated order was more along the lines of coincidental.

I tweaked your fiddle (the "sorted" query):

GROUP_CONCAT(s.SortOrder ORDER BY a.PathLength DESC separator '') AS SortPath

Now, it looks like the results are being explicitly sorted in the order you expect, which is the order in which they were implicitly sorted before.

So why did that order change?  If you do an EXPLAIN SELECT on the "sorted" query with and without the ORDER BY clause, you'll notice that the ordering of the tables changes in the output of EXPLAIN depending on whether you specify ORDER BY on the entire query.  This is because the query optimizer re-orders the join-order of the tables to what it thinks is the most cost-effective sequence.

With the ORDER BY, the tables are joined from left to right, n-d-a-s.  Without the ORDER BY, the tables are joined from left to right, d-n-a-s.  Both are logically valid, but this causes the rows to be read from the tables in a different order, and your unsorted GROUP CONCAT's output order changes as a result of the change to the query plan.


Postgresql versus MySQL insert performance with same indexes I'm trying to compare the performance speed differences for an insert of 100,000 records (7 columns) between Postgresql and MySQL:

I get the following insert query speeds for MySQL:

Test 1 (primary key): 58 seconds
Test 2 (primary key, index 1) : 52 seconds
Test 3 (primary key, index 1, index 2): 414 seconds

I get the following insert query speeds for Postgresql:

Test 1 (primary key): 56 seconds
Test 2 (primary key, index 1) : 52 seconds
Test 3 (primary key, index 1, index 2): 60 seconds

Is there a reason why Postgresql doesn't seem to suffer from insert performance when I add more indexes for 100,000 records? Does this have something to do with the type of indexes Postgresql is using compared to MySQL or is it something to do with memory differences.

For MySQL, I would guess that got bogged down in I/O.  Possible scenario:

you are using InnoDB, and
the total data + index size exceeded innodb_buffer_pool_size, and
the extra index in the slow test was very random (such as a UUID)

As long as everything fits in the buffer_pool, InnoDB can run quite rapidly.  When you run out of cache space, there might be a lot of I/O, thereby slowing things down.

Also..., InnoDB caches and delays non-unique index writes.  But eventually the writes must be done.  It is hard to say whether you got to that point.

Other things that may speed up inserts:

Batching, either through LOAD DATA or multi-row INSERTs.  The latter runs about 10 times as fast when inserting 100 rows at a time.
Even single-row INSERTs run a lot faster if you do not COMMIT after each row (such as with autocommit=ON).
Sorting the data according to some index, especially some UNIQUE index.  (This helps with caching.)

Those are just some of the ways your benchmark may have been biased in favor of or against MySQL.  (I cannot speak for Postgres, other than  to say that the PRIMARY KEY is stored quite differently.)

An argument against the benchmark:  The main use for indexes is during SELECT, so why measure INSERTs?  Also, if you need to insert a zillion rows per millisecond, when will the database find time to run SELECTs?

Here are more tips on MySQL high-speed ingestion.  (I do not know which parts would also help Postgres.)

A common flaw in benchmarking is to accidentally set the number of iterations (or rows or whatever) just above or below some critical point.  Perhaps 10K rows would report nice rows/second for both engines; perhaps 1M rows works terrible for both, but 100K is near the "cliff" --  and MySQL fell off the cliff just before Postgres would.


Connecting SQL Anywhere with Sybase database file on local computer I have installed SQL Anywhere 12 in my system.

I have a .db file in my computer. I have no idea what is it's username and password.

If I try to connect to it by providing credentials- username=DBA and password=sql
I'm getting below error-

How can I connect to it successfully?

Here is a way to reset the password using the free SQL Anywhere 17 Developer edition. The paid edition should work also.
"C:\Program Files\SQL Anywhere 17\bin64\dbsrv17.exe"  dbsrv17 -orp "UID=DBA;NEWPWD=newpassword;AUTHUID=reset_user;AUTHPWD=sql456" C:\database.file.db


How to pivot this unpivot? This is the pivot I made:

SELECT *
FROM (
    SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao
    FROM (
        SELECT *
        FROM (
            SELECT 39 as codigo
                , (ROW_NUMBER() over
                    (
                    PARTITION by T1.CodNrQuestao
                    ORDER BY T1.CodNrQuestao
                    )
                ) as ID
                , 0 as Grupo
                , T1.Opcao as CodOpcao_1
                , T2.Opcao as CodOpcao_2
                , T3.Opcao as CodOpcao_3
                , T4.Opcao as CodOpcao_4
            From tblQuestaoOpcao T1
                Cross Join tblQuestaoOpcao T2
                Cross Join tblQuestaoOpcao T3
                Cross Join tblQuestaoOpcao T4
            Where T1.codNrQuestao = 811962
                And T2.CodNRQuestao = 811757
                And T3.CodNRQuestao = 812009
                And T4.CodNRQuestao = 812011
        )  as T0
        UNPIVOT (
        [CodOpcao] FOR [DescQuestao] IN (
        [CodOpcao_1]
        , [CodOpcao_2]
        , [CodOpcao_3]
        , [CodOpcao_4]
        )
        ) AS U
    )  as T0
)  as T0
PIVOT ( -- from here I start to get errors
    CONVERT(VARCHAR, AVG(codOpcao))
    FOR descQuestao IN (
        CodOpcao_1
        , CodOpcao_2
        , CodOpcao_3
        , CodOpcao_4
        )
) AS U

I need that this:

Become this:

CODIGO | ID | GRUPO | CODOPÇÃO_1 | CODOPÇÃO_2 | CODOPÇÃO_3 | CODOPÇÃO_4
 39      1     0

SQL is giving me error sintax in everything I try.

This is the fiddle of the table:

http://sqlfiddle.com/#!3/8d3d0

Why do you need to convert the average into a varchar?  Also, why do you have two extra SELECT * FROM (...) AS T0 wrapping the main select statement?

This runs without error:

USE tempdb;

CREATE TABLE [dbo].[tblQuestaoOpcao](
    [CodQuestaoOpcao] [int] IDENTITY(1,1) NOT NULL,
    [CodNRQuestao] [int] NULL,
    [Opcao] [int] NULL,
    [DescOpcao] [varchar](5000) NULL,
 CONSTRAINT [PK_tblQuestaoOpcao] PRIMARY KEY CLUSTERED
(
    [CodQuestaoOpcao] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
    , FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO dbo.tblQuestaoOpcao(CodNRQuestao, Opcao, DescOpcao)
VALUES (811962, 2, 'CodOpcao_1')
    , (811757, 3, 'CodOpcao_2')
    , (812009, 3, 'CodOpcao_3')
    , (812011, 3, 'CodOpcao_4')

SELECT *
FROM (
    SELECT 39 as codigo
        , (ROW_NUMBER() over
            (
            PARTITION by T1.CodNrQuestao
            ORDER BY T1.CodNrQuestao
            )
        ) as ID
        , 0 as Grupo
        , T1.Opcao as CodOpcao_1
        , T2.Opcao as CodOpcao_2
        , T3.Opcao as CodOpcao_3
        , T4.Opcao as CodOpcao_4
    From tblQuestaoOpcao T1
        Cross Join tblQuestaoOpcao T2
        Cross Join tblQuestaoOpcao T3
        Cross Join tblQuestaoOpcao T4
    Where T1.codNrQuestao = 811962
        And T2.CodNRQuestao = 811757
        And T3.CodNRQuestao = 812009
        And T4.CodNRQuestao = 812011
)  as T0
UNPIVOT (
[CodOpcao] FOR [DescQuestao] IN (
[CodOpcao_1]
, [CodOpcao_2]
, [CodOpcao_3]
, [CodOpcao_4]
)
) AS U
PIVOT ( -- from here I start to get errors
    AVG(codOpcao)
    FOR descQuestao IN (
        CodOpcao_1
        , CodOpcao_2
        , CodOpcao_3
        , CodOpcao_4
        )
) AS U;

Results:

Perhaps I really don't understand what you are attempting to do, or perhaps this is just a code fragment, or perhaps my sample data is incorrect, however the following query returns the same result as the above PIVOT / UNPIVOT:

SELECT 39 as codigo
    , (ROW_NUMBER() over
        (
        PARTITION by T1.CodNrQuestao
        ORDER BY T1.CodNrQuestao
        )
    ) as ID
    , 0 as Grupo
    , T1.Opcao as CodOpcao_1
    , T2.Opcao as CodOpcao_2
    , T3.Opcao as CodOpcao_3
    , T4.Opcao as CodOpcao_4
From tblQuestaoOpcao T1
    Cross Join tblQuestaoOpcao T2
    Cross Join tblQuestaoOpcao T3
    Cross Join tblQuestaoOpcao T4
Where T1.codNrQuestao = 811962
    And T2.CodNRQuestao = 811757
    And T3.CodNRQuestao = 812009
    And T4.CodNRQuestao = 812011

This can be further simplified by removing the CROSS JOINs and performing a PIVOT, as per this:

SELECT *
FROM (
SELECT 39 as codigo
    , (ROW_NUMBER() over
        (
        PARTITION by T1.CodNrQuestao
        ORDER BY T1.CodNrQuestao
        )
    ) as ID
    , 0 as Grupo
    , DescOpcao
    , Opcao
FROM tblQuestaoOpcao T1
) pvt
PIVOT (
AVG(Opcao) FOR DescOpcao IN
    (
        [CodOpcao_1]
        , [CodOpcao_2]
        , [CodOpcao_3]
        , [CodOpcao_4]
    )
) u;

Results:


Based from experience, is dreamfactory better paired with a RDBMS like MySQL or NoSQL like MongoDB This is our first time to create a data logging web application and we have chosen to use Dreamfactory as the backend framework. We have been used to with MySQL from the past with small amount of data but now considering into trying NoSQL like MongoDB.

These are the data that we are trying to log is basically timestamp of devices and when it was detected by a certain sensor and the signal strength when it was detected. So these items will be logged:

ClientDeviceID
HostDeviceID
Timestamp
SignalStrength

I'm trying to determine from the two database if which one is efficiently handling this type of data to be logged

DreamFactory works just fine with either SQL or NoSQL, and the API will actually be the same to GET or PUT data. The fields you have above make sense for either SQL or NoSQL. The only difference is that you need to create schema for the SQL table you want to use. Here is a tutorial that is very similar to your use case:

http://thenewstack.io/tutorial-prototyping-a-sensor-node-and-iot-gateway-with-arduino-and-raspberry-pi-part-1/

Disclaimer: I work for DreamFactory Software.


MySQL INFORMATION_SCHEMA.TABLES not adding to file size I have a file for a table which has grown very large. I expected to have a large amount of unallocated space needing to be optimized, but when checking INFORMATION_SCHEMA.TABLES, there is little data_free (4 MiB, 0.4 %) and the numbers do not sum to the size of the file:
$ sudo ls -lh /var/lib/mysql/data/modnar.ibd
-rw-r----- 1 mysql mysql 7.0G Aug 23 09:52 /var/lib/mysql/data/modnar.ibd

mysql> select table_name, engine, data_length, max_data_length, index_length, data_free from information_schema.tables where table_name = "modnar";
+------------+--------+-------------+-----------------+--------------+-----------+
| table_name | engine | data_length | max_data_length | index_length | data_free |
+------------+--------+-------------+-----------------+--------------+-----------+
| modnar     | InnoDB |  1172307968 |               0 |            0 |   4194304 |
+------------+--------+-------------+-----------------+--------------+-----------+

The file is 7 GiB but adding the numbers from INFORMATION_SCHEMA.TABLES, (1172307968+4194304)/1024/1024/1024 is only 1.1 GiB.
Why is the file so large, 7 times the amount of data in the table?

It turns out INFORMATION_SCHEMA.TABLES is rarely updated and can be off by a large factor. Extensive details from percona: https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/
INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES is always up to date, indeed:
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name = 'data/modnar';
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME        | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE |
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
|    54 | data/modnar |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 7470055424 |     7470059520 |
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+

Running OPTIMIZE on the modnar table did update INFORMATION_SCHEMA.TABLES for which the numbers now add up to 6.25 GiB.
A query to find which table use large amount of unallocated space from up to date data:
select name, unallocated_KB, round(unallocated_KB/(fs_block_size/1024)) unallocated_blocks, unallocated_KB/(file_size/1024) unallocated_percent from (
  select name, fs_block_size, file_size, (ALLOCATED_SIZE - FILE_SIZE)/1024 unallocated_KB
  from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
  where name not like 'mysql/%' and name not like 'sys/%'
  order by unallocated_KB
  ) as tables;


Estimate a Database Size I have to do some project and need to estimate the DB size.
I'm going to use Oracle in the project because we will have a lot of transactions and data.
But in the "presentation" I need to specify, in 5 years, how much I expect the DB to grow.

So, I have a PDF with the way of calculating the row size and another stuff, but for SQL-Server.
I want to do it for Oracle.

The pdf is an estimation formula - without having the real DB, so I can't do queries to the DB to check the actual size (That's What I've seen in other posts here when I googled).

The formula for SQL-Server row is

  Row_Size = Fixed_Data_Size +Variable_Data_Size +Null_Bitmap + 4

This formula works in Oracle too?
Also, "4" is the size of  the  header of a row (I don't know how to translate the meaning in English)

So, I have to do this in Oracle. What will be the value 4 in Oracle?
The other things I think I think I've solved them.

Ideally, you would create the database, load a bit of sample data, measure the size, and extrapolate. That is, by far, the more accurate method of estimating the size of a database in 5 years.

If you do want to compute the database size, you would generally start by figuring out how many rows will fit in a single block. For simplicity, we'll assume that rows are never deleted and that updates never change the size of the row. We'll also assume no compression is being used.  Otherwise, things get a bit (more) complicated.

Calculate the size of the data in the row.  For fixed-size data types (i.e. DATE, CHAR), that's just the size of the type.  For variable-size data types (i.e. NUMBER, VARCHAR2), that's the average size of the data in the column.  There are a couple of bytes of additional overhead, but you can pretty safely ignore that-- they're going to be swamped by the errors in estimating the size of your actual data and in the subsequent estimate of the number of rows per block.

If you expect that each row will have x bytes of data, the number of rows per block will be

<<rows per block>> =
  floor( <<database block size>> *
          (1 - <<pctfree of table>>/100) /
          <<size of row>> ) + 1

assuming that a row is smaller than <<database block size>> * <<pctfree of table>>/100.  If the row is larger, then don't add 1 to the result of the floor.

Once you know the number of rows per block, the estimated size of the table will be

<<size of table>> =
  ceil( <<number of rows in table>> / <<rows per block>> ) *
    <<database block size>>

Walking through an example

My database block size is 8k and we'll assume that I'm using the default PCTFREE of 10 (meaning 10% of the block is reserved for future updates that increase the size of the row).    I'll create a simple two-column table

SQL> create table foo(
  2    foo_id number,
  3    foo_str varchar2(100)
  4  );

Table created.

If foo_id is going to be the primary key with values from 1 to 1 million, each foo_id will consume between 1 and 7 bytes of space.  But I also know from doing the test that, on average, it'll take ~6 bytes (actually 5.89 bytes).  Of course, the larger the foo_id values get, the more space, on average, each foo_id requires.  Oracle needs, on average 1.1 bytes per element to store the numbers 1-10, 1.92 bytes to store 1-100, 2.89 bytes to store 1-1,000, 3.89 bytes to store 1-10,000, 4.89 bytes to store 1-100,000, and 5.89 bytes to store 1-1,000,000.  So, let's estimate for our example that foo_id will require 6 bytes and foo_str will require 50 bytes because the average foo_str is roughly 50 bytes.  So we'll estimate a row size of 56 bytes.

The number of rows per block

<<rows per block>> =
  floor( 8192 *
          (1 - 10/100) /
          56 ) + 1

which works out to 132 rows per block. If we want to estimate the size of a 1 million row table,

<<size of table>> =
  ceil( 1000000 / 132 ) *
    8192

which works out to 59.19 MB.

Now, let's test our estimate

We'll insert 1 million rows where foo_id goes from 1 to 1,000,000 and foo_str is a string with a random length between 1 and 100.

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    select level, dbms_random.string( 'p', dbms_random.value(1,100))
  3      from dual
  4*  connect by level <= 1000000
SQL> /

1000000 rows created.

Our estimate of the average row length was spot on (note that, in reality, you won't be nearly this close-- your estimate of variable column sizes will not be nearly so accurate)

SQL> exec dbms_stats.gather_table_stats( 'SCOTT', 'FOO' );

PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf

  1  select avg_row_len, num_rows
  2    from user_tables
  3*  where table_name = 'FOO'
SQL> /

AVG_ROW_LEN   NUM_ROWS
----------- ----------
         56    1000000

But how big is the actual table?  The most common measure is to look at the size of the segment which is 72 MB.

SQL> select sum(bytes)/1024/1024 mb
  2    from user_segments
  3   where segment_name = 'FOO';

        MB
----------
        72

Our guess was off by ~20% and that was when we were perfect on our estimate of the size of a row.  That's because Oracle allocates space to tables in chunks called extents that we've ignored.  There are different algorithms for this that depend on the setup of the tablespace.  Assuming recent Oracle versions where all tables are in locally managed tablespaces, you would be choosing between uniform extent allocation and automatic extent allocation.  In my example, the tablespace I'm using is using automatic extent allocation.  The exact algorithm for that, in turn, may depend on the version of Oracle you're using.  In my case, though, the first 16 extents are 64 kb, the next 63 extents are 1 MB, and the last extent is 8 MB

SQL> ed
Wrote file afiedt.buf

  1  select bytes, count(*)
  2    from user_extents
  3   where segment_name = 'FOO'
  4   group by bytes
  5*  order by bytes
SQL> /

     BYTES   COUNT(*)
---------- ----------
     65536         16
   1048576         63
   8388608          1

That means that I probably got a bit unlucky and I had just a bit more data than would fit in 79 extents totaling 64 MB so I had to allocate an 80th extent that was 8 MB in size, for a total of 72 MB.  We can use the dbms_space package to get more details about how much space is being used.  When we do that, we see that we're actually only using 66.22 MB of the 72 MB that have been allocated.  So our actual estimation error is really only ~10%

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_space_used NUMBER;
  3   l_space_allocated NUMBER;
  4   l_chained_pct NUMBER;
  5  BEGIN
  6    dbms_space.object_space_usage(
  7      'SCOTT',
  8      'FOO',
  9      'TABLE',
 10      NULL,
 11      l_space_used,
 12      l_space_allocated,
 13      l_chained_pct);
 14    dbms_output.put_line('Space Used: ' || TO_CHAR(round(l_space_used/1024/1024,2)) || ' MB');
 15    dbms_output.put_line('Space Allocated: ' || TO_CHAR(l_space_allocated/1024/1024) || ' MB');
 16    dbms_output.put_line('Chained Percentage: ' || TO_CHAR(l_chained_pct));
 17* END;
SQL> /
Space Used: 66.22 MB
Space Allocated: 72 MB
Chained Percentage: 0

PL/SQL procedure successfully completed.

Is there a shortcut?

If you're using a recent version of Oracle, you can use the dbms_space.create_table_cost procedure to estimate the size of a table.  There are a couple ways of doing this.  The first option is to pass in the size of the row.  Using our 56 byte estimate, that yields a table size estimate of 64 MB with 63.52 MB used which is pretty close

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_used_bytes NUMBER;
  3   l_allocated_bytes NUMBER;
  4  BEGIN
  5    dbms_space.create_table_cost('USERS',
  6                                 56,
  7                                 1000000,
  8                                 10,
  9                                 l_used_bytes,
 10                                 l_allocated_bytes);
 11    dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
 12    dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
 13* END;
SQL> /
Used Bytes: 63.52 MB
Alloc Bytes: 64 MB

PL/SQL procedure successfully completed.

You can also specify the data types of the columns that will be in your table.  Since there is no way to specify the average size, though, this tends not to be nearly as accurate as doing it yourself.  In our case, though, it's pretty good because our VARCHAR2 column happened to be populated with strings that were, on average, half the maximum size of the column.  In our case, though, it correctly estimated the allocated size of 72 MB with 67.94 MB used.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_used_bytes NUMBER;
  3   l_allocated_bytes NUMBER;
  4   l_cols sys.create_table_cost_columns;
  5  BEGIN
  6    l_cols := sys.create_table_cost_columns(
  7            sys.create_table_cost_colinfo('NUMBER',10),
  8            sys.create_table_cost_colinfo('VARCHAR2',100));
  9    dbms_space.create_table_cost('USERS',
 10                                 l_cols,
 11                                 1000000,
 12                                 10,
 13                                 l_used_bytes,
 14                                 l_allocated_bytes);
 15    dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
 16    dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
 17* END;
SQL> /
Used Bytes: 67.94 MB
Alloc Bytes: 72 MB

PL/SQL procedure successfully completed.


How to obtain a good execution plan for (LIKE @p) when the pattern is of the form 'prefix%' I'm using an ORM that executes queries of the form:

SELECT Columns
FROM T
WHERE Col LIKE @p0 ESCAPE '~'

Col is indexed and the index is covering. The predicate is very selective. The tables has 500,000 rows.

I'm always querying for a string prefix (e.g. 'prefix%'). Clearly, this is not statically known to SQL Server here. But I know that SQL Server is basically capable of seeking based on a LIKE pattern with a prefix.

I would like SQL Server to discover at execution time the relevant range of the index to scan and only scan that range. The execution plan that I'm getting has an index scan, though.

The ORM does not support OPTION (RECOMPILE) and I'm not sure it would be a good option since the queries execute in about 20 milliseconds. (However I would like this query to go faster. With a seek this should be able to run in ~1ms).

Can I somehow make SQL Server perform a seek here?

I would like SQL Server to discover at execution time the relevant
  range of the index to scan and only scan that range.

This is exactly the plan I get.

CREATE TABLE T(Columns VARCHAR(50),
               Col VARCHAR(50),
               INDEX IX(Col, Columns));

DECLARE @p0 VARCHAR(50) = 'foobar%'

SELECT Columns
FROM T
WHERE Col LIKE @p0 ESCAPE '~'

See Dynamic Seeks and Hidden Implicit Conversions for more about this.

Check that the index is indeed covering and that the datatype of the parameter is correct for the column definition (i.e. if the column is varchar and the parameter is nvarchar this can cause a scan).


How to debug huge binary log file increases in MySQL replication setup? I'm running a single slave off a MySQL master. I'm using this version:

Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

The problem I have is that the system will replicate quite happily for a day or so and then something hits it and the log files on the master grow hugely.

The data directory for the database is about 9.5GB in size.

When the replication problem happens, the log files can take up 175GB of disk space in a very short amount of time (filling the disk).

How do I debug what is happening? Can anyone suggest what to look for in the database operations that may cause this huge blow-out?

I think your replication uses ROW as binlog_format. To verify, please run this on the master:

SHOW VARIABLES LIKE 'binlog_format'

The result would be one of the following values: statement, row, or mixed. More info.

In the case of ROW format, every changed row will add info to the bin log. If there are statements that affect a table of 1000000 rows, all these changes will be written to the binlog.

For example, suppose you have a huge table, with 1M rows, and you run a statement like this:

UPDATE big_table SET hashed_email=md5(email)

This statement will add 1M entries to the binlog when it is row based, while it adds only one entry to the binlog when it is statement based.

Suggested solution:

Enable the general query log around the time when this this issue usually happens, and analyze all the statements to see if there are many that does update operation on huge number of rows.


How do I get a list of "large data users" from my table? This situation is a little specific so bear with me.

I have a (very) large table with many users (from a sync server). The users are stored by a hash identifier "username". Each user has many rows in the table where data is stored for them with a common username field.

I can get the total size used per user with the following (example) query:
select username,round(sum(length(payload))/1024) from wbo where username = 's5i7goejhuxdfhulhpdqjubxgkskfdve';
This gives me the output I'm looking for for a single, known user.

What I'm looking for is a way to get a similar output, but then listing the users that have a total data (payload) size larger than a certain amount, so I can find the username IDs of all large data users, so they can be addressed.
What query would I need for this?

select  username, round(sum(length(payload))/1024) AS metric
    from  wbo
    GROUP BY  username      -- to get one row per user
    ORDER BY  metric DESC   -- list biggest first
    LIMIT  50;              -- but show only 50


mysql Replication and Point in time Recovery I have a master and a slave mysql database (version 5.1). Is it possible to dump the database only on the slave side? If I do a dump with mysqldump I have a fulldump. Which files can I use for a point in time recovery? The relay logs?

Thanks and regards,
Martin

Steps you should follow:

a) Execute flush logs; on Slave server and Take a backup of slave server using mysqldump now new binary log will be created on slave server

b) Import the dump taken using mysqldump and after that using mysqlbinlog specify binlog file to read from till point of recovery time this will restore your slave status to a point you need to start with.


Need to encrypt certain columns in a table but also be able to sort and search We've got a web app that needs to store sensitive data entered by the user. Currently we're exploring PostgreSQL in AWS. I'm aware of pgcryto and that we can hash contents of certain columns (as not everything needs to be encrypted). However, we need to also be able to search through these columns and perform sorting. These two seem to be limitations once we encrypt the data.

What are my choices at the moment if we must also support sorting and searching? Keeping in mind that the solution must also be performant.

You don't want to encrypt specific columns, what you want to do is encrypt the filesystem that the database is written to, and any backups of your database that you make. AWS offers facilities for doing both of those ( RDS Encrypted resources and S3 server-side encryption ) or you can do it yourself by using LVM volumes that are mounted using ecryptfs.

You fundamentally cannot search or sort an encrypted column without decrypting the contents. If your encryption is effective then any sorting or indexing process will see it as gibberish and if it's not effective then why are you bothering with it?

The other consideration you should be taking into account is the threat model and risk models associated with your data. If you are dealing with certain classes of data you will need to manage your exposure by following mandated security protocols. HIPAA for medical information, PCI DSS for payment information. Detailed guides for implementing those protocols are available, and if you are covered by one of them; follow it.

Encryption and data protection is not an area in which you should be creative; it's hard to get right and will bite you hard if you get it wrong.


How to get count 0 using group by if record not exist in the same table? I need some expert advice please.
I have a table with Employees and eCodes in SQL server. I want to get the count of eCodes for each employee but in some cases an employee doesn't have a certain eCode.
Where the eCode is missing for an employee I want to return zero.
SELECT Employee, eCode, Count(*) cnt FROM Test
Group By Employee, eCode
Order by Employee, eCode

The above SQL gives me following output

David is missing eCode X and Y, which Gary and Tony has. How can I return zero for its count.
Similarly Gary and Tony is missing F and G and I want to get zero for those two.
I can use some help here.

This should accomplish what you're looking for but this assumes all of your eCodes exist at least for one record in your Test table. Otherwise you can replace the Test table instance on the right side of the CROSS JOIN in the CTE below with your source table of eCodes:
WITH CTE_EmployeeECodes AS -- Gets a distinct list of all Employee and eCode combinations
(
   SELECT DISTINCT E.Employee, EC.eCode
   FROM Test AS E
   CROSS JOIN Test AS EC
)

SELECT EEC.Employee, EEC.eCode, SUM(CASE WHEN T.eCode IS NULL THEN 0 ELSE 1) END AS cnt -- Sum up the number of eCodes per Employee, counting NULLs as 0
FROM CTE_EmployeeECodes AS EEC
LEFT JOIN Test AS T -- Left join allows us to keep all records from our source Employee & eCode combinations CTE, but T.eCode will be NULL for the eCodes an employee has 0 instances of
    ON EEC.Employee = T.Employee
    AND EEC.eCode = T.eCode
Group By EEC.Employee, EEC.eCode
Order by EEC.Employee, EEC.eCode


Postgresql performance issues when issuing many small inserts and updates one at a time I have a production web server with postgresql database.

My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in an insert/update to the postgresql database.

In order to avoid overloading my server, the requests are queued and handled one at a time.

So typically my server is doing (not many) reads from the database most of the time, and every hour on the hour it runs many inserts and update, one after the other.

The problem I'm experiencing is that once the updates/inserts start, the performance of the database gets very bad for a couple of minutes and then, even though the updates/inserts continue, the performance gets better and stay at a good level until the updates/inserts are finished.

I asked around and got some good directions that may explain the degradation in the performance of the webserver itself, but since I'm seeing that the database's performance also degrades, there might be more to this.

What can explain this behaviour? How can I fix this?

In order to avoid overloading my server, the requests are queued and
  handled one at a time.

That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details there may be transaction overhead and even connection overhead, too.

Try to run multi-row INSERT / UPDATE commands instead. Or use COPY, or one of the fastest options would be to define a foreign table and fetch all data from a remote DB directly in a single SQL command (if that's at all possible).

Or depending on details, some other replication method may fit your needs.

This is how you update multiple rows at once:

Update multiple rows in a table from another table when condition exists

And here is how to do that from a CSV file:

How to update selected rows with values from a CSV file in Postgres?


Postgres reindex on crash folklore I'm struggling with a problem of folk memory re Postgres.

The folklore

I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered from an issue where if the machine holding the server failed (e.g. kernel crash), when the machine was restarted, Postgres would start but the indexes on the database might be corrupted. For instance

SELECT foo FROM bar WHERE baz = 123

would fail because the index on bar.baz would be corrupted. Reindexing the table fixed the issue but as there was no way to detect this issue, and this was in an appliance environment (no DBA on hand), in essence to guard against this it would be necessary to reindex all tables on a reboot, which would be prohibitively expensive. Thus (the reasoning goes) without a DBA on hand it would not be sensible to use Postgres particularly in an environment where tables were large and computing indices expensive.

Because of this folklore, MySQL is preferred in such circumstances.

My theory

I dimly recall seeing this issue happening under 8.4, so I think there's some truth in it.
I am assured this also has happened on Postgres 9.3 under Ubuntu 14.0.4. I don't know whether it happens 'frequently'. The system it happened on is running with (admittedly old) enterprise grade SAS drives which claim write caching is turned off, so I don't think this is a write caching problem.
Were this still the case, I think I'd be able to find more evidence of it on the web than I can find.
Thus I suspect it may be fixed, or may be a result of something bad in postgresql.conf (though inspection suggests only the autovacuum settings are changed from the defaults).

Further information

In the system I know did this recently (using 9.3) no LVM is in use. The storage system is ext4 on a physical drive mounted with default attributes. This drive is an (admittedly old) enterprise SAS drive plus SAS1068 PCI-X Fusion-MPT SAS controller which sdparm claims it doesn't have a write cache switched on, but this has (allegedly) been seen on multiple drives with different vendors etc., though always on ext4 on Ubuntu.
fsync is in the default state (on I believe).
No master/slave configuration  - just a single master - (so it's not the multixact bug).
Tables are created with default indexing (no fancy hash indexes).
Postgres regularly updated to whatever the relevant Ubuntu LTS release has. The latest problem was on 14.04.

Questions

Is manual rebuilding of indices an expected task after a system crash?
If not, has it ever been, and when was that changed?
Is it possible to avoid the need for manual reindexing?

TL;DR: Except for a specific bug in PostgreSQL 9.3 prior to 9.3.4 which it's vital that you patch, index corruption after crash is never expected and hasn't been since PostgreSQL 7.1 was released in 2001.

The advice/folklore you got was wrong.

  I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered from an issue where if the machine holding the server failed (e.g. kernel crash), when the machine was restarted, Postgres would start but the indexes on the database might be corrupted.

That way predates 8.4 unless you're using hash indexes, which are documented not to be crash safe and are not recommended.

WAL logging of indexes was introduced in the deep dark distant past of mid-2001. PostgreSQL has been crash safe for an exceedingly long time. That advice was wrong.

MySQL switched to crash-safe by default (by defaulting to InnoDB tables) in MySQL 5.5, in 2009. Prior to that its default storage engine, MyISAM, isn't crash-safe and will happily corrupt indexes and tables. So your folklore isn't only wrong, it's backwards.

If you had corruption after a crash under 8.4, then either:

you hit a bug (not impossible, but not likely either) in PostgreSQL, the kernel, the filesystem, etc;
You were using LVM before write-barrier support was fixed, and had barriers enabled on your file system;
your storage subsystem lied to the kernel about fsync(); or
you disabled crash safety by setting fsync=off.

There have been a couple of index-related bugs over time, but all the others I know of have been minor and/or hard to trigger, and few have been related to crash recovery.

The issue with 9.3 is different. That's a very specific bug that we're all rather embarrassed about. It occurred when a replica PostgreSQL server was promoted to a master after certain other operations had happened in a particular order. It's a really bad bug because it affects the table contents, not just indexes, so it's vital that you patch to 9.3.5 or higher. To learn more about it than you ever wanted to search for "multixact bug".

So:

  Is manual rebuilding of indices an expected task after a system crash?

Not unless you are using hash indexes, which the manual specifically wants you about.

  If not, has it ever been, and when was that changed?

PostgreSQL 7.1, back in 2001.

If you read the release notes you'll find the occasional bug and bug fix, but other than the aforementioned multixact bugs in 9.3, nothing too serious.

  Is it possible to avoid the need for manual reindexing?

Do nothing. Congratulations, you don't need manual reindexing.

Simply:

Leave fsync set to on, the default
Leave full_page_writes set to on, the default
Don't kill -9 the postmaster but no other processes then delete postmaster.pid and start PostgreSQL again. The latest releases even detect it if you do this and try to prevent damage.
Don't explicitly specify hash indexes when creating an index. PostgreSQL will never use a hash index unless you ask it to.

Bonus points if you:

Keep PostgreSQL reasonably up to date, i.e. apply point release patches
Use decent quality SSDs if you're going to use SSDs, otherwise any database will be prone to corruption. Make sure they have a supercapacitor or other power-loss protection.


"Network connection was interrupted" error with shared Access database We have an Access database with a split front/back end. The back end resides on a Samba 4.1.6 share with around 15-20 users. Frequently the database will popup with the error "Network access has been interrupted". Obviously this is not ideal.

When we move the back-end to a Windows home server share the issues do not persist, so as far as I'm aware this rules out any hardware issues within the network itself.

Has anyone else encountered the same issue? Or anybody have any ideas?

Has anyone else encountered the same issue?

Yes. The Access Database Engine is designed to work with "real" Windows file sharing and Samba – or at least some versions of Samba – apparently do not implement all of the low-level features of the SMB protocol that are needed for the Access Database Engine to work reliably.

(ref: Corrupt Microsoft Access MDB Causes - Samba)


Database Replication SQL Server We have to do database replication with SQL Server; the two database are on different servers.

We achieve replication, but our scenario is when live is down so we use replicated server as our production server.

Here is where the problem occurred: when we insert anything on replicated server from application it fails because of violation of a primary key constraint.

And yes, it violates b/c on replicated server primary key starts from 1

How to overcome this problem? Or it is correct way to convert replicated server to production server?

In case of aligning identity columns, run this on the source:

 SELECT
        IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS Seed,
        IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS Increment,
        IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS Current_Identity,
        TABLE_SCHEMA+'.'+TABLE_NAME as table_name,
        'DBCC CHECKIDENT('''+TABLE_SCHEMA+'.'+TABLE_NAME+''', RESEED,'+CAST(IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) +1  AS VARCHAR(10))+')' as Command
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME not like 'Mspeer%'
    AND TABLE_NAME not in ('sysarticles','syspublications')
    ORDER BY TABLE_SCHEMA, TABLE_NAME

The code to run on the replica is in a column


Unrolling a self-referencing table referred to by another table...? What's a proper way to "unroll" a self-referencing table with a variable depth and concatenate the levels with an associated value from another table?

table 1: FILENAME

filename      folder_id  size
-----------------------------
toplevel1.txt 1          1
onedeep1.txt  2          2
twodeep.txt   3          4
toplevel2.txt 1          4
onedeep2.txt  4          3

table 2: FOLDERS

folder_id  folder_name  parent_folder_id
----------------------------------------
0          ROOT         NULL
1          temp         0
2          subdir1      1
3          subsubdir    2
4          subdir2      1

Desired output:

path                                size
----------------------------------------
temp\toplevel1.txt                  1
temp\subdir1\onedeep1.txt           2
temp\subdir1\subsubdir\twodeep.txt  4
temp\toplevel2.txt                  4
temp\subdir2\onedeep2.txt           3

Have tried (and failed) using while loops, ctes, and cursors.  Seems relatively straight forward, but I can't get a handle on the variable depth of the paths...

Use a recursive CTE to expand the folder names to full paths:

WITH
  folderpaths AS
  (
    SELECT
      folder_id,
      folder_name,
      parent_folder_id,
      folder_path = CAST(folder_name AS varchar(1000))
    FROM
      dbo.Folders AS f
    WHERE
      parent_folder_id = 0

    UNION ALL

    SELECT
      c.folder_id,
      c.folder_name,
      c.parent_folder_id,
      folder_path = CAST(p.folder_name + '\' + c.folder_name AS varchar(1000))
    FROM
      dbo.Folders AS c
      INNER JOIN folderpaths AS p ON c.parent_folder_id = p.folder_id
  )
...

For your example, the CTE will produce this output:

folder_id  folder_name  parent_folder_id  folder_path
---------  -----------  ----------------  ------------------
1          temp         0                 temp
2          subdir1      1                 temp\subdir1
4          subdir2      1                 temp\subdir2
3          subsubdir    2                 subdir1\subsubdir

As you can probably guess now, you can just join the Filename table to the CTE and use its folder_path column to get the path column of your output:

WITH
  folderpaths AS
  (
    SELECT
      folder_id,
      folder_name,
      parent_folder_id,
      folder_path = CAST(folder_name AS varchar(1000))
    FROM
      dbo.Folders AS f
    WHERE
      parent_folder_id = 0

    UNION ALL

    SELECT
      c.folder_id,
      c.folder_name,
      c.parent_folder_id,
      folder_path = CAST(p.folder_name + '\' + c.folder_name AS varchar(1000))
    FROM
      dbo.Folders AS c
      INNER JOIN folderpaths AS p ON c.parent_folder_id = p.folder_id
  )
SELECT
  path = fp.folder_path + '\' + fn.filename,
  fn.size
FROM
  dbo.Filenames AS fn
  INNER JOIN folderpaths AS fp ON fn.folder_id = fp.folder_id
;

This is the output:

path                             size
-------------------------------  ----
temp\toplevel1.txt               1
temp\subdir1\onedeep1.txt        2
subdir1\subsubdir\twodeep.txt    4
temp\toplevel2.txt               4
temp\subdir2\onedeep2.txt        3

A live demo of this solution is available at Rextester.


Trigger to prevent update if existing value is higher than inserted value I need a trigger that will prevent from updating the price if the existing value is higher than the inserted value. If such operation transaction appears, the trigger should prevent value update but let transaction end (i.e. not roll it back). The one below doesn't work -- it doesn't let the update happen even if inserted value is higher than existing.
CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger]
ON [dbo].[Prices]
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

IF UPDATE(PRC_Value) AND EXISTS (SELECT *
                                 FROM deleted d
                                 JOIN inserted i
                                     ON d.PRC_PrcId = i.PRC_PrcId
                                  WHERE d.PRC_Value > i.PRC_Value)
SELECT NULL

SET NOCOUNT OFF

Regards,
Przemek

You are using INSTEAD OF UPDATE, this means that each update will end in the trigger.

You can add an ELSE statement to do the update if the first IF is not true.

CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger]
ON [dbo].[Prices]
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

IF UPDATE(PRC_Value) AND EXISTS (SELECT * FROM deleted d JOIN inserted i ON d.PRC_PrcId=i.PRC_PrcId WHERE d.PRC_Value>i.PRC_Value)
SELECT NULL
ELSE
UPDATE P
SET PRC_Value = i.PRC_Value
FROM dbo.Prices P
INNER JOIN inserted i
ON i.PRC_PrcId = P.PRC_PrcId;

SET NOCOUNT OFF;

Testing

CREATE TABLE dbo.Prices(PRC_PrcId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                        PRC_Value numeric(5,2));

INSERT INTO dbo.Prices(PRC_Value)
VALUES(1.2)

GO

Lower Price

UPDATE dbo.Prices
SET PRC_Value = 0.5;

Result

(No column name)
NULL

Higher price

UPDATE dbo.Prices
SET PRC_Value = 1.5;

Result

PRC_PrcId   PRC_Value
1   1.50


How do I list the database files contained in a SQL Server 2005 backup file using T-SQL? How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?

For instance:

The restore process will create:

bob.mdf
bob.ldf

etc...


Configuring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign table Project Details:

MS SQL 2008 R2

I have already asked what amounts to one half of the question here, but I am hoping someone in this forum can answer the other half, involving MS SQL server specifically.

So I am doing a code-first ASP.NET MVC 5 project, and there is a business requirement that involves two very specific types of foreign keys to lookup tables. Since these lookup tables must always be loaded with a default value already selected, there is one value in these lookup tables that will always have a Guid.Empty value (00000000-0000-0000-0000-000000000000) as its primary key, so that it can be easily identified as the default value.

I am trying to find a way to have the foreign keys that make use of these two lookup tables to default to an empty Guid if they are passed a null value. This is added insurance just in case I miss a spot in the code itself where I am supposed to replace a null value with an empty Guid, but don’t. Plus, in this project in particular there will be scheduled weekly imports from an external database, and I want to be able to have all imported data have this default value in its foreign key without having to do extra work. Having the foreign key automatically assume this empty Guid as a default value when passed a null value would be very handy from several different angles.

And yes, I use Guids as uniqueidentifier GUIDs, not as nvarchar(128) strings. These foreign keys are non-nullable uniqueidentifier fields.

My other question tried to focus on the MVC Code-First migration itself, but I cannot wait around for an answer in terms of this project (although it will be useful for future projects), so this question on this forum involves the MSSQL server itself. I would like to know how to modify a foreign key uniqueidentifer field so that it will automatically use a null guid (00000000-0000-0000-0000-000000000000) when passed a null value. I have already tried adding (00000000-0000-0000-0000-000000000000) and ((00000000-0000-0000-0000-000000000000)) to the Default Value or Binding property for the foreign key, but both attempts came back with “Error validating the default for column 'column'. Do you want to cancel your changes?”

I am hoping that someone might be able to help me to add a default value to a non-nullable uniqueidentifier foreign key.

To add a default is easy:

CREATE TABLE dbo.t
( tid INT PRIMARY KEY,
  ggid UNIQUEIDENTIFIER NOT NULL
      DEFAULT '00000000-0000-0000-0000-000000000000'
 ) ;

Then the following insert will work:

INSERT INTO dbo.t
  (tid)
VALUES
  (1), (2) ;

Test at rextester.

If you want NULLs to not raise errors but be converted to the default, I think the easiest way is by using an INSTEAD insert trigger on the table. Something like this:

CREATE TRIGGER t_replace_null_guid_with_default
ON dbo.t
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.t (tid, ggid)
    SELECT tid, COALESCE(ggid, '00000000-0000-0000-0000-000000000000')
    FROM inserted;
END ;


MSSQL: Export Wizard, not possible to save SSIS Package short question, is there a problem while trying to save a SSIS Package from the export wizard when the management studio is version 2016 and the SQL server is version 2014?

Errormessage is:

  In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

SSMS Version: 13.0.15600.2
SQL Server Version: 12.0.4416.0

License Information via:

  SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Output:

  12.0.4416.0, SP1, Enterprise Edition: Core-based Licensing (64-bit)

If I try the same on the server, with ssms 12.0.4416.0, there isn't any problem.

Thank you in advance.

edit: found the solution:
https://msdn.microsoft.com/en-us/library/mt238486.aspx

  SSMS can only connect to SQL Server 2016 Integrated Services (SSIS 2016) instances.
  There is a known compatibility limitation with SQL Server Integration Services that prevents connecting to previous versions.
  As a workaround for this problem, you can connect to your SQL Server Integration Service instance by using the SSMS release aligned with your SSIS instance.


What does wait_resource LOG_MANAGER mean in SQL Server 2008? Let's say we have two Stored Procedures A & B within the same database.

Stored Procedure A:

Executes for a long time
Has transactions within
Makes Calls over Linked Server

Stored Procedure B:

Simple SP that inserts/updates a table
Quick. Within 1 second

A & B execute on the same database but DO NOT have any common tables.

Yesterday, B was blocked by A and I found that the

wait_type was LATCH_EX
wait_resource was LOG_MANAGER

What does this LOG_MANAGER mean? What kind of resource are they waiting on? Plus, does it have anything to do with with transactions within the 'A' SP.

A quick Googling indicates that it has something do with growth of logs, but I would appreciate a more indepth explanation.

Whenever the log fills up it has to grow. See How to shrink SQL Server log for an explanation how it grows. Any transaction could be the one that triggers growth, simply because any transaction could be the one that happened to take the very last unused byte in the existing file. When growth occur all transaction block. And if you have default 10% auto-growth in place and your database log already grew out- of check then the log growth can be very slow as instant initialization is not possible on log files.

What matters is that you have a log growing, so you must take immediate measures to understand why is growing and to mitigate accordingly. Go read Factors That Can Delay Log Truncation right now. The fact that you caught B in the fact of being blocked by A could be just random. B could had just as easily trigger the growth, it would had taken exactly the same time to grow it, and you would have catch A being blocked by B. The fact that A is causing a long running transaction may be one of the factors that prevent truncation, but that has to be confirmed.


Oracle XE installation aborts with no error explanation I have downloaded Oracle XE 18c for Windows 64 bit, and tried to install it on a Windows 10 machine. I checked the SHA-256 checksum and it's correct.

The installation starts fine, but when it's copying new files it suddenly says "Rolling back installation", then ends and there's no error log to look at (there's no even C:\Program Files\Oracle folder, where Oracle error logs are stored). Any ideas?

System Requirements

  Oracle Database XE for Windows x64 is supported on the following
  operating system versions:

  Windows 7 x64 - Professional, Enterprise, and Ultimate editions
  Windows 8.1 x64 - Pro and Enterprise editions
  Windows 10 x64 - Pro, Enterprise, and Education editions
  Windows Server 2012 x64 - Standard, Datacenter, Essentials, and Foundation editions
  Windows Server 2012 R2 x64 - Standard, Datacenter, Essentials, and Foundation editions
  Windows Server 2016 x64 - Standard, Datacenter, and Essentials editions

Home edition is not supported for Oracle databases and is known to have issues, like here:

Oracle 11g setup closes without errors on pre-requisite check


Displaying number before decimal points in oracle I have a logic where I want to calculate and display numbers based upon some operations. The operations are below
V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100),
So I want to calculate V_CALPERCENT and I have one value for v_yearMSA1 = 2
SO here goes my calculation,
V_CALPERCENT :=  (v_yearMSA1  * 2.25) / 100

Its returning me as .05
where as I want it should also display the number before decimal point. How to get it please help

Example:
SQL> select to_char((2 * 2.5) / 100) from dual;

TO_
---
.05

SQL> select to_char((2 * 2.5) / 100, '99990D99') from dual;

TO_CHAR((
---------
     0.05

SQL>

Update:
Number example:
SQL> select (2 * 2.5) / 100 as mynum from dual;

     MYNUM
----------
       .05

SQL> col mynum format 99990D99
SQL> select (2 * 2.5) / 100 as mynum from dual;

    MYNUM
---------
     0.05

SQL>

Number Format Models


Assign Failover Cluster and High Availability Always On from Old to New Domain What are best practices steps when the Windows Server 2016 machine hosting SQL Server and failover cluster (2 node and shared folder witness) is joined to another domain?
What steps definitely/might need to be done for SQL Server and failover cluster before and after the new domain is joined?

An important note is to ensure you have databases fully backed up before beginning any migration path, particularly when moving between domains. File permissions, and other problems could result in data loss & require reverting to those backups.
If you are using Windows Server 2019
With Windows Server 2019, migrating a cluster to a new domain is supported via a straightforward process documented here.
Given most folks are still on 2016 and older
With Windows Server 2016 & older, the migration process involves destroying the cluster, and rebuilding it on the new domain. Given the need to destroy & re-create the cluster, I recommend uninstalling before & reinstalling after the SQL Server installation. You will want to have good backups before the migration as well.
The instructions to move a Windows Server Failover Cluster to a new domain are included in the docs, and quoted below.
Build a fresh cluster
Building a fresh cluster & migrating to it is going to be your best option. (And moving to an Availability Group at the time would be my preferred path, too!)
If you don't have new or extra hardware to build new, you can do something like this to reuse existing servers:

Evict a node from your existing FCI.
Uninstall SQL Server from that node.
Remove the server from the domain & add it to the new domain.
Build the new single-node Windows cluster on the new domain.
Install the FCI on the single-node cluster.
Unmount storage from cluster in the old domain.
Mount storage to the cluster in the new domain.

Note: Be prepared to encounter NTFS permissions issues on your disks. Permissions will be assigned to users/groups on the old domain.
Alternatively, you could assign fresh storage on the new domain & move databases via backup/restore or log shipping.

Attach your databases.
Repeat steps 1-3 for remaining nodes.
Add remaining nodes to the FCI on the new node.

Documented Cluster Migration steps:

The steps in this article enable the Cluster service to start in the new domain. However, you may be unable to bring the resources online in the new domain, and the resources that can be brought online may not work correctly.
To move the cluster:

Create a user account for the Cluster service in the new domain. You must make sure that no Group Policy objects (GPOs) or security template requirements remove any of these rights. The user account must have the following rights:

Lock pages in memory.
Log on as a service.
Act as part of the operating system. (Windows 2000 and Windows Server 2003)
Back up files and directories.
Increase quotas.
Increase scheduling priority.
Load and unload device drivers.
Restore files and directories.
Adjust memory quotas for a process (Windows Server 2003).
For more information about the Cluster service account, click the following article number to view the article in the Microsoft Knowledge Base:

269229 How to manually re-create the Cluster service account
In addition, the Cluster service account must have administrative permissions on all nodes in the cluster.

Set the Startup value for the Cluster service to Manual on all nodes in the cluster:
a. Click Start, point to Settings, click Control Panel, and then double-click Services.
b. Click Cluster Service, and then click Startup.
c. Change the Startup Type from Automatic to Manual.
d. Click OK.

Stop the Cluster service on all cluster nodes:

Click Start, point to Settings, click Control Panel, and then double-click Services.

Click Cluster Service, and then click Stop.

Turn off all nodes except one.

Move the node into the new domain by using procedures that are appropriate to your operating system. Complete the process, and then restart the node.

On the node, change the service account that is used by the Cluster service to log on to the domain to the user account that you created.

Start the Cluster service on that node.

10.Use Cluster Administrator to verify that there are no issues. Try to bring all resources online. Test the functionality of all resources from client computers, and then check the Event Viewer System log for error messages.

⚠ Note
At this point, you can still cancel the move by moving this node back into the old domain and starting the nodes that are not moved.

If the first node move is successful, continue to migrate the other nodes in the cluster to the new domain starting with step 5 for each node.


Shortcuts/Snippets to select a block of code I am using SQL Server Management Studio 2016 on Windows 10. I'm tired of using shift/ctrl+up/down/right/left keys to select the code I want to run. I'm wondering whether there are shortcuts/snippets to select a block of code that separated from other code by blank lines?

Here is a code example:

select *
from tab1

select *
from tab2

select *
from tab3

Say, my cursor is inside the middle block and what's the best way to select the middle block?

Using Autohotkey, I was able to develop a solution to select a block of code and here is the script I have:

!b::

send ^f

sendraw ^\r$

send {F3}

send +{F3}

send {Esc}

send {down}

send ^{=}

return

Specifically, pressing Alt + B will invoke this script, and the script will select the block of code where the cursor is in. Note that the block of code has to be separated from other blocks of code by blank lines, and the block of code cannot have blank of lines inside itself.

The key steps of the about script are

using regular expression "^\r$
" to locate the blank lines above and below the cursor
moving the cursor to these two blank lines one by one
using ctrl + = to select the block of code between these two blank lines.

Note that !/^/+ are alt/ctrl/shift separately.


UPDATE with CASE with 2 tables I have 2 tables and I want to update table2 with criteria of table1 and table2.
The criteria are:

1.table1.amount > 10000

2.table2.flag is null or table2.flag = '0'

So if the 2 conditions are true, then update table2.flag = '1' and table2.flagdate = getdate()

table2.flag is a flag to check everyone who pays more than 10,000. But if already flagged, I don't want to flag again because I can't loose the original table2.flagdate. But I try a lot of things, nothing works the way I want. Any ideas?

UPDATE t2
  SET flag = 1, flagdate = CURRENT_TIMESTAMP
  FROM dbo.table2 AS t2
  INNER JOIN dbo.table1 AS t1
  ON t2.id = t1.id
  WHERE t1.amount > 10000
    AND (t2.flag IS NULL OR t2.flag = 0);

You can avoid the OR condition if you change the flag column to NOT NULL with default 0.


Can Indexes mitigate untrusted Constraint I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with:

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName;
GO

But got this error message

  Msg 547, Level 16, State 0, Line 1
  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_importsessionrow_importsession". The conflict occurred in database "DatabaseName", table "dbo.importsession", column 'importsessionid'.

So I went and talked to the developers team and they told me that this ok and its how it should look like. Then I just inform them that it could have a huge performance impact on queries. So the question I went back with from Developer team is:

  Can this be mitigated with indexes?

I would be very pleased if you can help me answer the Dev team

Regards
Håkan

Just for a bit of background on pros/cons to not having FK's in your database feel free to review this post on StackOverflow.  As you can see from that article there are some good reasons (performance penalty on insert/update more painful to the business than dirty data) to go without foreign keys and some bad reasons (laziness/ignorance).

I think you should figure out if the Dev team has valid reasons why they don't want FK's or if they are just being lazy.  Based on the information you've provided it doesn't seem like they have a well articulated reason why they don't care about the bad data.

As @AaronBertrand hinted if the Dev team is adamant that FK's shouldn't be enforced, AND business doesn't care about dirty data then you should probably remove the FK constraint.  If the business cares about dirty data and the dev team doesn't then that is a whole 'nother ball of wax.  Keep in mind, you can still use indexes to make queries faster regardless of whether you have FK relationships  between your tables.  You just won't get the performance benefits of a FK.  In general terms this is like how a non-covering nonclustered index scan is faster than a table scan, but a covering nonclustered covering index scan is even faster.

In summary here is your plan of attack:

Get a real answer why the Dev team doesn't want FK's to be enforced.
.....
Make your queries faster.