How to transfer the ownership of the dbo schema in SQL Server 2012 I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot:

Transfer ownership to another user
Drop the user from DB (though I can delete login in the SQL Server)

I tried The database principal owns a schema in the database, and cannot be dropped.

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo

And while it completed successfully, the user still has ownership, and it's greyed out so I can't seem to do it in the UI either.

Found a solution:

In addition to Arron's answer, I realised that I'd run the above command in wrong DB (facepalm!). Once the DB had been corrected both the above SQL and the answer below worked.

Slightly different approach would be to give the schema back to the original owner/schema, instead of to dbo:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;


Creating a view on a non-existing view I have a whole bunch of views that I need to create. Many of these views depend on other views.

If I create a view that depends on a view not yet created, I get an Invalid Object error.

Rather than go through the whole lot and work out the dependencies and then deal with creating them all in the correct order, is there a way I can just switch this checking off until all the views are created?

It is possible to delete a dependant view after the view is installed, so it is possible for the database to be in a state where a view exists that depends on a non-existent object. I need it to accept being in this state during creation..

To my knowledge it's not possible to create a view that is missing objects referenced in that view.

Why is it such a pain to know what the dependencies are on your views?  You are CREATING them, right?

Do they already exist in another database?  If so you can use SSMS or SMO to script them out in dependency order and bypass this issue.


Getting Error ORA-00902 - Invalid Identifier when creating a foreign key I'm using the following script to create tables:

DROP TABLE Vehicle_type;
CREATE TABLE Vehicle_type
(
Vehicle_type_id INT PRIMARY KEY,
Model VARCHAR2(15),
Make VARCHAR2(15)
);

DROP TABLE Vehicle;
CREATE TABLE Vehicle
(
Registration VARCHAR2(7) PRIMARY KEY,
FOREIGN KEY (Vehicle_type_id) REFERENCES Vehicle_type(Vehicle_type_id),
GVW INT,
Year INT,
Body VARCHAR2(15)
);

Which creates the Vehicle_type table just fine, but when it gets to the foreign key in the Vehicle table I get the error ORA-00904: "VEHICLE_TYPE_ID": invalid identifier.

It's like the Vehicle_type_id column doesn't exist, but it definitely does because it shows up in SQLDeveloper.

Your syntax is off. You need to actually define the column that has the constraint on it, and add the constraint at the end.

CREATE TABLE Vehicle
(
  Registration VARCHAR2(7) PRIMARY KEY,
  Vehicle_type_id INT,                      -- add this
  GVW INT,
  Year INT,
  Body VARCHAR2(15),
  CONSTRAINT vehicle_type_fk                -- constraint name
    FOREIGN KEY (Vehicle_type_id)           -- constrained column
    REFERENCES Vehicle_type(Vehicle_type_id)
);

Also you'll need to drop the vehicle table before the vehicle_type table, otherwise you'll get a dependency error:

ORA-02449: unique/primary keys in table referenced by foreign keys


Offset vs Where performance for pagination with index Suppose that you have a list of millions of items, each one with a timestamp (created_at).

You also have an index on created_at DESC.

Sometimes you need to navigate through all items, chronologically, and obviously you will use pagination on created_at.

Now I have a question about pagination on big data: considering that there's an index, and no other filter conditions on items, is there any performance difference in using OFFSET or using WHERE created_at > last_item_timestamp?

I mean, is there any difference in the way that the btree index will be accessed? Is there any difference in the amount of data that will be loaded from disk?

I ask this because I would say that there's no difference in the traversal of the btree, using OFFSET or WHERE, but from some quick tests that I have made it seems to be a performance difference. In particular I am testing this with PostgreSQL 11.

Update: after more reasoning I think that there's actually a difference in the number of tree nodes that have to be read. In case of OFFSET you need to read all the N nodes before the item, while with WHERE you only need to read log(N) nodes (because you don't need to count them).

Yes, there is a performance difference, especially with big OFFSETs. With OFFSET you ORDER BY your indexed column, and then limit the result set by removing the first x rows indicated by OFFSET x. All the rows still have to be read from disk.

With WHERE you ORDER BY your indexed column, but then only read the range of rows specified by your indexed WHERE condition from disk.

For more details, see: We need tool support for keyset pagination (On the Use The Index, Luke blog)


Assigning a domain user to run MSSQLSERVER service 2008R2 I have done this on a server (windows server 2008) before, but can not achieve the same thing on another virtual server

I want a low level domain user to run the MSSQLSERVER service so that I can enable backup to a remote network location, this is working fine on my other SQL Server 2008R2 instance.

I have a domain user called fred@mydomain I have added this user to the server local administrator group and the group SQLServerMSASUser$**MYSERVER**$MSSQLSERVER. This is the group I believe is the one that allows members to run the MSSQLSERVER service

I have created the login in SQL Server for the user and added him to all the server roles but dbcreator and mapped it to all the instance databases as a public database role

HOWEVER when I go to SQL Server Configuration Manager to change the MSSQLSERVER service and selected the fred@mydomain and press apply I get a log on error.

Can anyone suggest what I might have missed please ? I have used Here as my reference

If you check the event log, does the event say something about needing "log on as service" rights?

In my experience you will need to add 'log on as service' rights to the account you select. Although the SQL Server Configuration Manager is supposed to handle this, I've found that it does not always. Try the following:

Log on to your domain controller and load the group policy management console (depending on your AD version)
add 'log on as a service' to the account you created for the SQL Server service in an existing or new group policy object. (maybe ask a sysadmin to assign this right for you)
Load SQL Server configuration manager and set the service account to the account you created


Grouping data based on a condition Effectively I want to be able to group data based on a condition.

I have:

I want the data grouped using value effectively as a breakpoint.

I know how to do this with a cursor and some relatively simple logic.  However I'd rather use a set based solution.

use CTE & ROW_NUMBER() function

declare @T TABLE
(name nvarchar(55),surname nvarchar(55), dates datetime, value decimal(18,2))

insert into @T values ('David','Example','2015-08-24 15:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 15:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 16:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 16:00:00.000',7.8)
insert into @T values ('David','Example','2015-09-20 20:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-20 20:00:00.000',7.7)

;with cte as
(SELECT name,surname, [Dates],[Value],ROW_NUMBER() OVER (PARTITION BY cast(dates as date) ORDER BY value desc)  AS RN
FROM @t)

select name,surname, [Dates],[Value] from cte
where RN = 1


Is query efficiency affected by data not relevant to the query? Say I have roughly 200 million records and have an indexed column user_id on a product table. If I select back 1 million records by doing the following query :

select * from product where user_id = 1 (Assuming my user_id is indexed.)

Would the other 199 million rows' column data affect the efficiency of my query ?

Example :

Consider 199 million rows were completely populated (containing blobs / varchars)
Consider 199 million rows were only partially populated, and carried a lot of null values

I've always understood this as the query would perform the same in either scenario - since user_id is indexed and could bypass traversing through all the data - but I'd like to get confirmation on this.

If there's another question / resource that answers this - feel free to redirect me elsewhere. Just looking for some confirmation (and an explanation if my assumption incorrect).

Thanks.

Summary:  It depends on

Percentage of rows being fetch (at least according to imprecise statistics)
Size of buffer_pool (relative to size of table)
ENGINE
How much off-record blobs, etc, you have.
Whether the data is somewhat ordered by user_id.

Details:

For smaller numbers, I would simply say "No".  But you describe a very large table -- possibly large enough to have issues with caching.

Let me start by explaining a few things...

