Unable to search between IPv6 range when converting string IP Address to VARBINARY(16) I am attempting to search for IPGeolocation data, which is presented by the data vendor as a start and end range for both IPv4 and IPv6. However, when I convert the IP that is provided, I am getting incorrect results.

To search by range, I am converting the VARCHAR(39) representation of the IPv6 into a VARBINARY (i.e., Hex) and then using the BETWEEN operator to see if the searched Hex value falls within a specific range.

Query logic:

SELECT CONVERT(VARBINARY(16), '2600:8800:6a06:2000:1d29:3b7:8c7c:271b')

Search:

IP: 2600:8800:6a06:2000:1d29:3b7:8c7c:271b

IpHex: 0x323630303A383830303A366130363A32

Result 1

IpStart: 2600:8800:490:0:0:0:0:0

IpStartHex: 0x323630303A383830303A3439303A303A

IpEnd: 2600:8800:77f:ffff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A3737663A6666

Result 2

IpStart: 2600:8800:6a06:1d01:0:0:0:0

IpStartHex: 0x323630303A383830303A366130363A31

IpEnd: 2600:8800:6a06:24ff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A366130363A32

Initially there are two problems with your conversion:

It's the wrong "type" of conversion. You are converting a string into a hex / binary representation of the characters them selves. For example, a "26" will become 0x3236 in hex as 0x32 is the same as doing CHAR(0x32) and 0x36 is the same as CHAR(0x36), or it can be referred to as the ASCII value of the number "2". What you want is to simply change the form of the string "26" (which represents a byte) into 0x26. And for that you need to use a style number of "2" for the CONVERT function. For example:

SELECT CONVERT(VARBINARY(2), '26'), CONVERT(VARBINARY(2), '26', 2);
-- 0x3236    0x26

This is why you are getting 0x323630303A383830303A366130363A32 from 2600:8800:6a06:2000:1d29:3b7:8c7c:271b. You are really only getting the first 16 characters from that string, which are: 2600:8800:6a06:2. The two characters on the left I explained directly above. The 3 characters on the right, 6:2 are the 0x363A32 on the right side of the hex value, since 0x36 = "6", 0x3A = ":", and 0x32 = "2".
You will need to get rid of the colons as they are not valid binary digits. But the colons are there as delimiters for a reason: there are 8 segments, and if leading "0"s are removed, which is valid to do, then 0 really needs to be 0x0000, not 0x00 or 0x0. So, you need to ensure that each segment comes back as 4 hex digits.

You can accomplish all of this by:

splitting on the colons
left-padding with "0"s
converting to BINARY(2)
concatenating those pieces together

If you are using SQL Server 2016 or newer, you can probably use the built-in STRING_SPLIT function. Or, if you are using a version prior to SQL Server 2016, you can either use whatever splitter you already have, or you can use the free version of my SQL# SQLCLR library (there are several string splitters in the Free version).

For example:

SELECT RIGHT('0000' + prt.[SplitVal], 4) AS [string],
       CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2) AS [hex]
FROM   SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;

returns:

string    hex
2600      0x2600
0100      0x0100
0400      0x0400
0000      0x0000
0000      0x0000
0000      0x0000
0000      0x0000
0000      0x0000

That was just a way to see what the pieces are. To put them together into a single hex value, you can do the following:

DECLARE @IPv6 VARBINARY(16) = 0x;

SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2)
FROM SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;

SELECT @IPv6;
-- 0x26000100040000000000000000000000

You can create a function to handle this conversion. You can then store these VARBINARY(16) values and create an index on them, and then convert the string coming in and store that in a variable to use in the query.

Here is that same approach, but using the built-in STRING_SPLIT function:

DECLARE @IPv6 VARBINARY(16) = 0x;

SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[value], 4), 2)
FROM STRING_SPLIT('2600:100:400:0:0:0:0:0', ':') prt;

SELECT @IPv6;
-- 0x26000100040000000000000000000000

Just keep in mind that some folks are hesitant to use STRING_SPLIT in this type of situation because here the order that the split parts are returned is important as it can change the value if they came back in a different order, and the built-in function doesn't return a row number. I personally don't see how the returned values could come back out of order, but that is just an educated guess and not a guarantee. If you want a guarantee, then don't use the STRING_SPLIT built-in function.

P.S. The solution shown above (i.e. splitting on the colons) does not handle the "reduced" notation / syntax whereby "::" can be substituted for one or more consecutive ranges of "0000". For example: 2600:100:400::0. Hence, that solution requires that all 8 segments exist with at least "0" in them.

P.P.S. If you are using .NET either in the app or SQLCLR or SSIS, you can use a combination of IPAddress.TryParse and IPAddress.GetAddressBytes to handle the reduced "::" notation. Or, you could always write a bunch of code to handle it in pure T-SQL.

P.P.P.S. INET_IPv6toBinary will be available in the next release (version 4.3) of SQL# :-)


Finding the end of a Relationship chain optimally I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from the individual record, I don't know what position it is within the chain nor what the first part or last part of the chain.

For demonstration purposes, assume we have the following setup. I realize that there are many design issues with this type of setup, but this is what I have to work with.

CREATE TABLE Relationships (
ID VARCHAR(4),
ParentID VARCHAR(4),
ChildID VARCHAR(4)
)

-- Insert root entries with their children
INSERT INTO Relationships (ID, ParentID, ChildID)
VALUES ('0001', '', '0003'), ('0002', '', '0004')

-- Now add further entries for each relationship chain
INSERT INTO Relationships(ID, ParentID, ChildID)
VALUES('0003', '0001', '0005'), ('0005', '0003', '0006'), ('0006', '0005', '0007'), ('0007', '0006', ''),
  ('0004', '0002', '')

--Now we have two chains of 0001 -> 0003 -> 0005 -> 0006 -> 0007 and 0002 ->   0004

With a recursive CTE like below, I can find how all records are related in their chain and their position within the chain.

WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID
)

SELECT * FROM RelationshipChain
ORDER BY RootID, Seq

For about 2 million records this runs in about 30 seconds which is pretty good, however if I try to also include what the final part of the chain, it takes 4x as long to run. Currently, I am doing it like this:

WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID
)

SELECT *
FROM RelationshipChain rc
CROSS APPLY (
SELECT MAX(Seq) AS FinalSeq FROM RelationshipChain WHERE rc.RootID = RootID
) AS b
CROSS APPLY (
SELECT ID AS LastChild FROM RelationshipChain WHERE b.FinalSeq = seq AND rc.RootID = RootID
) AS c
ORDER BY RootID, Seq

Is there a way to do this more efficiently?

I don't have your dataset, so can't test if this is better, but it feels better. After building the chains, we reverse them to find the 'childest' item in each chain, then join back to the original chain.

-- The same as your first CTE
;WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID
)

-- This CTE is new and reverses the sense of the chains
, BackChain AS (
SELECT ID, RootID, ROW_NUMBER() OVER (PARTITION BY RootID ORDER BY Seq DESC) BackSeq
FROM RelationshipChain
)

-- Now we join each chain to the childest item in the reverse chain
SELECT
    rc.ID
    , rc.ParentID
    , rc.ChildID
    , rc.Seq
    , rc.RootID
    , bc.ID ChainEndID
FROM RelationshipChain rc INNER JOIN BackChain bc ON RC.RootID = bc.RootID AND bc.BackSeq = 1
ORDER BY RootID, Seq

Results with your sample data:

ID   ParentID ChildID Seq         RootID ChainEndID
---- -------- ------- ----------- ------ ----------
0001          0003    0           0001   0007
0003 0001     0005    1           0001   0007
0005 0003     0006    2           0001   0007
0006 0005     0007    3           0001   0007
0007 0006             4           0001   0007
0002          0004    0           0002   0004
0004 0002             1           0002   0004

Indexes will help performance.


How to read this output from MySQL? Query that is being ran:

INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES (?, ?, ?)

Error that is being thrown:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '41721' for key 1

Where does 41721 fall into? How do I read this? How do I check the 'key 1'?

Run this command : SHOW CREATE TABLE log_url\G

This will give you a display of the SQL needed to create the table.

It will also show you the indexes and constraints for that table.

The first index (key 1) in that table must have a column called url_id.

From the error message, you are being told that there exists a row in log_url that has 41721 already present. You can verify this with SELECT * FROM log_url WHERE url_id = 41721;


Design question about student assessment data I am designing a relational database in Filemaker Pro that will be tracking assessment data for students in after school tutoring programs. Depending on the student's grade level and skill set, each student may have a number of different assessments. Thus, there will be a many-to-many relationship between students and assessments.

What I am trying to determine is whether I should make a single assessment table (example header row: foreign key student ID, foreign key assessment ID, student score, date) or whether I should have a separate table for each assessment (example header row: foreign key student ID, student score, date).

The reason I might want a separate table for each assessment is that not every assessment will have the same descriptions, so I may not be able to create a single assessment_info table that encompasses all that info. For instance, some assessments will have a total possible score, whereas others will only have a pass/fail indicator.

Any advice for how to think about this design decision would be appreciated.

The first thing you want to do to keep your thinking straight is to choose some clear terminology.  You are using the word "assessment" in two different contexts: (i) a Test and (ii) the results for a student taking the test.  I understand that "test" may not be the best term, since you might be assessing based on lots of different kinds of work.  The important thing is that you need to clearly differentiate between a measuring instrument and the results of an individual student against that measuring instrument.

Keeping these concepts separate will help you to keep your thinking straight when designing your database.

