Well I might not get it, but I try to answer it.

You said you need a high performance solution which runs often (minimum all 2 minutes) and you need a good approach which should be fast without locking. But you don't want a blackbox system.

Instead of a blackbox system, which is used on millions of installations with good results, you try to invent the wheel again and build your own solution? Hm, sounds a bit weird.

In fact these are my suggestions.

Replication even if you said you won't use it. It's quite the easiest and best solution you can use for this. The replication is easy to setup, replicate fast and you don't have to invent the wheel again. If you just weird about locking, you may try to set the ISOLATION LEVEL to READ_COMMITTED_SNAPSHOT. You can read more about it here. This will use up a part of your tempdb, but your table is always read- and writeable and the replication can work in the background.

See the example below:

ALTER DATABASE yourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE yourDatabase SET READ_COMMITTED_SNAPSHOT ON

CDC (Change Data Capture) can also be a solution. But this way you need to build nearly everything on your own. And I've made the experience that CDC can be a fragile thing in some circumstances. CDC will capture all data on a watched table (you need to specify each watched table manually). Afterwards you'll get the value before and the value after an INSERT, UPDATE or DELETE. CDC will hold back those information for a period of time (you can specify it on your own). The approach could be to use CDC on certain tables you need to watch and manually replicate those changes to the other database. By the way, CDC uses the SQL Server Replication under the hood too. ;-) You can read more about it here.

  Warning: CDC will not be aware of DDL-changes. This means, if you change a table and add a new column, CDC will watch the table but ignore all changes to the new column. In fact it only records NULL as value before and value after. You need to reinitialize it after DDL-Changes to a watched table.

The way you described above is something like capturing a workload using SQL Server Profiler and run it again on another database for some benchmarks. Well it could work. But the fact that there are too many side effects is a bit too heavy for me. What do you do if you capture a procedure call on your client. Afterwards running the same command at your principle database as it is out of sync? The procedure may run through, but it may delete/update/insert rows which were not present in your client. Or how do you handle multiple clients with one principle. I think this is too tricky. In the worst case, you probably destroy your integrity.
Another idea could be application based or using a trigger. Depending on how many tables you want to be synced. You can write all changes to a separate staging table and run an SQL Server Agent Job all x Minutes to sync those rows in the staging table with your master. But this may be a bit to heavy if you try to sync (e.g.) 150 tables. You would have a big overhead.

Well these are my 2 cents. Hopefully you have a good overview and maybe you found one solution which works for you.


"String or binary data would be truncated" error copying nvarchar(10) to char(10)

Without having an example of the data as well as the table DDL from the O.P., it is difficult to say for certain what the exact cause of this error is for the O.P. However, this behavior (and hence problem) can happen for others for the following reason:

Some Code Pages (which are determined by the Collation of each CHAR / VARCHAR field) allow for double-byte characters in order to map more than the 256 characters that can fit into a single byte. Since the max length of a string field is really a matter of bytes instead of characters, a CHAR / VARCHAR field with a max length of 10 can only hold 10 bytes, which can hold anywhere from 5 - 10 characters. With the source field being NVARCHAR(10), those 10 characters could map to characters that require more than 1 byte in the VARCHAR field. All it takes to get this error is 9 "regular" single-byte characters and 1 double-byte character which would require 11 actual bytes.

So, check the Collation of the CHAR(10) field in the destination table. Once you know the name of the Collation for that field, that will indicate what Code Page is being used, and if it is either 932, 936, 949, or 950, then this is quite likely the source of the problem. And in this case, just alter the destination field to have a max length of 20 (to be safe in case all 10 characters are double-byte). I would recommend VARCHAR(20), but if you really, really like blank padding, then do CHAR(20) .

A slightly more detailed explanation follows:

It is commonly understood that VARCHAR (i.e. 8-bit Extended ASCII) data is single-byte and NVARCHAR (i.e. Unicode) data is double-byte. This understanding of string data (and of encodings) is always true when working with the US-English alphabet (and with quite a few others, at least most "active" languages). And while it is true more often than not when working with most languages, it is certainly not always true.

The ways in which this understanding is not true for Unicode data is beyond the scope of this Question, and will not be detailed here.

But when it comes to our old friend Mr. VARCHAR, there are some situations that would allow for characters to take up 2 bytes instead of one. Yes, you read that correctly. But how? Well, prior to the existence of Unicode, some cultures with more than 255 characters in their alphabet still wanted to work with their native alphabet. Since that is not possible within a single byte (with a range of 256 values), they came up with Double-Byte Character Sets (DBCS). These are handled as different Code Pages, and Windows and SQL Server support four of them:

932 = Japanese (Shift-JIS within Microsoft, Windows-31J outside of Microsoft)
936 = Chinese Simplified (GB2312)
949 = Korean
950 = Chinese Traditional (Big5)

Don't let the term "Double-Byte" confuse you with the implication that they are just like NVARCHAR which only works in 16-bit sequences. These Code Pages are actually variable-length encodings, similar to UTF-8, and will use a single byte (8 bits) for at least the first 128 values (0 - 127) and some amount of the (128 - 255 range).

How does any of this fit into the truncate error? Well, the max length of both NVARCHAR and VARCHAR datatypes is really expressed in terms of bytes, not characters. Meaning, VARCHAR(10) is 10 bytes max, even if less than 10 characters fit into that 10 bytes. Likewise, NVARCHAR(10) is 20 bytes max, even if less than 10 characters fit into that 20 bytes.

