In a two-server dataguard setup, can I store the recovery catalog in the same database? We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, via Bacula, to more disks and to tape, but rman doesn't know this).

I'm planning on adding a physical standby database using Data Guard Redo Apply. The Data Guard and and RMAN documents all tell me that I must use a recovery catalog now. I don't actually, so far as I can tell need any of the features of a recovery catalog. The control file was fine. I don't need to offload backup from the primary database; it has more than enough capacity to do them itself (and the workload basically guarantees that will always be the case).

So, I would just put it in the same database (the one I'm backing up with rman), but those same documents tell me I mustn't, but they only reason I've found so far is:

  To illustrate why data separation is advised, assume that you store the catalog for database prod1 in prod1. If prod1 suffers a total media failure, and if the recovery catalog for prod1 is also stored in prod1, then if you lose the database you also lose the recovery catalog.

But this isn't true. The recovery catalog would still exist on the standby. And even if both the primary and standby broke simultaneously (say, due to a madman at a sqlplus prompt, but that'd break a recovery catalog in a separate database, too), they say:

  At this point the only option is to restore an autobackup of the control file for prod1 and use it to restore and recover the database without the benefit of any information stored in the recovery catalog. ("Protecting the Recovery Catalog" from Oracle Database Backup and Recovery User’s Guide 11g Release 2 (11.2), p. 13-15.)

The "only option" of using a control file backup doesn't sound bad at all.

Am I missing something? Are there some non-obvious downsides to putting the recovery catalog in the same database, or is this really only a concern in a much larger operation?