I'd recommend that all of your RESULTS be kept in a single table.  That will make things like report cards easier to handle.  Whether the result is a percentage, a letter grade or a pass/fail, you probably want to have a common representation (percentage is a good lowest common denominator) and a weight to help you aggregate marks across assessments.


What will be the best mysql configuration for 12 core processor 128 gb ram ssd 160gb hdd What will be the best mysql configuration for 12 core processor, 128 GB ram sized 160gb HDD.
Mysqltuner.pl result!

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.71
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 79M (Tables: 124)
[--] Data in InnoDB tables: 5M (Tables: 339)
[!!] Total fragmented tables: 346

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 13h 37m 35s (94M q [1K qps], 9M conn, TX: 46B, RX: 22B)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 34.0M global + 2.8M per thread (1000 max threads)
[OK] Maximum possible memory usage: 2.7G (2% of installed RAM)
[OK] Slow queries: 0% (0/94M)
[OK] Highest usage of available connections: 34% (346/1000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/35.1M
[OK] Key buffer hit rate: 97.9% (1B cached / 38M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10M sorts)
[!!] Joins performed without indexes: 3360
[!!] Temporary tables created on disk: 49% (4M on disk / 9M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (214 open / 15M opened)
[OK] Open file limit used: 4% (225/5K)
[OK] Table locks acquired immediately: 95% (94M immediate / 99M locks)
[OK] InnoDB data size / buffer pool: 5.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

Check out http://tools.percona.com. It has a wizard where you type in your server's hardware configuration, and it recommends MySQL tuning parameters.

It also asks questions about your workload and size of database, and so on. You didn't consider these details in your question above, but they are important things to consider when tuning MySQL.


How to select mutiple values with like clause SELECT *
from table
WHERE column LIKE "AAA%"
   OR column LIKE "BBB%"
   OR column LIKE "CCC%"

I have more than 10 values to select, so it is not appropriate to write like the above example. Is there any way to select possible values in one sentence? I am using MySQL.

This case is a good candidate for using REGEXP instead of LIKE.

SELECT *
from table
WHERE column REGEXP '^(AAA|BBB|CCC)'

REGEXP is case insensitive. For case sensitive searches use REGEXP BINARY.

SELECT *
from table
WHERE column REGEXP BINARY '^(AAA|BBB|CCC)'

REGEXP can't benefit from indexes, so it may only be an efficient option if LIKE wouldn't use indexes either. With multiple LIKE statements that could individually access an index (like in your case if column is indexed and the like has a wildcard at the end), performing UNION of individual queries with a single LIKE would probably be most effective in a large dataset.

However, if your query filter primarily by, say, a range of dates, then a single REGEXP may be faster than multiple LIKEs ORed.

For more information about REGEXP in MySQL see this and this.


Multi-master (NoSQL) database solutions with cloud and no-premise option We are currently investigating database options for distributed database (possibly globally distributed) where the data in general is stored in "cloud" (this would not be a special requirement). The special requirement is that a parts of the database must be also available in local branches.
Data added in local branches should be replicated (delay is not a problem) to the cloud and changes made in the cloud should be replicated back to the related database in branches. So a multi-master setup.

Focus in on partition tolerance. It is possible that the local branches have from time to time no network connectivity or a slow network. So the database in the branches should be able to work by themselves and sync data and changes back.

We are long to solve this problem on the database layer. One database for cloud and local branches.

The only database that I came across to solve this problem is CouchDB with providing the "AP" of the CAP theorem.

Are there any database available (I checked options like Elasticsearch, Crate, ArangoDB etc.) that play in the same area ("AP")?

Requirement is that we must be able to install the database on-premise in local branches. For the "cloud" part I could image using a SaaS or do the hosting ourselves.

Options like Postgres or MySQL with related replication options are not of interest here.

You may want to consider Aerospike NoSQL database. You can install it on-premise and in the cloud yourselves. It has both AP and CP modes. It has Cross-Datacenter-Replication (XDR) which will replicate data from one cluster to another in asynchronous way. Network delays/disconnection are taken care of. However, XDR is an enterprise-only feature. Its not available in the free community version.


Can I use SNAPSHOT for large catalogs? I've been reading about Snapshots, and it says you can send small catalogs lets say, in a monthly routine.

I liked the Idea and i will start to study about this, but can I use snapshot for large databases?

Every week I need to backup development database  (2tb) and migrate it to test environment. It will be a good idea to make this via snapshot? Performance will not be testes, since it's development, we can wait a lot.

It seems like you've been reading about Snapshot Publications and replication. There are three items in SQL Server with the name snapshot. One is the database snapshot. When a database snapshot is created, one new file per existing data file is created and the database engine will save the original page for any changed page in the database into those files. Database snapshot files are just a collection of pages since the snapshot creation and won't be useful on any other server. Another type of snapshot is a Snapshot Publication, which is part of the replication system. When you create a Snapshot Publication, you specify which articles, which can be tables, views procedures,ect, to include in the publication, specify a subscriber and schedule it for delivery. You can configure it to drop the existing articles at the subscriber and recreate them so they will be replaced every time the snapshot is pushed. It will synchronize all articles between the publisher and subscriber at the time it is run. Which brings be to the third instance of an object named snapshot. Within the replication system there is an agent or executable named the snapshot agent, whose job is to take a snapshot of the articles in the publication at the time it is executed.

If you really need to transfer the entire database from one server to another on a regular basis, you can design a backup system that takes full backups once per week, for example, with differential backups daily and transaction log backups hourly for production. Then transfer the full backup once per week to the test environment and leave it there. After that you would only need to transfer the differential backup daily to restore using the full followed by the differential. I would also advise turning on backup compression to minimize the sizes of the files involved.


SQL Server 2008 R2 Database Diagram Error Code I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error message:

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

>String or binary data would be truncated.
>The statement has been terminated.
>The sp_creatediagram procedure attempted to return a status of `NULL`, which is not allowed. A status of 0 will be returned instead. (.Net SqlClient Data Provider)

Program Location: at Microsoft.SqlServer.Management.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)

I don't want to ALTER the database at all.

My goal is the save the diagram to references key relationships and other information in the future.

Does anyone know how I can enable saving the database diagram this way?

This can happen if your database was created in a previous version of SQL Server. You need to recreate the table [dbo].[sysdiagrams] or if you do not want to lose already existing diagrams try this:

ALTER table [dbo].[sysdiagrams]
DROP COLUMN [diagram_id]

GO

ALTER table [dbo].[sysdiagrams]
ADD [diagram_id] [int] NOT NULL IDENTITY (1, 1)


Only InnoDB databases locking up I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation.  At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up.

These sites are WordPress.  I converted one to Myisam and now when the lockups happen that one is still good, so in my mind this rules out site specific issues.

Any suggestions?

This is my my.cnf

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
query_cache_size=128M
tmp_table_size=32M
thread_cache_size=4
innodb_buffer_pool_size=400M
key_buffer_size=60M
max_heap_table_size=32M
tmp_table_size=64M
join_buffer_size=1M

InnoDB
InnoDB has to lock up because you do not have it configured properly.
You have to realize the framework behind the InnoDB
Here is a Pictorial Representation of InnoDB from Percona CTO Vadim Tkachenko

Looking at your my.cnf, there are many settings you do not have enabled or is underconfigured

innodb_buffer_pool_size : 400M is your setting right now. If the DB Server is dedicated to just the database, you need to scale this up to 60-75% of the installed RAM.
innodb_log_file_size : For MySQL 5.6 it's 48M. Before MySQL 5.6, it's 5M.
innodb_log_buffer_size : This is 8M by default. A larger log buffer makes for reduced I/O
innodb_read_io_threads and innodb_write_io_threads are both 4 by default (Percona Server's default is 8). Increase this for more CPU engagement (See my old post Possible to make MySQL use more than one core?)

The list can go on. The main point is that you cannot leave InnoDB in its default settings and expect good performance. You must configure InnoDB properly. Left unconfigured, oldver version of MySQL run better than newer versions (As an example, see my old post Why mysql 5.5 slower than 5.1 (linux,using mysqlslap))
You can read many posts in the DBA StackExchange from me (just another MySQL DBA) and a whole host of others MySQL and Percona Experts.
MyISAM
As regards MyISAM, you have key_buffer_size=60M. That's probably OK if just one Wordpress site is all MyISAM. If you really want to know how to set up caching for InnoDB and MyISAM, please read my old post What are the main differences between InnoDB and MyISAM?


Optimized Way of Scheduling a Differential Backup I am working with a data warehouse with SQL Server 2012 and was wondering what would be the most optimized, AUTOMATED procedure for a backup/restore strategy.

Current observations and limitations:

1) Cannot use transaction logs as it would affect my load performance - datasets are potentially huge with large transactions

2) Current plan is to do full backup every week and differential backup every day

I am not sure when DML operations will happen as it depends on my application's usage, but is there a way to just track the NUMBER of changes to a database that would trigger a differential backup? A way that would not affect performance? I do not want to be taking unnecessary differential backups.

Would Change tracking be a good solution for my scenario? Or would there be overhead involved? I do not need to know the actual data that was changed, just the fact that it was changed by a certain amount.

Thanks in advance!

First, this assumption is questionable:

  "Cannot use transaction logs as it would affect my load performance -
  datasets are potentially huge with large transactions"

