Error in creating shard cluster mongodb I have followed the mongodb documentation to create a sharded cluster. I have created a config server replica set and shard replica set with 3 members each.Following is the procedure I followed as per the docs:

Step 1: Created config server replica set with 3 members:

mongod --configsvr --replSet "myrepl" --dbpath <path1> --port 27019
 mongod --configsvr --replSet "myrepl" --dbpath <path2> --port 27020
 mongod --configsvr --replSet "myrepl" --dbpath <path3> --port 27021

Step 2: Connected a mongo shell to one of the mongod instances:

mongo --host localhost --port 27019

Step 3: Initiated the replica set in the mongo shell created above:

rs.initiate(
  {
    _id: "myrepl",
    configsvr: true,
    members: [
      { _id : 0, host : "localhost:27019" },
      { _id : 1, host : "localhost:27020" },
      { _id : 2, host : "localhost:270" }
    ]
  }
)

Step 4: Created shard replica sets:

mongod --shardsvr --replSet "shardrepl"  --dbpath <path1> --port 37019
 mongod --shardsvr --replSet "shardrepl"  --dbpath <path2> --port 37020
 mongod --shardsvr --replSet "shardrepl"  --dbpath <path3> --port 37021

Step 5: Connected to one member of the replica set and initiated the replica set:

mongo --host localhost --port 37019
 rs.initiate(
  {
    _id : "shardrepl",
    members: [
      { _id : 0, host : "localhost:37019" },
      { _id : 1, host : "localhost:37020" },
      { _id : 2, host : "localhost:37021" }
    ]
  }
)

Everything is fine till here, the problem is in the next step:

Step 6: Connect mongos to sharded cluster:

mongos --configdb "myrepl"/localhost:27019,localhost:27020

Further it is stated to connect a mongo shell to this this mongos instance using
mongo --host <hostname> --port <port> But it's not explicitly stated which host or port to use here. What host or port should I use here? And where should I execute the further commands to add shards?

Although I had a strong feeling this won't work (and it didn't :p), I tried executing mongo --host localhost --port 27019 and executed sh.addShard("localhost:37019") ,it gave an error- "no such command addShard"

Kindly help me out here,thanks!

This is resolved now. What I did is to start the mongos at a specific port number,like:

mongos --configdb "myrepl/localhost:27019,localhost:27020 --port 47019

Then I connected a mongo instance to it:

mongo --host localhost --port 47019

Further I executed all the commands to add shards in this shell and it worked! Please correct me if I'm wrong


Sql Server agent can not start I can not start Sql Server agent to create job. Can anyone help me? .
I'm using Windows 7 Ultimate , Sql server 2008 Rc2
See this images:

SQL Server Express Edition does not include SQL Agent functionality.

While the installer does register the service entry, the service will fail to start if you try. (I verified with one of my own instances -- the error message I got is exactly the same.)

See this MSDN article for a comparison of features available in each edition of SQL Server 2008 R2.

If it's appropriate for what you need to accomplish, you can use the Windows Task Scheduler instead.


MySQL - Error code 2013. Lost connection to MySQL server during query I did check other posts. Change timeout value...does not help. its something with the server. It seems each time if I trying to insert sth out range, the server just collapse. Its my localhost, Server on my own computer.
Tried to drop a table or other things. None of the code would work.
Restart the computer would work again, but I'd rather not to to that each time I run into problem.
None of the code in Workbench would work, all come back with Error code 2013...
mysql.connector.errors.DataError: 1264 (22003): Out of range value for column 'total_revenue' at row 1
23:02:40    DROP TABLE `a_share`.`income_statement` Error Code: 2013. Lost connection to MySQL server during query  30.000 sec
23:03:51    DROP TABLE `a_share`.`income_statement` Error Code: 2013. Lost connection to MySQL server during query  30.000 sec

Nerver mind. I found solution.
The server crashed on errors, changing any preference would not help.
you gota press WINDOWS + R
and type in Services.msc
Find MySQL80
right click to reboot the server.


Stored procedure to check for missing fields in a table I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... If any record has missing data in those columns the sp should return the recordID and which columns having missing data.

I'm having trouble constructing the final query. I already split the comma separated list into a table variable. and am using a cursor to through each required column

DECLARE columnCursor CURSOR
    FOR SELECT * FROM @columnsTable
  OPEN columnCursor

  DECLARE @currentColumnName nvarchar(256);
  FETCH NEXT FROM columnCursor INTO @currentColumnName
  WHILE(@@FETCH_STATUS = 0)
  BEGIN

    ....

     FETCH NEXT FROM columnCursor INTO @currentColumnName
  END

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this:

DECLARE @columns TABLE
(
    ID INT IDENTITY NOT NULL,
    NAME NVARCHAR(128) NOT NULL
);

Then, using whatever method you have; split the string and populate the table above. After which you could do something like this to get your desired results:

EDIT: Changed the body of the loop based on your comment below.

CREATE TABLE ##results
(
    RecordID INT NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL
);

DECLARE @i INT = 1;
DECLARE @j INT = (SELECT MAX(ID) FROM @columns);
DECLARE @c NVARCHAR(128); -- used to store column name
DECLARE @s NVARCHAR(max); -- to store the command....

WHILE(@i <= @j)
BEGIN
SET @c = (SELECT NAME FROM @columns WHERE ID = @i);

IF @i > 1
    SET @s += 'OR (' + @c + ' IS NULL) ';
ELSE
    SET @s += '(' + @c + ' IS NULL) ';

