texts: Validating SQL Server Change Tracking Synchronization

texts:
I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the latest changes and then checks to verify that the changes were synchronized correctly.
The way that it goes about this is:

It fetches the current change tracking version at the source together with some statistics about the source table that will later be used to verify that things synched correctly. The query looks like:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
WITH T AS (
    SELECT  [NumericValue] AS [NumericValue]
    FROM [SourceTable]
)
SELECT
    AVG(CONVERT([NumericValue])) AS Avg,
    SUM(CONVERT([NumericValue])) AS Sum,
    COUNT(*) AS Count,
    CHANGE_TRACKING_CURRENT_VERSION() AS CTVersion
FROM T;
COMMIT TRANSACTION;

It fetches all changes between the last version it synchronized to and the source version fetched in step 1. The query to fetch the changes in the desired version range looks like:

SELECT SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, [SYSKEY_PrimaryKey], [PrimaryKey], [NumericValue]
FROM (
    SELECT
        T.*,
        CT.SYS_CHANGE_OPERATION,
        CASE WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN CT.SYS_CHANGE_CREATION_VERSION ELSE CT.SYS_CHANGE_VERSION END AS SYS_CHANGE_VERSION,
        [CT].[PrimaryKey] AS [SYSKEY_PrimaryKey]
    FROM CHANGETABLE(CHANGES [SourceTable], @startVersion) AS CT
        LEFT JOIN [SourceTable] AS T ON T.[PrimaryKey] = CT.[PrimaryKey]

    WHERE CT.SYS_CHANGE_OPERATION = 'D'
        OR (T.[PrimaryKey] = CT.[PrimaryKey])
        AND
            CASE
                WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN
                    CT.SYS_CHANGE_CREATION_VERSION
                ELSE CT.SYS_CHANGE_VERSION
            END <= @endVersion
) AS [SourceTable]

It gets the same statistics from the destination table that it did in step 1. The query looks like:

SELECT
    AVG(CONVERT([NumericValue])) AS Avg,
    SUM(CONVERT([NumericValue])) AS Sum,
    COUNT(*) AS Count
FROM [DestinationTable]

It compares the statistics from steps 1 and 2. If they match then all is good, if not then it knows there was a problem with the synchronization

The problem I'm having is that it is intermittently detecting that the values don't match on tables that experience very rapid updates. Investigating, I have found no evidence of anything actually going wrong in the synchronization process, but the destination seems to lag slightly behind where it needs to be to match the source. When the source table updates slow down, the destination always catches up and matches the source. It seems at this point that the problem stems from the version fetched in step 1 not actually matching the statistics that are fetched at the same time. This seems to indicate that the increment of the change tracking version is not performed atomically together with the actual changes to the table. Unfortunately I haven't found anything in the documentation that explicitly confirms or denies this theory, which makes it difficult to know whether there's something else I need to be looking for. Additionally, if it turns out to be true that the change tracking version and the table state aren't guaranteed to be consistent then I don't know how it would be possible for me to accurately perform validation after synchronization.
Any help clarifying whether it's expected for the version number to lag behind the actual state of the table, or suggestions of techniques I could use to get around this problem would be much appreciated.


texts: Convert date in date list condition to list of date ranges

texts:
I want to look for all the records that occur on specific dates.

SELECT *
FROM table1
WHERE date(column) in ($date1, $date2, ...);

However, as many of you, know this kind of comparison doesn't get along with indexes. So, I was wondering if there is a simple way to convert this query to something of the style of the following query without too much effort (i.e., : not using an external tool).

SELECT *
FROM table1
WHERE (column >= $date1 AND column < $date1 + interval 1 day)
   OR (column >= $date2 AND column < $date2 + interval 1 day)
   ...

So the optimizer can still use the indexes. (I'm using MySQL, but ANSI SQL would be great)


texts: Generating time series based on existing table

texts:
I have a table that has something like this:

|    Zip    |     Population    |
+-----------+-------------------+
|    00001  |     100           |
|    00002  |     200           |
|    00003  |     250           |

and so on. I would like to generate a new table from the above table which looks something like this:

|    Date       |    Zip    |     Population    |
+---------------+-----------+-------------------+
|    01-01-2016 |   00001   |     100           |
|    01-02-2016 |   00001   |     100           |
|    01-03-2016 |   00001   |     100           |
|    ...        |   00001   |     100           |
|    31-12-2016 |   00001   |     100           |
|    01-01-2016 |   00002   |     200           |
|    01-02-2016 |   00002   |     200           |
|    01-03-2016 |   00002   |     200           |
|    ...        |   00002   |     200           |
|    31-12-2016 |   00002   |     200           |

and so on for all the rows in the original table at the top.

I have found a way to generate dates between a specific range (e.g., as outlined here). I also think that I should use LEFT JOIN to achieve that?? I'm fairly new to SQL (or SQL Server, which is what I'm using at the moment). If so, what should I use as the join key? If someone could guide me through the process, I'd greatly appreciate the help. :) Thank you in advance for the answers!


texts: Need database of categories for products and services

texts:
Need to develop a database schema that associates products and services into industry-standard categories.  I know that both the schema and data are out there, yet I'm not finding it.

There are databases that list products (and contain millions of records), but I'm not finding a good database that lists corresponding categories.  For example, individual cell phones might be in a "cell phone" category which, in turn might be in the "consumer electronics" category.  What would be good sources of these types of categories?


texts: How to figure out what a DTSRun command is doing

texts:
I have taken over a SQL 2000 database. I am trying to figure out what a job is doing. Its command is:

DTSRun /~....a big hex string....

I have tried to run:

DTSRun /~....a big hex string.... /!Y

to see what it is doing, but it just repeats the command. Is there a way I can see what this string represents?


texts:
I would like to retrieve records between time spans,All i have is this snippet of string indicating the time span

9:00 AM - 10:00 AM

below is the table schema

