Combining 2 SELECT queries and printing the results in PostgreSQL I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to combine both queries with UNION:

Query failed: ERROR: syntax error at or near "UNION" LINE 6: UNION ^

I have had a look at a similar question but the solutions & discussions provided do not seem to work.

$name = pg_escape_string($_POST['name']); // Start destination from user input
$name2 = pg_escape_string($_POST['name2']); // End destination from user input

//If no results are given, the following query below will execute instead.

if (pg_num_rows($result) == 0 ) {

$query = "SELECT dt1.name as name1,dt1.time as time1,dt2.name as name2,dt2.time as time2
From departure_times as dt1
inner join departure_times as dt2 on dt2.tram_id = dt1.tram_id
where dt1.name = '$name' and dt2.name = 'CitySquare' LIMIT 5

UNION

SELECT dt1.name as name3,dt1.time as time3,dt2.name as name4,dt2.time as time4
From departure_times as dt1
inner join departure_times as dt2 on dt2.tram_id = dt1.tram_id
where dt1.name = 'CitySquare' and dt2.name = '$name2' LIMIT 5";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
} ...

The desired output:

------------------------------------------------------------------------
    name1 | time1  | name2  | time2 | name3 |  time3    | name4  | time4
------------------------------------------------------------------------

The primary solution is to add parentheses:

(  -- parentheses required!
SELECT dt1.name AS name1, dt1.time AS time1
     , dt2.name AS name2, dt2.time AS time2
FROM   departure_times dt1
JOIN   departure_times dt2 USING (tram_id)
WHERE  dt1.name = '$name'
AND    dt2.name = 'CitySquare'
-- no ORDER BY?
LIMIT  5
)
UNION ALL  -- see below
(
SELECT dt1.name, dt1.time, dt2.name, dt2.time  -- see below
FROM   departure_times dt1
JOIN   departure_times dt2 USING (tram_id)
WHERE  dt1.name = 'CitySquare'
AND    dt2.name = '$name2'
-- no ORDER BY?
LIMIT  5
);

Without extra parentheses LIMIT is only allowed once at the end of the query and applies to the result of the UNION. To attach a LIMIT to each SELECT parentheses are required.

The manual:

  The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement

  select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a
  subexpression if it is enclosed in parentheses. Without parentheses,
  these clauses will be taken to apply to the result of the UNION, not
  to its right-hand input expression.)

Other notes:

I switched to UNION ALL - assuming you don't actually want to try and fold duplicate rows.
Names of result columns are determined by first SELECT of a UNION query. Names (or aliases) in all following SELECT statements are ignored.
You may want to add ORDER BY to each SELECT. LIMIT without prior ORDER BY returns an arbitrary selection that can change with every execution - in simple queries determined by the physical order of rows in the table (or in the index), which can change any time.


Versioning: Is this technology used in DBMS other than spatial? ESRI's spatial database management systems, called geodatabases (more), use a technology called versioning.

  A version represents a snapshot in time of the entire geodatabase and
  contains all the datasets in the geodatabase.

  Versions are not separate copies of the geodatabase. Instead, versions
  and the transactions that take place within them are tracked in system
  tables. This isolates a user's work across multiple edit sessions,
  allowing users to edit without locking features in the production
  version or immediately impacting other users and without having to
  make copies of the data.

http://help.arcgis.com/en/geodatabase/10.0/sdk/arcsde/concepts/versioning/basicprinciples/state.htm

  When you register a dataset (a feature class, feature dataset, or
  table) as versioned, two delta tables are created: the A (or adds)
  table, which records insertions and updates, and the D (or deletes)
  table, which stores deletions. Each time you update or delete a record
  in the dataset, rows are added to one or both of these tables. A
  versioned dataset, therefore, consists of the original table (referred
  to as the base or business table) plus any changes in the delta
  tables. The geodatabase keeps track of which version you were
  connected to when you made the edits that populated the delta tables.
  When you query or display a dataset in a version, ArcGIS assembles the
  relevant rows from the original table and the delta tables to present
  a seamless view of the data for that version.

To be honest, I find the documentation to be rather vague; it doesn't tell me much about how the technology actually works, or what part of traditional database theory it is based on.

I don't imagine many DBA SE community members would have experience with ESRI's versioning technology. So I won't ask something like 'how does it work?'.

Instead, I'm wondering, are there any technologies in the non-spatial database world that are similar to ESRI versioning?

What you are describing seems really similar to MMVC- https://en.wikipedia.org/wiki/Multiversion_concurrency_control Where different clients can have a different "view" or "version" of the same database, and edits done withing a transaction cannot be seen by other sessions until they decide so by executing "COMMIT".

That concept is not exclusive to spatial databases (the wiki cites a 1981 paper as one of the first descriptions of it: https://en.wikipedia.org/wiki/Multiversion_concurrency_control#cite_note-3 ) but the concept has to be older than that. And while I see some special needs and implementations for extra dimensions, the essentials would be the same.

The implementation, of course, can vary widely from engine to engine. Git, even if not a database, it has to implement concurrency control in a particular way, and that will be completely different to how postgres does it. If then we go to distributed databases, we arrive to the wild west of keeping consistency thought heterogenous systems over the net. You want an implementation that is performant for the most useful cases, and all optimizations imply a trade off for operations considered not as important. One that is fast for small transaction sizes may be too expensive or unbearable for large edits. Also, different database engines interpret thing like consistency levels on its own way.

The good news is that with the rise of open-source database systems, you do not need to read large manuals anymore to understand what is happening internally- you can read the source code directly yourself and understand how those are written. For example, for Innodb:

https://www.percona.com/live/mysql-conference-2013/sessions/innodb-journey-core
https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/row/row0undo.cc


SQL Server - TRY/CATCH does not work in certain cases How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?

For example, this does not work:

Define a SQL Agent job with no steps
Try to start the job inside a TRY/CATCH

BEGIN TRY
    EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
END TRY
BEGIN CATCH
    SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
END CATCH

  Msg 22022, Level 16, State 1, Line 0
  SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.

Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?

It also bypasses my CATCH block if the job is busy handling a request (I'm spamming stop and start requests in my testing).

  Msg 22022, Level 16, State 1, Line 0
  SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.

Related case: invalid linked server reference

P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.

P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?

*What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20))

How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?

You can't, unfortunately.

SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server

The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error.  In particular, that error comes from master.dbo.xp_sqlagent_notify.

Erland covers that issue here:

  SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.

I know you mentioned you're not interested bespoke solutions that aren't broadly applicable, but I think it's worth mentioning that, when dealing with Microsoft-shipped stored procedures, one solution to this TRY / CATCH issue is to always capture the return code of the procedure.  If it's non-zero, you can manually raise an error to trigger the CATCH block (thanks Dan Guzman).

* brief, ha!


FirstResponderKit - missing objects our vendor has recommended us to use your tool to collect data for him. After the installation (version 8.03) we received following errors (warnings):
*The module 'sp_AllNightLog' depends on the missing object 'master.dbo.DatabaseBackup'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_AllNightLog' depends on the missing object 'master.dbo.DatabaseBackup'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_AllNightLog' depends on the missing object 'master.dbo.sp_DatabaseRestore'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_AllNightLog' depends on the missing object 'master.dbo.sp_DatabaseRestore'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_DatabaseRestore' depends on the missing object 'dbo.CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.*
I verified the master database and I did not find the object mentioned above. Is it OK?
P.S. We are running Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)   Mar 14 2020 16:10:35   Copyright (C) 2019 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: )
Regards,
Ondrej

The procedure sp_AllNightLog uses Ola Hallengren's scripts to do backup. If you don't plan to use sp_AllNightLog, then it is fine. If you do plan to use sp_AllNightLog, then install Ola Hallengren's scripts, which you can find here: https://ola.hallengren.com/
This is documented here: https://www.brentozar.com/sp_allnightlog/


Moving SQL Server default instance's folders to another path on the same server My customer installed SQL Server 2012 default instance. But default instance's folders and data folder are on the same disk.

I want to move the default instance's folders to another disk (C:Program Files\Sql Server\).

Can I do it without uninstalling exist instance then installing new instance?

From View or Change the Default Locations for Data and Log Files:

  In Object Explorer, right-click a server and click Properties.
  In the left panel, click the Database settings page.
  In Database default locations, view the current default locations for new data files and new log files.
  To change a default location, enter a new default pathname in the Data or Log field, or click the browse button to find and select a pathname.

Moving system databases is a little more intense, and harder for some databases (like master) than others (like tempdb). I'd start here, and consider whether you really need to move master/model/msdb:

Move System Databases

I don't know that there's a supported way to move folders like Binn, FTData, etc. If you find ways to move those that seem to work, I'm pretty confident they'll break at some point. So if the object is really to remove all traces of SQL Server from the installation drive, it will be much cleaner to backup your databases, uninstall SQL Server, and start over. If the object is just to keep Binn and Data separate, I'd have to question the value in going through a lot of trouble for that.


Rolling update to all rows in innodb table (no rollback if aborted) I have the following table, which stores the Social Security Death Master File database (~86M rows):