When you do work in the database, your transactions are logged regardless. Full recovery mode just means your logged work sticks around until the next log backup. If non-logged operations are truly important to you, then you need to look at using minimally logged operations like bulk inserts. This is way harder than it sounds - check out the SQL 2008 data loading guide for more details. (For example, you may not be able to do it on a table that already has multiple nonclustered indexes and data in it.)

If it really is a good fit, though, check out this Serverfault post on estimating the size of your differential backup.


Key/Value table with limiters I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search.

I have a table storing key/value pairs.  There are also 2 limiting columns in the table, host and environment, both nullable. The way that the limiting tables work is that if there is an entry in the host column then the key/value applies to that host, similarly if there is an entry in the environment column then that entry takes effect only for that environment. If a key/value is sought with a host or environment then the limited values take precedent over the values where host=NULL or environment=NULL.  Furthermore, host takes precedence over environment.

So as an example, here are some table entries:

id      env     host     key    value
1       NULL    NULL     fruit  apple
2       prod    NULL     fruit  banana
3       prod    www      fruit  mango
4       test    NULL     fruit  peach
5       NULL    test     fruit  orange

Given that example data, what I'm after is a SQL statement (or statements) that will return these data based on these criteria:

env     host   key    data returned
NULL    NULL   fruit  1, fruit, apple
prod    NULL   fruit  2, fruit, banana
NULL    www    fruit  3, fruit, mango
junk    junk   fruit  1, fruit, apple  // NULL/NULL values are used as a fallback
junk    www    fruit  1, fruit, apple  // no match, fallback
prod    test   fruit  5, fruit, orange // host=test takes precedence

Does that make sense?

I can break the search up into multiple selects, try each one, check to see if I get a hit, etc, but I'm hoping to achieve this as efficiently as possible.

Ideally I would like the type of search that would be easily extensible if more limiters are added later (e.g. group, namespace, etc).

Anyone?

So you're going to pass in two optional parameters:

@host
@env

Example sql (to be put in a stored proc):

Select top 1 * from dbo.FilterTable ft
Where
    ((@host is not null and ft.host = @host) or ft.host is null)
    And ((@env is not null and ft.environment = @env) or ft.environment is null)
Order by
    Case
        When ft.host is not null and ft.environment is not null then 1
        When ft.host is not null then 2
        When ft.environment is not null then 3
        Else 4
    End asc


Where clause for aliased column I have a query which parses a string values from a column:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1

When I do:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1.timesent != '0'

I get:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'timesent'.

Any ideas why?

You can't reference an alias in the WHERE clause.  You'd have to do something like this:

select left(DateTimeSent, 17) as timesent
from allog_07_11_e
where left(DateTimeSent, 17) <> '0'

Likewise, you could also use a subquery:

select *
from
(
    select left(DateTimeSent, 17) as timesent
    from allog_07_11_e
) a
where timesent <> '0'


Problem joining two tables on date field? im using following query with PostgreSQL 9.1.13:

SELECT
    day::DATE AS date,
    COUNT(s.id) AS sale_count,
    COUNT(c.id) AS claim_count

FROM
    GENERATE_SERIES('2017-08-10', '2017-08-13', INTERVAL '1 day') day
        LEFT JOIN sale_order s ON s.date_confirm = day
        LEFT JOIN crm_claim c ON c.create_date = day

GROUP BY
    day.day

ORDER BY
    day.day

Expected Output:

date|sale_count|claim_count
---------------------------
2017-08-10|31|17
2017-08-11|32|23
2017-08-12|33|3
2017-08-13|21|0

Actual Output:

date|sale_count|claim_count
---------------------------
2017-08-10|527|527
2017-08-11|384|384
2017-08-12|99|99
2017-08-13|21|0

What am I doing wrong?

You got multiple rows with the same date in both tables (e.g. 2017-08-10 31 & 17) and you join every row in the 1st table with that date to  every row in the 2nd table with the same date, which results in 31 * 17 = 527 rows.

A quick solution is adding DISTINCT:

SELECT
    day::DATE AS date,
    COUNT(s.id) AS sale_count,
    COUNT(c.id) AS claim_count

but this is very inefficient, because it creates those 527 rows first and then applies DISTINCT.

Better count both tables seperatly before the join using Derived Tables:

SELECT
    day::DATE AS date,
    COALESCE(sale_count, 0),
    COALESCE(claim_count, 0)

FROM
    GENERATE_SERIES('2017-08-10', '2017-08-13', INTERVAL '1 day') day
        LEFT JOIN
         ( SELECT date_confirm, COUNT(*) AS sale_count
           FROM sale_order
           WHERE date_confirm BETWEEN '2017-08-10' AND '2017-08-13'
           GROUP BY date_confirm
         ) AS s
        ON s.date_confirm = day
        LEFT JOIN
         ( SELECT create_date, COUNT(*) AS claim_count
           FROM crm_claim
           WHERE create_date BETWEEN '2017-08-10' AND '2017-08-13'
           GROUP BY create_date
         ) AS c
        ON c.create_date = day

ORDER BY
    day.day

I added the filter on the date range for each table to remove unneeded data before joining.


What benefit is there from enabling Query Store on msdb? Of the SQL system database (master, model, msdb, tempdb) query store can only be used on msdb. I looked and don't find any documentation about query store on msdb.

While you can't see it in the GUI, it can be validated on your SQL 2016 instance

Validate Query Store is off

USE msdb
SELECT * FROM sys.database_query_store_options;

Turn Query Store on

USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = ON
GO
ALTER DATABASE msdb SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
GO

Validate Query Store is on

USE msdb
SELECT * FROM sys.database_query_store_options;

Of all the system database why is msdb the only one with the option to use Query Store, and what value does it add?

-- Stop Query Store
USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = OFF
GO

Microsoft enabling a feature does not mean it will be useful for everyone. For systems using some of the features can mean relying on information stored in MSDB.  In those cases Query Store can be useful.

Here are few articles about the usage and tuning  of MSDB database objects.

msdb Database from books online.

MSDB Performance Tuning by Geoff N. Hiten

The Importance of Maintenance on MSDB by Tim Radney where he mentioned following:

  Optimizing indexes in msdb is just as important as your user
  databases. Many times I have found clients who are optimizing user
  databases but not the system databases. Since the msdb database is
  heavily used by SQL Server Agent, Log Shipping, Service Broker, SSIS,
  backup and restore, and other processes, the indexes can get highly
  fragmented. Ensure that your index optimization jobs also include your
  system databases, or at least msdb. I’ve seen index optimizations free
  up several gigabytes of space from highly fragmented indexes within
  msdb.

I can see how query store can help in optimizing your indexing strategy and optimally querying/aggrgating/purging some of the information stored in MSDB.


Postgres consumes a lot of RAM (cached) I have a small database PostgreSQL (v9.3) on Centos 6 x64 ( RAM: 8 GB ).

postgresql.conf

max_connections = 512
shared_buffers = 3000MB
temp_buffers = 8MB
work_mem = 2MB
maintenance_work_mem = 128MB
effective_cache_size = 3000MB

About 150 connections, PostgreSQL takes more than 6 GB of RAM (of course, other applications use about 200 MB of RAM), here my info:

Mem:  7062.945M total, 6892.410M used,  170.535M free, 6644.000k buffers
Swap:    0.000k total,    0.000k used,    0.000k free, 5378.922M cached

Questions:

Why does PG takes a lot of RAM ?
How can I reduce cached buffers of PG ?

PostgreSQL isn't using lots of RAM, the kernel is using it for disk cache.

This is exactly what you want to happen. There is nothing to fix here.

"Free" RAM is wasted RAM. The best thing for a database server is for as much RAM as possible to be used as disk cache.


Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY? In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE  index on all fields except for the PRIMARY KEY.

The SHOW CREATE TABLE:

CREATE TABLE `zdb_userbeschikbaarheid` (
  `UserBeschikbaarheid_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `UB_User_ID` int(10) unsigned NOT NULL,
  `UB_PlanDagdeelTaak_ID` int(10) unsigned NOT NULL,
  `UB_Datum` date NOT NULL,
  PRIMARY KEY (`UserBeschikbaarheid_ID`),
  UNIQUE KEY `UQ_UB_User_ID_PlanDagdeelTaak_ID_Datum` (`UB_Datum`,`UB_User_ID`,`UB_PlanDagdeelTaak_ID`),
  KEY `FK_UB_User_ID` (`UB_User_ID`),
  KEY `FK_UB_PlanDagdeelTaak_ID` (`UB_PlanDagdeelTaak_ID`),
  CONSTRAINT `FK_UB_PlanDagdeelTaak_ID` FOREIGN KEY (`UB_PlanDagdeelTaak_ID`) REFERENCES `zdb_plandagdeeltaak` (`PlanDagdeelTaak_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_UB_User_ID` FOREIGN KEY (`UB_User_ID`) REFERENCES `zdb_user` (`User_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=522 DEFAULT CHARSET=utf8

For some weird reason when I select the full table it's using the UNIQUE index instead of the PRIMARY KEY. An EXPLAIN SELECT * FROM zdb_userbeschikbaarheid; yields:

id  select_type table                   type    possible_keys
1   SIMPLE      zdb_userbeschikbaarheid index   \N

key                                     key_len ref rows    Extra
UQ_UB_User_ID_PlanDagdeelTaak_ID_Datum  11      \N  415     Using index

It will not use the PRIMARY KEY until I specifically tell it to ORDER BY the primary key.
EXPLAIN SELECT * FROM zdb_userbeschikbaarheid ORDER BY UserBeschikbaarheid_ID;

id  select_type table                   type    possible_keys
1   SIMPLE      zdb_userbeschikbaarheid index   \N

key     key_len ref rows    Extra
PRIMARY 4       \N  415     \N

It just seems odd that the UNIQUE KEY is chosen instead of the PRIMARY KEY.

What's causing this behavior, is it a potential problem for my database? And if possible, how can I change it to use the PRIMARY KEY by default?

With InnoDB tables, all secondary indexes include the columns of the clustered index (which is the primary key), appended in the end. So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. In fact, as explained in InnoDB Table and Index Structures, the clustered index contains some additional information that costs 13 more bytes per row:

  Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

This makes your unique index less wide than the primary key index, which is why it is chosen by the optimizer.


Disable auto-start of postgres server on boot (Mac) Running a Mac.  I installed Postgresql 9 many years ago and configured it to auto-start.  Now I have upgraded to version 12 using brew, but on system restart version 9 server is still starting automatically.  I don't know how to determine what is starting it, I don't see anything in my bash_profile and I don't see a feature in brew or pg_ctl that allows me to disable an auto-start configuration.  I have no postgres files in /Library/LaunchAgents either.  Thanks

In answer to Prevent PostgreSQL from running at startup on Super User:
ithinkiknowruby wrote:

cd /Library/LaunchDaemons/
sudo rm com.edb.launchd.postgresql-9.0.plist


Execution plan using B+ tree index, but also sorts I'm using Oracle sqlplus. And I have the following query:

SELECT fooID from foo MINUS
SELECT fooID from bar;

I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo and one in the field fooID of the table bar. After that I analyze the statistics of both my tables: foo and bar and check the execution plan of my query using EXPLAIN PLAN .... But I get this:

  SELECT STATEMENT
      MINUS
        SORT UNIQUE
          INDEX FAST FULL SCAN FOO_INDEX
        SORT UNIQUE
          INDEX FAST FULL SCAN BAR_INDEX

How is that even possible? When doing INDEX FAST FULL SCAN, because the index is a B+ tree, doesn't the system gets back it's tuples sorted? Why does it need to do SORT UNIQUE (data already sorted)?

Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order.

Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order. (Emphasis mine)

Now, probably, the question should be: why did the optimzier choose INDEX FAST FULL SCAN over INDEX FULL SCAN.

A hint to the answer of the latter question is given in  under 11.2.3.7 Fast Full Index Scans:  A fast full scan is faster than a normal full index scan because it can use multiblock I/O and can run in parallel just like a table scan.

If you insist that Oracle use a full index scan, you might want to try the /*+ index() */ hint:

create table tq84_foo (
  fooID number not null
);

create table tq84_bar (
  fooID number not null
);

create unique index ix_foo on tq84_foo(fooID);
create unique index ix_bar on tq84_bar(fooID);

explain plan for
 select /*+ index(f ix_foo) */fooID from tq84_foo f
        MINUS
select /*+ index(b ix_bar) */ fooID from tq84_bar b;

select * from table(dbms_xplan.display);

resulting in

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    26 |     4  (75)| 00:00:01 |
|   1 |  MINUS              |        |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|        |     1 |    13 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | IX_FOO |     1 |    13 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|        |     1 |    13 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | IX_BAR |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Getting data from 2 tables by JOIN, when @Foreign Keys exist to the same Table/Object I have 2 Tables:

User

id | name | email  | staff
1    name1  email1   true
2    name2  email2   no
3    name3  email3   true
4    name4  email4   no

Note

id | text | sender_id | receiver_id
1    text1    1            2
2    text2    1            3
3    text3    1            2
4    text4    3            2

Note has 2 Foreign Keys to the User, sender_id and receiver_id.

I want to get in a Select(one if possible) the data (name, email, staff) of both the sender and the receiver/.

I tried :

  SELECT N.text, U.name, U.email from Note as N
    LEFT JOIN User as U on Note.sender_id = U.id where U.staff is True ...

but this way I'm getting the sender only.

You must use the User table twice:

SELECT N.text, S.name, S.email, R.name, R.email
FROM   Note as N
   LEFT JOIN User as S on N.sender_id = S.id where S.staff is True
   LEFT JOIN User as R on N.sender_id = R.id where R.staff is True


In Postgresql, after pg_dump the access privileges on the database itself are not restored Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee

gee=> \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 gee       | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/foo              +
           |          |          |             |             | foo=CTc/foo          +
           |          |          |             |             | bar=CTc/foo

We dump this database,

$ pg_dump -U foo -v -f gee.dump gee

In database gee user bar has created a schema. So because all of these users we restore database gee as user postgres (after user foo has created a database goo),

$ sudo -u postgres psql -d goo -f gee.dump

User bar does not have the access privileges on the database goo. But inside database goo every privilege is OK, for all users, on schemas and tables.

goo=> \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 goo       | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Why are the access privileges on the database itself not restored?

Why are the access privileges on the database itself not restored?

It's a bug, or a design oversight. Though the responder to that report doesn't think so.

pg_dumpall --globals-only doesn't dump rights on the database. Neither does pg_dump as part of the database dump.

So grants on databases only get included in a full pg_dumpall.

I'll make some noise about it by poking this thread.


Tablespace and userid info I have to create some scripts to create a new and empty DB ready for a new install of our apps on a new server.

I am easily getting the tables, views etc info from SQL Developers Export facility (without the data of course), but I have a problem where I need to define the userid and the TableSpace.

I realise these are quite straight forward, but what I would like to know is how the original ones are defined on our current DB, as the original scripts (and info) are no longer available, and I need to make sure I create them correctly with the new scripts.

So how do I display all the required info for a userid and for a TableSpace so that I can use this info in the create commands in the scripts ?

It may be helpful for you to know this is my first attempt at scripting and only my 2nd attempt at and interfacing with Oracle, so I have limited knowledge in this field, hence the simple request. :)

Any help or advice would be most appreciated.

Cheers, George.

Have a look at the DBMS_METADATA package. First set long to some big number so the output isn't truncated, and set the SQL terminator so you can see how statements are separated:

SQL> set long 30000
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

To get the tablespace (substitute your own for USERS of course):

SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
   ALTER DATABASE DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/users01.dbf' RESIZE 256901120;

You'll probably need to modify the datafile path for the new server. To get the user:

SQL> select dbms_metadata.get_ddl('USER', 'SCOTT') from dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:64F10A7102E73EFFAC1BE155794F1E8DA6C8F948A5713D5D5EC462AA0382;4DD35D67F0372586'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

Then you need the grants the user has. Depending on your actual user some of these may not return anything:

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------

  GRANT CHANGE NOTIFICATION TO "SCOTT";
  GRANT DROP ANY DIRECTORY TO "SCOTT";
  GRANT CREATE ANY DIRECTORY TO "SCOTT";
  GRANT CREATE DATABASE LINK TO "SCOTT";
  GRANT CREATE VIEW TO "SCOTT";
  GRANT CREATE SYNONYM TO "SCOTT";
  GRANT CREATE TABLE TO "SCOTT";
  GRANT UNLIMITED TABLESPACE TO "SCOTT";
  GRANT ALTER SESSION TO "SCOTT";

SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
--------------------------------------------------------------------------------

   GRANT "CONNECT" TO "SCOTT";
   GRANT "RESOURCE" TO "SCOTT";
   GRANT "XFILES_USER" TO "SCOTT";

SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
--------------------------------------------------------------------------------

  GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "SCOTT";
  GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "SCOTT";
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "SO_DIR" TO "SCOTT";

You can use the same functions to get your table and view scripts if you prefer - I believe SQL Developer's export uses them under the hood anyway - and to get role definitions and grants.


IO thread error : 1595 (Relay log write failure: could not queue event from master) Slave status :

           Last_IO_Errno: 1595
           Last_IO_Error: Relay log write failure: could not queue event from master
           Last_SQL_Errno: 0

from error log :

       [ERROR] Slave I/O for channel 'db12': Unexpected master's heartbeat data: heartbeat is not compatible with local info; the event's data: log_file_name toku10-bin.000063<D1> log_pos 97223067, Error_code: 1623
       [ERROR] Slave I/O for channel 'db12': Relay log write failure: could not queue event from master, Error_code: 1595

  I tried to restarting the slave_io thread for many times, still its same.

we need to keep on start io_thread whenever it stopped manually, hope its bug from percona

I have simply written shell and scheduled the same  for every 10mins to check if io_thread is not running , start slave io_thread for channel 'db12';.   It's working as of now


Install of SQL Server 2014 SE The SQL Sever 2014 installation our company has is of SQL Server 2014 SE. I need to install the latest version of SQL Server on there.

I understand that I can install 2014 SE and then apply updates to bring it to SQL Server 2014 SP2 CU7, but I was wondering if there is a way to directly install it to the latest version using the installer?

You could look into slip streaming the installation.

In order to slipstream the updates:

Download the necessary updates and store them in a location that you have access to (local or UNC path is fine).
Recent updates are just the .exe file; older CUs were compressed, so if necessary extract the files
open cmd
change directory to the root of the installation media
start setup.exe from cmd spcifying the necessary switches

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="updates path"

If this is something that you are likely to do regularly, it is possible to re-package the iso with these updates in.

Extract ISO image using a utility like 7zip or mount and copy to a directory
Create an updates folder on in the root of the extracted media
Copy in the required SP/CU
Recreate the ISO image using any iso image creator such as MagicISO

To use the slipstreamed files, open cmd and run similar to before:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=".\updates"

For a detailed walk through with screenshots see here


