Count Items not In Time Range I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out.

So, I have this table:

WITH ADates AS (
    SELECT
          Id
        , SharedId
        , TheDateTime
    FROM (VALUES
        (CAST(1 AS int), CAST(1 AS int), CAST('2019-01-01 01:01:00.00' AS datetime2(7))),
        (2, 1, '2019-01-01 01:01:33.00'),
        (3, 1, '2019-01-01 01:02:00.00'),
        (4, 1, '2019-01-01 01:02:01.00'),
        (5, 1, '2019-01-01 01:02:04.00'),
        (6, 1, '2019-01-01 01:06:15.00'),
        (7, 2, '2019-01-01 01:06:00.00'),
        (8, 2, '2019-01-01 01:06:45.00'),
        (9, 1, '2019-01-01 01:02:31.00'),
        (10, 2, '2019-01-01 01:06:05.00'),
        (11, 2, '2019-01-01 01:06:46.00'),
    ) X (Id, SharedId, TheDateTime)
)

So, the expected result that I'm looking for is:

+==========+=======+
| SharedId | Count |
+==========+=======+
| 1        | 4     |
+----------+-------+
| 2        | 2     |
+----------+-------+

The numbers are determined by:

Count since first in new group.
Not within 30 seconds of previous group so it is a new group and count.
Don't count since it is within 30 seconds of 2.
Don't count since it is within 30 seconds of 2.
Count since not within 30 seconds of previous group (Item 2).
Count since not within 30 seconds of previous group (item 2).
Count in new group for SharedId.
Count since not within previous grouping.

I'm thinking I should be doing a Window Function for this. Just not sure how to have it rely on just the first of the group.

It has something to do with item 6 being after item 7 which is in another group. Because of that it is seen as a new count in my query, resulting in 5.  Is this time with id=6 correct? can it be after id=7?

The query has gotten so complex that I cannot explain, or it too late already (), which is not good, but ….

The next one seems to produce the correct results (with the 9 records):

WITH ADates AS (
    SELECT
          Id
        , SharedId
        , TheDateTime
    FROM (VALUES
        (CAST(1 AS int), CAST(1 AS int), CAST('2019-01-01 01:01:00.00' AS datetime2(7))),
        (2, 1, '2019-01-01 01:01:33.00'),
        (3, 1, '2019-01-01 01:02:00.00'),
        (4, 1, '2019-01-01 01:02:01.00'),
        (5, 1, '2019-01-01 01:02:04.00'),
        (6, 1, '2019-01-01 01:06:15.00'),
        (7, 2, '2019-01-01 01:06:00.00'),
        (9, 1, '2019-01-01 01:02:31.00'),
        (8, 2, '2019-01-01 01:06:45.00')
    ) X (Id, SharedId, TheDateTime)
),
TMPADates AS (
SELECT
    Id,
    SharedId,
    TheDateTime,
    --DATEADD(S, 30, TheDateTime ) TheDateTime30,
    ISNULL((SELECT MIN(TheDateTime)
            FROM ADates t2
            WHERE t2.TheDateTime BETWEEN DATEADD(s,-30,ADates.TheDateTime) AND ADates.TheDateTime
              and ADates.SharedId=t2.SharedId),TheDateTime) ingroup
FROM ADates
),
TMPAdates2 AS (
SELECT
    Id,
    SharedId,
    TheDateTime,
    ingroup,
    -- ISNULL(LAG(ingroup) OVER (ORDER BY SharedId,ingroup DESC),ingroup) as ingroup2
    ISNULL(LAG(ingroup) OVER (PARTITION BY SharedId ORDER BY SharedId,ingroup DESC),ingroup) as ingroup2
FROM TMPADates
)
SELECT
    SharedId,  COUNT(DISTINCT ingroup2) As Count
FROM TMPADates2
GROUP BY  SharedId


In what range can you expect a cloud based Sql Server data reading in mb/s? Say everything is optimized, queries are simple, indexes are good, etc.
Should a SQL (Azure) database where you spend about 150 to 300 euros per month easily handle data reading with about 250-500 mb per second on a table of ~100 million rows or is that getting close to the limits and should I look at other solutions?
I understand this is a very broad and general question, but I'm looking for something like very rough estimates. The problem is that I have a very complicated legacy project and I am clearly hitting some limits.
I tried (after consulting Azure experts) various other data solutions in the past to only hit other bottlenecks after I migrated the project and had the real production traffic coming along again.
The site has extreme peak traffic in a very short period of time. So it will be on 5% of the database (eDTU) consumption the whole week with about one or two times a day extreme traffic for half an hour. About +50.000 very active concurrent users with a lot of dynamic data being written and mainly requested. The data can't be stale.
So I'm just looking for a very rough estimated guess so I know if I need to move into a really different direction like i.e. memory-cached microservices.
If 500 mb/second should be no problem then I will try to explore trying to layout my data differently or other SQL based solutions.

So measuring if a server can handle your specific workload by the amount of money it costs when weighing different implementation options is as as much of a moot point as measuring how much a car costs when comparing their MPG between different cars to figure out if they can drive you from point A to point B.
My analogy above is meant to convey that we can't really provide a meaningful solution based on $ spent since there are different types of server configurations for the same amount of money, and the same amount of money spent on the server will generally result in the same output performance of any implementation you choose (RDBMS, NoSQL, memory-cached, alternative DB system, etc) for equivalent problems respective of their domain. It is not qualitative enough for an accurate answer to be provided.
That being said, knowing things like how frequently the data is changing, how frequently new data is being added, total size of the Tables and database, and how concurrently active your database will be help paint the picture, which you've provided some of already.
Other things that will be key in helping clarify a good solution for you are what kind of data is it and how is your data structured currently? (E.g. is it highly relational, is it not very concretely defined, is it mixed.) What kind of queries are typical during your off-peak and peak concurrent time-frames? Examples are very helpful here in communicating what your data looks like and what the querying of that data looks like.
For example if your data isn't very well structured / not concretely defined and it's only typically accessed by the key then a NoSQL solution is probably best. If the type of querying your users do is analytical (such as aggregative querying) then it might make sense to scale up a data warehouse or use things built into Azure SQL to improve analytical querying.
Finally I'll add from my previous experience with a not too far off server of similar data size and concurrency, $300 was not enough to support the hardware behind our server. Sure, we definitely could've made some design and implementation improvements, but I still think $300 just wouldn't cut it (for a cloud based solution). I'm going to assume the same will be true for you, but that's a big assumption since my example is only anecdotal and as David Browne mentioned, you're best off testing to see what works within your price range.


Performance problem with big query on first run I'm having a problem with a query usually being so slow on first time run that it times out and then executing in under a second once it's loaded into cache.

The query looks something like this

exec sp_executesql N'SELECT
    [Project1].RecordID AS [RecordId],
    ...
    FROM ( SELECT
        [Extent1].RecordId] AS [RecordId],
        ...
        FROM Items AS [Extent1]
        LEFT OUTER JOIN ItemParameters as [Extent2]
        ON [Extent1].[RecordId] = [Extent2].[SampleId]
        WHERE ...
    ) as [Project1]
    ORDER BY [Project1].CreatedDate DESC, [Project1].RecordId ASC, ...

The query is generated by EntityFramework for me and only has this one join. The Items table is ~3.5 million rows and ~2.5 GB data and the ItemParameters is ~23.6 million rows and ~19.2 GB data. The query returns 11400 rows and, as far as I can tell, properly hits the indexes.

I've written a test program that does this in a loop, forever:

Restart the SQL Server service (MSSQLSERVER).
Sleep for 6 minutes. To allow it to start up.
Execute above query.
Write execution time to a file.

The results are disturbing. Sometimes it takes 4 minutes and sometimes it takes 4 seconds and I don't see why. It is running on a VM Ware virtual machine. On my developer laptop the same query (against a third of the data) is completed in 3 seconds.

I've run it with set statistics io on and set statistics time on and the output looks like this:

(11409 rows affected)
Table 'ItemParameters'. Scan count 3803, logical reads 19689, physical reads 2724, read-ahead reads 4261, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Items'. Scan count 109, logical reads 40423, physical reads 4, read-ahead reads 4089, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

Things I've done to speed up performance:

Run sp_blitzFirst from https://www.brentozar.com/blitz/. Gave nothing.
Set SQL Max memory to 90 % of my 32 GB as per https://www.brentozar.com/blitz/max-memory/.
Rebuilt all the indexes and column statistics.
Disabled the SQL Full Text service. We're not using it.
Restarted the server various times.

What can I do to make sure the query executes quickly enough every time? Any help is greatly appreciated.

EDIT

Execution plan for poor-performing query (132 seconds): https://www.brentozar.com/pastetheplan/?id=Syxbf-q5m
Ex
Execution plan against cache (1 second): https://www.brentozar.com/pastetheplan/?id=ByegNZcqX

What can I do to make sure the query executes quickly enough every time?

It's unlikely that this is a query performance issue. You're doing very little IO, using very little CPU time, and the query isn't processing very much data. The likely solution will be to resolve the hardware or configuration issue that is causing your query to run for so long sometimes. In one of your executions the query 92636 ms of elapsed time but only used 546 ms of CPU time  That means that SQL Server was waiting for something for over 92 seconds during query execution. Based on your description of the problem it's likely that there's a severe IO problem on the server. 11000 physical reads isn't that much and what you're seeing isn't normal.

You didn't include your version of SQL Server, but to get more information about the problem I would look at wait stats. You seem to have control over the server so you can try taking a before and after snapshot of the sys.dm_os_wait_stats DMV and seeing which waits increase after the query finishes executing. That DMV is server wide so you'll want to avoid running other queries if possible. You can also reset the DMV immediately before running your query by running the following T-SQL:

DBCC SQLPERF ("sys.dm_os_wait_stats" , CLEAR);

The wait stats should let you confirm if IO is indeed the problem. You may need to work with the admin in charge of the VM or storage to resolve this issue.