SET @s = 'INSERT ##results SELECT RecordID, '''
       + @c + ''' FROM some.table WHERE '
       + @c + ' IS NULL; '
@i += 1;
END
EXECUTE (@s);

SELECT * FROM ##results;
DROP TABLE ##results;

Be aware though that depending on the amount of rows you have in your table this could take a while...

I hope this helps you.


How do I write this cross Join in MySQL? It should create a set of columns (ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose) and all permutations of their values. I want the order given to be the order of permutation (permute through all values of ControlRange before incrementing SensorBias). I get syntax errors when running this in MySQL, which worked fine in SQL Server.

USE IMTsimulations
;WITH Five(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
), Four(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
), Three(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
), Two(num) AS
(
  SELECT 5 UNION ALL SELECT 6
)
-- ##INSERT INTO [dbo].[Parameters]
     -- (ControlRange, InitGlucose, ExogDEXCurve, INSSensCurve, INSHalfCurve, VDCurve, SensorError, SensorBias)
     -- ##(ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose)
     -- USE the SELECT to enforce incrementation order

SELECT cr.[num] AS [ControlRange],
       sb.[num] AS [SensorBias],
       se.[num] AS [SensorError],
       vd.[num] AS [VDCurve],
       i_t.[num] AS [INSHalfCurve],
       i_s.[num] AS [INSSensCurve],
       exdex.[num] AS [ExogDEXCurve],
       initg.[num] AS [InitGlucose]
FROM Two cr
CROSS JOIN Three initg
CROSS JOIN Five exdex
CROSS JOIN Five i_s
CROSS JOIN Four i_t
CROSS JOIN Four vd
CROSS JOIN Three se
CROSS JOIN Five sb
ORDER BY ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose

MySQL does not support WITH and does not use [] around identifiers (it uses backticks (`) instead). You will probably have to repeat the right SELECT ... UNION ... as a subquery for each join. Other than that Cross join is the default one if you do not specify any join conditions.

SELECT cr.num AS ControlRange,
       sb.num AS SensorBias,
       se.num AS SensorError,
       vd.num AS VDCurve,
       i_t.num AS INSHalfCurve,
       i_s.num AS INSSensCurve,
       exdex.num AS ExogDEXCurve,
       initg.num AS InitGlucose
FROM (SELECT 0 as num UNION ALL SELECT 1) cr
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2) initg
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) exdex
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) i_s
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) i_t
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) vd
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2) se
CROSS JOIN (SELECT 0 as num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) sb
ORDER BY ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose

http://sqlfiddle.com/#!9/9eecb7d/27829 (hope I did not do any mistake when rewriting it)

You could instead create tables Two, Three, Four and Five if you use it often, but there is soe overhead in actually storing it.


AlwaysOn High Availability doesn't work , can't activate it I am trying to activate the high availability
on my SQL server and getting below message:

SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169
(X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3
 (Build 9600: ) (Hypervisor)
Operating System: windows server 2012.

Appreciate any help.

You need Enterprise edition for fully functional Always ON AG and a minimum Standard edition to use the basic Always On AG. Express edition of SQL Server does not supports Availability Groups


How do I judge whether a given sql statement has sql injection risk or not Analysis the sql comes from sql log only, trying to find out which sql statement has risk of sql injection. it is different from the perspective of application side check.

Analysis the sql comes from sql log only, trying to find out which sql statement has risk of sql injection.

By the time the SQL reaches your database, it's probably too late to tell.
By the time the SQL reaches the sql log (having been executed), it's definitely too late!
If you see lots of placeholders ('?' or ':name'), then your SQL is using Prepared Statements and, therefore, is probably safe from SQL Injection.
If you see lots of literal values, your database has no way of knowing how they got there.
For example, both of the following would reach you looking exactly the same but, as you can clearly see, one is clearly vulnerable.
$sql = "update table1 set name = 'fred' where id = 76543 ";

$name = $_POST['name'];
$id =   $_POST['id'];
$sql =  "update table1 set name = '$name' where id = $id ";

SQL Injection is an Application problem.  Plain and simple.
Point your Developers in the direction of this Accepted Answer, over on Software Engineering, particularly the Security section.


MySQL Syntax Error using FROM I am getting the following syntax error when running the below query.

  Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM shorewarecdrarchive.connect         LEFT JOIN     shorewarecdrarchive.call ' at line 28

Line 28 is the FROM part of the query.

SELECT
    call.Extension,
    -- agentactivity.AgentDN,
    -- agentactivity.AgentFirstName,
    -- agentactivity.AgentLastName,
    -- agentactivity.WorkgroupDN,
    -- agentactivity.WorkgroupName,
    call.DialedNumber,
    connect.PartyID,
    call.CallerID,
    connect.PartyIDName,
    connect.PartyIDLastName,
    -- agentactivity.StartTimeStamp,
    call.StartTime,
    date(call.StartTime),
    time(call.StartTime),
    hour(call.StartTime),
    call.EndTime,
    -- agentactivity.EndTimeStamp,
    call.CallType,
    call.ID,
    connect.PartyType,
    connect.ConnectReason,
    connect.TalkTimeSeconds,
    connect.duration,
    call.duration,
    -- agentactivity.State
FROM shorewarecdrarchive.connect
LEFT JOIN
    shorewarecdrarchive.call ON connect.CallTableID = call.ID
-- LEFT JOIN
-- shorewarecdrarchive.agentactivity ON call.Extension = agentactivity.AgentDN
WHERE
-- StartTimestamp >= '2016-12-05 08:20:00'
-- AND StartTimestamp < '2016-12-05 08:40:00'
-- AND PartyIDName NOT IN ('Overhead Paging','Voice Mail','Voice Mail Login','Door','Door Intercom','Main Line Hunt Group','Main Line','Sales','ACT','Auto-Attendant','Night Bell','Backup Auto-Attendant','Dispatch','North', 'North Lunch')
-- AND WorkgroupName NOT IN ('Dispatch' , 'Main Line')
-- AND CallType between 1 and 3
-- Calltype 1 = Extension to extension call.
-- CallType 2 = A trunk is the originating party. (Inbound calls)
-- Calltype 3 = An extension is originating and a trunk is called. (Outbound calls)
-- CallType 4 = A call originating from a trunk and going to a trunk. (Tie trunks)
-- AND agentactivity.WorkgroupName = 'Sales' or'Pre-sales' or 'QFC Orders'
-- and call.id = '402300'
call.ID = '612916'
order by call.ID ASC;

Can anyone take a look and let me know what I am not seeing; I am pretty sure it's something simple, though I am having one of those 'to close to the painting' situations.

Change

    call.duration,
-- agentactivity.State
FROM shorewarecdrarchive.connect

to

call.duration --removing the comma
-- agentactivity.State
FROM shorewarecdrarchive.connect

or

call.duration,
agentactivity.State --uncommenting this line
FROM shorewarecdrarchive.connect

The problem is the trailing comma at the end of call.duration


How to save Outlook attachment in a drive with a new name and overwrite if it already exists? I am using below VBA script to download attachments from email and save it to my windows directory.
I need help to modify this script - 1) to save rename the attachment before saving it 2) Overwrite the file if it already exists. Kindly help.
Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)
Dim oAttachment As Outlook.Attachment
Dim sSaveFolder As String
sSaveFolder = "F:\RBL Attachments\"
For Each oAttachment In MItem.Attachments
oAttachment.SaveAsFile sSaveFolder & oAttachment.DisplayName
Next
End Sub

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim obj As Outlook.Attachment
Dim saveFolder As String
saveFolder = "D:\EmailAttachments"
     For Each obj In itm.Attachments
          obj.SaveAsFile saveFolder & "\" & obj.DisplayName
          Set obj = Nothing
     Next
End Sub


Why do Indexed Views not permit non-unique clustered indexes? I've been looking into using Indexed Views to increase performance on a few of our most commonly used views.

However Indexed Views do not support non-unique clustered indexes which goes a little against the precedence set by the rest of the database structure.

For example, here is a simplified version of a couple of our tables.

-Groups-
Group ID    GroupName

-Users-
UserKey    UserName    FullName     GroupID

The indexes are on Groups.GroupID (Non-clustered) and Users.GroupID (Clustered). The clustered key being on GroupID in the Users table as most commonly a range of users from a specific group would be retrieved.
Obviously you would have multiple users per group, so this clustered index is non-unique.

This leaves me a bit uncertain of how to follow this precedence when indexing my views such as this example, as I cannot have a non-unique clustered index.

ConsumableID    ConsumableVariantID AllowThresholdOverwrite FullPath    GroupID ManufacturerID  Type    ModelID
101              29                 1                       0.1.2.4.    4       3               3       2

In actuality the only value on this View which would always be unique is the ConsumableID column, so I am left with little choice as of where to place my index.

Why do Views not permit non-unique clustered indexes when regular tables do?

The following explanation is given in this Microsoft Technical Article:

  Why does the first index on a view have to be CLUSTERED and UNIQUE?
  It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be clustered because only a clustered index can enforce uniqueness and store the rows at the same time.

SQL Server uses a system of delta algebra to keep indexed views in step with the base data. It also automatically incorporates view-maintenance query plan operators for each DML query that affects one or more indexed views. Having a unique clustered index on the view greatly simplifies the implementation details.

The current arrangement allows for fixed-shape maintenance operator tree shapes to be incorporated in the base DML query tree, providing orthogonality that also simplifies testing. Ultimately, indexed views could be enhanced one day to support non-unique clustered indexes, but then again all things are possible given unlimited time and boundless resources (neither of which apply to the SQL Server development team as of the time of writing).

For an example showing how complex update query plan building can get, and how easily subtle bugs can creep in, see this example of a bug that occurs with MERGE and filtered indexes (a feature that has a close connection to indexed views).


Can we retrieve historical database growth report for MySQL? Can someone share a method on how can I retrieve the database growth report from the date of creation till current for MySQL? Coming from Oracle background, I understand for Oracle we can do a script to query the creation date or from the snapshot. But I don't now if MySQL has a similar table/view which I can query and check from.

You cannot obtain the info about DB size in the past - server do not store those information.

You can obtain the info about current DB size. It can be obtained from the INFORMATION_SCHEMA.FILES system pseudotable. You can shedule the get of this info in some table using CREATE EVENT, so You will see the dynamic of size change.

But I think (especially assuming You use InnoDB engine with file_per_table enabled) the best way is to obtain DB size info directly from the OS' filesystem (and shedule it using OS' sheduler - cron or another). Of course, You can store that info in MYSQL server table :) - it's not a problem to add it via command-line client.


Byte ordering for multibyte characters in SQL Server versus Oracle I am currently in the process of migrating data from Oracle to SQL Server and I'm encountering an issue trying to validate the data post-migration.

Environment Details:

Oracle 12 - AL32UTF8 character set
Client - NLS_LANG - WE8MSWIN1252
VARCHAR2 field

SQL Server 2016

Latin1_General_CI_AS collation
NVARCHAR field

I'm using DBMS_CRYPTO.HASH on Oracle to generate a checksum of the whole row, then copying to SQL and using HASHBYTES to generate a checksum of the whole row, which I'm then comparing to validate the data matches.

The checksums match for all rows, except those with multibyte characters.

For example, rows with this character: ◦ do not match in the checksums, even though the data is transferred correctly. When I use DUMP in Oracle or convert to VARBINARY in SQL Server the data matches exactly except for the bytes for this character.

In SQL Server, the bytes are 0xE625 and in Oracle they are 0x25E6.

Why are they ordered differently, and is there a reliable way to convert one to the other to ensure the checksum at the other end matches for strings with multi-byte characters?

What you are seeing is Little-Endian encoding that SQL Server uses to store Unicode characters (more precisely, it uses UCS-2 LE).

More on Little-Endian here: Difference between Big Endian and little Endian Byte order

I don't know how it was possible that

  When I use DUMP in Oracle or convert to VARBINARY in SQL Server the
  data matches exactly except for the bytes for this character

All the Unicode characters stored in SQL Server, converted to binary, are "inverted", I mean, to see the real codes you should divide them in groups of 2 bytes and invert the order within every pair.

Example:

declare @str varchar(3) = 'abc';
declare @str_n nvarchar(3) = N'abc';

select cast(@str as varbinary(3));
select cast(@str_n as varbinary(6));

The result is

0x616263

0x610062006300

As you see in case of Unicode characters bytes are inverted:
"a" is represented as 0x6100 and not as 0x0061.

The same story is about 0x25E6 that is real Unicode code while in binary representation in SQL Server you see it as 0xE625, i.e. inverted.


How do you get the default compatibility level in Azure SQL Database from T-SQL? In on-prem SQL Server you can check the server version and multiply by 10 to get the maximum compatibility level supported by the server, or check the compatibility level of the model database to get the default compatibility level new databases will be created with.

In Azure SQL Database, neither of these work; the server version stays at 12 even though the maximum compatibility level is 140, and there isn't a model database to check against.

How do you find out what the current default compatibility level is in Azure SQL Database, from T-SQL? In other words, I want a T-SQL query that would have returned 130 if run before 18th Jan 2018, returns 140 if run today, and will return 150 whenever Azure updates to a default compatibility level of 150.

I couldn't find anything specifically documented for this, but:
SELECT SERVERPROPERTY('ResourceVersion');

...currently returns "15.00.2000" for Azure SQL Database, which may be useful to you.
I had thought you might be able to query:
SELECT H.[name]
FROM sys.dm_exec_valid_use_hints AS H
WHERE H.name LIKE N'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_%';

...but that returns:

name

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160

...which are all valid hints, but 150 is the maximum you can set database compatibility to at the time of writing.


Postgres how to drop table owned by another user without mirroring that user's Permissions User A owns all of our tables and has access to all of our schemas

User B needs full access including Drop and Alter permissions to some of those tables and schemas but not all of them.

As is well documented, Drops and Alters are not possible because User B does not own the tables.

UserB => DROP TABLE userATable;
ERROR:  must be owner of relation test

The recommendation in this case seems to be to make Role B a member of Role A.

grant UserB to UserA;

But we cannot do this because then UserB will have access to schemas for which it should not.  What are recommended strategies to handle this?

make the tables be owned by role A and grant role A to role B (or make them owned by role C and grant C to both A and B)


Query Store System View JOINs In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats?  I don't see any obvious common ID columns and INFORMATION_SCHEMA.COLUMNS doesn't help either.  I'm trying to beef up a simple querystore text search query to include usage and performance detail from the runtime_stats view.
Here's what I'm using currently, which works fine to identify queries containing a search string:
DECLARE  @SearchString NVARCHAR(100)=''
SELECT q.query_id, q.query_text_id, t.query_sql_text, object_name(q.object_id) AS parent_object, q.last_execution_time
FROM sys.query_store_query_text t JOIN sys.query_store_query q
   ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE '%' + @SearchString + '%'

I want to add a handful of the stats from sys.query_store_runtime_stats to this, if it's doable.

A "query" one or more "plans", linked by sys.query_store_plan.  Each "plan" has multiple rows in sys.query_store_runtime_stats.  If you turn on Profiler while running the built-in Query Store reports you can see lots of examples.  EG
exec sp_executesql N'SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC',N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@results_row_count=25,@interval_start_time='2021-04-16 10:13:43.0578184 -05:00',@interval_end_time='2021-04-16 11:13:43.0578184 -05:00'


How to handle queries of 500M+ items The structure of my data is the following:

date: <timestamp>
filter_a: <integer> -> range [0, 1000]
filter_b: <integer> -> range [0, 1000]
filter_c: <integer> -> range [0, 86400]
filter_d: <integer> -> range [0, 6]
group: <string>
second_group: <integer>
variable_a: <float>
variable_b: <float>
variable_c: <float>
a couple more no very important

I need to perform the following queries:

First:

Filter data by date, filter_a, filter_b, filter_c and others

Second, with the filtered data:

count all records
get average of variable_a, variable_b and variable_c
get standard deviation of variable_a, variable_b and variable_c
get quartiles  of variable_a, variable_b and variable_c
group data by group or second_group and aggregate(Count, Avg, Std, ..)

The number of the system's users is about 10 or 15, but the number of items is huge, right now it is 70M but it will be 500M in a couple of weeks and it will be 1000M in about a year.

The number of queries is small, no more than 10 users concurrently, my problem is how to handle those queries with this huge amount of data.

What have I tried so far?

I started with mongodb, at the beginning it was fast but it became slow when calculating quartiles with 10M+. It improved when I added indexes but it didn't help very much when I had to query all data. I started using mongodb because data was very dynamic but luckily the data format "isn't going to change anymore".
As filter_a and filter_b could be seen like nodes, I tried neo4j. I liked it neo4j very much but my graph had A LOT of edges so that queries wasn't very fast.
Finally, since data format isn't going to change and it is only one collection/table so needs no joins in SQL, I checked postgresql. My tests has been faster with postgresql, but I'm scared it could not scale properly in the future.

What do I need?

Is postgresql a good choice for this case?
Is there another kind of database I could use? which one is the best for this case?
What else could I do to improve it?

Edit

About 1M of elements are inserted every day and "should not change" along the time.
Write speed is not important
The hard requirement is to read/aggregate fast

Thanks!

Instead of leaning on a relational database to perform these statistical calculations on time-series data, I'd suggest that you move this math and post-processing work outside of the database into a client application.

Using a scripting language like Python or Ruby, you can incremental solve the problem by querying for "chunks" of data over a fixed-width period of time, compute an intermediate statistical summary, and then combine the results across multiple chunks, as you loop over the whole history.  Some statistical measures are hard to combine across chunks, but something like Avg() only needs sum() and count() per chunk, O(1) vs. O(chunksize), so chunk-merging may scale well.


Inserting into a table a row represented by JSON I've been laboring away trying to create a procedure which can data JSONB (or JSON, doesn't really matter), a table, and convert the JSON to a record and insert that record into the table. This is (roughly) the closest I've come to getting a functional prototype:

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, table_type anyelement) RETURNS VOID
    SECURITY INVOKER
    LANGUAGE plpgsql
AS $$
    DECLARE
        record RECORD;
        record_type RECORD;
    BEGIN
        EXECUTE format('SELECT x.* FROM (SELECT a.* FROM CAST(NULL as %s) a) x', pg_typeof(table_type))
            INTO record_type;
        record := jsonb_populate_record(record_type, data);
        INSERT INTO users SELECT (record).* RETURNING id;
    END;
$$;

So let's say that I do the following:

SELECT fn_alter_entity('{"a": "b"}'::JSONB, users);

I receive the following error:

ERROR: record type has not been registered
SQL state: 42809
Context: SQL statement "INSERT INTO users SELECT (record).* RETURNING id"
PL/pgSQL function fn_alter_entity(jsonb,anyelement) line 9 at SQL statement

Now, obviously the reason I'm receiving that error is that record_type is not actually a real record type. Now, normally we would just do something like jsonb_populate_record(data, NULL::users) or jsonb_populate_record(data, NULL::events) or the like. But what if I want that record type to be dynamic? Is there any possibility? I feel like I'm on the verge of nailing this down, but just can't quite get over the hump. All I want to do is be able to transform JSON into a record.

I think you want something like this.

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, t TEXT )
  RETURNS VOID
  SECURITY INVOKER
AS $$
    BEGIN
        EXECUTE FORMAT(
          'INSERT INTO %I SELECT * FROM jsonb_populate_record( null::%I, $1);',
          t,
          t
        )
        USING data;
    END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int, b text );

SELECT fn_alter_entity( '{"a": 5}', 'foo' );
SELECT fn_alter_entity( '{"a": 42}', 'foo' );
SELECT fn_alter_entity( '{"a": 5, "b": "foo"}', 'bar' );
SELECT fn_alter_entity( '{"a": 42, "b": "baz"}', 'bar' );

test=# TABLE foo;
 a
----
  5
 42
(2 rows)

test=# TABLE bar;
 a  |  b
----+-----
  5 | foo
 42 | baz
(2 rows)

But seeing where you may be going, I would highly suggest you look into PostgREST.


Exception when starting Snapshot Agent when setting up database replication I am trying to set up replication between databases. I followed these tutorials:

Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)

and after:

Tutorial: Configure replication between two fully connected servers (transactional)

I went through first without any problems. Almost completed the second, but I got stuck on step View the status of snapshot generation

When I try to start the Snapshot Agent, I get an error:

  Exception Message: An unspecified error had occurred in the native SQL Server connection component.

I tried to search on the internet MSSQL_REPL55012 (that's error code I get), but didn't succeed.

I tried to look in SQL Profiler, Jobs history, Replication Monitor, even ran failing job step from PowerShell, but couldn't get any more info on that.

What might be the issue? Anybody else stumbled upon that?

I am using:

SSMS version 15.0.18206.0
SQL Server engine 15.0.2000.5

Also, I run two instances of SQL Server and have two Management Studios installed, if that's relevant.

I finally got it resolved. The issue was missing OLE DB driver.
I just downloaded latest driver. Then I got msoledbsqlr.rll driver in directory C:\Windows\SysWOW64\1033. Then I copied to C:\Program Files\Microsoft SQL Server\150\COM directory, so it can be found by snapshot.exe, which is executable run by Snapshot Agent.
Alternatvely, one can define environmental variable to point to C:\Windows\SysWOW64\1033 direcotry, so it will be found there.
Another issue might be that 64-bit SQL tries to use 32-bit drivers (msoledbsql.dll, msoledbsqlr.rll), but it wasn't issue in my case.


Automating SQL Server Merge Replication Processes Hi all is there anyway I can automate the entire process of the Merge Replication?

There are some sps (stored procs) on SQL Server, that can be used to setup merge replication.

Enabling merge replication on a db: sp_replicationdboption  'db name', 'merge publish', true

Creating a merge publication: exec sp_addmergepublication

Adding snapshot agent job: [exec sp_addpublication_snapshot ....

Adding each article: exec sp_addmergearticle

Configuring added/removed columns: exec sp_mergearticlecolumn (if needed only, by default, all columns are part of publication)

However, I am not sure of Transactional and other types of replication. Please let me know, if this is not what you intended.


Percona XtraDb cluster auto increment Help me please clarify a very important question on which I can't find the answer. It only can be proven with tests.

Let's say we have XtraDB Cluster (or Galera Cluster) with three nodes. We have main user' table with the auto-increment primary key.

Ids in this table on the first node goes like 1,4,7,10...
In the second node - 2,5,8,11...
In the third node - 3,6,9,12...

After some time we have that the first node has 10000 inserts, so its auto-increment value is around 30000.
But two other nodes were less loaded, so they have only about 100 inserts and their auto increment value is around 300.

Now we decided to add two more nodes to the cluster. Now the auto-increment offset is equaled five.
Obviously, two lesser loaded nodes can't fill the numbers from 300 to 30000 with their new offset, right?

So what will happen to the table's auto-increment on each node? How will the cluster behave?

Is it gonna start the auto-increment from 30000 for every node and unfilled ids from 300 to 30000 will remain unfilled?

Thank you for your time and experience.

We've made some tests. It turns out that auto-increment thing works completely different, so my concerns were meaningless.

With three nodes each insert into the table updates auto-increment index of a table completely. So you're always have not used values, only in rear cases where two inserts from different nodes are coming simultaneously then ids can appear one after another.

This brings us to another thought. If we have five nodes we're gonna be out of INT(11) five times sooner than with an ordinary non-cluster table.


mysql products db design I am building a products site with php and mysql(no shopping cart).

I have 400k products, the main site with 2 methods for query.

query for products types, all the query by menu, cloud tags (no custom form search)
query for products details, custom form search, search with products name, details.

Each products have these fields: name, details, add_date, catalog, brand. The main problem is each products would have multi catalogs. The total catalogs have more than 600. And each product would have 3-10. For example:

(name) SAUCONY LADY PROGRID RIDE 3 RUNNING SHOES
(details) This ProGrid Ride 3 is a neutral running shoe designed for the keen female runner who is competing at a decent level.
(add_date) 1333128354
(catalog) sport women USA pink leather
(brand) nike

So in my require, how to design this db?

I am not good at mysql, I have 2 ideas.

Use Innodb, make 2 dbs

db1

id | name      | details      | add_date   | catalog             | brand
1  | some name | some details | 1333128354 | 101 201 301 401 501 | nike
2  | some name | some details | 1333128355 | 101 202             | nike
3  | some name | some details | 1333128357 | 102 202 334 401     | puma

db2

cid | catalog
101 | sport
201 | man
202 | woman
301 | USA
302 | Franch

Use select * from db1 where catalog like '%101%' to get all the sport shoes, use Rlike to do the custom form search.

Use MyISAM, make 3 dbs

db1

id | name      | details      | add_date   | brand
1  | some name | some details | 1333128354 | nike
2  | some name | some details | 1333128355 | nike
3  | some name | some details | 1333128357 | puma

db2

pid | catalog
1   | 101
1   | 201
1   | 301
1   | 401
1   | 501
2   | 101
2   | 202
3   | 101
3   | 202
3   | 334
3   | 401

db3

cid | catalog
101 | sport
201 | man
202 | woman
301 | USA
302 | Franch

Use SELECT * FROM db1 INNER JOIN db2 ON db1.id=db2.pid WHERE db2.pid='101' to get all the sport shoes, Add fulltext index to db1.name and db1.details, use fulltext search to do the custom form search.

Or other better ideas? I need a fast way and occupy lower CPU and RAM. many thanks.

I am not a database designer.

You don't make dbs but tables.

Both of your table structures can be implemented as innodb tables and as MyIsam tables.

Your first idea is not a good design. It violates first normal form,
so i would propose the second idea with innodb.
the table db1 can be decomposed further in two tables

id | name      | details      | add_date   | brand
1  | some name | some details | 1333128354 | 1
2  | some name | some details | 1333128355 | 1
3  | some name | some details | 1333128357 | 2

id | brand
1  | nike
2  | puma


MyISAM table row size is bigger than the sum of all the columns size? I have a table:

CREATE TABLE `playlist` (
  `pid` int(11) unsigned NOT NULL,
  `mid` bigint(21) unsigned NOT NULL,
  `status` tinyint(1) NOT NULL,
  `add_time` int(11) unsigned NOT NULL,
  `modify_time` int(11) unsigned DEFAULT NULL,
  `filename` char(255) NOT NULL,
  `source` tinyint(3) unsigned NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

From show table status, I got the "Avg_row_length: 788". But the sum of the all the columns is 277 bytes.So I am confused that why the real row size is much larger then the sum of all columns'size?

I hexdump the data file and find the tail of the row is padded by space char:

00000000  fd 63 00 00 00 f2 3c 08  00 00 00 00 00 01 33 f4  |.c....<.......3.|
00000010  c7 4b 33 64 c8 4b 31 32  36 39 35 37 35 37 34 37  |.K3d.K1269575747|
00000020  38 31 37 2e 6d 70 33 20  20 20 20 20 20 20 20 20  |817.mp3         |
00000030  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20  |                |
*
00000310  20 20 20 01                                       |   .|
00000314

MySQL add many spaces into the tail of the row, I don't know why there are so many additional space characters.

That's because you're using a CHAR column type; this is a fixed-width column; regardless of how much data you put in there, it'll always take up exactly that number of characters in the data store.  It is designed for columns where you'll always have exactly that number of characters, because it's more efficient in that specific case.

What you want in your case is VARCHAR; it'll make a note in the column data of how many characters you're storing, and only take up that amount of space in the data store.


Distinguish between schema and database I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code:

DECLARE
    v_tmp_host scan.location.alias%TYPE;
    v_ip_address character varying;
BEGIN
    -- Some assignment to v_ip_address

    v_tmp_host := v_ip_address::scan.location.alias%TYPE;

Since the code was working for a long time, I investigated and found out, that "scan" is a schema in the database this trigger exists in and someone created a database called "scan" some days ago. Since now "scan" is a db as well as a schema, pg seems to use the db instead of the schema.

My question is, how can I avoid such problems in future? How should I write this code to don't get broken by creating additional relations, databases or schemas?

Or in other words: How can I force pg to use the schema and not the db in this situation?

You have to declare your variable as the desired type.  The %TYPE construct is for copying types, therefore it is usable in the DECLARE section only.  In return, the assignment will be simpler:

DECLARE
    v_tmp_host scan.location.alias%TYPE;
    v_ip_address character varying;
BEGIN
    v_ip_address := '127.0.0.1';
    v_tmp_host := v_ip_address;
    [...]

The only change to your code is that I completely omitted the cast from the assignment.  In this case, PostgreSQL won't try to resolve scan as a database name, since cross-database references are not implemented...

If you declared your variable with a certain type, assignments will try to cast the given value to that type.  In case of failure you'll get an error.  Since normal inet input is just a single-quoted string, a varchar will be accepted (if its format fits the inet input format).


change SSIS package protection level before deployment I'm developing SSIS project locally using project deployment model. I use SQL Server Authentication to connect to remote server. During this period Protection level is set to EncryptSensitiveWithUserKey.

However, upon deployment this package will be executed in a completely different environment, using SSPI security context and with environment variables from SSIS catalog.

This means that right now, before deployment I have to manually change Protection level to DontSaveSensitive inside a project and every package in it. Only after that tedious procedure I deploy the project to remote server.

I cannot keep DontSaveSensitive locally because every time I run a package it will ask me for a password. I cannot deploy with EncryptSensitiveWithUserKey either, because it gives error saying it cannot decrypt some of the values (passwords).

My question is how can skip changes of protection level manually every time I deploy a package? How can I automate this?

Since you're in project deployment mode, which means you are deploying to the SSISDB, there's no need for what you're doing ;)

When you deploy, the isdeploymentwizard.exe is handling all that for your

As part of the deployment, the "Changing protection level" step switches the packages to ServerStorage level


Postgres - how to investigate a dependency before DROPing a field I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with CASCADE?

My table is defined as (edited for brevity) -

CREATE TABLE public.positionfix
(
    fixid3 integer,
    fixid bigint NOT NULL DEFAULT nextval('positionfix_fixid_seq'::regclass),
    messageid bigint NOT NULL,
    fixtime timestamp with out timezone NOT NULL
) PARTITION BY RANGE (fixtime)
WITH (
    OIDS = FALSE
)
TABLESPACE fastspace;

I'm trying to remove the field 'fixId3' using the command -

ALTER TABLE positionfix  DROP COLUMN fixId3;

The error messages I get are -

ERROR:  cannot drop table positionfix column fixid3 because other objects depend on it
DETAIL:  default for table positionfix column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_201909 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190926 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190927 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190928 column fixid depends on sequence positionfix_fixid_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

All the tables mentioned in the errors are partitions of the parent table. The issue is that the error message mentions other objects dependent on "fixId3", but the detail messages refer to a different field "fixId"

There should be nothing that's dependent on "fixId3".

My questions are

1) Based on the messages above, which additional objects will postgres delete if I attempt to drop "fixId3" with the cascade option? Field "fixId"? the sequence? both? Something else?

2) why is postgres listing a dependency between "fixId" and the sequence  "positionfix_fixid_seq" when the field being deleted is "fixId3"?

3) how do I remove "fixId3" without risking accidentally removing "fixId" instead. (I am reluctant to try CASCADE before I understand the consequences.

Ok, got an answer from a related question I raised a year ago. Although the sequence is used by 'fixId' it is owned by 'fixId3'

So by running this command.....

ALTER SEQUENCE POSITIONFIX_FIXID_SEQ owned by positionFix.fixId;

I can then run the DROP command without any dependency issues.


Average Value not working I have a database of students and grades across varying report cycles. Each Report Cycle has a unique ID and each subject also has a unique ID. Students can have more than one teacher per subject so I need to average the grades given and produce just the averaged grade for their attainment scores. An example of the raw data is below:

+--------------+------+-----+----+-------------+---+
| 223599152142 |   12 |  92 |  3 | Mathematics | 0 |
| 223599152142 |   12 |  92 |  3 | Mathematics | 3 |
| 223599152142 |   12 |  92 |  7 | History     | 3 |
| 223599152142 |   12 |  92 | 12 | Economics   | 3 |
| 223599152142 |   12 |  92 | 12 | Economics   | 4 |
| 223599152142 |   12 |  92 | 26 | Latin       | 2 |
| 223599152142 |   12 | 109 |  3 | Mathematics | 3 |
| 223599152142 |   12 | 109 |  3 | Mathematics | 4 |
| 223599152142 |   12 | 109 |  7 | History     | 3 |
| 223599152142 |   12 | 109 | 26 | Latin       | 2 |
| 223599152142 |   12 | 109 | 26 | Latin       | 3 |
| 223599152142 |   12 | 110 |  3 | Mathematics | 4 |
| 223599152142 |   12 | 110 |  7 | History     | 2 |
| 223599152142 |   12 | 110 |  7 | History     | 3 |
| 223599152142 |   12 | 110 | 26 | Latin       | 2 |
| 223599152142 |   12 | 110 | 26 | Latin       | 3 |
+--------------+------+-----+----+-------------+---+

I wish to average the GradeTransposeValue based on the SubjectID for each ReportCycleID, but I am getting odd results. I have set up the below query to just focus on one student and one report cycle to show the problem.

SELECT intSubjectID, txtCurrentSubjectReportName,
       CAST(AVG(intGradeTransposeValue) AS decimal (5,2)) AS avg_attainment
FROM VwReportsManagementAcademicReports
WHERE intReportCycleAcademicYear = 2017
     AND intNCYear >6 AND intGradeID = 1
     AND txtSchoolID = 223599152142
     AND intReportCycleID= 110
GROUP BY intSubjectID, txtCurrentSubjectReportName

This is producing this result

+--------------+-----------------------------+----------------+
| intSubjectID | txtCurrentSubjectReportName | avg_attainment |
+--------------+-----------------------------+----------------+
|            7 | History                     | 2.00           |
|           26 | Latin                       | 2.00           |
|            3 | Mathematics                 | 4.00           |
+--------------+-----------------------------+----------------+

However, if we look at Latin as an example, the original values were 3 and 2, so 3+2 = 5 / 2 should = 2.5 and not 2.

Any help on this would be much appreciated.

You can't get more precision from an INT value than a whole number. You need to make them decimals before calculating:

CREATE TABLE [grades]
([intGradeTransposeValue]      INT,
 [txtCurrentSubjectReportName] NVARCHAR(50)
);

INSERT INTO [grades]
([intGradeTransposeValue],
 [txtCurrentSubjectReportName])
VALUES (2,'latin'), (3, 'latin');

--Returns whole number
SELECT AVG([intGradeTransposeValue])
FROM   [grades];

--Returns 2.5
SELECT AVG(CAST([intGradeTransposeValue] AS DECIMAL(5,2)))
FROM   [grades];

Ideally you'd just store the grades as DECIMAL values to begin with, but the above works in a pinch, too.


Update a view with daily tables in Postgres I have a view like this:

CREATE OR REPLACE VIEW some_table AS
select * from h20180101
union all
select * from h20180102
union all
select * from h20180103
etc...

It's a union daily tables. The thing is each day a new table is created. Is there a way in PL/PgSQL to automatically update the script daily to make a union to the table of the previous day?

Thanks!

If you are on postgresql 10, the best way to handle this is to use partitioning so you have a single table instead of a view. On lower versions you can do partitioning, but it is not declarative (i.e. you have to do a lot of the work yourself).

Can't think of a way to do this from within Postgres/Pl/PgSQL, but you could do something like this:

Create a shell script that you run in psql to output the list of tables. Something like psql -c "\dt h2018*" or the equivalent query looking at system tables
Use the output to send it to AWK, or your favorite scripting language, and then build a new .sql file and then call that with psql. You likely can do it with a scripting language completely to build the query, but likely the above is the easiest and fastest way to accomplish what you want.


Find out the number of rows/columns changed - SQL Server? I found row/colmodctr options in sys.sysindexes but this has no equivalent in sys.indexes. Because sys.indexes is the recommended catalog view how can I find row and column change number without using sys.sysindexes?

Because sys.indexes is the recommended catalog view...

The reason behind non-using of row/colmodctr is another one (you can find it in sys.sysindexes (Transact-SQL) article:

  In SQL Server 2000 and earlier, the Database Engine maintained
  row-level modification counters. Such counters are now maintained at
  the column level. Therefore, the rowmodctr column is calculated and
  produces results that are similar to the results in earlier versions,
  but are not exact.

  If you use the value in rowmodctr to determine when to update
  statistics, consider the following solutions:

  Do nothing. The new rowmodctr value will frequently help you determine
  when to update statistics because the behavior is reasonably close to
  the results of earlier versions.

  Use AUTO_UPDATE_STATISTICS. For more information see, Statistics.

  Use a time limit to determine when to update statistics. For example,
  every hour, every day, or every week.

  Use application-level information to determine when to update
  statistics. For example, every time the maximum value of an identity
  column changes by more than 10,000, or every time a bulk insert
  operation is performed.

So now you can use modification_counter column of sys.dm_db_stats_properties (Transact-SQL) for the statistics of interest:

  modification_counter

  Total number of modifications for the leading
  statistics column (the column on which the histogram is built) since
  the last time statistics were updated.


Indexed (Materialized) View for Parent and Newest Child Say I have parent->child tables setup.  Call them Shipment and Widget.

Is there anyway to create an Indexed view that will have the PK of the parent (ShipmentId) and the created when of the newest child (Widget.CreatedWhen).

This is the query I am looking for:

SELECT  widg.ShipmentId, MAX(widg.CreatedWhen) NewestWidget
FROM    dbo.Widget widg
GROUP BY widg.ShipmentId

Obviously this query cannot be an Indexed View because it has MAX in it.

But is there some other way via "Indexed View Legal" SQL to get a similar result set?

There isn't a way to do this currently, as finding the "latest" widget date will require an aggregate (grouping), ranking function, subquery, or apply; none of which the indexed view will allow in its definition.

However - is there a particular reason you want to have an indexed view? Do you have appropriate indexing on the underlying Widget table? I'm assuming that the reason you want to index the view is so you can pull out the values for a particular Widget ID.

I would index the underlying table on (WidgetID, CreatedWhen DESC) and implement your query as a normal view - or perhaps a inline table valued function if you always want to get the date for a single Widget.


Wich solutions is better for translate some data? I've a little mobile app who need some translation. Currently, I just want to translate my app in one language, I don't think I'll need more. So I'm facing a problem, should I translate stuff in database, or in translations files on client side ?
The user will be able to select his language, but first i need to determine it with the locale variable for the registration (because he must select his country at registration).
First I tought about creating one translation table for each table who need it.

But it seems to not be the good solution if I need to add more language, I'll have to alter all translations tables.
So, my second solution is to create a language table, keep the translation table for each table but as one-to-many relationship.

I think this solution is better, but I'm not sure about performance. I know it's a little app and the question does not really arise, but we never know.
Now, I'm stuck because It means that every time I need translation, I have to query the database.
There are tons of contries, and I'll like to have auto completion. Fruits will be displayed in several pages and used in others tables.
So I thought about keep translations on client side within json files.
For exemple translations/fr_FR.json
"fruits": [
  {
    "id": 1,
    "original": "banana",
    "translation": "banane"
  },
  {
    "id": 2,
    "original": "apple",
    "translation": "pomme"
  }
],
  "countries": [
  {
    "id": "ES",
    "original": "spain",
    "translation": "espagne"
  }
]

Everytime I need translation, I can use a function to get the translation from the file.
It means more calculations, and user will have to update his app if translation has changed.
Wich solution is better ?

You should only store your data in one language and handle the translating of text on the application side, for a multitude of reasons:

Different languages use different characters which may affect the data type and collation of how you store the translated data. This can affect you from a flexibility standpoint and potentially a performance standpoint (depending on the size of your data) and what collations you're using elsewhere.

Different languages have different dialects which means there's more rules to how you store the translated words. For example, Spanish spoken in parts of Spain may vary from how it's spoken in Brazil or Mexico.

Per #2, there may be different words in each of those dialects for the corresponding English word, and you'll then need to store more translations than anticipated.

Even within the same dialect of a language, a word may translate differently depending on the context it's used it. Building out a relational model to manage all the contexts of a given word sounds like a nightmare to me.

There's already a multitude of APIs out there that can be consumed on the application side and take care of all the aforementioned issues, such as Google's Translate API. There are other free ones as well. This is probably the simplest solution to this kind of problem.


Why not have Persons for every person in schema? I noticed that a lot of database schemas often show like, "students" or "teachers" or "employees" or "passengers" or something to that affect.

Why not have a schema where every individual is a person and is in Persons table and if went to look for "student", we call "students" table which links its person id to the id in Persons table to get data.

Students may become teachers so the data from students table can be updated/removed and be added in "teachers" table.

Why is that not common? What would be a problem with having "Persons" table rather than to have a table of people of a given position?

Many databases are designed exactly this way.  This pattern is known as class/subclass in object modeling, generalization/specialization in ER modeling, and sometimes referred to as type/subtype in other places.

There are several techniques for accomodating class/subclass patterns in table design.  For more details, see the subtypes tag.


Extract Multidimensional JSON I have some tables:

teste // id, descricao
teste2 // id, descricao
relacao // id, idTeste, idTeste2

With this structure, I got all products owned by a client. Now I'm trying to use FOR JSON PATH to extract data from the database:

This is where I am at the moment:

SELECT
    teste2.descricao AS "nome",
    relacao.id AS "produto.id"
FROM teste2
    INNER JOIN relacao ON relacao.idTeste2 = teste2.id
FOR JSON PATH

And this is the result from the server:

[{
    "nome": "Raphael Schubert",
    "produto": {
        "id": 1
    }
}, {
    "nome": "Raphael Schubert",
    "produto": {
        "id": 2
    }
}, {
    "nome": "Lorraine Schubert",
    "produto": {
        "id": 3
    }
}, {
    "nome": "Lorraine Schubert",
    "produto": {
        "id": 4
    }
}]

I was expecting to create a query where the answer is like this:

[{
    "nome": "Raphael Schubert",
    "produto": [{
        "id": 1
    }, {
        "id": 2
    }]
}, {
    "nome": "Lorraine Schubert",
    "produto": [{
        "id": 3
    }, {
        "id": 4
    }]
}]

I think with for each at select was possible, but I'm new to SQL Server, I know a bit about MySQL but was not able to extract that info.

As Martin Smith suggested in a comment the necessary syntax is very similar to FOR XML. Here is how I solved my problem:

SELECT
    teste2.descricao AS cliente,
    teste2.id AS clienteID,
    (
        SELECT
            teste.id,
            teste.descricao AS produto
        FROM relacao
        INNER JOIN teste ON relacao.idTeste = teste.id
            WHERE relacao.idTeste2 = teste2.id
       FOR JSON PATH
    ) AS produtos
FROM teste2
FOR JSON PATH, ROOT('clientes')

Result:

{
    "clientes": [{
        "cliente": "Raphael Schubert",
        "clienteID": 1,
        "produtos": [{
            "id": 1,
            "produto": "Casa"
        }, {
            "id": 2,
            "produto": "Carro"
        }]
    }, {
        "cliente": "Lorraine Schubert",
        "clienteID": 2,
        "produtos": [{
            "id": 3,
            "produto": "Moto"
        }, {
            "id": 1,
            "produto": "Casa"
        }]
    }]
}


How can I trigger this stored procedure on insert? I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous "Power Lost" errors. ("Error" being used loosely to describe any enumerable diagnostic message.)

To do this, I've defined the following stored procedure:

BEGIN
   -- Declare local variables
   DECLARE done BIT DEFAULT 0;
   DECLARE target SMALLINT(5) UNSIGNED DEFAULT '0';
   DECLARE `action` ENUM('Clear','Raise','SetSeverity');
   DECLARE severity TINYINT(3) UNSIGNED DEFAULT NULL;
   DECLARE timespan INT(11) DEFAULT NULL;
   DECLARE cutoff TIMESTAMP;
   -- Declare the cursor
   DECLARE interactions CURSOR FOR
   SELECT TargetErrNo, errorinteraction.`Action`, errorinteraction.Severity, errorinteraction.TimeSpan
      FROM errorinteraction WHERE ActorErrNo=NEW.Number ORDER BY Priority DESC;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   CREATE TABLE IF NOT EXISTS interactionrecord
      (`datediff` INT);
   TRUNCATE TABLE interactionrecord;
   -- Open the cursor
   OPEN interactions;
   -- Loop through all rows
   REPEAT
      -- Get
      FETCH interactions INTO target, `action`, severity, timespan;
      IF NOT done THEN
         -- Process
         IF timespan IS NULL THEN
            IF `action`='Clear' THEN
               UPDATE error SET Cleared=NOW(), UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NULL;
            ELSEIF `action`='Raise' THEN
               UPDATE error SET Cleared=NULL, UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NOT NULL;
            ELSEIF `action`='SetSeverity' AND severity IS NOT NULL THEN
               UPDATE error SET error.Severity=severity, UpdatedBy=NEW.Id WHERE Number=target AND error.Severity!=severity;
            END IF;
         ELSE
            SET cutoff=DATE_SUB(NEW.Reported, INTERVAL timespan SECOND);
            IF `action`='Clear' THEN
               UPDATE error SET Cleared=NOW(), UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NULL AND error.Reported>=cutoff;
            ELSEIF `action`='Raise' THEN
               UPDATE error SET Cleared=NULL, UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NOT NULL AND error.Reported>=cutoff;
            ELSEIF `action`='SetSeverity' AND severity IS NOT NULL THEN
               UPDATE error SET error.Severity=severity, UpdatedBy=NEW.Id WHERE Number=target AND error.Severity!=severity AND error.Reported>=cutoff;
            END IF;
         END IF;
      END IF;
   -- End of loop
   UNTIL done END REPEAT;
   -- Close the cursor
   CLOSE interactions;
END

This works perfectly when I insert a new error by hand, then call this function passing the new error's Id.

But when I try and insert a trigger to call it automatically, MySQL begins throwing Error 1422 at me. I've tried both FOR EACH ROW CALL updateerror(NEW.Id); and copying the stored procedure code into the trigger directly, replacing a few of the variables with their respective NEW columns. Should I simply leave this as a stored procedure and assume users are responsible to call it? Is there a better way to do this?

My exact MySQL version is 5.5.25.

I figured out that the following lines of code weren't allowed to run in a trigger:

CREATE TABLE IF NOT EXISTS interactionrecord
      (`datediff` INT);
   TRUNCATE TABLE interactionrecord;

I had had them in there for debugging purposes and forgot to remove them.

Once I removed them, I could run the code as a trigger, until I tried updating the error table, since apparently updating the table the trigger is on isn't allowed either.

What I'm doing now is a trigger which simply inserts new error Id into a small table, then having an event run every minute or so, calling my stored procedure on all the newly inserted errors.


Sybase ASE 15 "reorg rebuild" fixes performance issues - why and how to tell when it's needed? I had a table of several million rows in a Sybase ASE 15 server which was running extremely slow, even basic queries would take 5 minutes to execute, for example:

SELECT COUNT(1) FROM TABLENAME;

After googling the issue I discovered that running a reorg rebuild on the table fixed the issue.

Is there a way to determine when a table requires a reorg rebuild, or when running reorg rebuild would result in performance gains?

Should reorg rebuilds be performed on a regular basis? If so, under what circumstances?

What is the underlying cause of the degradation that a reorg rebuild fixes? Can it be avoided?

When a table is first indexed, especially with a clustered index, the records are in order, and there are a minimal number of index pages to find each record.  As update, insert and deletes happen, the number of index pages increases.  Over time this will impact query performance.  It is recommended that dba's run reorgs periodically to collect garbage, compact the database, and compact the index pages.  The downside to reorgs, is that prior to 15.7, they block access to the table, so typically must be run during off hours.

  1 - What is the underlying cause?

Insert/Update/Delete activity in the database creates additional database pages, and index pages.  The more pages the query has to read to get to the data it's looking for, the longer the query will take.

  2 - Should reogs be performed on a regular basis?

Probably.  Static, or rarely changing tables will need it infrequently, but as the activity on a table increases, the need for reorgs increases as well.

  How should I determine when to run?

This depends on the environement you are in.  The need for reorgs is directly related to the Insert/Update/Delete activity of the table.  You can use the optdiag command to check the health of your tables, take a close look at some of the following values:
Index page cluster ratio;
Data page cluster ratio;
Empty data page count;
Empty leaf pages;
Deleted row count;
Forwarded row count;
Index empty leaf page count.

Check the Sybase documentation on optidag and the Sybase documentation on reorg to get a good idea of how to determine when to run reorgs in your environment.


CASE in WHERE clause - retuns a single-row result instead of 4 rows? Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to EmployeeID = 24, if the project has started during or after year 2005, then the ProjectName field should be 'NULL', otherwise the ProjectName should show the actual value.

Once I solve it this way, I receive just a single result in MS SQL Management Studio

SELECT Employees.EmployeeID, Employees.FirstName, Projects.[Name] AS [ProjectName], Projects.StartDate
FROM Employees
INNER JOIN EmployeesProjects ON EmployeesProjects.EmployeeID = Employees.EmployeeID
JOIN Projects ON Projects.ProjectID = EmployeesProjects.ProjectID
    WHERE Employees.EmployeeID = 24
        AND Projects.[Name] = CASE
            WHEN Projects.StartDate = '2005' THEN NULL
            WHEN Projects.StartDate > '2005' THEN NULL
            ELSE Projects.[Name]
            END

If I run the above code partially:

SELECT Employees.EmployeeID, Employees.FirstName, Projects.[Name] AS [ProjectName], Projects.StartDate
FROM Employees
INNER JOIN EmployeesProjects ON EmployeesProjects.EmployeeID = Employees.EmployeeID
JOIN Projects ON Projects.ProjectID = EmployeesProjects.ProjectID
    WHERE Employees.EmployeeID = 24

there are 4 results as per the attached screenshot:

This solution is suggested and gives the full mark:

SELECT E.EmployeeID,FirstName,
  CASE
   WHEN P.StartDate > '01/01/2005' THEN NULL
   ELSE P.NAME
  END
  FROM Employees AS E
INNER JOIN EmployeesProjects AS EP ON EP.EmployeeID = E.EmployeeID
INNER JOIN Projects AS P ON EP.ProjectID = P.ProjectID
WHERE E.EmployeeID = 24

However, I want to ask why my solution shows just a single row, what is my mistake in the way query sequence? Is not it possible to retrieve the same by first doing the joins and then using a WHERE clause which includes a CASE?

Appreciate your feedback!

p.s. in case you need the script (schemas, insert), just let me know and I can add them as well.

The WHERE clause is used to filter data. You aren't trying to filter out the records, instead you want to include them but alter their displayed value based on their actual value. This is why the CASE statement in the SELECT statement gives you the desired results.

Specifically, the reason your query omits 3 results is not because the CASE statement isn't working, but rather that the first 2 WHEN statements will likely always evaluate false. Effectively, when Projects.StartDate >= '2005/01/01' then Projects.[Name] would have to equal NULL for the records to be selected. If employee 24 had any projects started on or after 01/01/2005 and a NULL project name, those records would show up.

To break it down even further, for each record in your query, it will do the following.

Checks if Projects.StartDate = '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
If the check in step 1 evaluates false, then check if Projects.StartDate > '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
If the checks in both step 1 and step 2 evaluate false, then does Projects.[Name] = Projects.[Name] then return record. Hint: it always will.

Because 3 of the records associated with employee 24 were on or after '2005-01-01 00:00:00.000' and did not have NULL project names, they were excluded from the results.


Remove specific character from string I have a variable which is a VARCHAR and will contain a list of folders (1 or more).
It may looks like that :

@Folder = '/folder1/folder2/'
or
@Folder = 'folder1/folder2/'
or
@Folder = '/Folder1/Folder2'

And if the first and the last characater of my string are '/', i want to remove them and my variable should be always

@Folder = 'Folder1/Folder2'

Thanks for your help

You could use the LEFT() and RIGHT() functions to do this.

DECLARE @Folder varchar(20) = '/folder1/folder2/';

IF LEFT(@folder,1) = '/'
SET @Folder = RIGHT(@Folder ,LEN(@Folder)-1);

IF RIGHT(@folder,1) = '/'
SET @Folder = LEFT(@Folder ,LEN(@Folder)-1);

SELECT @Folder;

Result

(No column name)
folder1/folder2


Move log file without taking database offline I need to move a database log file to a new partition without taking the database offline.

The normal way of doing this would be to detach the DB, move the log file then reattach the db.

Is it possible to do this without taking the database itself offline and if so how?

There is no way to do this with an online database.

When you move a database file (ALTER DATABASE ... MODIFY FILE), you even get the following message:

  The file "YourFile" has been modified in the system catalog. The new path will be used the next time the database is started.

  The normal way of doing this would be to detach the DB, move the log file then reattach the db.

That's not the "normal" or accepted way I would do it.  To move database files, I do the following:

Run an ALTER DATABASE command to change the location of the file(s)
Take the database offline
Physically move the file(s) to the new location specified in step #1
Bring database online

See this reference on TechNet: Move User Databases


Merging two backups form a postgresql database I have a kind of big postgresql database and two backup files (each uncompressed dump file ~56 mb) representing 2 states of the data in time.

1st state - backup from a couple days ago
2nd state - backup current state of db, minus deleted records and plus the new data added in those two days

this is the same database, and the records have a lot of relations to be handled manually

so I want to join this two backups in order to get a third snapshot of my DB that has the same data as the first state plus the extra data found in state 2.

Anyone can suggest a tool to do this? or a way to do this?

Is a rails project with a psql db.

I already try to merge the two files using the file merge mac app, but because of the size of the files is a really slow process, and there are a lot of differences (a lot of rows added and deleted) to manually pick each conflict.

I don't know an easy way, but I'd try this:

Restore both databases individually and dump out only the data using INSERT statements (instead of COPY).  Something like pg_dump -a --inserts.
Restore the schema from the original backup into the target database.
Disable all foreign key constraints.
Restore the first dump created in 1.
Restore the second dump created in 1., ignore errors.
Enable foreign key constraints.


Remove master role from MySQL server I have two MySQL servers, one is the master and other one is slave, that's all good.

The problem I have is that the slave is also set up as a master. I would like to disable or remove the master role from the slave server.I have commented everything out in my.ini that relates to master.

The way we set this up was a copy of a database which was a master and copied it across to the new server. So the master configuration was already there. I set up slave replication and that all works, it talks to the original server.

Set up is like this: Server 1 is a master. Server 2 is the slave. Server 2 replicates with server 1. Server 2 also has Master replication configured. Is it possible to remove or disable the master configuration on server 2?

The server is Windows 2008 R2. The data sits on WAMP server with phpmyadmin.

I'll get to the answer shortly, but first I should say I'm not sure why it is that you are trying to remove a "master role" from a host. Basically, a host is not configured "as master". Slaves just connect to it, which makes it a master.

I'm just curious: is this a security issue or something, that you want to forcibly prevent slaves from connecting to this host?

Anyway, one very easy way to do this is to remove the log-bin parameter, and restart MySQL. This will prevent MySQL from generating binary logs. No binary logs means slaves have nothing to get from this host, hence it cannot be a master.

Otherwise you may try and control that in terms of privileges. I don't think this is a good idea because this slave of yours will normally replicate everything from its master, including the privileges set on the master. So setting the slave to have a different (stricter?) set of privileges means it's not entirely in sync with its master.

Oh, and you may just grant the replication privilege on a pre-defined set of hosts, that would do the trick as well:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.2.101' IDENTIFIED BY '123456';


Need help tuning my.cnf file I need help tuning mysql. Currently it is using a lot of CPU. I dont find any query that last longer than 3 seconds in SHOW PROCESSLIST and there are only around 20-25  running most of the time. Still the cpu usage goes beyond 600%

I have 24 GB RAM and 64 bit 8 core processor.

I got this results from mysqltuner but I cannot figure out what configuration change I need to do in my.cnf file.

my.cnf:

[mysqld]
max_connections=1000
innodb_file_per_table=1
innodb_io_capacity = 5000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_buffer_pool_size=10G
sort_buffer_size=2G
read_buffer_size=2G
join_buffer_size=2G
tmp_table_size=2G
key_buffer_size=3G
max_allowed_packet=160M
table_cache=1G
slow_query_log = 1
innodb_flush_method = O_DIRECT
query_cache_type = 1
thread_cache_size = 150
thread_concurrency=16
query_cache_limit=10M
query_cache_size=2G

Output of mysqltuner:

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 288M (Tables: 20)
[--] Data in InnoDB tables: 3G (Tables: 179)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 18

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 4m 46s (7M q [300.507 qps], 266K conn, TX: 12B, RX: 2B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 15.0G global + 6.0G per thread (214 max threads)
[!!] Maximum possible memory usage: 1299.1G (5769% of installed RAM)
[OK] Slow queries: 0% (1K/7M)
[OK] Highest usage of available connections: 85% (182/214)
[OK] Key buffer size / total MyISAM indexes: 3.0G/61.2M
[OK] Key buffer hit rate: 99.8% (4M cached / 6K reads)
[OK] Query cache efficiency: 79.2% (5M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 366K sorts)
[OK] Temporary tables created on disk: 21% (12K on disk / 57K total)
[OK] Thread cache hit rate: 99% (182 created / 266K connections)
[OK] Table cache hit rate: 22% (400 open / 1K opened)
[OK] Open file limit used: 9% (96/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 10.0G/4.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability

Please help me figure out how to improve cpu usage for mysql.

3 seconds is kind of a long time for mysql. I bet you have a lot of full-table scans going on that are using up CPU. I've gotten good mileage in decreasing CPU with just a little indexing.

This is not going to be a short answer, but try this:

Enable the slow query log (set global slow_query_log=on and set global slow_query_log_file='/someplace/you/can/find/it'), and set it to log anything above 1 second (set global long_query_time=1).
Let it log a little bit. Then look at the output. You can just look at the slow log or use a tool like pt-query-digest (http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html) to find your long-running queries. Look especially for queries that scan a lot of rows to only get a few rows of results.
Pick a query that gets run a lot. Look at the involved tables (show create table my-table-in-this-query) and see what the indexes are. Also see the explain plan to show what indexes mysql plans to use (explain query select blah blah put your query here).
Add an index. See CPU decrease.

You might not be able to index everything; the queries may have to be rewritten as well. But I bet you can get a lot of CPU back by doing this for your top queries.


Audit Logins on PostgreSQL 9.0 I have setup a script, created from various links on the web to trace activity in a table using a trigger.

/* Create the MinUser Table */
CREATE TABLE MinUser (
    User_Id             int NOT NULL PRIMARY KEY,
    User_Name           varchar (50) NOT NULL,
    User_Password           varchar (50) NOT NULL,
    User_Email          varchar (50),
    User_Role           varchar (50),
    UNIQUE (User_Name)
) WITH (OIDS=FALSE);

/* Create the Audit Table */
CREATE TABLE MinUser_Audit (
    AuditUser_Id            SERIAL,
        operation               char(1)   NOT NULL,
        stamp                   timestamp NOT NULL,
        userid                  text      NOT NULL,
        ipaddress                   text      NOT NULL,
    User_Id             int NOT NULL,
    User_Name           varchar (50) NOT NULL,
    User_Password           varchar (50) NOT NULL,
    User_Email          varchar (50),
    User_Role           varchar (50)
) WITH (OIDS=FALSE);

/* Function that will add a row to the MinUser_audit table when an insertion/deletion/modification is made to the MinUser table */
CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
    BEGIN
        --
        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('D', now(), user, inet_client_addr(), OLD.User_Id,OLD.User_Name, OLD.User_Email, OLD.User_Password, OLD.User_Role);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('U', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('I', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$usr_audit$ LANGUAGE plpgsql;

/* Create the trigger that will use the MinUser_audit() function */
CREATE TRIGGER MinUser_auditt AFTER INSERT OR UPDATE OR DELETE ON MinUser
    FOR EACH ROW EXECUTE PROCEDURE MinUser_audit();

I understand how to get the user name of the person who inserted/modified/deleted the record using user.  Is there a function I can use to get the ip address as well?  Also, is there a reference on PostgreSQL's website that shows all the different server variables that you can use in a query?

For the IP address you can use the function inet_client_addr()

All the system information functions are documented in the manual in the chapter "System Information Functions"

http://www.postgresql.org/docs/current/static/functions-info.html


Trigger function error: Record "old" not yet assigned I'm new to trigger functions and trying to work this out. I have looked for solutions in the documentation and applied those, but still something is wrong here. My aim is to show error an message when inserting into this specific column.

CREATE OR REPLACE FUNCTION test_f()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
    IF (TG_OP = 'INSERT') THEN
    IF (OLD.col1 != NEW.col1) THEN
        RAISE EXCEPTION 'Editing not allowed';
        END IF;
        END IF;
        RETURN NEW;
        END;
    $$;

    CREATE TRIGGER test_t
    Before insert on schema.table
    For each row execute procedure test_f();

    Insert into schema.table(col1)
    VALUES
    ('test');

(I'm aware there are questions like mine already however those haven't helped in my case.)

In the case of an INSERT, there is no previous version of the row, so the OLD variable is unassigned.

See the documentation:

  OLD

     Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.


How to "analyze" a SQL query? I'm not sure if I used the right term in my question.

I am creating a prototype, which allows users to type in a raw SQL query, which will query the backend DB. I want to also make sure that users only apply SQl to the tables that they are allowed to.

So when a user types in something like SELECT * FROM t1 WHERE id > 10, I feel like I need to

Analyze the query to make sure that the tables are ok - in this case "t1"
They cannot access any other admin-type tables. For example, I don't want them to access "information_schema (i.e. for Postgres)"

What do you think the best strategy would be for this?

FYI, I'm NodeJS, so the primary programming language would be Javascript.

Would I need to "parse" the query to make sure that the tables being accessed by the query are ok?

It's extremely hard to make this secure. Event if you are trying to be smart and check for every "possible" aspect. There is also dynamic SQL. And all kinds of ways to obfuscate code.

I wouldn't let the world run any query with more permissions than they should have.

If at all, I would create a user with restricted privileges, so that he can only do what he's allowed to in the first place. Also be aware that some database objects are visible to public by default, some tables in the system catalog like pg_class for instance ...


Reason for failed query plan executions in SQL Server We are using Azure SQL Database and recently we have experienced a severe performance degradation on our production database. When looking into Regressed queries in Query Store to find out the probable culprit, I found a regressed query where there were a few hundred failed executions of its usual query plan:

I was not able to find any information on how to figure out what exactly went wrong, just that an exception aborted the execution. The problem went away on its own after a couple of minutes.
I queried the sys.query_store_runtime_stats catalog, but saw nothing useful there.
Is there a way to get to the actual failures? Could I set up these errors to be sent out to e.g. Log Analytics automatically (as we're in Azure)?
Thank you.

The best way I know to solve this is to set up Extended Events. Specifically you can capture the error_reported event. Use Causality Tracking along with batch/rpc starting and batch/rpc completed to capture every bit of data about what's happening with the queries. Be sure to use some good filtering because this could generate quite a lot of data.


How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD.
I couln'd find any valuable information so far unfortunately. Perhaps because Linux platform is quite fresh for MSSQL Server. Thank you for any advice here.

I think you need to use a server / login trigger. Aaron Bertrand wrote a cool little article about combining login triggers and extended events here - https://www.mssqltips.com/sqlservertip/3582/prevent-and-log-certain-sql-server-login-attempts/


SQL Server: TSQL or safe steps needed: Change collation en masse for group of tables Looking for a safe TSQL (preferred) or safe steps (rather automated if possible) to change collation on dozens of tables in one throw.

Background:

We have a db, SQL Server 2005, with ~100 tables.

30-40 of them have a collation set which is inappropriate for the app we are running. (we need them all to be "SQL_Latin1_General_CP1_CS_AS" but this group of tables is "SQL_Latin1_General_CP1_CI_AS")

Possible Solution:

We found this script:
http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

Issue with found solution:

But this script appears to introduce the possibility of data loss, AND it appears to neither detect nor warn nor measure if there will be data loss.

Feels risky to use!

Question to Community:

Is there a more advanced way of tackling this?

Preferably, a script that:

Detects if there will be data loss and at least gives a warning. (Even a script which just scans tables, considering change from collation A to B, and tells me what will happen if this change is made...)
Can work in bulk (e.g. I tell it what my target collation is, and it does all the work against all tables and columns in the db and reports back what it did)

Thank you!

I had to do this recently and it was a big pain.  I ended up doing a write up on changing collations: Collation: Correction -> Expression, Column, Database

My personal preference for method in your situation would probably be to

Create a new database (with the correct collation if needed)
Script out the old database
Do a search and replace on the script file to change the collation from one to another
Run the script on the new database
Move the data across
Do a rename swap to put the new database in place of the old

Your other options are to either create a script that generates alters for each column or to use the GUI.  The GUI is going to be slower but more certain.  The script method generates the risk of making changes you didn't intend if your script isn't letter perfect.

The script method also has the problem that you have to drop any constraints, indexes etc that touch the column before you can alter it to change the collation.  You might be able to generate a script to do that for you but it wouldn't be easy.

If you decide to go the script method I can probably help you generate one to create the alters but for dropping and re-creating indexes/constraints you would be on your own. Like I said I would go with the re-create the database method.  It's easy and relatively fast unless you have a huge amount of data.  The only real drawback is that it requires there to be double or even triple (space for logs) the size of your database free.


mysqlimport locks all tables system wide I recently developed a script that spawns multiple processes to import tables in parallel using mysqlimport and a --tab type mysqldump export. On the development server it works very well and compared to a standard mysql db_name < backup.sql type of import it cuts the time from around 15 minutes to 4 or 5 minutes.

The problem is on our production server this script seems to be locking tables system wide. That is to say, I'm importing a backup to a completely different database but our live application tables still end up locked. A SHOW PROCESSLIST confirms that tables on our live db are indeed locked but no INSERT or UPDATE queries are running on any tables in that database.

Why is this happening? Is there a configuration variable / setting that I can adjust to prevent this lock from happening?

If you start mysqlimport with --lock-tables=0 then there will be no locks.

mysqlimport uses LOAD DATA rather than INSERT/UPDATE.


SQL Server 2008 and compatibility level 80 Is there any technical reason why an application certified on SQL Server 2000 should not work properly on SQL Server 2008 with compatibility level 80?

In other words would there be any possible data or functionality loss in attaching a 2000 database to a 2008 server, keeping the 80 level?

In theory it should work.  You better test it thoroughly before converting production.  If your application uses DTS packages, they will have to be converted to SSIS.  Your OS will have changed as well, so double check to make sure your app does not rely on operating system level functionality.

To further answer your question, the vendor is likely not wanting to support it as a way of covering their butts.  They certified the application to run on SQL 2000, not SQL 2008.

I would stop giving them money if I were you.


With OPTION RECOMPILE vs without OPTION RECOMPILE I am lost why execution plan is different if I run query with option recompile to compare to same query (with clean proc cache) without option recompile. The result is one row. The script is:

Create two tables:

CREATE TABLE H (id INT PRIMARY KEY CLUSTERED IDENTITY(1,1), header CHAR(100))
CREATE TABLE D (id INT PRIMARY KEY CLUSTERED IDENTITY(1,1), idH INT, detail CHAR(100))
CREATE INDEX IX_idH ON dbo.D(idH)

Fulfill:

DECLARE @i int = 1
WHILE @i <= 10
BEGIN
    INSERT INTO H (header) VALUES ('nonononono')
    SET @i = @i + 1
    PRINT @i
END
SELECT * FROM H
GO

DECLARE @i int = 1
DECLARE @ii int = 1
WHILE @i <= 7
BEGIN
    WHILE @ii <= POWER (10,(@i - 1))
    BEGIN
        INSERT INTO D (idH, detail) VALUES (@i, 'nonononono')
        SET @ii = @ii + 1
    END
    SET @i = @i + 1
    SET @ii = 1
END

The statement:

DBCC FREESESSIONCACHE

DECLARE @hid INT = 1

SELECT *
FROM H h
JOIN D d ON d.idH = h.id
WHERE h.id = @hid
OPTION (RECOMPILE)

With OPTION (RECOMPILE) it uses the key lookup for the D table, without it uses scan for D table. I do not understand why. MS SQL version 13.0.4411.0. And simple (7 rows) and actual statistics.

With OPTION(RECOMPILE) optimiser knows the value of the @hid variable and essentially generates the plan, as if you wrote:

SELECT *
FROM H h
JOIN D d ON d.idH = h.id
WHERE h.id = 1

The generated plan has to be valid for this specific value of the parameter. It does not have to be valid for any other value of the parameter. Also, optimiser knows statistics of the table and usually can make a better decision. If there is only one value in the table that is equal to 1, most likely it will choose a seek. If there are a lot of values in the table that are equal to 1, it would choose a scan.

Without OPTION(RECOMPILE) optimiser has to generate a plan that is valid (produces correct results) for any possible value of the parameter.

As you have observed, this may lead to different plans.

I would recommend to look at both actual execution plans in the free SQL Sentry Plan Explorer tool. Look at details of each operator in the plan and you should see what is going on. You can do it in SSMS as well, but Plan Explorer is much nicer.


Why Is Null Ignored In Query Results I have a table with similar structure to the below, and a field has a NULL value in it.  When I query the table this record is not returned, it is omitted.  How should the query be altered so that this record is returned?

Declare @Fiesty Table
(
  numberfour varchar(100)
  ,valid varchar(20)
)

Insert Into @Fiesty (numberfour, valid) Values
('1ACRELBS', NULL), ('4ACRELBS', 'Green'), ('17ACRELBS', 'White')

Select * FROM @Fiesty WHERE valid NOT IN ('Green', 'Blue', 'White')

My expected returned result would be where numberfour = 1ACRELBS

SQL uses ternary (three-valued) logic and comparisons with NULL are tricky, counter intuitive. Regarding the condition:

WHERE valid NOT IN ('Green', 'Blue', 'White')

it expands to:

WHERE valid <> 'Green' AND valid <> 'Blue' AND valid <> 'White'

When valid is NULL, all these 3 sub-conditions evaluate to UNKNOWN (neither TRUE nor FALSE). The whole condition evaluates to UNKNOWN as well and thus the row rejected (WHERE keeps only the rows that the condition evaluates to TRUE).

When comparing nulls, you can think this way. I don't know the exact value of valid so I can't be sure if it's going to be different to 'Green' or not. It might be and it might be not. The result is UNKNOWN.

If the intention is to get in the result the rows where valid IS NULL, you can use either:

SELECT * FROM @Fiesty
WHERE valid IS NULL
   OR valid NOT IN ('Green', 'Blue', 'White') ;

or EXISTS / EXCEPT (or NOT EXISTS / INTERSECT):

SELECT f.* FROM @Fiesty AS f
WHERE EXISTS
      ( SELECT f.valid
      EXCEPT
        SELECT v.valid
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
      ) ;

There is also a NOT EXISTS way which is my opinion to be preferred to NOT IN, especially because the behaviour regarding nulls is more intuitive. (Notice also the similarity with the INTERSECT version):

SELECT f.* FROM Fiesty AS f
WHERE NOT EXISTS
      ( SELECT *
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
        WHERE v.valid = f.valid
      ) ;

Test at dbfiddle.uk.


"Access is denied." for xp_cmdshell for network share On a SQL 2014 SP 1 instance a SQL Agent job  I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied."

The command is EXEC master..xp_cmdshell 'DIR "\\backups\marshall\master" /A:-D /S /B'

The command will work for a local folder.EXEC master..xp_cmdshell 'DIR "C:\Temp" /A:-D /S /B'

Running EXEC master..xp_cmdshell 'whoami' returns the Windows account that is used for the SQL Engine and SQL Agent. This account has Full Control for the backup folder. I've deleted and re-added permissions for this account. I can manually add and delete files from this folder.

I will be changing the process to no longer use xp_cmdshell, but can't do that today. Any ideas on what is going wrong?

Create a proxy account to run xp_cmdshell and give the proxy access to your share.

Link


Changing Auto-Growth Setting in AOAG for log file We are on AOAG SQL Server 2014.

I am planning to change Auto-Growth setting from % --> MBs for log file.

Do I need to change the setting in all Replicas or only Primary Replica?

Set it on Primary replica only and it gets applied to all secondaries.

You can test it out on a small db .. add it to availability group and change the autogrowth setting.


Where is my bind address? With mysqld --verbose --help, I got this cnf files

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-10.0 mariadb mariadb-10.0 client-server

With netstat -tln

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.1.1:53            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:31416           0.0.0.0:*               LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN

I want to change my Local Address 127.0.1.:53 to 127.0.0.:3036.How to do this?

EDIT

cat ~/.my.cnf
cat: /home/milenko/.my.cnf: No such file or directory

IT DOES NOT FIND THIS CNF FILE AT ALL.

milenko@milenko-desktop:~$ cat /etc/mysql/my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Should I add the port in this file?

You can edit your port in your my.cnf file.

port = 3306

As per your edit:

MariaDB is slightly different. Your config file is in this location:

/etc/mysql/mariadb.conf.d/

And it's called *.cnf (something that's not usually my.cnf). For example, 50-server.cnf.