user_id = 1 is a tiny percentage of the table, so it is very likely that INDEX(user_id) will be used.
I am assuming you are using InnoDB.
The lookup will work this way:  Reach into the index for the first index row with user_id = 1; scan forward until that no longer holds true (1M rows as you say).  For each of those index rows, reach into the data BTree to get * (using the PRIMARY KEY).
If the data is not effectively sorted by user_id, that will be upwards of 1M lookups in the data.  InnoDB blocks are 16KB (by default).  So this might involve 16GB of data being loaded.
You mention blobs, etc.  They are probably stored 'off record', that is, in some other block.  Maybe another 16GB to be fetched?  If you don't need the blobs, don't say SELECT *, specify only what you want -- this is an example of a significant speedup.
If the other rows are full of NULLs, then that changes how many rows are packed in each 16KB block.  This may (but probably won't) mean that you won't need a full 1M blocks for the desired rows.  That is, the caching in the buffer_pool may be useful.
What is the value of innodb_buffer_pool_size?  If it is much less than 32GB, then there will be a lot of I/O.  If it a not more than 32GB, then a second run of that same query will find everything in cache, and run a lot faster.
If it weren't a "tiny percentage", but instead over about 20%, the Optimizer would probably decide that a table scan is cheaper than bouncing back and forth between the index's BTree and the data BTree.

(The EXPLAIN will point out whether the index will be used, but not the rest of the details.)


How do I differentiate between user-defined public synonyms and built-in public synonyms? I am trying to query the Oracle ALL_SYNONYMS view, but filtering by OWNER=PUBLIC returns both synonyms I have created and Oracle created ones. Is there a way to distinguish between the two?

Well, you could go by object creation date:

SELECT AO.*
  FROM ALL_OBJECTS AO
  INNER JOIN ALL_SYNONYMS ASY ON AO.OWNER = ASY.OWNER and AO.OBJECT_NAME = ASY.SYNONYM_NAME
  AND ASY.OWNER = 'PUBLIC'
  ORDER by AO.CREATED DESC

The system created synonyms should all have the same created date, equal to the creation time of the database and the earliest of this list.

You can also filter out the system owned objects being referenced by the synonym using this :

SELECT AO.*
  FROM ALL_OBJECTS AO
  INNER JOIN ALL_SYNONYMS ASY ON AO.OWNER = ASY.OWNER and AO.OBJECT_NAME = ASY.SYNONYM_NAME
  AND ASY.OWNER = 'PUBLIC'
  AND ASY.TABLE_OWNER NOT IN ('CTXSYS','EXFSYS','APPQOSSYS','XDB','SYS', 'DVSYS', 'FLOWS_FILES', 'LBACSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'ORDDATA','WMSYS','SYSTEM', 'DBMS_PRIVILEGE_CAPTURE')
  and ASY.TABLE_OWNER not like 'APEX_%'
  ORDER by AO.CREATED DESC


SQL Server 2008: how do I hide data from a DB Maintainance User? Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one.

In order to do the maintenance work, they are using the SA user.

Is there a way to make them a new user they can use, to do all of the administrative operations without being to actually see the data. So, they can see all the tables and columns, but they will be unable to see any row.

If I'm missing something, please feel free to enlighten me since my DBA knowledge is fairly poor.

Sysadmins can see all the data in the instance and there is no way to restrict the permissions of members of the sysadmin server role.

The only possible way to hide data from sysadmins is by encrypting it, using a key that is not available on the server. This is exactly what the Always Encrypted feature in SQL Server 2016 does.

In SQL Server 2008 your options are very limited: you can use a custom encryption layer, but you would have to code it yourself. Also, unlike what happens in Always Encrypted, there would be nothing to enforce that the columns you want to encrypt actually contain only encrypted data (the application has to take care of that).

That said, this is not only a tech issue: the company that does maintenance for you is a trusted partner, so you can talk to them straight and ask not to look at the data. You can also set up auditing to catch unauthorized reads/writes. However, it's a grey area: if they're troubleshooting a performance issue, for instance, they will probably run some queries and see some data.

Bottom line is: if you don't trust a third party, don't let them manage your instances.


Generating time series based on existing table I have a table that has something like this:

|    Zip    |     Population    |
+-----------+-------------------+
|    00001  |     100           |
|    00002  |     200           |
|    00003  |     250           |

and so on. I would like to generate a new table from the above table which looks something like this:

|    Date       |    Zip    |     Population    |
+---------------+-----------+-------------------+
|    01-01-2016 |   00001   |     100           |
|    01-02-2016 |   00001   |     100           |
|    01-03-2016 |   00001   |     100           |
|    ...        |   00001   |     100           |
|    31-12-2016 |   00001   |     100           |
|    01-01-2016 |   00002   |     200           |
|    01-02-2016 |   00002   |     200           |
|    01-03-2016 |   00002   |     200           |
|    ...        |   00002   |     200           |
|    31-12-2016 |   00002   |     200           |

and so on for all the rows in the original table at the top.

I have found a way to generate dates between a specific range (e.g., as outlined here). I also think that I should use LEFT JOIN to achieve that?? I'm fairly new to SQL (or SQL Server, which is what I'm using at the moment). If so, what should I use as the join key? If someone could guide me through the process, I'd greatly appreciate the help. :) Thank you in advance for the answers!

You may be interested in learning an efficient way of doing this using a set-based-methodology instead of a procedural (loop-based) methodology.

SQL Server typically performs set-based operations far more quickly than procedural operations.

The code below shows how to use a select statement to generate 366 rows containing dates from 2016-01-01 to 2016-12-31 (2016 is a leap-year).  We then combine each of those rows with the "Zip" and "Population" columns of your source table, using a CROSS JOIN.

First, we setup a test-bed with your table, and a new destination table:

USE tempdb;

IF OBJECT_ID('dbo.Zips') IS NOT NULL
DROP TABLE dbo.Zips;
CREATE TABLE dbo.Zips
(
    Zip VARCHAR(5) NOT NULL
    , Pop INT NOT NULL
);

INSERT INTO dbo.Zips (Zip, Pop)
VALUES ('00001', 100)
    , ('00002', 200)
    , ('00003', 250);

IF OBJECT_ID('dbo.ZipsWithDates') IS NOT NULL
DROP TABLE dbo.ZipsWithDates;
CREATE TABLE dbo.ZipsWithDates
(
    Zip VARCHAR(5) NOT NULL
    , Pop INT NOT NULL
    , ZipDate DATE NOT NULL
);

This inserts rows, one for each day in the year, for each row in Zips:

;WITH dates AS (
SELECT TOP(366) /* 366 days in a leap-year, which 2016 is */
    TheDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id), CONVERT(DATE, N'2015-12-31'))
FROM sys.objects o1
    CROSS JOIN sys.objects o2
)
INSERT INTO dbo.ZipsWithDates (Zip, Pop, ZipDate)
SELECT z.Zip
    , z.Pop
    , d.TheDate
FROM dates d
    CROSS JOIN dbo.Zips z

This shows the contents of the new table:

SELECT *
FROM dbo.ZipsWithDates;

And this is the output from the first few rows shows:

Note, you should always specify the schema, and please don't use reserved words for column names


Problem getting zips in radius via MySQL I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point.
It just occurred to me that, just because a zip's center point is not within a given radius, does not mean that the zip itself is not within the radius.
I used my super advanced art skills to illustrate the point here:

The green stripy blobs represent zip codes A, B and C.

The red smudges are the geographic centers for each zip code

The fuchsia dot is the target location, and..

The lumpy blue circle is a 1 mile radius from the target location

If I run a query for all the zip codes within a 1 mile radius from the pink smudge, only zip codes B and C will be returned as the center point for zip A is not within the one mile radius, even though the pink smudge itself is clearly in zip code A.
SELECT *,
        p.distance_unit
                 * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                 * COS(RADIANS(z.y))
                 * COS(RADIANS(p.longpoint) - RADIANS(z.x))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(z.y)))) AS dist
  FROM standard_zip AS z
  JOIN (   /* these are the query parameters */
        SELECT  $lat  AS latpoint,  $lng AS longpoint,
                $miles AS radius,      69 AS distance_unit
    ) AS p ON 1=1
  WHERE z.y
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND z.x
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
  ORDER BY dist

How the heck do I write a query that will include zip A in the results?
I have access to spatial/geometry for each zip code that I can add to the table if needed, but I have no idea how I would use it for this purpose in MySQL.

Edit: I spent a day reading the Oracle and MySQL docs for spatial data and managed to successfully convert my spatial data to MySQL. How do I go about writing a similar query that uses the geometry column instead of the lat and long? I am using 2D data.. the geometry are polygons and multipolygons only..
I think I sort of figured it out..
select
  *
from
  (
    select
      MIN(st_distance(geom, POINT(-82.765136, 28.0914015))) * 69 as miles,
      zip
    from
      zip_spatial
    group by
      zip
    order by
      miles asc
  ) d
where
  d.miles < 5

I'll leave the bounty open for now in case someone has a better, more efficient solution.

From Indexing and Querying Spatial Data in Oracle in the Oracle® Spatial Developer's Guide 11g Release 2 (11.2):

  Querying Spatial Data

  Spatial uses a two-tier query model with primary and secondary filter operations to resolve spatial queries and spatial joins. The term two-tier indicates that two distinct operations are performed to resolve queries. If both operations are performed, the exact result set is returned.

  You cannot append a database link (dblink) name to the name of a spatial table in a query if a spatial index is defined on that table.

  Spatial Query

  In a spatial R-tree index, each geometry is represented by its minimum bounding rectangle (MBR). Consider the following layer containing several objects in Figure1. Each object is labeled with its geometry name (geom_1 for the line string, geom_2 for the four-sided polygon, geom_3 for the triangular polygon, and geom_4 for the ellipse), and the MBR around each object is represented by a dashed line.

  Description of "Figure1 Geometries with MBRs"

  A typical spatial query is to request all objects that lie within a query window, that is, a defined fence or window. A dynamic query window refers to a rectangular area that is not defined in the database, but that must be defined before it is used. Figure2 shows the same geometries as in Figure1, but adds a query window represented by the heavy dotted-line box.

  Description of "Figure2 Layer with a Query Window"

  In Figure 2, the query window covers parts of geometries geom_1 and geom_2, as well as part of the MBR for geom_3 but none of the actual geom_3 geometry. The query window does not cover any part of the geom_4 geometry or its MBR.

  Primary Filter Operator

  The SDO_FILTER operator, implements the primary filter portion of the two-step process involved in the Oracle Spatial query processing model. The primary filter uses the index data to determine only if a set of candidate object pairs may interact. Specifically, the primary filter checks to see if the MBRs of the candidate objects interact, not whether the objects themselves interact. The SDO_FILTER operator syntax is as follows:

SDO_FILTER(geometry1 SDO_GEOMETRY, geometry2 SDO_GEOMETRY, param VARCHAR2)

  In the preceding syntax:

  geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.
  geometry2 is an object of type SDO_GEOMETRY. This object may or may not come from a table. If it comes from a table, it may or may not be spatially indexed.
  param is an optional string of type VARCHAR2. It can specify either or both of the min_resolution and max_resolution keywords.

  The following examples perform a primary filter operation only (with no secondary filter operation). They will return all the geometries shown in Figure2 that have an MBR that interacts with the query window. The result of the following examples are geometries geom_1, geom_2, and geom_3.

  Example1 performs a primary filter operation without inserting the query window into a table. The window will be indexed in memory and performance will be very good.

  Example1 Primary Filter with a Temporary Query Window