SQL Database to XML? I would like to export SQL data to an XML file so it can be read in COBOL. I am working with SQL Express 2008 r2.

I am using export data from query (table ) to XML in SQL when I have needs for autonomous transactions.

Way I do: I wrote a SQL-CLR stored procedure which using C# (code) and XML.Writer for writing data to disk

Here is parts of my code in C# (you need to compile this )

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static void NapisiXMLtoFile(string fileName, SqlXml xmlLog )
    {
        try
        {
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }
            File.WriteAllText(fileName, xmlLog.Value.ToString());
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error writing to file " + ex.Message);
        }
    }
}

Here is how you register your procedure

CREATE PROCEDURE [dbo].[clr_write_to_file]
    @fileName [nchar](100),
    @xmlLog [xml]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [WriteToFile].[UserDefinedFunctions].[NapisiXMLtoFile]
GO

and finally here is how you call your stored procedure to write file to disk

exec dbo.clr_write_to_file @imeFajla,@xmlLOG


MySQL open_files_limit - cannot change this variable I'm running into issues because my open_files_limit for mysql is only 1024.

Centos 7, MySQL Community Server 5.6.22-log

It's actually a vps dedicated to mysql for a WHM server (remote mysql), but that should be beside the point.

Config files

my.cnf:

[mysqld]
open_files_limit = 100000
open-files-limit = 100000 #I've read that the dashes are required on older versions, but I threw it in anyway.
innodb_buffer_pool_size = 600M
...

# and the same for mysqld_safe
[mysqld_safe]
open_files_limit = 100000
open-files-limit = 100000
...

/etc/security/limits.conf:

*       hard    nofile  100000
*       soft    nofile  100000

Command outputs

My os open_files_limit for root:

[root@mack ~]# ulimit -Hn -Sn
open files                      (-n) 100000
open files                      (-n) 100000

And then as mysql user:

[root@mack ~]# su mysql
bash-4.2$ ulimit -Hn -Sn
open files                      (-n) 100000
open files                      (-n) 100000

MySQL status:

[root@mack ~]# service mysql status
Redirecting to /bin/systemctl status  mysql.service
mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
   Active: active (running) since Wed 2014-12-24 10:41:09 EST; 40min ago
  Process: 2982 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
  Process: 2970 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 2981 (mysqld_safe)
   CGroup: /system.slice/mysqld.service
           ââ2981 /bin/sh /usr/bin/mysqld_safe
           ââ3268 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --open-files-limit=100000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/my...

Dec 24 10:41:08 mack systemd[1]: Starting MySQL Community Server...
Dec 24 10:41:09 mack mysqld_safe[2981]: 141224 10:41:09 mysqld_safe Logging to '/var/log/mysqld.log'.
Dec 24 10:41:09 mack mysqld_safe[2981]: 141224 10:41:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Dec 24 10:41:09 mack systemd[1]: Started MySQL Community Server.

I've restarted mysql, stopped, checked status to make sure it stopped, then started it, and rebooted the whole system.

Evidence that my.cnf works:

mysql> show global variables like '%buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 629145600 |
+-------------------------+-----------+
1 row in set (0.00 sec)

Now here's where my blood pressure goes up:

mysql> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

What am I missing?  Is there another my.cnf that's overwriting my value?

[root@mack ~]# find / -name "*.cnf"
/usr/share/mysql/my-default.cnf
/usr/share/doc/mysql-community-server-5.6.22/my-default.cnf
/var/lib/mysql/auto.cnf
/etc/my.cnf
/etc/pki/tls/openssl.cnf

I looked into all of those and there is no mention of open_files_limit.  For the heck of it, I grep'd through these looking for the setting:

[root@mack ~]# grep -r "open_files_limit" /etc
/etc/my.cnf:open_files_limit = 100000
/etc/my.cnf:open_files_limit = 100000
[root@mack ~]# grep -r "open_files_limit" /var
[root@mack ~]# grep -r "open_files_limit" /usr
/usr/share/vim/vim74/syntax/ora.vim:syn keyword oraKeywordUnd     _number_cached_attributes _offline_rollback_segments _open_files_limit
Binary file /usr/sbin/mysqld matches
Binary file /usr/sbin/mysqld-debug matches
Binary file /usr/bin/mysqlbinlog matches
/usr/bin/mysqld_safe:      --open_files_limit=*) open_files="$val" ;;

But nope, they don't affect it.

I needed to edit /usr/lib/systemd/system/mysqld.service and add

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then run systemctl daemon-reload and systemctl restart  mysql.service.

Now the variable is capped at 65536 for some reason unknown, but I can live with that for now.

Found this at https://unix.stackexchange.com/questions/152186/mysql-max-open-files-more-than-1024#answer-157910


You could do something like this with row_number(). Performance probably is going to depend on how many active articles are present each day.

This codes creates a seq based on the sort you provide and then finds the sequence of the last Id value, which you can use to grab the top (x) rows.

declare @LastId INT,
        @PageSize INT

set @LastId = 187322
set @PageSize = 2

;with cte
as(
SELECT *, seq = ROW_NUMBER() over(ORDER BY important DESC, release_date DESC, edit_date DESC)
FROM @demo d
WHERE
    status = 1
    AND release_date <= GETUTCDATE()
)
select TOP (@PageSize) *
from cte t1
where t1.seq > (select seq from cte t2 where id = @LastId)
Order by t1.seq


database functional dependency candidate keys How do you know when you found all the candidate keys in a functional dependency?

I know how i would go about finding a candidate key, just don't know how to tell if there are more.

Thanks.

If you're talking about identifying candidate keys for relational synthesis you need to know the dependencies.  If you have the functional dependencies you can use the relational synthesis algorithm; a synopsis of which can be found here.

Note, if this is a homework question please mark it as such in the tags.


How to access SQL Server 2012 on EC2 server from my ASP.NET website published on the same EC2 server? My site is not able to establish a connection to the SQL Server 2012 from EC2. My connection string is as follows:

Data Source=AMAZONA-2SSRDM3;Initial Catalog=MyDatabase;Integrated Security=SSPI;

As you can see I am using Windows Authentication; is there something wrong with this approach I am taking? I am new to Ec2

Please help

The New Connection string i am using is

 connectionString="Data Source=http://ec2-46-137-228-121.ap-southeast-1.compute.amazonaws.com/;Initial Catalog=MyDb;User ID=something;Password=something"

More Info : I just restored a new database on to my Ec2 Machine . My sql server instance was using only Windows Authentication which i changed to Mixed Mode. I created a new Login something and made it the Db Owner of the Restored Database as well

Change the data source parameter of the connection string to "localhost".  When everything is running on the same machine there's no need to specify the actual host name.  If you do need to specify the host name, when SQL Server is running on one server and the database is on another server for example, only specify the name of the server.  In your case the connection string would reference the internal name of the server.  In that case the connection string would look something like this.

connectionString="Data Source=ec2-46-137-228-121;Initial Catalog=MyDb;User ID=something;Password=something"

What ever the value in the data source you should be able to ping that name to make it work.


SQL Server 11.0 and AD Groups I have a user that has his Active Directory logon set to allow logging into a specific SQL Server, and that works fine.  This user is also part of an AD Group that has access to a specific database on this server. However, this membership doesn't grant him access to that specific database. I have to give his AD logon access to the database separately.

Question: Is there a conflict when granting SQL Server access via a user's AD login, and also adding permissions to a specific database on the SQL Server by the user's membership in a specific AD group?

If there is anyway to set this up to work let me know.

You will have no problem doing what you have described. SQL Server security is designed intentionally to allow you to grant different permissions to a group and to a person in that group, and keep the two separated.  They did this to address exactly the scenario that you are describing.

The only way that you would have a conflict is if you explicitly deny a permission. Then the deny would override any grant.


Can I change Fill Factor option for our database server? I have a production database for ERP which has clustered and non clustered indexes. Some other tables have index type as heap and  i don't understand what does heap means. Every month i have a job run this command sp_msforeachtable "dbcc dbreindex('?')"  to rebuild all indexes in our database  after that i checked the fragmentation and i found it to be still 98%. I searched on web to discover that our  Fill factor option for our server is 0, so can i change it to suitable percentage and what factors I need to consider?

As stated in the documentation "Fill factor 0 and 100 are actually interchangeable and fill factor does not apply to heaps, which are tables without a clustered index.

Brent Ozar has pretty good writeup on fillfactor settings which I recommend that you look at but it boils down to this:

Don't spend too much time on the exact fill factor unless you can pinpoint specific performance problems to page splits and then change those from the defaults, for instance indexes on GUID columns which are modified frequently

Use Ola Hallengrens maintenance solution which rebuilds those indexes which are fragmented and skips the others making maintenance run faster and with less load on the server.

If your application uses quite a lot of heaps Ola's solution does not help rebuilding those. You can run alter table <tablename> rebuild to rebuild the heaps ( Alter Table rebuild command applies to SQL Server 2008 and above) but Tibor Karaszi has more intelligent solution which you can run periodically.


ORA-12631 using Database Links with Oracle DBMS 19c on Windows Server 2019 We had a set of long-time Oracle 12c Database installations (multiple instances on separate VMs) on Windows Server.  Over the past year we transitioned to Oracle 19c on Windows Server 2019.  We found that database links that worked in the 12c environment did not work in the 19c environment; a query referencing such a link would fail with the error ORA-12631: Username retrieval failed.  SQLNET traces showed
naun5validate: SPP is NEGOTIATE

naun5validate: SSPI: 0x8009030c error in AcceptSecurityContext

For many years we have adjusted the configuration of our Windows DBMS hosts by revoking the Access this computer from the Network User Right from the Everyone group.  With previous versions of Oracle DBMS this did not cause an issue, but with 19C we found that we needed to grant this user right on each database link target host to the AD service account used to run each database link source host.


Help with adding a time filter to Postgres query I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes...

I know Postgres is brilliant for grouping points based on their spatial values - that I can do - but is there a way I can group points by their 'closeness' in time. By this I mean, how would I go about identifying points that were created within x minutes of the first point.