(would tag this recovery-catalog and data-guard as well, but seems I can't with less than 300 rep here)

If I correctly understand your question as: "I want to create a standby database and later on take backups on the primary like if there would be no standby present"

In this case, you do not need a recovery catalog: quote "An RMAN recovery catalog is required so that backups taken on one database server can be restored to another database server. It is not sufficient to use only the control file as the RMAN repository because the primary database will have no knowledge of backups taken on the standby database." See http://download.oracle.com/docs/cd/E11882_01/server.112/e25608/rman.htm#BAJEDHFB


Unable to update table after creating an After update trigger I have a table like this

/*Table: pageprivilege*/
------------------------

/*Column Information*/
----------------------

FIELD   TYPE              COLLATION          NULL    KEY     DEFAULT  Extra           PRIVILEGES                       COMMENT
------  ----------------  -----------------  ------  ------  -------  --------------  -------------------------------  -------
id      INT(10) UNSIGNED  (NULL)             NO      PRI     (NULL)   AUTO_INCREMENT  SELECT,INSERT,UPDATE,REFERENCES
pageid  VARCHAR(20)       latin1_swedish_ci  YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES
roleid  TINYINT(4)        (NULL)             YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES
ad      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
ed      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
dl      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
rd      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
st      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES

I have an condition that, if the rows are updated such that

ad, ed, dl, rd are all set as 0 then the value of st automatically gets changed to 0 too

For that I wrote a procedure

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `smsdev`.`update_priv`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

     UPDATE pageprivilege SET st=0 WHERE ad = 0 AND ed =0 AND dl =0 AND rd = 0;

    END$$

DELIMITER ;

And added a trigger which gets called after the table has been updated and calls the procedure like this

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` AFTER UPDATE ON `pageprivilege`
    FOR EACH ROW BEGIN
     CALL update_priv();
END;
$$

DELIMITER ;

The issue is whenever I make any update to the any of the 4 field, ad, ed, dl, rd I get this error

  Can't update table 'pageprivilege' in stored function/trigger because
  it is already used by statement which invoked this stored
  function/trigger.

I am new to using trigger and stored procedure, I did read that is a bad idea to fire procedure from inside trigger, but this is just like a test of idea for me so if you could just bear with me and help me with this hack-ish code it would be gr8.

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` BEFORE UPDATE ON `pageprivilege`
    FOR EACH ROW
    BEGIN
     if (new.ad = 0 AND new.ed =0 AND new.dl =0 AND new.rd = 0) then
       set new.st=0 ;
     end if;
END;
$$

DELIMITER ;


Do I risk losing the benefits of indexing if I have an index on every column? I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of the columns, e.g.

select * from table where C > 43 and H is not null;
select * from table where A is null and F < 10 and F > 1 and X > 2;

For performance I plan to create an index on every column of the table. Some feeling in my stomach tells me to ask the experts first: Is it good design for the above described use case to create an index on every column?

UPDATE:
I have to speculate about real use cases. I can't measure the exact queries yet. This is in design phase.

The server is well equipped with RAM and SSD storage, so queries are already "fast" now, and I can feel the effect of caching when I fire similar queries in sequence.

The columns are of types double, integer, timestamp and geometry (which explicitly gets a 'gist' index).

The queries will include from 1 to 10 columns. Usually ~6. Results will usually be <20k rows. Queries on a column will never relate to another column.

Thanks for all the explanations. What I will do:
 * select 1/4th of the columns that I think will be most used and create indexes.
 * wait for more testing/usage and start measuring/analysing the queries and use-cases then.

Thank you

When you say "for performance" you're taking a narrow view - "performance" as SELECT performance only, one query at a time, without considering caching and I/O contention issues.

Yes, having an index on every column might improve read performance. It'll certainly slow write performance (INSERT, UPDATE and DELETE), though that's not a concern for your DB. More subtly, more indexes means more contention for space in RAM to cache the indexes, which means there's a greater chance that a scan of any given index will result in slow disk reads.

Then there's the fact that you can index more than just individual columns. It's often most productive to create multicolumn, partial, expression and/or descending indexes to best satisfy the needs of your queries. You simply cannot create every possible index. Here's a recent example of a real world index you'd never create without analysis of the queries that required it:

CREATE INDEX contfloattable_tag_and_timeseg
ON contfloattable(tagindex, (floor(extract(epoch FROM dateandtime) / 60 / 15) ));

So: you could create an index on every column, but it's a bad idea. Use EXPLAIN AND EXPLAIN ANALYZE, possibly via the auto_explain module, to analyse your workload and queries, and make indexing decisons based on how you actually query the tables. An index you don't use will hurt you in a read/write environment, and won't do any good in a read-only environment.

Don't view queries in isolation. Look at patterns. If query1 seems to want an index on col1 and query2 seems to want an index on both col1 and col2, don't bother creating an index on just col1. Create a composite index on col1, col2; it'll be almost as fast for a search on col1 alone and way better than maintaining two indexes and having them fight over cache space and disk I/O.

BTW, this sounds like it's more of an OLAP workload than an OLTP one, so you might want to consider schemas designed for OLAP workloads like star schema with fact and dimension tables.


Representing a Superclass/Subclass (or Supertype-Subtype) relationship in a MySQL Workbench diagram Situation
I am trying to figure out how to represent a one-to-one (1:1) relationship regarding a superclass/subclass or supertype-subtype structure in an entity-relationship diagram (ERD) of the kind created by means of MySQL Workbench (which, as you know, looks more close to a concrete SQL implementation than an original P. P. Chen diagram).
I am new to this, so I am not sure whether I have to type out all the attributes1 again in the subclass entities (or subtypes), i.e., MANAGER and DRIVER, or whether I there is a way of connecting to and grabbing the attributes from the superclass (or supertype) so that it is more clear that they relate, something like a FOREIGN KEY, I guess.
1. Each attribute in EMPLOYEE applies to MANAGER and DRIVER, but MANAGER and DRIVER will have some extra attributes that are not characteristics of the EMPLOYEE entity type (or table, once it is implemented).
Current diagram
Bellow is the ERD I have created so far:

Question
So, how can I represent accurately this sort of relationship in my diagram?

This is a case of what's called "generalization/specialization" in ER lingo.  It's really the same thing as what object models call "superclass/subclass" as you have done.  There are two separable issues.  The first is how do you want to draw the diagram, and the second is how do you want to design the tables.

As far as drawing the diagram goes, I would draw it the same way you have, with one exception.  The lines leading out of Managers and Drivers would not go separately to Employees.  Instead, they would go to something I'll call a gen/spec box.  The following sample shows one way to depict a gen/spec case.  Then a single line connects the gen/spec box with the Employees table.
It all depends on which way seems simpler and clearer to you and to others who look at the diagram.

As far as tables go,  you could have three tables, just as you have shown.  This is sometimes called "class table inheritance".  There is a technique you could benefit from, called "shared primary key".  In this technique, the manager table and the driver table do not have an id field of their own.  Instead, there is a copy of Employee_id in each subclass table, and employee_id is declared to be both the primary key of this table and also a foreign key that references Employees.

Using shared primary key buys you a few things and it costs you something.

It buys you enforcement of the 1:1 limit on manager-employee or driver-employee. It may end up speeding the resulting joins.  And it allows a fourth table that uses employee-id as a foreign key to be joined directly to Drivers or Managers.   The irrelevant employees drop out of the join.

What it costs you is that you have to obtain and use a copy of the right employee_id every time you go to insert a driver or a manager.

You could also just pack driver attributes and manager attributes into the employee table, leaving irrelevant fields NULL.  That results in fewer joins, but it does have a downside.  It makes the employee table slightly bigger and slower.  And if you use nullable fields in WHERE clauses, you'll have to learn how SQL does three valued logic  (True, False, and Unknown).  For some people, this is awfully difficult to grasp.  I try to avoid three valued logic myself.


Full and differential restore with two backup files - SQL Server 2008 We are trying to establish a maintenance plan with 2 separate backups.

One full backup over the weekend and differential backups through the week. [the differential backup has a "_diff" appended to its name].

The backups are supplied to us by an external entity we use for processing orders.  My problem is with restoring the databases.

1: I tried the method here: http://msdn.microsoft.com/en-us/library/ms175510.aspx#TsqlProcedure but, I can't seem to "add" a backup file to the existing backup device on the server that contains the full backup.

2: I tried restoring them as 2 separate backups but I get this error:

The backup set holds a backup of a database other than the existing 'DBXXX' database. [possibly due to the _diff name associated with the .mdf and .ldf files]

How do I go about restoring the differential backups in this scenario.  Can I add the differential backup to the existing full backup device?  Is there another way out?

Edit:

Added T-SQL statements:

Restoring the full backup

RESTORE DATABASE DB_TEST FROM DB_TEST_Device WITH REPLACE, NORECOVERY;
GO

Restoring the diff. backup

RESTORE DATABASE DB_TEST FROM DB_TEST_Device_Delta WITH RECOVERY;
GO

**Full Backup Device:**
OM_00987_Backup_987 D:\SQLdata\OM_00987_Backup_987.mdf  D   PRIMARY 32852082688 3.51844E+13 1   0   0   BAC0AD62-9CB6-46FA-8600-7852009C638A    0   0   32850247680 512 1   NULL    1.33E+17    2B366F37-7D81-4BD1-AA3C-DD9757EFF075    0   1   NULL

OM_00987_Backup_987_log    E:\SQLlogs\OM_00987_Backup_987_log.LDF  L   NULL    516096  2.19902E+12 2   0   0   F76787F2-521B-4A75-A2F6-8977DB88E0A9    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL

**Delta Backup Device:**
OM_00987_Backup_987_Delta   D:\SQLdata\OM_00987_Backup_987_Delta.mdf    D   PRIMARY 9633464320  3.51844E+13 1   0   0   E75C765B-ECEC-46AC-B8AE-5F1EB7D3C724    0   0   9629138944  512 1   NULL    1.36E+17    8C66A6C8-DE57-4B57-B0E1-DE8F3C64E68A    0   1   NULL

OM_00987_Backup_987_Delta_log  E:\SQLlogs\OM_00987_Backup_987_Delta_log.LDF    L   NULL    516096  2.19902E+12 2   0   0   FC843B68-BC7C-478A-BE4E-D4AA4D61B30E    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL

You will need to set up a separate backup device for the FULL and the DIFF.  When you create the Backup Device it is a single file.  You can't save a FULL and a DIFF in the same Backup Device.

More information about backup devices can be found here: http://msdn.microsoft.com/en-us/library/ms179313.aspx

If you are trying to restore the database over an existing database and want to replace the data you will need to use the WITH REPLACE option in the restore.  More information can be found here: http://msdn.microsoft.com/en-us/library/ms186858.aspx

  REPLACE should be used rarely and only after careful consideration.
  Restore normally prevents accidentally overwriting a database with a
  different database. If the database specified in a RESTORE statement
  already exists on the current server and the specified database family
  GUID differs from the database family GUID recorded in the backup set,
  the database is not restored. This is an important safeguard.

  The REPLACE option overrides several important safety checks that
  restore normally performs. The overridden checks are as follows:

  Restoring over an existing database with a backup taken of another
  database.

  With the REPLACE option, restore allows you to overwrite an existing
  database with whatever database is in the backup set, even if the
  specified database name differs from the database name recorded in the
  backup set. This can result in accidentally overwriting a database by
  a different database.

  Restoring over a database using the full or bulk-logged recovery model
  where a tail-log backup has not been taken and the STOPAT option is
  not used.

  With the REPLACE option, you can lose committed work, because the log
  written most recently has not been backed up.

  Overwriting existing files.

  For example, a mistake could allow overwriting files of the wrong
  type, such as .xls files, or that are being used by another database
  that is not online. Arbitrary data loss is possible if existing files
  are overwritten, although the restored database is complete.


Risks regarding simple recovery model and importance of log file backup We have some databases on our server with 'Simple' recovery mode. We are using a third party application for the whole server backup every 30 minutes. We are also taking SQL server database backups twice a day (plan).
My question is

When we take SQL database backup with Simple recovery mode and restore it on a test server, is it correct that we have a blank log file on the test server but the latest database file?
In our current backup scenario, what is the benefit of Full recovery mode as we already have replication every 30 minutes and our RPO is agreed as 1 hour for every client?
I am not a DB admin and probably that is why I have never come across a situation where I had to use SQL server database log file. Whenever anything happened I have always restored the database to the last taken backup which is around 30 minutes old. Can anyone highlight if I need to improve on this point?

I'm just a learner and any guidance / advice is much appreciated.

When we take SQL database backup with Simple recovery mode and restore it on a test server, is it correct that we have a blank log file on the test server but the latest database file?

Yes, that's normal.  When using the Simple recovery model, the transaction log isn't used for recovering the database (for instance, during a restore).  It's essentially just a performance optimization.  Data changes are written efficiently to the log first, and modified data pages are written back to the data files periodically during a process called a CHECKPOINT.

In our current backup scenario, what is the benefit of Full recovery mode as we already have replication every 30 minutes and our RPO is agreed as 1 hour for every client?

If that meets your RPO needs, then log backups are really unnecessary for your situation.
However, it's important to make sure that these every-30-minute, full server backups are "SQL Server aware."  See Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment

SQL Server supports virtualization-aware backup solutions that use VSS (volume snapshots). For example, SQL Server supports Hyper-V backup.
Virtual machine snapshots that do not use VSS volume snapshots are not supported by SQL Server. Any snapshot technology that does a behind-the-scenes save of a VMs point-in-time memory, disk, and device state without interacting with applications on the guest using VSS may leave SQL Server in an inconsistent state.

They should be freezing I/O, otherwise the backed up database files could be in an inconsistent (corrupted) state.  Verify this with your vendor or their documentation.  A SQL Server backup triggers the CHECKPOINT process mentioned above, which means all data that's been modified in memory gets written back to disk before the backup.
One advantage of log backups is that you can restore to a specific point in time.  This can help when recovering from "oops" DBA actions (deleting or updating data accidentally - for instance, by forgetting a WHERE clause).

Can anyone highlight if I need to improve on this point?

The fact that you're thinking about RPO at all probably puts you ahead of a lot of folks in your situation   Double check that the VM backups are compatible with SQL Server, but otherwise it sounds like you're on the right track!


Unable to view MySQL db stored procedure We have many stored procedures created with SQL SECURITY DEFINER by user A
Now user B is responsible for maintaining some of the stored procedure. However user B is unable to view/update the stored procedure from workbench since the definer is set to A. Is there an way to give A and B access to the stored procedure at the same time ?

use following command:
GRANT EXECUTE ON dbname.* TO 'user'@'%';


MySQL: Indexing by column which is also a part of a compound primary keys and I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example:

CREATE TABLE `user_visit_stats` (
  `hour` datetime NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `visits` int(10) unsigned NOT NULL,
  `avg_time` float unsigned NOT NULL
  PRIMARY KEY  (`hour`,`user_id`,`page_id`)
)

So the primary key is composed of hour, user_id and page_id. I would like to be able to efficiently query the table on different combinations of these columns - for example to get the total visits for a page in a day or the total pages visited by a user in a week.

Does it make sense to define additional indexes on hour, user_id and page_id or will these indexes be redundant?

visits for a page for a period of time:  INDEX(page_id, hour)

visits for a period of time:  INDEX(hour)

Your primary key, plus these, are not redundant; have them all.

Recommend you keep sum_time, not avg_time, then compute the avg as SUM(sum_time) / SUM(visits).  (The average of averages is not mathematically correct.)


Querying a database efficiently for a huge chunk of data Here is my query

SELECT *
FROM
    (
        SELECT a.*, rownum rnum
        FROM
            (
                SELECT id, data
                FROM t
                ORDER BY id
            ) a
       WHERE rownum <= HIGHER
    )
WHERE rnum >= LOWER;

I have a huge set of data and hence I am getting parts of it one at a time, say from 1 to 100 in first attempt, 101-200 in the second attempt and so on.

If I execute the above query it will do the following piece for each portion of data:

SELECT id, data
FROM t
ORDER BY id

I think it is pretty inefficient to do it for each part.  Can't I do something like order my data set once and apply a rownum?  Will it be of any use at all?

Is your intention to get every row eventually?  For example, are you fetching all the rows and passing them to some sort of process that needs to process every row?  Or is your intention to present pages of results to a human who will likely only look at the first or second page of results.

Assuming that you are presenting results to a human that will only be looking at the first couple pages of data, the query you have is likely to be reasonably efficient.  Assuming that there is an index on the id column, in order to fetch rows 101-200, Oracle would simply have to read the first 200 id values from the index then filter out rows 1-100.  That's not quite as efficient as getting the first page of results but it's still pretty efficient.

Of course, as you get further and further into the data, the pagination query gets less and less efficient.  That's a problem if your intention is to eventually fetch every row.  It's generally not a problem, though, if you are presenting results to a human.  Humans don't really care how long it takes to fetch page 50 of a result set-- they're going to give up long before then.

If your intention is to send the data to a Java process to process the data (this will be substantially less efficient than processing the data in the database-- Oracle and PL/SQL are designed specifically to process large amounts of data), it would generally make sense to issue a single query without an ORDER BY, have a master thread on the Java application server that fetches N rows of data, spawns a thread to process that data, fetches the next N rows of data, etc.


Is the following table for a simple music database in 3NF? If not why? Im new to databases and I'm struggling to get my head around third normal form (3NF). I have been given a csv file where each record has fields song, album, artist and numerous tags.  I have put the data in a database and want to know if it's in 3NF. The headings of the tables I have inserted the data into are below:

 | Track_Id | Track_Name | Track_Artist | Track_Album |
 | Tag_Id | Tag_Name |
 | Track_Id | Tag_Id |

I believe the candidate keys in the first table are Track_Id and the combination of {Track_Name, Track_Artist, Track_Album}.

I know alternatively I could have a separate song table with a foreign key pointing to an album table which in turn has a foreign key making a reference to an artist table, but is this necessary?

If Track_Id and (Track_Name, Track_Artist, Track_Album) are candidate keys, with no other partial or transitive dependencies, then the table is in 3NF. Think about this - can Track_Name determine Track_Album or Track_Artist, or can Track_Album determine Track_Artist? If not, it's in 3NF.

If the other two tables have candidate keys, they'll be in 3NF.


Accidentally executed reset master on the live server Turns out I have called RESET MASTER; accidentally on the live server. The slaves stopped replication now because they can not find the expected bin log files on the master. Is there a way to continue replication without data corruption and without reimporting the whole dump on the slave?

Is it save to call CHANGE MASTER TO... with 'mysql-bin.000001' and position 1 on the slave now, because that is where they should continue, or do I risk data corruption in that case and it is generally preferred to reimport the whole dump and start over?

Considering all the slaves were in-sync with master while you issued reset-master, it is safe to issue change master to on slaves with newly generated first bin-log.


SQL 2008 Server agent cant see perfmon counters I am trying to create a new SQL server agent alert to capture "SQLServer:General Statistics|Processes Blocked||>|5' but it looks like alert creation GUI is not able to see any perfmon counters as in the following picture:

The drop downs for Object, counter and instance are empty.
I can't even create the alert using TSQL as follows:

I can see these counters collecting data in perfmon.

Any ideas?
Thanks in advance...

You'll have to work through this "SQL Server – Missing Performance Counters: A consolidated list of known issues" sorry.

I'm surprised this still happens: I last saw this with SQL Server 7/2000 years ago.


Filter results based on data from another table I have 3 three tables, products, discounts, and discounted_products:
products stores title, desc, price and etc information about products;
discounts stores discounts' start date, end date, and status;
discounted_products stores product_id (FK), discount_id (FK), discount type (percentage, static), discount amount, and status.

I need to filter and sort products by price ascending order, let's say greater than 100 and less than 150. But before querying the discount amount should be taken into consideration. Only some products have a discount, not all of them. How should I do it effectively?

I guess, first of all, active discounts should be extracted:

SELECT `dicount_id` WHERE CURDATE() > `start_date` AND CURDATE() < `end_date` AND `status` = 2

But after that I have no idea what to do.

Edit: detailed explanation

Let me graph it quickly:

Products table:

id: 1   name: A  price: 200
id: 2   name: B  price: 110
id: 3   name: C  price: 130
id: 4   name: D  price: 180
id: 5   name: E  price: 145

Discounts table:

id: 1   name: DA start_date: 2018-09-15 end_date: 2018-09-30    status: 2
id: 2   name: DB start_date: 2018-09-20 end_date: 2018-10-01    status: 2
id: 3   name: DB start_date: 2018-09-10 end_date: 2018-09-20    status: 2

Discounted_products table:

id: 1   discount_id: 1  product_id: 1 type: 1 (percent) amount: 30 status: 2
id: 2   discount_id: 1  product_id: 2 type: 2 (fix) amount: 45 status: 2
id: 3   discount_id: 2  product_id: 4 type: 2 (fix) amount: 60 status: 2

I wanna query products in 100 - 150 price range.
If there would be no active discounts it will return: from products table: 2, 3 and 5 (both in range 100 - 150)
Since we have two active discounts: from discounts table id : 1 and 2

new price table will look like:

id: 1   name: A  price: 140 => 200 - 200*30%
id: 2   name: B  price: 75  => 110 - 45
id: 3   name: C  price: 130
id: 4   name: D  price: 120 => 180 - 60
id: 5   name: E  price: 145

New result should be: 4, 3, 1, 5

Start with that:

select * from products where price > 100 and price < 150;

Then...

select * from products p
left join discounted_products dp on dp.product_id = p.id
left join discounts d on dp.discount_id = d.discount_id
 where (price+IFNULL(d.amount,0)) > 100 and (price+IFNULL(d.amount,0)) < 150;

I do not have any idea about the column names you are using, so...

Is that what you want?


Power BI DESKTOP licensing - is it really free to share pbix files? The Power BI Desktop app for Windows is free, which is great. However, the Microsoft site is loaded with talk of licensing to share/view Power BI content. Is that only for their hosted Power BI SERVICE?

I want to create reports in Power BI Desktop, and distribute these as .pbix files. Is there any reason I can't distribute these without paying a dime? I realize that Microsoft's goal is to get people into their hosted Power BI service, but using Power BI Desktop (free app) to distribute content for free seems to good too be true.

You are absolutely free to build and distribute .pbix files by file shares or OneDrive, or SharePoint Online.  Eventually this will lead to Power BI Service usage, or at least your team mates will want brand new Surface PCs to run Power BI Desktop.

And in any case you are investing your precious time in learning the technology and spreading its use.


How do I tell if a long running update won't lock the database? There is a update query I need to run on a table in production. The update would touch millions of rows and I would take a couple of minutes.
The database is Mysql.
I was wondering if the table would be locked during this update? The desired behaviour is that it should not be locked as I still want other operations to be possible while the update is going on.
Is there a way I can check before hand? What could I look for to find out the effect of such query? Is there something like a query plan from where I can see if the table would be locked or not?

Any UPDATE transaction will always acquire locks on the table being updated in some capacity. This is the normal default behavior in all modern relational database management systems. It's just a matter of the type of lock and how long a particular row stays locked during an UPDATE. Depending on the isolation level your database is configured for, even though a row may be locked, doesn't necessarily mean things like reads of that row will be blocked. You can read up more here on locks in MariaDB.
To your follow up question in the comments, depending on the isolation level, if an UPDATE is locking a set of rows that causes blocking on other subsequent queries running against those same rows, then those other queries will wait until those rows are no longer locked by the UPDATE query, to execute, in general.


Choosing between MyISAM & InnoDB - two servers with related tables I don't want to make a mistake.

I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.

I read here and there that InnoDB should be preferred now. MySQL is version 5.1.63.

Context: Two MySQL servers, serv1 and serv2.

serv1: one table with unique id. Call this table daddy.
serv2: one table child on which id's are the same as daddy's.

I have to update (and only this) my fields contained in daddy with the fields of child, where id's are the same. Indexes are on the columns that are important for the query.

So, to max performance out, should I stick with MyISAM or convert to InnoDB? I can still do it as the database is empty.

In my case, every hundredth of a second is worth a million $ :D (pure joke of course)

May you enlighten me?

"MyISAM is faster than InnoDB" -- This is an old wives tale.  It used to be somewhat correct; now it is somewhat incorrect.

SELECTing during UPDATEing -- MyISAM is usually fast enough so this is not an issue, but InnoDB will usually not block at all.  Go with InnoDB.

Power failure -- InnoDB automatically recovers.

You are talking about two "servers" -- Do you mean two different computers?  And two differen instances of mysqld?  There is nothing to have them talk to each other.  (OK there are Replication and Federation, but it did not sound like you are into those.)

If you really meant "database" or "table" on a single MySQL instance, then so state.  Tables (even in different databases) can be JOINed for most operations.


Activity Monitor Showing Long Duration for WAITFOR Using Activity Monitor, I am consistently seeing the following query showing an average duration of over 300 seconds:

WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body
                FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

There don't seem to be any adverse effects on the server, and this query is showing zeros for all other columns.

Is this normal or a cause for concern?

This is a Service Broker process listening to a queue for messages. 300 seconds will be the time out specified in @rec_timeout. See RECEIVE reference in books online.


to change collation for system database SQL Server 2017 We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change  all system databases (master , model , temp, msdb) to another collation. how to change this without installing SQL Server ?
thanks,

The only way to change the collation for the instance itself (instance-level and system DBs plus user databases) without reinstalling is an undocumented command/option. The "rebuilding system databases" option referred to in the other answers is effectively reinstalling, even if it isn't in a literal sense, nor does that approach update any user databases to match the new collation.
I posted a detailed analysis of this undocumented approach, and the implications of making any collation changes to an instance and/or database, here:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
There are quite a few areas that could be affected, and it depends greatly on if you are talking about just one particular database, or the instance and one or more of the databases on that instance, and if you have any VARCHAR data containing characters with values of 128 - 255, and if you are switching sensitivities (e.g. case-sensitive to case-insensitive, or vice-verse).
With regards to the ALTER DATABASE ... COLLATE ... option:

That does not work on system databases.
That is not permitted if you have one of many dependencies (e.g. TVFs returning string columns, computed column returning string types, etc).
For user databases where it is permitted, it only changes the system tables (e.g. sys.objects, sys.schemas, etc); it does not change any user tables (columns or indexes)


Log Shipping Backup and Destination Folder Location We plan to incorporate Log Shipping for basic disaster recovery with reporting needs. Server A Source Database will log ship to Server B database. We will conduct log shipping every four hours.

Original plan, source backup location will be in Server A Harddrive backup folder. Backups will copy to Server B Harddrive destination folder.

However after reading two MS articles below,

Are the two bold statements saying the same thing or different?

(1) Should source backup folder not be located in Server A Harddrive folder? (I can make it accessible to Server B as needed.)

(2) If so, why should I save source backups on separate server host computer? What benefit will this serve?

(3) Do I need to copy log backups in Server B Hard drive folder? Why not keep in separate server host computer, as they recommended to have consistent philosophy?

About Log Shipping
"The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder."

Database Mirroring and Log Shipping
During a log shipping session, backup jobs on the primary database create log backups in a backup folder. From there, the backups are copied by the copy jobs of the secondary servers. For the backup jobs and copy jobs to succeed, they must have access to the log shipping backup folder. To maximize availability of the primary server, we recommend that you establish the backup folder in a shared backup location on a separate host computer. Ensure that all the log shipping servers, including the mirror/primary server, can access the shared backup location (known as a backup share).

Recommendations are accurate. Reason 1 being never keep you backups on your database server or disk. Just good practice to separate them in case of disaster. Ideally you want you backups on different hardware as well, or even different location/data center. Approach it this way, if disaster struck, would you be able to recover? if not, what was your point of failure, server, disk, infrastructure.

The scenario you reference to on MS page explains multiple copy and restore jobs to multiple secondary locations. Copying to each secondary and restore keeps unique history on the jobs for that instance. As MS states "The primary and secondary server instances send their own history and status to the monitor server instance."


Postgres command failed: No space left on device I'm running a command to create a new column on a fairly large table (about 315 million rows). This used more disk space than I expected and it has completely filled up my SAN. The command failed with the ERROR:  could not extend file "pg_tblspc/14342 etc.": No space left on device

My questions are, when a command fails like this, are the results of the command not immediately discarded? Why when no actual changes have been made to the table, is the tablespace still full?

And finally, what is the best way to free up space again when postgres will not allow to me execute any other commands e.g. VACUUM?

when a command fails like this, are the results of the command not immediately discarded?

You need to understand the difference between the logical and physical structures of the database. When you insert 350m rows to a table and it fails, the logical changes are rolled back.

But when the underlying datafiles have to be increased in size to accomodate the 350m rows, this physical change is not rolled back.

  Why when no actual changes have been made to the table, is the tablespace still full?

While your 350m rows were being added, the tablespace grew its datafiles to accomodate the new data. When the data was deleted, the space remains. Deletions will not shrink the datafile, it will merely mark the space as unused and leave it there to be reused later. The space will only be returned to the operating system if the DBA issues a VACUUM FULL command.

  And finally, what is the best way to free up space again when postgres will not allow to me execute any other commands e.g. VACUUM?

Multiple valid strategies for fixing this problem are provided in these two older questions:

I need to run VACUUM FULL with no available disk space

VACUUM returning disk space to operating system


Error ORA-12505 when connecting to Database Oracle 11g SQL Developer recently I installed Oracle XE 11g with SQL Developer.

When I first ran it and created a database and a new user, everything worked perfectly - I was able to connect to the database and was able to run queries and scripts.
However, when I restart my laptop (for example, the following day), I am unable to connect to my database using that new user I created, as well as unable to use SYS/SYSTEM accounts.
I get an error with the code ORA-12505 that states:

"An error was encountered performing the requested operation:

Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505".

Nothing has changed since I restarted my laptop! I have also Googled the error but I am still unable to solve it. I know the error has something to do with my 'tnsnames.ora' file, but the service_name is correct from what I can tell.

My tnsnames.ora file is as follows:

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Mark-Prescher-Laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

Thank you for any help in advance!

The way you are connecting to the database (connection type "basic") isn't using your tnsnames.ora file at all. It is using a basic JDBC connect string - which is fine. That said, the error indicates that the database "xe" hasn't registered with the listener, meaning that the database itself probably isn't running at all. Check to make sure that your database instance is actually up. Check this document for details: https://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm#XEGSG101


Count two different columns from two different tables and throw into a single variable I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. And every single category in "cats" table has its unique ID.

When I add a new item, I can choose the category ID which can only be the one of categories from "cats" table: "music" (id=1), "books" (id=2) or "movies" (id=3). Items stored: "Pulp fiction" (id=1), "Mein kampf" (id=2), "Gone with the wind" (id=3), "Les miserables" (id=4), "The Holy Bible" (id=5) and "The best of Tina Turner" (id=6). Items 1, 3 and 4 belong to category 3, items 2 and 5 belong to category 2, and item 6 belongs to category 1 (I hope I'm still understandable ;) ).

Now I want to count how many items are assigned to every category. Is it possible to use "SELECT COUNT" in some combination, to get such a result as an output: cat1 = 1 (item), cat2 = 2 (items), cat3 = 3 (items)? How to do this?

Thank you in advance for your help!

I found myself a solution. Here is the query:

select i.item_cat, count(*) total, sum(case when i.item_cat = c.cat_id then 1 else 0 end) SumCat FROM `items` i left join `cats` c on (i.item_cat = c.cat_id) group by i.item_cat

It outputs:

item_cat | total | SumCat
    1    |   1   |   1
    2    |   2   |   2
    3    |   3   |   3

=========

EDIT: Found a better and (IMO) more efficient solution, which also outputs an empty categories (where no items are assigned to). Firstly, I have added a two more categories: "games" (id=4) and "cooking" (id=5), but did only insert a single item into a "cooking" category, leaving "games" category with no items assigned. Then, the query below did a really nice piece of work:

select c.cat_id, sum(case when i.item_cat = c.cat_id then 1 else 0 end) total FROM `cats` c left join `items` i on (c.cat_id = i.item_cat) group by cat_id

This will output:

item_cat | total
    1    |   1
    2    |   2
    3    |   3
    4    |   0
    5    |   1

So the solution above suits my needs completely and does exactly what I want it to do.


What does changing Max Server Memory do besides clear the plan cache and (obviously) alter the memory setting)? Running SQL Server 2012 SP3 on 32 GB ram and 4 cores, 60-80 concurrent connections, with a largely ad-hoc workload, we are seeing the SQL Server process (CPU) spike and remain spiked once or twice per day at unpredictable times. We are working on identifying the root cause of the spike. In the meantime, we've found that altering the Max Memory setting (either up or down) seems to be the only thing that will return the CPU load to normal.

Checking logs, and searching StackExchange (https://dba.stackexchange.com/a/183276) we see that the plan cache is being flushed by changing the Max Memory setting. However, if we flush the plan cache via DBCC FREESYSTEMCACHE('SQL Plans'), the CPU load doesn't return to normal.

Since changing the Max Memory setting resolves the issue no matter weather we increase or decrease it, the issue doesn't seem to be related directly to the Max Server Memory setting. As such, we are trying to understand what else changing the memory setting does, and then use that information to help identify the root cause of our CPU spike.

Ref: Server Memory Server Configuration Options

max server memory controls the SQL Server memory allocation, including (essentially any memory clerk found in sys.dm_os_memory_clerks):

the buffer pool
compile memory
all caches
qe memory grants
lock manager memory
clr memory

So all of these components will be effected by max server memory setting change.

On a side note this will be a very difficult path to find what is causing your CPU spike. I suggest you use following articles for your troubleshooting.

Troubleshooting SQL Server CPU Performance Issues by Joe Sack
How do I find out what is hammering my SQL Server?


Get count of value row I'm trying to figure out what MySQL code would do the following.

Count * from 'users' table (eg; 0,7 rows) then within the count get the count of 'money' based on the value of the user. I'm struggling to explain, so I hope that I've gave as much detail, 1 more example below.

I'm trying to achieve this:

You are currently the eg 1st, 2nd (depending on money value). richest person on the website.

database example:

-------------------------------
 id  | username  | money
-------------------------------
 1   | name      | 1000
 2   | name2     | 3000
-------------------------------

This is an example of getting the rankings:

select version();

| version() |
| :-------- |
| 8.0.13    |

CREATE TABLE person( name CHAR(10), money INT );

✓

INSERT INTO person VALUES
  ('Smith',10),('Jones',15),('White',20),
  ('Black',40),('Green',50),('Brown',20);

✓

SELECT name, money, rank() OVER(win) AS 'Richest'
FROM person
WINDOW win AS (ORDER BY money DESC);

name  | money | Richest
:---- | ----: | ------:
Green |    50 |       1
Black |    40 |       2
White |    20 |       3
Brown |    20 |       3
Jones |    15 |       5
Smith |    10 |       6

db<>fiddle here

Note this code is for MySQL-8.0 or MariaDB-10.2+. For earlier version implementations see this example

To get a value for a single user:

SELECT COUNT(*) + 1 AS "Richest"
FROM person p
JOIN person richer
  ON p.money < richer.money
WHERE p.name="Brown"

This doesn't have any version requirements.

Ref this fiddle.


Oracle Database 11g Failing to mount on windows server 2012 I have an Oracle 11g database installed on Windows Server 2012. It's failing to mount after shutdown. The reason why i want to mount it is that i want to enable Archive log mode, and i have been able to do the basic steps of Enabling Archive Log Mode on Linux but the same steps are failing on mount stage:

Below are the steps i have done:

SHUTDOWN IMMEDIATE
startup mount

It is here where the error below pops up:

  ORA-12505: TNS:listener does not currently know of SID given in
  connect     descriptor

PS C:\Users\inhousedwh> sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 17 17:46:02 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys@rvas as sysdba
Enter password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Use a local connection.

sqlplus sys as sysdba

or

sqlplus / as sysdba

In your output, you used a remote connection. Database instances can be started through remote connections only if you have registered them in the listener manually with static registration.


Sort table by column with column ordered by earliest instance of another column Order a table query based on a column but using another column.

I have a table equivalent to the following:

FOO  |  BAR  | TIMESTAMP
-------------------------
CAT  |  BOB  | 2018-03-01
CAT  |  ANN  | 2018-03-06
DOG  |  JON  | 2017-07-02
DOG  |  ABE  | 2012-03-02
CAT  |  HAL  | 2016-04-12
DOG  |  ANN  | 2012-02-06
BRD  |  JON  | 2015-09-12
...  |  ...  | ....-..-..

I want it to return a list ordered by FOO, but I don't want it ordered ascending or descending, but by whichever has the earliest timestamp. So if
the earliest Bird was in 2015, the earliest dog was in 2012, and the earliest cat in 2016, I'd want all the values ordered by DOG then BRD then CAT, and then afterwards sort by BAR.

However, I have no clue how to do this. I obviously don't want to order by timestamp first, or the dogs, cats, and birds will be scattered throughout, and ordering by timestamp after Foo wil leave it ordered BRD, CAT, DOG instead of DOG, BRD, CAT.

So output would need to kind of look like this:

FOO  |  BAR  | TIMESTAMP
-------------------------
DOG  |  ABE  | 2012-03-02
DOG  |  ANN  | 2012-02-06
DOG  |  JON  | 2017-07-02
BRD  |  JON  | 2015-09-12
CAT  |  ANN  | 2018-03-06
CAT  |  BOB  | 2018-03-01
CAT  |  HAL  | 2016-04-12
...  |  ...  | ....-..-..

You can use a subquery / derived table to get the earliest timestamps and then join;

SELECT t.foo, t.bar, t.timestamp
FROM tbl AS t
  JOIN
    ( SELECT foo, MIN(timestamp) AS min_timestamp
      FROM tbl
      GROUP BY foo
    ) AS m
  ON  t.foo = m.foo
ORDER BY
    m.min_timestamp,
    t.foo,
    t.bar ;

In MariaDB (and in MySQL version 8, when it is released), you can also use window functions:

SELECT t.foo, t.bar, t.timestamp
FROM tbl AS t
ORDER BY
    MIN(t.timestamp) OVER (PARTITION BY t.foo),
    t.foo,
    t.bar ;


How to get service broker database queue status? I need to get service broker queue status (Enabled/Disabled) somehow programmatically. Either, T-SQL, PowerShell, etc.
I tried $MyQueue = Get-ChildItem SQLSERVER:\SQL\mydbserver\default\databases\MyQueue\ServiceBroker\Queues | where {$_.Name -like "MyQueue"} but couldn’t find how to get the status.
Thanks in advance.

PoSh just wraps SMO. ServiceQueue exposes IsEnqueueEnabled for queue status, IsActivationEnabled for activation status,
IsRetentionEnabled for retention status and finally IsPoisonMessageHandlingEnabled for, well, poison message handling status.

Also PoSh has the habit of hiding properties. See how to see all properties.


MySQL cluster concurrency I have a little application that executes select queries against a MySQL cluster.

By using 1 thread to read a record the latency is x.

By using 2 threads to read records concurrently the avg latency is ~ 2x.

By using n threads to read records concurrently the avg latency is ~ nx.

Is there a way to optimize that?

I use one mysqld for 3 Data Nodes.

Make sure that you have uniform servers across the cluster because a slow/overloaded master node can affect performance of the Galera Replication cluster.
Check the node status by monitoring value of these variables wsrep_ready and wsrep_connected
Make sure that value of this variable is close to 0 wsrep_flow_control_paused
A high value for this variable wsrep_local_send_queue_avg for a node indicates a bottleneck in network line, If this is the case, the cause can be at any layer, from physical to OS configuration and must be investigated.
You can yourself profile SELECTs on diffrent nodes and see performance.

  mysql> SET profiling = 1;

  mysql> SELECT * FROM

  mysql> SHOW PROFILES;

I hope this information helps.


MySQL. Default length for a field Is there a list of already set standards for some common fields like: firstName, lastName, email, phone?

I see most people just set varchar(300) to all fields, does it affect performance in any way?

Suggested max len

First, I will mention some common strings that are always hex, or otherwise limited to ASCII.  For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space:

UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16)
country_code CHAR(2) CHARACTER SET ascii
ip_address CHAR(39) CHARACTER SET ascii -- or pack into BINARY(16)
phone VARCHAR(20) CHARACTER SET ascii -- probably enough to handle extension
postal_code VARCHAR(20) CHARACTER SET ascii -- (not 'zip_code') (don't know the max

city VARCHAR(100) -- This Russian town needs 91:
    Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin
country VARCHAR(50) -- probably enough
name VARCHAR(64) -- probably adequate; more than some government agencies allow

Why not simply 255?
There are two reasons to avoid the common practice of using (255) for everything.

When a complex SELECT needs to create temporary table (for a subquery, UNION, GROUP BY, etc), the preferred choice is to use the MEMORY engine, which puts the data in RAM.  But VARCHARs are turned into CHAR in the process.  This makes VARCHAR(255) CHARACTER SET utf8mb4 take 1020 bytes.  That can lead to needing to spill to disk, which is slower.
In certain situations, InnoDB will look at the potential size of the columns in a table and decide that it will be too big, aborting a CREATE TABLE.


Cannot find DReplay.exe for SQL Server 2019 I have been working on SQL Server 2019 version and trying to setup a load test environment using SQL Server Distributed Reply Controller - Client, however I cannot find the DReplay.exe. I thought the it will be installed as part of the installation of Distributed Reply Controller, but it didnt. Some articles suggest that it will be installed as part Management Tools from the installation media for SQL Server but the Management Tools option has been removed from the installation option.

There is also no link online to suggest an alternative way to get the DReplay.exe installed. Running out of options now.

As a desperate measure, I did try to copy the  the "C:\Program Files (x86)\Microsoft SQL Server\150\Tools\Binn" folder content from an older server where it was installed to the new SQL Server 2019 which did not work, throwing error "Failed to load assembly...."

Any ideas any suggestions please?? Thank you.

After much more googling and even speaking to Microsoft People, I found out that they have very quietly discontinued it partially.
Which means you do get the "Distributed Replay Controller" and "Distributed Replay Client" but there is no cmd utility "Distributed Replay administration tool" available for SQL Server 2019.
After a lot more research I found the tool which is much simpler and easier to use.
WorkLoadTools

It is a very lightweight application, no installation is needed.
Setup takes 5 minutes as compare to quite a few hours for MS Distributed replay
It uses Extended events to capture only the data that is needed for replaying, therefore the trace file size is much smaller.
You can capture events on Production and replay on client in real-time.
You can also capture a trace on Production and replay on client later.
There is reasonable amount of documentation on it as well.
Plus the guy who wrote this beauty, @spaghettidba is very helpful and will respond to your queries and help you to sort out any issues you may face.

Give it a go and you wont be disappointed.


mongodb moving value from NumberInt to NumberLong We are trying to preallocate documents in a collection. Usually NumberInt for our values is sufficient. When we get 1million writes into that document (it is one doc per day time series, so 1 million is possible), then the value moves from NumberInt to NumberLong.

We are planning to disable padding, so any single byte movement will move the document.

The question is, whether mongodb will move the document if the NumberInt changes to NumberLong

If you are using MongoDB 3.0 + MMAPv1 with noPadding allocation (aka "exact fit"), then the document will have to move if you change from 32-bit ints (4 bytes) to 64-bit longs (8 bytes). With noPadding there is no extra space available to store more bytes, so you should start with NumberLongs to avoid expected document growth.

  The question is, whether mongodb will move the document if the NumberInt changes to NumberLong

You can confirm the outcome via the database profiler. Here's a quick run in the 3.0.7 mongo shell:

// Drop test collection
db.mycollection.drop();

// Create new test collection with noPadding (exact-fit allocation)
db.createCollection("mycollection", { noPadding: true });

// Enabling profiling of all operations
db.setProfilingLevel(2)

// Insert a document with a 32-bit int
db.mycollection.insert({ _id: 'nopadding', value: NumberInt(42) });

// Update document to have a 64-bit int
db.mycollection.update({ _id: 'nopadding' }, { $set: { value: NumberLong(10000000) }});

// Disable profiling
db.setProfilingLevel(0);

// Check if our update operation resulted in a move
db.system.profile.find({op:'update', 'query._id': 'nopadding', moved: true}).count()
1


How can I safeguard against SQL injection where parameterizing my query is not an option? I'm working with ESRI's arcpy module, and I need to specify a WHERE clause as a parameter to this function. Since the result will eventually be used to retrieve data from the database, the text of the WHERE clause is passed directly to the database at some point, but sadly, ESRI does not give me any options for parametrizing the query. Since I want to safeguard against potential SQL injection, I need to find some alternative means of protecting my database.

One option that comes to mind is limiting the input and throwing an error before this function call if it doesn't conform. In my case, callers will only ever need ASCII alphanumeric characters. No other kind of characters are allowed in the column to be used for filtering. Would limiting the user to only alphanumeric characters be sufficient to prevent SQL injection, especially since the text must be quoted as a string?

If you really can limit to alphanumeric characters, then yes, that's fine, IF you are limiting to ANSI alphanumeric characters. In Unicode, because every character is more than one byte, many representations of alphanumeric characters are actually unsafe and could lead to injection.

You will need to sanitize the data server-side and make sure the encoding is one that is safe. I'd suggest you take a look at the ESAPI libraries by OWASP for how they do it (see third section on this page).


MySQL select all records unless user must be present in favorites I am having trouble to find the best solution for the following problem;

Description: I want to select all records from an items table, unless it is for favorites only, in that case the user must be present in the owners favorites.

The tables look something like these;

Items table (Table that holds all the items)

+----+----------+---------------+
| id | owner_id | favorite_only |
+----+----------+---------------+
|  1 |        1 |             0 |
|  2 |        2 |             1 |
|  3 |        2 |             1 |
+----+----------+---------------+

Favorites table (Table which holds the owners favorite users)

+----+----------+---------+
| id | owner_id | user_id |
+----+----------+---------+
|  1 |        2 |       1 |
+----+----------+---------+

I hope the above sufficient, if not, please let me know.

I made a simple solution to my problem with the following condition;

CASE
    WHEN items.favorite_only = 1 THEN
    (SELECT favorites.user_id FROM favorites WHERE favorites.owner_id = items.owner_id AND favorites.user_id = 1 LIMIT 1)
    WHEN items.favorite_only = 0 THEN 1
END

Is there performance wise a better solution?


Errors in the metadata manager. The process type specified for the Effect dimension is not valid since it is not processed Trying to hunt down an issue with an SSIS package we have and I don't know what to look for to help correct this issue.

Error:

Errors in the metadata manager. The process type specified for the Effect dimension is not valid since it is not processed.

I tried googling this and haven't seen anything that could point in a direction of what I should be looking at or checking.

You are trying to process the dimension with a type that needs the dimension to be processed. For instance, you are trying to do a process indexes, but the dimension is not previously processed.

Change the process indexes by a process update or a process complete.


SQL Master Data Services database has huge leaf member staging table - can I purge old records? One of our SQL instances has a large MDS database, and virtually 100% of the size is due to a 300+ million row table named stg.Stg_Project_Leaf. It recently started blowing up our weekly index rebuilds, so I need to figure this out.

From what I can tell, this is a Leaf Member Staging Table for a "Project" entity, set up and automated (by a prior consultant) as a part of an ETL process that builds BI cubes/reports.

Looks like it has been processing about 10k rows per half hour for the past several years and has never been purged. When I check the ImportStatus_ID, though, I see 0 rows waiting to be processed. (Most are 1 - succeeded, less than 1% are 2 - failed.)

Can I just TRUNCATE this table?

This system is being used in an automated fashion, its not like people are logging into the MDS console and individually monitoring and rolling back changes. I found some references to stored procedures that purge these tables (mdm.udpStagingClear or maybe mdm.udpDeletedMembersPurge), but I'm not sure which of those is the one I'd need, nor do I know if they would blow up the log if they tried to do 300m rows at once.

In short: The official answer is to use the new proc mdm.udpEntityStagingBatchTableCleanup to clean up entity staging tables, but in practice, DELETE or TRUNCATE should work just fine (or might be necessary), as long as you understand your batch detail.

I found reference to a SQL 2012 patch that adds 3 stored procedures to MDS, including the one I need:

--Cleanup entity-based staging table
EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

See also this article for scheduled cleanup options utilizing the same procs.

For my purpose, though, I was still concerned about blowing up the tran log with the 300+ million rows that had to be deleted, so I looked in detail at the inner workings of mdm.udpEntityStagingBatchTableCleanup. This stored proc does the following:

Populates a temp table with all completed batches that match your deletion criteria (for the given @Model_ID, older than @CleanupOlderThanDate).
Builds dynamic DELETE statements for the corresponding Leaf, Consolidated, and Relationship tables for each DISTINCT Entity_ID in the temp table.
It executes each of those DELETE statements for each entity table, filtered via a join to the temp table.
It then deletes from mdm.tblStgBatch itself, again filtered by join to the temp table.

There is no attempt to limit the amount of rows deleted at once, and in my case,  the 300+ million rows are all for a single Entity_ID, which would likely blow up the transaction log.

The good news, though, is that if I intend to remove all Entity_IDs and all dates anyway (which I do), then that's functionally equivalent to a TRUNCATE,
 as long as I am careful to not attempt it right in the middle of an active batch.

Finally, while I couldn't find any official MS sources that explicitly recommended truncating these tables, I did find several articles where DBAs do a routine TRUNCATE at either the beginning or the end of their import process:

"Step 1:  Truncates each of the 3 staging tables."
"If you want to empty the staging table, you can either delete all records, or click Clear Batches in Integration Management."
TRUNCATE TABLE stg.TableName_Leaf

So my plan is to do an initial TRUNCATE, and then schedule mdm.udpEntityStagingBatchTableCleanup to run on a periodic basis.


What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE? There is a long and quite elucidating answer on the differences between

TIMESTAMP WITH TIME ZONE
-vs-
TIMESTAMP WITHOUT TIME ZONE

available in the SO post Ignoring time zones altogether in Rails and PostgreSQL.

What I would like to know is: Are there any valid use cases for actually using TIMESTAMP WITHOUT TIME ZONE or should it be considered an anti-pattern?

This is stated in a lot of places, but I think it worth mentioning always when we compare the timestamp with time zone with timestamp without time zone types: the timestamp WITH time zone does not store the time zone information along with the timestamp. What it does is to store every data in UTC time zone, as stated in the docs:

  For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

It is considered valid for some to use timestamp WITHOUT time zone in situations where (1) everything is in the same timezone or (2) the application layer handles the time zone and just store everything in a certain time zone (usually UTC). But it is also considered an anti-pattern, simple because the correct solution for (1) is to configure the TimeZone setting to the given one timezone for the system and (2) is already solved, as PostgreSQL already stores everything on the same timezone (UTC).

Now, with those two down, I can came with only one good reason to use timestamp WITHOUT time zone. That is when you want to store events in the future and that some kind of alert must be triggered when we got to that time. That could be good for timestamp WITH time zone if, and only if, the rules defined by region's laws about time zone didn't ever change. The most common rule that changes is about the adoption or not of day light saving time (DST).

For example, imagine that you are at, let's say, 2013-06-15 (not yet in DST) schedule some event to happen at 2013-01-15 10:00 (which would be already in DST), and at 2013-06-15 your region was designated to adopt DST; but, some time after that, the government changed the rule and say your region will no longer use DST, and suddenly your scheduled time becomes 2013-01-15 11:00 (instead of 10:00), that if you use timestamp WITH time zone, and keep your TZ configurations up-to-date. Of course you may also notice that it is possible to treat such cases also with time zone, if you keep track of the rule changes in the regions/timezones of your interest, and update the affected records.

Worth mentioning that some regions does often change these rules (like at Brazil, some states - not the entire country - often change), but in most cases it changes it very earlier, so your users would be affected only by events scheduled very far from the current time.

With all that said, I only have one more suggestion. If you do have users, logs, or anything on different timezones, store the timezone they are coming from somewhere and choose and use timestamp with time zone. That way you can (1) cross events happening closer to each other for different sources (independent of their timezones) and (2) show the original time (the clock time) the event has happened.


Storing logical WAL in the same database I have a simple Postgres setup (one machine, one disk) and would like to record a changelog for some of the tables. As I would like to access that changelog from the same web app that uses the database, it would be ideal to store the row-by-row changes in Postgres as well.

It is critical that the logging doesn't slow the machine down too much during times of high load. I would like to avoid setting up triggers (unless you believe they are the best option here).

I know about Debezium and Kafka, but setting all of that up seems like a dreadful amount of complexity for what I'm trying to achieve. What is the easiest way to accomplish what I'm trying to do? I'm open to other options too.

Edit for clarification: I don't need the log entry to be written alongside the changes in one atomic transaction – I'd rather it's queued up to happen at some unspecified time later. If some timestamps are wrong or (worst case) a log entry is missing, it's not the end of the world in my situation; write performance is more important.

setup a trigger on UPDATE DELETE and INSERT that writes the info you want to a logging table.


any documentation on tpcc-mysql benchmark tool Where can I find tpcc-mysql documentation? I google it but I can't find any documentation about it.

Here's a recent blog post (2013-07-01) that shows how to use tpcc-mysql and graph results:

http://www.mysqlperformanceblog.com/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/

This is written by Michael Rikmas, who is a super nice guy, and a long time Percona support engineer.


pg_dump: SQL command failed I am trying to take backup of postgresql 9.0 by using pg_dump command.
command here is:
cd /opt/PostgresPlus/9.0AS/bin and hit the following command

pg_dump -h xxx.xxx.xxx.xxx -p 5432 -U superuser db_name>db_name.dump

But i am getting error like,

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  schema "dbms_sql" does not exist
pg_dump: The command was: SET search_path = dbms_sql, pg_catalog

please give me suggestion.

There are various ways to set the search_path in PostgreSQL.

To diagnose, log into the same database as the same superuser and check:

SHOW search_path;

If it contains dbms_sql, then check your postgresql.conf, databse and role settings to find where it came from.

Use any editor or for instance grep on in a Linux shell to audit postgresql.conf:

grep 'search_path' /your/path/to/postgresql.conf

To check on database and role settings, use the default command line client psql or a graphical GUI like pgAdmin.

In pgAdmin you just select the object in the object browser to see all settings in the SQL pane.

In psql, use

\drds superuser

to see settings for this role. And

\drds '' db_name

to see settings for the database. Or just

\drds

to see all settings for all roles and databases.


MySQL and Solid State Disks on EMC Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace which is used for disk sorts that cannot fit in memory. How can I isolate the parts of MySQL which are characterized by a random disk read pattern?

Here's a clarification of my original question: This is a reporting database with very large tables, large sorts, etc. Cannot fit into server memory.

Typically any OLTP database that can't fit into memory has random reads.  If you are reading that much from your OLTP database that you need EFDs (what EMC calls SSDs) then you need to first look at putting a LOT more RAM into the database server.  It will be a lot cheaper to prevent the reads than to make the reads faster.  Those EMC EFDs run about $20k each.


Using a query in an update I have a query which generates a result set (key, a, b).

I want to use an update statement where, for each entry in a table with Tkey = key, set ColA = a, ColB = b.

How would I do this?

Right now I'm trying:

UPDATE table1
set ColA = a,
ColB = b
where TKey in (select TKey, a, b from ...)

but a and b can't reference the where clause, right? So how can I do this?

I'm a big fan of MERGE.
You can try the following;

MERGE INTO table1 main
USING ( SELECT TKey, a, b
          FROM table2 ) subsel
ON (main.TKey = subsel.TKey)
WHEN MATCHED THEN
UPDATE SET main.ColA = subsel.a,
          main.ColB = subsel.b

Here's a quick example;

SQL> SELECT * FROM maintab;

TKEY                 COLA                 COLB
-------------------- -------------------- --------------------
rec1
rec2
rec3

SQL> SELECT * FROM updtable;

TKEY                 A                    B
-------------------- -------------------- --------------------
rec1                 123                  456
rec2                 789                  321
rec3                 654                  987

SQL> l
  1  MERGE INTO maintab main
  2  USING ( SELECT TKey, a, b
  3            FROM updtable ) subsel
  4  ON (main.TKey = subsel.TKey)
  5  WHEN MATCHED THEN
  6  UPDATE SET main.ColA = subsel.a,
  7*            main.ColB = subsel.b
SQL> /

3 rows merged.

SQL> SELECT * FROM maintab;

TKEY                 COLA                 COLB
-------------------- -------------------- --------------------
rec1                 123                  456
rec2                 789                  321
rec3                 654                  987


Splitting Existing Partitions in SQL 2014 I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition.

The table is currently partitioned 6 ways by a "Time" field:

2011
2012
2013
2014
2015
2016/2017/2018

The 2016/2017/2018 partition needs to be split into their own partitions so there is a 2016, 2017, 2018 partition.

How can this be done and in the process avoiding a massive log file growth? The database is in Simple Recovery and currently each partition is in a separate file group on its own set of disks.

CREATE TABLE [dbo].[Log](

      [RefNo] [nchar](7) NOT NULL,

      [DevID] [nvarchar](7) NOT NULL,

      [Time] [datetime] NOT NULL,

      [summary] [float] NULL,

      [staging] [float] NULL,

      [position] [float] NULL,

CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED

(

      [RefNo] ASC,

      [DevID] ASC,

      [Time] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)

)

Below is an example script to create the new yearly partitions. You may need to tweak the script for your actual partition function name and new filegroup names. This method assumes the operation is performed in a maintenance window when there are no inserts into the table and data for 2016 and later isn't needed for queries.

The summary of the steps performed by the script are:

create new partition function and scheme like the originals
create new aligned staging table like the original
switch the partition containing data > 2016-01-01 into the staging table
add 3 new partitions to the original partition function/scheme
rebuild the staging table on the original partition with DROP_EXISTING=ON
switch the 3 new partitions from the staging table back into the original table

There are a couple of issues with your existing table partitioning you should be aware of. Because of the RANGE LEFT function, datetime values that are exactly midnight on the first of the year will be placed in the wrong partition. For example, a row with Time value '2012-01-01T00:00:00' will be in partition 1 along with year 2011 data instead of partition 2 with the rest of year 2012 data as desired. This might not be a concern unless you purge data by partition.

Be mindful that the last partition if a RANGE LEFT function can never be removed. The implication is that the filegroup of the last partition is permanently part of the partition schemes that use the function. In your case, filegroup Year6FileGroup will always be the last filegroup of the psYearlyPartition_Log scheme, although it can also be used for another partition, such as for year 2016 here.

The partitions after the script is run will be as below and it is expected the last partition will be empty (assuming you have no future year data). You should create the partition for next year before the start of the year to avoid splitting a non-empty partition or avoid jumping through these hoops again.

+------------------+----------------+----------------+----------------+
| Partition Number | Lower Boundary | Upper Boundary |   Filegroup    |
+------------------+----------------+----------------+----------------+
|                1 | NULL           | <= 2012-01-01  | Year1FileGroup |
|                2 | > 2012-01-01   | <= 2013-01-01  | Year2FileGroup |
|                3 | > 2013-01-01   | <= 2014-01-01  | Year3FileGroup |
|                4 | > 2014-01-01   | <= 2015-01-01  | Year4FileGroup |
|                5 | > 2015-01-01   | <= 2016-01-01  | Year5FileGroup |
|                6 | > 2016-01-01   | <= 2017-01-01  | Year6FileGroup |
|                7 | > 2017-01-01   | <= 2018-01-01  | Year7FileGroup |
|                8 | > 2018-01-01   | <= 2019-01-01  | Year8FileGroup |
|                9 | > 2019-01-01   | NULL           | Year6FileGroup |
+------------------+----------------+----------------+----------------+

I generally recommend a RANGE RIGHT function when partitioning on incremental values like datetime because the behavior is more intuitive. See Table Partitioning Best Practices for this and other considerations.

--create identical partition function and scheme
CREATE PARTITION FUNCTION pfYearlyPartition_Log_Staging(datetime)
    AS RANGE LEFT FOR VALUES(
      '20120101'
    , '20130101'
    , '20140101'
    , '20150101'
    , '20160101'
);
CREATE PARTITION SCHEME psYearlyPartition_Log_Staging
    AS PARTITION pfYearlyPartition_Log_Staging TO (
      Year1FileGroup
    , Year2FileGroup
    , Year3FileGroup
    , Year4FileGroup
    , Year5FileGroup
    , Year6FileGroup
);

--create identical aligned staging table
CREATE TABLE [dbo].[Log_Staging](
      [RefNo] [nchar](7) NOT NULL,
      [DevID] [nvarchar](7) NOT NULL,
      [Time] [datetime] NOT NULL,
      [summary] [float] NULL,
      [staging] [float] NULL,
      [position] [float] NULL,
CONSTRAINT [PK_Log_Staging] PRIMARY KEY CLUSTERED
(
      [RefNo] ASC,
      [DevID] ASC,
      [Time] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)
) ON psYearlyPartition_Log_Staging(Time);

--switch last partition into staging table
ALTER TABLE dbo.Log
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20170101')
    TO dbo.Log_Staging PARTITION $PARTITION.pfYearlyPartition_Log_Staging('20170101');

--create new partitions for main table (assuming filegroups already exist for years 7 and 8)
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year6FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20170101');
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20180101');
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year8FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20190101');

--we must specify IGNORE_DUP_KEY=OFF in order to use DROP_EXISTING_ON here
--but, since the original table PK index definition is unchanged, that's a non-issue
CREATE UNIQUE CLUSTERED INDEX [PK_Log_Staging] ON dbo.Log_Staging
(
      [RefNo] ASC,
      [DevID] ASC,
      [Time] ASC
)
WITH (DROP_EXISTING=ON, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)
ON psYearlyPartition_Log(Time);

ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20170101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20170101');
ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20180101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20180101');
ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20190101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20190101');


Column of generic/variable type? In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is:

CREATE TABLE TagValue
(
     tag VARCHAR(64),
     value BLOB,
     type INTEGER,
     PRIMARY KEY(tag)
);

Here in the 'type' column I put the type of data contained in value.

Is there a better solution?

Why BLOB ?
Why not string or varbinary(max) or nvarchar
Dot Net Nuke in their database inside table UserProfile have generc column

CREATE TABLE [dbo].[UserProfile](
    [ProfileID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [PropertyDefinitionID] [int] NOT NULL,
    [PropertyValue] [nvarchar](3750) NULL,
    [PropertyText] [nvarchar](max) NULL,
    [Visibility] [int] NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL,
    [ExtendedVisibility] [varchar](400) NULL ....


Distribute MySQL in different cities? We have our main MySQL server in city A. We have the bulk of our customer service staff in city B. The staff are complaining that the connection is too slow to the server. The options are:

Have them go out over the public internet instead of our direct link between cities; that way, ping might be longer but they aren't competing with the VOIP traffic between cities
Cluster the database so that writes and reads can happen simultaneously on local servers in both cities.

2 is much preferable, but as you can probably tell by my description I have absolutely no clue how to implement it or even if it is 'clustering.' The best I could find for MySQL Cluster is that it would require using NDB tables and I'd rather not convert our entire database to that. What are my options here? Thanks.

How far apart (ping time) are the two cities?  80ms is what we experience going across the US.  It is not bad.

Writing to both heads of Master-Master is possible, but has lots of pain points.

NDB Cluster allows hot-hot, but (as you say) requires some conversion.

So, back to what I see as the only viable solution:  A single writable master, plus any number of slaves.

One thing that can make a remote master painful is if the user's "unit" of action translates into many SQL statements.  That can/should be solved by
(1) Rethink the code to use fewer statements
(2) Use a Stored Procedure to encapsulate as many of the SQL statements as possible, then deploy that on the remote Master.

Reads (other than "critical reads") can/should go to a slave, behind a load balancer.  And some mechanism should ensure that reads are usually "local".


mysqltuner - Write Key buffer hit rate I'm pasting section of MySQL Tuner result regarding MyISAM Metrics.
[OK] Currently running supported MySQL version 5.5.57-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Performance Metrics -----------------------------------------------------
[--] Up for: 25d 1h 40m 20s (6M q [3.149 qps], 170K conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 90% / 10%

-------- MyISAM Metrics ----------------------------------------------------------
[!!] Key buffer used: 20.1% (80M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/31.6M
[OK] Read Key buffer hit rate: 99.9% (7M cached / 4K reads)
[!!] Write Key buffer hit rate: 1.2% (2M cached / 25K writes)

I'm looking for way to improve or optimize the Write Key buffer hit rate. Or why is it so bad?
I don't know which server variables may play role in this result, so please let me know what variables should I paste here or if you need any specific details.
Thanks for help!
UPDATE 2017-08-11 13:56 CET
I did some more tests based on recommendations from @RolandoMySQLDBA and it really seems I should not worry about this metric. After I lowered the key_buffer_size, the Write buffer key hit rate improved directly. However, I couldn't measure any improvement in speed of reads. I assume it's because there was not enough memory for cache, so almost all writes went through. I still don't really understand what is the good or bad thing about Write buffer key hit rate, but it seems to be related to optimal size of key buffer based on the data and index in database.

OK, the hit rate is low.  But let's put things in perspective...  25K writes in 25 days is so insignificant (< 1/minute) as to be the least of your worries.  5K/minute would be a concern.

If you are having performance problems, locate the slowest query and study it -- its indexes, its formulation, etc.

The "key buffer" applies to MyISAM tables, of which you have only 1.  The InnoDB statistics are not listed.

For further discussion, see http://mysql.rjweb.org/doc.php/mysql_analysis


Can not give NT SERVICE\MSSQLSERVER permissions on network drive I have an SSIS package that I would like to run as a job. The flat file source sits on Server A while the package and job sit on Server B.
When I run the job that executes the SSIS package I get an error (Error code 0xC020200E Cannot open the datafile) but I can manually execute the deployed package in SSMS and it executes successfully . I had found out that this was caused by the Server B's NT SERVICE\MSSQLSERVER service account not having access to the folder on Server A. To test this I had created a test folder on Server B and made the SSIS package look there, I have then given NT SERVICE\MSSQLSERVER full access to the folder on Server B. The job executed successfully and the package ran however when I try to give NT SERVICE\MSSQLSERVER permissions to the folder on server A, I can not find the server in the locations tab and I cannot access the NT SERVICE\MSSQLSERVER service account. I have tried mapping the network drive however that did not help. The files are automatically uploaded onto server A every morning, the folder has to stay on Server B. Any assistance or advise is appreciated.

NT SERVICE\MSSQLSERVER is a local service account - therefore it does not have access to network resources (like file shares)
Change to either a Network service account or a domain account using SQL Server Configuration Manager
The different service account types are described here :-
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15


Do you feel like DBA/DBD Is a "secure Field" still? I noticed recently alot of Database jobs/Development have kind been pushed onto general developers, not to mention with NoSQL Solutions (Which IMO are probably a little easier for Non-DBA's to throw together) coming down the line. Do you feel like DBA/DBD (database developer) is a secure field for a CS Graduate to go in. Also...do you feel that Databases in General are still as popular as they were say....5-10 years ago?

For reference I do have a job as a Developer right now, but Databases is something that's always interested me.

I know theirs a Career Overflow site, but I'd prefer to keep it here since it's Specifically about Database Development/Administration.

Thanks

Yes, absolutely. Whether you use NoSQL (which means "not only SQL" not "no SQL") or an enterprise RDBMS, there is still quite a large need for architecture, data modeling, design, tuning, data security, etc. If you think throwing your databases on SQL Azure means you no longer need a DBA and it just runs itself, you might be in for a shock. Every company uses data in one way or another, and the trend I see developing is that more and more companies are moving to taking their data to big solutions. I don't anticipate the need for the DBA/DBD folks around that to dwindle, though I do sense that the nitty-gritty specifics involved in any such role are constantly evolving.


Limiting join to top 1 row for each row I'm trying to join two tables and filter join results but I couldn't manage it and I'd appreciate any help.
I have these row sets:
Table A
id | userid | targetid | start_date
-----------------------------------
11 | user1  | 123      | 22/10/2019
22 | user1  | 123      | 02/10/2019
33 | user1  | 123      | 04/10/2019
44 | user1  | 456      | 02/10/2019
55 | user1  | 123      | 13/11/2020

Table B
id | targetid | start_date
---------------------------
66 | 123      | 21/10/2019
77 | 456      | 11/11/2020
88 | 123      | 11/11/2020
99 | 123      | 12/11/2020

What I'm trying to do is to find most recent Table B row for each Table A row using targetid as FK and using start_date as filter/order values. Here's the result I'm looking for:
11 | user1  | 123      | 66
22 | user1  | 123      | 66
33 | user1  | 123      | 66
44 | user1  | 456      | 77
55 | user1  | 123      | 99

I tried to inner join and limit join condition using on ... and a.start_date <= b.start_date but that made Table A | 55 join with each row in Table B.
This could be any type of SQL, join/cursor/loop the method and performance doesn't matter.
SQL Fiddle

So there are two ways to do this:

If you have keys that guarantee that combinations of (targetId,start_date) are unique, a correlated subquery will return consistent results
If you cannot guarantee uniqueness, you will have to use a partitioning function in order to guarantee the same result is returned each  time the query is executed

If a unique index exists:
SELECT
  a.id
 ,a.userid
 ,a.targetid
 ,b.id AS b_id
 /* And other columns from Table B */
FROM
  table_a a
LEFT JOIN
  table_b b
    ON b.targetid = a.targetid
        AND b.start_date =
          (
            SELECT
              MAX(start_date)
            FROM
              table_b
            WHERE
              targetid = a.targetid
                AND start_date <= a.start_date
          )

No unique index exists:
For this I'm assuming Id on Table B is a row identifier and unique.  If it isn't, you will need to add additional columns to the SORT portion of the PARTITION statement to guarantee the query is deterministic.
SELECT
  id
 ,userid
 ,targetid
 ,b_id
 /* Other columns from table b */
FROM
  (
    SELECT
      a.id
     ,a.userid
     ,a.targetid
     ,b.id AS b_id
     /* Other columns from Table B */
     ,ROW_NUMBER()
        OVER
          (
            PARTITION BY
              a.id
            ORDER BY
              b.start_date DESC
             ,b.id DESC
          ) AS rownum
    FROM
      table_a a
    LEFT JOIN
      table_b b
        ON b.targetid = a.targetid
            AND b.start_date <= a.start_date
  ) x
WHERE
  rownum = 1

Notes:
As I mentioned in my earlier comment, the desired results you provided are inconsistent with the requirement tableb.start_date <= tablea.start_date, specifically for Ids 22, 33, 44.


Flush Host Connection Blocked, but some connections allowed We are running Percona MySQL 5.6.30.  We've had an issue where a PHP script on a different host was reporting an issue connecting to the database:

  Database may be down : SQLSTATE[HY000] [1129] Host 'x.x.x.101' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

We did the flush hosts, and it resolved the issue.

When we went back to check the Percona Audit Logs for the same time, we see a number of connections at that same time, from that same host ('x.x.x.101').

I'm trying to understand how a host could be blocked, and not blocked, at the same time. :)  Any ideas?

That host was blocked because there have been too many connection errors. The maximum number of errors before a host is blocked is represented by the max_connection_errors variable. Before reaching that number of errors, the host was not blocked.

The FLUSH HOSTS commands (or mysqladmin flush-hosts) empties the host cache. After that, the host can connect again and the errors counter is reset.


So if I understand correctly, you want the latest status and be able to filter on it. I don't know what version of SQL Server you're using but APPLY has been out since 2005 so try this out:

SELECT  DISTINCT TagNo,
        TruckNo,
        CA.VerifiedDateTime,
        CA.Status
FROM yourTable A
CROSS APPLY
(
    SELECT TOP 1 VerifiedDateTime,Status
    FROM yourTable
    WHERE TagNo = A.TagNo
    ORDER BY VerifiedDateTime DESC
) CA
WHERE CA.Status = 'A';


How to match column names and values from joining and related tables? I have the following DB structure:

cars:

id
make

features:

id
name

cars_feature:

id
car_id
feature_id
value

I want to be able to select all cars with all features and return results with feature.name as the column name and cars_feature.value as the value. Right now I am able to get all feature and all values but I only figured out how to do that with group_concat.

What I am looking for is the following output:

car_id  car_make  color  wheels  doors
1       Ford      blue   alloy
2       Audi             alloy   3

Data example: SQLFiddle

With the information you gave, I think what you're trying to achieve is a Pivot table. I'd use CASE to make the pivot with your same query:

SELECT
    cars.id as car_id,
    cars.make as car_make,
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color,
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels,
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
FROM cars
LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
LEFT JOIN features ON (car_feature.feature_id = features.id)
GROUP BY cars.id;

Test:

mysql> SELECT
    -> cars.id as car_id,
    ->     cars.make as car_make,
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color,
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels,
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
    -> FROM cars
    -> LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
    -> LEFT JOIN features ON (car_feature.feature_id = features.id)
    -> GROUP BY cars.id;
+--------+----------+--------+--------+-------+
| car_id | car_make | color  | Wheels | Doors |
+--------+----------+--------+--------+-------+
|      1 | Ford     | Blue   | Alloy  |       |
|      2 | Audi     | Yellow |        | 4     |
+--------+----------+--------+--------+-------+
2 rows in set (0.00 sec)

mysql>

Try it on this Fiddle


Log shipping confusion I have a database that is currently shipping log files to a standby server.

I ship the log from the primary to the standby every 5 minutes

Every 15 minutes, a restore Agent Job runs on the standby to import the transactions into the standby database.

I want to be sure I understand how to bring the standby server online in case the primary fails BEFORE I am in that scenario. Everything I have been reading online tells me that I simply need to run restore database }}dbname{{ with recovery to accomplish that.

Questions I have are:

1) Is that the correct code to run?
2) Does that automatically
restore any transaction logs  that have shipped since the last
restore Agent Job was ran?
3) Once the restore Agent Job runs, do I
need to keep the .trn files?

I have failed to locate any resources that fully explain the actual switch over process. Lots out there on how to set up log shipping though.

My ultimate goal is to get to a situation where I am doing a full backup each night, shipping it to the standby server and restoring it, deleting all the .trn files prior to the full back up,  then do log shipping each 5 minutes, restoring them at 15 minute intervals.

1) Is that the correct code to run?

Well, kind of. It would probably look a bit more like this:

RESTORE DATABASE [YourMom] WITH RECOVERY

  2) Does that automatically restore any transaction logs that have shipped since the last restore Agent Job was ran?

No, it's your job to make sure that Log Shipping is caught up, and if possible (if the primary is still available), take a Tail of the Log (sounds like a bar, I know) backup, which will grab any remaining transactions, and put the database on the primary in a restoring state (will cut off any current users). The TOL backup is a usually a manual step, unless you have third party tools (like Quest LiteSpeed to help).

  3) Once the restore Agent Job runs, do I need to keep the .trn files?

This isn't a yes or no question. You should keep all backups according to your data retention policy.

Hope this helps!


Search all columns in list of tables for particular string I have a list of market data tables in one of our environments and need to search every column in the list for a certain string. Can anyone point me in the right direction.

1> select @@version
2> go

  Adaptive Server Enterprise/15.0.3/EBF 16558 ESD#1/P/Solaris AMD64/OS 5.10/

Thanks

You can query syscolumns and sysobjects in each database to get the information you need.

select
  distinct so.name as TABLE_NAME,
  sc.name as COLUMN_NAME
from
  syscolumns sc,
  sysobjects so
where
  sc.id = so.id
and   sc.name like '%searchstring%'
order by so.name

If you need to limit your search to specific tables, then you can add an additional 'and' clause:

and so.name in ("Table1","Table2","Table3")