With that in mind, we know that converting from Unicode to a Code Page will attempt to map to the same characters. In most Code Pages, those characters are all 1 byte in size. But in the 4 DBCS Code Pages, there are quite a few characters that exist (and hence can be mapped to) and are 2 bytes (else they wouldn't exist).

The problem here is that a DBCS Code Page is being used (for the destination), and at least one of the characters being mapped is taking up 2 bytes within the VARCHAR type.

The following is a working example of this behavior.

Test Setup

First, run this code to set up the test. The default Collation of the database in which you run this test does not matter. Here we are creating a temporary table to hold each of the first 65,536 Code Points of the main Unicode character set (characters beyond the initial 65,536 require two Code Points and are hence 4 bytes each, but again, out of scope here since they would not change the behavior related to the current issue ;)

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#Source') IS NOT NULL)
BEGIN
  DROP TABLE #Source;
END;

CREATE TABLE #Source ([Character] NVARCHAR(1));

;WITH nums (num) AS
(
  SELECT TOP (65536) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
INSERT INTO #Source ([Character])
  SELECT NCHAR(num - 1)
  FROM nums;

SELECT * FROM #Source;

Test 1: Latin1 character set (Code Page 1252)

Running the following will produce no errors. It just works. But it only translates the first 256 values as that is all that can fit into any Single-Byte Character Set (SBCS).

IF (OBJECT_ID(N'tempdb..#Destination_CP1252') IS NOT NULL)
BEGIN
  DROP TABLE #Destination_CP1252;
END;

CREATE TABLE #Destination_CP1252 ([Character] VARCHAR(1) COLLATE Latin1_General_100_CI_AS);

INSERT INTO #Destination_CP1252 ([Character])
  SELECT [Character]
  FROM   #Source;

SELECT [Character],
       LEN([Character]) AS [NumberOfCharacters],
       DATALENGTH([Character]) AS [NumberOfBytes],
       CONVERT(VARBINARY(2), [Character]) AS [BinaryValue]
FROM   #Destination_CP1252;

Test 2: Japanese (Shift-JIS) character set (Code Page 932)

IF (OBJECT_ID(N'tempdb..#Destination_CP932') IS NOT NULL)
BEGIN
  DROP TABLE #Destination_CP932;
END;

CREATE TABLE #Destination_CP932 ([Character] VARCHAR(1) COLLATE Japanese_Unicode_CI_AS);

INSERT INTO #Destination_CP932 ([Character])
  SELECT [Character]
  FROM   #Source;

Running the code shown above will produce the following error:

  Msg 8152, Level 16, State 2, Line 1
  String or binary data would be truncated.
  The statement has been terminated.

If this is an issue of a Double-Byte Character Set (DBCS) conversion, then increasing the field size by 1 byte should fix it. First, let's just make sure that nothing was actually inserted into the table (so that we will know for certain that the next INSERT statement is what put the data in):

SELECT *
FROM   #Destination_CP932;
-- no rows

Great. Now run the following:

ALTER TABLE #Destination_CP932
  ALTER COLUMN [Character] VARCHAR(2) COLLATE Japanese_Unicode_CI_AS;

INSERT INTO #Destination_CP932 ([Character])
  SELECT [Character]
  FROM   #Source;

No errors. Woo hoo! Let's see which characters converted:

SELECT [Character],
       LEN([Character]) AS [NumberOfCharacters],
       DATALENGTH([Character]) AS [NumberOfBytes],
       CONVERT(VARBINARY(2), [Character]) AS [BinaryValue]
FROM   #Destination_CP932
WHERE  1 = 1
--AND    DATALENGTH([Character]) > 1
--AND    [Character] <> '?'

If you scroll through the results, you should pay attention to the [NumberOfBytes] and [BinaryValue] fields.

To see just the double-byte values, uncomment the following line and re-run:

AND    DATALENGTH([Character]) > 1

To see the full range of values in Code Page 932, re-comment-out the DATALENGTH where condition and uncomment the following line and re-run:

AND    [Character] <> '?'

My count shows 9483 characters. And to be fair, add 1 to account for the actual ? character that was excluded, and we get a grant total of 9484 characters represented in a VARCHAR field .


Is catclust.sql really required? What is the importance

Let me start with another question.

Is catproc.sql/catalog.sql really required?

Try creating a new database manually:

create database;

And stop here, don't run any scripts. You have a "functioning" database, you can create tablespaces, users, query v$ views, create tables, indexes, insert and query data. You can even create user-managed backups.

Now try to run PL/SQL code, query ALL|DBA|USER_* views, start RMAN, use AWR or jobs. They will not work, because the objects they need were not created.

Can we use the database without running the above scripts? Yes we can, but some feature may not work, and life becomes quite uncomfortable. For example, the view DBA_DATA_FILES is created by the above script, but that view is not mandatory for the operation of the database.

Can we use RAC without running catclust.sql? Yes we can, but some feature may not work.
We can view the content of catclust.sql, but to be honest, I don't know what features depend on the objects created in there, so I just follow the documentation, and run the script.

  Why isn't the component showing up as installed?

Because that is nothing more, than an extra row in a specific table (SYS.REGISTRY$), we can insert anything in there. And the required script was not run.

  And, if the documentation states that catclust.sql create rac
  additional views, shouldn't it be a mandatory component for RAC dbs?

No, as it (in my opinion, based on the content of the script) does not add any additional core feature, just extends the existing ones.


Look into the module auto-explain.

We had a very similar question some time ago, it should be of help:
Postgres query plan of a UDF invocation written in pgpsql


Joining tables on DB server vs App-Backend server

JOINing is an important, standard, operation in SQL.  It is possible, but not that standard in app languages.  Do it in SQL.

Ditto for other operations, such as aggregation (eg SUM with GROUP BY).

Ditto for sorting (ORDER BY) and limiting (LIMIT).

Also, consider the amount of data that might need to be transferred.  In most example like the above, less data need be shoveled to the client.  This is another argument for doing the work in SQL.

Another argument...  SQL can handle a 1-row table or a billion-row table.  It does not care.  It will probably use totally different code for each (RAM-based code versus caching versus disk-based).  Do you really want to compete with that level of sophistication?

SQL loves doing "set-based" operations; client code loves tedious 'for'-loops.  Why write a for-loop when a single SQL statement will do the 'for' and the stuff inside it?

Use SQL for what it good at.

In rare cases, some tasks are better done in client code.  Your Question comes nowhere near such.


How to query chart data in a database agnostic way?

I have got something that I believe solves the problem, however I have not worked with dynamic truncation:

  the query must work with different truncation unit (year, month, day,
  hour, minute);

Basically I generate a table with random date and random values, and from that table #foo, I create the report with the help of table #bar.

I have only done partition by date, not year, month or hour or minute, but I guess it could follow the same logic.

everything else should be fine.

-- some topics:
-- how to check if a temp table exists?
-- create a random datetime
-- create a random money
-- checksum
-- newid
-- create a computed column
-- tally table
-- sp_executesql with parameters
-- cte
-- getting rid of duplicates

use tempdb
go

IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    drop table #foo

IF OBJECT_ID('tempdb..#bar') IS NOT NULL
    drop table #bar

-- creates the temp tables

create table #foo (
        creation datetime not null,
        value money)
go

create table #bar (dt date primary key clustered)
go

-- insert 1000 rows into the table
-- there might be cases when we get a duplicate value but rarely

SET NOCOUNT ON
INSERT INTO #FOO(CREATION,VALUE)
SELECT DATEADD(MILLISECOND, ABS(CHECKSUM(NEWID()) * 45000.98 % 3650), DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2001-01-01')) AS RANDOM_DATE,
       ABS(CHECKSUM(NEWID()) * 45000.98 % 3650) AS RANDOM_MONEY
GO 1000

--- add a date column to the #foo table so that we can use it for joints later on
ALTER TABLE #FOO
ADD DT AS CONVERT(DATE,CREATION)
PERSISTED
GO

-- add a clustered index on the dt column
CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT)
GO

--get the min date and the max date to build a tally table

declare @min datetime
declare @max datetime
DECLARE @TOTAL INT

DECLARE @sql        nvarchar(4000),
        @paramlist  nvarchar(4000)

select @total=datediff(day,[min],[max]),
       @min = [min],
       @max = [max]
from (
        select min (creation) as [min],
               max(creation) as [max]
        from #foo ) x

--convert @min to date
--using a tally table adds all dates to the end
--save them into table #bar

select [min]=@min,
       [max]=@max,
       [total]=@TOTAL

SELECT @SQL = '
;WITH    cteTally
          AS (SELECT TOP 500000
                ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
              FROM
                sys.all_columns AS AC CROSS JOIN
                sys.all_columns AS AC2  )

SELECT TOP ( ' + CAST (@TOTAL AS VARCHAR) + ' ) DATEADD(DAY,N-1,CONVERT(DATE,@MIN)) AS DT FROM cteTally '

SELECT @paramlist = '@min  datetime'

INSERT INTO #bar
exec sp_executesql @statement=@sql,@paramlist=@paramlist,@min=@min

-- by now we have the tables and the data to do our query

-- just having a look at the table here
SELECT *
FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.DT

-- prepares all the calculations that we need
WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt,
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0)

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

--get rid of the duplicates and present the result
SELECT
THE_DATE,
THE_NUMBER_OF_RECORDS_ON_THIS_DAY,
THE_TOTAL_VALUE_FOR_THE_DAY
FROM RADHE
WHERE THE_ROW = 1
--OPTION (RECOMPILE,MERGE JOIN)

--- drops the temp tables
IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    drop table #foo

IF OBJECT_ID('tempdb..#bar') IS NOT NULL
    drop table #bar


INSERT INTO FROM: Concatenating Forename and Surname when Forename can be blank,

Liya has a nice solution. This is another that handles both the null case and the empty string last name case as well.

INSERT INTO [dbo].Test
select LTRIM(RTRIM (COALESCE(firstname,'') + ' ' + COALESCE(lastname,'')))
    ,field1
    ,field2
    ,fieldn
FROM RAW.Test ;

EDIT: Modified to allow either field to be nullable or an empty string with trimming in full effect in response to the modified requirements of the OP.


you have to allow azure services and applications to connect to your database.
Take a look at the firewall rules in Azure sql database:
https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
You can eventually allow azure services to connect to your database in this way:

You can disable this option after the export activity.
Please, remember that this is not an option of export task but this is a firewall rule of your Azure SQL Database Server.
Consider the export task like an azure service against your database so you have to allow azure to connect as written here:
https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure#connections-from-inside-azure


Delete cascade produce scan intead of index seek

You may want to look in the table sys.foreign_keys for the foreign key joining the tables, and see if the index has the value is_not_trusted = 1.

This query, originally from BrentOzar.com, will show you which foreign keys in your system are not trusted:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;
GO

Note: I recommend at least subscribing to Brent Ozar's emails - lots of interesting information coming out of that website, and their Blitz queries are really helpful.


Your questions are very broad as you are touching a lot of topics in sql server. I am just going to provide you relevant links so you can understand the subject in a better way and at your own pace.

  Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same?

Seperate spindles is the best recommendation. Read on When should you put data and logs on the same drive?

  Do Trans Logs and ldf logs write concurrently or one after the other?

Transaction log is your ldf file. Writes to the transaction log are sequential in nature and benefit by being isolated on to separate physical devices.

Read up on : Diagnosing Transaction Log Performance Issues and Limits of the Log Manager and Optimizing Transaction Log Throughput

  Regardless of your decision, though, use Perfmon after the system goes live and track the drive activity.  If one of the two arrays is being overwhelmed with load while the other one sits idle, then it’s time to rethink the decision.

I would suggest :

RAID 1 for OS
RAID 10 for data, logs and tempdb

Note that there are many other factors that will directly or indirectly affect your choice as to whether your workload is OLTP or DSS ?

For optimized I/O parallelism, use 64 KB stripe size. Read on Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?
Make sure to properly size the tempdb files
Use DMVs and Perfmon to set a baseline and look out for spikes or drops in your baseline.
Make sure to Test Disk Performance
Make sure that once your backups are completed locally, you move it to a safe and secure location and test them for restores regularly. So in an event of a disaster, you can confidently recover your data.


Select a set of keywords from table1 and search for strings containing these keywords in table2

Something like:

select t2.txt
from t1
join t2
    on t2.txt like '%' + t1.name + '%'
where t2.char = 'A'

Adding ID = 1 is left as an exercise for the reader.

I assume you just made an example up. If not, you should avoid using reserved words like CHAR as column names.

It's generally considered bad practice for sargability to use a wildcard on both sides of a field.  It may not matter in this instance, but if there was an index on that field, it would not be usable and could cause some performance issues.

It may be useful to use SELECT DISTINCT to cover the case where both '1a' and '3c' match the same row (which would otherwise result in the row returning twice).


mysql query builder inefficiently builds a query

Short answer:  You need to rewrite the query.  Or maybe upgrade.

Long answer:

MySQL has long been dumb when it comes to IN ( SELECT ... ).

By using a derived table, the optimizer will do the subquery first, thereby being a lot more efficient:

SELECT ...
    FROM ( SELECT DISTINCT track_id
               FROM ...
               WHERE ... ) x
    JOIN ...
        ON x.track_id = ...

Or...  Upgrade to 5.7 (or 8.0) with its "query rewrite" so that you can rewrite the query after it is sent from whatever is building it.


INNER JOIN Giving time out on large database

At the very least, you should have an index on WardCode on uk_pc.  If you also add Latitude and Longitude it will become covering for this query and so the table will not need to be used at all.

Your datatypes aren't really the most efficient, so I'd definitely revisit your table designs.  narrower columns (Latitude and Longitude are better off as decimal and would never be 100 characters long) will store better in the tables (and of course in the indexes as well).  The width of varchar columns might seem to be something you don't neet to worry about, but it does affect what the database engine sets aside while performing queries since it does not know if it will encounter larger strings in the data.  In general, limiting the working set like this can only help, especially when it is easily foreseeable.


mysql query matching multiple elements from a single table

You can't have anything about the inner tables in the WHERE clause for the outer joins to work as expected - move those to JOIN conditions
You would need FULL JOIN which is something that MySQL sadly doesn't support - you can emulate FULL JOIN with two outer joins and UNION

 SELECT
    a.`cust`
   ,a.`type`
   ,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
   ,a.`data2`,
   IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
   ,b.`cust`
   ,b.`type`
   ,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
LEFT OUTER JOIN `test`.`tab1` b ON (a.`type`=b.`type`) AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`) AND b.`cust` = 200
WHERE 1=1 AND a.`cust` = 100
UNION
 SELECT
    a.`cust`
   ,a.`type`
   ,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
   ,a.`data2`,
   IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
   ,b.`cust`
   ,b.`type`
   ,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
RIGHT OUTER JOIN `test`.`tab1` b ON (a.`type`=b.`type`) AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`) AND a.`cust` = 100
WHERE 1=1
AND b.`cust` = 200;