To put it into some context, the first point could be the first Facebook status update immediately after Arsenal scored against Aston Villa. There would be a sharp rise in status updates immediately after the goal before gradually trailing off.

So..If I had a dataset of 50,000 records representing Facebook status updates each with a random time and then I edited 1000 of these to be within 10 minutes of the first post, how would I best approach this and what would the SQL look like?

The time format is arranged as YY:MM:DD HH:MM:SS.

So far I have been able to return those records which are close spatially and have a similar message, but adding in the time element so I have results that are close spatially, temporally and share the same topic has proved a little more difficult for me.

I've looked at the interval function but I am unsure of how exactly to deploy this in my SQL. Would it be that first I filter records by their spatial dispersal, then filter which are close in time and finally the similarity of their status?

My SQL looks something like this for now..

Creating the table...

CREATE TABLE FB_status (
id character varying (254),
status character varying (254),
longitude numeric,
latitude numeric,
posted_at character varying (254),
geom geometry(POINT));

My SQL thus far...

SELECT (a.status, b.status)
FROM FB_status AS a
JOIN FB_status AS b
ON ST_Dwithin (a.geom, b.geom, 0.01)
WHERE similarity (a.status, b.status) > 0.5;

posted_at should certainly be data type timestamptz, not varchar.
(And id should be int or bigint.)

Assuming timestamptz for posted_at and leaving further performance optimization aside, you could make it work range types and the range overlap operator &&:

SELECT (a.status, b.status)
FROM   FB_status a
JOIN   FB_status b ON ST_Dwithin (a.geom, b.geom, 0.01)
WHERE  similarity (a.status, b.status) > 0.5
AND   tstzrange(a.posted_at, a.posted_at + interval '10 min')
   && tstzrange(b.posted_at, b.posted_at + interval '10 min');

One might argue it should really be:

   tstzrange(a.posted_at - interval '5 min', a.posted_at + interval '5 min')
&& tstzrange(b.posted_at - interval '5 min', b.posted_at + interval '5 min')

But the result is the same for a self-join.

In reality, you probably need to optimize performance with nearest-neighbor queries on top of (functional) GiST indexes using LATERAL joins ...

I would consider operating with a spacial or temporal raster (granulation) instead to largely simplify things. Using generate_series() and / or width_bucket().


Greenplum ERROR: Canceling query because of high VMEM usage We have a GreenPlum Cluster which we have set up recently and getting this error on a single query run:
current group id is 140611, group memory usage 40720 MB, group shared memory quota is 31320 MB, slot memory quota is 0 MB, global freechunks memory is 1044 MB, global safe memory threshold is 1048 MB (runaway_cleaner.c:197) SQL state: XX0
I'll be happy to post the settings we currently have in place that may help able to troubleshoot further.
Here are some basic ones I can share:
Cluster has got 128GB RAM on each node host
SWAP on each Node is 32GB
 groupid  |   groupname   | concurrency | cpu_rate_limit | memory_limit | memory_shared_quota | memory_spill_ratio | memory_auditor | cpuset
 24964400 | my_user_group    | 10          | 60             | 60           | 80                  | 0                  | vmtracker      | -1

From ERROR: Canceling query because of high VMEM usage on the vmware Tanzu Community by Faisal Ali:

"Canceling query because of high VMEM usage" is when the segments uses 90% ( default ) of the gp_vmem_protect_limit. If it's the same query with the same error all the time, it would be better to check how much memory is the query using and take a explain plan to understand the query execution path and fine tune the query.


How to DRY record metadata in SQL Server There is a database that has been given to me, and all tables contain these columns:

CreatorUserId
CreatorUsername
CreationDate
ModifierUserId
ModifierUsername
ModificationDate

However, it seems that this design is not DRY and it's really hard to maintain because this database contains like 300 tables.

On the other hand, the modification history and creation history and lot's of other actions on the database level matter to us.

Is there any better way to record any DDL and DML operations?

DDL (Data Definition Language: create/alter/drop/...) and DML (Data Manipulation Language: insert/update/delete/...) operations will need to be logged separately as the first affects the structure of the tables (and other objects) where the second affects the contents of those objects. I'll only deal with the DML side (when your application modifies table contents on behalf of a user) here.

Either way keeping the user ID and user name separately is not normal form - unless one isn't direclty modified (i.e. if the name is maintained as a computed column) you could potentally end up with data inconsistency. Also if you are trying to maintain an audit of data modifications you should have some sort of timestamp against the action. A generic arrangement might look like:

                   ActionAudit
ThingTable         ---------------
------------       AuditRowID (PK)
ThingID (PK)  <--  ThingID    (FK)       Users
Property1          Action                -------------
Property2          UserID     (FK)  -->  UserID   (PK)
   ...             TimeStamp             USerName
PropertyN

(where @action@ in the audit table is an enumeration representing insert,update,delete)
Of course you now have an extra table to maintain as whenever rows are affected in ThingTable you need to add an entry in AuditTable too, but you do have a complete list of who changed to the data not just the most recent user to do so.

If you want to keep a copy of the user ID (and the name too if you like) that inserted or last updated a row in ThingTable, then you could use a computed column or maintain those columns by a trigger that fires when an entry is made in ThingTable.

If you want to audit multiple objects this way, you either need an action audiot table per other object, or you need to drop the FK relationship with the ThingID in the one table, or you need a general Things table to stay in normal form, like so:

                                                ActionAudit
ThingType1Table             Things              ---------------
---------------             ------------        AuditRowID (PK)
ThingID   (PK, FK)  ----->  ThingID (PK)  <---  ThingID    (FK)       Users
Property1             |                         Action                -------------
Property2             |                         UserID     (FK)  -->  UserID   (PK)
   ...                |                         TimeStamp             USerName
PropertyN             |
                      |
                      |
ThingType2Table       |
---------------       |
ThingID   (PK, FK)  --'
Property1
Property2
   ...
PropertyN

If you need to note what changed each time then you'll need to add some way of recording that, but as all you are recorindg right now id the user I assume you don't need this level of recording. If you did you could do something like:

                     ThingFullAudit
                     -------------------
                .--  ThingID    (FK)
                |    AuditRowID (PK, FK) --.
                |    Property1             |
                |    Property2             |
                |       ...                |
                |    PropertyN             |
                |                          |
                |                          |
                |    ActionAudit           |
ThingTable      |    ---------------       |
------------    |    AuditRowID (PK)  <----'
ThingID (PK)  <----  ThingID    (FK)            Users
Property1            Action                     -------------
Property2            UserID     (FK)  ------->  UserID   (PK)
   ...               TimeStamp                  USerName
PropertyN