SELECT A.Feature_ID FROM TARGET A  WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
                                       SDO_elem_info_array(1,1003,3),
                                       SDO_ordinate_array(x1,y1, x2,y2))
                           ) = 'TRUE';

  In Example1, (x1,y1) and (x2,y2) are the lower-left and upper-right corners of the query window.


What's the best way to ensure referential integrity on a replicated database? Using SQL SERVER 2005, I have a couple of questions on Replication and referential integrity.

1) Does Replication handle referential integrity?

2) If I do an Insert to Parent table and then to Insert to Child table, in one transaction, on Source DB - will Replicated DB also behave in the same manner? I.e. In Replicated DB record must be present in Master table, before it is referenced in child table?

Replicated data (articles) maintain the same data and schema across the subscribers as that on the publisher. In the case of transactional replication, the data changes are applied within the same transactional boundaries as those happening at the publisher.

In short, the referential integrity will be maintained.


MySQL Master/Slave. Actual use of Slave Machine We have a MySQL server running in a mobile production trailer.  The trailer is out on the road for weeks at a time.  We have cell internet and wifi access is nearly all of our locations.  Our office staff (bookkeeper, HR, etc) are at a brick and mortar office.

There are many times when the office staff need to access information but if the trailer is traveling or after hours then clearly we aren't online.

I was thinking/hoping we could replicate from the Trailer (Master) to the Office (Slave).  This way the office web servers (duplicated of the trailer web server) would be able to access nearly up to date information.  It wouldn't be 100% live but it's better then waiting a week or two for the trailer to come home.

The office would need to be able to write to the database.. BUT these changes CAN be overwritten during replication.  All of the work they do is simply reading data.  The few writes they would perform are inconsequential and would not matter if they were overwritten.

Am I going about this the right way?  Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Thanks!

Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Short answer: Yes, but they probably shouldn't.

Depending on how your application works, it is possible (even likely) that writing to the slave will break replication. Even if it doesn't, over time it's likely that your databases will develop split brain - for example, someone could change some data on your slave that remains undetected for a long period of time.

If you are able to maintain two MySQL servers in your office, I would suggest the following:

Master server #1 on your trailer
Replicant server #2 in your office, acting as slave to #1
"Working" server #3 in your office

Server #3 would be the server that your staff connect to. Set up a job - maybe one that runs each night - which takes a backup of server #2 and then restores it over the top of server #3. This way staff can work with fairly recent data without the risk of affecting replication.

You could even make server #3 a slave to server #2 if it looked like that might work, though I suspect this might cause more problems than it solves.

Public cell and wifi networks are unlikely to be as secure as you like - I'm assuming that you will secure the connection to your office via VPN. If not, MySQL can secure communications via SSL and you may want to consider that.


XML column list specific value I have an xml column that looks like this:

<document>
      <item>
        <key>
          <string>Michael Burry</string>
        </key>
        <value>
          <string>CDO</string>
        </value>
      </item>
      <item>
        <key>
          <string>Adam Smith</string>
        </key>
        <value>
          <string>£20</string>
        </value>
      </item>
      <item>
        <key>
          <string>World Cup 2018</string>
        </key>
        <value>
          <string>football</string>
        </value>
      </item>......

Instead of listing the entire contents of the column, I want to instead list only the value of <string>football</string> when the first value is <string>World Cup 2018</string>.

Using value('(/document/item/key/string)[7]', 'nvarchar(max)') is not suitable as "World Cup 2018" can appear anywhere.

If you just want a single value then you can just use the value method of the XML datatype with a little bit of XPath that translates to:

  Get me the first value of ...key/string which has a sibling element of
  ...value/string under the same item element with the value "football"

Something like this:

DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @yourTable ( yourXML )
SELECT
'<document>
      <item>
        <key>
          <string>Michael Burry</string>
        </key>
        <value>
          <string>CDO</string>
        </value>
      </item>
      <item>
        <key>
          <string>Adam Smith</string>
        </key>
        <value>
          <string>£20</string>
        </value>
      </item>
      <item>
        <key>
          <string>World Cup 2018</string>
        </key>
        <value>
          <string>football</string>
        </value>
      </item>......
</document>'

SELECT *, yt.yourXML.value('(document/item[key/string[.="World Cup 2018"]]/value/string/text())[1]', 'VARCHAR(50)') result
FROM @yourTable yt


Restoring Database to a smaller test envrionment I don't believe this is possible but wanted to check.

I've got a bi-weekly job running to restore the full backup to a test environment so the devs have something fresh to destroy without damaging the actual environment. The problem is cropping up that the test environment has less space than the production environment (it's stuck on one drive instead of a number of drives).

When copied across the database is now using over 95% of the hard disk allocation that it has. Is there any way along side the Restore Database WITH MOVE to resize those files? There is free space in a number of the files that could be shrunk and the file size is never going to change from the backup as it doesn't have data getting thrown at it so technically they can get cut all the way back to their data size.

The alternative, which I don't really want to do, is shrink the files on the production environment that have a bit too much space available at the minute.

Any tips or ideas of anything to do? Adding a new hard disk unfortunately isn't an option as the drive bay attached is full.

Ste

You are stuck there. There is no option to re-size the files during restore. The backup is made by dumping the data pages directly to the backup device.

There is the option of exporting/importing the data using SSIS but that takes time and it would probably be cheaper to buy a new larger hard drive and take the test server down and exchange it's drive.


Selecting random rows from database initially while preserving that order later I am building a quiz app, in which the student can login and can select the option of give exam. I want to show multiple choice questions to students randomly.

I stored all the questions in one table. For example, I stored 20 questions for a chemistry exam. Now I want to show 10 random questions to students. I don't want to select questions randomly every time when user refresh the page. Currently I am doing:

SELECT * from table-name order by rand() limit 5;

The problem is this query selects random rows every time the user refreshes the page. This is my code:

<?php
$query="SELECT * from question_bank order by rand() limit 10";
$result= mysqli_query($connection,$query);
confirm_query($result);

?>

<form action="result.php" name="form1" method="post" id="quiz">
<?php
while ($read_all_data = mysqli_fetch_assoc($result))
{
    $_SESSION['id']=1;

$id=$read_all_data['question_id'];
$a=$read_all_data['a'];
$b=$read_all_data['b'];
$c=$read_all_data['c'];
$d=$read_all_data['d'];
 echo $read_all_data['question']."
";
 echo "A:<input type ='radio' value ='a'  name='$id' >".$read_all_data['a']."
";
 echo "B:<input type ='radio' value ='b'  name='$id' >".$read_all_data['b']."
";
 echo "C:<input type ='radio' value ='c'  name='$id' >".$read_all_data['c']."
";
 echo "D:<input type ='radio' value ='d'  name='$id' >".$read_all_data['d']."
";

}

You'll want to hold an actual random number for each user then you can call

rand($usersrandomnumber)

to generate your static random generator start point

the $usersrandomnumber acts as a starting seed

see the mysql documentation here:
https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

it will involve you storing the user's seed in a table somewhere, or cookie /local/server storage but will get you what you're after


Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8 I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04

Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it.

I have 2 same Innodb tables, 1 was created and filled before updating and has right numbers of Rows / Size when querying:

SHOW TABLE STATUS FROM dbname;

Another table shows me 16Kb size and no rows, But its now ~8GB and 4.5 millions rows there. I tried to make Analyze Tablename / Update in Client (Heidisql).

I can't find anything in Google about problem like this. I choose table manually and check rows count, but when I'm using mysql visual clients like Phpmyadmin / HeidiSql it shows me no data in this table.

I've got suggestion to make Check and after it Analyze, and it worked for me! Now size and other table service information updated. I will try to find why its not updated automatically and post here.


Maintaining data integrity when one table has multiple relationships, but only one should be used. (is this bad design?) I'm working on a database that holds laboratory medical testing orders. The issue I have (maybe) is that I have a table that holds an order number, and a test code. There is also a TestNumber PK.
There are two other tables that have a relationship with this table. One of them holds information for testing urine samples, the other holds information for testing blood samples. Depending on the test code in the first table, an additional record should be inserted into one of the related tables. The problem I have is there is nothing stopping a record being inserted into the wrong table for the test code.

Here's a simplified image of the table diagram showing what I'm talking about:

So if a test code is inserted into the LabOrders table and it's sample type is effectively "Blood," then a record should also be inserted into the table BloodTestDetails. But there is nothing stopping a record from also being inserted (or instead, being inserted) into the UrineTestDetails table... Is there a way I can prevent this and maintain data integrity, or is this a bad design?

If it's a bad design, what kind of recommendation would someone make to hold this type of data?

I've worked with six or seven different laboratory information management systems (LIMS) over my years in IT.

The general structure those systems have used tends to be something like:

Accessions (This is where the case begins.)
AccessionKey PK
AccessionDate
CreatedBy
{other columns as needed}