Of the several utf8 collations available:

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

These are reasonable ways to compare greek strings:

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

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

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

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

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

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

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

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

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

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


Why does sp_replmonitorhelpsubscription not work without parameters?

Checking the code reveals that :

-- validate @publication_type
    --
    if (@publisher_db is NULL or @publication is NULL)
        and @publication_type is NULL
    begin
        raiserror(20587, 16, -1, '@publication_type', 'sp_replmonitorhelpsubscription')
        return (1)
    end

This means that if @publication_type is NULL then it will throw an error which is expected.

Even on 2014, it is throwing same error.

Also, you just need to run with

sp_replmonitorhelpsubscription  @publication_type = 0 -- for Transactional publication. since the data type is INT.


MySQL Error: impossible to write to binary log

There are 3 methods that MySQL can use to write to the binary logs:

STATEMENT
This means that every SQL statement on the master is recorded in the log, and executed on the slave.  This can cause problems if the SQL statement contains statements such as "NOW()", "RAND()" and anything non-deterministic.  This also requires support from the storage engine in use.

ROW
This means that every row that is changed by a statement is individually recorded in the binary log.  This gives larger binary logs (generally) than statement based logging, but is almost always guaranteed to give the exact replication needed.

MIXED
This allows MySQL to choose between binary and row based logging as required.

If you're getting this error, one suggestion is to change the BINLOG_FORMAT variable to MIXED.  This allows MySQL to automatically switch between ROW and STATEMENT based logging as required.


When working with addresses, one thing you may consider -- at least give it a serious look -- is using the USPS to convert the user-supplied address into the USPS standard address. So the same address, just with different abbreviations or punctuations, will converge to the same address. There are several remote apis the Post Office makes available to your app.

As for the database, yes, the literature shows that the place to put a 1-to-many FK is in the "many" tuple. You have also stated you want to maintain a history of address changes so this format makes even more sense.

However, there are many advantages to having a 1-to-many intersection table. This is similar to a many-to-many intersection table except that one side is made part of the PK to enforce the "1" side of the 1-to-many cardinality.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  constraint PK_OneToMany primary key( OneID, TypeID )
);

Each user has only one entry for each kind of address and even if multiple users share the same address, if one user moves to a different address, the other users are not affected.

There is a small alteration needed to maintain the history of changes. All that is needed is the addition of a datetime field.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  Effective datetime not null default NOW,
  constraint PK_OneToMany primary key( OneID, TypeID, Effective )
);

Now all changes are maintained and it is very easy to look back at any time in the past to see what the address was at that time. Here is an answer I posted that will refer you to more information. Mine is not the accepted answer, and I would highly recommend you take the time to become familiar with both mine and the accepted answer. Choose the one (or design your own with what you learn) that is best for your situation.


Installing "SQL Server 2000 Client" and "SQL Server 2000 service pack 4"

The SQL Server 2000 Client is the driver, admin and query tools.  The service pack is an update for the tools and the server.

I'd recommend looking at a newer version.  Your options are SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and the soon to be released SQL Server 2012.


Does enabling pg_stat_statements reduce disk space?

pg_stat_statements shouldn't use much disk space.

It uses two files:

pg_stat_statements.stat persists pg_stat_statement's data across server restarts.

It is written upon shutdown and read on server startup. The file is deleted as soon as it has been read, so it should not be present when the server is up.
pg_stat_tmp/pgss_query_texts.stat contains the text of the queries being tracked.

This file will grow as new statements are added, but if it becomes too big, pg_stat_statements will perform a “garbage collection” and rewrite it.

If you suspect that there is a disk space problem with pg_stat_statements, check pg_stat_tmp/pgss_query_texts.stat. But I don't think this file can be a problem unless you set pg_stat_statements.max to an astronomically high value and run lots of different statements.