Why is @temp table performance some times worse than #temp table performance? I was recently working in a very slow stored procedure (took 5 minutes to run).  I made a very small tweak from doing this:

declare @tempTable table
(
  ...
)
insert into @tempTable
select .....

to

select ... into #tempTable from someTable

The script then ran in ~2 seconds.  What can explain this time difference?

Table Variables don't have statisics in the same way as Temp Tables normally they're assumed to have only 1 row.  This incorrect estimate of rowcount will make a nested loop operation look like the best plan but when this is done for a larger amount of rows the cost can easier be greater than a table scan.


Red Gate comparison not working after upgrading SQL Server 2016 I am using SQL Compare Red Gate Red Gate to make comparisons between 2 DBs on 2 different SQL Servers.

For this, I have  created a SSIS package which encompasses an Execute Process Task. I have passed following as an Executable:

C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe

and as an argument:

sqlcompare /Server1:\"server_ip\" /Database1:\"db_name\" /Username1:\"user_id\"
  /Password1:\"password\" /MakeSnapshot:\"E:\Folder\AUDITserver1.snp\"

This Execute Process Task is to create a .snp file of server 1 and to compare it with server 2. This used to work fine before. However, recently, I have upgraded SQL Server 1 from 2012 to 2016.

I have also re-created a linked server connection for Server 1 on the server where the comparison package is being run. However, I am getting an error. The description is:

  [Execute Process Task] Error: In Executing "C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" "
  sqlcompare /Server1:\"server_ip\" /Database1:\"db_name\" /Username1:\"user_id\" /Password1:\"password\" /MakeSnapshot:\"E:\Folder\AUDITserver1.snp"" at "",
  The process exit code was "70"
  while the expected was "0".

Please help.

I tried running it via command prompt as follows:

cd C:\Program Files (x86)\Red Gate\SQL Compare 10

sqlcompare /Server1:\"server_ip\" /Database1:\"db_name\" /Username1:\"user_id\"
  /Password1:\"password\" /MakeSnapshot:\"E:\Folder\AUDITserver1.snp\"
  /Options:CaseSensitiveObjectDefinition

And now facing an error as follows:

  Unexpected error: A duplicate object name has been found.
  This may occur if the SQL Server that you are registering is case sensitive but the case sensitive option is not set.
  RedGate.SQLCompare.Engine.SqlCompareException
     at #mWo.#KYo.#0cp(DBPopulationOptions #YBCc)
     at #mWo.#HPud.#crc(ConnectionProperties #x1lc, #en #LfCc)
     at RedGate.SQLCompare.Engine.Database.#crc(ConnectionProperties #Ap8b, #en #5
  OPb)
     at #namb.#n9G.#W8G(DataSource #ovUb, #en #5OPb, Boolean #q9rc)
     at #i9G.#n9G.#W8G(DataSource #ovUb, #en #5OPb, Boolean #q9rc)
     at #i9G.#n9G.#1tD(String #p9rc, DataSource #ovUb, #en #5OPb, Boolean #q9rc)
     at RedGate.SQLCompare.CommandLine.CommandProcessor.#lMPb()
     at RedGate.SQLCompare.CommandLine.CommandProcessor.#kcmb()
     at RedGate.SQLToolsCommandLine.CommandProcessor.#OUb()
     at #i9G.#OsPb.#6sc(String[] #ead)
  Caused by:
  Index was out of range. Must be non-negative and less than the size of the colle
  ction.
  Parameter name: index
  System.ArgumentOutOfRangeException
     at System.Collections.BitArray.Set(Int32 index, Boolean value)
     at #8rg.#5sg.set_Item(DatabaseLevelPermissionAction #pYrc, PermissionType #iT
  c)
     at #mWo.#KYo.#MCCc()
     at #mWo.#KYo.#ICCc()
     at #mWo.#KYo.#0cp(DBPopulationOptions #YBCc)

If this actually is SQL Compare 10.7 or earlier, I believe it may not work with SQL Server 2016.
https://documentation.red-gate.com/display/SC10/SQL+Compare+10.7+release+notes


Extra "count" row with total of 0 Consider the following:

db=> SELECT ga, count(ga) FROM gb GROUP BY ga ORDER BY ga ;

which gives the following output:

ga | count
---+------
?  |  200
A  |  100
B  |   50
   |    0
(4 rows)

Why is there an additional row with an empty ga and a count of zero?

Why is there an additional row with an empty ga and a count of zero?

Because there are NULL values in the ga column.

  Why the count is zero?

While COUNT(*) can never give 0 as a result (as it counts rows), COUNT(column) or COUNT(expression) can give 0 because it counts not null values in the column/expression.

Thus the only logical explanantion is that the ga column has some nulls.

  How many?

You can find out by running:

SELECT ga,
       count(ga) AS count_non_nulls,
       count(*)  AS count_all
FROM gb
GROUP BY ga
ORDER BY ga ;

which will give you something like:

ga | count_non_nulls | count_all
---+-----------------+-----------
?  |  200            |  200
A  |  100            |  100
B  |   50            |   50
   |    0            |   17  -- some number >= 1
(4 rows)


How does unique key help to improve SQL query performance? Suppose I have a table with an id column and and I am maintaining unique values manually while inserting and updating records instead of creating a unique key on that column (e.g. by using a date & time for the id column value, such as 201505061648). I am not using any index or key in my table. So, does it affect query performance when I execute a select query with a where clause even if the cardinality is 100% for the id column?

select * from myTable where id=15

I mean does a unique constraint help to improve query performance or does it just force the user to maintain the record uniquely, so that SQL will get higher selectivity on the unique constraint column?

I don't know much about unique constraint and unique index. The answer given on this question helps in understanding the difference between UniqueIndex and UniqueKey. I want to know more about unique key and how it can improve my query performance.

Since the Stack Overflow post you linked to explains how the unique key constraint is implemented using a unique index, so the two are the same for practical purposes, I'll skip that part and try to explain how a unique index improves query performance.

Let's stick to your example where table named myTable has a column named id which happens to be filled with unique values, but no unique index on that column. Let's also suppose that the table has another column, anotherCol.

Now someone comes and issues a query like

select * from myTable where id = 15

The optimizer must decide how to find all the rows satisfying the condition id = 15. It may know, from the statistics, that values in the column are unique (num distinct = num rows in the table), however, that doesn't provide any information on where the row with id = 15 is physically located on the disk. So, the database has no choice but to scan the entire table to find the matching row. It can't even stop once it finds the first row with id = 15, since there's no guarantee that there aren't more such rows.

Now, we create a unique index on column id, and repeat the same query.

Things are radically different now. The optimizer now knows that there are only two possibilities: either there is exactly one row that satisfies the condition, or there are no such rows. So, a cheap index seek is all that is needed to find the required row if it exists; the same index seek will return no results if there are no such rows. So, the query will run fast.

Suppose there is also a (normal, non-unique) index on anotherCol, and you issue a query like

select * from myTable where id = 15 and anotherCol = 100

The optimizer can now choose between using the index on id, index on anotherCol, and a table scan. It will choose the most selective method, because it requires the least work to get to the results - so it will use the unique index, again.

In general, whenever your query has where clause predicates that are joined with AND (a common case) and any of the columns in the where clause has a unique index on it, the optimizer will chose that index as the access method and the query will run fast.

The only case when the unique index will not be  used is when it cannot be used, for example when a query like

select * from myTable where id = 15 or val = 100

is issued. Since the condition between the predicates is OR, the fact that we can find the row where id = 15 fast doesn't mean much since we still have to find other rows where val = 100. If val were not indexed, a table scan would be the only solution (rendering unique index seek useless, as the row where id = 15 would be picked up by the table scan anyway); since val is indexed, the optimizer might opt to use both indexes to find rows satisfying each condition individually and then concatenate the results.

Hopefully this makes things a bit clearer to you.


IN Clause - listing values VS query My app runs a lot of queries like this

SELECT ... FROM Table
    WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..)

To save performance I decided to cache the second subquery in the application's memory (it rarely changes) and change the DB-query to

SELECT ... FROM Table
    WHERE CategoryID IN (123,234,345,456,567,678,789,...)

Question: does this make any sense?

PROS: it caches the data which is a good thing. Right?

CONS: the second query cannot be parameterized, so the execution plan is not compiled/cached by the SQL server. Also, the IN clause is treated like a dozen OR's, can this degrade performance?

CategoryID field  in Table is an integer with an index.

PS. I tried testing, but I cannot get my tests to the scale of my app (that runs 1000s of queries) so I'd sppreciate any "generic" explanation from someone... Thanks!

A lot of this depends on what else your query is doing. If that predicate is one of many, and isn't particularly useful for an index, then you may be seeing that predicate in a Predicate property (being a residual predicate) in your plan.

Alternatively, if you have an index on CategoryID, then are you wanting it to do dozens of Seeks into Table?

I suspect that the cost of the sub-query, especially if it's run frequently so that it's in memory already, would be negligible if you're doing dozens of Seeks. But without an index on CategoryID, you might be better off with just a residual, by using your IN clause. If you have a maximum number of values that could be used, you could parameterise it, like: (@p1, @p2, ...). To get the same number of parameters each time would be important. If you always have between 10 and 15, then maybe code up for 15, and use -1 as the value for any extras (ie, if you have 11 values, then 12-15 could be all -1 to avoid recompilation).