CREATE TABLE `death_master_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ssn_an` smallint(5) unsigned DEFAULT NULL,
  `ssn_gn` tinyint(3) unsigned DEFAULT NULL,
  `ssn_sn` smallint(5) unsigned DEFAULT NULL,
  `as_of` date DEFAULT NULL,
  `change_type` varchar(1) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `first_name` varchar(15) DEFAULT NULL,
  `middle_name` varchar(15) DEFAULT NULL,
  `name_suffix` varchar(4) DEFAULT NULL,
  `verify_proof_code` varchar(1) DEFAULT NULL,
  `death_year` smallint(4) unsigned DEFAULT NULL,
  `death_month` tinyint(3) unsigned DEFAULT NULL,
  `death_day` tinyint(3) unsigned DEFAULT NULL,
  `birth_year` smallint(4) unsigned DEFAULT NULL,
  `birth_month` tinyint(3) unsigned DEFAULT NULL,
  `birth_day` tinyint(3) unsigned DEFAULT NULL,
  `state_of_residence` varchar(2) DEFAULT NULL,
  `last_known_zip_residence` varchar(5) DEFAULT NULL,
  `last_known_zip_payment` varchar(5) DEFAULT NULL,
  `extra` varchar(7) DEFAULT NULL,
  `lifespan` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ssn_as_of` (`ssn_an`,`ssn_gn`,`ssn_sn`,`as_of`),
  KEY `index_death_master_files_on_last_name_and_first_name` (`last_name`,`first_name`),
  KEY `idx_dob_ssn` (`birth_year`,`birth_month`,`ssn_an`)
) ENGINE=InnoDB AUTO_INCREMENT=85952194 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

I tried populating lifespan using update death_master_files set lifespan = datediff(concat(death_year, "-", death_month, "-", ifnull(death_day,1)), concat(birth_year, "-", birth_month, "-", ifnull(birth_day,1)));

Unfortunately, there was an unrelated issue which caused the server to restart, thus killing the transaction midstream. Now I'm looking at ROLLING BACK 1 lock struct(s), heap size 376, 1 row lock(s), undo log entries 32937515 in my SHOW ENGINE INNODB STATUS, which is blocking all further actions on that table until it finishes. :(

One speed improvement would seem to be using update death_master_files set lifespan = datediff(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (ifnull(death_day,1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (ifnull(birth_day,1)-1) DAY); instead of the string manipulation.

There's no need for any transactions on this. The table is not in use yet, and the lifespan column is either NULL or the value populated by this update. The update is multi-run-invariant. There are currently no slaves on the database either.

I'm going to let it finish the rollback (which'll take hours…). The next time I run it though, I'd like it to run such that it just writes, without rollback/transactions, so that if something dies, it can resume where it left off.

So: any suggestions for how to run UPDATE on the entire table, with rolling updates per row (no locking / transactions)?

(Bonus: it'd be nice if I don't have to do an OPTIMIZE TABLE after this either, since that also takes a while with an 8 GB table. This is the only change since the db was populated [by 1krow bulk inserts from the raw file parser].)

I ended up doing this, to do it in batches of 1krow (which takes about .2s per batch if there's nothing to update, 2s otherwise; basically it has the same total speed per row if it's in batches of 10k, 5k or 1k):

SET SESSION sql_mode = 'strict_all_tables';
SET SESSION sql_warnings = 1;
\W
DELIMITER //
DROP PROCEDURE IF EXISTS update_dmf_lifespan//
CREATE PROCEDURE update_dmf_lifespan(INOUT i INT)
BEGIN
 SET @i = i;
 SET @max = (select id from death_master_files order by id desc limit 1);
 setloop: LOOP
  UPDATE death_master_files SET lifespan = DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) WHERE DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) >= 0 and id BETWEEN @i AND @i+1000;
  SELECT @i, ROW_COUNT(), LAST_INSERT_ID();
  SELECT *, DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) as diff from death_master_files WHERE DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) < 0 and id BETWEEN @i AND @i+1000;
  SET @i = @i + 1000;
  IF @i + 1000 > @max THEN
   LEAVE setloop;
  END IF;
 END LOOP setloop;
END
//
SET @i = 1//
CALL update_dmf_lifespan(@i)//
DROP PROCEDURE IF EXISTS update_dmf_lifespan//
DELIMITER ;

… so this'll probably take 2 days to run, plus more for the OPTIMIZE TABLE afterwards. :-/

Also, I haven't figured out how to get both SHOW WARNINGS and ROW_COUNT(), since they don't seem to be joinable in a single statement and both output results based on the previous statement (including each other).

I've set it to strict mode with a resumable point, which lets me manually inspect and resume after warnings.

That's surely not the best option, though I'm not sure what would be better. For cases where the deathdate is before the birthdate (the data is not exactly 100% reliable), and thus it raises on lifespan being unsigned, it doesn't update and instead outputs with select. This won't catch other sources of warnings, though.


Using MySQL EXPLAIN to analyze the performance of a function You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as a whole.

EXPLAIN was meant to do queries, not functions or procedures.

When it comes to procedural code, you must think like a developer all over again. For what purposes would you optimize code, especially for a database ?

Streamline Code Execution

Use SQL for all rows
Forcing SQL behavior over the Query Optimizer

Reduce Client/Server Traffic

Minimize Responses
Triggers

Divide/Conquer Complex Operations

Optimize Correlated Queries
Avoid Self Joins
Remove Unnecessary Statements from Loops
Use Controlled Break Statements (LEAVE or CONTINUE)
Good IF/CASE statements
Recursion
Cursors

It is no longer a case of EXPLAIN a query. If you can EXPLAIN the procedure, you can code. Obviously, if you can EXPLAIN a procedure faster, you can produce faster code.


Installing SQL Server and Visual studio side-by-side Is there any problems that I should be aware of when installing SQL Server and Visual Studio side-by-side? In which order should I install them?

My head tells me to install Visual Studio and then to install SQL Server second but will any problem occur if I install SQL Server 1st and Visual Studio at 2nd?

There is actually no official guide (AFAIK) for which one you should install first but based on my personal experience here is what I recommend:

If you are just installing the management components of SQL Server, such as Management Studio and the client libraries then I recommend always installing Visual Studio first followed by the SQL Server components.
However, if you are installing the database engine itself then this should be installed first followed by Visual Studio (as SQL Server is the priority not VS).
Even when performing updates, ensure to update Visual Studio and then SQL Server.

This is because generally the components used by Visual Studio will be newer than those used by the build of SQL Server. I have found complications in the past when installing them side-by-side because sometimes they can be overwritten or updated by a Visual Studio install.

By installing Visual Studio and then SQL Server, any components that are required by SQL Server which have been updated by the Visual Studio installation are reinstalled by SQL Server itself.

That being said, with SSMS being a separate product Microsoft have been able to offer more frequent builds and problems are substantially reduced.


What can I do about high CXPACKET waits? We are experiencing some slowness on our MS SQL Server 2016 database, I have been using Brent Ozar's first aid kit to do some initial troubleshooting.

I am seeing a high amounts of CXPACKET wait types, out of a 17.5 hour data sample we saw 99 hours of wait across our 10 CPU's, that 55.5%!

I was hoping someone here could confirm that we should be concerned about this number and resolve it asap. We have MAXDOP setting of 4 which is accurate to MS recommendations but our CTP is set to 5 which I believe needs to be changed to 50.

Just looking for clarification before I take this information to my boss, yes, I am new to database administration and yes I am looking at other wait types but this seems to be the most significant so far.

Cheers,
Josh

Install SQL Server 2016 SP2

You mentioned you're on 13.0.1745.2.  This is an RTM version of SQL Server, and is actually out of support as of 1/9/2018.

Plus, if you install SP2 you'll get the new CXCONSUMER wait type (see here for lots of info about that).  This splits out "harmless" parallelism waits from ones that you can actually do something about.  This can help you a lot in determining whether or not CXPACKET is really a problem for your server!

Query Tuning

Check out this article from Paul Randal: Knee-Jerk Wait Statistics : CXPACKET

In it, he talks about how CXPACKET can be a normal wait type - it's always going to occur if queries are going parallel (especially since you don't have the CXCONSUMER split yet).  And he also discusses how query tuning can be a more effective solution (getting at the root cause) over adjusting MAXDOP and Cost Threshold:

  One of the common cases of unexpected parallelism is when a table scan happens where you’re expecting a smaller index seek or scan.

He goes on to say that indexing, statistics updates, etc can be used to eliminate the scan, and thus the query will be less likely to go parallel.

Change MAXDOP

If you really do need to reduce the amount of CXPACKET waits, you can reduce MAXDOP to 2 (from the current setting of 4) - but this might introduce other problems.  Of course, if you have already determined that 4 is a good setting for your system through performance testing, ignore that suggestion.

Increase Cost Threshold for Parallelism

Another option is to increase this value, to prevent some queries from going parallel entirely.  I've seen a lot of advice suggesting that 50 is a good place to start, rather than the default of 5 (example).


Partitioning a table state So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is this possible with SQL Server ?

Create a secondary table with contiguous values for the known states (and maybe leave a little room for others you may want to segregate later):

CREATE TABLE dbo.States
(
  StateID TINYINT PRIMARY KEY,
  Abbreviation CHAR(2)
  StateName VARCHAR(255) NOT NULL UNIQUE
);

INSERT dbo.States VALUES
(1,'AL','Alabama'),(2,'AK','Alaska'), ..., (51,'WY','Wyoming');

-- leave 52 - 68 available for other partitions

INSERT dbo.States VALUES
(69,'EH','Canada'),(70,'PR','Puerto Rico');

Now, in the partitioned table, you can partition against the StateID column, and just set up the partition scheme such that 69+ is its own partition.

You can't do this with state or city names because partitioning on those values can only be alphabetical. There's not really a clean way to say "put PA in its own partition, and RI in its own partition, but lump PR into another partition along with Guam."


MySQL Unique Key Limit I have been given a database dump that I am trying to rebuild. When I import I get the following error:

Too many key parts specified; max 16 parts allowed

There are indeed more than 16 key parts in the dump.

I have read and understand the limitations of InnoDB, what I don't understand is how the original database was able to create a key with more than 16 parts.

Here is the relevent part of the dump:

CREATE TABLE `v1_view` (
  `view_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `view_date` date NOT NULL,
  `account_id` int(9) unsigned NOT NULL DEFAULT '0',
  ....
  PRIMARY KEY (`view_id`),
  UNIQUE KEY `viewUnique` (`view_date`,`account_id`,`player_id`,`video_id`,`order_id`,`orderlist_id`,`feed_id`,`domain_id`,`location_id`,`city_id`,`region_id`,`dma_id`,`country_id`,`continent_id`,`browser_id`,`platform_id`,`os_id`,`size_id`,`autoplay`,`volume`,`pacing`,`ad_spend`,`ad_revenue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A couple of thoughts I had:

It was created with an older version of MySQL that allowed more key parts
It is using a different ENGINE even though the dump says InnoDB

Not sure how valid either of those are. I don't have direct access to the original DB but can probably get more info if needed.

How is it that the original database has a unique key with so many parts? How do I replicate this database?

Somehow this was a VIEW that turned into a TABLE?

I checked; the max of 16 columns per index has existed for a 'long time' and applies to 'all' engines.  Reference. I can't explain your specific question.

Also, I am thinking there are worse problems than the failing UNIQUE.

It is unreasonable to normalize something like "location", while also including all the fields in this table:

location_id,city_id,region_id,dma_id,country_id,continent_id

That is, there should be a different table with all of those columns, but only location_id in this table.

I recommend you manually remove the UNIQUE line, load the data, then clean up messes such as location_id.


tempdb logs on same drive as tempdb data or with other logs? For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files:

D: Data
E: Logs
F: Tempdb

Should the tempdb log file go on F: with the data file(s) or on E:?

My tempdb data file has the highest stalls by far, with the log file 4th out of 24.

In my limited DBA experience (I'm a developer) I would lean to putting the tempdb.ldf on E: as the writes will all be sequential.

The answer here depends on a lot of things, since you are using direct disks (Local RAID) and not SAN disks which are wide striped (I assume), sequential operations are faster, so the logs (tempdb included) are benefited in E: . However there can be a situation where you don't want tempdb activity (logging activity) to interfere with the logging on your other databases, this can be the case if your tempdb activity is erratic and not related to application performance, while the logging performance of your databases is directly important to your use case.

I would generally test both setups and see which does better under the load you intend to use (the benchmarks could be transaction times for SQL Server, or disk queue lengths or writes\sec on the log drive).


Restoring master database I am trying to restore master database, when I set the server to single mode user I am unable to connect I get the error below.

  Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time.

Start SQL Server with the -m parameter, specifying the application name allowed to take the only available connection. For instance, to allow sqlcmd to take the connection:

sqlservr -m"sqlcmd"

If you want to work with SQL Server Management Studio, use this:

sqlservr -m"Microsoft SQL Server Management Studio - Query"

Remember that the "Connect..." button in Management Studio connects the object explorer, that would take your connection without letting you open another one to run the query. In this case, open a query window either disconnected or connected to another server and then use the "Connection, Change Connection" command from the context menu.

See the docs for more information: http://msdn.microsoft.com/en-us/library/ms188236.aspx

An interesting thing to note is that the Configuration Manager doesn't let you enter -m"Microsoft SQL Server Management Studio - Query" as a parameter (I suppose it's the spaces in the parameter), so this is an option only when starting SQL Server from the command line. If you decide to do this, remember to start cmd as an Administrator.


Is there such thing as Custom Data Types? Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to whether it is a 5 digit or 5+4 digit zip code.

Is there a way to install seamless data types for such things? As far as the application is concerned, it would be a string type, there would simply be data truncation (with or without warning), if the application passed invalid data.

Custom functions could be used (for example, there is a built in function INET_ATON for IPv4 addresses. But that does not allow for such things as zip LIKE '12345%' which should be properly indexed. A well written support for custom data types would allow a data type to be marked as sortable. So the compact zip int, when sorted, would sort just as if it was a zip varchar(10).

This would allow for the column to be fixed width, it would allow 6 or 10 byte variable storage to be cut down to 4 byes of fixed width.

There are several applicable uses

Zip Codes
IPv6 addresses
Custom timestamp fields with minute level precision and capacity beyond 2038 with less storage usage than datetime, but no need to support dates before the year of implementation (say the min could be 2007 if those are the oldest dates in the system)
Timestamps that implement DST (which don't seem to exist)
Two letter US state can be stored in a single byte
long ENUMs can be separated into a custom data type so that DESCRIBE's output would not be so messy looking with all the wrapping.

I expect the data type handlers would be stored similar to the way functions are stored.

Is there anything remotely like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of making the application call a function like the INET_ATON function.

MS SQL does seem to have something of that nature, but I would like to know if it is more than just a synonym. (for example boolean could be a synonym for tinyint(1), or postal_code for one of char or varchar (5 or 9 or 10)) Synonyms are not what I am asking about here.

Does MySQL have any support for custom data types?

Simple answer: no

  Is there anything remotely like this on any database engine? I mostly
  use MySQL, but I am curious if this has ever been implemented, short
  of making the application call a function like the INET_ATON function.

Oracle has CREATE TYPE which is analogous to some degree to a OO class, including features like member functions and inheritance

Postgres has CREATE TYPE which is a bit less like OO classes (no member functions or inheritance) but are incredibly flexible and useful, even allowing you to create new base types. There is also CREATE DOMAIN which allows a form of inheritance or sub-typing and basically extends a base type with some constraints. Postgres also has quite a few interesting base types by default, eg inet and geometric types. In Postgres one can write an extension in C for a custom datatype, such as in this example here with base36 data type.

SQL Server has CREATE TYPE which allows you to create a custom data type based on an existing system data type.  For example I could create a type called SSN which is basically defined as VARCHAR(11) but this way I don't have to remember how big a field it is.


Can I create and restore partial backups with mariabackup on MariaDB 10.1.23? I read in Percona xtrabackup manual that xtrabackup require MySQL 5.6 for restoring partial backups (only some databases or tables). But there are nothing about MariaDB. How about mariabackup? I have MariaDB 10.1.23. Does mariabackup support partial backup and restoring for this version?

Yes, and you do it exactly in the same way, like you would do in Percona, substituting xtrabackup with mariabackup (or innobackupex with "mariabackup --innobackupex" on the command line)


Cannot create login on cluster I have a SQL Server 2008 R2 cluster on Win2008 R2.

I am unable to add a login for the service account.

Any other login I add to this instance works fine and I have been able to add the same service account login to numerous other SQL instances.

Moving the instance to other node does not resolve the issue.

when I run

 CREATE LOGIN [DOMAIN\ServiceAccount] FROM windows;

I get the error

Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'DOMAIN\ServiceAccount' not found.
    Check the name again.

When I run

exec xp_logininfo 'DOMAIN\ServiceAccount', 'all';

I get the error

Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user
    'DOMAIN\ServiceAccount', error code 0xffff0002.

Any suggestions would be appreciated.

Is the service account a domain account?  Sounds to me like the service account may in fact be an account that is local to the machine, not a domain account.

Try:

CREATE LOGIN [WINDOWS_MACHINE_NAME\ServiceAccount] FROM windows;

If this works, the account is not a domain account.


Out-of-sync Replication in Sql Server Okay, a little complex:

One local DB table is being replicated to another local DB with same table. Worked for years. User account on replication job is gone, so job was failing. I updated it to a different account, and every 10 minutes, it had no errors then. However, it didn't replicate a new row.
I manually created the new record in the subscriber DB table.
Restarting the server ended up working, but now the replication fails because it has the queue of new records it's trying to add and the identity column value already exists in the destination table.

Options: Should I...

Delete the records I added so that the replication can copy down the new records?

Problem: Not sure if the used IDs will be able to be used with the new records coming over in the replication (because of the auto-numbering identity column), and we need those IDs to remain matched.

Somehow clean out the queue so that those records aren't attempted any more (since I already created them) -- not sure how to do this.

Yes, I searched a lot on this stuff, but I haven't seen this problem described in the same way.

Thanks for any help or answer!

You do not need to drop the subscription.

You're getting primary key violations as the replication agent is trying to insert duplicates.

  Delete the records I added so that the replication can copy down the new records?

Yes, you will need to manually remove the records in order to alleviate the primary key violations.


How the input entered through the front-end form match with the existing data in database Am working on an application that helps user to enter symptoms that matches those existing in the database and gets disease(diagnosis).

I have the following database table with information about symptoms, diseases, and disease-symptoms which associates the two tables, as shown below:

DISEASE_T   SYMPTOMS    disease-symptoms
==========  ==========  ========

DISEASE_ID  SYMPTOM_ID  DISEASE_ID

VALUE       VALUE       SYMPTOM_ID

I want to search user selected symptoms, which are array of symptoms id in disease-symptoms table and return top 5 matching disease IDs, based on, where most of the symptoms are matching with each disease.

For example, user select symptoms are 1,2,3 (cough,cold,headache)

DISEASE_T      SYMPTOMS_T  disease-symptoms

=========      ==========  ===============

1,malaria       1,cough          1,1

2,tuberculosis  2,cold           1,2

3,typhoid       3,headache       2,2
                                 2,3
                                 3,1
                                 3,2
                                 3,3

Help will be appreciated.

Select diseases with the 3 symptoms:

select DISEASE_ID, count()
  from DiseaseSymptoms_T
 where SYMPTOM_ID in (1, 2, 3)
 group by DISEASE_ID
having count() = 3  -- number of symptoms in the where

That will only list typhoid, because it has all three symptoms

If you want all the diseases with the symptoms 1 and 2

select DISEASE_ID, count()
  from DiseaseSymptoms_T
 where SYMPTOM_ID in (1, 2)
 group by DISEASE_ID
having count() = 2  -- number of symptoms in the where

This last query will also show the disease typhoid, because it has the two symptoms in the where

If you want only the first 5 rows, the syntax depends on the engine. For SQL Server use SELECT TOP 5 DISEASE_ID... For MySQL add LIMIT 1 at the end of the query... For Informix use SELECT FIRST 5...


Process sessions foreach minute I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL).

It's for online radio. A listener connects to the stream, and a row is created. Once they stop listening, Stop is updated to the current time.

Currently, it's about 80K rows per day.

I'd like to process the sessions once a day by determining the max amount of listeners for each minute, and push that data into a different table: Timestamp, ListenerCount (UNSIGNED SMALL INT).

I'm not super concerned with speed, just with working out how I can process this data within MySQL. I'm assuming I use a stored procedure (not much experience there), loop over each minute between the MIN(Start) and Max(Stop), and do a COUNT() between x-minute and x-minute:59.

Does this sound viable? Is there a better way?

Thanks for your time.

Here's an idea you could build on to achieve this: create a simple table that contains only a list of integers from 1 to 24*60 (i.e. number of minutes in a day). Use this to generate the list of minutes in the day you're interested in. Then join that on your session data, grouping by "minute" to get a count of active sessions.

Probably better explained with an example:

Tables:

-- Holds a list of integers
create table minutes (m int) engine innodb;
-- The session data
create table sessions (id int,
                       st timestamp,
                       en timestamp) engine innodb;

Generate the list of "minutes"

delimiter ;;
create procedure fill_minutes()
begin
  declare i int default 0;
  while i < 60*24 do
    insert into minutes(m) values (i);
    set i = i + 1;
  end while;
end;;
delimiter ;

start transaction;
call fill_minutes();
commit;

Adapted from Generating a range of numbers in MySQL

To generate the minutes in a given day:

select
    date('2013-01-01 00:00:00') + interval m minute
from
    minutes;

2013-01-01 00:00:00
2013-01-01 00:01:00
2013-01-01 00:02:00
2013-01-01 00:03:00
2013-01-01 00:04:00
2013-01-01 00:05:00
....

A bit of fake data:

insert into sessions (id, st, en)
values (1, '2013-01-01 00:00:00', '2013-01-01 00:10:00')
,      (2, '2013-01-01 00:05:00', '2013-01-01 00:10:00')
,      (3, '2013-01-01 00:07:00', '2013-01-01 00:12:00');

Join the minute table with the session data

select
    dates.d, count(*)
from
    (select date('2013-01-01 00:00:00') + interval m minute d from minutes) dates
    join sessions s on ((dates.d >= s.st) and (dates.d < s.en))
group by dates.d
order by dates.d;

d                   count(*)
2013-01-01 00:00:00 1
2013-01-01 00:01:00 1
2013-01-01 00:02:00 1
2013-01-01 00:03:00 1
2013-01-01 00:04:00 1
2013-01-01 00:05:00 2
2013-01-01 00:06:00 2
2013-01-01 00:07:00 3
2013-01-01 00:08:00 3
2013-01-01 00:09:00 3
2013-01-01 00:10:00 1
2013-01-01 00:11:00 1

Note:

I don't know how to avoid that "list of minutes" table in MySQL. Doable in Oracle with a select ... from dual connect by level <= ... or similar.
You'll probably need to restrict the join on sessions more to avoid a full scan of that table (limiting the start date at least, start and end sounds better)


mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to MySQL version 5.5 server, which is set to require X509.

Any other ways to get mysqlbinlog to connect?

Update: I've noticed this bug report from MySQL: https://bugs.mysql.com/bug.php?id=74864

And I've tried use --loose prefix for the 3 SSL options to connect. Still don't work.

Update: I am running Google Cloud SQL (a hosted version of MySQL 5.5, potentially tweaked by Google, running in Google Cloud), no other access except using MySQL client and via Google Cloud Dashboard to tweak only a few limited parameters.

This is in fact the same limitation for RDS in Amazon Cloud (AWS). The differences is that RDS have option to select MySQL minor versions whereas Google Cloud SQL doesn't allow you to choose a minor version, only 5.5 or 5.6 are available.

BOTTOM OF THE BUG REPORT YOU REFERENCED

[21 Jan 16:44] David Moss
Thanks for your feedback. This was fixed in version 5.7.3, and the following text was added to the 5.7.3 release notes: Previous versions of mysqlbinlog did not correctly accept the ssl-ca option in an option file. This fix ensures that this option can be correctly used. In earlier versions a work around is to use the loose-ssl-ca option.
[19 Feb 16:08] David Moss
Posted by developer:
This was covered in the release notes. Closing.
[27 Feb 9:02] Christoph Mitasch
will this also be fixed for the 5.6.X release?

MySQL 5.6 Documentation
The --ssl-crl and --ssl-crlpath variables were first introduced for mysqlbinlog in MySQL 5.6.3. Once MySQL 5.6 went GA, those options were not meant to be used at all. How did I know ? Look at the list of options for mysqlbinlog (Table 4.16). They appear in the list between --socket and --start-datetime=datetime BUT THEY HAVE NO HYPERLINKS !!!
mysqlbinlog
Here is something else to consider: mysqlbinlog is not a client program. It is a utility program. In mysql, client programs require logging into mysqld with a username and password.
If you look at the list of MySQL Client Programs, please note that mysqlbinlog is not there because it is a utility. You can see mysqlbinlog among the list of MySQL Administrative and Utility Programs.
If you are using an option file, mysqlbinlog can never respond to the options under the [client] group header. Why ? According to the Options File Documentation:

The [client] option group is read by all client programs (but not by mysqld). This enables you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

SUGGESTION
I don't know if you will encounter the bug with my suggestion, but here it goes ...
If you are using an option file, make the header use [mysqlbinlog] instead of [client]. As a utility, I know [mysqlhotcopy] can be used as a group header. Please try out [mysqlbinlog] as a group header and see if it helps.
ADDITIONAL OBSERVATION
Since you are connecting to DB Server running MySQL 5.5, its mysqld will never understand --ssl-crl and --ssl-crlpath variables because they belong to MySQL 5.6.3. I guess this means you were connecting from a MySQL 5.6 machine using the mysqlbinlog for MySQL 5.6. You will not need to specify any SSL connection options if you are remote connecting for binlgogs


oracle - list users with access to certain tables I'm sure this has been asked before but I can't seem to find the relevant details for the following.

Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it is limited and does not meet all my needs), if not does anyone have some queries for answering the following?

List all users who have been assigned a particular role?
List all roles given to a user?
List all privileges given to a user?
List which tables a certain role gives SELECT access to?
List all tables a user can SELECT from?
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

List all users who have been assigned a particular role

-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'

List all roles given to a user

-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';

List all privileges given to a user

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

List which tables a certain role gives SELECT access to?

-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';

List all tables a user can SELECT from?

--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';

List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;


Select statement for selecting every week's log record Here is a simplified version of the table in question:

+------------------------------------------------------+
| DatabaseName | LogRecordNumber | LogTimestamp        |
+------------------------------------------------------+
|DB1           | 47378910        | 2014-11-12 10:40:00 |
|DB1           | 48268418        | 2014-11-12 10:41:00 |
|DB2           | 223781480       | 2014-11-12 10:40:00 |
|DB2           | 228976522       | 2014-11-12 10:41:00 |
+------------------------------------------------------+

We have a table that contains the log record number for each database captured every minute. This data spans back a full year, or shorter if the database is newer than that.

We have to watch the log record number to ensure it will not go over the maximum number of bits that it can have. Currently I'm tracking this information with a simple query and adding it into a spreadsheet.

Sample Query:

SELECT Databasename, MAX(LogRecordNumber), LogTimestamp
FROM MyDB.MyTable
WHERE LogTimestamp BETWEEN '2014-11-12-04...' AND '2014-11-12-08...'
GROUP BY DatabaseName, LogTimestamp

I would like to instead develop a report that a technician could run to determine through stat regression when the log records would be out of bits.

So I now need a select statement that would retrieve the LogRecordNumber for each interval of time where we have a log record. Getting the log number once a week would be appropriate and allow me to make a reasonable regression guess at when we will be out of bits.

Again these data can be as far as a year back or as soon as a few months ago, so hard coding something like DATE(CURRENT_TIMESTAMP) - X WEEKS is not possible.

Is there any way to do what I'm suggesting with a query, or do I need to rethink this?

If it matters, the database in question is DB2.

It is possible do this in SQL.  There are really 2 problems here:

Find 1 record per week for each database
Calculate the average rate of consumption

For the first problem, you have a couple of ways to do this:

select databasename, logrecordnumber, logtimestamp
  from MyTable
 where logtimestamp > current timestamp - 42 days
   and dayofweek(logtimestamp) = 0
   and time(logtimestamp) between '11:59:30' and '12:00:30'

This looks at only records in the last 6 weeks, and then finds records that occurred on Sunday (dayofweek() = 0) at 12:00.  (Using the between predicate should handle minor differences in clocks on your database servers and/or instances when your script doesn't execute exactly on the minute.

It may also be possible to write this using OLAP windowing functions, but I'm not sure it would be any more efficient.

For the second problem you may want to look at using the LAG() function so you can calculate the delta in the log record:

select
    databasename
   ,logrecordnumber
   ,logtimestamp
   ,logrecordnumber - lag(logrecordnumber,1) over (partition by database order by logtimestamp) as logrecordnumber_growth
from
    MyTable
where
    logtimestamp > current timestamp - 42 days
    and dayofweek(logtimestamp) = 0
    and time(logtimestamp) between '11:59:30' and '12:00:30'

This example adds a calculation showing how much the logrecordnumber has grown since the week before.

Doing the rest of the calculation (extrapolating to when LSNs might run out) is left as an exercise for the OP.


MariaDB ran mysql_secure_installation, but seeing remote login attemps in logs I installed MariaDB and ran mysql_secure_installation and answered yes to all questions to remove anonymous user, test DB, remote login, etc.

However, I recently had MariaDB crash and in checking the logs I found numerous login attempts of the form:

  [Warning] IP address '218.60.8.253' could not be resolved: Name or
  service not known

Is this normal?  To me it indicates that the server is indeed accepting remote connections.

I checked /etc/my.cnf, and did not see the expected bind-address=127.0.0.1 directive shutting down remote login.  I have added it manually and restarted the server, but now I am wondering if the mysql_secure_installation did what it was supposed to do.

Is there a way to check that mysql_secure_installation ran correctly?

Yes, the server would still be accepting remote connections. And yes, it probably did what it was supposed to do. mysql_secure_installation doesn't set bind-address=127.0.0.1 neither in MariaDB nor in MySQL. It does however allow you to remove root accounts that are accessible from outside of localhost, as you have already done.

If you don't have any database users with host != localhost, then remote login shouldn't be possible. You can of course also block the MariaDB server port in your firewall.


MySQL default wait_timeout is 28800 but in my server I can see more than that value Im using AWS MySQL Aurora, the current wait_timeout is 28800 which is the default one, and interactive_waitimeout is also 28800.

But I can see more than the default value.

Ah! This one could be application framework has the value passed at the time of creating connection to DBs to set wait_timeout or interactive_timeout for their session level, this overrides default mysql settings.

If you enable general log on any test environment and have their application to point to that DB. You will find wait_timeout or interactive_timeout setting such values.

Short Term Fix:

You might consider killing connections that are more than 500secs in sleep state as a cron running every minute.

Permenant Fix:

Have application team to fix their code, by not to pass any default values to DB.
Once app is fixed, remove the cron that kills every minute.


Group permissions in postgreSQL not inheriting to table level I am moving to a group role from a lot of individual users and have one table that needs to have permissions SELECT, INSERT only.

Lets say I have:

user1
user2
newgroup

Both of the users are in the group and both users have INHERIT as well as the Group has INHERIT on it.

I have added

GRANT USAGE ON SCHEMA testschema TO newgroup;
GRANT user1 to newgroup;
GRANT user2 to newgroup;

as well as

GRANT SELECT, INSERT ON testschema.testtable TO newgroup;

When I log in as one of the new users, I do not have permission to that table(ex: testschema.testtable)

I look at the PSQL \z and see for that table newgroup has ar permissions.

I am getting permission denied for relation testschema.testtable.  I do not want to create more of a mess by individually putting permissions on the table (it works if I do that currently).

The simple test:

postgres=# create schema foo;
CREATE SCHEMA
postgres=# create table foo.t(x int);
CREATE TABLE
postgres=# create role foomaster nosuperuser nologin inherit;
CREATE ROLE
postgres=# create role fooslave password '111' login inherit in role foomaster;
CREATE ROLE
postgres=# grant usage on schema foo to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
ERROR:  permission denied for relation t
postgres=> set role postgres;
SET
postgres=# grant select on foo.t to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
 x
---
(0 rows)

It seems that you are doing something wrong.

Upd: About newely created table:

postgres=> set role postgres;
SET
postgres=# create table foo.tt(xx int);
CREATE TABLE
postgres=# set role fooslave;
SET
postgres=> select * from foo.tt;
ERROR:  permission denied for relation tt
postgres=> set role postgres;
SET
postgres=# grant select on foo.tt to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from  foo.tt;
 xx
----
(0 rows)

PPS: Hope that my investigations was helpful at least for somebody.


In PostgreSQL, can you read from an index during a ALTER INDEX ... SET TABLESPACE operation? I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production system, and the index is useful for making read performance acceptable, so I want the index to be usable as an index while it is being moved from one tablespace to another. Data writes are not a problem, they can be halted, turning the database into essentially a "read-only database".

But I can't find information in the postgres docs about this.

The PostgreSQL 9.3 docs for ALTER INDEX don't mention locks:

  This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. See also CREATE TABLESPACE.

The PostgreSQL 9.4 docs for ALTER INDEX mention locks but doesn't say which locks, and only when refering to ALL IN TABLESPACE form.

  This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all indexes to be moved and then move each one. This form also supports OWNED BY, which will only move indexes owned by the roles specified. If the NOWAIT option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs will not be moved by this command, use ALTER DATABASE or explicit ALTER INDEX invocations instead if desired. See also CREATE TABLESPACE.

So if I run an ALTER INDEX ... SET TABLESPACE ... will I still be able to read from the index while it is being moved?

Try to recreate the index concurrently, so the original one is still used while creating the new.

CREATE INDEX CONCURRENTLY idx_name
ON table_name
USING BTREE(col_name)
TABLESPACE new_tblspace


Centralized Database for user information Our current setup is that we have what I would call a centralized database that holds user information that is shared by several different databases. We have the several databases to separate out data from different apps, as only the user information is common between them. What is the best way of handling this? Currently we can't have foreign keys to user id as that is in a different database. Also, we have to hard code that separate database name in any stored procedures or queries that may need the user data, which makes it a pain to develop with as I can only have 1 development database rather than keeping one that works with different branches. Does anyone have a suggestion for a better way of handling this (other than bringing the databases together)?

Design is largely driven by personal preference, and I don't have enough context to tell you if you're approach is one I would agree with or not.  However, if you wish to proceed with your current setup, I can at least provide some answers to your questions.

  Currently we can't have foreign keys to user id as that is in a different database.... Does anyone have a suggestion for a better way of handling this (other than bringing the databases together)?

As you suspect you cannot define an explicit foreign key relationship between objects in different databases.  This StackOverflow post walks through a simple way to configure an implied fkey relationship, however it uses triggers that come with inherit risks and this approach will be unable to take advantage of any performance improvements you would get with a typical foreign key relationship.

  Also, we have to hard code that separate database name in any stored procedures or queries that may need the user data, which makes it a pain to develop with as I can only have 1 development database rather than keeping one that works with different branches. Does anyone have a suggestion for a better way of handling this (other than bringing the databases together)?

If you want to avoid hard links in your code to other databases, configure synonyms for said objects that exist in another database so your code doesn't have to use any three-part queries.


Unknown sticky Problem after MySQL upgrade few weeks ago we upgraded mysql server from 5.5 to 5.6
on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . (mostly phpfox - also few in other scripts) - also new data not submitted to database
I was reinstall mysql - disable cloudlinux - cagefs ... but problem not resolve. also rebuild php
after above , I move that database to remote-mysql-test-server with same Mysql version (5.6) and problem resolved. So I found this problem mysql related
yesterday in an other server like above (server B - that mysql upgraded from 5.5 - 5.6 few weeks ago) when mysql server restart , same problem occurred . for example when I answer ticket (WHMCS) , this date not submitted to ticket ... some clients can't update their site .

few website error like this :
SQL] [1364] Field 'stat_time' doesn't have a default value
STRING: INSERT INTO pn_statistics (stat_name,stat_value) VALUES ('Day-1394-02-24','0')

also in this server when I move database to remote-mysql-test-server this problem resolved.

I speak with few system admin and they can't find my issue .

Any suggestion ?

PROBLEM
I think your problem stems from a change in sql_mode.

sql_mode is blank in MySQL 5.5
It is not blank in MySQL 5.6

SOLUTION
Run this command
SET GLOBAL sql_mode = '';

Then, add this to my.cnf to keep sql_mode blank should you restart mysqld
[mysqld]
sql_mode = ''

You do not need to restart mysqld since you set it globally
GIVE IT A TRY !!!


How can I determine the average number of transactions per second in MySQL? I'd like to see how many transactions per second an instance of MySQL server is processing, on average (per hour and per day). Is there some query to determine this? I've looked through performance_schema as well as information_schema, but nothing seems obvious.

I am running version 5.6.14 for x86_64 on debian6.0

Have you looked at stats from \s? I wasn't sure by 'transaction' you simply mean queries.


mariadb won't start (InnoDB error: space header page consists of zero bytes in data file ./ibdata1) The following messages are in /var/log/mariadb/mariadb.log when I try to start mariadb:

151103 13:42:01 [Note] InnoDB: Initializing buffer pool, size = 128.0M
151103 13:42:01 [Note] InnoDB: Completed initialization of buffer pool
151103 13:42:01 [Note] InnoDB: Restoring page 0 of tablespace 0
151103 13:42:01 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
151103 13:42:01 [ERROR] InnoDB: space header page consists of zero bytes in data file ./ibdata1
151103 13:42:01 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
151103 13:42:01 [ERROR] Plugin 'InnoDB' init function returned error.

This error seems similar to this one (https://stackoverflow.com/questions/26576745/innodb-error-space-header-page-consists-of-zero-bytes-in-data-file-ibdata1
), but this is a brand new Fedora 22 system and the database has not been used, yet.


That's a fairly serious defect.

http://bugs.mysql.com/bug.php?id=55477

The lexer, encountering ! considers that the next character might be a continuation of the != operator, =, and if that happens, everything is fine.  Otherwise, if the next character is of the same class (of characters which might be part of a logical operator, such as <) but doesn't match what's expected... then the next character just... disappears, apparently since ! is a complete logical operator by itself.

The result is that !! resolves to ! when not separated by whitespace...

!!expr resolves to !(expr)

!!!expr resolves to !(!(expr))

!!!!expr also resolves to !(!(expr))


Providing an ad-hoc report facility for business users in SQL Server 2008 R2 We are using SQL Server 2008 R2. We need to provide the business users an interactive data exploration facility which they can drag and drop the attributes(columns) they want to create their own ad-hoc report.

I know in SQL Server 2012 it can be done using Power View and Sharepoint, but what about SQL Server 2008 R2?

You're probably looking for Report Builder 3.0 and/or Power Pivot.

  Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive
  report authoring environment for business and power users. It supports
  the full capabilities of SQL Server 2008 R2 Reporting Services.

Your Excel ninja users will likely prefer Power Pivot, your more typical tabular report folk will feel right at home with Report Builder. There's a useful summary of the differences between Report Builder and the BIDS Report Designer at Designing Reports in Report Designer and Report Builder 3.0 (SSRS).


Is it possible to set SQLite DB on server and connect to it in an Android app? I'm creating an app as a study project. One of requirements is not to hardcode. For this reason, I want to get data from DB with every start of the app and change data stored in DB outside of the app code. I wanted to use Redis and store data in JSON at first, but there were too many problems, because Redis doesn't work with Android by default.

So, the question is: is it possible to use SQLite set on server? I know that this DB is not supposed to work in client-server mode, but I have found out some SQLite hostings in the Internet. If it is possible, how to perform it? If not, which way should I store data? SQL format is pretty convenient for me, because it is literally a table.

No, it's not possible. SQLite is an embedded database engine, which means it runs as part of your program and uses a file (usually) in the application sandbox on Android (so this is not part of your source code).

  SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

It's probably possible to have an application run on a server which is able to function as a backend for your app and stores its data in a SQLite file, but I'd rather choose a database which is intended for this kind of purposes like PostgreSQL or MySQL. You can talk SQL to those databases too. Of course, in a production-grade application you'll need an application as a back end, not just a database, which is able to authenticate users, validate business logic etc.


SQL backup to azure storage failed I have a SQL Server 2014 up and running and I am trying to backup a specific database to Azure storage. I created a credential using the storage account name as identity and the primary key as password. Then I tried to backup the database to URL but I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server '*****'.  (Microsoft.SqlServer.SmoExtended)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "https://******.blob.core.windows.net/backupcontainer/demo_backup_2016_06_22_110957.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo)

I figured out what the problem is. When I created the storage account I choose the kind to be "Blob Storage". It must be "General Purpose" to work. The backup steps was correct, the problem was with the kind of storage account.


Group by reversible pair I'm sure my title is not clear enough...

Let's say I have a "chat" table, storing individual messages like this:

message_id, from_user_id, to_user_id, timestamp

For a given user_id, I want a list of "conversations" for this user. A conversation would be all messages involving the same 2 users. Kinda of like Facebook.

What would be the best way to achieve this? I was starting to write a simple query with GROUP BY but then realized the pair of user ids could be both ways - but to me it's still the same conversation.

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY ... ??

You can use a UNION of two subqueries:

SELECT
    user_id,
    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
FROM
  ( SELECT  to_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE from_user_id = 1
    GROUP BY to_user_id
  UNION ALL
    SELECT  from_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE to_user_id = 1
    GROUP BY from_user_id
  ) AS tmp
GROUP BY
    user_id ;

Two indexes, on (from_user_id, to_user_id, timestamp) and (to_user_id, from_user_id, timestamp) would help efficiency of the subqueries.


Removing all Schemas and Objects from a database, excluding specific Schemas and Objects I have a database with three main Schemas;

Global
Permissions
Unique Schema name based on the customers ID number (1, 2, 3, 4, ect...)

Each customer has objects in all three schemas, with the Global and Permissions schema object names, containing the customers ID number followed by an _

Example;

[1].[Test]
[Global].[1_Test]
[Permissions].[1_Test]

So far, I have written the bellow script that does this, but only keeps one customers data, not multiple;

    DECLARE @TenantId NVARCHAR(10)
DECLARE @Commands NVARCHAR(MAX)
DECLARE @Statements TABLE
    (
        Commands NVARCHAR(500)
    )

SET @TenantId = '686'

INSERT INTO @Statements
    EXECUTE  (

'SELECT ''DROP TABLE'' + '' '' + ''['' + s.[name] + '']'' + ''.'' + ''['' + t.[name] + '']'' AS ''Commands''

FROM sys.schemas s
    JOIN sys.tables t on s.schema_id = t.schema_id

WHERE   s.name <> '''+ @TenantId +'''
AND         s.name <> ''dbo''
AND         t.name NOT LIKE '''+ @TenantId +'_%''
AND         t.name NOT IN (''DatabaseVersion'', ''PermissionTypes'')

ORDER BY s.name, t.name'    )

SELECT * FROM @Statements

--SET @Commands =(
--SELECT STRING_AGG (CAST([Commands] AS NVARCHAR(MAX)), ' ') FROM @Statements )

--SELECT (@Commands)

How can I convert this so that it takes multiple IDs into account, except for just one? I feel that I need to use a Cursor here, but not sure how that would be structured.

Thanks in advance.

I've cracked it! Thought I'd share in case anyone else was interested too.

CREATE TABLE #Safe
    ( [Schema] NVARCHAR(50), [Table] NVARCHAR(100) )

INSERT INTO #Safe
SELECT SCHEMA_NAME(schema_id) AS [schema], [name]
    FROM sys.tables
        WHERE SCHEMA_NAME(schema_id) IN ('1', '2')
        OR [name] LIKE ('1_%')
        OR [name] LIKE ('2_%')
        OR [name] = 'DatabaseVersion'

DECLARE cur_delete CURSOR FOR
SELECT SCHEMA_NAME(schema_id) AS [schema], [name]
    FROM sys.tables
        WHERE SCHEMA_NAME(schema_id) NOT IN (SELECT [Schema] FROM #Safe)
        OR [name] NOT IN (SELECT [Table] FROM #Safe)

OPEN cur_delete

DECLARE @Schema NVARCHAR(50)
DECLARE @Table sysname
DECLARE @SQL NVARCHAR(MAX)

FETCH NEXT FROM cur_delete
INTO @Schema, @Table;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP TABLE [' + @Schema + '].[' + @Table + '];';
    PRINT @SQL
    -- EXEC sp_executesql @SQL;

FETCH NEXT FROM cur_delete INTO @Schema, @Table
END

CLOSE cur_delete
deallocate cur_delete

DROP TABLE #Safe

Found this post: Drop all tables from database command apart from named few

It included a Cursor that, with some modification, I was able to get working for my needs.


How to perform almost the same query N times without rewriting it N times? Basically I have a list of numbers like:

1,2,3,4,5

And I want to perform a query like this for each of the previous numbers:

SELECT COUNT(*), field_name
FROM my_table
WHERE field_name = NUMBER

Where "NUMBER" will be 1, 2 ... 5 in this example. So I expect a result containing 5 rows and 2 columns.

Right now I'm doing the task this way:

(SELECT ... field_name = 1) UNION (SELECT ... field_name = 2 ) ... (SELECT ... field_name = 5)

Is there any alternative way to do this while still using a single query? In particularly I don't like how the last query may become very long as my list of numbers grows.

-----EDIT-----

What if the query to execute would be something like:

SELECT *
FROM my_table
WHERE field_name > NUMBER

Using the same mechanism as above but this time let's image that this query will return more than 1 row at every execution.

Is there any way to create a single, elegant, query to do this job instead of using "UNION" multiple times?

You can use the IN statement

SELECT COUNT(*), field_name
FROM MY_table
WHERE field_name IN (1,2,3,4,5)
GROUP BY field_name


Oracle 19c Standard Edition RPM installation I'm wondering if it's possible to install Oracle Standard Edition from RPM, not Enterprise Edition. It's possible to choose edition when you do 'runInstaller', is it possible to set some parameter during rpm installation?

Restrictions and Guidelines for RPM-Based Installations

Patching Oracle Database software using RPMs is not supported. Please    use the OPatch utility and follow the regular patching process to    apply Oracle Database patches.
An RPM-based Oracle Database    installation is not available for Standard Edition 2.
RPM-based    database upgrades using rpm -Uvh is not supported. For Oracle    Database upgrades, follow the regular upgrade process.
An RPM-based    installation supports the installation of multiple Oracle Database    software versions into different Oracle homes on the same machine.


query an indexed jsonb column I want to query a jsonb column with the @> operator.

This query gives results:

select * from json_table1
where p_attribute @> '{"age":"37"}'

This query doesn't fail but also doesn't give results:

select * from json_table1
where p_attribute -> 'age' @> '37'::jsonb

The table used in the example:

create table json_table1 (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute jsonb,
    quote_content text
)

The index on p_attribute:

create index gin_idx on json_table1 using gin(p_attribute jsonb_path_ops)

Since "37" is a string, not a number, your second query would have to use a jsonb string:

... WHERE p_attribute -> 'age' @> '"37"'::jsonb

Note the double quotes around 37.

Also note that this query wouldn't be able to use the index, while the first query can.


Probable circular dependency, but query still runs I built this ER diagram in MySQL Workbench and it will run and generate the database.  My concern is that the relationship between the employees, departments, positions, and employment_statuses is a circular relationship.

Because I know people will ask, this is something I was working on in a class.  I'm not asking for help with homework; this is something we were working on in class and I took a different route than the professor.  I'm asking for clarification about database modelling theory.

There's nothing wrong with your model. Au contraire - it's pretty advanced!

employment_statuses provide data in a time-lined, historical, fashion, whilst departments identifies the actual manager.

These entities are very different both in purpose and use (queries on employment_statuses will serve different scenarios than those on manager).

I have to congratulate you, as I repeatedly work with peers that just can’t abstract this kind of scenario (the majority just see the now, or, at maximum, a single then - not the natural sequence of thens leading to the as today).

A remark: you should move high/low_salary from positions to another time-lined entity.


MySQL Duplicate entry for a greek word I have a column which is unique and when I add Αγάθη and Αγαθή (which are different) it says #1062 - Duplicate entry 'Î‘Î³Î±Î¸Î®' for key 'word'. My collation is set to utf8_general_ci. Is there any better collation?

Of the several utf8 collations available:

mysql> SELECT * FROM information_schema.collations WHERE character_set_name = 'utf8';
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci          | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_bin                 | utf8               |  83 |            | Yes         |       1 |
| utf8_unicode_ci          | utf8               | 192 |            | Yes         |       8 |
| utf8_icelandic_ci        | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci          | utf8               | 194 |            | Yes         |       8 |
| utf8_romanian_ci         | utf8               | 195 |            | Yes         |       8 |
| utf8_slovenian_ci        | utf8               | 196 |            | Yes         |       8 |
| utf8_polish_ci           | utf8               | 197 |            | Yes         |       8 |
| utf8_estonian_ci         | utf8               | 198 |            | Yes         |       8 |
| utf8_spanish_ci          | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci          | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci          | utf8               | 201 |            | Yes         |       8 |
| utf8_czech_ci            | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci           | utf8               | 203 |            | Yes         |       8 |
| utf8_lithuanian_ci       | utf8               | 204 |            | Yes         |       8 |
| utf8_slovak_ci           | utf8               | 205 |            | Yes         |       8 |
| utf8_spanish2_ci         | utf8               | 206 |            | Yes         |       8 |
| utf8_roman_ci            | utf8               | 207 |            | Yes         |       8 |
| utf8_persian_ci          | utf8               | 208 |            | Yes         |       8 |
| utf8_esperanto_ci        | utf8               | 209 |            | Yes         |       8 |
| utf8_hungarian_ci        | utf8               | 210 |            | Yes         |       8 |
| utf8_sinhala_ci          | utf8               | 211 |            | Yes         |       8 |
| utf8_german2_ci          | utf8               | 212 |            | Yes         |       8 |
| utf8_croatian_ci         | utf8               | 213 |            | Yes         |       8 |
| utf8_unicode_520_ci      | utf8               | 214 |            | Yes         |       8 |
| utf8_vietnamese_ci       | utf8               | 215 |            | Yes         |       8 |
| utf8_general_mysql500_ci | utf8               | 223 |            | Yes         |       1 |
+--------------------------+--------------------+-----+------------+-------------+---------+
27 rows in set (0.00 sec)

These are reasonable ways to compare greek strings:

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'Αγάθη' = 'Αγαθή' COLLATE utf8_general_ci;
+-----------------------------------------------------+
| 'Αγάθη' = 'Αγαθή' COLLATE utf8_general_ci           |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Αγάθη' = 'Αγαθή' COLLATE utf8_unicode_ci;
+-----------------------------------------------------+
| 'Αγάθη' = 'Αγαθή' COLLATE utf8_unicode_ci           |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Αγάθη' = 'Αγαθή' COLLATE utf8_unicode_520_ci;
+---------------------------------------------------------+
| 'Αγάθη' = 'Αγαθή' COLLATE utf8_unicode_520_ci           |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Αγάθη' = 'Αγαθή' COLLATE utf8_bin;
+----------------------------------------------+
| 'Αγάθη' = 'Αγαθή' COLLATE utf8_bin           |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT BINARY 'Αγάθη' = 'Αγαθή';
+------------------------------------+
| BINARY 'Αγάθη' = 'Αγαθή'           |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.01 sec)

As you can see, only the utf8bin collation considers them as different, and of course,  the BINARY comparison.

There doesn't seem to be a specific greek collation in utf8 or utf8mb4, but there is also a 1-byte ISO 8859-7 implementation "greek", with a couple of extra collations:

mysql> SELECT * FROM information_schema.collations WHERE character_set_name = 'greek';
+------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME   | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------+--------------------+----+------------+-------------+---------+
| greek_general_ci | greek              | 25 | Yes        | Yes         |       1 |
| greek_bin        | greek              | 70 |            | Yes         |       1 |
+------------------+--------------------+----+------------+-------------+---------+
2 rows in set (0.00 sec)

I do not know Greek at all, so make sure you double check all your queries if you change a column's default encoding and collation, as it may have unexpected ordering/comparing/grouping results both for Greek and for other languages (e.g. utf8 does not allow storing emojis; utf8mb4_unicode_ci and utf8mb4_unicode_520_ci compares them differently), and what you may want would depend a lot on the context of the call.


Windows crashes on doing sql dump Windows simply crashes and opens the blue screen for dumping physical memory on trying to import sql dump.
Blue screen error: IRQL_NOT_LESS_OR_EQUAL
So far I have tried 4 methods:

Through workbench
Through command prompt using mysql command
Through command prompt using mysql -h command
Through ant: ant import-database after creating data-sync folder

Windows crashes in all the 3 cases

Before each new trial I un-install mysql and delete the MySQL folder from Program Files and MySQL data from Program Data. Size of dump files is 2.75 GB.

MySQL versions I have tried with:

 mysql-5.5.44
 mysql-5.5.33.2
 mysql-5.5.44.0

System configurations:

Workbench: 6.3.30
My system has 12 GB RAM
Windows 7 Service Pack 1
AMD A6-5400B APU with Radeon(tm) HD Graphics 3.60GHz

That error is nonsequiter with regards to MySQL. This would especially be true if your Windows server crashed for reasons other than MySQL. There are ways to fix IRQL_NOT_LESS_OR_EQUAL.
However, if you are reloading a mysqldump and it is the only reason Windows crashes, you may have to accommodate Windows until you fix the IRQL_NOT_LESS_OR_EQUAL error by applying one of the following suggestions (but not both):
SUGGESTION #1 : Change the mysqldump layout
You may have to create another mysqldump using --hex-blob or --skip-extended-insert but not both. That way, MySQL will ingest data in bite-sized chunks instead of woofing down thousands of rows of data. See my answer to the post When does creating a .sql backup with mysqldump become too large? for clarification.
SUGGESTION #2 : Change the MySQL Packet size
Most people take the MySQL Packet for granted. Some mysqldump could time out if you not paying attention. mysqldump's default for max-allowed-packet in 24M. Extended inserts can benefit from a larger packet upon reload. It could possibly increase the number of rows per extended INSERT command. Since a mysqldump can trigger a crash, you could experiment with  slightly larger values (the max size is 1G). The MySQL Packet never starts out at max_allowed_packet. The size starts out at net_buffer_length and expands to max_allowed_packet on demand.
EPILOGUE
These two suggestions is really no more than a BandAid for your scenario. You need to do your homework and fix the IRQL_NOT_LESS_OR_EQUAL first before resorting to workarounds.


Problem when extracting the structure of DB2 (Back up Structure) I want to extract the structure of a 9.7 DB2 database to use to clone it but some of the authorization statements are missing afterwards:
I tried something like this:

db2look -d dbName -a -x -o db2look.sql

what I noticed, is that the Authorization Statements on User Defined Functions and Stored Procedures are missing.

I expected to see something like this: https://www-01.ibm.com/support/docview.wss?uid=swg1IC65658

EDIT: A try indicated, that this command correctly extracted the GRANTstatements on the productive system.

GRANT statements will only be extracted if they were ever explicitly issued against database objects, e.g. GRANT EXECUTE ON FUNCTION myfunc TO USER whoever. Implicit privileges will not be extracted, because technically they were never GRANTed.


Get partial results in Postgres in case of timeout? Is it possible to send a query that does a sequential scan of the table - something like
SELECT content
  FROM some_big_table
 WHERE <some criteria(content) are met>;
 LIMIT <...>;

with a time limit (e. g. 1000 milliseconds) - so that the query terminates after the specified time period and returns any rows it could find by that time as a result of the scan?
I'm not worried about the result being predictable - I just need to give the user the first matches the server can find, if any.
SET statement_timeout does not help here as it cancels the query altogether if it doesn't execute within the time limit, while I need the partial results.

There is nothing to do exactly what you want, making a query end normally on the server based on a time limit rather than a row LIMIT.  You could declare a cursor and then FETCH rows in chunks (perhaps chunks of 1), closing the cursor after a certain amount of time has passed as witnessed on the client. Depending on the driver you are using, there may be other options to get similar results, such as PQsetSingleRowMode in libpq.


Database model / design best practice I'm looking for a some guidance/advice on how to best model my data.

Some background: I have location information that I'm trying to model. There are two categories of locations (company and non-company locations), as such, not all locations have the same data elements.

The two tables below represent my current model (without enumerating all columns).

My thoughts on the current design:

If I combine the two tables below I will have to put in null values for company_code, because not all locations have a company_code. I do not want to add nulls values because of the huge number of different data elements between the two different types of locations.
The location_company and location are two important concepts in the company. I would like to have tables in the database two represent these two concepts.

I'm unclear if this is the best approach. Any thoughts on the design would be greatly appreciated. Any additional thoughts are also welcomed.

If my issue is unclear, please ask. I will graciously add clarification. Thanks in advance.

I would say that if the company locations and non-company locations store a fair amount of different data, then to avoid a bunch of NULLS I would have two tables, and let the application insert data into the appropriate table. A different approach could be to have a table [Locations], with all the data that it needs, then have a table [CompanyLocations] and another table [Non-CompanyLocations]. The tables for company and non-company locations would then reference a base table using a foreign key, something very like what you have in the image, if I am reading it the same way you are.


How to specify authentication database and target database separately on mongodb connection uri? I am using this connection uri to connect to mongodb: mongodb://user:password@localhost/admin. It will use admin as bot authentication and target database. How can I make the uri to use admin as authentication but allow me to connect to a different database? Please see below command as an example:

mongo --host localhost -u user -p password --authentication admin test

the above command will use admin as the authentcation database but connect to test database. how can I do the same thing on uri?

Yes.. Easily!

mongo "mongodb://test:testpwd@localhost/test?authSource=admin"
MongoDB shell version v3.4.6
connecting to: mongodb://test:testpwd@localhost/test?authSource=admin
MongoDB server version: 3.4.6
MongoDB> db
test
MongoDB>

You put your destination DB at end of "base" string and add authentication db to parameter authSource


Is Log Shipping or Mirroring a better solution for Disaster Recovery? I am planning an index rebuild, however my logs are prone to huge overblown sizes.

In fact I was leaning towards doing an intelligent re-indexing, but since the fragmentation levels are greater than 30% I thought I should do a rebuild.

My databases use the full recovery model.  Should I change the recovery model to Bulk logged before I do the rebuild later this weekend when there are less transactions happening.

Log shipping is enabled as a DR solution in these databases.

What do you suggest if my latency is quite high? Is there a better DR solution that I should look into?

I think this is kind of the same question as the other one you just posted. there are a couple of new points you address:

Bulk logged mode doesn't make the logs shipped over the wire smaller. It just makes writing to the local log file less IO intensive. Once the log has to be backupped, alle changed pages haved to be included in the backup. The end result is that your trans log backup will be just as big.

If you already have High latency, changing to mirroring will not change that. the only way to spread the load is: stop doing rebuild and change to a spread reorganize strategy (as I mentioned in your other question.)

Or as somebody else there mentioned, start doing more frequent log backups and ship smaller log backups in a higher frequency.

Just based on what you are telling us now. (the high latency) I personally would not advise to change to mirroring. But the choice between mirroring and log shipping should be based on many more items then only latency.


MySQL order of query My query return like this.

12
14
14
99
78
78
54

I want result to be sorted so that no same number comes consecutively in the result. The result that I want is like this.

12
14
99
14
78
54
78

Can you please post an example of your query? Without knowing what your query structure look likes, this will be difficult to fix. However here is an example that may point you in the right direction based off of your post title, in regards to ORDER and GROUP:

SELECT pkey,
       COUNT(int),
  FROM my_table
  LEFT JOIN this_table USING (key)
 GROUP BY 1
 ORDER BY 2;


Why is my database restore being aborted? I'm trying to restore a SQL Server 2016 database into a SQL Server 2017 docker instance (microsoft/mssql-server-linux:latest). The process seems to be:

Create docker container: docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=<strong password>’ -p 1433:1433 --name 'sql1' microsoft/mssql-server-linux
Create backup directory: docker exec -it sql1 mkdir /var/opt/mssql/backup
Copy backup to directory: docker cp Foobar.bak sql1:/var/opt/mssql/backup
Login to mssql server: mssql -o 1433 -u sa -p
Get list of backed up files: restore filelistonly from disk='/var/opt/mssql/backup/Foobar.bak' <- each row returned will result in a move command in the next step
Restore database: restore database Foobar from disk='/var/opt/mssql/backup/Foobar.bak' with stats=10, replace, move 'Foobar' to '/var/opt/mssql/data/Foobar.mdf', move 'Foobar_log' to '/var/opt/mssql/data/Foobar.ldf'

The last step is failing with the output:

  Error: The backup or restore was aborted.

I tried to find an error log, and found /var/opt/mssql/log/errorlog. AFAICT, nothing gets output in this file from my aborted restore operation.

How can I determine what is happening? Are there logs available that I can use to get more info?

An easy way to execute any ad-hoc query within your container, including RESTORE, is with SQLCMD:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -Usa -P<strong password> -Q"restore database Foobar from disk='/var/opt/mssql/backup/Foobar.bak' with stats=10, replace, move 'Foobar' to '/var/opt/mssql/data/Foobar.mdf', move 'Foobar_log' to '/var/opt/mssql/data/Foobar.ldf';"


Improve CASE select performance I have the following query which is taking up to 2 seconds to run on a data set of less than 100K rows.

My SQL is very rusty but this just looks like it's doing a ton more work than it should. Can anyone offer some pointers on where to look to speed this up?

SELECT
    a.AddressID,
    IsPrincipal = CASE
    WHEN EXISTS(SELECT TOP 1 1 FROM dbo.Setting s WHERE s.SettingValue = a.AddressID AND s.SettingDefinitionID = 3 AND s.ProfileID = 1)
                THEN 1
                ELSE 0
            END,
    IsPickUp = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 2)
                THEN 1
                ELSE 0
            END,
    IsSender = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 3)
                THEN 1
                ELSE 0
            END,
            IsDelivery = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 4)
                THEN 1
                ELSE 0
            END,
    IsReceiver = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 5)
                THEN 1
                ELSE 0
            END
        FROM dbo.[Address] AS a
        WHERE a.MFTID = '12345'

Instead of doing a lookup like that for each row, you could join the AddressRole and Setting tables like this:

SELECT
    a.AddressID,
    IsPrincipal = Max(iif(s.SettingValue Is Not Null, 1, 0)),
    IsPickUp = Max(iif(ar.AddressRoleTypeID = 2, 1, 0)),
    IsSender = Max(iif(ar.AddressRoleTypeID = 3, 1, 0)),
    IsDelivery = Max(iif(ar.AddressRoleTypeID = 4, 1, 0)),
    IsReceiver = Max(iif(ar.AddressRoleTypeID = 5, 1, 0))
  FROM dbo.[Address] AS a
  Left Join dbo.Setting As s
    On a.AddressID = s.SettingValue
    And s.SettingDefinitionID = 3
    And s.ProfileID = 1
  Left Join dbo.AddressRole As ar
    On a.AddressID = ar.AddressID
  WHERE a.MFTID = '12345'
  Group By a.AddressID;

This should reduce the number of joins performed, and not change the cardinality of the resultset.

I added the MAX so that I could do a GROUP BY, since I fully expect that there might be more than one row in the AddressRole table for each row in the Address table. This way we maintain the cardinality as being the same as the Address table, but we can see if any of the rows in the AddressRole contained the value of AddressRoleTypeID that we were looking for.

The MAX and GROUP BY aren't needed if the subqueries of your original query without the TOP return a single row.


"Could not find login" failures for remote LocalSystem that exists I imagine security changed in some way for Windows 2016, but I'm unable to determine what exactly.

For context: We have a variety of remote clients that handle data processing within a (non-SQL) data source as well as ETL to SQL instances. All of those processes are managed by services that may be running as LocalSystem or an AD user. The remotes are running a mix of Windows 2008 and 2012.

What we're finding is that after the remotes migrate to Windows 2016, the services running as system accounts no longer can connect to SQL Server 2016; logging errors that a login could not be found for "DOMAIN\MACHINE$", even though the login was present beforehand and still is. The new machine takes on the name of the old one, and the old-server services' registry keys are copied to the new remote to make sure nothing gets changed inadvertently.

This has been resolved by changing the service to run as the AD user; what I currently consider ideal seems to be required. In order to best push for the process changes to use the AD user all the time I'm trying to understand what about Windows 2016 System Accounts would "trick" SQL Server into thinking there's no login.

SQL Server uses SIDs (Security IDs) behind the scenes https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql?view=sql-server-ver15

The name column is a label, and doesn't have to match what is in AD as long as the SIDs match. For example, I have seen a user who was able to connect to SQL Server after their AD username changed (married surname), but the old username still showed in SQL Server.

I suspect that this might be the other way around, and that the name is the same, but the sid was regenerated when the OS was upgraded. One way to test that would be to create login ... from windows for the computer account on a different, test SQL Server instance, and compare the sid column values for old and new logins.

HTH