(that is based on the audit table per other object version: I'll leave merging this concept with the single audit table for all objects idea as an exercise for the reader!)
You can maintain the ThingFullAudit table using a trigger on ActionAudit, assuming the row in ThingTable is added/modified before the row in ActionAudit is inserted, that copies the contents of ThingTable to ThingFullAudit. You can then see what changes each user made in each update.

Of course this may all be massively over-kill for your logging requirements - you'll need to describe what the existing columns are trying to acheive in order for us to give you a more specific and concise answer.


Generate linear growth report My table has the following data:

2016-01-01 : 1
2016-01-02 : 0
2016-01-03 : 4
2016-01-04 : 1
2016-01-05 : 2

I want to generate a report (linear growth) like this:

date       : linear growth
2016-01-01 : 1
2016-01-02 : 1
2016-01-03 : 5
2016-01-04 : 6
2016-01-05 : 8

Are there any good ideas for a SQL statement to do this?

MySQL doesn't support Window Functions such as SUM(...) OVER(...) but it can be calculated using a variable, a self join or a subquery.

Queries using the question's sample

Variable

SELECT created, value
    , @growth := @growth + value as growth
FROM data
  , (SELECT @growth := 0 as growth) as v
ORDER BY created

Output

| created                   | value | growth |
|---------------------------|-------|--------|
| January, 01 2016 00:00:00 |     1 |      1 |
| January, 02 2016 00:00:00 |     0 |      1 |
| January, 03 2016 00:00:00 |     4 |      5 |
| January, 04 2016 00:00:00 |     1 |      6 |
| January, 05 2016 00:00:00 |     2 |      8 |

Self JOIN

This can also be done with a self JOIN:

SELECT d1.created, d1.value
    , SUM(d2.value) as growth
FROM data d1
INNER JOIN data d2
    ON d1.created >= d2.created
GROUP BY d1.created, d1.value
ORDER BY d1.created;

Subquery

Or with a subquery:

SELECT d1.created, d1.value
    , (
      SELECT SUM(value)
      FROM data
      WHERE created <= d1.created
    ) as growth
FROM mydata d1
ORDER BY d1.created;

This SQL Fiddle contains both queries (variable, self join and subquery)

Query with growth for several IDs

Variables

If you want to calculate the growth for several IDs, an extra @id variable and a CASE statement can be added:

SELECT created, value
    , CASE WHEN @id <> d.id THEN @growth := value
        ELSE @growth := @growth + value
      END as growth
    , @id := d.id as id
FROM data d
  , (SELECT @growth := 0 as growth, @id := 0 as id) as v
ORDER BY d.id, d.created;

Self JOIN

SELECT d1.created, d1.value
    , SUM(d2.value) as growth
    , d1.id
FROM data d1
INNER JOIN data d2
    ON d1.created >= d2.created AND d1.id = d2.id
GROUP BY d1.created, d1.value, d1.id
ORDER BY d1.created;

Subquery

SELECT d1.id, d1.created, d1.value
    , (
      SELECT SUM(value)
      FROM data
      WHERE created <= d1.created AND id = d1.id
    ) as growth
FROM data d1
ORDER BY d1.id, d1.created;

This SQL Fiddle contains both queries for growth by IDs (variables, self join or subquery)


Store two metric sets long or wide if frequently joined? Given two similar tables, metrics_a and metrics_b, PostgreSQL 9.6:

create table metric_a (
    logdate date not null;
    point_id int4 not null;
    m00 numeric(9,5) not null;
    ...
    m23 numeric(9,5) not null;
    CONSTRAINT metric_a_pk PRIMARY KEY (logdate, point_id)
);
create index metric_a_point_idx on metric_a (point_id);

Each with about 150,000 point_id values with hourly measurements into m00, m01, etc. for 24 hours a day. That's about 55 million rows per year per table, and we have 10 years of data so far. There will always be only 2 metrics, a and b.

I assume I should partition by date range, which I'm working on. But in general, for queries where metrics like this are joined for at least 30 days of data, is it better to store them as:

one wide table -- 48 hourly columns, 24 for each metric
one "half-wide" table -- a point_id column and 24 hourly columns
one long table -- logdate, point_id, hour, metric_value
two tables, wide or long?
range partitioned by month, week, or metric? long or wide?

Of course the long table version is by far the easiest to deal with. It's much simpler to write queries to total the whole day, or compare several hours' metrics against each other. However, the table size for the long version could easily total over 13 billion rows with very inefficient use of storage.

I've tried using two wide tables and one combined 48-column wide table (neither partitioned), with little difference in performance.

Current queries look like this:

select a.point_id, count(*) tot_ct, :lim as lim,
    count(case when a.m00 <= :lim then 1 end) a_ct00,
    count(case when a.m01 <= :lim then 1 end) a_ct01,
    count(case when a.m02 <= :lim then 1 end) a_ct02,
    ...
    count(case when (a.m00 - b.m00) >= 4.25
        and b.m00 <= :lim then 1 end) w_ct00,
    count(case when (a.m01 - b.m01) >= 4.25
        and b.m01 <= :lim then 1 end) w_ct01,
    count(case when (a.m02 - b.m02) >= 4.25
        and b.m02 <= :lim then 1 end) w_ct02,
    ...
    avg(a.m00) av_a00, avg(a.m01) av_a01, avg(a.m02) av_a02, ...
    ...
from metrics_a a, metrics_b b
where a.logdate = b.logdate
    and a.point_id = b.point_id
    and a.logdate between '6/1/2017' and '7/31/2017'

The query above and most anything similar to it takes about 8 minutes to run. That's not too bad except I need to run several of these in a batch for different values of :lim and the 4.25 value and then combine and filter the results (currently in Python).

I can provide more details, but this post is already very long. Thanks for any help.

If that's the complete situation I would merge metrics_a and metrics_b for considerably smaller storage and considerably faster and simpler queries.

Your example query could then look like this:

SELECT point_id
     , count(*) AS tot_ct
     , :lim AS lim
     , count(a00 <= :lim OR NULL) AS a_ct00
     , count(a01 <= :lim OR NULL) AS a_ct01
     , count(a02 <= :lim OR NULL) AS a_ct02
     -- ,  ... more
     , count((a00 - b00) >= 4.25 AND b00 <= :lim OR NULL) w_ct00
     , count((a01 - b01) >= 4.25 AND b01 <= :lim OR NULL) w_ct01
     , count((a02 - b02) >= 4.25 AND b02 <= :lim OR NULL) w_ct02
     -- ,  ... more
     , avg(a00) AS av_a00, avg(a01) AS av_a01, avg(a02) AS av_a02  -- , ... more
FROM   metrics
WHERE  logdate BETWEEN '2017-06-01' AND '2017-07-31';

Renamed m00 to a00 and b00 etc. respectively.
Saves at least 28 bytes (tuple header + item identifier) + 8 - 16 bytes for redundant columns per row. Smaller is faster, too. See:

Configuring PostgreSQL for read performance

No join, faster query.
Shorter, simpler code.

Asides:

I shortened the count syntax. Should be about as fast. See:

For absolute performance, is SUM faster or COUNT?

Don't omit the AS keyword for column aliases. See:

Aliases with correlated subqueries

Always use standard ISO 8601 date format. No exceptions. Related:

Can't execute dynamic DDL, argument is NULL


RavenDB - Disaster Recovery on a Replicated Instance EDIT: I'm encountering precisely the same issue.

Assume that I have two RavenDB instances.
Instance 1 contains one document: users/1.
Instance 2 contains three documents: users/2, users/3, and users/4.

_

Assume that I configure and enable replication for both instances.

That is, Instance 1 will replicate to Instance 2 and Instance 2 will replicate to Instance 1.

After replication, both instances will have all four documents.

But both instances will agree that users/1 's Raven-Replication-Source is Instance 1, and users/2, users/3, and users/4 will have their Raven-Replication-Source set to Instance 2.

_

Assume that Instance 1 suffers a catastrophic failure and I need to reinstall RavenDB.

_

My testing shows that in this scenario: users/2, users/3, and users/4 will again be replicated to Instance 1.
However, users/1 will not be replicated to Instance 1 because its Raven-Replication-Source is Instance 1.

My Question:

How can I force Instance 2 to replicate users/1 back to Instance 1, even though users/1 was originally created on Instance 1 and its Raven-Replication-Source is Instance 1?

Configure replication as 'Changed and Replicated'.


AWS RDS MySQL some time get slow performance when update table1 set field='any word' where primarykey=1234 Normally when update table1 set field='any word' where primarykey=1234 take 1sec. or less but sometimes random take much more time possible 500sec. or more.

I was set very well and never see high CPU, or IOPS

I use MySQL RDS 8.0.17 and workaround was down wait_time from 3600(default) to 200 to close sleep connections and then working better.

Finally was the auto commit as mustaccio told me in a comment:

  Looks like your "sleep connections" are not committing their transactions and thus keeping locks. Revise your application logic.


Built-in Postgres' Automatic Backup Is there any automatic built-in mechanism in postgres to backup data if the data have exceeded the maximum permissible storage amount?

The only maximum permissible storage amount is the amount of storage your disk has. When you're running out of space, postgreSQL will simply stop. (with a PANIC message)

You have to monitor your disks free space to prevent it to happen...

Sadly documentation is quite light on it:
https://www.postgresql.org/docs/9.6/static/diskusage.html


Table with image data base64 per row causes deadlocks/block We have a table,

CREATE TABLE [dbo].[MyTable](
    [MasterKey] [uniqueidentifier] NOT NULL,
    [DetailKey] [varchar](100) NULL,
    [JSON] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_MyTable_details] ON [dbo].[MyTable]
(
    [MasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

And we have below query which is causing too many deadlocks/blocks.

IF EXISTS(SELECT 1 from  [MyTable](nolock) where  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey)
BEGIN

 UPDATE [MyTable]
           SET [JSON] = @JSON
    WHERE  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey
END
ELSE
BEGIN
INSERT INTO [MyTable]
           ([MasterKey]
           ,[DetailKey]
           ,[JSON])
     VALUES
           (@MasterKey
           ,@DetailKey
           ,@JSON)
END

Note different user uses MasterKey key at the same time but in peek time we see application becomes unresponsive. When we see the logs we found a lot of block/deadlocks. We also see Sch-S locks in this table.

Will adding MasterKey and DetailKey as primary key can fix this issue or what can we do?

Note sometimes JSON size is huge because it includes images as base64.

In general, you should always change this approach:

IF EXISTS ( /* scan the table with a where clause */ )
    UPDATE ( /* scan the table AGAIN with a where clause */ )
ELSE
    INSERT

To this:

UPDATE ( /* scan the table JUST ONCE with a where clause */ )
IF @@ROWCOUNT = 0
  INSERT

Forget you ever learned the first approach. It's like going to the grocery store to check if they have any eggs, then going back home to get your wallet, and back to the grocery store again to buy your eggs.

Also, yes, a clustered index can help in many scenarios. Is MasterKey really a key (e.g. is it unique)? Or is the combination of MasterKey + DetailKey the candidate key? In the latter case, an index (clustered or not) with both columns as key columns will help reduce any residual searching the query has to do to locate a specific row after it finds the rows with that MasterKey value (which prolongs blocking and can lead to deadlocks depending on other factors).

Actual use case. First, change the indexes:

DROP INDEX [ix_MyTable_details] ON [dbo].[MyTable];

CREATE UNIQUE CLUSTERED INDEX cix_MyTable_details
  ON dbo.MyTable(MasterKey, DetailKey);

Then change the query to:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

UPDATE dbo.[MyTable]
  SET [JSON] = @JSON
  WHERE MasterKey = @MasterKey AND DetailKey = @DetailKey;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.[MyTable]
       ([MasterKey]
       ,[DetailKey]
       ,[JSON])
  VALUES
       (@MasterKey
       ,@DetailKey
       ,@JSON);
END

COMMIT TRANSACTION;


You create an extra table with customer ID and all other fields that will possibly change over the time.

In the orders table, you link to the id of this new table, not to the costumers tables.

customers(customer_id, first_name, last_name...)
customer_info(id, customer_id, address, other field)
orders(id, customer_info_id, order_date, ...etc)


SELECT Proxy User For Other Sessions When a Proxy Authentication is used, there is the impersonated user (called the client) and the impersonating user (called the proxy).  So if Bob wants all the privileges of App1 and has been granted the privilege to connect as App1, then the client is App1 and the proxy is Bob.  I would like a query of sessions that provides both the client and the proxy.

v$session shows schemaname as the client user.  It has osuser and machine columns from which the proxy may be inferred, but it would be fragile at best.

v$session_connect_info changes the column authentication_type to proxy when proxy authentication is in use, but that only indicates that proxying is in effect, not who the proxy is.

The proxy_users table gives all the potential proxy/client combinations, which would work if there were only one match for the client, but wouldn't otherwise.

As the proxy user a selection could be done of SYS_CONTEXT with a parameter of PROXY_USER to get it, but that does not allow a session to get the proxy for other sessions.

Update:

MOS Doc ID 782078.1 shows how this can be done by joining dba_audit_trail with itself if auditing is turned on specifically and the user performs an audited action.  It seems like a simpler solution should exist.

I don't believe this info appears in any v$ or dba views. Using auditing is the only method.


Duplicate entry in MySQL slave I have a MySQL master-slave replication setup. When I checked show slave status, I got the result as Slave_Sql_running:No and last error as Duplicate entry 'x' on query.

But when I tried to delete the entry from that table result shown as 0 rows affected.

I tried to fetch the record using select statement. Even then the result shown as 0 rows affected.

Then I checked slave status again. The same state persists and the same error appears.

First, let me start with a warning. Be very careful when you start messing with the data on the slave. It can really ruin the integrity of the replication setup.

That being said, if you are absolutely certain you don't want the slave to execute that particular statement, you can issue the following commands on the slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

This will skip the next statement in the replication log and move on. Here's the documentation for reference.


How to model an entity type that can have different sets of attributes? I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items.
This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.:
Car structure:
+----+--------------+--------------+
| PK | Attribute #1 | Attribute #2 |
+----+--------------+--------------+

Boat structure:
+----+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 |
+----+--------------+--------------+--------------+

Plane structure:
+----+--------------+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+--------------+--------------+--------------+--------------+

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.
Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column.
In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query.
I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal.
Additional information
These are my responses to MDCCL comments:

1. How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?

In fact, it’s very simple: There are only five Categories in total.

2. Will the same Item always belong to the same User (that is, once a given Item has been “assigned” to a certain User it cannot be changed)?

No, they could change. In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.

3. Are there attributes that are shared by some or all of the Categories?

Not shared but, from memory, I can tell that at least three attributes are present in all Categories.

4. Is there a chance that the cardinality of the relationship between User and Item is many-to-many (M:N) instead of one-to-many (1:M)? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

5. Regarding your following response to one of the question comments:

“In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.”

It seems that you are planning to keep track of the item ownership evolution, so to speak. In this way, which attributes would you like to store about such phenomenon? Only the modification of the attribute that indicates the specific User who is the Owner of a specific Item?

No, not really. The ownership may change, but I don’t need to keep track of the previous Owner.

According to your description of the business environment under consideration, there exists a supertype-subtype structure that encompasses Item —the supertype— and each of its Categories, i.e., Car, Boat and Plane (along with two more that were not made known) —the subtypes—.
I will detail below the method I would employ to manage said scenario.
Business rules
In order to start delineating the relevant conceptual schema, some of the most important business rules determined so far (restricting the analysis to the three disclosed Categories only, to keep things as brief as possible) can be formulated as follows:

A User owns zero-one-or-many Items.
An Item is owned by exactly-one User at a specific instant.
An Item may be owned by one-to-many Users at distinct points in time.
An Item is classified by exactly-one Category.
An Item is, at all times,

either a Car
or a Boat
or a Plane.

Illustrative IDEF1X diagram
Figure 1 displays an IDEF1X1 diagram that I created to group the previous formulations along with other business rules that appear pertinent:

Supertype
On the one hand, Item, the supertype, presents the properties† or attributes that are common to all the Categories, i.e.,

CategoryCode —specified as a FOREIGN KEY (FK) that references Category.CategoryCode and functions as a subtype discriminator, i.e., it indicates the exact Category of subtype with which a given Item must be connected—,
OwnerId —distinguished as a FK that points to User.UserId, but I assigned it a role name2 in order to reflect its special implications more accurately—,
Foo,
Bar,
Baz and
CreatedDateTime.

Subtypes
On the other hand, the properties‡ that pertain to every particular Category, i.e.,

Qux and Corge;
Grault, Garply and Plugh;
Xyzzy, Thud, Wibble and Flob;

are shown in the corresponding subtype box.
Identifiers
Then, the Item.ItemId PRIMARY KEY (PK) has migrated3 to the subtypes with different role names, i.e.,

CarId,
BoatId and
PlaneId.

Mutually exclusive associations
As depicted, there is an association or relationship with a one-to-one (1:1) cardinality ratio between (a) each supertype occurrence and (b) its complementary subtype instance.
The exclusive subtype symbol portrays the fact that the subtypes are mutually exclusive, i.e., a concrete Item occurrence can be supplemented by a single subtype instance only: either one Car, or one Plane, or one Boat (never by zero or less, nor by two or more).
†, ‡ I employed classic placeholder names to entitle some of the entity type properties, as their actual denominations were not supplied in the question.
Expository logical-level layout
Consequently, in order to discuss an expository logical design, I derived the following SQL-DDL statements based on the IDEF1X diagram displayed and described above:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business context.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Category (
    CategoryCode     CHAR(1)  NOT NULL, -- Meant to contain meaningful, short and stable values, e.g.; 'C' for 'Car'; 'B' for 'Boat'; 'P' for 'Plane'.
    Name             CHAR(30) NOT NULL,
    --
    CONSTRAINT Category_PK PRIMARY KEY (CategoryCode),
    CONSTRAINT Category_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Item ( -- Stands for the supertype.
    ItemId           INT      NOT NULL,
    OwnerId          INT      NOT NULL,
    CategoryCode     CHAR(1)  NOT NULL, -- Denotes the subtype discriminator.
    Foo              CHAR(30) NOT NULL,
    Bar              CHAR(40) NOT NULL,
    Baz              CHAR(55) NOT NULL,
    CreatedDateTime  DATETIME NOT NULL,
    --
    CONSTRAINT Item_PK             PRIMARY KEY (ItemId),
    CONSTRAINT Item_to_Category_FK FOREIGN KEY (CategoryCode)
        REFERENCES Category    (CategoryCode),
    CONSTRAINT Item_to_User_FK     FOREIGN KEY (OwnerId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE Car ( -- Represents one of the subtypes.
    CarId INT          NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Qux   DATE         NOT NULL,
    Corge DECIMAL(5,2) NOT NULL,
    --
    CONSTRAINT Car_PK         PRIMARY KEY (CarId),
    CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidQux_CK    CHECK       (Qux >= '1990-01-01')
);

CREATE TABLE Boat ( -- Stands for one of the subtypes.
    BoatId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Grault SMALLINT NOT NULL,
    Garply DATETIME NOT NULL,
    Plugh  CHAR(63) NOT NULL,
    --
    CONSTRAINT Boat_PK         PRIMARY KEY (BoatId),
    CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidGrault_CK  CHECK       (Grault <= 10000)
);

CREATE TABLE Plane ( -- Denotes one of the subtypes.
    PlaneId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Xyzzy   BIGINT   NOT NULL,
    Thud    TEXT     NOT NULL,
    Wibble  CHAR(20) NOT NULL,
    Flob    BIT(1)   NOT NULL,
    --
    CONSTRAINT Plane_PK         PRIMARY KEY (PlaneId),
    CONSTRAINT Plane_to_Item_PK FOREIGN KEY (PlaneId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidXyzzy_CK    CHECK       (Xyzzy <= 3258594758)
);

This has been tested in this db<>fiddle running on MySQL 8.0.
As demonstrated, the superentity type and each of the subentity types are represented by the corresponding base table.
The columns CarId, BoatId and PlaneId, constrained as the PKs of the appropriate tables, help in representing the conceptual-level one-to-one association by way of FK constraints§ that point to the ItemId column, which is constrained as the PK of the Item table. This signifies that, in an actual “pair”, both the supertype and the subtype rows are identified by the same PK value; thus, it is more than opportune to mention that

(a) attaching an extra column to hold system-controlled surrogate values‖ to (b) the tables standing for the subtypes is (c) entirely superfluous.

§ In order to prevent problems and errors concerning (particularly FOREIGN) KEY constraint definitions —situation you referred to in comments—, it is very important to take into account the existence-dependency that takes place among the different tables at hand, as exemplified in the declaration order of the tables in the expository DDL structure, which I supplied in this db<>fiddle too.
‖ E.g., appending an additional column with the AUTO_INCREMENT property to a table of a database built on MySQL.
Integrity and consistency considerations
It is critical to point out that, in your business environment, you have to (1) ensure that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart, and, in turn, (2) guarantee that said “subtype” row is compatible with the value contained in the “discriminator” column of the “supertype” row.
It would be very elegant to enforce such circumstances in a declarative manner but, unfortunately, none of the major SQL platforms has provided the proper mechanisms to do so, as far as I know. Therefore, resorting to procedural code within ACID TRANSACTIONS it is quite convenient so that these conditions are always met in your database. Other option would be employing TRIGGERS, but they tend to make things untidy, so to speak.
Declaring useful views
Having a logical design like the one explained above, it would be very practical to create one or more views, i.e., derived tables that comprise columns that belong to two or more of the relevant base tables. In this way, you can, e.g., SELECT directly FROM those views without having to write all the JOINs every time you have to retrieve “combined” information.
Sample data
In this respect, let us say that the base tables are “populated” with the sample data shown below:
--
INSERT INTO UserProfile
    (UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
    (1, 'Edgar', 'Codd', '1923-08-19', 'M', 'ted.codd', CURDATE()),
    (2, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M', 'michelangelo', CURDATE()),
    (3, 'Diego', 'Velázquez', '1599-06-06', 'M', 'd.velazquez', CURDATE());

INSERT INTO Category
    (CategoryCode, Name)
VALUES
    ('C', 'Car'), ('B', 'Boat'), ('P', 'Plane');

-- 1. ‘Full’ Car INSERTion

-- 1.1
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (1, 1, 'C', 'Motorway', 'Tire', 'Chauffeur', CURDATE());

 -- 1.2
INSERT INTO Car
    (CarId, Qux, Corge)
VALUES
    (1, '1999-06-11',  999.99);

-- 2. ‘Full’ Boat INSERTion

-- 2.1
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (2, 2, 'B', 'Ocean', 'Anchor', 'Sailor', CURDATE());

-- 2.2
INSERT INTO Boat
    (BoatId, Grault, Garply, Plugh)
VALUES
    (2, 10000, '2016-03-09 07:32:04.000', 'So far so good.');

-- 3 ‘Full’ Plane INSERTion

-- 3.1
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (3, 3, 'P', 'Sky', 'Wing', 'Aviator', CURDATE());

-- 3.2

INSERT INTO Plane
    (PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
    (3, 3258594758, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris. ', 'Here we go!', TRUE);

--

Then, an advantageous view is one that gathers columns from Item, Car and UserProfile:
--

CREATE VIEW CarAndOwner AS
    SELECT C.CarId,
           I.Foo,
           I.Bar,
           I.Baz,
           C.Qux,
           C.Corge,
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Car C
          ON C.CarId = I.ItemId
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;

--

Naturally, a similar approach can be followed so that you can as well SELECT the “full” Boat and Plane information straight FROM one single table (a derived one, in these cases).
After that —if you do not mind about the presence of NULL marks in result sets— with the following VIEW definition, you can, e.g., “collect” columns from the tables Item, Car, Boat, Plane and UserProfile:
--

CREATE VIEW FullItemAndOwner AS
    SELECT I.ItemId,
           I.Foo, -- Common to all Categories.
           I.Bar, -- Common to all Categories.
           I.Baz, -- Common to all Categories.
          IC.Name      AS Category,
           C.Qux,    -- Applies to Cars only.
           C.Corge,  -- Applies to Cars only.
           --
           B.Grault, -- Applies to Boats only.
           B.Garply, -- Applies to Boats only.
           B.Plugh,  -- Applies to Boats only.
           --
           P.Xyzzy,  -- Applies to Planes only.
           P.Thud,   -- Applies to Planes only.
           P.Wibble, -- Applies to Planes only.
           P.Flob,   -- Applies to Planes only.
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Category IC
          ON I.CategoryCode = IC.CategoryCode
   LEFT JOIN Car C
          ON C.CarId = I.ItemId
   LEFT JOIN Boat B
          ON B.BoatId = I.ItemId
   LEFT JOIN Plane P
          ON P.PlaneId = I.ItemId
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;

--

The code of the views here shown is only illustrative. Of course, doing some testing exercises and modifications might help to accelerate the (physical) execution of the queries at hand. In addition, you might need to remove or add columns to said views as the business needs dictate.
The sample data and all the view definitions are incorporated into this db<>fiddle so that they can be observed “in action”.
Data manipulation: Application program(s) code and column aliases
The usage of application program(s) code (if that is what you mean by “server-side specific code”) and column aliases are other significant points that you brought up in the next comments:

I did manage to workaround [a JOIN] issue with server-side specific code, but I really don't want to do that -AND- adding aliases to all columns might be "stressing".

Very well explained, thank you very much. However, as I suspected, I'll have to manipulate the result set when listing all data because of the similarities with some columns, since I don't want to use several aliases to keep the statement cleaner.

It is opportune to indicate that while using application program code is a very fitting resource to handle the presentation or graphical features —i.e., the external level of representation of a computerized information system— of data sets, it is paramount that you avoid carrying out data retrieval on a row-by-row basis to prevent execution speed issues. The objective should be to “fetch” the pertinent data sets in toto by means of the robust data manipulation instruments provided by the (precisely) set engine of the SQL platform so that you can optimize the behaviour of your system.
Furthermore, utilizing aliases to rename one or more columns within a certain scope may appear stressing but, personally, I see such resource as a very powerful tool that helps to (i) contextualize and (ii) disambiguate the meaning and intention ascribed to the concerning columns; hence, this is an aspect that should be thoroughly pondered with respect to the manipulation of the data of interest.
Similar scenarios
You might as well find of help this series of posts and this group of posts which contain my take on two other cases that include supertype-subtype associations with mutually exclusive subtypes.
I have also proposed a solution for a business environment involving a supertype-subtype cluster where the subtypes are not mutually exclusive in this (newer) answer.

Endnotes
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
2 In IDEF1X, a role name is a distinctive label assigned to a FK property (or attribute) in order to express the meaning that it holds within the scope of its respective entity type.
3 The IDEF1X standard defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.


How would I build a data model for parking lot hours and respective parking fees? Say a parking lot has the following hours and associated costs...

Mon-Fri 7am - 5pm parking is $5/2hrs
Mon-Fri 5pm - 7am parking is $1/hr
Sat and Sun - 12am - 12pm parking is free

How would you model this data such that I can ask questions like "What does Parking Lot A cost right now?"

I'm not sure at the moment, but it seems like a model should accomodate multiple prices for certain times of the day. For example...

Mon-Fri 7am - 5pm parking is $5/2hrs or $7/3hrs

It should also be easy to modify if, for example, a lot's cost ever changes.

Here's a general idea I had so far...

ParkingLot -
    ParkingLotId
    Name
    Address

ParkingLotSchedule -
    ParkingLotId
    DayOfTheWeek
    StartTime
    EndTime
    Cost

Then I could basically write a query something like...

select cost from parkingLotSchedule where parkingLotId = currentLotId and dayOfTheWeek = today and currentTime between startTime and endTime

This doesn't really take into account the possibility of multiple prices for certain times (granted I'm not 100% sure that's necessary yet). It also seems like there could potentially be a lot of records for a single lot.

Is there a better way to do this? If so, what would a model look like?

Depending on how many parking lots you're operating, I wouldn't be too worried about how many records you have to manage.  It's not like this data would be fast changing, since you'd probably have to get signs printed up with the prices marked and so on.  Still, assuming that you have many lots (many of which have the same pricing) and that you want to minimize data maintenance, you could build a price schedule and map lots to the schedule.

Consider something like this:

ParkingLot -
    ParkingLotId
    Name
    Address

PriceSchedule -
    PriceScheduleId
    Description

PriceScheduleDetail -
    PriceScheduleDetailId
    PriceScheduleId
    DayOfTheWeek
    StartTime
    EndTime
    DollarsPerUnit
    MinutesPerUnit
    MinutesMinimumStay

ParkingLotPrice -
    ParkingLotId
    PriceScheduleId
    EffectiveDate
    ExpiryDate

Note that the PriceScheduleDetail table allows you to keep two or more versions of a price for a given time of day range.

Note also that the ParkingLotPrice table uses effective and expiry dates to link parking lots to a price table by date range.  This allows you to set up a price change in advance so you don't need to load new data in the middle of the night when a price change takes effect.

You're queries for pricing are going to have to find the version of the price that applies based on the minimum stay length, which is much more complicated than just "What is the price right now?" since it will depend on how long a stay is and which is the best price that the customer qualifies for.  Also, the price a customer pays will depend on how long their stay is and it may cross one or more price point boundaries.


Clickhouse create database structure for json data New to clickhouse and stuck on the database creation structure for importing json data which is nested

Take for example the json data that looks like the following

when there is data populated

"FirewallMatchesActions": [
    "allow"
  ],
  "FirewallMatchesRuleIDs": [
    "1234abc"
  ],
  "FirewallMatchesSources": [
    "firewallRules"
  ],

or

"FirewallMatchesActions": [
    "allow",
    "block"
  ],
  "FirewallMatchesRuleIDs": [
    "1234abc",
    "1235abb"
  ],
  "FirewallMatchesSources": [
    "firewallRules"
  ],

but there maybe json data which doesn't have them populated

  "FirewallMatchesActions": [],
  "FirewallMatchesRuleIDs": [],
  "FirewallMatchesSources": [],

what would the clickhouse create database structure look like ?

ClickHouse supports types of columns as Array as Nested.

It looks like for your case Array will be enough:

CREATE TABLE json_import (
  TimeStamp DateTime DEFAULT now(),

  /* other columns */

  FirewallMatchesActions Array(String),
  FirewallMatchesRuleIDs Array(String),
  FirewallMatchesSources Array(String)
) ENGINE = MergeTree()
ORDER BY (TimeStamp);

/* insert test data */

INSERT INTO json_import (FirewallMatchesActions, FirewallMatchesRuleIDs, FirewallMatchesSources)
VALUES (['allow'], ['1234abc', '1235abb'], ['firewallRules']),
       (['allow', 'block'], ['1234abc'], ['firewallRules']),
       ([], [], []);

/* select data */
SELECT *
FROM json_import

/* result
┌───────────TimeStamp─┬─FirewallMatchesActions─┬─FirewallMatchesRuleIDs─┬─FirewallMatchesSources─┐
│ 2020-06-12 06:06:17 │ ['allow']              │ ['1234abc','1235abb']  │ ['firewallRules']      │
│ 2020-06-12 06:06:17 │ ['allow','block']      │ ['1234abc']            │ ['firewallRules']      │
│ 2020-06-12 06:06:17 │ []                     │ []                     │ []                     │
└─────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┘
*/

To reduce storage consumption consider using LowCardinality-type and data encoding.

Additional info:

Nested Data Structures in ClickHouse

Reducing Clickhouse Storage Cost with the LowCardinality Type – Lessons from an Instana Engineer

New Encodings to Improve ClickHouse Efficiency


MySQL Update old data after adding new column I have a table called review, which looks like that

| id | business_id|
|----|------------|
| 1  |     B1     |
| 2  |     B1     |
| 3  |     B1     |
| 4  |     B2     |
| 5  |     B2     |
| 6  |     B2     |

To this table I have added a new column called review_number, which represents an incremental sequence separately for each business (referenced via business_id) and this is handled with BEFORE INSERT trigger. This means for each business the review_number sequence would start from 1 and each new record increments it by one. The new column is BIGINT type and for default value is set to 0 for all existing records.

| id | business_id| review_number |
|----|------------|---------------|
| 1  |     B1     |       0       |
| 2  |     B1     |       0       |
| 3  |     B1     |       0       |
| 4  |     B2     |       0       |
| 5  |     B2     |       0       |
| 6  |     B2     |       0       |

The update trigger is as simple as it can get:

BEFORE INSERT ON review
FOR EACH ROW BEGIN
    SET NEW.`review_number` = (SELECT MAX(review_number) + 1 FROM review WHERE business_id = NEW.business_id);

Problem

My problem and question is, how would I go about updating all the old records after I would update my database where I have live data already. Desired result would be after updating the old data (and when adding new ones after):

| id | business_id| review_number |
|----|------------|---------------|
| 1  |     B1     |       1       |
| 2  |     B1     |       2       |
| 3  |     B1     |       3       |
| 4  |     B2     |       1       |
| 5  |     B2     |       2       |
| 6  |     B2     |       3       |
| 7  |     B1     |       4       | <--- new record for B1
| 8  |     B2     |       4       | <--- new record for B2

I have tried achieving it with some procedures but haven't found a neat way of helping old records. As much as I know then MyISAM engine supports a way to have multiple auto-increment fields and composite primary key on multiple fields and there the auto-increment would be separate for each PK, which would get me the desired result without triggers, but unfortunately I think I cannot change from InnoDB currently.

Looking for suggestions how to update review_number for all existing records so so these wouldn't be defaulted to 0 after migration scripts finish.

You gave away the answer; here are some more details.

create a TEMPORARY MyISAM table with a 2-column PK, second column being AUTO_INCREMENT
INSERT INTO myisam_table (business_id, id) SELECT business_id, id ORDER BY business_id, id -- to get it to fill in review_number.
Do a multi-table UPDATE ... SET your_table.review_number = myisam_table.review_number between the two tables to copy those ids.


With LocalAdmin become sysadmin SQL Server I am a local Admin on my machine.  My account wasn't added when SQL Server was installed, and I don't want to have to reinstall SQL Server.  It worked until I tried to log and run the commands via SQLCMD or SSMS.  I got the error message

  Login failed for user <Machine_Name>/<Windows Name>. Reason: Server is in single user mode. Only one administrator can connect at this time...

I found this blog and added the application name after the m switch.  But no dice, I still get the above error.  The User Account looks fine I guess, I am an Administrator.

Also,
  I had to start the cmd prompt as an Admin to start sqlserver in single user mode, or else I couldn't have access to the log files that the instance was using and it was giving me an error to that.  So I ran the cmd prompt as admin. But still, I am the same admin trying to run SQLCMD or SSMS.  It shouldn't be telling me there is already an admin logged in, because I am that admin.  What am I missing?

This is on SQLExpress.

For such a situation we have made use of PSExec utility to get regain access on the SQL server.

You can download the utility from here , extract and copy the exe file to the drive location from where you can run below command.

Once downloaded you need to open the cmd with admin and run below:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

The path above may vary, like for one of our SQL 2k8 , we found the path to be:

"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

This will make you connect to the current via sever Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM.

YOu can create the required login from here:


MySQL-5.5 InnoDB memory issue Version in use is mysql-5.5.24.

In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version.

I am not seeing free space in innodb_buffer_pool even after all the apps are shut down.

Is there such a difference between the Enterprise and Community versions of MySQL?

In an ideal world, all of your data would be resident in the InnoDB buffer pool, all the time... so I had a really hard time thinking of a situation where evicting data from the InnoDB buffer pool would be a good thing, but I did find two cases.

I assume you're referring to this value:

mysql> show status like 'Innodb_buffer_pool_pages_free';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_free | 28    |
+-------------------------------+-------+
1 row in set (0.00 sec)

I can't directly address the Enterprise aspect, since I don't use it, but I can tell you that I was able to get "MySQL Community Server (GPL) 5.5.15-log" to actually free up space in the InnoDB buffer pool.

If you have a light enough traffic load or small enough data set that your InnoDB buffer pool is technically larger than is needed (and therefore already has blocks free, the value of innodb_buffer_pool_pages_free will actually increase if you delete rows from a table that's already in the buffer pool.

In the example above, I started out with a value of 35 on a test server with very low traffic.  I did SELECT * from a table that probably wasn't all in the pool, which dropped the value to 27.  Then I deleted everything from the table and the value rose to 29... so at least in some cases, deletes from a table that is already in the pool will trigger an increase in the pages_free value, assuming your load isn't such that the pages will be claimed by a different table as soon as they are freed -- which is the case on my servers with much larger datasets and heavier traffic.

Similarly, from a second session, I created a small temporary table using InnoDB.  This cost me 4 blocks from the pool.  Then I closed that session, and immediately regained the 4 blocks back.

These same tests on a busy server don't yield the same results.  The "free" pretty much stays at 0, which means I technically need a larger pool, though performance on that machine is quite adequate.

So the difference you are seeing may be a difference in traffic load and data size, rather than a difference in the server editions.

However, "free space" isn't something to be wished for, here, unless you gain that space by increasing the provisioned size of the pool.

I can think of no reason why actively evicting live data from the InnoDB buffer pool in the interest of "having more free space" there would be beneficial, at all, ever.

If a page is clean (i.e., its contents match the disk files where it came from because it's unmodified or modifications have been written back to the disk files), then it can be reused, without being "free" first.  The InnoDB buffer pool is exactly where you want as much of your data as possible to always be resident.


SQL Server 2012 backward compatibility for backups with 2008 I have a number of clients with SQL Server 2008 and that's what I have here on my server too. I use backup files to send databases back and forth between clients and at my office.

I have read that when you create a backup from SQL Server 2012 there is no way to restore it onto a 2008 instance. I assumed that the compatibility level would take care of this problem, but it doesn't. Therefore, I am at a loss as to how to upgrade. Other than upgrade all my clients all at once, which is impossible, I can think of no clean way to do this.

I have the need to send a database to a client as well as receive a database from a client. This is my first version upgrade on SQL Server, so I'm new to this problem. Any ideas on how to proceed?

There's two things involved here: the version number of the file, and the compatibility level.  When you attach a database to a newer major version (like from 2008 to 2008R2, or 2008R2 to 2012) the database version is changed permanently, and you can't attach that database to an older version again.

The compatibility level is for parsing old-school deprecated T-SQL that used to work in older versions of SQL Server.  It doesn't change how the data is stored on disk.

To give the database to someone on an older version of SQL Server, you'll need to export the data and import it into another database.  Tools like Red Gate's Data Compare are helpful for this.


MySQL stored procedure returns null I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried

call NewProc('tableName','colName')

it returned

  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 'NULL' at line 1". The error may occur in the "set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);

but I have no clue why it's the problem.

CREATE DEFINER = root@% PROCEDURE NewProc (IN tn VARCHAR(20), IN mc VARCHAR(20))
BEGIN
  DECLARE j INT;
  DECLARE tab1 VARCHAR(20);
  DECLARE col1 VARCHAR(20);
  DECLARE stmt VARCHAR(1000);
  SET tab1:=tn;
  SET col1:=mc;
    set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);
    PREPARE stmt FROM @sql0;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET j=1;
WHILE j<@i DO
    SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656' where id=",j);
    PREPARE stmt FROM @sql1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET j=j+1;
END WHILE;
END;

Most likely the issue is with the first dynamic query, the one you are storing into @sql0:

set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);

You are concatenating the value of @i into the query, but @i has not been defined yet at this point – so it is null. Concatenating a null with other string literals gives you a null as well. So @sql0 ends up to be a null and that is what you are attempting to execute subsequently and getting the error mentioned.

My guess is you meant the @i to be part of the dynamic query, so it should probably go like this:

set @sql0=CONCAT('select count(*) into @i from ',tab1);

that is, you need to concatenate the name of @i, not the value. That way the dynamic query will be storing the result of count(*) into @i.

Note, though, that you may also need to declare the variable explicitly before the dynamic query. Otherwise the dynamic query will probably implicitly declare the variable at its own, nested, level, and the variable will go out of scope once the query is completed, and so the @i reference further in your query will still evaluate to null.


How To Update Random ID to a table in mysql? I have table like this:

ID | City     | Prize |
----------------------------
1  |Surabaya  | HP
2  |Surabaya  | Watch
3  |Surabaya  | Bag
4  |Semarang  |
5  |Semarang  |Watch
6  |Semarang  |
7  |Bandung   |
8  |Bandung   |
9  |Bandung   |

My Question is How to creat Update Query for Fill The Prize column where City = '(already in settings)' AND ID =  Random.

ex: I want Update to fill Watch in City = 'Bandung' But ID = 'random' maybe (7,8,9).

I Hope you know What I Mean.

Im Very Apreciated your Advice

Thanks

The simple solution is to materialize the result of the query that identifies the row to update, and fetch that result with a scalar subquery...

UPDATE table_name
   SET prize = 'Watch'
 WHERE id = (SELECT *
               FROM ( SELECT id
                        FROM table_name
                       WHERE City = 'Bandung'
                         AND (Prize != 'Watch' OR Prize IS NULL)
                       ORDER BY RAND()
                       LIMIT 1 ) dt1
             );

This selects a random id FROM the table from the desired set of rows, materializes that as a derived table with the alias `dt1` with one row and one column (the id of the new winner), and then selects that id using a subquery and uses it in the outer where clause to update the row.

There's no chance of 0 rows affected unless everybody already has a watch because we selected the "randomly first" id from among the list of potential winners.


Database design for 2 1-to-many? It's the first time here so I don't know if it's the right place to ask.. I'm not good in database design, just learning, so I'd like some advice if possible.

The case:

I'm building a really small application, a plugin, who's tables will likely have not too much records in real life (less than 50 in extreme situations), so performance is not crucial.

The user can create n Groups. A Group should have a 'hook' field.
For each Group, the user can create n BannerBoxes. So, a Group can have many BannerBoxes, a BannerBox can have only one group. Therefore a 1-many relation.
There can be n languages, and the Language table is already in place and has id_lang field. The language-group relation is 1 (language)-many (groups)

I'll often have to ask the db for all BannerBoxes saved for a certain language, grouped by Group. So, first find all Groups of a language, then for each Group, find its BanneBoxes.

I've thought about 2 solutions, but I cannot decide which could be better, and since I'm definitly a newbe, both could be just wrong.

a. Group entity with [hook, id_lang] fields; BannerBox entity with an [hook] field (desides the others). So when selecting, I can join the Group table with Language table on id_lang and then join with BannerBoxes on hook field.

b. Only the BannerBox entity, in this case with both [hook, id_lang] fields. I could join with Language by id_lang and then group by hook.

I also guess that a Group_BannerBox table [id_group, id_bannerbox] would be useless since I think it's a solution for many-many relations, am I right?

So, what would you do? a. or b.?

Thank you!

Create a User table with a UserId PK and any other user related fields.
Create a Language with a LanguageId PK and the name of the language.
Create a Group table with a GroupId PK, UserId FK, LanguageId FK, Hook? and any group specific fields.
Create a BannerBox table with a BannerBoxId PK, GroupId FK, and any other Banner Box specific fields

This will let you get all the information you need assuming that languages are consistent for a group.

I don't know what your Hook field is so answering @ypercube's comment would be helpful.  In this answer I assumed it was some sort of business logic that was relevant at the group level.


How to get the next auto-increment id in MySQL? How to get the next id in mysql to insert it in the table ?

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("chaminda", id))

Here is an approach for this.

Step 1: Insert without payment_code.

Step 2: Get mysql_insert_id from STEP 1.

Step 3: Update payment_code with where condition as mysql_insert_id.

Code Snippet in Php:
 The commands might have to be corrected.

1.    mysql_query("INSERT INTO payments (date, item, method, payment_code)
    VALUES (NOW(), '1 Month', 'paypal')");

2.     printf("Last inserted record has id %d\n", mysql_insert_id());

3.     mysql_query(“UPDATE payments set payment_code=CONCAT("chaminda",mysql_insert_id()) where id=mysql_insert_id());


How to keep off-site developer databases up to date with production? We have a production database running on SQL Server 2008 R2 with files stored in it.  We were concerned about the size of the database so we've opted to enable FILESTREAM so the files would be stored on the disk and we could hopefully reduce the size of our backups. I have found that we can exclude the FILESTREAM filegroup from the backups but then we have issues querying those tables upon restore (because the fields are missing data, which is expected). Our next thought was then to possibly sync the files from the disk using automated FTP or dropbox or something of that nature.  I haven't completely explored this yet though.

The real problem we're trying to solve here is keeping our developers up to date with the production database. With the growing size of our database it's becoming more and more of a chore to do a full backup and download the database and it will eventually become prohibitive. I'm wondering what other folks are doing to keep remote devs up to date.

I've taken a quick look at SQL Server's log shipping but that just seems like it will require a lot of custom automation (scheduling FTP and restore scripts) to get it working reliably with a few devs in various geographic locations and I'm not sure how to use the secondary database for development since it would likely be read-only. I'm open to any suggestions and please let me know if I can provide more information about our situation that would help bring out suggestions.

UPDATE: The developers only need a reasonably current copy of the production data. This helps to reproduce issues so they can be resolved. We're okay with the data being a bit stale but keeping the transfer times to a minimum would suggest frequent backups.

Thanks for the suggestions so far, just to add a bit more our database is currently 12GB or so and we expect to double that in the near future with the signing of more clients.  I've run a couple test backups with compression enabled and that saved us about 1-2GB but I think that since most of the bulk is the stored files (now in FILESTREAM) that we're not going to get the drastic reduction we would hope for (though we'll probably still compress to save what space we can). I think we'd like to be able to restore the local copies once or twice a week just to keep tabs on things. I'm not exactly sure what our bandwidth cap is from our hosted server but it seems that several people downloading 20GB files several times a month won't be a great use of whatever bandwidth we have. I don't have any metrics on the transaction log growth/usage but I know we have processes to that lots of deletes/inserts so I can imagine it would need to ship a decent amount.

I manage a DB environment for several tier 1 telcos and our data/environments are distributed across the world, there are many ways you can achieve what you're looking for but it depends on your pain points.

Questions:

-How big are the databases, how far are the nodes (or more importantly what is the latency between them), and how much bandwidth do you have?

-How much transaction log activity do you generate?

-How often do you want to restore?

-Are you enabling data compression on the backups?

Some Lessons Learned

-One thing I noticed with copying data across the world is that the Windows CIFS protocol absolutely sucks for doing this.  It is highly inefficient.

We ended up using HTTPS and issuing a GET request.  It was over 10x faster copying backups from Asia to Los Angeles.

Using Multithreading options in Robocopy or other copy programs was useful if you break up the backup into multiple files.

Some Options:

2 Surprising Linux Solutions

A solution I really like is to RSYNC with Linux servers on each end and mount the backup folder.  This is really nice as backups are automatically synced using a protocol that is efficient for WANs.  If you don't want to sync ALL of your backups, mount another drive on your linux server that really mounts to a windows share that you will place backups in.  The RSYNC will take care of it from there and do it much faster than CIFS.  Linux protocols are much better for long distance file copying than Windows CIFS based on my experience.  I spoke with some of CCIEs and they stated the same.
You could also look at a VM WAN Optimizer (Google or 'Bing' WAN optimizer or get more specific, for example high latency high bandwidth WAN optimizer or low latency high bandwidth, whatever your environment is)

SQL Transaction Log Shipping Using FTP is not scary at all.  I had a junior .Net developer get it running fast, although I was impressed and got him hired at another company I worked at.  Problem is you might send a lot more data in a 24 hour period if there are a lot of UPDATES but not INSERTS using log shipping, instead of just copying backups.

SAN File System Block Level Replication: Not sure if you have hardware that supports this, but many mid range SANs allow you to replicate live data to the other ones.  If yours does, this is a great option as it has 0 sql server overhead, unless your network pipe is capped in which case you have other issues.

Transaction replication to a central database:  You could create a replication scheme where you have an up to date copy of the data.  You could then take your backups from that copy so it doesn't have to copy full backups across the wire.

Let us know some of your pain points and we can go through other options.

Also be sure to read this excellent white papers by MS if you go with replication:

Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008


MySQL Workbench - "deploying" schema into a real database a noob here, finding my way around. So, how can I "transfer, deploy or whatever the right term is" the database tables, fields, etc. that I have created with Workbench into a real database?

The dB I have is currently on a local WAMP installation but I also have it on a real web server.

Many thanks,
Alex

EDIT: I wish to use MySQL Workbench to "draw" database tables, columns and all the other info that is needed for a database to function properly and then transfer this onto my server. Meaning, I wish to avoid having to use clunky phpMyAdmin to create database for my application but instead create it on my PC with Workbench and then transfer all this onto real production server. Thanks!

What you want is called either Forward engineering (create a new set of schemas + db objects based on your model) or Synchronization (apply changes in your model to an existing setup and incorporate changes from that setup into your model). The latter feature works best if you first reverse engineered an existing setup into a model and work from there. Synchronization does a 2-way merge and allows to manually exclude changes from being taken over to the other side or even change the direction for changes, if the automatic detection is not right.


Force replication to statement for INSERT to a table to fire trigger on slave We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that this INSERT is coming through using Row-based replication and the trigger (which is on table_a on slave) is not firing. We need to have the trigger on the slave table as that is where our DW is.

Looking around online it looks like this should work if statement-based replication is used. Is it possible to force the INSERT to table A to be processed as statement-based replication? Or is there any other way we can achieve this?

The INSERT itself is deterministic as is the trigger. We are using MySQL 5.6.

If you need any other information please let me know.

As documentation say

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design.

So in anycase statement/row, you don't need to be worrying about the data on slave and it should match the master.
but still if you want and can fit-the-logic on an event, this might be a possibility on slave at the risk of inconsistency!!
As you said this is dw slave, would you consider a separate process to do the task you're looking to do, say a procedure?


Effect of where statement in subquery (to get rid of unnecessary join) Please consider the following query:

select *
from parent p
where p.id in (
  select p.id
  from parent p
  inner join child c on c.parentId = p.id
  where p.age > 50
)

Is it safe (in general!) to rewrite the query to this, in order to save a join? The subquery contains the same alias as the outer query (not a typo).

select *
from parent p
where p.id in (
  select p.id
  from child c
  where c.parentId = p.id
  and p.age > 50
)

My concerns are that the subquery's where-statement is going to be applied to the outer query's result. Is that reasoning correct?

Please note that the query above is just a simple example and the real query is big, where the sub-select is somewhere in some or-condition and contains several joins.

Perhaps it is easier to see with the aid of some rewrites. Your first query can be rewritten to (using the same alias at different levels makes it a bit difficult to understand):

select *
from parent p1
where exists (
  select 1
  from parent p2
  join child c on c.parentId = p2.id
  where p2.age > 50
    and p1.id = p2.id
)

Assuming id is unique in parent this can be rewritten as:

select *
from parent p1
where exists (
  select 1
  from child c
  where c.parentId = p1.id
    and p1.age > 50
)

which in turn can be rewritten to your query.

So you can eliminate the join against parent inside the sub-select. However, your second query is a bit difficult to understand as it is written. I would suggest (I'm guessing your intention)

select *
from parent p1
where exists (
  select 1
  from child c
  where c.parentId = p1.id
)
and p1.age > 50

If parent.id is not unique the above wont hold


How do I count the number of different child id's belonging to a parent record? For instance I have two tables in a music database.

Table album and table track.

Each track record has (amongst other things), an album_id and an artist_id

Each album record has a unique album_id

In order to spot which albums are by 'various artists' I want to count the number of distinct artist_id's which belong to each album.

Such that two or more track records with the same album_id but with differing artist_id's should signal that I can flag the album as 'various artists'

I can do it with multiple queries but I'm seeking the elegant all in one query.

There are various ways to solve this problem, but with a composite index on (album_id, artist_id) most DBMS will manage to use only this index (a full index scan) with the following query and thus be quite efficient:

SELECT album_id
FROM track
GROUP BY album_id
HAVING MIN(artist_id) < MAX(artist_id) ;

If you also want the number of artists per album, you can add COUNT(DISTINCT artist_id) in the select list.

A less obfuscated alternative condition is to use (which could be less efficient than the above MIN < MAX method):

HAVING COUNT(DISTINCT artist_id) > 1 ;