Need help to understand tuning up a slow SQL server query For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [Id]
      ,[AboutMe]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[WebsiteUrl]
      ,[AccountId]
  FROM [StackOverflow2010].[dbo].[Users]
  WHERE DisplayName IN (

 SELECT DisplayName from dbo.Users
 WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)
 AND CreationDate>= DATEADD (DAY, -30,LastAccessDate)
 AND CreationDate<= LastAccessDate)

In stackoverflow database this does not return any rows but for our existing 6 TBS database its really slow.
CreationDate and LastAccessdate columns are both datetime (10)
and DisplayName is Varchar(50)
Please suggest how can I improve performance if above can be re-written

You can improve things greatly without rewriting the query by just adding indexes, from what I can tell.
Here's the execution plan I get on SQL Server 2019 (note that I changed the date to '20090814' just so it would return some results):

Table 'Users'. Scan count 18, logical reads 15430

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 94 ms.

This scans the entire Users table once to get the users that meet the date criteria, and then several more times to get the rest of the users that have matching display names.
Not ideal.
The query only takes 94 ms on my machine, mostly because everything is in RAM and the query is running at degrees of parallelism (DOP) 8.
However, adding these two indexes helps the situation a lot:
CREATE NONCLUSTERED INDEX IX_LastAccessDate
ON dbo.Users (LastAccessDate)
INCLUDE (CreationDate, DisplayName);

CREATE NONCLUSTERED INDEX IX_DisplayName
ON dbo.Users (DisplayName);

Now I get this execution plan:

Table 'Users'. Scan count 15, logical reads 153

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server is able to produce a seek plan here by using the internal GetRangeThroughConvert function to determine the range of possible datetime values that are equivalent to CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE).  Essentially, it rewrites the query in the background to match the suggestion made by Mo64.
Note: you are probably better off rewriting to explicitly use that range query, rather than depending on the hidden implicit conversion
You can read more details about this sort of execution plan on Paul White's blog: Dynamic Seeks and Hidden Implicit Conversions
The situation could be improved even further if you eliminate the key lookups - either by selecting less columns, or including all the necessary columns in the first index (on LastAccessDate).

I realize this is kind of a toy example, but hopefully it illustrates the general solution that can be applied to your real situation:

Add an index that will allow SQL Server to jump to the right date in the subquery
potentially rewrite the CAST from datetime to date to be two inequality conditions.  In other words, replaced this:

WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)

With this:
WHERE LastAccessDate >= '20160814' AND LastAccessDate < '20160815'

Add an index that will allow SQL Server to jump to the matching varchar(50) column values


How to update mysql 5.7 to 8.0 Amazon linux 1, ec2 I'm using MySQL 8.0.14 for my project and I want to use it for my AWS ec2 server.
I updated from 5.5 to 5.7 by following this link: Upgrade MySQL to 5.6 on ec2
But don't have any information to update to MySQL 8.0

Please check this.
https://mysqlserverteam.com/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/
it might help you................


Normalization of library book table with multiple authors I have a database structure with two tables:

The first one is called Author, has two columns (AuthID, AuthName) and its Primary Key is (PK_AuthID).
The second one is called Book and has three columns (BookID, BookTitle, AuthID). Its Primary Key is (PK_BookID), and has (FK_AuthID) defined as a Foreign Key that referes to Author table.

The question is: If one Book has more than one Author, what is the best design pattern to follow in order to maintain the second Author, and at the same time keep the Book table without NULL values?

The only sensible design I can think of - since books can have 20 authors - is to always use a junction table and don't bother storing the AuthorID in the book table some of the time (and don't even think about storing a comma-separated list of AuthorIDs, please, or adding Author2, Author3, ... columns). This will just make queries complex.

BookAuthors(BookID (FK points to Book), AuthorID (FK points to Author))

In this case, you can remove the AuthID column from the Book table. Don't bother abbreviating to AuthID - you don't really think that makes you more productive or makes the code easier to read, do you?

Also, as a general rule, don't spend time trying to worry about how you will eliminate NULLs everywhere.


Monitor and alerting tool for specific queries I need to monitor and alert a MySQL DB which is hosted on AWS. I'd like an alert to trigger if certain SQL query criteria aren't met (e.g. SELECT * FROM my_table returns less than 100 results).

What open-source tools are available for this type of monitoring? Most I've come across tend to monitor low level MySQL DB stats but I'd like specific queries to trigger alerts. I've come across Prometheus but was wondering if there others I could look into?

Nagios is an open source solution for Unix/Linux based systems.  It operates by scripts so it can do what you are requesting and more.  You can write your own script tailored to your specific alert (and you can set the warning and critical criteria).

Here is a link to MySQL specific plugins freely available for your use:
https://exchange.nagios.org/directory/Plugins/Databases/MySQL

And a link to other similar opensource alerting products:
https://serverfault.com/questions/83963/alternatives-to-nagios


How to get the second string in the ORA-02296 error to show something meaningful Whenever I run the following script*:
ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL;

I receive the following error:

ORA-02296: cannot enable (ANIMAL.) - null values found

I have a script that modifies a large number of tables.  It would be really helpful if the error message read:

ORA-02296: cannot enable (ANIMAL.MONKEY) - null values found

... or something else that identifies either the problem column or the table in which it resides.
ORA-02296 has the following detail about it:

ORA-02296 cannot enable (string.string) - null values found
Cause:    Obvious
Action:   an alter table enable constraint failed because the table contains values that do not satisfy the constraint.

This suggests there is a second string available somehow.  How do I get the error to display more meaningful information?
*names have been changed to protect the innocent.

Provide a name for the constraint:

SQL> create table monkey(tail_count number);

Table created.

SQL> insert into monkey values (null);

1 row created.

SQL> alter table monkey modify tail_count not null;
alter table monkey modify tail_count not null
*
ERROR at line 1:
ORA-02296: cannot enable (ANIMAL.) - null values found

SQL> alter table monkey modify tail_count constraint tail_count_not_null not null;
alter table monkey modify tail_count constraint tail_count_not_null not null
                                                *
ERROR at line 1:
ORA-02296: cannot enable (ANIMAL.TAIL_COUNT_NOT_NULL) - null values found


SSRS Report drop down parameters. Use SP or embeded SQL query for datasource? When I create SSRS reports I create stored procedures to bring data into my report. When I create the report parameters I just embed SQL code directly in to the parameter/SSRS report.

Should I also create stored procedures for my parameter data sources?

It seems like a bit of overkill to create a SP for every single paramamter/datasource in a report but I guess if you want to be able to modify the SQL code the parameter uses to load the drop down values without having to open the actual report, then I would probably want to go with SPs for everything....

Apparently there is not a set (or recommended) standard (that I could find). Since development needs vary from project to project the developer should decide during the development process, whether to create stored procedures for their parameter data sources or to simply hard code the drop down values inside the report itself.


Group by with a break on date I am trying to group data on homogenous periods of time (not sure this is clear).
Here is a simplified example of my data:
CREATE TABLE workhours_over_time (
             worker varchar,
             workhours integer,
             otherinfotobeignored varchar,
             startofperiod date,
             endofperiod date);

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any info', '2020-01-01','2020-01-31');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any other info', '2020-02-01','2020-02-28');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '39', 'any info', '2020-03-01','2020-04-15');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any info', '2020-04-16','2111-11-11');

So here is the result:
worker|workhours|otherinfotobeignored|startofperiod|endofperiod|
------|---------|--------------------|-------------|-----------|
W1    |       35|any info            |   2020-01-01| 2020-01-31|
W1    |       35|any other info      |   2020-02-01| 2020-02-28|
W1    |       39|any info            |   2020-03-01| 2020-04-15|
W1    |       35|any info            |   2020-04-16| 2111-11-11|

The result I need to obtain would group data in 3 periods: from 2020-01-01 to 2020-02-28, worker W1 works 35 hours a week. Then from 2020-03-01 to 2020-04-15 W1 works 39 hours a week. Then from 2020-04-16 to end of time, back to 35 hours.
worker|workhours|startofperiod|endofperiod|
------|---------|-------------|-----------|
W1    |       35|   2020-01-01| 2020-02-28|
W1    |       39|   2020-03-01| 2020-04-15|
W1    |       35|   2020-04-16| 2111-11-11|

I had first naive try with a simple group by:
select worker, workhours, min(startofperiod), max(endofperiod)
from workhours_over_time
group by worker, workhours

but of course, first and third period where grouped into a 2020-01-01 to 2111-11-11 period, which is not what I want.
Then I have toyed with window function, read on groups and island, but didn't find the solution...
Any idea ?

This is probably the most straightforward way to do this (edited to handle gaps):
SELECT
  Worker
 ,WorkHours
 ,startofperiod
 ,LEAD(priorend,1,'2111-11-11') OVER (Partition By Worker ORDER BY startofperiod) AS endofperiod
FROM
  (
    SELECT
      Worker
     ,startofperiod
     ,endofperiod
     ,LAG(endofperiod) OVER (PARTITION BY Worker ORDER BY startofperiod) AS priorend --Get prior end date in case of gap
     ,WorkHours
     ,CASE
        WHEN WorkHours <> LAG(WorkHours,1,-1) OVER (PARTITION BY Worker ORDER BY startofperiod) THEN 'Y'  --If hours change
        WHEN LAG(endofperiod + Interval '1 DAY') OVER (PARTITION BY Worker ORDER BY startofperiod) <> startofperiod THEN 'Y'  --If gap
        ELSE 'N'
      END AS Change_Ind
    FROM
      workhours_over_time
  ) summary
WHERE
  Change_Ind = 'Y'