If you just want to backup to a non-network path location, then there is now a [tool for backing up to Windows Azure.  Also, SQL Server 2014 will allow to backup directly to Windows Azure without a separate tool. This is a viable alternative to trying to backup to a FTP site while eliminating the need to keep a local copy of the backup. Please note the backups can be encrypted which is an obvious must if you are storing the backups off premises.

Otherwise, you may want to find a way to map a FTP site as either a network path or drive.  Although I have never used either of these products, FTPUse and NetDrive should allow you to accomplish this goal.  There's a blog post on how to map FTP sites as network drives in Windows 7 that might also work for you, but it appears to be an OS-specific solution that might not work for you.

Although these solutions may work, I would strongly recommend that you backup the file locally and then copy it to a FTP site.  Drive space is cheap.  If you store the backup locally, then you can easily restore from backup without having to download the backups.  You should also periodically test restoring your backups to validate that you have good backups.  You should also periodically test restoring your backups from wherever you are sending the backups to make sure these are good as well.

Backing up a database is an IO intensive process and backing up to a FTP location even if it is represented as a local drive will probably be very slow.  Even if the performance is adequate for your purposes, FTP connections can be unstable and can be interrupted for a variety of reasons.  You don't want to have to run another backup just to get the file over the to FTP site.  It would be safer to backup to a high speed local drive and upload the file separately.  I would strongly recommend finding a way to solve your storage problem rather than going down this road.


How to constraint a foreign key to a subset

First, you don't have to duplicate data.

If you add an inspector table, that's not duplicating. You need an "inspectors" entity in your model, you add an "inspector" table. Only the inspector_id values will be duplicated which is fine. So, that's solution A.

Solution B might be to add a an is_inspector column in the table you want to have the foreign key and restrict that column to TRUE:

is_inspector BOOLEAN DEFAULT TRUE,
CHECK (is_inspector),
FOREIGN KEY (inspector_id, is_inspector)
  REFERENCES employee(id, is_inspector)

I would go for solution A every time but if solution B simplifies your application code for some reason, it still solves the issue and enforces the same requirements. But if you later need other tables to reference "inspectors", you'll need a boolean column in every one of them as well. So, solution A is the more simple approach as it would not require any such boolean columns at all (not even at the employee table, you can find the inspectors by joining to the inspector table).


SSMS Oracle import using OpenQuery over LinkedServer (Oracle Provider for OLEDB) - XML Parsing: unable to switch encoding

I found a solution, for everyone to know:

Assuming that we put the xml field in a variable of type varchar(max) and then casting it to a variable of type XML (but not by using the CONVERT function, rather just by simple assignment), in the end the value of the newly created XML variable will be included in an insert query, by casting it back to varchar(max).

DECLARE @id numeric(19, 0)
DECLARE @xmlmsg varchar(max)
SELECT @id = id, @xmlmsg = internal_message FROM
    OPENQUERY(DATAB_ORACLE, 'SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company,
        XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message, exchange_message FROM datab.tra')

DECLARE @xml xml
SET @xml = @xmlmsg

DECLARE @tsql varchar(8000)
SELECT @tsql = 'INSERT INTO [datab].[dbo].[tr] (ID, CREATED, UPDATED, COMPANY, INTERNAL_MESSAGE, EXCHANGE_MESSAGE)
    SELECT id, created, updated, company, ''' + CONVERT(varchar(max), @xml) + ''', exchange_message FROM OPENQUERY(DATAB_ORACLE,
    ''SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company, XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message,
    exchange_message FROM datab.tra where id = ' + CONVERT(VARCHAR(max), @id) + ''')'

EXEC (@tsql)

You should note that in such cases, seeing that the XML Document Declaration (e.g. "") is gone missing after inserting the row is normal, due to the fact that SQL Server strips that part (by design). Find out more here: http://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx


Is it possible to prevent data loss with Postgres WAL archiving?

You are correct.

The way to avoid this is to use pg_receivewal, which streams WAL information from the database server and persists it in WAL segment copies immediately.

By default this is asynchronous, so you could lose some split seconds in case of a crash. It you need to avoid that, you'd have to use synchronous replication with pg_receivewal, but be warned that

This makes DML statements slower, since COMMIT cannot succeed until it has heard back from pg_receivewal.
you shouldn't set synchronous_commit = remote_apply, because pg_receivewal never applies the changes, it just saves them to a WAL file.


how do you revoke create table from a user on postgresql 9.4?

According to the documentation, what one needs for creating tables in a schema is CREATE on that schema.  This you think you revoked, but as you experience it didn't really happen - the only plausible explanation is that from Daniel's comment: you are revoking access on the schemas of another database.

When you clear this up, you'll still need to grant/revoke the following:

GRANT USAGE ON SCHEMA public TO readonly; to be able to access the objects in there
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public, readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
and, if you plan to add new objects (tables) later, you have to set the default privileges, too.


SQL 2008, how does it link Full Text Catalog to a filegroup

In SQL Server 2005 and prior, you could specify the filegroup for full text.

In SQL Server 2008 and newer, full text is completely different, and the ON FILEGROUP stuff doesn't matter.  However, Microsoft doesn't just rip out syntax - they deprecate it, and a few versions later, it'll disappear.  (Backup log with truncate_only is a good example of this.)  This gives you time to clean up your scripts before the syntax disappears.


Deploying a cube where database name has been changed

This is basically because the Sale cube still has SalesAndStuff as the ID, renaming your database only changed the name

It will probably be the easiest if you just click "view code" in visual studio and change the <id> node to match your new name.

If that doesn't work out or you think that's too risky you can follow this procedure on msdn.

Make sure you only edit the ID for the database though. If you edit ID's for other objects such as dimensions you will mess up a lot of objects.

Create a backup of your database and solution before you try this.


WIN10 -Unable to run MySql server after installation - 5.7

A few potential issues:

In your my.ini file, remove the trailing \ from the datadir variable, and change the remaining final \ to /. You path will look like ...\MySQL\MySQL Server 5.7/data
Whatever user you are running mysqld as may not have permissions on C:\Program Files\MySQL\MySQL Server 5.7\data. If you installed as administrator, try running command prompt as administrator. If there is a service registered for MySQL, open up services.msc and start it from there.


Select first and last row based on each user id mysql

SELECT id,
       MIN(`timestamp`) first_timestamp,
       FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) first_event,
       MAX(`timestamp`) last_timestamp,
       LAST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) last_event
 -- or FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp DESC) last_event
FROM `table`
GROUP BY id


jdbc connection string ORACLE , integrated security

When you use Kerberos authentication (for example Active Directory) with JDBC, you have to prepare your code for it, and not just simply change the connection string.

The Database JDBC Developer's Guide and Reference covers this topic:
http://docs.oracle.com/cd/E11882_01/java.112/e16548/clntsec.htm#JJDBC28344

Essential parts from the example of the above URL:

Example connection string and declarations:

  String url ="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)"+
    "(HOST=oracleserver.mydomain.com)(PORT=5561))(CONNECT_DATA=" +
    "(SERVICE_NAME=mydatabaseinstance)))";

   OracleDriver driver = new OracleDriver();
    Properties prop = new Properties();

Specifying the kerberos specific options:

prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_SERVICES, "("+AnoServices.AUTHENTICATION_KERBEROS5+")");
prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_MUTUAL, "true");

Specifying the credential cache location:

prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_CC_NAME, "/tmp/krb5cc_5088");

Finally connecting:

Connection conn  = driver.connect(url,prop);
String auth = ((OracleConnection)conn).getAuthenticationAdaptorName();
System.out.println("Authentication adaptor="+auth);
printUserName(conn);
conn.close();


Can I skip the intermediate table in a join?

tl;dr

The short answer is no. You have to do the multiple join, even if you only want columns from the two tables 'on each side'.

Table design

This seems like a pain, but although it makes your queries longer, it is much more important to get the database structure, including foreign keys right, than it is to make queries short.

There are a couple of ways of arranging foreign keys, sometimes with extra tables which are only there to express relations. For example, if every Cat has a unique Mouse, there could be a foreign key from Cat to Mouse with a unique constraint. It could even be the primary key of Cat. If every Cat has zero or more Mouses, but no Mouse having more than one Cat, you would have a usual foreign key from Mouse to Cat. If every Cat has zero or more Mouses, and every Mouse has zero or more Cats, you would create a third table CatMouseMatching, which has an entry for each 'edge' between a Cat and a Mouse, with foreign keys to the Cat and the Mouse. You will learn that these patterns are very important and the basic cases cover a lot of situations.

Now, suppose that you reverse the foreign keys in your table design, so that Utilizadors and Areas both have a foreign key to Permissoes. This would mean that you could simplify the join - because you could filter on permissoe_id being equal in both tables. However, this would be a quite strange relationship between Utilizador and Area which might not correspond to reality. You might have an Utilizador associated with a Permissoe which did not have any Area linked to it. This Utilizador would just disappear from the inner join.

This might only make sense if there was some real entity which formed the link between the two entities. For example, every Cat lives in a House, and every Mouse lives in a House. You can easily find the Cat-Mouse pairs which live in the same House.

Practical tips

You could always create a VIEW, which makes a common query act like a table. Your query above could be defined as the view, and then other queries could select from it and join to it, without having to understand what joins make up the view internally.

If you don't create a view, you might at least save the snippet of code which does the join, so that you can paste it into other similar queries later.


SSMS runs on Windows only. If you're looking for a Microsoft tool, check out Azure Data Studio. There are also third-party tools available for Windows, Linux and Mac.


SQL Server data extract with limited performance impact

Many ways you can approach this scenario.

  I need to do a data extract from the database for reporting purposes.

Backup restore approach is best suited if you want to do this on an infrequent basis and do a static reporting of data.
Database Snapshot is an enterprise version feature only and this also gives you a static point-in-time view of the database.

Note that database snapshots take up space on disk, and having too many can fill up your disk space, especially in a production environment where data is updated frequently. In addition, using database snapshots will degrade performance a bit because I/O is increased on the database due to the fact that the data pages are copied as the write operation is performed.

Refer to Microsoft SQL Server Database Snapshots and Synonyms to over come some limitations that snapshots have and workarounds.

Look into Replication especially T-Rep if you want to do near to real-time reporting and want to offload the reporting work from the primary server. T-Rep will give you the benefits of Transactional consistency, Low latency, High Throughput, Minimal overhead as well as the ability to filter rows.
Lastly if none of the options are feasible to you, then with proper testing look into changing the isolation level of the database to RCSI (Read Committed Snapshot Isolation). This will be at the COST of TEMPDB performance.

-- check if RCSI is enabled
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'
-- Enable RCSI
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON

From The Data Loading Performance Guide :

  RCSI basically will prevent queries reading data  to block or be blocked by other queries modifying data in the same table. It is a powerful alternative to NOLOCK because it guarantees a complete, transactionally consistent view of the data and does not require a special hint. While RCSI originally targeted scenarios common to OLTP workloads, the feature can be a powerful tool in data warehouse workloads or scenarios involving large-scale bulk insert operations.

  RCSI is enabled as a database-wide setting. When enabled, reader queries do not acquire shared locks on rows, pages or tables, and as a result they are not blocked by X or BU-locks taken by others. Instead, new or modified rows in a table carry a 17-byte version identifier, and the before-images of any rows being changed by a transaction (updated or deleted) are copied to tempdb using the row versioning mechanisms within SQL Server. Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows.

Also read up on : Comparing Different Results with RCSI & Read Committed from SQL CAT team.

LASTLY, to touch on NOLOCK hint, I would suggest you to read up

SQL Server NOLOCK Hint & other poor ideas.
Is the NOLOCK (Sql Server hint) bad practice?
THE SIDE EFFECT OF NOLOCK


how to publish user defined table type as an article in Transactional replication

UDTs are not articles that can be added to a publication directly. They are often used in stored procedures, so when you publish a stored procedure that depends on a UDT, the table type is scripted with the procedure itself.

In this case, it looks like the snapshot agent is failing while scripting the UDT. You could investigate the issue by changing the -OutputVerboseLevel switch to 2 and look for meaningful error messages in the output.