CREATE TABLE [dbo].[Samples](
[RID] [int] IDENTITY(1,1) NOT NULL,
[PersonName[nvarchar](20) NOT NULL,
[NOT_AVL_DATE] [datetime] NULL,
[NOT_AVL_FOR] [int] NULL
)

brief:
Rid is Primary key, PersonName is Employee/Staff/Technician Name, NOT_AVL_Date is the datetime the  staff wont be avilable, NOT_AVL_FOR represents time span the staff wont be avilable.
1 - Leave for Full day
2-  Leave for First half of the day
3 - Leave for Second half of the day

Requirement:
Given timeSpan i want the Staff who are not on leave for that time span. Could someone construct a Full Statement , my head is whirling thinking about this..thanks for any help


texts: Updating Database Email Profile for all notifications

texts:
I am creating an email profile for database email in a script that needs to be run on 200+ instances. The script will create a database email profile to send out alerts from agent jobs. Is there a way to go through all the jobs that exist on an instance and change a parameter so it will use the new email profile I created instead of the email profile its currently using? (added bonus if afterwards i can clean up all the unused email profiles)
DECLARE @sb INT;
DECLARE @sc INT;
SET @sb =
(
    SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
);
SET @sc =
(
    SELECT CONVERT(INT, value_in_use)
    FROM sys.configurations
    WHERE name = 'Database Mail XPs'
);
IF (@sb = 1 AND @sc = 1)
BEGIN
    PRINT 'Creating email profile';
    DECLARE @account_id INT;
    EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'DatabaseServices - SQLMail Account',
                                         @display_name = N'XXX - Database Services',
                                         @replyto_address = N'noreply@blah.com',
                                         @description = N'SQL Server generated email from databases services team',
                                         @mailserver_name = 'yyy.xxx.pvt',
                                         @email_address = 'ITDatabaseServices@texasmutual.com',
                                         @port = 25,
                                         @account_id = @account_id OUTPUT;
END


texts: Number of sessions connected to database

texts:
I need to know how many sessions was connected in a period of time for each snapshot.  Is this possible in Oracle?

Thanks


texts: Can't connect to PostgreSQL instance using Python

texts:
I have installed vagrant and virtual box on my laptop to finish a class I am working on. I have created a database called tournament.  I can connect to it directly, create tables, insert data, etc.

However, I am trying to write a python script to connect to it and having great difficulty.

Here is my greatly simplified code:

import psycopg2

psycopg2.connect('dbname=tournament')

Here is what I am getting:

Any help would be greatly appreciated.  I am really frustrated.


texts: How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables

texts:
I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example:

recipe_table:

id  | calories | protein| carbs | fat

recipe1, 100,    20g,     10g,     2g
recipe2, 110,    10g,     12g,     12g
recipe3, 240,    20g,     1g,      23g
....

I needed to create a new table (recipe_index) that would show every possible combination of every recipe in recipe_table as a set of 3, so it would look something like:

recipe_index:

id1     | id2    | id3    |calories| protein | carbs | fat
recipe1, recipe2, recipe3,   450,     50g,      23g,   37g
....

Basically it allows me to query recipe_index and say "what 3 recipe combinations come to a total value that's between 440 calories and 460 calories"

My current code for doing this works at 3 meals, however I end up with about 450,000 records in recipe_index, I need to do this same thing for 4,5 and 6 meals as well, so I'm calculating billions of records at the end of this. Is there a more efficient way of doing this? Perhaps I need to look into partitioning a table for each range?

My current SQL code:

INSERT INTO recipe_index
SELECT distinct '3' as nummeals, t1.id as id1, t2.id as id2, t3.id as id3, 0 as id4,
t1.calories_ps+t2.calories_ps+t3.calories_ps as calories,
t1.protein_ps+t2.protein_ps+t3.protein_ps as
protein, t1.carbohydrate_ps+t2.carbohydrate_ps+t3.carbohydrate_ps as carbohydrate,
t1.fat_ps+t2.fat_ps+t3.fat_ps as fat from recipes t1 inner join  recipes t2  on t1.Id <
t2.Id inner join  recipes t3  on t2.Id < t3.Id WHERE t1.image <> '' AND t2.image <> ''
AND t3.image <> ''


texts:
I had MySQL master/slave replication working properly but suddenly replication stopped and accidentally someone has changed mysql.binxxx file in salve so I am getting this error

  ERROR: 'Got fatal error 1236'.

So I checked latest mysql.bin and log poss from master and changed in slave so replication started but now again new mysql.binxxx created in server so again I am getting fatal error. Is there any fix solution for this without any data loss.


texts:
In my continuing effort to bring my database up to a given standard, I need to ensure that all loading, unloading, and restarting of kernel modules are being audited. I have no idea what this thing is talking about or how I would go about auditing such a thing. This is one in a list of auditable events. Before the list begins, the standard claims that some may require OS settings to audit; I'm assuming this is one of them. Unfortunately, the people who may know are not easily accessible.

Any enlightenment would be greatly appreciated.


texts: Windows Login can't access SQL Server even with SYSADMIN role

texts:
I'm working on AD permissions and have removed my user account from the Domain Admins security group. Now I'm having difficult accessing my SQL Server database with my Windows login. The login is in the sysadmin server role group and has mapped permissions to databases on the server. Any ideas why I can't access the server itself?

I will say that if I add a security group that my Windows login is a part of and give that group read permissions...I can then access the server.

Any advice would be appreciated.

Thank you!


texts: Is it necessary to ANALYZE a table after an index has been created?

texts:
In my transaction, I am creating a temporary table:

create temporary table x on commit drop as
select ...

I also add an index on that table:

create index on x(some_column);

Is it now necessary to run analyze on that table? Or do I only need to analyze the table for updates/deletes after index creation?

In other words, my question is: does the creation of an index already imply an analyze execution?


texts: Multiple databases in same AWS RDS PostgreSQL instance

texts:
I have an existing database in a RDS AWS PostgreSQL instance. I need to create one more database in this instance as spinning up a separate cluster will be expensive. There doesn't seem to be any option to create one more database inside this instance. I understand that I can create a separate schema for this purpose(like in MySQL) but I need to keep the resources for these separate and hence the need for a separate database.

Please let me know if it is possible.
PostgreSQL 9.6.5


texts: Get complete value of varchar(max) field - SQL Server 2005

texts:
I have searched for a couple of hours but cannot find the exact solution for this.

In my SQL Server 2005 database table (tableA) there is a varchar(max) column.

I want to get complete value of that column (now it is not displaying any value) in SQL Server Management Studio.

Can anybody provide me a solution?


texts: How to use psql with no password prompt?

texts:
I wrote a script to REINDEX indexes in a database. Here is one of them:

echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX scm_main.unq_vbvdata_vehicle;"
if [[ ${?} -eq 0 ]]; then
    echo "reindex for unq_vbvdata_vehicle finished at: `date "+%F %T"`" >> ${LOG_FILE}
else
    echo "reindex for unq_vbvdata_vehicle failed" >> ${LOG_FILE}
    exit 1
fi

The problem is I can not run this script in standalone mode. psql is prompting password every time it runs. There is also two limitations:

I can not create a user on database with no password.
Because REINDEX locks tables, I should use sleep <num> between each REINDEX.

Is there any automatic solution?


texts:
All,

I'm familiar with the good reasons not to shrink a database and I am in the process of shrinking a 2TB one. We have a large database that gets backed up each day (by policy) and because of the size, the backup runs most of a day. We have been running for some time with over 60% free and that shouldn't change significantly. I'm reducing the table size to mitigate what the backup is doing to our performance.

The table is large and shrinking is quite slow so I am running a DBCC SHRINKFILE in a looping script where I shrink 250MB at a time. I'm doing this because when the backup starts SHRINKFILE errors out saying it can't change the file system. I'm assuming this is because the backup has the file locked so the file isn't changing in the middle of the backup. I am doing the small shrinks as I been in the middle of one when the backup starts. So I loop it so I can see the shrinkfile errors and kill my script - the backup can start at different times.

My question is that although the DBCC SHRINKFILE can't change the file after the backup starts and I get the error message, the first iteration of the script when the backup is done goes quite quickly and then settles into a normal cadence. As folks have said if you kill a SHRINKFILE, you "don't lose your work". Is the results of the shrink sitting out in tempdb somewhere ready to go? To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea? It would give me a jump on the next day.

Thanks,

Pat


texts:
I have executed many times a similar query to this one

INSERT INTO table (col1,col2,...) SELECT col1, col2,... FROM table

at first, I just want to duplicate the rows in this table, then I ended up having more than 2000 rows on the table after just a few iterations, I know that I made a mistake because I forgot LIMIT N, but what I want know is: If I keep repeating that query, what would happen to the MySQL server?

I know this is similar to the Wheat and chessboard problem.


texts: Tool for migrate SQL Server 2005 to SQL 2017

texts: There's any tools that can migrate SQL Server 2005 to SQL Server 2017?


texts:
I noticed a performance issue with my SQL queries. The same query executed for the first time took ~7 seconds, consecutive executions took around 50 ms.

I tried the same query another day and once again, first one took ~7 seconds and next ones 50-60 ms.

I used EXPLAIN then:

First run:

main=> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM txes INNER JOIN accounts ON txes.account_id = accounts.id WHERE accounts.user_id = 135736;

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=8564.89..8564.90 rows=1 width=8) (actual time=7854.024..7854.024 rows=1 loops=1)
   Buffers: shared hit=3636 read=2052
   ->  Nested Loop  (cost=0.98..8562.64 rows=899 width=0) (actual time=3.901..7852.603 rows=9650 loops=1)
         Buffers: shared hit=3636 read=2052
         ->  Index Scan using index_user_accounts_on_user_id on user_accounts  (cost=0.42..6.03 rows=5 width=4) (actual time=0.016..0.087 rows=31 loops=1)
               Index Cond: (user_id = 135736)
               Buffers: shared hit=29
         ->  Index Only Scan using index_money_transactions_on_user_account_id_and_transaction_on on money_transactions  (cost=0.56..1694.89 rows=1643 width=4) (actual time=1.162..253.254 rows=311 loops=31)
               Index Cond: (user_account_id = user_accounts.id)
               Heap Fetches: 9650
               Buffers: shared hit=3607 read=2052
 Planning time: 0.217 ms
 Execution time: 7854.055 ms
(13 rows)

Next run:

main=> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM txes INNER JOIN accounts ON txes.account_id = accounts.id WHERE accounts.user_id = 135736;

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8564.89..8564.90 rows=1 width=8) (actual time=14.739..14.739 rows=1 loops=1)
   Buffers: shared hit=5688
   ->  Nested Loop  (cost=0.98..8562.64 rows=899 width=0) (actual time=0.068..14.146 rows=9650 loops=1)
         Buffers: shared hit=5688
         ->  Index Scan using index_accounts_on_user_id on accounts  (cost=0.42..6.03 rows=5 width=4) (actual time=0.037..0.077 rows=31 loops=1)
               Index Cond: (user_id = 135736)
               Buffers: shared hit=29
         ->  Index Only Scan using index_txes_on_account_id_and_tx_on on txes  (cost=0.56..1694.89 rows=1643 width=4) (actual time=0.010..0.424 rows=311 loops=31)
               Index Cond: (account_id = accounts.id)
               Heap Fetches: 9650
               Buffers: shared hit=5659
 Planning time: 1.060 ms
 Execution time: 14.805 ms
(13 rows)

So it seems that shared buffers do not contain all the data. And as far as I understand shared buffers represent RAM memory.

So my question is how long the data stays in shared buffers, until the machine is rebooted or is there some kind of validity/expiration period?

Total RAM on machine is 32GB. It is designed to run both web application and database. 12 GB are dedicated for DB.

shared_buffers = 3000MB
effective_cache_size = 9000MB


texts: DELETE and INSERT using OPENQUERY to Oracle require long delay between them

texts:
I'm doing a DELETE followed by an INSERT on an OPENQUERY to Oracle. I have tried a straight DELETE with a WHERE as well as filtering with a JOIN. INSERT is just an INSERT OPENQUERY/SELECT. I have also tried a filtered UPDATE followed by INSERT on new rows. Since I can't do a MERGE on OPENQUERY, I must do two statements back-to-back.
DELETE, UPDATE and the INSERT work as expected, but only if I run them 10 minutes apart. Individually, they complete in less than one second. When run back-to-back, using either sp_executesql or SSMS, whether running them individually or in one script, they take 10 minutes exactly.
Talked to the Oracle DBA, doesn't ring any bells for him.
The queries themselves, as much as I would like to share them to help you help me, I am not free to do so. Please take my word for the fact that they both work as expected in less than one second when run 10 minutes apart and take 10 minutes when run together.


texts: User-defined Policy Evaluates to True when Evaluated but False when Invoked by Test-SqlAvailabilityReplica

texts:
I created a PBM condition and policy in SSMS. The policy runs a T-SQL script using the ExecuteSql function against the Server facet to determine whether all databases that are expected to be a member of an availability group are in fact members of the group. The expected values are stored in a table. SQL below; it executes fine.

When I evaluate the policy (right-click | Evaluate) it shows success. The actual and expected values are zero. So far, so good.

The policy is configured to be in the Availability Replica warnings category so it can be evaluated by the High Availabiltiy dashboard (or the Test-SqlAvailabilityReplica cmdlet). When I run the High Availabilty dashboard it reports a policy execution running the policy but gives no further detail. See 2nd screen capture in image.

When I run the Test-SqlAvailabilityReplica cmdlet in Powershell it reports a value of False for the policy. All other policies report True (3rd screen capture). When I run the cmdlet with the -Verbose parameter I don't see the SQL for my policy being executed but I do see the SQL for the other policies. It appears that is returns False without being evaluated. I'm running as a user with sysadmin.

I'm stumped. Why is it returning False when it should be True? Any insight appreciated.

Condition:

ExecuteSql('numeric', 'SELECT
       count(*)
FROM myDB.dbo.AvailabilityGroups ag
    INNER JOIN myDB.dbo.AvailabilityGroupDatabases agd
        ON ag.AvailabilityGroupId = agd.AvailabilityGroupId
    LEFT JOIN
    (
        SELECT name,
               database_name
        FROM master.sys.availability_groups ag
            INNER JOIN master.sys.availability_databases_cluster adc
                ON ag.group_id = adc.group_id
    ) sag
        ON ag.AvailabilityGroupName = sag.name
           AND agd.DatabaseName = sag.database_name
WHERE sag.name IS NULL
      AND sag.database_name IS NULL')


texts:
Below is a sscce to motivate the case.

I keep a table of persons:

CREATE TABLE person (name VARCHAR);
ALTER TABLE person add PRIMARY KEY (name);
INSERT INTO person (name) VALUES ('Bob'), ('Mike');

... and the encounters these persons have had with the law:

CREATE TABLE infraction (person VARCHAR, crimetype VARCHAR);
ALTER TABLE infraction ADD CONSTRAINT infraction_2_person FOREIGN KEY (person) REFERENCES person(name);

INSERT INTO infraction (person, crimetype) VALUES('Bob', 'MISDEMEANOR'), ('Bob', 'MISDEMEANOR'), ('Bob', 'FELONY');

Where crimetype in the table above would normally be a FK into a master table of crime types (omitted here to keep things short).

So Bob has committed two misdemeanors and one felony whereas Mike has a clean record.
The above two tables capture all the information I need.

Now I create a view that produces an overall assessment of that person, depending on the crime types they've committed and how many of them:

CREATE VIEW person_assessment AS
SELECT name, CASE
           WHEN NOT EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name)                          THEN 'exemplary'
           WHEN     EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name) AND
                NOT EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY') THEN 'relatively law-abiding'
           WHEN     (SELECT COUNT(*) FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY')<=4 THEN 'felon'
           WHEN     (SELECT COUNT(*) FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY')>5 THEN 'career criminal'
           ELSE 'unhandled case'
          END AS citizen_type
FROM person a;

The above view produces:

name citizen_type
---- ------------
Bob  felon
Mike exemplary

My question are:

is the SQL code for the view above idiomatic or is there a better way to write it?
is there a way I can somehow constraint the citizen_type that the view produces so that I can make it reference a master table of citizen types with descriptions, etc. I somehow want to make it explicit that the values relatively law-abiding, felon and career criminal come from a controlled set of strings that other tables can cross-reference and I can assign some other properties on them (description for instance).


texts:
I am new to DBA job and in my environment index rebuild job is failing and when I checked reason for failure it is showing  :

  "The transaction log for database 'x' is full due to 'AVAILABILITY_REPLICA'.

We are using SQL Server 2014 with full recovery mode.


texts: Discover the original OS of a datafile from a oracle backup

texts:
I have just been awarded with the task of recovering a oracle database with some given datafiles. However, nobody knows from which OS this backup comes from.

I have an AIX box to make the recover. Is there any way that i can determine the OS from the original datafiles?


texts: Is it better to put filters on JOIN clauses instead of WHERE clauses

texts:
MySQL 5.5.8:

Question 1:
Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables before and after the where clause and possible index usage?

SELECT *
FROM a JOIN b ON a.id = b.id AND a.col2 BETWEEN 1 AND 5;

SELECT *
FROM a JOIN b ON a.id = b.id
WHERE a.col2 BETWEEN 1 AND 5;

My reason for asking is because if for instance the tables are massive but the where clause filters 99.9999% of the records out, I don't want to waste time joining massive tables if after the join 99.9999% of the records are not used.

Question 2:

Take the same situation, which statement (if any difference) requires less IOPS. (index present on b.id and one on a.col2)


texts: MSSQL - Limit Query to last 5 records of group by

texts:
I have a data table that is expected to grow at about 20,000 records a day

I'm wanting to sum the last five 'detector_record_status' for each 'installation_id''s 'detector_id'.

If the sum of the last 5 (detector_record_status) is >=3 then its deemed Active.

eg
installation_id
detector_id
Active = sum(detector_record_status) => 3

installation_id|  detector_id  |  Status
            1  |            8  |  Active

I do how to limit to 5 records for each 'detector_id' of the 'installation_id'
And to show 'Active' rather than the sum(detector_record_status)

I've created a sqlfiddle
http://sqlfiddle.com/#!18/3f65f/3/0


texts:
I'm using this query to restore a database from a C++ program

RAISERROR ('-- Performing full restore...', 0, 1) WITH NOWAIT
USE MASTER;
ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [{0}] FROM  DISK = N'{1}'
WITH  FILE = 1, REPLACE, KEEP_REPLICATION, NOUNLOAD, STATS = 5;
ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
RAISERROR ('-- Performing full restore: Done', 0, 1) WITH NOWAIT;

On my computer it works as intended, but when I try to restore the database on our customer's computer an error message pops out saying

  Timeout expired.  The timeout period elapsed prior to completion of
  the operation or the server is not responding. The backup or restore
  was aborted.

And this shows right after this info shows up

  Processed 1768 pages for database '[database]', file '[logic_name]'
  on file 1. Processed 4 pages for database '[database]', file
  '[logic_name]_log' on file 1.

I checked the NETWORK SERVICE user and it has access to the folder of the .bak file. I noticed that under the general Security options (not the ones just for the database I want to restore, the outer ones in the tree of the Microsoft SQL Server Management Studio) the NT AUTHORITY\NETWORK SERVICE user was missing, so I created it with the same options as the one I have on my computer right now, but this didn't change anything.

I set the LockTimeOut to 0 (it was -1), but again, nothing changed.

What can cause this timeout expiration? And how can I have different behaviour on different computers? I guess there is some option that I'm missing, but I really don't know what else to do.
Thanks!

P.S.: this is the query I use to back up the database

BEGIN
SET NOCOUNT ON;
BACKUP DATABASE [{0}] TO  DISK = N'{1}'
WITH NAME = N'program-Full Database Backup', STATS = 10;
END


texts: What is the foreign key between two children in the same hierarchy?

texts:
I'm trying to map an entity relationship design into a relational database. Under STAFF entity, I have 2 children are BUSINESS MANAGER and EMPLOYEE.

My teacher requests to add a 1-M relationship SUPERVISE between Business_MGR and EMPLOYEE (a BUSINESS-MGR supervises 0, 1, or more employees, an EMPLOYEE is always supervised by one BUSINESS-MGR).

What is the foreign key for the EMPLOYEE entity?


texts: (MYSQL) Select union of rows depending on row before?

texts:
I have a table in MySQL that looks like this:

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) NOT NULL,
  `data` VARCHAR(255),
  `otherid` int(11),
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

With some rows:

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
3 |  "y"  | NULL
4 |  "z"  | NULL

I want to select first the row where data = "stuff" and then all the following rows (which could be "infinite") where the next rows are found by id = lastrow.otherid. Like this:

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
4 |  "z"  | NULL

If there is no row with data = something, then no rows should be selected.

Any suggestions?


texts:
I tried to enlarge a database size of database instance on my SQL Server 2012.
I manually added a new .MDF file under database instance

After restart on shrink database dialog I can see available space

on shrink files dialog the available free space is added

but when I executed following SQL query the available space still small

select
    name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
   , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from
    dbo.sysfiles a

The database still see only two files ....

What I did wrong ?

[Edit] If just statistics shown wrong in query, it's OK for me. My question is the whole process to add space to the database made in the right way or I need to add something to finish the process? Sorry for editing later

Thanks for any help


texts: Merge replication - can't create snapshot – timeout

texts:
I have a SQL Server 2008 database, and I need a merge replication because I want to sync with mobile devices afterwards.

So I created a replication but when it comes to start the snapshot agent, the agent tries to start for about 20 minutes and then it shows the message:

The replication agent has not logged a progress message in 10
minutes. This might indicate an unresponsive agent or high system
activity. Verify that records are being replicated to the destination
and that connections to the Subscriber, Publisher, and Distributor are
still active.

There aren't any other error messages, neither in the snapshot-agent-status-window nor in the agent-log-window.

I don't have the administrator of the domain, but the local administrator and a domain user with admin-privileges. Both have all rights to database, are in the access-list of the replication.

The server agent runs on the local administrator-account and there are *3 Merge Replications   working on the server *

The job runs also under the local administrator.


texts: SQL Server 2014 Replication - The process could not connect to Subscriber 'xxx'

texts:
I have just set up very basic transactional replication on SQL Server 2014.

The Publisher and Distributor are one and the same.

I have configured the Publisher to push to the Subscriber, so any and all configuration was done on the Publisher.

I set up all of the user accounts exactly as detailed in this Microsoft Tutorial.

The Publisher and Subscriber are successfully set up, and SQL Server Agent is running on both servers.

Regardless of this, when I check the status on the Publisher, I repeatedly get this message:

  The process could not connect to Subscriber '(subscriber-box)'

I've checked online, including here, here, here and here.

I've tried the suggestions and can't debug it, though I am pretty sure it's logon related.


texts:
I would like to know if anybody can point me towards a tool/way for MySQL database tuning.

Actually the tuning of my.cnf is like below:

My database engine is MyISAM | Mysql 5.1.73

key_buffer = 500M
sort_buffer_size = 256M
read_buffer_size = 256M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
thread_cache_size = 16
thread_concurrency = 32
tmp_table_size = 500M #default 33M!
max_heap_table_size = 500M
skip-name-resolve
skip-host-cache
myisam_recover = BACKUP,FORCE
query_cache_size=256M

My computer is:

Intel(R) Xeon(R) CPU E5620 @ 2.40GHz, 16 cores
Linux 2.6.32-5
Memory - 23.58 GB total

I also have had look at slow query but what do you recognize or recommend?

I've made the select:

SELECT
    CONCAT(ROUND(KBS/POWER(1024,
    IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
    SUBSTR(' KMG',IF(PowerOf1024<0,0,
    IF(PowerOf1024>3,0,PowerOf1024))+1,1))
    recommended_key_buffer_size FROM
    (SELECT LEAST(POWER(2,32),KBS1) KBS
    FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
    table_schema NOT IN ('information_schema','mysql')) AA ) A,
    (SELECT 2 PowerOf1024) B;

3967M


texts: Large archive table with "unimportant" data, how to improve/redesign?

texts:
We have a SQL DB that collects analog and digital sensor data from several hundred devices. The archive table is quite simple, just a (datetime) timestamp, (int) stationId, (int) datatapointId and (double) value (plus 3 bool flags).

The DB collects about 12 million entries per month. This table is supposed to be always growing, no rollbacks, updates or deletions are neccessary.

The main purpose of this table is to be used to show archive values in our web visualization ordered by timestamp and filtered by this or that id, therefore I created the relevant indexes for the fields I need. It works, but the performance is quite poor (about 5 seconds when filtering by a start/end time, stationId and datapointId).

What would be a better way to store and query this kind of data? Is SQL Server Standard 2016 perhaps not the right DBMS for such an archive? The original decision to use SQL Server dates back several years when the amount of data was way smaller than today. Is there some magic in SQL Server that I can apply to trade reliability for performance?


texts:
I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with.

So I'm creating a webapp that is essentially an online art gallery store. The database will contain an inventory of items and possibly a customer list (this is what I'm not sure about). I want to allow for various levels of manipulation to the inventory list.

I want user 1 let's call that "sales" to add new items, edit and delete. I also want sales to to the same for a customer list.
I want user 2 "artists" to be able to add new, edit what they added and delete what they've added to the inventory list, along with their own info.
I want user 3 "customer" to be able edit one field in the inventory list (quantity required), along with their own info.

So would this come under rules and privileges?? I want an admin account to oversee the db and all connections. Would I need to create separate rules for all others? Do i add type 1 "sales" as admin with certain rights? Can I contain new users type 2&3 in a customer list and have that list manipulate the inventory list with rules? E.g. So if customer list is type 2, user is an "artist" and can add new stock, but if they are type 3, they are "customer" and can only edit quantity.

Again I am a noob. I hope what I'm asking makes sense. Thanks for your help kind folks.


texts: Order of Execution of Set clause in Update Query (Postgres)

texts:
I recently came across this strange behaviour in Postgres. I had a table like the following one:

sasdb=# \d emp_manager_rel
           Table "db3004db.emp_manager_rel"
   Column   |  Type  | Collation | Nullable | Default
------------+--------+-----------+----------+---------
 emp_id     | bigint |           |          |
 manager_id | bigint |           |          |

select * from emp_manager_rel ;
 emp_id | manager_id
--------+------------
      1 |        123

I executed the following update statement :

UPDATE 1:

update emp_manager_rel set manager_id = manager_id+emp_id , emp_id=emp_id*4;

which update the table like follow:

 emp_id | manager_id
--------+------------
      4 |        124

UPDATE 2:
I executed the following query (on the original table, not on the updated)

update emp_manager_rel set  emp_id=emp_id*4 , manager_id = manager_id+emp_id ;

it updates the table like follows:

 emp_id | manager_id
--------+------------
      4 |        124

I am expecting the value of manager_id on UPDATE 2 to be 127 (because emp_id has been changed to 4 by empid * 4). But, both UPDATES produce the same result. I wonder what will be the order of execution of set clause in ANSI standard.


texts:
I have A Problem In
Alwayson Configuration - SQL 2012 SP3
                       -Windows server 2012
                      "FCI+AG" Configuration

Xnode-1  Xnode-2 'FCI' Cluser Name XDB +
Xnode3 "Read-only Replica NonFailover Asynccommit "  No listener
All connect to XDB Cluster

  Cluster Policy Role  HealthCheckTimeout 40000 LeaseTimeout       20000
  FailureConditionLevel 3

Suddenly Primary Replica Xdb Database Not Accessible
Unable to access database 'PROD_DB' because its replica role is RESOLVING which does not allow connections. Try the operation again later.

and

message    The availability group database "prod_DB" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

and Secondary X-node3 Read Only Auot Check  database Running For 5-7 Min
after Finishing Check Database

Pls. Need to know WHY PRIMARY REPLICA Not Accessible !and Please How TO prevent that occur again ?

Thanks

  Another Log error
  AlwaysOn: The local replica of availability group 'X_AG' is going
  offline because the corresponding resource in the Windows Server
  Failover Clustering (WSFC) cluster is no longer online. This is an
  informational message only. No user action is required.

  The state of the local availability replica in availability group
  'X_AG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'. The
  replica state changed because of either a startup, a failover, a
  communication issue, or a cluster error. For more information, see the
  availability group dashboard, SQL Server error log, Windows Server
  Failover Cluster management console or Windows Server Failover Cluster
  log.


texts:
I upgraded postgresql from 8.4.2 to 12.3. I created a dump of the db from the old version, and restored into the new version.
The port for the new version is 5532. The old one uses 5432. After I restore the dump, I check that the service is running for the new version, and the server is listening on port 5532 for all interfaces.
I even copy the pg_hba.conf entries from the old version to the new version. But instead of "password" I change all the entries to "md5" because this is a newer version. After changes, I reload pg_hba.conf.
However, when we test access to the new db now, it does not work. It fails with error :

Your login attempt was not successful, try again. Reason : Could not
get JDBC Connection; nested exception is
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for
host "192.168.130.209", user "app_support", database "bosupportdb"

Postgres ports :
[root@reportv2 data]# ss -tulpn | grep postgres
tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postgres",pid=3454,fd=3))
tcp    LISTEN     0      128       *:5532                  *:*                   users:(("postgres",pid=4763,fd=3))
tcp    LISTEN     0      128      :::5432                 :::*                   users:(("postgres",pid=3454,fd=4))
tcp    LISTEN     0      128      :::5532                 :::*                   users:(("postgres",pid=4763,fd=4))
[root@reportv2 data]#

These are my pg_hba.conf entries :
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host   all              all             127.0.0.1/32            md5
host    all             all             192.168.130.0/24        md5
#host   all             all             192.168.8.0/24          md5
host    all             all             192.168.90.8/32         md5
host    all             all             192.168.8.175/32        md5
host    all             all             192.168.112.0/23        md5

Please help me resolve this. I am clueless.


texts: RSA Certificate Error Starting MySQL 5.7 on CentOS 6

texts:
We've been getting some trouble installing MySQL 5.7 on a CentOS 6 box. Information about the environment is available below:

CentOS Version:

[root@ip-10-0-0-73 ~]# cat /etc/centos-release
CentOS release 6.10 (Final)

MySQL Version:

[root@ip-10-0-0-73 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Installation of the product went smoothly, using the RPM Bundle that is avaliable on MySQL's website. The thing is when we try to start the service, it fails with the following error on the log:

2020-05-07T23:30:15.740642Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-05-07T23:30:15.843684Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-05-07T23:30:15.908471Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b4c0fbe7-90ba-11ea-bb12-0274ede54730.
2020-05-07T23:30:15.911056Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-05-07T23:30:15.914313Z 0 [ERROR] Could not generate RSA private key required for X509 certificate.
2020-05-07T23:30:15.914355Z 0 [ERROR] Aborting

After some research, we were able to find the mysql_ssl_rsa_setup command which generates the certificates on the MySQL data folder, but after starting the server again, since it is the first time it initializes, it validates the emptyness of the data folder and hangs on the initialization phase:

Initializing MySQL database:  2020-05-08T13:31:31.308631Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2020-05-08T13:31:31.308718Z 0 [ERROR] Aborting

This basically means we are stuck on a loop and can't start the service. OpenSSL is properly installed on the latest version provided by CentOS 6:

[root@ip-10-0-0-73 ~]# openssl
OpenSSL> version
OpenSSL 1.0.1e-fips 11 Feb 2013
OpenSSL>

The MySQL documentation is kind of poor on this error and we even tried to use the option to skip the ssl certificates creation and later create them, but with no success.

Has anyone seen this error on the wild and can share some insight?

Thanks!


texts: How to count number of occurrences for all different values in database column and obtain 0 if are there empty result?

texts:
I'am newby with postgresSQL.
I have a table with this structured:

city          | type     | quantity | sellout   | sellout_update
"ACIREALE"    | "Man"    |  "3"     |"1100.0"   |"366.6666666666666667"
"ACIREALE"    | "Unisex" |  "3"     | "1405.0"  |"468.3333333333333333"
"ACIREALE"    | "Woman"  |  "9"     | "2480.0"  |"275.5555555555555556"
"ALESSANDRIA" | "Kid"    |  "1"     |"100.0"    |"100.0000000000000000"
"ALESSANDRIA" | "Man"    |  "13"    | "3500.0"  |"269.2307692307692308"
"ALESSANDRIA" | "Unisex" |  "6"     | "1485.0"  |"247.5000000000000000"
"ALESSANDRIA" | "Woman"  |  "29"    | "8085.0"  | "278.7931034482758621"

I obtain this result from this query:

select lg.city,
        so.type,
        SUM(so.sell_out_quantity)  as quantity,
        SUM(so.estimated_sell_out_value_based_on_srp_euro5) as sellout,
        (SUM(so.estimated_sell_out_value_based_on_srp_euro5)/SUM(so.sell_out_quantity )) as sellout_update
FROM doors As lg INNER JOIN sellout as so ON lg.code = so.code
GROUP BY lg.city,
         so.type

now, i want obtain this result:

city        | N man | N Unisex  | N Woman | N Kid | ...   | sellout
"ACIREALE"    | 3     |  "3"      |   9     | **0**     | ...
"ALESSANDRIA" | 1     |  13       |   6     | 29    | ....

For each row I want count the number of occurrences for all different values and transpose them in a specific column. I think that i need to different subquery and join them. For Example:

...
INNER JOIN (select lg.city,
                 COALESCE(COUNT(so.type),0) as c_kid
                FROM doors As lg INNER JOIN sellout as so ON lg.code =
                so.code
                WHERE so.type = 'Kid'
                GROUP BY lg.city
                         ) As count_kid ON lg.city = count_kid.city
...

It work. (I don't know if exist the a better way to achieve this result)
As it can be see, i use COALESCE function for obtain a 0 if a value doesn't exist but after several attempts I always get an empty line if it doesn't exist. Where am I doing wrong ?


texts: Pass an array or record to a function in PostgreSQL?

texts: I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.


texts:
I'm working on Oracle 11g database and I need to list all index-organized tables.
Is there a dba's view to query for that ?

Thanks


texts: SSRS 2014 Exec Log gives the Service Account as UserName instead of Actual User name

texts:
I am using the below query for accessing the Users who are using the Application Reports.

SSRS 2014 gives the Service Account(IIS Account used for Application) as User Name instead of Actual User name.

USE ReportServer;
GO
SELECT el2.username,
el2.InstanceName,
el2.ReportPath,
el2.TimeStart,
el2.TimeEnd,
el2.[Status],
isnull(el2.Parameters, 'N/A') as Parameters
FROM ExecutionLog2 el2
GO

Can you please let me know why I am not able to see exact username who accessed the Report instead why I am seeing the Service Account in the place of User Name exactly.

There is only one DISTINCT RequestType which is Interactive. Where as when we read IIS logs using Log parser it does contain the User Name individually

Thanks


texts: Restoring only differences with SQL differential backups?

texts:
I find something similar here but it was asked almost 10 years ago and by that time there was no option. I wonder if there is any now.
I am doing some actions on application and getting back ups before and after but when I want to restore the db to roll back to previous version, it cost a lot of time and distraction. I wonder if there is any quick way to get differential back ups like myDB_v1, myDB_v2, v3 etc.. and restore between them quickly.
Thanks in advance.


texts: How can I use value of the id column as default value of another non-nullable column when using postgresql-rails?

texts:
Here's one example where such a requirement comes up:

Suppose we have a table 'sample' in our database which has a column 'col1' and an auto-incremental 'id' column. We want the default value of 'col1' to be taken as the value of the 'id' column. How do I do that?

I am using rails to write the migration script which will create the table in my postgres databse.


texts: Performance of a linked server between two instances of SQL Server on the same virtual machine

texts:
I would like to set up log shipping between SQL Server 2014 (main) and SQL Server 2016 (secondary). The thing is I want to set the secondary DB in STANDBY. Unfortunalely this setup is not supported by MS, as the secondary is a higher version (2016) than primary (2014). To overcome that issue I have a plan to install  a second instance of SQL Server on secondary in version 2014. Then in order to be able to access it I will have to create a linked server between those two instances  on the same machine.

And now the main question. Provided that both instances reside on the same VM, should I expect a significant performance drop during queries between them (large joins)? Is there a way to overcome this?

edit:

Thank you for your feedback. A bit of clarification from my side:

Secondary (2016) will be used solely for reporting purposes. There is no need to support a fallback scenario. The database is quite large so daily restores to secondary are becoming a bit cumbersome.

Having the secondary in a STANDBY state is a must, as I want the database to be accessible for reporting. As far as I'm concerned it is not possible.

https://www.brentozar.com/archive/2015/01/reporting-log-shipping-secondary-standby-mode/


texts: Views for XML with attributes using XMlTable

texts:
I created an XMLType table

CREATE TABLE PERSON OF XMLType
  XMLTYPE store AS OBJECT RELATIONAL
  XMLSCHEMA "PERSON.XSD" ELEMENT "root";

The XML is -

<root>
<Person empid="1" empname="Joe" empproj="IT" emporg="Consulting" />
</root>

I created view for the XMLType table. But I could not see the values inserted in table in the view. I want to have the XML attributes (empid, empname, empproj, emporg) as columns of VIEW person_master_view for table PERSON.

CREATE OR REPLACE VIEW
  person_master_view(empid, empname, empproj, emporg)
AS SELECT xt.*
     FROM PERSON,
                XMLTable ('/root/Person' passing PERSON.object_value
                                 columns
                                     empid        number(10)   path '/Person',
                                     empname      varchar2(30) path '/Person',
                                     empproj      varchar2(30) path '/Person',
                                     emporg       varchar2(30) path '/Person'
                             ) as xt


texts: Implementing a simple ERD in MySQL: is the implementation below 100% compliant with the ERD?

texts:
ERD

Consider the following ERD:

Tables

From it, I derived the following tables:

Company
------------------
c_id        c_name
------------------

CompanyEmail
------------------
c_id        e_id
------------------

Email
------------------
e_id        e_addr
------------------

Note: the use of the relationship table (CompanyEmail) is not arbitrary.

Implementation

From those tables, I made the following implementation (tested in MySQL Workbench 6.1):

http://pastebin.com/3A0H21kw (please bear with my lack of skills :)

I guess the most important part of it (besides learning the language) was deciding which attribute is FK and the use of CASCADE. Is it [CASCADE] the implementation of parcial/strong participation? and/or weak/strong entity?

Question

Does my implementation accurately complies with the ERD? If not, what would be a better solution?


texts: Debug stored procedure that times out from year to year

texts:
We have a stored procedure that joins a couple of tables and returns json. We've added indexes and it returned the expected results without any issues for months. Btw, here's a snapshop of the live query statistics for an execution:

Today, and after being in production for more than a couple of years, it suddenly stopped working. Whenever it gets executed, it times out and doesn't return anything. Restarting the instance solved the issue and it's fast again. When it was timing out (ie, before we restarted the instance), I've tried running the query to see if there was any index suggestions, but it kept timing out.My colleagues say that CPU and memory weren't issues (CPU was pretty low and more than 50% of memory was available).
As expected, logs (sql and machine) show nothing out of the ordinary.
Interestigly, the reamining queries work as expected.
Any clues on how to debug this issue? I'd really like to understand what happened, but not sure on what to check...


texts: Optimize finding events with overbooked attendees

texts:
I have the following strcture to store events and their attendees:

CREATE TABLE `plan_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_datetime` datetime DEFAULT NULL,
  `end_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `start_datetime_idx` (`start_datetime`),
  KEY `end_datetime_idx` (`end_datetime`),
  KEY `start_end_datetime_idx` (`start_datetime`,`end_datetime`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `plan_event_attendee` (
  `event_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`event_id`,`user_id`),
  KEY `IDX_E8625CBD71F7E88B` (`event_id`),
  KEY `IDX_E8625CBDA76ED395` (`user_id`),
  CONSTRAINT `FK_E8625CBD71F7E88B` FOREIGN KEY (`event_id`) REFERENCES `plan_event` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_E8625CBDA76ED395` FOREIGN KEY (`user_id`) REFERENCES `plan_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I would like to find all events where someone is attending in another event at the same time (no need to know who exactly, just which events has at least one of such attendee). This is what I have so far:

    SELECT e1.id, e2.id
      FROM plan_event e1
INNER JOIN plan_event e2
        ON e2.start_datetime < e1.end_datetime
       AND e2.end_datetime > e1.start_datetime
       AND e2.id <> e1.id
INNER JOIN plan_event_attendee a1
        ON a1.event_id = e1.id
INNER JOIN plan_event_attendee a2
        ON a2.event_id = e2.id
     WHERE a1.user_id = a2.user_id
       AND e1.start_datetime > '2015-06-01'
       AND e1.end_datetime < '2016-06-01'

Unfortunately with the given dataset the query is not performant enough, likely caused by the exponential nature of joining tables to themselves. EXPLAIN gives me the following output:

+----+-------------+-------+--------+--------------------------------------------------------------------+------------------------+---------+------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys                                                      | key                    | key_len | ref              | rows  | Extra                    |
+----+-------------+-------+--------+--------------------------------------------------------------------+------------------------+---------+------------------+-------+--------------------------+
|  1 | SIMPLE      | e1    | range  | PRIMARY,start_datetime_idx,end_datetime_idx,start_end_datetime_idx | start_end_datetime_idx | 9       | NULL             | 26640 | Using where; Using index |
|  1 | SIMPLE      | a1    | ref    | PRIMARY,IDX_E8625CBD71F7E88B,IDX_E8625CBDA76ED395                  | PRIMARY                | 4       | aire.e1.id       |     4 | Using index              |
|  1 | SIMPLE      | a2    | ref    | PRIMARY,IDX_E8625CBD71F7E88B,IDX_E8625CBDA76ED395                  | IDX_E8625CBDA76ED395   | 4       | aire.a1.user_id  |   475 | Using where; Using index |
|  1 | SIMPLE      | e2    | eq_ref | PRIMARY,start_datetime_idx,end_datetime_idx,start_end_datetime_idx | PRIMARY                | 4       | aire.a2.event_id |     1 | Using where              |
+----+-------------+-------+--------+--------------------------------------------------------------------+------------------------+---------+------------------+-------+--------------------------+

What other performance optimizations could I attempt or this the best I can get?


texts: Does SQL Server 2008R2 Express actually enforce the memory and CPU limit?

texts:
I've been testing SQL Server 2008R2 Express to see if it is limited to 1GB of RAM and 1 CPU.

It certainly seems to be using more than one CPU, as I see spikes on all 4 CPUs in Task Manager when I run a big query. I've selected * from all the tables in the database I have, and it got it to use 1.4GB RAM when looking at the Total Server Memory in perfmon (I gather this is the correct stat to look at). This is showing as 1.5GB Mem Usage on Task Manager (which I gather isn't reliable).

However, it does not seem to want to go any further than 1,441,792KB. This seems odd, so I am not convinced either way.

Anyone have any knowledge of why this might happen and whether the limits are actually enforced?


texts:
I am trying to add PostGIS functionality to a database that I created in PostgreSQL using the following command: psql -d <db_name> -f /location/to/this/file/postgis.sql

However, I receive a bunch of errors relating to 'geometry does not exist', but on line 1 of these error messages, it says ERROR: could not access file “$libdir/postgis-1.5”.

I Google'd and checked StackOverflow, and it seems like this error is related to dependencies or scripts not being installed. I installed PostGIS using yum install postgis84. I did not see any dependencies except for proj. Based on these instructions, it seems like I will need Proj4, GEOS, LibXML2, GDAL, JSON-C, etc.

Is there a repository that I can connect to that will let me install PostGIS with all the crucial dependencies rather than installing them one by one? I am running a Red Hat Enterprise Linux 6.5 server on Amazon EC2.

I am new to databases, so please excuse me if I used any incorrect lingo.


texts: TINYINT vs Nullable BIT performance in MS SQL Server

texts:
We need three modes to display the status of users in the database!
We can do it in two ways

Nullable BIT:

Null: Not checked

0: Not accepted

1: accepted

TINYINT

0: Not checked

1: accepted

2: Not accepted

Which data type is better when we need to represent three values?


texts: Split table vs. same values in multiple rows

texts:
I have a table products:

#products
ID | category | type | criteria1 | criteria2

, with category and type being foreign keys of other tables.

Should I split this table up into category1_type1_products, category1_type2_products and so on? It seems to me I should, because right now in my table, there are some thousands of entries with the same ID and category value. Loads of redundand information.

Also, a query could perform faster if mysql didn't have to search for all rows with the specific category and type first. (right?)

Is this recommended structuring behaviour at all? If not, what if my table has got 5 million tuples?


texts: Inserting records into Mongodb using Pymongo is using up my RAM incrementally and is eventually exhausting all memory

texts:
I am writing Python code that parses through a huge XML document of size 17GB(Wikipedia collection) and inserts each page title along with the links in that page (storing as a list) present on the page in a MongoDB collection. I am inserting using the command links.insert_one(data) wheredata is a dictionary consisting of the following

data = {
          'document': self.title, # a string (basically title of the Wikipedia page)
          'links': self.links # list of strings (all hyperlinked words in a page)
       }

<db_name>.insert_one(data)

I am repeating this process for every page (around 19 million pages)

When I monitor my RAM usage(using htop in Ubuntu), I see that the amount of RAM used by MongoDB keeps increasing and continues till all my memory is used eventually crashing the system.

I have also made the following observation when I comment out the insertion statement and execute the code, it works fine. So, I presume that the problem is with my MongoDB insertion process and not with the actual python logic.

Any help would be really appreciated.


texts: MariaDB: Syntax Error on Create Trigger (with Delimeter) (but works in MySQL)

texts:
After one hour staring into my statement... I don't know how to proceed with the following statement:
 delimiter //
CREATE TRIGGER `insert_transaction` BEFORE INSERT ON `transactions`
 FOR EACH ROW BEGIN
    set @tmpTransactionNo = nextval(0, CAST(SUBSTR(new.transactionDate, 1,4 ) as signed));
    set new.tmpTransactionNo =
            (SELECT
    CONCAT(SUBSTR(new.transactionDate,1,4), '-', LPAD(@tmpTransactionNo,3,'0') )
              );
end //

Nextval is a function that is existing on the database. Executing the function yields a correct result.
Executing the statement on MariaDB throws an error. On a MySQL DB it works... The error message says:

Error Code: 1064. You have an error in your SQL syntax; check the
manual that corresponds to your MariaDB server version for the right
syntax to use near '0, CAST(SUBSTR(new.Buchungstag, 1,4 ) as signed));
set new.Buchungsnummer...' at line 3

Why does it work on MySQL (5.7.32) but no on MariaDB (10.3.25)?


texts: MySQL count rows from other table AND get most recent title

texts:
I have a situation which can be boiled down to the following:

TABLE students
COLUMNS id, name, email

TABLE attendances
COLUMNS id, student_id, event_id

TABLE events
COLUMNS id, name, date_of_event

I want to be able to see all the students, and how many events they've attended, so I have:
select name, email, count(attendances.id) as number_of_shows_attended from students
left join attendances
   on student_id = students.id
group by name, email

RESULT

Jane Doe | jane@uni.edu | 0
John Doe | john@uni.edu | 10
Will Doe | will@uni.edu | 2

Perfect. Except I also wish to have a final column which lists the most recent event attended (which can be based on the events.date_of_event date column).
I have tried simply adding the missing column, like so:
select name, email, count(attendances.id) as number_of_shows_attended,
   event.name as most_recent_event_attended from students
left join attendances
   on student_id = students.id
inner join events
   on attendances.event_id on events.id
group by name, email

It gives me  an event name as expected, but how can I ensure it is the most recent one? I tried adding order by events.date_of_event desc, but of course it's sorting the results of the query.


texts: Is there any way to reduce pg_largeobject size?

texts:
The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject.

I could remove loid which are not required with a plain delete query or by doing lo_unlink and running vacuum on it. But I would like to know if there is any way to compress the data to reduce the table size without removing any rows in the pg_largeobject table?

(Using PostgreSQL 9.1.)

Update:

As per https://stackoverflow.com/questions/18636850/does-pg-database-size-include-blobs-in-pg-largeobject , on every insert in pg_largeobject will be compressed  (Answer by Craig). Is my understanding is correct? or do I need to enable any settings to achieve the compression?


texts:
We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table.

So, what are the best options to store all this data in our database and afterward access to this data (search in the database) ?

No need to say that it must be fast.

Thanks.


texts: MySQL schema design - how to decide between an `enum` vs. a table join

texts:
In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as:

Stackoverflow post on efficiency of enum vs a join
enum is data, not code
Blog: Reason why MySQL's enum data type is evil
Right time to use an enum

Or, is there a choice to be made specific to the use case in hand? If there is, what decision framework should I use, i.e. when to choose one of the two options?

For instance:

Currency: It's better to use a table join, since currency codes to be supported could change with time (since modification has been emphasized in many posts as anti-enum, but then what could not change with time?).
PaymentStatus: It's better to use enum type with values like, SUCCESS, FAILED, CANCELLED.
TierPlans: It's better to use a table join, as it would give the flexibility to add columns like monthlyPricing, annualPricing, discountRate, etc. in the future. (I am currently using enum instead, with values like FREE, PRO, MASTER; hadn't thought of pricing yet -_- ).

  I am finding that if I stress enough, I could find a table join being a better choice always, mutation safe route. Is increasing number of tables or table joins a symptom of bad design? How should I approach
  design in general, and design in such use cases specifically?