Patients (This holds information about the case's patient)
PatientName
PatientBirthDate
{other columns, including foreign keys to the address entries, etc.}

AccessionPatients (links an existing patient to a new case)
AccessionPatientKey PK
AccessionKey FK
PatientKey FK
{etc.}

Specimens (this holds information about any specimens captured from the patient)
SpecimenKey PK
AccessPatientKey FK
SpecimenTypeKey FK
CollectionDate
(etc)

SpecimenTypes (this holds information on the different kinds of specimens)
SpecimenTypeKey PK
SpecimenTypeName
SpecimenContainerTypeKey FK (link to a container type table OR just a text field for EDTA container, or specimen cup, or blood tube or...)
{etc}

SpecimenTestRequests (This holds what tests have been ordered against what specimens)
SpecimenTestRequestKey PK
SpecimenKey FK (the specimen that this test was requested against.)
TestKey FK (The test that was requested.)
RequestDate
{etc}

Tests (this stores information about the tests the lab can perform)
TestKey PK
TestName
CreatedDate
EndDate
TestFee
{etc}

TestResultTypes (This tracks the kinds of results a test can have)
TestResultTypesKey PK
TestKey FK
TestResultName  (display name for this answer in this test)
UnitsKey FK (link to a units of measure table)
TestResultDataType (free text, dropdown list name, numeric, etc.)
TestResultListName (FK to a lists table or NULL)
DisplayOrder (numeric, to set where in the results this appears)
{etc.}

TestResultLists (this holds valid list entries for drop-down results)
TestResultListKey PK
TestResultListName (used in TestResultTypes)
TestResultListValue (what shows in the dropdown list)
(Etc.)

TestResults (This is where actual results are stored)
TestResultKey PK
SpecimenTestRequestKey FK (what test was requested for what case?)
TestResultTypeKey FK (what answer is this the result for?)
TestResultAnswer (long text that holds the actual result)
EnteredDate
EnteredBy FK (the user who entered the result)
ReleasedBy FK (the user who released the results)
ReleasedDate
(etc)

Units (Units of measure table for values like mg or mg/dL)
UnitsKey PK
UnitsText
(etc)

There are usually several other tables wrapped up in a LIMS -- history tables to track changes over time (audit trails), user tables, permissions tables, address tables, sometimes fee schedule tables, diagnoses tables, and so on. I'm looking through the schema for one of the LIMS I've supported and it has 457 tables. Another has 465 tables.

Anyway, this creates a case or accession, connects a patient to that case (if your system has multiple patients on 1 case, then you'll need a linking table in between). Then one or more specimens are assigned to the case with specimen types. Next, tests are requested for each specimen. Each test can have 1 or more result values. Then there's a results table to provide the results for each test.

A full LIMS would also have reference ranges (your Blood Glucose was 100. It should be between 80 and 120, so you are within normal ranges). It might also have the idea of Panels (a group of tests performed together, like a CBC panel), which can be defined and ordered as a group rather than as individual tests...)

But hopefully this helps.


Identify records with one identical field, but different timestamp I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on.

My current query uses this line in the SELECT statement to identify records that have a field that occurs elsewhere in the database:

CASE SIGN(COUNT(*) - 2) WHEN -1 then 0 else 1 END AS "REPEAT_OFFENDER_BIT"

So basically it says "if there are multiple records with this field (which is determined by my GROUP BY clause), put a one in the REPEAT_OFFENDER_BIT column, else put a zero.

I also need to make sure that the timestamps associated with these records it finds as having a certain common field are not the same.  Only then would the record get a one rather than a zero.

Let's say my table columns are these:

fname | lname | ssn | chargeddate

A row should get a one for REPEAT_OFFENDER_BIT only if there exists another record with the same SSN and a different charged date.

What are my options for achieving this?  My SQL is quite primitive so I came here for help.  Thank you!

Sample data:

create table offender
(
  fname varchar2(10),
  lname varchar2 (10),
  ssn number,
  chargeddate date
);

insert into offender values ('A', 'A', 1, date'2015-08-01');
insert into offender values ('B', 'B', 2, date'2015-08-02');
insert into offender values ('C', 'C', 3, date'2015-08-03');
insert into offender values ('D', 'D', 4, date'2015-08-04');
insert into offender values ('E', 'E', 1, date'2015-08-01');
insert into offender values ('F', 'F', 2, date'2015-08-05');
insert into offender values ('G', 'G', 3, date'2015-08-06');
insert into offender values ('H', 'H', 4, date'2015-08-04');
commit;

The query:

select
  fname,
  lname,
  ssn,
  chargeddate,
  decode(count(distinct chargeddate) over (partition by ssn), 1, 0, 1) as repeat_offender_bit
from
  offender;

FNAME      LNAME             SSN CHARGEDDATE         REPEAT_OFFENDER_BIT
---------- ---------- ---------- ------------------- -------------------
A          A                   1 2015-08-01 00:00:00                   0
E          E                   1 2015-08-01 00:00:00                   0
B          B                   2 2015-08-02 00:00:00                   1
F          F                   2 2015-08-05 00:00:00                   1
C          C                   3 2015-08-03 00:00:00                   1
G          G                   3 2015-08-06 00:00:00                   1
H          H                   4 2015-08-04 00:00:00                   0
D          D                   4 2015-08-04 00:00:00                   0


Mariadb Galera cluster empty set I am playing around with a MariaDB Galera Master Master Cluster and I have some issues which I do not understand.
[mysqld]
bind-address=0.0.0.0

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
default_storage_engine=InnoDB
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.2.67'
wsrep_node_name='KVM-1'
wsrep_sst_method=rsync
wsrep_sst_auth=repl_user:PASS

On the other nodes

[mysqld]
bind-address=0.0.0.0

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
default_storage_engine=InnoDB
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://IP_ADDR_MAIN_NODE,OTHER_NODES_ADDRS"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.2.68'
wsrep_node_name='KVM-2'
wsrep_sst_method=rsync
wsrep_sst_auth=repl_user:PASS

when i login to mysql and query for

SHOW STATUS LIKE 'wsrep_cluster_size';
Empty set (0.00 sec)

Am i missing anything?

stop all nodes.
On first node run -> galera_new_cluster
Once it is started.
Then on Second node run - > service mysql start


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

There are no "non-transactional" statements; in the absence of explicit transaction control by default a transaction simply starts at the beginning of statement execution and ends when the statement completes. As a result, any query will have to wait for a conflicting lock and might encounter a lock timeout or a deadlock.


Using Regex in mySQL query I have a database column with multiple names separated by line breaks like this:

jones, sue
jones, mark

I want to generate a query that keeps the first line and discards the rest, no matter how many lines there are...

I though I was getting close with this:

SELECT
    name1
FROM
    email
WHERE
    name1  REGEXP '.*(\r\n)?'

but not quite...


Unable to create connection on MySQL Workbench Edition in Mac? I have installed MySQL Workbench on my Mac. I am trying to connect the MySQL. I am not able to connect.

I have attached the screenshot.

I am getting Access denied for user 'root'

Common issues:

Is MySQL server running? check that by making sure you have 1 or more processes called mysqld
Is MySQL listening on the 127.0.0.1 address? Make sure it is not running with skip-netwoking or with bind-address different from 0.0.0.0, *, or 127.0.0.1 (it could be only listening on a public ip)
Is there an user called root? It should be there if you just installed it
Did you write the right password- in the latest MySQL versions, mysql gets installed with a random default password. You need to know that or connect to the socket (localhost + socket, not 127.0.0.1 + 3306) using the unix_autentication plugin
What error do you get? It is not the same to get a "could not connect" than a "connection denied"- the first one could be points 1 and 2, the second 3 and 4. Can you connect using the command line "mysql"?
Do you have the latest mysql workbench version? Latest authentication methids may not be supported on old workbench versions

This is a very common question, please read the manual first for troubleshooting: https://dev.mysql.com/doc/refman/5.7/en/can-not-connect-to-server.html


Migrate from Standalone Instance to Failover Node Instance I am fairly new to the MS SQL and i am currently working on a project at my company. We have a ERP System built on a SQL Server and we want to create a SQL failover node.
The original configuration is like this :
SQL Standalone Instance SERVER -> SAN Storage (Storage for MDF & LDF).
Is it possible to create a new server, install SQL Server Failover Instance from scratch, then connect the same primary SAN Storage. Import all the user credentials from standalone (original instance) to the new server.?
And when ready to migrate, we simply turn off the Standalone SQL Server, and attach all mdf files in the new server? will this method be good to migrate from standalone instance to failover instance?
Then we have a one node sql cluster available for users to work on
Later we uninstall standalone instance and install a failover sql node on the source Server, then WSFC etc, making it 2 node SQL Cluster?
I thank you for any help that you could provide.
Asif

This is not really advisable. Install and validate your WSFC then install and test the clustered SQL Server instance on each WSFC node. SQL Server can be really picky about installing to additional nodes of a WSFC cluster and you could easily find it failing leaving you with a one node cluster.
Also storage has a level of indirection for a cluster with ownership of a disk(s) that contains the datafiles for an instance being passed around as necessary.


List all tables that a specific login has permissions This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any insight will be helpful.
PS: I'm not very good with TSQL. :(

Thanks a lot.

the script I use will show you all objects (SP, Tables, Functions) for a specified Database name and all the users that have rights on them, but you can narrow the search and extract exactly what you need.

DECLARE @EXEC_SCRIPT    NVARCHAR(4000)
DECLARE @DBNAME         NVARCHAR(256) = 'DB name'

SET @EXEC_SCRIPT = 'USE ['+ @DBNAME + ']
select DB_NAME ()                   as [db_name],
a.class_desc                        as [class_type] ,
user_name(a.grantee_principal_id)           as [user_name] ,
isnull(OBJECT_NAME(a.major_id),'''')            as [object_name],
isnull      (b.name,'''')               as [column_name] ,
a.permission_name                   as [permission_name] ,
a.state_desc                        as [permission_type] ,
c.type_desc                     as [object_type],
CASE d.type
                WHEN ''A'' THEN N''ApplicationRole''
                WHEN ''R'' THEN N''Role''
                ELSE N''User'' END  as [security_type],
CASE WHEN a.major_id  < 0 THEN N''Y''
     ELSE N''N'' END                as [system_object],
d.is_fixed_role                     as [is_fixed_role]

from sys.database_permissions a left join sys.columns b
on a.minor_id = b.column_id and a.major_id = b.object_id
left join sys.objects c on  c.object_id = a.major_id
INNER JOIN sys.database_principals AS d ON d.principal_id = a.grantee_principal_id
where a.class_desc <> ''DATABASE''  and user_name(a.grantee_principal_id) <> ''public''
order by user_name'

EXEC master.dbo.sp_executesql @EXEC_SCRIPT


INSERT Statement error message references column I am not including in query I am using MSSQL 2008.

My INSERT statement is as follows:

INSERT INTO [DB1].[dbo].[SpotArtSong](ID)
SELECT  SNG.S_ID
FROM [DB1].[dbo].[SpotArtSong] SPOT
INNER JOIN [DB2].[dbo].[nt-art] ART
ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
INNER JOIN [DB2].[dbo].[nt-sng] SNG
ON ART.Artist_ID  = SNG.Artist_ID
AND left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE spot.BDSID is NULL

Column [ID] on [DB1].[dbo].[SpotArtSong] has some null values that I am trying to replace. I have found the replacements and when looking over the full column results, I am happy with what the search would return from SNG.S_ID. I am trying to insert that one column into the one column on [DB1].[dbo].[SpotArtSong].

Essentially, I scrapped some data into [DB1].[dbo].[SpotArtSong] and I am matching it to some existing production data split among [DB2].[dbo].[nt-art] (ART) and [DB2].[dbo].[nt-sng] (SNG). I know the data in ART and SNG is correct, I am correlating it with the scrapped data in SPOT

The failure statement says:

Cannot insert the value NULL into column 'Artist', table
'DB1.dbo.SpotArtSong'; column does not allow nulls. INSERT
fails.
The statement has been terminated.

I am not inserting anything there so I am not sure why it would fail. Plus, if I comment out the insert statement, the select statement does not return any NULL values.

The table SpotArtSong which you are trying to insert some records has at least these columns:

ID
Artist

You are just supplying a value for ID, but column Artist has a NOT NULL constraint. When you don't supply a value for a column (by omitting it from the INSERT list), NULL is inserted by default unless there is a DEFAULT constraint. Because this column doesn't allow NULL values, the insert statement fails.

Either supply a value for the Artist column (and any other column that is NOT NULL) or change the NOT NULL constraint on that column.

To supply the artist name, add the Artist column name to the INSERT and supply a value through the SELECT, like the following:

INSERT INTO [DB1].[dbo].[SpotArtSong](
    ID,
    Artist)
SELECT
    ID = SNG.S_ID,
    Artist = ART.Artist_Name
FROM
    [DB1].[dbo].[SpotArtSong] SPOT
    INNER JOIN [DB2].[dbo].[nt-art] ART ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
    INNER JOIN [DB2].[dbo].[nt-sng] SNG ON
        ART.Artist_ID  = SNG.Artist_ID AND
        left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE
    spot.BDSID is NULL

To change the NOT NULL constraint  use ALTER TABLE (note the lack of NOT NULL at the end):

ALTER TABLE [DB1].[dbo].[SpotArtSong] ALTER COLUMN Artist VARCHAR(100)

Althought it seems to me that you are actually trying to do an UPDATE instead of an INSERT:

UPDATE SPOT SET
    ID = SNG.S_ID
FROM
    [DB1].[dbo].[SpotArtSong] SPOT
    INNER JOIN [DB2].[dbo].[nt-art] ART ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
    INNER JOIN [DB2].[dbo].[nt-sng] SNG ON
        ART.Artist_ID  = SNG.Artist_ID AND
        left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE
    spot.BDSID is NULL


I used the basis of a pivot table query on StackOverflow. Here is a similar post to the one that I used in my query below. Here is a more general link for pivot tables.

SELECT [Range]
       ,[0-20000]=ISNULL(SUM([0-20000]),0)
       ,[20001-21000]=ISNULL(SUM([20001-21000]),0)
       ,[21001-22000]=ISNULL(SUM([21001-22000]),0)
       ,[22001-23000]=ISNULL(SUM([22001-23000]),0)
       ,[23001-24000]=ISNULL(SUM([23001-24000]),0)
FROM (
    SELECT  CASE     WHEN Value2 BETWEEN 0 AND 70 THEN 'a 0-70'
                     WHEN Value2 BETWEEN 71 AND 90 THEN 'b 71-90'
                     WHEN Value2 BETWEEN 91 AND 110 THEN 'c 91-110'
                     WHEN Value2 BETWEEN 111 AND 130 THEN 'd 111-130'
                     WHEN Value2 BETWEEN 131 AND 150 THEN 'e 131-150'
                     WHEN Value2 BETWEEN 151 AND 170 THEN 'f 151-170'
                     ELSE 'Over 170' END AS Range,
                    *
     FROM
        (SELECT     value2,
                    Range2 AS [Value],
                    COUNT(*)  AS Foo
        FROM    (SELECT
        value2,
        CASE WHEN Value1 BETWEEN 0 AND 20000 THEN '0-20000'
                     WHEN Value1 BETWEEN 20001 AND 21000 THEN '20001-21000'
                     WHEN Value1 BETWEEN 21001 AND 22000 THEN '21001-22000'
                     WHEN Value1 BETWEEN 22001 AND 23000 THEN '22001-23000'
                     WHEN Value1 BETWEEN 23001 AND 24000 THEN '23001-24000'
                     ELSE 'Over 24000' END AS Range2
                FROM    (SELECT value1,value2
                         FROM StackDBA) AS derivedtbl_1) AS t
        GROUP BY Range2, value2) q1
        pivot
        (max(foo)
         for [value] in ([0-20000],[20001-21000],[21001-22000],[22001-23000],[23001-       24000])
        )as pvt
) grp

GROUP BY [Range]

SQL Fiddle


Linked server not working after AWS RDS failover Linked servers created on AWS RDS to other instances is not working after RDS instance failover. Error we get is "An error occurred during decryption".

This is similar issue reported, but no solution offered.
Linked Server: An error occurred during decryption

Any help if this is a bug or limitation with RDS?

Update: July 2020
As of July 2020, we now replicate the service master key after a host replacement. Previously, during a host replacement linked servers with passwords would get the error “An error occurred during decryption”. This is now resolved.
Found the answer here
https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/
If a host replacement happens, RDS SQL Server does not restore the service master key. This means that linked servers with passwords will get the error “An error occurred during decryption” when you try to use the linked server. Unfortunately, there is no current notification of a host replacement. The closest you can come is to monitor reboots and failovers via SNS events. You can read more about subscribing to those events on Using Amazon RDS Event Notification. When a reboot or failover happens, you need to manually re-add the linked server login passwords. This is a temporary workaround until a future update to RDS SQL Server addresses this by restoring the service master key after a host replacement.


Is the chunk size a limit to the max data size that can be associated with a shard key value? I haven't read about the relation between chunk size and the data size associated to a single shard key value in a collection, so, another way to ask the question could be, can a shard key span more than one chunk?

can a shard key span more than one chunk?

No. A chunk in MongoDB is a logical contiguous range of one or more shard key values. A unique shard key value cannot exist in more than a single chunk range, but many documents may have the same shard key. The cardinality for your chosen shard key determines the granularity of documents that can be represented in a chunk range (with the lowest granularity being a chunk that represents a single shard key value).

The relationship between shard key and chunks is illustrated in the following diagram from the MongoDB documentation explaining Choosing a Shard Key:

  Is the chunk size a limit to the max data size that can be associated with a shard key value

No. The chunk size determines the approximate size of documents expected to be represented by a chunk range (by default, 64MB). If a chunk range is observed to be approaching (or possibly exceeding) the configured chunk size, MongoDB will attempt to split that single chunk into multiple chunks representing smaller contiguous ranges of the shard key. Chunk splits update the sharded cluster metadata but do not involve copying documents between shards. MongoDB's sharded cluster balancer uses migration thresholds to determine when it is appropriate to migrate chunks and associated documents between available shards.

If MongoDB cannot split a chunk that exceeds the configured chunk size (for example, due to a low cardinality shard key), that chunk will be marked as jumbo. Jumbo chunks are problematic as they will lead to an imbalance of data distribution within your sharded cluster: the balancer will no longer attempt to split or migrate jumbo chunks.

The default chunk size works well for most deployments, however it is possible to modify the chunk size if there is a more appropriate setting for your deployment or use case. A smaller chunk size will lead to more frequent chunk split and migration activity; a larger chunk size will lead to more I/O for individual chunk operations such as migrations. The documentation for modifying the chunk size notes several considerations which are worth reviewing before changing this setting.


Recommended number of index for a table I've a table with many columns (~70) which contains computer's configuration. It contains approximately 50 000 rows.

This table is very requested and all these column too.

I use to run the following query to know missing index history:

-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011.

use myDatabaseName

SELECT TOP 25
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY Avg_Estimated_Impact DESC

GO

But for these specific table it gaves me twenty different indexes with an impact > 300 (impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)).

I read somewhere that it's not relevant to have more than 10 indexes, is there any recommendations about it and how can I know if it's really usefull to define all these indexes?

I read somewhere that it's not relevant to have more than 10 indexes

No hard-and-fast rules like that are generally applicable. If you have more than ten columns (or combinations of columns as an index can cover several) that are regularly searched by then you probably need more than 10 indexes.

The key problems with too many indexes which you need to consider are:

Space needed. If you have limited disk space then this can be an issue, but space is cheap these days. Remember to consider backup sizes as well as the size of your production databases.
Potential RAM use. Though this effect is usually small unless you have too little RAM to start with.
Insert and update speed, though if your table sees vastly more reads than writes (which is the most common use profile) then this is not generally significant either.

  how can I know if it's really usefull to define all these indexes?

Nothing beats application specific benchmarking for that sort of thing. You know how your application and its users hit the database (or if you don't, you need to study this (or apply some careful thought if the app isn't live yet so doesn't have real users)) so build some benchmarks based on that activity and see what difference it makes. Remember to test queries/updates that don't happen very often as well as those that do though - sometimes infrequent reports are the most important (i.e. in the app that I manage there are reports the users access once or twice per month, but while they aren't used often they are vital to their business and need to respond in reasonable time) so you don't want them timing out or simply taking hours to produce.


COUNT(*) gives more than 1 with LIMIT 1? I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case?

SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1;

 count
--------
1160
(1 row)

I expected a result of 1 or 0, but it gives 1160. Why?

You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE datetime < '2015-09-23 00:00:00'.

Basically:

Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00'
Postgres then count(*)s them
Postgres then LIMITs that
count

I suspect you're wanting it to do this:

SELECT COUNT(*) FROM (
    SELECT * FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1
);

As this is basically an existence check, one could also do:

SELECT (EXISTS
           (SELECT 1 FROM data WHERE datetime < '2015-09-23 00:00:00')
       )::integer;


Remove both Special Characters and String Characters from output SQL Server I am looking to remove some string characters in an output that I have so that it only shows numbers rather then letters and numbers. My Query is:

Select DISTINCT t.processed 'Title',
(Select max(reconst) from isbn where bib# = t.bib#) 'ISBN',
t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc

The output from the Query is:

The desired output is in the ISBN column which would would look like 9781501132957, which would be removing the letters C and also the words such as hardcover, HRD1, acid-free paper, softcover,PAP, DVD, CD, pbk and special characters such as (), :, ., -

Thank you in advance

Try this code that uses a combination of SUBSTRING, PATINDEX, LEN, and REVERSE, to account for the possibility of 10-13 digit ISBN variability to return the exact ISBN with no trailing extras:

declare @myvar varchar(50)='C9781501132957 : (hardcover)'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar)))

...or perhaps differentiating between other numbers in the string from ones that are at least 10 digits in size, like maybe a date of 1987 included in the description (if this is even needed):

declare @myvar varchar(50)='C9781501132957 : (hardcover) 1987'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',reverse(@myvar)))

i.e.

Select DISTINCT t.processed 'Title',
substring((Select max(reconst) from isbn where bib# = t.bib#),2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar))) 'ISBN',    t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc


Postgresql database NOTIFICATION trigger that only returns if some sql is met I'm trying to get a postgresql trigger that calls a notify function to work but only if the data meets certain criterion.
create trigger notify_site after insert on sites
for each row execute procedure notify_site();

So far I've got it triggering OK on every row thats insterted into the table using the following trigger.
Which is great.. but what I need to do now is only have it call the pg_notify if site='x' and not if site is something else..
How do I go about that (trigger newbie here, but a google hasn't helped so far).
For bonus points... how would I insert the tablename into the json object being returned by the select all?
Thanks to @a_horse_with_no_name I've managed to get the trigger working selectively quite happily.
Adding the table name to the json on the other hand, not so much.
I've managed to get the function to work without the table name addition, and have managed to get my attempt to actually run without errors, but it produces no output either.
This is what I've come up with
create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
declare
  payload jsonb := row_to_json(NEW)::text;
begin
payload := jsonb_build_object('tableName', TG_RELNAME) || payload;
perform pg_notify('ppsite', payload::text);
      RETURN new;
    END;
$function$;

But with this code, nothing ever makes it into the queue.
This code works fine, but without the table name
create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
begin
perform pg_notify('ppdsite', row_to_json(NEW)::text);
      RETURN new;
    END;
$function$;

Any thoughts.
Further on from this thread... I tried the following, which actually ran, but instead of adding the table name to the return, it removed the first two fields entirely from the package sent to the notification...
create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
declare
  payload jsonb := row_to_json(NEW)::text;
begin
payload := json_build_object('tableName', TG_RELNAME)::jsonb || payload;
perform pg_notify('ppsite', payload::text);
      RETURN new;
    END;
$function$;

The same thing happened if I tried
payload := payload || json_build_object('tableName', TG_RELNAME)::jsonb

You have two options:
1. Do it in the trigger with an IF condition:
if new.site = 'X' then
  notify ...
end if;

2. Only fire the trigger if the column contains the value:
create trigger notify_site
after insert on sites
for each row
when (new.site = 'X')
execute procedure notify_site();

how would I insert the tablename into the json object being returned by the select all?

As documented in the manual the table name is available in the variable TG_RELNAME, so you could just append that to your JSON:
payload := payload || jsonb_build_object('tableName', TG_RELNAME);


Partitioning a table on a column in another table I have a fact table called FactAccount which has the following columns:

AccountId (identity)
AccountMonthId - FK referring to DimMonth
AccountYear

The DBA has done the partitioning on the AccountYear (2014, 2015, 2016...) column. But this table will be queried on the AccountMonthId column (with a join to DimMonth) where users will select the year and month from the DimMonth dimension table.

So in this case, I don't think the partitioning will make any difference as the WHERE clause doesn't filter by AccountYear.

The DimMonth dimension contains all combinations of year and month, i.e. 12 dimension members for each year and the AccountMonthId column is an identity column, not a natural key.

Is there a way to partition a table on a column in a table referenced by a foreign key? Or, to be very specific in my case, is it possible to partition the FactAccount table on DimMonth.AccountMonthId?

Short answer: No, you cannot partition by a column in another table, because that would cause problems whenever you change something in the "other" table - rows would magically have to move between partitions.

Summarizing some of the views in the comments from Aaron, Kenneth and me on your question:

You could change AccountMonthId from a sequential number to a natural key, in this case an integer column from which you can compute the year and month. For example, the value 201503 could correspond to march 2015. This type of key is very common for date dimensions, because date dimensions don't change, so you can hard-code their keys.
With a natural key for AccountMonthId you could partition on this column, RANGE RIGHT FOR VALUES (201400, 201500, 201600) or RANGE LEFT FOR VALUES (201499, 201599, 201699).
Whether you have dates or just month values in your table, you could use a date column to store dates/months in your fact tables and in the month dimension. This would not only save storage space (three bytes, as opposed to four with int), it would also allow for your queries to use date functions among other gains. The same logic still applies with regards to partitioning.
You can still achieve what's known as "partition elimination" (querying a single partition) in your current setup, as long as you include the partition column in your query. With your current setup, that could look something like Select * from FactAccount A inner join DimMonth m on a.AccountMonthId = m.MonthId where M.Month = 2 and m.Year = 2016 and a.AccountYearId = 2016
You could partition on months instead of years. It'll take up the same disk space, although there are perhaps more partitions to manage, but this seems like a minor cost.
Adding an extra column for the year and partitioning on that column seems a bit lazy, ignorant, or at least strange of your DBA.
We have hardly touched on the subject of indexing - indexes, be they aligned or non-aligned, are often much more important than partitioning as performance goes.


Does a table with an index ever slow down? We've got a single table where we store Events.
The table has an index on it's primary key

Assuming we're only doing simple SELECTs or INSERTs on that table - would the retrieval or insertion time get noticeably* slower as more and more rows are added?

My (very crude) understanding is that an index makes the lookup time non-linear (non-O(N)), therefore, at least theroreticaly, it won't be a problem - but I'd like to confirm this.

We're working with PostgreSQL 9.5.3 and at some point we expect this table to have billions of rows.

*noticeable: It takes < 1 second to retrieve 5000 events right now, noticeable would be > 2 seconds

Tables do not slow down or speed up, they just sit there. Statements, on the other hand, can slow down. You don't provide sufficient details about your data, but if your primary key is monotonically increasing, like a timestamp or a sequence value, you'll end up rebalancing your tree quite frequently upon inserts. At the same time with such a primary key you can create a concurrency hot spot at the tail end, where all inserts will be happening.

Meanwhile, growing from 106 to 109 entries in the index takes you from 20 comparisons to 30 for the worst case lookup, which is a 50% increase, so depending on your PK value size it's totally feasible that you exceed your noticeability threshold of 2 seconds with some "billions" of rows in the table.


How to limit sql server table to 100,000 rows max I know that this is impossible but still need a solution.
I need to limit the number of rows in a table to 100,000 - having rows moderated by a datetime2 column. Newest rows come in, the old ones disappear.
This way I will not have to write a batch task that periodically deletes the unneeded rows.
Also the solution needs to be deployed into a production environment where there are 1,000,000 rows already and it will have to delete the existing ones.

The best solution is to "write a batch task that periodically deletes the unneeded rows."


Selecting data from multiple rows into a single row multiple columns I have a table that has columns like following. Batch number is a database design thing like row number

dm_id        type_in_id    type_student_id     amount
 36              1               1               1
 36              1               2               2
 36              1               3               3
 36              2               1               4
 36              2               2               5
 36              2               3               6
 36              3               1               7
 36              3               2               8
 36              3               3               9

I want to create a query that would return this:

dm_id  amount1 amount2 amount3 amount4 amount5 amount6 amount7 amount8 amount9
 36       1       2       3       4       5       6       7       8       9

How can I do that?

P.S. Version 5.5.56-MariaDB - MariaDB Server

SELECT dm_ID, MAX(Amount1) Amount1, MAX(Amount2) Amount2,...
FROM (
SELECT dm_id,
CASE Value WHEN 1 THEN Value ELSE NULL END AS Amount1,
CASE Value WHEN 2 THEN Value ELSE NULL END AS Amount2,
....
FROM Table
) RearrangedData

Old school pivot. It will work, but you need to know the possible values of Amount column, to use in the CASE statement...  TSQL so should be supported by mySQL.


How add user with same privileges? There is a database owner, I need to add another user with the same privileges. With rights to update/delete/alter, etc.
How can I do it?

you can create an user and add all privileges trough grant command or
you can just create a role and grant all privileges your role have to the new created role

`GRANT A TO B;`

It will grant all right of role A to B.

If A is a superuser, you also need: ALTER USER B WITH SUPERUSER;

reference link1 and link2


DBT2 benchmark for AWS RDS I am new to MySQL and linux and also AWS. Previously i have worked with windows azure and .NET framework.

Currently I am trying to get DBT2 benchmark to run for AWS RDS for MySQL. I was able to connect to RDS with shell command line from MySQL. But i was not able to configure or install the benchmark on the DBInstance.

To configure the dbt2 one needs to run shell script on the system. So far i have 'cd' to directory where files for dbt2 are located. But when i try to run './configure' it gives error that "'.' is not recognized as an internal or external command, operable program or batch file." Same error if i use 'sh ./configure'.

I am just a starter in this, is there something i am doing wrong?

How can i even run any shell script on AWS RDS system?

Any help is appreciated.

It is not possible to run shell scripts, install other software, or do any kind of serious administrative tasks on Amazon RDS.

In an RDS environment, you're completely isolated from the operating system and from administration of the actual system that is running MySQL for you.  Your RDS instance is an opaque black box that you can only communicate with via the MySQL command line client, and even that is not likely to be what you think it is.

  I was able to connect to RDS with shell command line from MySQL.

That's good, but that's not the same thing as being at a "command shell" or "command prompt" on the underlying server, which is where you would need to be to install DBT2 or any other utility, and this is not allowed with RDS.

The mysql command line client is, fundamentally, just a tool that allows you to craft queries, issue them to the server, and see the responses.  The underlying protocol is exactly the same protocol that your applications use to issue queries and retrieve results from the server.  What it can do isn't all that different than connecting with Workbench, Query Browser, Toad, HeidiSQL, or any other graphical MySQL administrative tool.  It just isn't graphical.

  So far i have 'cd' to directory where files for dbt2 are located.

If you are saying you have 'cd' into a directory on the RDS instance, then, no, you haven't.  That's not possible.  Perhaps on your local machine, which explains why you are seeing a Windows error message.

C:\Users\Sqlbot>./configure
'.' is not recognized as an internal or external command,
operable program or batch file.

I am a big fan of AWS, a huge fan of MySQL (of course), but not as much of RDS, because I do not generally see being "relieved" of the "burden" of administering my systems as a positive thing, and I don't like the things I am restricted from doing.  On the other hand, I am moving forward with plans to migrate some of my production systems to RDS -- but only the systems that with more mundane database requirements, systems which don't leverage some of the more creative things I do with MySQL.

But I don't want my comments to be taken as anti-RDS.  I realize that to provide a managed, hosted service like they are doing, they have little choice but to lock things down... I just wish some things weren't locked down quite so tightly.  My solution so far is to run MySQL on EC2 and set it up myself, but I digress.

If you are trying to learn to administer MySQL (in depth) and Linux (at all), RDS will severely limit you.


MongoDB Shard - Disable Cursor timeout I'm using an sharded-cluster(Mongo version 2.4.1) with a java application. After a specific time (10 minutes or more) I got the following exception:

com.mongodb.MongoException$Network: Read operation to server cs-mongo-mongo-west-t:3306 failed on database mdb
    at com.mongodb.DBTCPConnector.innerCall(DBTCPConnector.java:253)
    at com.mongodb.DBTCPConnector.call(DBTCPConnector.java:216)
    at com.mongodb.DBApiLayer$MyCollection.__find(DBApiLayer.java:288)
    at com.mongodb.DBApiLayer$MyCollection.__find(DBApiLayer.java:273)
    at com.mongodb.DBCursor._check(DBCursor.java:368)
    at com.mongodb.DBCursor._hasNext(DBCursor.java:459)
    at com.mongodb.DBCursor._fill(DBCursor.java:518)
    at com.mongodb.DBCursor.toArray(DBCursor.java:553)
    at com.mongodb.DBCursor.toArray(DBCursor.java:542)
    at com.pribas.messagesdb.impl.GetMessagesDBHandlerImpl.handleRequest(GetMessagesDBHandlerImpl.java:86)
    at com.pribas.messagesdb.impl.MessagesDBHandlerImpl.handleRequest(MessagesDBHandlerImpl.java:37)
    at com.pribas.messagesdb.service.MessageServlet.processRequest(MessageServlet.java:161)
    at com.pribas.messagesdb.service.MessageServlet.doPost(MessageServlet.java:86)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Is there a way to disable this timeout of the cursor within the sharded-cluster or within the config-file?
At the moment this issue can only be solved by restarting the application so it establishes a new connection.

Thank you very much,
Daniel

There is a 10 minute time out for inactive cursors, and it can be overridden, but be careful: "immortal" cursors can become a problem if enough of them accumulate on the server over time.  So, it would be a good idea to close your cursor correctly from time to time and avoid that if possible.  You should also make sure that it is in fact the cursor timeout that is hurting you here (and not a TCP timeout for example).

The option to turn off the timeout involves basically setting the noTimeout flag.  In Java that means using addOption on the cursor and setting QUERYOPTION_NOTIMEOUT.


Reason why SQL Max memory expansion seems slow when migrated to newer version of SQL server We are trying to understand below behavior happening in SQL Server and need some guidance:-
After migrating from older version SQL2012 (running on bare metal P Server) to newer SQL2017 running inside VM with matching Hardware specs we are seeing this interesting behavior:
On SQL2012 Phy server:- For server with 700 GB RAM; MAX memory setting of 500GB, after any flush of cache/restart activity on sql instance , approx. in 20-30 mins we see SQL server quickly able to  reach that MAX memory setting and can be seen using 500 GB instantly
On SQL2017 server (VM): -For server with 700 GB RAM; MAX memory setting of 500GB, after any flush of cache/restart activity on SQL instance , it is taking around 4-5 hours yet we still see almost 50% of that max memory usage. This seems that memory allocation extension somehow is very slow on this server
Memory reservation on VM is set to 750 GB
Yes we see little slowness in app response and our top 2 waits are generally PAGEIOLATCH and WRITELOG
Please let me know what additional info i can provide to help me understand why allocation of MAX memory between 2 is vastly diff.
Edit:- Yes we have same load and database running as we did load test on both Physical and VM's. Above is one thing we are noticing. Database migrated to 2017 is still on compat 110 i.e. SQL2012

This seems that memory allocation extension somehow is very slow on this server

More likely that you had query plans on SQL 2012 that required scanning more data than the plans on SQL 2017, and a side-effect of those scan-heavy plans is that they loaded most of your database into memory.
Or your disks could be slower, requiring more time (PAGEIOLATCH wait time) to to load the database pages into the page cache.


How does MySql(InnoDB) update secondary non unique index? Let's say we have a table with the following structure:
CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);
CREATE INDEX Lastname ON Persons (LastName);

Also, you can notice we have a secondary index called Lastname.
From the documentation of MySql(InnoDB engine), it is clear that the secondary index is referencing the primary key.
So my questions are:

what happens if we update a primary key? How does InnoDB quickly update all secondary index entries? Is
there a reference from the Primary key index page to all secondary index pages?
what happens if we update the field "LastName"? As the secondary index is nonunique, how does MySQL search for the page to be updated in the secondary index? It looks like there should be a reference from PK index to all secondary indexes pages of the given row. But I can not find explicit mention of this in the documentation of MySql.

Every InnoDB table has a special index called the clustered index where the data for the rows is stored (in other words clustered index contains all columns of the table). By default InnoDB uses primary key as clustered index. So

when you update a row in clustered index MySQL server uses other columns values to find corresponding records in non-clustered indexes and update them.
All non-clustered indexes contain key columns of the clustered indexes. This way MySQL server can determine corresponding row in clustered index using these values.

Reference: 14.6.2.1 Clustered and Secondary Indexes (MySQL 5.7 Reference Manual)


Retrieving Parent Hierarchy Entity I have the following table structure:

CREATE TABLE "Material" (
    "MaterialId" TEXT NOT NULL CONSTRAINT "PK_Material" PRIMARY KEY,
    "Content" TEXT NULL,
    "Title" TEXT NULL,
    "Paid" INTEGER NOT NULL,
    "Published" INTEGER NOT NULL,
    "ParentMaterialId" TEXT NULL,
    "Dates" TEXT NULL,
    "Private" INTEGER NOT NULL,
    CONSTRAINT "FK_Material_Material_ParentMaterialId" FOREIGN KEY ("ParentMaterialId") REFERENCES "Material" ("MaterialId") ON DELETE RESTRICT
)

Each material can have an infinite sub materials inside them, and i would like:

Get the outer most ancestor material for a particular child.
Get the First ancestor that is Paid (Paid is a boolean)

I am not that good at complex database operations, so please be kind.

Both SQLite and SQL Server understand enough standard SQL to do a recursive tree walk like this:

WITH AllAncestors AS (
  SELECT *
  FROM Material
  WHERE MaterialId = 12345

  UNION ALL

  SELECT parent.*
  FROM Material AS parent
  JOIN AllAncestors AS child ON child.ParentMaterialId = parent.MaterialId
)
SELECT *
FROM AllAncestors
WHERE ParentMaterialId IS NULL;

WITH AncestorsUntilPaid AS (
  SELECT *
  FROM Material
  WHERE MaterialId = 12345

  UNION ALL

  SELECT parent.*
  FROM Material AS parent
  JOIN AncestorsUntilPaid AS child ON child.ParentMaterialId = parent.MaterialId
  WHERE child.Paid = 0   -- stops before the parent of a paid child
)
SELECT *
FROM AncestorsUntilPaid
WHERE Paid = 1;


SQL Server 2008: Measure tps / select statements per second for a specific table? There is a discussion how to measure activity against an entire DB: https://stackoverflow.com/questions/5019593/sql-server-2008-transaction-per-second-for-specific-database

My question is narrower.

We are working on performance optimization of our application. To see how we are doing, we need to measure SELECT operations per second/minute/hour (prefer hour) on a specific table. (This way we can measure before and after implementing the optimization, to see how we are doing.)

What is the method or tool to do this?

Thank you!

OK, here is a cheap way, but it is not what I really want (I really want nice charts and graphs!):

-- First PASS
DECLARE @First BIGINT
DECLARE @Second BIGINT
SELECT @First = USER_SEEKS
FROM sys.dm_db_index_usage_stats
where object_id=14473333 AND index_id = 1

WAITFOR DELAY '00:00:15'

SELECT @Second = USER_SEEKS
FROM sys.dm_db_index_usage_stats
where object_id=14473333 AND index_id = 1
SELECT (@Second - @First) 'TableSeeks'
GO


PK Violation after transactional replication I have an application set up with transactional replication being pushed to a standby machine that will be used for emergency failovers. The replication appears to be working, any inserts made to Server 1 will automatically appear at Server 2.

However, I can't quite get the failover working. In the scenario that Server 1 becomes unavailable (which is the only scenario where Server 2 will ever be used, so the replication is one-way), the idea is that work should continue at Server 2, and that the transition should be somewhat seamless since all data has already been replicated.

But when moving to Server 2, after making sure that all updates on Server 1 have been transferred, I keep getting primary key violation exceptions, for some tables.

  Violation of PRIMARY KEY constraint 'PK_TableA'. Cannot insert duplicate key in object 'dbo.TableA'.

A simple query such as

INSERT INTO TableA (Field1, Field2, TableB_ID) VALUES ('a','b', 6)

will yield the above error. It seems that when I instruct the table to assign an identity of its own, by omitting it from the query (TableA has an ID int identity(1,1) field), SQL Server will auto-assign an ID that violates a PK constraint. Why would this be?

TableA has a trigger for INSERT and DELETE that does a simple denormalization job

UPDATE TableB
SET Count = (SELECT COUNT(1) FROM TableA WHERE TableB.ID = TableA.TableB_ID)
WHERE ID IN(
    -- Fetch affected ID's from deleted or inserted rows
    SELECT DISTINCT TableB_ID FROM deleted
    UNION
    SELECT DISTINCT TableB_ID FROM inserted
)

This was accidentally not a part of the Server 2 database at the time of the replication, and I inserted it afterwards. Consistency in the TableB.Count field is not critical for the task at hand. The PK Violation occurred before the trigger existed in Server 2, as well as after creating it.

At both Publisher and Subscriber, the ID field that is yielding the violations has the following definition:

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL

I suppose the NOT FOR REPLICATION part is redundant on the Publisher, as no replication job will ever write to it, but I can't see that it should be the cause of the problem, either.

It looks like that the identity column on the subscriber side is out of sync.  Run DBCC CHECKIDENT(TableA, RESEED) (or just DBCC CHECKIDENT(TableA)) and your problem will be gone.  You can check the documentation here.


Partitions and Indexes, in which cases should be used I know some knowledges:

Partitions used to achieve better performance (like described in this article)
And indexes used to increase performance in selection operations.

And as result my question: in which cases i should use indexes and in which cases i should use partitioned tables.

In other words what should be better in different operations(selections,deletions,updates) indexes or partitioning of table, and why.

Thanks.

Easy: you should always use indexes.

Partitioning for performance is probably the most misunderstood myth out there. When you partition, the best you can hope for is on-par performance with a non-partitioned table. And yes, that is including partition elimination enhancements. Reducing table scans to partition scans because of missing indexes is simply not the answer. Replacing table scans with index seeks or index range scans it is a much better the answer.

Partitioning is a great feature for data maintenance and administration and for efficient ETL switch-in and switch-out operations. For a good discussion of pros and cons of partitioning, see How To Decide if You Should Use Table Partitioning.


Proper procedure for migrating a MySQL database to another Debian machine? I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL.

I've created a backup of all databases on  the first server like so:

mysqldump -uuser -ppass --all-databases > dump.sql

On the other server,  I did a:

mysql -uuser -ppass < dump.sql

At first, everything seemed great. I could browse my databases in phpMyAdmin, but as soon as I tried logging in again, it failed. Turns out, my root password had been overwritten with the one from the older database.

I wanted to reset it, but in order to do so, I would have needed to start mysqld_safe. Which I couldn't because the password for the debian-sys-maint user had been overwritten as well in the database. When I thought all hell had broken loose, I somehow reset both the root and debian-sys-maint passwords to the original values of the new server, and I managed to revert to a clean state.

Since I obviously don't want to go down that road again, here's the question(s):

Was I right with my approach of using a complete --all-databases dump?
Was there something I needed to do in advance to reading in that dump to prevent this desaster from happening? Or even before creating the dump?

If I'm going about this the wrong way:

What is the proper procedure for migrating all databases and their users to another server?

Note that I'm not that experienced with MySQL and server administration at all, so I might be missing something obvious. All the tutorials and how-tos I've found never mention anything like this and just talk about importing the complete dump.

That is a normal procedure for setting up a new server from old database, so there's nothing wrong with this. Probably the reason there is no mention of the passwords getting overwritten is admins know the old passwords so can easily update them on the new server.

One thing you can do is modify the dumpfile to use the new passwords for root and your Debian admin user prior to loading it into the new server.


Move Oracle Database to New Server with Dataguard in place I have a live Oracle Database with Dataguard in placed. I want to migrate live database to new server as tech refresh, but only Primary database. How I can achieve this? I have ideas on how I want to do it but I'm not sure whether it is correct.

Create a full backup, then restore it to new server and update the dataguard configuration. Problem is, SCN in Standby database maybe different than New Primary database due to old Primary database is still running. If this happen, can i apply log from old Primary to New Primary? I'm not sure..
Switch the existing Standby database to be Primary database and create a new standby database in new server. Then switch back Primary role to new database in new server.

PS. I'm not good in English. Thanks in advance.

Create a full backup, then restore it to new server and update the dataguard configuration. Problem is, SCN in Standby database maybe
  different than New Primary database due to old Primary database is
  still running. If this happen, can i apply log from old Primary to New
  Primary? I'm not sure..

You can have multiple standby databases for a primary database. Create a full backup and restore it as a standby on the new machine, update the Data Guard configuration and keep that standby too in sync with archivelogs. When the time comes, simply perform a switchover to this new standby database.

  Switch the existing Standby database to be Primary database and create a new standby database in new server. Then switch back Primary
  role to new database in new server.

No, this is just unnecessary risk and extra work.


Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628) I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say Resumable error: ORA-01628: max # extents (32765) reached for rollback segment.
Since this is RDS I cannot manually manage undo. I created a fresh RDS instance with a new 4TB UNDO tablespace to perform the import of just this table. I've read about creating one giant rollback segment and also about creating lots of small rollback segments to solve this problem. I've also read I can split the import into multiple parts, but I'd rather not do that if possible. Is there anything more I can do here to maybe stop the UNDO tablespace from running out of extents?

IF: ORA-1628 Reported During Import (Doc ID 1955310.1)

Skip indexes and statistics
If the table already exists, drop it or use table_exists_action=replace.
use CONTENT=ALL instead of DATA_ONLY (redundant with previous point)


Is there any way to force MySQL use Hash Join instead of Nested Loop Join? According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?

Up to the (recently released) 5.6 version, no, you can't. Unfortunately, the only method that the optimizer knows is nested-loop method. As the 5.6 documentation says:

  MySQL resolves all joins using a nested-loop join method.

In other words, no hash join algorith or variation has been implemented, not even in version 5.7 - although that is still in development (hope never dies).

There is one alternative. MariaDB, a MySQL fork has implemented various other join methods in the latest (5.3 and 5.5) releases which can be drop-in replacements of MySQL (5.1 and 5.5 respectively) versions.

In their Block-Based Join Algorithms page, they describe the new methods:

  In the versions of MariaDB/MySQL before 5.3 only one block-based join algorithm was implemented: the Block Nested Loops (BNL) join algorithm. It could only be used for inner joins. MariaDB 5.3 (and later) enhances the implementation of BNL joins and provides a variety of block-based join algorithms that can be used for inner joins, outer joins, and semi-joins. Block-based join algorithms in MariaDB employ a join buffer to accumulate records of the first join operand before they start looking for matches in the second join operand.

  This page documents the various block-based join algorithms.

  Block Nested Loop (BNL) join
  Block Nested Loop Hash (BNLH) join
  Block Index join known as Batch Key Access (BKA) join
  Block Index Hash join known as Batch Key Access Hash (BKAH) join

There are also some optimizer switches that affect which algorithms will be considered when optimizing a query.