Another option is to remove the offending procedure from the publication and script it manually, along with its UDT.


How can I return the number of values in each row which match a criterion?

I think you are going to have to roll your own.

I'd do something like the below. (last query finds first 10 records that have exactly 2 readings above my threshold).

/*

Script for trial voltage query on DBSE

*/

drop table voltages;

Create table voltages(
    id  number
,   v1  number
,   v2  number
,   v3  number);

alter table voltages add constraint pk_voltages
primary key(id);

/*
add 10,000 rows
v1 between 0 and 10
v2 between 0 and 100
v3 between 5 and 15
*/

insert into voltages (id,v1,v2,v3)
select rownum, dbms_random.value(0,10),dbms_random.value(0,10),dbms_random.value(0,10)
from dual
connect by level <= 10000;

commit;

create or replace function above_threshold(p_thresh in number, v1 in number, v2 in number, v3 in number)
return number
as
l_retval number := 0;
begin
   if  v1 > p_thresh
    then l_retval := l_retval + 1;
   end if;
   if
       v2 > p_thresh
    then l_retval := l_retval + 1;
   end if;
   if
       v3 > p_thresh
    then l_retval := l_retval + 1;
  end if;
  return l_retval;
end;
/

/* display those that have 2 readings above threshold */

select id,v1,v2,v3
from voltages
where above_threshold(8,v1,v2,v3) = 2
and rownum <= 10;


One potential method is to create a date_time column and a version_id column on an indexing table, essentially what you have described. The difference that you should make though is a new data insert onto all project-related tables whenever version_id is incremented. Something like this psuedoSQL...

insert <current date_time>, (select MAX(version_id) from versions)+1 into versions
insert (select (* - version_id - <data that is being updated>) from project),updating_column=new_data, (select MAX(version_id) from versions) into <project related tables>

This is a rudimentary method of thinking about this, but this may lead you down the right track. The major downside with this concept is that you have a lot of data running around that is essentially redundant but is logically simple to implement.

The other, more storage-smart way to think about this relationship is to keep track of what data gets updated. Your versions table would now have a foreign-key column for all data that can potentially be updated as part of your projects as well as a version_id primary key. Instead of inserting version_id into your project tables, you would insert column_version_id.

The idea would conceptually work like this. When you are querying for all data at a point in time you will first hit the versions table. From there it will join all your project tables in based on column_version_id.

While this concept has better storage there are more moving parts to the logic and is also less flexible with changes in how your project data is managed.


Why does MySQL only sometimes use my index for a range query?

The general behavior of the MySQL Query Optimizer is as follows: If less than 5% of the rows of a table has to be traversed in an index, the index is used. Otherwise, the index is dismissed and a full table scan is done.

Now look at the index: (startBlock,endBlock).

Here are two questions

How many netblock up to the third octet do you have for startBlock ?
How many netblock up to the third octet do you have for endBlock ?

To get a hold of the distribution of netblocks, run this query

SELECT *,
    IF(ABS(endNetBlock - startNetBlock) >= 256,1,0) NotWithinSameRange,
    IF(startNetBlock - endNetBlock > 0,1,0) StartRangeAheadOfEndRange
FROM
(
    SELECT COUNT(1) rangeCount,startNetBlock,endNetBlock
    FROM
    (
        SELECT
            (startBlock - MOD(startBlock,256)) startNetBlock,
            (endBlock - MOD(endtBlock,256)) endNetBlock
        FROM geoIP
    ) AA GROUP BY startNetBlock,endNetBlock
) A

This will give you a general idea of

how many netblock combinations you have
presence of any invalid ranges

If NotWithinSameRange or StartRangeAheadOfEndRange are 1 in any instance, that range has to be clenaed up. You have to be sure that all netBlocks are properly represented.

If you have StartRangeAheadOfEndRange=1, that is a definite problem.

I hope these suggestions provide food for thought.


Connection pooling with 3 databases but 1 database as master for repmgr replication?

After research more about this topic the answer is, no, I can't, the repmgr way to work allows me to have pgbouncer connecting to master instance of postgres to handle writes and allowing to use slaves to perform reads.

I order to archive the goal exactly like I asked I must use pgbouncer for connection pooling and use pgpool2 and its Statement-Based Middleware for replication.


Why does using a variable make a difference in performance?

Sometimes yes it's good to use statics.  In this case the SQL Server doesn't know what the value is in the variable when it's creating the plan so it's escalating to a table lock I'm guessing.  You could force the locks to be row locks on the delete statement which should fix the problem.


Determine Sequence last used value for sql server before 2017

I'm not sure why you care about this.  Normally you only care about the NEXT VALUE, as looking at the current_value is tricky with multiple sessions.

But you're essentially trying to determine if the sequence has been incremented.  So you can simply increment every sequence once after you create it.  EG

instead of

CREATE SEQUENCE SEQ_TEST START WITH 1

use

CREATE SEQUENCE SEQ_TEST START WITH 0
SELECT NEXT VALUE FOR SEQ_TEST


Concatenating Strings & Using FORMAT Together - NOT WORKING

It doesn't work because you are attempting to concatenate the last two characters after specifying the column alias. Move the AS VARCHAR to after the + CHAR (10).

You currently have:

'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR + CHAR(13) + CHAR(10)

when instead it should be:

'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') + CHAR(13) + CHAR(10) AS [VARCHAR]

P.S. Please note that I added square brackets around the column name alias. It is a good habit to get into doing that always. It clearly isn't always necessary, but is required when using spaces, most punctuation, etc. And it also avoids potential issues when using reserved keywords (such as VARCHAR, though that isn't causing any problems at the moment).


This looks ugly:

  with uu(unit, coefficient, u_ord) as (
    select
     unit,
     coefficient,
     case
      when log(u.coefficient) < 0
      then floor (log(u.coefficient))
      else ceil(log(u.coefficient))
     end u_ord
    from
     unit_conversion u
  ),
  norm (label, norm_qty) as (
   select
    s.label,
    sum( uc.coefficient * s.quantity ) AS norm_qty
  from
    unit_conversion uc,
    substance s
  where
    uc.unit = s.unit
  group by
    s.label
  ),
  norm_ord (label, norm_qty, log, ord) as (
   select
    label,
    norm_qty,
    log(t.norm_qty) as log,
    case
     when log(t.norm_qty) < 0
     then floor(log(t.norm_qty))
     else ceil(log(t.norm_qty))
    end ord
   from norm t
  )
  select
   norm_ord.label,
   norm_ord.norm_qty,
   norm_ord.norm_qty / uu.coefficient val,
   uu.unit
  from
   norm_ord,
   uu where uu.u_ord =
     (select max(uu.u_ord)
      from uu
      where mod(norm_ord.norm_qty , uu.coefficient) = 0);

but seems to do the trick:

|   LABEL | NORM_QTY | VAL |       UNIT |
-----------------------------------------
| mercury |   1.5e-8 |  15 | microlitre |
|   water |  0.00112 | 112 | centilitre |

You don't really need the parent-child relationship in the unit_conversion table, because the units in the same family are naturally related to each other by the order of coefficient, as long as you have the family identified.


The no-beep option causes the mysql command line client not to beep when errors occur.

If that's an option you want, it should be in the [mysql] section of your my.cnf.  This error suggests that it's in the [client] section of your my.cnf, which causes mysql_upgrade to throw the error since the option isn't recognized.

Check your my.cnf and either remove the line or place it in the [mysql] section instead of [client] and you should be good.

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

-- http://dev.mysql.com/doc/refman/5.5/en/option-files.html


Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It's organized by last name, first name.

If I asked you to get that phone book and read out the names:

SELECT FirstName, LastName FROM dbo.PhoneBook

You would usually read them out to me in last name order. You wouldn't have to sort them - they'd just come out that way because that's how they were already stored.

That's why SQL Server's results can seem sorted by default.