Note: I had asked this question on SO here. I am posting it here since I see that there is a closing request there, and I desperately need an answer to this confusing roadblock of mine. Understandably this might not be a good question to ask here too. I'd rather edit, if you may guide!


texts: Does it make sense to run a cluster of database servers in docker containers?

texts:
I'm currently looking into setting up a database cluster (MySQL Galera or Percona) but I have only 2 machines.

However, according to many sources I've read (This article from the Mariadb KB), the minimal node size in the cluster should be 3, to avoid split brain conditions.

  In order to avoid a split-brain condition, the minimum recommended number of nodes in a cluster is 3. Blocking state transfer is yet another reason to require a minimum of 3 nodes in order to enjoy service availability in case one of the members fails and needs to be restarted. While two of the members will be engaged in state transfer, the remaining member(s) will be able to keep on serving client requests.

So I'm wondering if I can run maybe 3 (or more) docker containers, each running a database server, on both machines. If one of the machine fails, there are still 3 nodes in the cluster, so no split brain problems.

It feels like cheating, but I cannot think of a reason not to do it.

Does this setup make sense ?


texts: ANSI_NULLS and QUOTED_IDENTIFIERS mismatch

texts: When I export the entire list of stored procedures/functions, I noticed there is a mismatch between ANSI_NULLS/QUOTED_IDENTIFIERS settings.  Some have them on, others off.  I'm not explicitly setting this for the stored procedures or functions, so how does SQL determine whether to define on or off for these settings by default?