Returns:
Worker  |   WorkHours   |   startofperiod   |   endofperiod
W1      |   35          |   2020-01-01      |   2020-02-29
W1      |   39          |   2020-03-01      |   2020-04-15
W1      |   35          |   2020-04-16      |   2111-11-11

This will handle gaps as long as your intervals are correct (in your example they are not as 2020 is a leap year).  You could change the logic to LAG(endofperiod + Interval '2 DAYS') OVER (PARTITION BY Worker ORDER BY startofperiod) < startofperiod or just fix the end dates.


Normalisation from 1NF to 3NF I need some guidance on normalisation from 1st Normal Form to 3rd Normal form. I have already set up my database which is working fine but needed to show some normalisation to show that I did do some design. MY only issue is that while going through the normal forms, have I actually normalised the database correctly or not?

Can somebody please have a look at my normalisation and show any changes I should make in their replies?

Screenshort of ER schema - For full size image click here:

And where I started. Screenshot of 0NF:

Screenshot of 1NF:

Screenshot of 2NF:

Screenshot of 3NF:

question.noofanswers.  Why is it there?  Cant you just pull this from answer table at run-time?  Or are you enforcing rules on this?
Your student table looks like it has transitive functional dependencies in it but I can't be sure.  Is year dependent on courseid?  At any rate you are likely to want to break off student records and what courses they take since a student could take multiple courses over time.

Those are the only clear normalization/Codd's rules issues I see with this.  I could be missing something though.

Expanding on the second.  Take your student table.  Presumably there is a natural key somewhere but for now let's use StudentEmail as a candidate key because this can uniquely identify a given student (if there is a practical problem in your case, you can use StudentID as a substitute for argument's sake).

In this case StudentForename, StudentSurname depend functionally on StudentEmail.  For every email address there is one and only one forename and surname associated with it.  You might want to break away StudentAlias as well because that would give you an ability to have more than one alias per student, but if you will not have that, it breaks no normalization rules.

The problem is that Year depends on (StudentEmail, CourseID) meaning you have a 2NF violation here, where some attributes are dependent on part of the candidate key and others are dependent on the whole candidate key.  This really needs to be fixed before you can call it 3NF.


How does the correlation statistic parameter affect the cost of index scan? I have the table customers having customerid SERIAL as a primary key. Initially I had the following statistic for that column:

  attname   n_distinct    correlation
customerid       -1         -0.393365

The following query

EXPLAIN ANALYZE SELECT * FROM customers WHERE customerid < 10

produced the following analyzed plan:

Index Scan using customers_pkey on customers  (cost=0.00..35.46 rows=9 width=268) (actual time=0.003..0.010 rows=9 loops=1)
  Index Cond: (customerid < 10)
Total runtime: 0.029 ms

Note, that the cost to get all rows is 35.46. Now, I ran

CLUSTER customers USING customers_pkey;
ANALYZE;

and the correlation became equals to 1. After running the query once again I got the following analyzed plan:

Index Scan using customers_pkey on customers  (cost=0.00..8.41 rows=9 width=268) (actual time=0.003..0.005 rows=9 loops=1)
  Index Cond: (customerid < 10)
Total runtime: 0.024 ms

Note that the cost is decreased more than 4 times (8.41 now). My question is how exactly the cost of ranged index scan depends on the correlation? How is it computed? It would be good if you pointed out to some references to the postgresql documentation.

The details you want will not be found in the documentation, but rather in the source code.  In particular, src/backend/optimizer/path/costsize.c in the function cost_index.

It computes min_IO_cost based on the assumption the table is correlated, max_IO_cost based on the assumption it is uncorrelated, and then interpolates between them based on the correlation.


postgresql 9.4 + BDR Fails I'm trying to get postgresql 9.4 and BDR working. Following this tutorial: https://wiki.postgresql.org/wiki/BDR_Quick_Start

Actually, my sysadmin provided me with a package to test with. He's taken care of the compilation "stuff".

I have two instances running, one on port 5598 and the other on 5599. What I'm noticing is that when I leave the following line in the postgresql.conf file, I can't start the service:

 # Load BDR
 shared_preload_libraries = 'bdr'

the error message i get is:

  FATAL:  could not access file "bdr": No such file or directory

When I remove the above line, it I can start the service on the specific port needed.
According to the BDR docs, if you get that message, you're missing the bdr contrib package:

https://wiki.postgresql.org/wiki/BDR_Reference#Could_not_access_file_.22bdr.22:_No_such_file_or_directory

But I have it installed. I just don't know how to verify it because I don't exactly know what it does.

When i check my package manager to see everything that's been installed, I have the following items listed:

postgresql-bdr
postgresql-bdr-contrib
postgresql-bdr-dbg

My apologies in advance for being such a noob. But i'm not a sysadmin, linux is relatively new to me... and it looks like BDR is pretty new too. I'm just trying to collect as much information as possible for my sysadmins to be able to fix whatever is wrong with this package...

Thanks.

The tutorial on that link is not complete.
You should type

apt-get install bdr-9.4

instead of

apt-get install postgresql-bdr-9.4

After that everything is working fine..

I also lose some time to figure that out..


SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps.

This seemed to work, and I verified that the records are in the new msdb tables.

Once I restart SQL Agent, I only see some of those jobs listed under SQL Server Agent in SSMS.  I am very puzzled why SOME are not listed.

I am using SQL Server 2008 R2.

A better option might be to script the jobs out instead of taking the information directly out of the system tables. In SSMS just right click each job and it should give you the option to script to a new query window or file.

Scripting the jobs out in this manner is probably the most affective, short of just restoring the msdb database to the other instance. You can review the script before running it on your second server and ensure the job steps and schedule looks right. If you have any that run frequently you might adjust the script to have those created as disabled, so you can verify first.

There are a few things that might affect why your pocess did not work, one being the order in which you inserted them and ensuring that the records or linked by the jobid like they should be.


Which characters are allowed in MySQL/MariaDB user account names? It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section includes information on character encodings and the maximum name length, but it doesn't state whether the characters that can be used in an account name are restricted (or perhaps I missed that part?).

So, are there any special charachters in MySQL/MariaDB that cannot be used in user account names? For example, are control characters (like a line break) or wildcard characters (like the asterisk) illegal characters?

User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running:

MariaDB [(none)]> SHOW CREATE TABLE mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

That means, for my version, usernames can be between 0 and 80 characters that are valid utf8 strings but not outside the basic multilingual plane (only 3-byte characters).

So control and wildcard characters are valid, but for example, not emojis:

MariaDB [(none)]> CREATE USER 'a*b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER 'a
    '> b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER '';
ERROR 1470 (HY000): String '????' is too long for user name (should be no longer than 80)

MariaDB [(none)]> CREATE USER 'Robert\'); DROP TABLE Students;-- ';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT user FROM mysql.user;
+----------------------------------+
| user                             |
+----------------------------------+
| Robert'); DROP TABLE Students;-- |
| a
b                              |
| a*b                              |
| root                             |
| root                             |
| root                             |
| root                             |
+----------------------------------+
7 rows in set (0.000 sec)

Check the type in your system, I guess it could change depending on the version/vendor or be different in the future/past.


Moved 'tempdb', now file 'tempdev2' does not exist I moved the tempdb first locating the files:

use master
select name, physical_name
from sys.master_files
where database_id = DB_ID('tempdb');
GO

then I moved each file:

use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\')
go
alter database tempdb
modify file (name = templog, filename = 'S:\')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\')
go

The ndf tempdev2 did not move, though the path was set. My SQL Server will not start unless I use the "/f" switch.  When I retry the move or set the path to original location I get the following message:

  Msg 5041, Level 16, State 1, Line 1 MODIFY FILE failed. File
  'tempdev2' does not exist.

Any ideas? tempdev and templog are fine, just the tempdev1 ndf.
Is it possible to set the path for tempdev2 via the registry?

The mistake I made was probably not shrinking the ndf before the move. Now I can not shrink it because the path is not correct.

Additional information

I know get the following error in event viewer:

  CREATE FILE encountered operating system error 5 (Access is denied.)
  while attempting to open or create the physical file 'S:\tempdb'.

I checked permissions on the partition and folder, and everyone has full access and this permission error happens.

You are not providing a unique physical filename for each file.  You should alter tempdb like this:
use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\tempdev.mdf')
go
alter database tempdb
modify file (name = templog, filename = 'S:\templog.ldf')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\tempdev2.mdf')
go

Microsoft Docs for ALTER DATABASE file and filegroup options state the following:

To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:
MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )


Boyce Codd Normal Form understanding I have a knotty (at least for me) problem to solve.

  Normalize, with respect to Boyce Codd NF, the relational scheme:

  E(A, B, C, D, E, F)

  by assuming that (A, B, C) is the unique candidate key and that the following additional functional dependencies hold:

  A,B → D
  C,D → E
  E → A

Actually what I did is:

E1(ABCEF), E2(ABD), E3(ABCDF), E4(CDE)

then I added E5(ABC) and E6(EA) getting the final result:

E1(ABC), E2(ABD), E3(CDE), E4(EA).

Unfortunately, what I wrote is wrong... Can anyone help me?

You dropped "F" somewhere.

Since you're given {ABC} is a candidate key, then you know that ABC->DEF. (That just follows from the definition of "candidate key".) And ABC->D, ABC->E, and ABC->F, by Armstrong's axiom of decomposition.

The important part as far as BCNF is concerned is E->A.

  So the final result should be: E1(ABC), E2(ABD), E3(CDE), E4(EA),
  E5(ABCF)