Thing is, it's not guaranteed, and several scenarios can change that. For example, say you walk over to the phone book to read the last names for me, and someone else is already reading through the white pages. You might choose to follow along with them, reading from the same area of the phone book. You would start where they already were (say the M's), and then when that other person reached the end of the phone book (the Z's), you'd circle back to the A's and read the part that you'd missed. That's called merry-go-round scanning, and SQL Server Enterprise Edition does that by default, and you can't turn it off. You can't predict when you're going to get it, either.

Another scenario - say that I only ask you for the first names in our city, not the last names:

SELECT FirstName FROM dbo.PhoneBook

Note that I didn't ask for the names to be sorted. At first, when we only have the white pages, you might use those white pages and yell the answer out to me - but they wouldn't seem sorted. (Our phone book's white pages are ordered by last name, first name, so if you just read from there, the first names will be all over the place.)

If later, someone creates an index just on first name, SQL Server is smart enough to use that for my first-name-only query because it'd be the narrowest/smallest object available to satisfy my query. Suddenly, the results might seem sorted by first name - but that's just a coincidence because there's a new index available to satisfy my query.

  For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

In order to do grouping, you may have to sort data. Taking our phone book example, if I asked you to group everyone together by first name, you would have to sort them by first name in order to accomplish that task. Note the resemblance is between the phone book and the index, not the table (except when the table has a clustered index, in which case the table is the clustered index, so the table/clustered index has a fixed logical order).

Now for some gentle career advice: because these concepts are new to you, I would humbly suggest that rather than forcing SQL Server to do your bidding, thinking that you're somehow going to tune it to go faster, that you take a few steps back. You're asking a great curiosity question here, so I'd suggest watching my free SQL Server training video series, How to Think Like the SQL Server Engine. I use pages from the Stack Overflow database to explain how SQL Server delivers your query results.

My suggestion might seem a little controversial - after all, I'm pitching my own training here - but it's free, and it's even open source, licensed under the MIT license for folks to reuse. After you've mastered it by watching me do it, you can take the slide deck and train your fellow teammates.


What is the name of the part between SELECT and FROM in a SQL query?

It's usually called a "select list", which contains "select list items".

The FROM clause contains a list of table references.

This is not specific to Postgres, by the way.

So, with respect to your use case you might say that json_array_elements() can be used both as an expression in the select list and as a table reference.


Receive strange error during mysql auto-repair check optimize

Here is the original command that you ran

/usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize DATABASE_NAME

Since you are running mysqlchk against the database, you need to specify the --databases option

/usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize --databases DATABASE_NAME

Give it a Try !!!


How Maxscale deals with "Select for update transactions"

It would be routed to the master, because SELECT ... FOR UPDATE is only a part of a transaction, and according to the MaxScale 2.1 readwritesplit documentation, "all statements within an open transaction" are routed to the master.

You should be able to verify this with a query like this, which should consistently give you the hostname of the master:

START TRANSACTION;
SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
SELECT @@hostname;
COMMIT;


How about an indexed view?

CREATE VIEW dbo.LoginsByDate
WITH SCHEMABINDING
AS
  SELECT
    UserID,
    LoginDate = CONVERT(DATE, LoginDate),
    LoginCount = COUNT_BIG(*)
  FROM dbo.LoginTable
  GROUP BY UserID, CONVERT(DATE, LoginDate);
GO
CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate
  ON dbo.LoginsByDate(UserID, LoginDate);
GO

What this does is pre-aggregate the login data for you (you pay the cost at write time), and flattens it into one row per UserID per LoginDate. Depending on how many times a user logs in in a given day, and how often you run these reports, this can be substantially more efficient than calculating those at query time (and much less hassle than running your own nightly report that aggregates the data and stores it somewhere).

Now, to get the total login events by day:

SELECT LoginDate, SUM(LoginCount)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

To get the distinct logins by day:

SELECT LoginDate, COUNT(UserID)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

Of course if you're looking for individual user activity, you can do that too, and you can easily aggregate the data to get whatever "total unique" really means to you. If UserID = 1 logs in today 4 times and tomorrow twice, is that 1 total unique (unique per entire time frame) or 2 total uniques (unique per day)?

If the former:

SELECT COUNT(DISTINCT UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd';

If the latter:

SELECT SUM(c) FROM
(
  SELECT LoginDate, c = COUNT(UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd'
  GROUP BY LoginDate
) AS x;


Are semicolon-delimited Postgres commands run serial or parallel?

All SQL commands in a single batch are executed linearly; that is the first statement is executed, then the second is executed after the first completes.

The only way to get multiple statements to run in parallel is to execute each statement on a separate connection.

So, to confirm, the two statements in your batch will always execute linearly, i.e. the insert will run first, then the update will run.


Design of small tables: clustered index or heap

As for subtables, I have a rule of always adding a primary key. In this case I would go with a new autoincrement column as a PK, as the index will be pretty narrow.

Secondly, I would consider propagating information like IsActive from UserSituation to User with a trigger (I assume user can only have one situation at the given moment). This may eliminate the need for one JOIN in most cases. Not sure it will work with UserCategory (it will not if it's a true 1:N relation).

As for clustered index on UserId, it sounds like a good idea on table User and a not-so-good idea on subtables. But I have to admit it is not my field of expertise.


How to perform multiple joins onto a table and then join resulting table onto multiple columns into another table

You have would have to join twice:
SELECT
  fam.family_id
 ,fam.parent_id_1
 ,parent1.address_id AS parent_1_address_id
 ,parent1.name AS parent_1_name
 ,parent1.job_id AS parent_1_job_id
 ,fam.parent_id_2
 ,parent2.address_id AS parent_2_address_id
 ,parent2.name AS parent_2_name
 ,parent2.job_id AS parent_2_job_id
FROM
  family fam
LEFT JOIN
  person parent1
    ON parent1.person_id = fam.parent_id_1
LEFT JOIN
  person parent2
    ON parent2.person_id = fam.parent_id_2
LEFT JOIN
  job job1
    ON parent1.job_id = job1.job_id
<... over and over ... >

You can use a common table expression to wrap some of the identical code together, but it's the same steps ultimately:
WITH person_detail AS
(
  SELECT
    person.person_id
   ,person.name
   ,<job columns>
   ,<address columns>
  FROM
    person person
  LEFT JOIN
    job job
      ON job.job_id = person.job_id
  LEFT JOIN
    address address
     ON address.address_id = person.address_id
)
SELECT
  fam.family_id
 ,fam.parent_id_1
 ,<parent_1 columns>
 ,fam.parent_id_2
 ,<parent_2 columns>
FROM
  family fam
LEFT JOIN
  person_detail parent1
    ON parent1.person_id = fam.parent_id_1
LEFT JOIN
  person_detail parent2
    ON parent2.person_id = fam.parent_id_2


Is there a standard formula to calculate the optimal resource required by SQL Server base on the Ram size of the server

The link that you pointed out gives a good starting number, which in most cases is sufficient enough as a good configuration.

I would suggest you to leave min memory as DEFAULT and adjust the max memory.

Also, best is to baseline your database server usage during your full business cycle as that will give you the best number based on your workload using below PERFMON counters :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Note: If you are going to use any formula or online calculator to calculate SQL Server memory configuration then best is to read Beaware of Wow… An online calculator to misconfigure your SQL Server memory ! - by Jonathan Kehayias  first.

Some good references :

How much memory does my SQL Server actually need? - by Jonathan Kehayias


Difference in location of transaction isolation level declaration - inside/ouside a transaction?

Below are relevant excerpts from the SET TRANSACTION ISOLATION LEVEL documentation.

  Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

It goes on to say:

  If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Note that there is no mention of BEGIN TRANSACTION, COMMIT, etc. These transaction control statements do not affect the current session transaction isolation level.

I don't see a functional difference with specifying the transaction isolation level immediately after the BEGIN TRAN. Perhaps the developer wanted to make sure the level was obvious to the reader and thought the best place was at the transaction boundary rather than having to view the beginning of the proc code.


Problem granting user privileges via roles in Oracle 12c

You did not mention it, but this is quite typical when using PL/SQL.

Privileges granted through roles are disabled for named PL/SQL blocks that are defined to execute with definer's rights.

How Roles Work in PL/SQL Blocks

  All roles are disabled in any named PL/SQL block (stored procedure,
  function, or trigger) that executes with definer's rights. Roles are
  not used for privilege checking and you cannot set roles within a
  definer's rights procedure.

More details you can find in the documentation referenced above.

Possible workarounds involve granting the privileges directly (as you already did) or define the PL/SQL program unit to execute with invoker's rights.

Another possibility starting with 12c, you can grant roles to PL/SQL program units. So if you create tables with the procedure called p1 using dynamic SQL, you can grant the above role to it as:

grant wertyq_usr_role to procedure p1;

Choose the one that suits your needs.


Clarification on Cloud Computing Database

Nope, you cannot work offline and then sync your changes. But this is a nice idea for a product! :)


What is the maximum number of rows in a clustered index on datetime column?

The number of rows is limited only by available storage. i.e. there isn't one.

Maximum Capacity Specifications for SQL Server

I suspect it might actually be 9,223,372,036,854,775,807 rows (maximum size of a bigint). To put that number of rows into context, if you inserted a billion rows per day, it would take approximately 25,269,512 years to exhaust.

Update: please read comments below, as they talk specifically about the uniqueifier.


Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?

In SQL Server, I would store this as an int, and use CRYPT_GEN_RANDOM(4) to generate cryptographically random numbers for each row.

Here's an example:

DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;

--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;

--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
SELECT @c_base64 = (SELECT CONVERT(varbinary(4), @c) FOR XML PATH(''), BINARY BASE64);
--convert it back to an int
SELECT @c_converted = CONVERT(int, (CONVERT(xml, @c_base64).value('.', 'varbinary(4)')));
--display the original, the string, and the converted string
SELECT original = @c, converted = @c_base64, unconverted = @c_converted;

The results look like:

+-----------+-----------+-------------+
| original  | converted | unconverted |
+-----------+-----------+-------------+
| 629493479 | JYVO5w==  |   629493479 |
+-----------+-----------+-------------+

Why?  Storing integers will consume half the space of storing the converted string.  This may not seem like much on disk, but if you have a table with half a billion rows, every byte counts.  Wasting 2 bytes per row would mean you'd need an extra gigabyte of RAM to scan the table in that scenario.


Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

Maybe, but maybe it's not worth worrying about. Depending on how many rows we're talking about, you might not be able to avoid a scan. How many rows in your table?

An index on the username column won't help this query. At all. Think about a phone book: Very easy to find all the people with the last name starting with Sm, right? Not so easy to find all the people with last names that contains ith (or the first name John, for that matter).

An index might help if you constraint your allowed searches to "starts with" instead of "contains." Which might be a better answer in the long run.

Who's searching for usernames that contain alex, anyway? Is this a common use case? If so you might want to look into alternative mechanisms. In SQL Server we have full-text search; see this StackOverflow question for some ideas on how to solve the same problem in MySQL (but again, keep in mind that it might be a "problem" you don't need to solve).


mySQL throws error, but if copied from error message or die() into phpmyadmin it works

It looks like you are having trouble executing multiple lines of SQL inside the one string. You need to find out of PHP Class can handle running multiple lines.

Instead of getting the SQL to run dynamic SQL, just execute it yourself

$sql = 'SELECT IF(
    (SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = "' . $this->t($table) . '"
             AND table_schema = DATABASE()
             AND column_name = "' . $column . '"
    ) > 0,
    "SELECT 1;",
    "ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';")';

Execute this one line, take that resulting string, and execute that.

Hey, it may means running two SQL commands, but it will work.

An alternative would be to write a stored procedure with that code and then call the stored procedure.

Please look at my answer to the post MySQL: Create index If not exists where I used dynamic SQL to create an index if an index does not exist. You could frame a stored procedure for yourself and call it from PHP.


Count number of common column names among all tables in a database

You probably want schema name along with table name, and here is a way to get the information from the proper catalog views instead of INFORMATION_SCHEMA (more on why here).

;WITH c AS
(
  SELECT
    t = QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
      + '.' + QUOTENAME(OBJECT_NAME([object_id])),
    name
  FROM sys.columns
  WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
)
SELECT
   list1 = c1.t,
   list2 = c2.t,
   commonColumns = COUNT(CASE WHEN c1.name = c2.name THEN 1 END)
 FROM c AS c1
 INNER JOIN c AS c2
 ON c1.t < c2.t
 GROUP BY c1.t, c2.t
 ORDER BY c1.t, c2.t;

SQLFiddle demo


How to connect using PDB userid on Oracle 18c XE?

I have finally found a solution to my problem and I will explain to help other users in same case.

I have no problem to connect to XE instance of Oracle Database using following sqlplus command

sqlplus SYSTEM/password@XE

My first problem is that PDB is not always open and every time that I stop and start DB services, I must restart PDB.

To avoid this, I have executed following SQL command

alter PLUGGABLE DATABASE ALL OPEN;
alter PLUGGABLE DATABASE ALL SAVE STATE;

as explained on asktom.oracle.com

Now every time I stop and restart the Oracle database services, the VIRLUX PDB is correctly started but following sqlplus command continue to return an Oracle error that now is ORA-01017.

sqlplus test1admin/test1pwd@virlux

SQL*Plus: Release 18.0.0.0.0 - Production on Mar. Janv. 14 08:41:41 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
ERROR: ORA-01017: invalid username/password; Logon denied

But, if I enter following command, I'm connected to Oracle PDB !

sqlplus test1admin/test1pwd@localhost/virlux

I think that old well known sqlplus connection command don't work for PDB and that the only solution is to prefix PDB name by hostname that has been defined in TNSNAMES.ORA file.

What is surprising is that a command that return an error's message saying that User/password are invalid is corrected without changind password !

This solution works now well on my PC on Windows Home 10.


I tried the above suggestions, but did not work, such as select SQL did not get results and I still cannot drop the undo table space.  The fundamental issue is that the old undo table space is still registered in Oracle as its undo, therefore we need to change it to the new undo table space.

Here are what I did and it worked:

create a new UNDO table space:

CREATE SMALLFILE UNDO TABLESPACE "UNDO"
DATAFILE '+DATA/t2/datafile/undo_01.dbf' SIZE 10G
REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

Switch the new UNDO to the "official" undo table space:

alter system set undo_tablespace='UNDO' scope=both;

drop the old undo table space:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

hope this helps.


Instead of going through the rabbit hole of finding a fix for the error, I would highly suggest you use dbatools
Copy-DbaLogin

This command migrates logins from source to destination SQL Servers. Supports SQL Server versions 2000 and above. Migrates logins with
SIDs
passwords
defaultdb
server roles & securables
database permissions & securables
login attributes (enforce password policy, expiration, language, etc).

e.g.
Copy-DbaLogin -Source sqlserver -Destination sqlcluster

and there are many more commands that will make your life easier


This turned out to not be a mysql issue at all.  The network team recently installed a new security device that will block packets on certain rules.  A legitimate database write contained a sequence of characters the device deemed nefarious.

The overall connection handshake for replication was able to make it fine but then it just sat there asking the master for the next log entry whose packets were never making it back.

As far as the broken slave was concerned, it was up to date b/c it had executed the most recent event in the relay logs it had received.


How should I model a database structure to store word information on flashcards?

Generally speaking, databases don't store arrays.

It sounds like you need several tables to track different things.

A "users" table which will store the userID (primary key), user's name, and so on.
A "base_word" table to store the base word, a BaseWordID (primary key) and such.
A "Users_Words" linking table. This table would have it's own primary key, a UserID foreign key to link to the Users table, a BaseWordID foreign key link to the base_word table, and a Learnt flag to track learnt or not (possibly other columns, too?)

If they need access to the data "offline," that's not duplicating the data. That's generating a "report" from the data. This might be an HTML page derived from the data that is printed (or printed to PDF), for example. Or an Excel file dump of the data.


Mass data upload from Excel - technology

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user.  Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use:

COPY table FROM 'fpath' CSV HEADER DELIMITER ';'

You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well.

If you must use excel formats like .xlsx, why not use something like Pentaho and develop an application that can connect to any database. There is a java api for this one. Other ETL tools may do the trick too.

In these instances, upload speed is under 10 minutes. My last upload over a non-commercial network of 900000 rows lasted 20 minutes in Pentaho. A straight copy to in PostgreSQL is actually much faster.

As for speed, querying is your major problem. If you are just copying and querying, Postgres is fine. However, if you are running intensive queries, counts; aggregation, use an ETL tool like Pentaho that you can run from Java and connect with a connection pool like BoneCP for Postgres or another connection pooling software for other databases.

Most databases support connection pooling but have connection limits so look at your configuration settings.


MySQL update a record on duplicate key update (merge)

you can use UPDATE like this:
UPDATE `products`
SET `price` = 200
WHERE ID = 2;

or REPLACE like this:
REPLACE into products (id, price) values(2, 200);


SSIS Data Flow Error Output runs all the time

I am looking for the article that describes this behaviour better than I'll do here and will update if I can find it. I thought it was by Kirk Haselden or John Welch but no so luck.

If the batch size is set to 0, default, either the entire data flow will make it in or none of it. Generally, this is what you want as it gives the best performance and consistency.

If you have an exception in in the commit size (full or otherwise) and have set redirect error rows, the error row(s) and all the valid ones will all be mingled in the exception flow.

If your desire is to get all to "good" rows in and spit the "bad" ones to a staging area, the pattern described in the article is to try and commit successively smaller batches into the destination table. Eventually, you'll partition the inserts down to a valid sized batch or find the offending row(s) which are then stored to a staging area for review/cleanup.

The implementation is to have serial error redirect OLE DB destinations with decreasing rows per batch commit sizes, starting at say 10,000 and having the penultimate one with a size of 1. The last OLE DB destination would be to the staging/error table.

All of this assumes that you're using "fast load" version for the OLE provider. If you're "table or view" or "table or view from variable" you're already doing singleton inserts (and probably killing your performance) so the redirect stuff isn't needed to find a bad row.


The best thing you can do to understand this behaviour better is to ask the software vendor. If you don't want to grant those permissions, lodging a bug report might be appropriate.

I'm going to hazard a guess that EMS SQL Manager is trying to be too clever, and check whether you have Alter_routine_priv or Create_routine_priv so that it can return a warning or error before it tries sending your ALTER ROUTINE to the database.

Most SQL Editors run lots of queries in the background to support "IntelliSense", speed up common queries, or provide background information, and a lot of the time they just don't make any sense (some of them are downright frightening).


Migrated a database to mySQL 8.0.17, unable to upgrade or start service

First make sure you are pointed to the right directory in you configuration/settings files - /usr/share/mysql[-8.0]
Then use command
mysqld --upgrade=FORCE

This worked for me.


Open source alternative to SQLMaestro for PostgreSQL

I found awesome tools in PostgreSQL wiki thank for your help guys.

The summary:

PGmodeler: If we mixed pgAdmin with PGmodeler we'll get full alternative to SQLMaestro for PostgreSQL but we'll miss one thing which is "Reverse Engineering" to the database because PGmodeler works in one direction.
As of 2017, importing existing schema / layout (aka. "Reverse Engineering") is possible with PGmodeler!
Power Architect: Supports many database engines but its designer has problem with database syncing "Forward Engineer" in case the developer changed the design of the scheme by another tool (ex. pgAdmin) in addition to slow performance in case of designing big schemes because it uses Java/Swing and finally, the user guide is totally commercial for 99$... damn :(
Open System Architect: Supports many database engines but it seems dead project although it built by Qt (latest commit was in 2010) I couldn't connect to any database because it failed to find unixODBC path although I already installed it in my Mint Linux!
TOra is also worth noting: TOra - An Oracle Toolkit for DBA's and developers. PostgreSQL support is officially not complete - seems to work reasonably well with older psql versions (<= 9.4), while having a segfault now and then. Special: All SQL queries (used to fill the GUI with data) may be edited.

I wish this will be helpful to you.

UPDATE: As of 2017, importing existing schema / layout (aka. "Reverse Engineering") is possible with PGmodeler!


Need to list SQL Windows auth users on each SQL server and rename the domain name

Unless you are actually deleting the user accounts in Active Directory and re-creating them, you don't actually need to do anything to the logins on SQL Server.

SQL Server references the accounts using the Active Directory SID, not the name.

You can test this by renaming an account in Active Directory and attempting to login to SQL Server with it.

Assuming you are using at least a semi-modern version1 of SQL Server, you can use ALTER LOGIN [DOMAIN\xyz] WITH NAME = [NEWDOMAIN\xyz]; syntax to modify the name of a Windows Active Directory login in SQL Server.  You can use the system DMV, sys.server_principals to determine which logins should be modified.  For example:

DECLARE @OldDomain SYSNAME;
DECLARE @NewDomain SYSNAME;
DECLARE @cmd NVARCHAR(MAX);

SET @OldDomain = 'MyOldDomain';
SET @NewDomain = 'NewDomainName';

DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) +
    ' WITH NAME = ' + QUOTENAME(@NewDomain +
    RIGHT(sp.name, CHARINDEX('\', REVERSE(sp.name)))) + ';'
FROM master.sys.server_principals sp
WHERE sp.type_desc LIKE 'WINDOWS_%'
    AND sp.name LIKE @OldDomain + '\%';

OPEN cur;

FETCH NEXT FROM cur
INTO @cmd;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);

    FETCH NEXT FROM cur
    INTO @cmd;
END

CLOSE cur;
DEALLOCATE cur;

The above code will print a list of commands that could be executed to modify the logins for Windows User Accounts and Windows Groups.

You can run the above code against multiple servers by creating a group of servers in the Server Registration Window in SSMS, the right-clicking it and selecting "New Query", as shown here:

This allows you to run any query against multiple servers simultaneously, and see the results in one window.  The results have a "Server Name" column added automatically so you determine where each row came from.

1 - SQL Server 2008+


Sequences are separate objects with separate privileges.

  Granting permission on a table does not automatically extend
  permissions to any sequences used by the table, including sequences
  tied to SERIAL columns. Permissions on sequences must be set
  separately.

Indexes belong to the table. To DROP or CREATE an index you must be the owner of the table (or superuser).

Columns go with table privileges. Granting privileges on the table grants applicable privileges to all columns automatically.

For DDL commands (ALTER TABLE ...) you must own the table again.

Since PostgreSQL 9.1 there are column level DML-grants additionally. But:

  A user may perform SELECT, INSERT, etc. on a column if he holds that
  privilege for either the specific column or its whole table. Granting
  the privilege at the table level and then revoking it for one column
  will not do what you might wish: the table-level grant is unaffected
  by a column-level operation.

More details in the manual about GRANT, where the quotes are from.


How do I determine the cause of a very small table responding to queries slowly?

If multiple threads are issuing the delete/update/select statements concurrently, then blocking is the most likely explanation.
You could use SQL Profiler and filter the trace on TextData Like '%MYTABLE%' to find all the statements accessing the table.

https://stackoverflow.com/questions/4076516/how-to-profile-for-one-table-in-sql-server

However use caution with SQL Profiler in a production environment, it can impact overall server performance.


How to deny access to see the logins in a database

Either if it's a Windows or SQL login, then by default, if it has only the PUBLIC role, won't be able to see anything except SA and himself. No other login (Windows or SQL) on the machine will be visible, if he hasn't some other server permissions.

Example for that behavior (both test logins have only the Public role):

So, if a login can see more than himself, it must've had some privileges assigned. It should be in the login properties.

Some other easy way to see it is by scripting the login: right click -> script login as - create to - and see if it has any server role assigned to it (role assignments are at the end of the script).


Transactional Replication Update process

If you need your combined table updated automatically you would need to put triggers on the 9 tables which have been replicated to your server.

A better option would be to put an indexed view on top of the 9 tables (I'm assuming that your table is created with a single query).  This way there's no need to handle updating your table as the indexed view would always be up to date and you can simply just query it.  As it is an indexed view you can add any non-clustered indexes to it as needed to make performance nice and fast.

If the query to combine the 9 tables together into your schema a normal view should be fine.  Just have your report query the view, and the data will always be up to date.  The big difference between a normal view and an indexed view (above) is that with a normal view the indexes go on the 9 replicated tables where with an indexed view the indexes can be placed on the view directly.

What it comes down to, is that there isn't any need for an additional table.


Oracle 12c Release 2 Database Configuration Assistant installation on CentOS - The port is already in use

Oracle support doc [DBT-06103] The port (5,500) is already in use. ACTION: Specify a free port (Doc ID 2277154.1) says to put the unqualified hostname into /etc/hosts too. i.e:

192.168.10.113 CentOS-Dev-db-12c.Company.local CentOS-Dev-db-12c


Hopefully this should provide an answser:
This implements "common" relational division:
SELECT DISTINCT grpid
FROM groups AS g
GROUP BY grpid, changeDate
HAVING NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g.grpid
        AND gi.changeDate = g.changeDate
        AND NOT EXISTS
            ( SELECT 1
               FROM groups AS gk
               WHERE gk.grpid = gi.grpid
                 AND gk.changeDate <> gi.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    ) ;

Result:
grid
   1
   4
   7

This implements exact relational division, which results in even more complicated code. Your case, where the groupings/versions are determined by two columns (grpid, changeDate) makes it look like even more complicated.
Tested in dbffdle.uk
Query 1:
SELECT DISTINCT g1.grpid
FROM
  ( SELECT grpid, changeDate
    FROM groups AS g
    GROUP BY grpid, changeDate
  ) AS g1
  JOIN
  ( SELECT grpid, changeDate
    FROM groups AS g
    GROUP BY grpid, changeDate
  ) AS g2
  ON  g1.grpid = g2.grpid
  AND g1.changeDate < g2.changeDate
WHERE NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g1.grpid
        AND gi.changeDate = g1.changeDate
        AND NOT EXISTS
            ( SELECT 1
               FROM groups AS gk
               WHERE gk.grpid = g2.grpid
                 AND gk.changeDate = g2.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    )
   AND NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g2.grpid
        AND gi.changeDate = g2.changeDate
        AND NOT EXISTS
            ( SELECT 1
               FROM groups AS gk
               WHERE gk.grpid = g1.grpid
                 AND gk.changeDate = g1.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    )
 ;

Query 2 that uses MySQL's GROUP_CONCAT function:
SELECT DISTINCT grpid
FROM
  ( SELECT grpid, changeDate,
           GROUP_CONCAT( CONCAT_WS('-', userid, hobby, pct)
                         ORDER BY userid, hobby, pct
                         SEPARATOR '  '
                       ) AS groupdata
    FROM groups AS gr
    GROUP BY grpid, changeDate
  ) AS g
GROUP BY grpid, groupdata
HAVING COUNT(*) > 1 ;


MySQL create trigger after insert not inserting all values

I can't reprocuce your behaviour.

CREATE TABLE bookings(ID int AUTO_INCREMENT PRIMARY KEY,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id  int)

CREATE TABLE bookings_clone(ID int ,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id  int)

CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings
FOR EACH ROW BEGIN
    INSERT INTO bookings_clone select * from bookings where id = NEW.id;
END;

INSERT INTO bookings  VALUES(NULL,13,13,13,13,13)

SELECT * FROM bookings_clone

ID | unique_id | branch_id | pos_user_id | user_id | user_address_id
-: | --------: | --------: | ----------: | ------: | --------------:
 1 |        13 |        13 |          13 |      13 |              13

db<>fiddle here
So as long  as you don't have a [mre] without images, you should check if the tables arethe same.


MySQL stress test tool, remote server preferred

The vast majority of database benchmarking tools allow the user to specify a host and a login - Hammerdb does, as does SysBench. The last programme on your first link also allows a host to be specified.

Check out this interesting link for real test datasets.


How to avoid SQLCODE=-968, SQLSTATE=57011 "The file system is full" error with merge statement

Assuming the merge statement needs to use some amount of temp table size, you should increase the file system size where the related user temporary tablespace resides.

To check the path of user temporary tablespace,

check db2diag message that may indicate which file system is related.
And also, db2pd -db <dbname> -tablespace

You may add new user temp tablespace on new path that has enough space for the same page size of related tables.