texts:
Here's the deal, I'm in charge of creating a website that will host small web apps. This apps will be photo contests, creatives sentences and other similar gibberish. The deal is, since apps we'll be constantly changing and some new ones will come up what's the best way to save the config specs for each one? Should I create new tables and fields as I need? Or should I serialize this data and put it on one field?

In this case instead of having a table apps, I was thinking having a table for each app, this way I could have a more modular way of dealing with apps, making changes more easy, or more painful (in case of the changes apply to all the apps)

Or if I serialize everything, then I would only have one table and I won't have the need to make changes to the table, but this option seems a bit lame to me.


texts: Is DBMS_CRYPTO.RANDOMNUMBER a cryptographically secure pseudo-random number generator?

texts:
Oracle documentation clearly states that DBMS_CRYPTO.RANDOMBYTES is a cryptographically secure pseudo-random sequence of bytes.
But how about DBMS_CRYPTO.RANDOMNUMBER? (or DBMS_CRYPTO.RANDOMINTEGER)  Are they cryptographically secure also?  If so, are there any evidences of it? (documentation, source code, ...)
My guess is that DBMS_CRYPTO.RANDOMNUMBER internal implementation is probably based on DBMS_CRYPTO.RANDOMBYTES, but I was not able to find this information in the docs.  Even if this is the case, it is not clear, for example, if DBMS_CRYPTO.RANDOMNUMBER generation method is biased or not (check this article, if you don't have an idea of what I am talking about).


texts: I can't figure out how to call a function with a trigger, keeps saying function doesn't exist

texts:
CREATE OR REPLACE FUNCTION calculate(nr char) RETURNS char AS $$
    BEGIN

      RETURN (SELECT COUNT(*) FROM patient_notitie WHERE pn_patient_nr = nr);

    END;
$$ LANGUAGE plpgsql;

This worked:

SELECT * FROM calculate('100001');

This is where it went wrong, when we try to create the trigger to call the function:

CREATE TRIGGER trigger_calculate AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate(pn_patient_nr);

It says the function doesn't exist:

  ERROR: function calculate() does not exist SQL state: 42883


texts: How to reset session context/variables in JDBC pooled connections - MySQL / Oracle

texts:
In my application I have multithreads that needs to access database and I am using apache.tomcat.jdbc.pool.DataSource as a JDBC connection pool.
In some cases users execute stored procedures that might affect the database session context/variables before executing another query to retrieve some data.
Is there a way to enforce closing the connection instead of just returning it to pool. Or how can we clear/reset the connection session context/variables for MySQL or Oracle using a stored procedure.


texts: SQL Server 2008 - Two FULL backups in one day

texts:
There is a database backed up by NetBackup once a day (Full backup 6PM to tape + 3/4x log backup per day). Database is in FULL recovery mode.
Vendor of application has remote access and he scheduled his own FULL backup to the file system (at midnight, probably directly in application).

Would be there a problem with POINT IN TIME recovery? Does his backup broke our chain?

In this picture, yellow are vendor's FULL backups to filesystem. Thank you guys


texts: Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this?

texts:
We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a short period of time (a few hours). We are trying to investigate the cause.

Normally our binary logs are 1 GB in file size but during the period that we were experiencing these issues, the log files jumped to 8.5 GB.

When I run mysqlbinlog --short-form BINARYLOG.0000 on one of the 8.5 GB binary log it only returns 196 KB of queries and data. When I run mysqlbinlog --short-form on a normal binary log (1 GB) it returns around 8,500 KB worth of queries and database activity. That doesn't make sense because it has 7 GB more of data yet returns less than a 1 GB binary log file.

I see lots of these statements with very sequential timestamps, but I'm not sure if that's related to the problem because they're in both the normal period as well as when we experienced these issues.

SET TIMESTAMP=1391452372/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;

How can I determine what caused those binary logs to balloon in size which also caused high writes, so much so it took the server offline at points, almost like a DDoS attack would?

How could mysqlbinlog return so much less data, even though the binary log file itself had 7 GB more? What can I do to identify the difference between a normal period where the binary logs are 1 GB to the period we had issues with the 8 GB binary log? Thank you for any help you can provide.

Bill


texts: ORA-01502: index or partition of such index is in usable state problem

texts:
I have a table in my Oracle database, where

select pkcol, count(*) from myTable group by pkcol having count(*) > 1;

yields

  PKCOL   COUNT(*)
------- ----------
      1          2
      2          2

Trying to remove the duplicate rows

delete myTable where pkcol = 1;

Yields:

  ORA-01502: index 'MYTABLE.PK_MT' or partition of such index is in
  usable state.

I'm using Oracle.DataAccess.Client.OracleBulkCopy to fill the table.

As far as I understand documentation from Oracle PRIMARY KEY constraints had to be checked.

Obviously they are not checked, as I found by doing the same bulkcopy two times in succession which ended in duplicates in all row.

Now I'm only using it after deleting all rows and I'm using a table with a similar primary key as source. As result I expect no problems.

But embedded deep inside my MS Build scripts, I end up with just 2 duplicates out of 2210 rows.

I guess that ignoring the primary key in the first place is a clear bug. No Bulkcopy should be allowed to ignore primary key constraints.

Edit:

Meanwhile I found, that the 2 conflicting rows where normally inserted by some script before bulkcopy was called. The problem reduces to my known problem, that bulkcopy doesn't check primary keys here.


texts:
When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards.

I want to know whether the newer versions of Oracle still has Auto-Commit OFF by default? I also want to know how to put OFF Auto-Commit in SQL Server 2005.


texts: SQL Server Agent on Linux Failover Cluster

texts:
We have set up FCI on Linux-SQL server, Everything is working except SQL server agent.
Agent is Enable and running but Job are not executing.
And Agent also getting stopped very often. Below are some error cited for reference.
What Could be the issue here ? please response if anything is missing on building the environment.
Errors:
Date        1/27/2021 3:09:50 PM
Log     SQL Server Agent (Archive #1 - 1/27/2021 3:09:00 PM)
Message
[382] Logon to server '(local),6666' failed (DisableAgentXPs)
Date        1/27/2021 3:09:45 PM
Log     SQL Server Agent (Archive #1 - 1/27/2021 3:09:00 PM)
Message
[150] SQL Server does not accept the connection (error: 258). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.


texts:
I'm trying to develop a django interface for an existing postgresql db, the db makes use of various schemas, looking at the literature the following example should work, but it only returns the schema defined in the default database when I run python manaage.py inspectdb. Also when this works, how do I define which schema to use when defining the django model?

DATABASES = {

'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS' : {
            'options': '-c search_path=public'
        },
    'NAME': 'gygaia',
    'USER':'postgres',
    'PASSWORD':'abc',
    'HOST':'localhost',
    'PORT':'5432',
},

'samples': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS' : {
            'options': '-c search_path=samples'
        },
    'NAME': 'gygaia',
    'USER':'postgres',
    'PASSWORD':'abc',
    'HOST':'localhost',
    'PORT':'5432',
},
    'excavation': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'OPTIONS' : {
                'options': '-c search_path=excavation'
            },
        'NAME': 'gygaia',
        'USER':'postgres',
        'PASSWORD':'abc',
        'HOST':'localhost',
        'PORT':'5432',
    },
}