I think that's right, except for E1. You don't need a separate relation for a candidate key.  I'm not sure how you derived that one given what's in the problem.

Usually, your textbook will have at least one algorithm for normalizing relations. In the real world, there's often more than one 5NF schema for a given set of FDs.


How should I store multiple relationships (many posts to many categories)? I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ?

I thought of it this way first:

Categories Table:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32)   | NO   |     | NULL    |                |
| parent     | int(11)       | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Relationships Table:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| post_id    | int(11)       | NO   |     | NULL    |                |
| cat_id     | int(11)       | NO   |     | NULL    |                |
| order      | int(11)       | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Using this structure I will have lots of rows inside the relationships table, for example if a post has 4 categories I will have 4 rows only for this post, of course I won't have that much categories related to the post but I want to implement the same structure for tags too.

Am I doing it right ? is there a better structure to use ?

The way that you have described it is the way to do it.  The Primary key for this table is Post_Id, Cat_ID.


MySQL insert with value derived from primary id I'd like to INSERT into a MySQL table, in such a way that one of the columns holds a value derived from the primary id.

For example:

insert into picture_table ('picture name','folder') values ('holiday_123.png',FLOOR(pic_id/1000)

Where pic_id is the auto_incrementing primary id of the table. It would be even better if this was somehow hard-wired into the table. Can this be done?

The best I've come up with is
insert, then update using last insert ID

The table may look like this:

CREATE TABLE IF NOT EXISTS `picture_table` (
  `pic_id` int(8) NOT NULL AUTO_INCREMENT,
  `picture_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `folder` varchar(8) NOT NULL,
  (...other stuff...)
  PRIMARY KEY (`pic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

Plan A:  Use a TRIGGER to compute the value for folder.

Plan B:  Have folder be a virtual generated column.


Where should I store a custom ".my.cnf" file? I am working in a Vagrant box (for development) and I have a provisioner script written in Bash. The script does install MySQL and setup a few things: basically it does what mysql_secure_installation does but without use it. See below:

sudo yum install -y mysql-server
sudo chkconfig --add mysqld && chkconfig mysqld on
mysql --user=root -e "DROP DATABASE IF EXISTS test;USE mysql;DELETE FROM mysql.user WHERE User='';DELETE FROM mysql.db WHERE Db='test' OR Db='test_%';CREATE USER 'root'@'%';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;FLUSH PRIVILEGES;"

That worked as expected however I need to import a "large" SQL file and MySQL complaints and fails with the famous max_allowed_packet error.

I know the solution is to increase such value by editing the my.cnf file and add it to the section [mysqld].

Because the provisioner script should do this by default I am looking for the easiest way to do it which for me is: copy a file from the host to the guest while provisioning. I could use commands like sed to insert that line in the default my.cnf file but is overcomplicate something easy.

Having said that I did some research and from docs found here I can see a few places where I could store that custom file:

+---------------------+------------------------------------------------------------+
|      File Name      |                          Purpose                           |
+---------------------+------------------------------------------------------------+
| /etc/my.cnf         | Global options                                             |
| /etc/mysql/my.cnf   | Global options (as of MySQL 5.1.15)                        |
| SYSCONFDIR/my.cnf   | Global options                                             |
| $MYSQL_HOME/my.cnf  | Server-specific options                                    |
| defaults-extra-file | The file specified with --defaults-extra-file=path, if any |
| ~/.my.cnf           | User-specific options                                      |
+---------------------+------------------------------------------------------------+

I do know also that I could execute a query as follow (not sure if it's correct since I took it from here during my research):

SET GLOBAL max_allowed_packet = 1073741824;

Now that I have all that information above I wanted to ask: if I choose the latest option "User-specific options" and knowing that by default a vagrant user is created, knowing also that MySQL runs as root (I think so I might be wrong):

Where I should store the custom file? The vagrant user home directory? The root user home directory?

Is not clear to where ~/.my.cnf points to in the docs.

I am open to suggestions as well if the best way to do it is by running the query or by putting the needed config in some other path just let me know.

The MySQL version is as shown below:

[root@appserver home]# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

To keep the things simple place all custom server settings into the /etc/my.cnf file. Some user-specific options can be stored in the ~/.my.cnf where ~/ means "the homedir for the given system user". Also note the leading dot in the user-specific file name. That file is used for command-line client utilities like mysql or mysqldump only, not for server. Therefore the [mysqld] section can be completely omitted in the user-specific config file.


SSRS and Managed Service Account - impossible to configure? We are trying to configure Reporting Services to use a Managed Service Account.  The environment is:

Server:  Windows 2008 R2 SP1
Reporting Services: SQL Server 2012 (version 11.0.6567.0)

Currently, SSRS runs as a domain service account, but we would like to change to running as an MSA.  The SQL Server instance and agent have both been successfully changed to use an MSA.

When I try to change the service account to an MSA, via Reporting Services Configuration Manager, I receive an error:

  Microsoft.ReportingServices.WmiProvider.WMIProviderException: The account name is not valid. Specify an account in the form domain\alias.

  ---> System.Runtime.InteropServices.COMException (0x8004021D): Exception from HRESULT: 0x8004021D
     --- End of inner exception stack trace ---
     at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
     at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.SetWindowsServiceIdentity(String accountName, SecureString password, Boolean useBuiltinAccount)
     at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWindowsServiceIdentity(String accountName, SecureString password, Boolean useBuiltinAccount)

My question is simple:

Has anyone successfully changed the SSRS service account to use a Managed Service Account?  If so, how?!

Yes you can run SSRS with a Managed Service Account.

I would double check that your MSA is installed on your SSRS server. You can do so with Powershell.

Get-ADServiceAccount -identity msaname -properties hostcomputers |select hostcomputers

If your SSRS server is not listed, you will need to install the MSA on the server. This needs to be run locally (on SSRS server). You MUST be domain admin to run Install-ADServiceAccount successfully.

Install-ADServiceAccount msaname


mysqldump: Got error: 1066: Not unique table/alias: 'temp1' when using LOCK TABLES When I try to dump a whole database using
mysqldump -htest.com -uabc -p --column-statistics=0 abc > abc.sql

The following error occured.
mysqldump: Got error: 1066: Not unique table/alias: 'temp1' when using LOCK TABLES

I have no problem in dumping other database in the same host, only this database is having problem.
What is the cause and how I can fix it?

This is may be you are having temp1 table with same name but the cases may be different.
Check the value of lower_case_table_names parameter in your instance.
Rename the same name table with unique name.
select table_schema,table_name
from information_schema.tables
where lcase(table_name)='temp1';


getting innodb buffer usage I am running MySQL 5.5.52 I am trying to see how much of my innodb buffer pool is used. If I run 'show engine innodb status' I see this:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10731520; in additional pool allocated 0
Dictionary memory allocated 1823022
Buffer pool size   639
Free buffers       0
Database pages     638
Old database pages 215
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16622990, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19765731, created 109264, written 174555
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Is there anything in there that tells me how much is used?

If I run MySQL workbench it shows InnoDB Buffer Usage: 79.2% but I
want a way to get this programmatically with SQL not just from a GUI.
How can I do that?

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free' and
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total' will enable you to derive this percentage.

Keep in mind a page_free of 0 isn't fatal or even necessary bad - it just means every once in a while a read will come from disk or page cache. Its the rate at this occurs that is more important to performance of related to the size of the innodb_buffer_pool.


Subquery to return multiple columns or a JSON object Is there any way for me to return multiple columns from a subquery or at least return a column that is a JSON object of all of the columns I need?

This is a working query that works:

select r.*,
       (select status
        from builds
        where repo = r.id
          and branch = 'master'
        order by id desc
        limit 1)
from repos r
where id = any($1)

Where $1 is an array of ids

But I would like to return more than just the status column from the builds table:

select r.*,
       (select status, start_time, end_time
        from builds
        where repo = r.id
          and branch = 'master'
        order by id desc
        limit 1)
from repos r
where id = any($1)

After looking around it seems that row_to_json should work for me. However, I'm not sure how it should work in this given case:

select r.*,
       row_to_json(select status,
                   start_time, end_time
                   from builds
                   where repo = r.id
                     and branch = 'master'
                   order by id desc
                   limit 1) as build
from repos r
where id = any($1)

I am getting

syntax error at or near "select"

Don't make life hard for yourself, use a lateral join:

SELECT r.*,
       b.status, b.start_time, b.end_time
FROM repos AS r
   LEFT JOIN LATERAL
      (SELECT status, start_time, end_time
       FROM builds
       WHERE repo = r.id
         AND branch = 'master'
       ORDER BY id DESC
       LIMIT 1
      ) AS b ON TRUE
WHERE id = ANY($1);


How to distinguish free-standing composite types from table composite types in pg_type? When querying pg_type, I'm interested in identifying free-standing composite types (user-defined) and not those which are created along with tables. Perhaps there is a way to lookup pg_class (with typrelid) which might help but it's not apparent how I might do that.

i believe i've found the answer - pg_class (which can be joined to pg_type ON pg_type.typrelid = pg_class.oid) has a column called relkind which provides:

r = ordinary table
i = index
S = sequence
v = view
c = composite type *** identifies free-standing composite type
t = TOAST table

This query identifies custom (free-standing) types:

select t.typname, t.typtype, t.typcategory, t.typrelid, c.relname, c.relkind
from pg_type t
left join pg_class c on c.oid = t.typrelid
where t.typtype = 'c' and c.relkind = 'c';