texts: Error trying to update stock with trigger

texts:
I have bascially 2 tables

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    stock INT(4) UNSIGNED NOT NULL
);

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product)
);

And a trigger

CREATE TRIGGER updateStock
AFTER INSERT
ON PRODUCT_SALES
FOR EACH ROW
    UPDATE PRODUCTS
    SET PRODUCTS.stock = PRODUCTS.stock - PRODUCT_SALES.quantity
    WHERE PRODUCTS.id_product = PRODUCT_SALES.product_id

I want to update the stock of X product when someone makes an instert in product_sales table, but i get the following error when inserting a row in my product_sales table

  Unknown column 'PRODUCT_SALES.product_id' in 'where clause'

I'm useing MariaDB Ver 15.1 Distrib 10.2.12-MariaDB, for Win64 (AMD64).

I've made a DBFiddle.

Thanks in advance, best regards.


texts:
Assume that I have a table Foo with columns A, B, C, D, and E. All are integers. Let's assume a million records, and that we're on SQL Server 2017.

The following index exists:

create Nonclustered index IX_Foo on Foo ([A], [B]) include ([C], [D], [E])

If I run the following query:

Select A, B, C, D, E from Foo where A = 1 and B = 2

Will this query trigger a Key Lookup in Foo because A and B are not included in the index's Include listing, or will SQL Server realize that A and B are located in the key itself and avoid a Key Lookup?


texts:
Here's what I'm working with right now.  I have a host OS which includes a SQL Server 2008 Enterprise instance.  I have a virtual machine running on this host which has a SQL Server 2008 R2 Express instance.  In the virtual machine, I can connect to the host instance in SSMS.  But in the host OS I cannot reach the virtual machine's Express instance.

What could be causing this?  The host can ping the virtual machine, so I don't think it's a network issue.  And in the SSCM on the virtual machine I have set the appropriate network protocols enabled.

Any other things I need to look out for?


texts:
I'm doing a software development course and I have to normalise my tables as part of it. Can anyone double check that my data is normalised correctly?

The database is for a gym. It has these tables currently:

Member - Storing member details

Membership - Storing memberships, these are attached to each Member

MembershipType - Storing the type of membership so that I don't
                 waste space storing it for each membership

Booking - Storing bookings made by a member

Class - Storing the details of each of the classes offered by the gym

Room - For storing the name of the room the class is in so that if
       the name of the room changes it only needs updating once

Here's my initial ER Diagram:

--------- 1     1 --------
| Class |---------| Room |
---------         --------
1   |
    |
M   |
--------- M     1 -------- 1     M ------------ 1     1 ----------------
|Booking|---------|Member|---------|Membership|---------|MembershipType|
---------         --------         ------------         ----------------
                                   1     |
                                         |
                                   M     |
                                   ------------
                                   |  Payment |
                                   ------------

  Here's my 1NF data:

Member(MemberID, Title, FirstName, LastName, DateOfBirth, AddressLine1, AddressLine2, City, PostCode, PhoneNumber, Email, Gender)

Membership(MembershipID, MemberID*, StartDate, EndDate, AgreedPrice, MembershipTypeID*, Duration, Price, PaymentSchedule)

Booking(BookingID, MemberID*, ClassID, RoomName, ClassName, MaxSize, ClassDate, ClassTime, Duration)

Payment(PaymentID, MembershipID, PaymentAmount, DateReceived, PaymentMethod)

  Here's my 2NF data:

Member(MemberID, Title, FirstName, LastName, DateOfBirth, AddressLine1, AddressLine2, City, PostCode, PhoneNumber, Email, Gender)

Membership(MembershipID, MemberID*, StartDate, EndDate, AgreedPrice, MembershipTypeID*, Duration, Price, PaymentSchedule)

Booking(BookingID, MemberID*)

Class(ClassID, RoomName, ClassName, MaxSize, ClassDate, ClassTime, Duration)

Payment(PaymentID, MembershipID, PaymentAmount, DateReceived, PaymentMethod)

  Here's my 3NF data:

Member(MemberID, Title, FirstName, LastName, DateOfBirth, AddressLine1, AddressLine2, City, PostCode, PhoneNumber, Email, Gender)

Membership(MembershipID, MemberID*, MembershipTypeID* StartDate, EndDate, AgreedPrice)

MembershipType(MembershipTypeID, Duration, Price, PaymentSchedule)

Booking(BookingID, MemberID*)

Class(ClassID, RoomID*, ClassName, MaxSize, ClassDate, ClassTime, Duration)

Room(RoomID, RoomName)

Payment(PaymentID, MembershipID, PaymentAmount, DateReceived, PaymentMethod)

Any help or advice would be much appreciated.


texts:
I have installed SQL Server 2008 R2 on my laptop, under 32-bit Windows 7.

The SQL Server installation is using approximately 30GB of space on my C: drive. Why is it using so much space, and how can I reduce it?


texts:
My query return like this.

12
14
14
99
78
78
54

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

12
14
99
14
78
54
78


texts: Weekly full backups and daily differential backups, saving the full backups from each week?

texts:
I am wanting to create a maintenance plan that performs a full backup that is done every Sunday morning. After work hours on Monday through Friday, I want to perform a differential backup based on this full backup. However, instead of overwriting the previous week's full backup, I want to retain that full backup for historic records/off-siting while getting rid of the differentials associated with that full backup. The type of plan I am planning on creating is as such:

If needed, a rebuild of the database indexes is done beginning early AM Sunday morning.

A further caveat to this would be to reorganize + update statistics instead based on the amount of fragmentation present.

Following the index maintenance, a full backup is created that same morning.
Monday through Friday at night, a differential backup is taken based on the full backup.
Following the differential, a DB integrity check is ran.
The next Sunday arrives, a full backup is established, the previous week’s differentials removed, and the previous full backup is brought here locally to a Drobo after-hours.

The reasoning for this type of plan is due to HDD size restrictions on the Azure environment I am working with. Given that, is this a sound strategy, or where should I seek improvement? Second, how do I go about retaining the previous week's full database backups but NOT the differentials? The articles I've read thus far seem to indicate that a revolving backup of this nature will have both the full backups and differentials removed.

This is a SQL Server 2012 instance on a Windows Server 2012 VM on the Azure environment. There are 5 databases, ranging from 6GB to 10GB each, that will be affected by this type of plan.

Thanks for your assistance.


texts: PostgreSQL 9.3: Aggregate collection of JSON arrays

texts:
I have a table inventory with a value column that contains JSON strings, with the following data structure:

{
  "category": {
    "item": ["price", "quantity"]
  },
  "widgets": {
    "foo": [300, 15]
    "bar": [500, 5],
    "baz": [400, 10]
  },
  ...
}

To query for a particular item, I use the following parameterized SQL:

SELECT
  (value::JSON->$1->$2->>0)::INT AS price
, (value::JSON->$1->$2->>1)::INT AS quantity
FROM inventory
WHERE as_of_date = $3::DATE

...where $1 is the category, $2 is the item and $3 is the date.

Now I need to sum() all of the prices and quantities for each item in a category, and I'm not sure how to go about it. I've tried using JSON functions like json_each, but can't manage to get the nth array element from all JSON field values.


texts: What could cause a sleeping session to have an open transaction?

texts:
For one of our SQL servers there has been a proc which creates a blocking mess:
That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from sp_whoisactive is status = sleeping and open tran 1 for duration of approx 5-6 minutes. In this duration heaving blocking chain shows up.
This SP do not have any transaction like BEGIN and END TRAN. It does some basic select col,col2,col3.... into #temptables from table1 inner join table 2 ... and then select from that #temptable
While we are checking from app why there could be a transaction left open, i was reading in such scenarios to use XACT ABORT ON in SP itself. But when there is no transaction involved how XACT ABORT setting will help in  this case?
Please advise


texts:
Whats wrong with this statement?
why it gives below error message

Error Message:

  SELECT failed because the following SET options have incorrect
  settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
  use with indexed views and/or indexes on computed columns and/or
  filtered indexes and/or query notifications and/or XML data type
  methods and/or spatial index operations.

Statement

  SELECT  ISNULL(STUFF((SELECT ', ' + u.TITLE + ' ' + u.FirstNAME  + ' ' + LastNAME   [text()]
                                         FROM table1 c
                                         INNER JOIN table2 u ON c.user = u.user
                                         AND c.Task = @task
                                         AND c.Type = 'Assign'
                                         FOR XML PATH(''), TYPE)
                                        .value('.','NVARCHAR(MAX)'),1,2,' ')
                                 ,'') UNAMESLIST


texts: Host not Allowed Connectivity to MySQL Server

texts:
I currently have a website and it references mySQL database. The website uses HTML, CSS and PHP.

PHP code embedded inside HTML website, fetches a value and stores it on the SQL database. Then the website reflects the value stored in the database. Basically,  the website is a page counter, for instance every time a user views my webpage it counts it as a numerical value and displays the value on the webpage.

Within my PHP code, I'm supplying correct credentials, such as Host, User, and Password. But, when I try to access the website I receive error code "SQLSTATE[HY000] Host'x.x.x.x' is not allowed to connect to this MySQL server"

I think the issue is mySQL isn't allowing host to access SQL database. The reason I know this, is because I performed 3 steps to log into SQL without a password. when I log into mySQL with no password, I can see the website page counter and it successfully works.

Steps I use to log into SQL without a password are:
1) /etc/init.d/mysqld stop
2) mysqld_safe --skip-grant-tables &
3) mysql -uroot.

When I perform the above steps I can see the page counter working on the website.

What modifications do I need to perform on mySQL to allow my host to access the SQL database?

I've already tried GRANT ALL PRIVILEGES ON .* To root@localhost and it did not work.
(MySQL database is installed on the same host accessing the website)

My web server is Apache 2.2.15 (CentOS). The database server is running mysql Ver 14.14 Distribution 5.5.49. On Linux environment.


texts:
What is a recommendataion regarding turning on Lock Pages in Memory for 64 bit SQL Servers.

Is there any implication on turning on lock pages in memory setting?


texts: mysql_select_db() very slow on a busy server

texts:
I have a MySQL Server with about 30 Databases and 1500 Tables. Overall size of the data is 2 GB. The Server has 16 GB RAM and an AMD FX-6100 CPU. Most tables are myisam.
On average the server answers 675 queries per second, but in the afternoon it gets easily into 20k queries per second. The load of the machine barely gets over 1. So it looks like the server is just running along, singing and full of joy.

But in my PHP/Nginx stack I see over 50k cases per day where the PHP needed longer than 1 second to do its work. The other 2m PHP requests of that day finished in 40 to 150 milliseconds. I started to profile my PHP code and I can see that sometimes one call to mysql_select_db() needs over 100 milliseconds. Because mysql_select_db() gets called multiple times, this can stack up to 15 seconds wall time or more :(

PHP processes are running on multiple machines, but not on the MySQL server. I have up to 1000 active & persistent connections. The machines are connected by 1G Ethernet, traffic between them is 60Mbit maximum.

So I'm suspecting that I'm doing something wrong on my MySQL config. It feels like I'm driving a race car but with a locked handbrake.

This is my config:

# MyISAM #
key_buffer_size                = 1G
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                        = NO_UNSIGNED_SUBTRACTION
innodb                         = FORCE

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 1700
thread_cache_size              = 100
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 10000

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

# LOGGING #
log_error                      = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log

Here are status variables about open tables:

Open_tables 5,523
Opened_tables   14 k
Table_locks_immediate   6,792 M
Table_locks_waited  6,171 k


texts:
The Postgres documentation says that materialized views with concurrent refreshes generate bloat as usual tables, because it executes deletes and inserts. For usual tables, there are some queries to monitor the table bloat.

Is there a way to monitor bloat on materialized views?


texts: Data replication between SQL server 2008 R2 and other NOSQL DB (ML, MongoDB)

texts:
I'm a newbie in RDBMs and I have the question regarding replication between SQL Server (Oracle in future) and NOSQL MarkLogic server (ML).

There are two servers:

Machine with SQL server 2008 R2 (Standard edition)
Machine with MarkLogic server.

SQL server has a lot of tables (data1, data2, ....., data200) with rows that can be changed, removed or added.

My goal is to provide replication between servers.

I need to retrieve data from SQL server and save it to ML. There is no problem with it. I can execute SQL query from ML server.
But, the most important thing is tracking changes in those tables. Of course, I could retrieve all data from all tables and compare it with existing data in ML, but it is very expensive.

So, I see few ways to resolve my problem:

As far as I know I can to get last modify data of each table.

SELECT modify_date FROM sys.tables WHERE name = 'data1';

ML server could to check SQL's tables every 1 or 2 minutes and retrieve data from modified tables.

Create procedure that would send http-post when the data changed. I've heard that isn't good idea.
Use capture data, but it isn't my way, because CD is supported by SQL server enterprise.
ML server has own ODBC driver, Maybe can somehow to use it?

Are there any more ways or good practice to replicate data between SQL server 2008 R2 and other NOSQL DB (ML, MongoDB and other.)?

Thanks.


texts: SSIS package run error indicates wrong credentials after VM migration

texts:
First, I am not a SQL DB admin. I inherited this system and working to migrate away from it...that said:
I have an old MSServer2000 SQL DB we migrated to VM. That server offloads data to another SQL Server running server2008 via an SSIS package executed by the 2008 server.
After the migration the SQLSrv2008 SSIS package is not running, and the error log indicates the "login failed for user '(username)'.". However the username was not changed in the SSIS connection string or on the VM (MSSQL2000)? So I have no idea why the SSIS package is having trouble authenticating.
Thanks for any input!


texts: SQL Server 2012 Express high Commit (KB) (low virtual memory condition)

texts:
I have a customer running SQL Server 2012 Express Edition:

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15
  Copyright (c) Microsoft Corporation Express Edition (64-bit) on
  Windows NT 6.1  (Build 7601: Service Pack 1)

The server is 64-bit, running Windows 7, and they have 8GB of RAM. The page file is configured to be managed by the OS.

The other day, they began noticing "low virtual memory condition" entries in the Windows Event Viewer such as the following:

  Windows successfully diagnosed a low virtual memory condition. The
  following programs consumed the most virtual memory: sqlservr.exe
  (2792) consumed 28103749632 bytes, svchost.exe (592) consumed
  617349120 bytes, and w3wp.exe (7700) consumed 609824768 bytes.

I logged in and took a look at the Windows Resource Monitor and noticed that the Commit (KB) counter for the sqlservr.exe process was "high" while the working set was relatively "low":

Process Explorer confirmed:

Based on the documentation for Commit (KB):

  This is the total amount of physical and virtual memory (page file)
  that is committed to this specific process.

I also monitored Perfmon's Available MBytes counter and it consistently reads an average around 4.624 GB.

select * from sys.dm_os_process_memory returned this following:

Why would sqlservr.exe be using so much "virtual" memory when there's still plenty of "physical" memory available? Also, I thought SQL Server 2012 Express was limited to 1GB of memory - is that just a physical memory limit?

On a side note, a number of web links mentioned that McAfee Virus Scan Enterprise/McAfee Host Intrusion Prevention could cause this type of behavior. Both were installed by on this server by their IT group. I attempted to add the appropriate exclusions but never saw the Commit (KB) drop. How could I prove it's a McAfee issue?

UPDATE: I found what seems to be a similar issue with McAfee Host Intrusion Prevention and SQL Server on the same box.


texts:
my question is about speed when perform service mysql start.

with many tables (some not often used).

what engines consumes more or less start time ?

example: innodb --> the more tables, the more time it takes for mysql to start because it has to "load" indexes at start...

but how is it with csv engine (no indexes) ; how mysql with "deal" with those tables during a mysql start ?

Thanks

(from Comment)  I don't use innodb. I have some aria tables, tokudb tables and rocksdb tables.