texts: Single or multiple non-clustered indexes when pairing with a composite primary key

texts:
I have a database where I am utilizing a TenantId throughout all tables that need to be uniquely identified to a particular tenant, and due to the ordering requirements within composite keys, I have TenantId as the first in the index list. Now comes into question an Authentication piece where the User table contains the TenantId, UserId (the IDENTITY column), and Email among other login-specific items.

The login portal is not tenant-specific, so when logging in, the user will simply input their Email, thus seeking to the row that verifies their login information. In this scenario, we are unable to immediately leverage the composite primary key of TenantId and UserId until we have found the row that applies to Email.

The composite primary key on TenantId and UserId will always be utilized in all other conditional clauses. However, in order to leverage this key in the first place, we must first seek to that row based on a query of Email. Without an index on Email, a table scan will occur instead.

My question is, what type of index combination would be seen as most suitable in this scenario? A single non-clustered index on Email alone, another composite key on UserId and Email in conjunction with the single non-clustered index on Email with INCLUDES on other relevant data, or none of the above?

The schema is similar as such:

CREATE TABLE [User] (
     [TenantId] [int] NOT NULL
    ,[UserId] [int] IDENTITY(1,1) NOT NULL
    ,[Email] [varchar](64) NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[MiddleName] [varchar](32) NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[PasswordHash] [varbinary](64) NOT NULL
    ,[PasswordSalt] [varbinary](32) NOT NULL
    ,[HashMethodId] [tinyint] NOT NULL
    ,[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT 1
    ,[IsLocked] [bit] NOT NULL CONSTRAINT [DF_User_IsLocked] DEFAULT 0

    ,CONSTRAINT [PK_User_TenantId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [UserId] ASC)
    ,INDEX [IX_User_UserId_Email] NONCLUSTERED ([UserId] ASC, [Email] ASC)
    ,CONSTRAINT [FK_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant]([TenantId])
    ,CONSTRAINT [FK_HashMethod_HashMethodId] FOREIGN KEY ([HashMethodId]) REFERENCES [HashMethod]([HashMethodId])
);
CREATE NONCLUSTERED INDEX [IX_User_Email] ON [User]([Email]) INCLUDE ([PasswordHash],[PasswordSalt],[HashMethodId],[IsActive],[IsLocked])

-- Note for research: Why can an index that has INCLUDE not be specified in CREATE TABLE?

My understanding is that [IX_User_UserId_Email] is useful in this scenario to quickly tie back to [PK_User_TenantId_UserId], thus seeking to the appropriate level of isolation more efficiently. Is this an incorrect assumption? Am I better served with using just [IX_User_Email]?

All tables will JOIN to User on TenantId and UserId.
No tables will strictly JOIN to User based on UserId.
A lookup will happen based strictly on a query of Email. TenantId and UserId will not be known until the row is fetched. Once the row is fetched, remaining queries will utilize TenantId and UserId.

Another option I have been tossing around is within the Tenant table, including a Domain column that specifies the tenant source e-mail domain (which will always be the same across a tenant). Once the user has entered their Email and tabs/selects the Password field on the login page, it will parse out the e-mail domain (@sample.com), allowing us to query the smaller Tenant table to find their TenantId, thus being able to leverage the composite key [PK_User_TenantId_UserId] and thus only having to utilize a non-clustered index on Email. This may be a needless approach, however.


texts: Should I store relation counts as their own columns, or will indexes be fast enough?

texts:
Let's say I have this schema:
posts
- id
- body

likes
- id
- post_id

Should I add a likes integer column to posts which increments every time a new record is inserted into likes?
Or will a join still be fast once it reaches millions of rows?


texts:
I am running a query window under my own account and the following command:

DECLARE @ReturnCode int
EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\backup\sql', 'bak', '2015-11-24T23:30:02'
IF @ReturnCode <> 0
 RAISERROR('Error deleting files.', 16, 1)

On one server this functions correctly but on another it hangs for a very long time and does not delete the files.

Is there anything in particular that could stop how this extended proc runs?

Just an update.  I have found that this proc is in the XPStar.dll and have copied that over from a working environment however its still not working.  I am wondering if I need to re-register it?  Although not sure if you can with system extended procs?


texts:
We are considering a solution that will store large files in MySQL with InnoDB. The size of the files can range from 250MB - 1GB (might grow up to 50GB in future). Here is what the plan is.

Create two tables similar to FILES(id, name) and FILE_PARTS(id, file_id, sequence int, data LONGBLOB).
Add a reference to each file in table FILES and 1GB chunks of the large file in the FILE_PARTS. This can theoretically let us store files of any size.
Wherever and whenever we need these files, we can get the parts and
join them together on the disk to get the original file.
We can also limit the amount of data in the FILE_PARTS by deleting old data. We should be able to limit ourselves within 100GB-200GB or so.

I want to understand what kind of limitations we might run into and what strategies can help us in getting this to work. Should we do a few more things? Are we missing something here? Is there a way to keep the FILE_PARTS table in a file of its own while the rest of the database is in its own file? How would database backups react?

Note: We know that storing files in relational databases is generally not recommended, but we have got some constraints in our organization that we got to live with. If you know of reasons as to why it would absolutely not work (being 5x inefficient is okay with us), please certainly let me know so that I can convince my folks here.


texts: Partial Backup / Restore Sql Server Simple Recovery

texts:
I have a scenario where we are using SQL Server 2008 R2 with FileStream.  I have partitioned the table where our files live into multiple filegroups.  What I want to be able to do is backup our primary data file and then ONLY the filestream filegroups that have changed and move them to another server.  I have a space limitation (files must fit on CD) so I only really want to move those fileGroups that have changed.

Server: Sql Server 2008 R2
Recovery: Simple

Example:

Primary Data File - Move
FileGroup 1 - No Change - Don't Move
FileGroup 2 - No Change - Don't Move
FileGroup 3 -Chagned - Move

My problem is that it appears that because my DB is in simple recovery I can't backup the individual filegroups I want to restore.  I would have to make FileGroups 1 & 2 Read Only and then use READ_WRITE_FILEGROUPS which could make it so my .Bak file is Greater than the CD Size limitation.

So with Simple Recovery is there a way I can still backup Primary Data File and FileGroup 3 separately and restore them or do I have to use READ_WRITE_FILEGROUPS? If so sounds like I will have to change the recovery model on our server.

Thanks,

S


texts:
I'm not an expert about DBMS_SCHEDULER.CREATE_JOB and i've been asked to create a job. When i run it i get these errors:

ORA-06550: line2,column 2
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'

My statement is:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'ME.Delete_org',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'BEGIN delete_org_sre_package.process_org_sre_deletions; END;',
   start_date         =>  '23-NOV-17 11.00.00 PM',
   repeat_interval    =>  'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=0; BYSECOND=0;',
   auto_drop          =>   FALSE,
   end_date           =>   NULL,
   enabled            =>   TRUE,
   comments           =>  'Every Friday at 11pm');
END;
/

Any ideas of what i'm doing wrong? Thanks


texts: Oracle isn't restoring correctly a backup

texts:
First of all I'll say I'm using:

Oracle Database 11g Express Edition
Oracle SQL developer

What I'm trying to achieve
Create a backup and restore it, so for example I can delete a table after a backup and then restore the backup to recover that table.
What I tried step by step
Step 1
First I entered to RMAN and used the following statements
connect target;
shutdown immediate;
startup mount;
configure controlfile autobackup on;
backup database;

So for now I'm connected to the database and it's only mounted, and the backup has finished correctly. You have above what I got after executing the backup.
Step 2
Now I execute startup dba; because I want to initiate the database, so I can connect with Oracle SQL Developer and for example delete a table.

As you can see I don't have now tables because I deleted the only I had (before the backup I had the table "regions" of the HR schema so that's inside the backup I guess).
After all of this I close the connections in sql developer, because I don't know if it's going to create errors, but I want to avoid them.
Step 3
Now I want to start trying recovering the backup and I execute this because I read that both for backing up and restoring, you must leave the database in the "mount state", so I go to RMAN and execute only this now:
shutdown immediate;
startup mount;

Step 4
And now in this step the real recovery, I execute this:
restore database;

recover database;

Now I should execute the following but it's returning this error:
alter database open;

So, I execute this instead:
alter database open resetlogs;

Step 5
Now that all is done and opened, I'll check if the table is recovered in sql developer.

And no, it's not there.
I hope all the info was clear


texts: Record of schema related changes - Oracle

texts:
Is it possible to get the all changes that is made to schema as in what privileges was first assigned to the schema on creation, creation date, if later modified with different privilege what was that priv, when the schema was dropped, basically this would be needed for audit purpose at the end of the year where they would be asking for all the info for the whole year for select schemas.

Is is possible to fetch this data from the database? I have enabled standard DB auditing for sys operations to be TRUE. Version 12.1.0.2 and 11gR2.


texts: Excessive log throttling on Azure SQL on business critical tier

texts:
We moved from the Biz Critical vCore Gen4_8 tier to Gen5_8 over the holidays. We had to move because our log growth is constantly pushing us into the 1 TB limit of Gen4.  Not only are our queries taking twice as long in Gen5, but we are seeing a lot of log rate throttles as shown here:

Why are there three different log rate throttles - what is the difference between them?  If you remove the non-important CXCONSUMER wait, almost half of our waits are from the 3 throttles.

I can reproduce the log rate throttles with nothing else running in our pool with a simple "SELECT TOP 20000000 * INTO newtable FROM oldtable" query.  We are selecting from a very narrow table (4 int and 2 float columns) that has a CCI and no fragmentation.

We contacted support and they responded with "its because of poor indexing or a very inefficient query plan". Obviously, there is nothing wrong with this query. It runs in 1:50 on Gen5 consistently, 49 secs in Gen4, and 20 secs on my old laptop.

When I run this query, the DMVs show the I/O for this query as: total_logical_writes=150258, total_physical_reads=44080.

Given that it takes 110 seconds to run, can we then say that each write and read is a single I/O which would make our average IOPS about 1766 ((150258+44080)/110)?   If not, is there a good way to measure how many IOPS a query hits?  I bring this up because the DB limit for Gen5_8 shows "Target IOPS" = 20000, but I think we are well below that.  Also is "target" an actual limit or an estimate of the throughput we could expect?

We never had throttling under Gen4 and are paying $50k a year for this service tier and our app is barely usable now - any suggestions/thoughts on where we go from here would be much appreciated!


texts: MySQL ran out of disk creating an index, now I can't reclaim my disk

texts:
I've run a command on my MySQL database to add an index to a large table containing 8 million records (ironically so I could start deleting some of them):

CREATE  INDEXindex_audit_logs_on_idONaudit_logs``

As part of that, MySQL will create a temporary table. I have 40GB of total disk, and this table is 25 GB, so I ran out of disk space while doing this. My commmand errored:

Mysql2::Error: The table '#sql-2b9f_2178' is full: CREATE  INDEX `index_audit_logs_on_id` ON `audit_logs`

However, I am still out of disk space now!

/dev/sdc               40G   38G   20K 100% /var/lib/mysql

I don't see this temporary table. Is there something I can do to reclaim by 15GB of disk space?


texts: How to attach .mdf file to SQL Server 2005 without an .ldf file?

texts:
I want to attach a database to SQL Server 2005 but I have only its .mdf file

How can I attach it?


texts: Create a backup daily and incremental every hour

texts:
How can I create a full backup every day, using Ola Hallengren's solution, suppose at 8AM and, after that, an incremental backup every hour. This processe should be repeat the process ever day.
I what also to keep las 2 days backup with increments.
Thanks


texts: Ola Hallengren Maintenance script - Full database backup delays

texts:
The DatabaseBackup job created by Maintenance Script will introduce delay for the last database being backed up.

What I mean is, there are 6 databases around 200GB each.

The FULL backup takes around 90minutes to complete.
The backup jobs is scheduled to run at 2AM.
So, the last 2 databases to be backed up will potentially run on business hours (after 8AM)

  1st DB will complete on 3:30 AM 2nd DB will complete on 5:00
  AM 3rd DB will complete on 6:30 AM 4th DB will complete on
  8:00 AM 5th DB will complete on 9:30 AM 6th DB will complete
  on 11:00 AM

All the data files are on E: drive
Does it take same amount of time even when the backups are made to run at the same time at 2AM(by different jobs) due to IO overhead?

Is this the normal way the FULL backups are made (with delays)?


texts: SQL Server : Perfmon Counter Incorrect value

texts:
I'm trying to understand why this Performance counter shows it is 0.

   "Mssql$instance1:locks(_total)\lock wait time (ms)"

I tried with wild card to check all instance_name and they're all 0.

   "Mssql$instance1:locks(*)\lock wait time (ms)"

But if I run the query

SELECT cntr_value
FROM master..sysperfinfo
WHERE (object_name LIKE '%:Locks%'
  AND counter_name = 'Lock Wait Time (ms)'
  AND instance_name = '_Total')

shows a different value (which is a count of "Object", "Key", and "Metadata")


texts:
I'm reading PostgreSQL 9.6 manual about Partioning.
And it says:

  Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters).

What does "externally supplied parameters" means? Is it env vars? Or config/runtime parameters? Or prepared statement parameters?


texts: Mirrored local accounts to connect to database on different server and domain

texts:
My database client and database server are on different machines and are not connected to the same domain (this is not supported with my web host). I'm not keen to send username/password details via a connection string and want to use windows authentication to connect to the database.

In this article's overview, it mentions this (emphasis mine):

  When you use Windows authentication to connect to SQL Server, you use
  either Kerberos or NTLM authentication depending on the configuration
  of your servers and domain. You might not be able to use Kerberos
  authentication if:

  Your database client and database server are separated by a firewall
  which prevents Kerberos authentication.
  Your application server and database server are in separate domains
  with no trust.

  In these situations, you can use either mirrored local accounts or SQL
  authentication. With mirrored local accounts, you configure two
  accounts on each server with identical user names and passwords. You
  must ensure that the passwords remain the same.

I'm assuming this just means creating a windows user with the same username and password on both machines, however I'm skeptical this would work. When I'm adding permissions for this user on the database client, it prefixes the username with the servername, and the same thing happens when creating a database login on the database server.

Is this what it means to create a mirrored local account? If so, how does this work considering the users are in different domains?


texts: Database Design (how to handle subclass & superclass)

texts:
I'm wondering what is the best approach to this type of problem (I'm a highschooler so don't have practical knowledge on the subject):

Let's say I want to store information about employees at a zoo. Some employees are zookeepers and take care of animals. Other employees are in charge of cleaning up, while others are in charge of delivering goods from one place to another.

Let's say each type of employee shares some attributes, like first_name, last_name, date_of_birth, but other attributes are dependent on the type of employee, like for delivery people, I would want to know if they a commercial drivers' license. For zookeepers, I would want to know what certifications they have on taking care of animals.

Am I better off creating separate entities for each type of employee, like "zookeeper", "driver" , "cleanup_dude", and having appropriate columns for each?

Or am I better off creating a single "employee" entity, and adding a column to indicate the type of employee, and other column flags for things like "has_CDL", "has_AnimalHandlingCert", etc. These would be left null for employees that do not match the criteria.

Also certain types of employees could have relationships with certain tables, which may or may not be shared with other employees. Like zookeepers could be handling different sections of the zoo, which I would want to store, but the same would not be true for a cleanup guy.

Not sure what would be the best approach to this. Any advice?
Thanks


texts: Find all users who rated all the movies rated by a specific user

texts:
So we have the following relational schema:

users(user_id, ..)
movies(movie_id, ..)
ratings(user_id, movie_id, rate)

So, we want to find all the users who have rated all the movies which are rated by a specific user '1234'

In other words, if user '1234' has rated movies 1, 2, 3, and 4, we want the user ID of all others who have also rated movies 1, 2, 3, 4.


texts: Register XML schema using dbms_xmlschema in Oracle

texts:
The requirement is to register XML schema from XSD files in Oracle 11gR2. I am using the following code:

DECLARE

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://[...]/spin-catalog-to-bpm.xsd',
    SCHEMADOC => bfilename ('/home/joe','spin-catalog-to-bpm.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/

The file spin-catalog-to-bpm.xsd is under /home/joe but when I try to register it, it gives me this error:

DECLARE
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 131
ORA-06512: at line 3


texts: No permissions to the certificate file when trying to restore a DB from a third party

texts:
I am trying to restore a Db in SQL server 16. It is a developer version installed on windows 10 laptop. We have a backup file from a SaaS instance. We have moved to a new instance of the SaaS and this is our archive from the first instance. This is strictly for archiving purposes. That said, the only was to read this of course is with SQL Server.

Script:

use master ;
 -- drop master key encryption by password ='somepassword';

-- create   master key encryption by password ='somepassword';

create certificate BULLHORN14852_45764 from file
    = 'C:\Program Files\Microsoft SQL Server\MSSQL13.LEVVEL\MSSQLBULLHORN14852_45764.cer'
with private key
  (file='C:\Program Files\Microsoft SQL Server\MSSQL13.LEVVEL\MSSQLBULLHORN14852_45764.pvk',
decryption by password = 'somepassword')

I was able to create the master key encryption no problem. When I run the create certificate command I get the famous

"The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it."

I am connected as the local administrator on windows. I have also granted permissions on the folders and the files.

NETWORK SERVICE account does have permission to the folder and the cert, pvk files.

When I look at the services and what the Logon as is I see the following.

SQL Server (LEVVEL) - logon as NT Service\MSSQL$LEVVEL

SQL Server (MSSQLSERVER) - logon as NT Service\MSSQLSERVER

SQL Server Agent (LEVVEL) - logon as NT Service\SQLAgent$LEVVEL

SQL Server Agent (MSSQLSERVER) - logon as NT Service\SQLSERVERAGENT

The granted permissions on the folder (and files ) are: NETWORK SERVICE and MSSQL$LEVEL. I can't find any user names starting with SQL or MSS or NT in order to add other users to the permissions.

I am not sure what I am missing.

I've been reading that these permissions above are not enough. I haven't administered a SQL DB in 15 years so I need some guidance. Any help would be greatly appreciated!

Rob.


texts:
I am trying to have a better understanding of the term: "Obsolete Database"

Are there elements that defines an obsolete database?


texts:
Working in SQL Server 2008 R2, I am trying to rollback a set of DDL statements as a group (think of an upgrade script for a database), but am running into trouble.

Take the following code:

begin try

begin tran

create table foo (i int)
alter table foo add x dog
insert into foo select 1,1
insert into foo select 1,1,1

commit tran

end try

begin catch

rollback tran
print @@error

end catch

I'm expecting the try to fail on the alter table statement, drop to the catch, rollback the transaction, and print the error message. However, if you check your objects/tables, you'll see that foo is still there (so the create table didn't rollback properly).

select * from sys.objects where name = 'foo'

What am I doing wrong here?


texts: Restore in Full Recovery model with no transaction log backups

texts:
Just realized one of our databases has been in Full Recovery mode but we've not been making log backups. On a nightly basis, we've been running a job that runs this T-SQL command to do the database backup:

BACKUP DATABASE [foo] TO  DISK = N'J:\NAV Backup\foo.bak' WITH NOFORMAT, INIT,  NAME = N'Foo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

After searching the intertubes, I'm still uncertain about what would have happened if we would have had to restore with only just the .bak file (no transactional log file or backup of the transaction log). Would we have been able to get back to the point of the last nightly backup?

Assuming a restore would have worked, would the new transaction log been has big as the old (lost) one (~300GB in our case)?


texts: SQL Agent still attempting replication for deleted publication

texts:
We recently retired a replication publication, deleting each subscription (push subscriptions) and then deleting the publication, all from within SSMS.  However, we continue to get the following message (once every minute) in the four days since deleting this publication.

  Replication-Replication Distribution Subsystem: agent (null) failed. The publication 'Replicate_ASG_Reference_Data' does not exist.

Why is it still trying to run a deleted publication, and how do I stop it?

Additional information: This is on SQL Server 2008 SP3 running on Windows Server 2008 R2.


texts: MySQL Update Trigger that updates multiple columns from values in another table

texts:
I have a table PRODUCTS with columns id, name, sku, packaging_type, width, height, weight, case_count.

I want a trigger to fire when packaging_type is updated.  packaging_type will be an integer value.

There is a second table PACK_TYPES with columns id, width, height, weight, case_count.  This is where I need to get the values from.

So PRODUCTS.packaging_type gets updated, how do I update PRODUCTS.width, PRODUCTS.height, PRODUCTS.weight, and PRODUCTS.case_count with the corresponding values where PRODUCTS.packaging_type = PACK_TYPES.id?


texts: Credentials error while performing unattended install of mulitple instances

texts:
I'm installing multiple named instances of SQL Server 2019 on, the base instance and 5 others (A/B/C/D/E).  I'm trying to use a configuration file to setup everything but I keep getting the error:

The following error occurred:
The credentials you provided for the 'NT Service\SQLTELEMETRY$C' service is invalid.`

(This is trying to install instance "C".)
The only reference in the ini is:
; TelemetryUserNameConfigDescription

SQLTELSVCACCT="NT SERVICE\SQLTELEMETRY"

So I assume it's trying to create a default user $C. Anyone else ever have this?


texts: Array of one value, for every integer in another field?

texts:
I'm stumped on this one, I have the following:

Color  Qty  Length
('Blue', 5, 50),
('Blue', 1, 500),
('Blue', 1, 250),
('Green', 4, 175),
('Green', 2, 35),
('Green', 2, 80)

I need an array that groups by color, showing the length for each qty, by that I mean:

Blue: 50,50,50,50,50,500,250
Green: 175,175,175,175,35,35,80,80

I've tried all sorts of GROUP_CONCAT/CONCAT combinations but I am stuck on getting the length column to repeat for every qty.

I have a sqlfiddle here.

Is it even possible? :)


texts: Restoring database always throwing exception

texts:
I am trying to restore some database backup file into the same database. The .bak file is creating problem always. When I select the .bak file from a network drive like "\\TestPC\ShareFolder\test.bak" then its working correctly.

But the same .bak file if I select from a local drive by copying it to my pc, say C:\TestPath\test.bak then for every time its throwing a exception saying

  Cannot open backup device 'C:\TestPath\test.bak'. Operating system error 3(The system cannot find the path specified.).
  RESTORE DATABASE is terminating abnormally.

I don't know why its throwing, I also changed the SQL Server log on from a Network drive to a local connection also. Still its not working. Can some one hint me how to solve this problem?


texts:
The service I created will increase the number of API call, actually we only insert on our log table arround 200k entries every days.
Soon we will need to support 1-2 Millions entries each day on our Database.
Each API call = One entrie on the DB.

How can I increase the performance of the table logs_fb ?
What is the number maximum of rows on a table ?


texts: DROP DATABASE IF EXISTS CREATE DATABASE IF NOT EXISTS USE in PostgreSQL

texts:
I always was used following header in files that defined database in MySql:

DROP DATABASE   IF     EXISTS base;
CREATE DATABASE IF NOT EXISTS base;
USE base;

In PostgreSQL there are the following problems.

IF NOT EXISTS is not definied, USE is not defined. Instead of this we can use \c but it doesn't work if I write it in executable sql file. Additionally I cant remove database If I am connected with this database.

Is there any alternative of these commands in PostgreSQL. I want to have idempotent sql code that create database if this database is not defined and totally clean it if this database exists.


texts:
I'm trying to write a query which will return a boolean value if there exists any user which has this specific role.

For example is there any user hwo's role is 'role1'

This is how far i got

SELECT CASE WHEN EXISTS
(SELECT --'Here i don't know how to select all user's which have a specific role'--    )
GO

And if it's possible to check user's from only one database .


texts: Doing a join to search geolocation points in zones


texts: Are SQL Server's Date values stored as a single int?

texts:
SQL Server stores DATETIME internally as two INT values, as SQLDenis explains.

Is it correct (by logical extension) that DATE types (SQL Server 2008+) are stored as a single INT?


texts: Why do MySQL tables crash? How do I prevent it?

texts:
I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable.

Luckily, a simple REPAIR TABLE mdl_user made it work back again. The thing is that I don't know why it actually crashed and made it unusable, and I want to make sure that I'm better prepared next time.

I'm not a thoroughly experienced DBA — I'm just a developer who does a lot of stuff, so please bear with me.

I could just restore a backup, but I guess there are ways to prevent crashes.

Those tables are utf8_general_ci and using MyISAM.

Why does a MySQL table crash? What can I do to prevent that from happening?


texts: Moved site to new, similar server - same query is taking 10x longer?

texts:
Edit:
Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0
And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7

This morning I moved my WP website from one server to another, similarly configured with MariaDB 10.5.8 on the old, and 10.5.9 on the new.
According to slow-query log, some queries are going very slow. Here is one.
SELECT DISTINCT a.id  FROM wp_bp_activity a
WHERE a.type IN ( 'activity_update','created_group','new_avatar' )
  AND a.is_spam = 0
  AND a.hide_sitewide = 0
  AND a.type NOT IN ('activity_comment', 'last_activity')
ORDER BY a.date_recorded DESC, a.id DESC LIMIT 0, 16;

It takes 5,6,7 seconds. Running the exact, same query on the old server, it takes 0.0002 seconds.
Here is an EXPLAIN from both servers:

Why is one "index" and the other "index_merge" using different keys? It's the same query on the same table.

Both tables are nearly identical, one was live earlier today so it has relatively updated information.
The OLD server DDL of the table:
CREATE TABLE `wp_bp_activity` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `item_id` bigint(20) NOT NULL,
 `secondary_item_id` bigint(20) DEFAULT NULL,
 `date_recorded` datetime NOT NULL,
 `hide_sitewide` tinyint(1) DEFAULT 0,
 `mptt_left` int(11) NOT NULL DEFAULT 0,
 `mptt_right` int(11) NOT NULL DEFAULT 0,
 `is_spam` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `date_recorded` (`date_recorded`),
 KEY `user_id` (`user_id`),
 KEY `item_id` (`item_id`),
 KEY `secondary_item_id` (`secondary_item_id`),
 KEY `component` (`component`),
 KEY `type` (`type`),
 KEY `mptt_left` (`mptt_left`),
 KEY `mptt_right` (`mptt_right`),
 KEY `hide_sitewide` (`hide_sitewide`),
 KEY `is_spam` (`is_spam`),
 FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1060622 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The NEW server DDL:
CREATE TABLE `wp_bp_activity` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `item_id` bigint(20) NOT NULL,
 `secondary_item_id` bigint(20) DEFAULT NULL,
 `date_recorded` datetime NOT NULL,
 `hide_sitewide` tinyint(1) DEFAULT 0,
 `mptt_left` int(11) NOT NULL DEFAULT 0,
 `mptt_right` int(11) NOT NULL DEFAULT 0,
 `is_spam` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `date_recorded` (`date_recorded`),
 KEY `user_id` (`user_id`),
 KEY `item_id` (`item_id`),
 KEY `secondary_item_id` (`secondary_item_id`),
 KEY `component` (`component`),
 KEY `type` (`type`),
 KEY `mptt_left` (`mptt_left`),
 KEY `mptt_right` (`mptt_right`),
 KEY `hide_sitewide` (`hide_sitewide`),
 KEY `is_spam` (`is_spam`),
 FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1060840 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: MySQL MEMORY table has high Lock Wait Ratio

texts:
Possible Duplicate:
  Mysql Memory table getting many locks

on my site I log every pageview (date,ip,referrer,page,etc) in a simple mysql table.

This table gets very little selects (3 per minute), but a lot of inserts. (about 100 per second)

Today I changed this table from an InnoDB table to a MEMORY table, this made sense to me to prevent unnecessary hard disk IO. I also prune this table once per minute, to make sure it never get's too big.

--

Performance wise, things are running fine. But I noticed that while running tuning-primer, that my Current Lock Wait ratio is quite high.

Current Lock Wait ratio = 1 : 561

My question: Should I worry about this Lock Wait Ratio? And is there something I can change in my my.cnf to improve things so that the lock wait ratio isn't so high?


texts: Cannot connect to MS SQL 2008 R2 by DBVisualizer. "Native SSPI library not loaded" error

texts:
I try to connect to MS SQL 2008R2 database by DBVisualizer.
I use jTDS driver, but following error occurs

An error occurred while establishing the connection:

Long Message:
I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

Details:
   Type: java.sql.SQLException
   SQL State: 08S01

I added ntlmauth.dll to jre/bin but it didn't help.
I have java 1.6.0_25.


texts: PostgreSQL continuous archiving - use snapshot for base backup on AWS?

texts:
I'm new to AWS and PostgreSQL admin and have been poring over the docs trying to come up with a good backup strategy for a fairly simple setup.

I have a single Amazon Web Services EC2 instance that will run a web app and a PostgreSQL 9.2.7 database. My setup is pretty similar to this question, but I'm considering a different approach.  Because I must encrypt the data, I'm storing it on a separate EBS volume formatted with XFS. I expect the database will be fairly small (a few GB) for a while. I want to be able to restore up with next to no data loss, so continuous archiving seems the way to go.

It looks like I can ship the WAL files directly to S3, probably using s3cmd (see this code).

But what about base backups? The PostgreSQL docs recommend using a file-based tool like tar. I had been planning to create an EBS snapshot as the base backup using a sequence something like this:

pg_start_backup
sync
freeze XFS
create snapshot
unfreeze XFS
pg_stop_backup

The PostgreSQL docs do talk about "consistent snapshots" here, but not in conjunction with continuous archiving. Is there any reason to avoid snapshots as a way of getting a base backup?  Wouldn't restoring an EBS volume from a snapshot be faster than creating an empty EBS volume and untarring a large file? It is a bit of a management headache since the base is not stored with the WAL files...

Also, if the snapshot is the way to go, do I need the freeze/unfreeze the XFS volume? For reasons I don't understand, the continuous archiving docs actually advise against stopping database writes during a base backup:  "It is neither necessary nor desirable to stop normal operation of the database while you do this."


texts: Multi server administration - Failed to import Maintenance Plan to named instance

texts:
I have a multi server administration environment and several maintenance plans deployed already. My MSX server is SQL2014. The problem i'm having is deploying maintenance plans to a 2008R2 named instance which is running alongside a default 2014 instance (which is working with MSX MPs already).

I'm getting this error:

[000] Failed to import Maintenance Plan 'MSA backup' from MSX: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.
 ---> System.Runtime.InteropServices.COMException (0xC0011008): Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.

I added the account under which the agent service is running on the MSX to the sysadmin role on the named instance.

I know it's trying to import to SSIS but there can only be one SSIS instance on a machine and that is the default, 2014 instance.

Locally created MPs run without problems. I can provide more info if needed.

I have googled without luck so far, but will continue.

TY.


texts: Identity column re-seed: when it is necessary?

texts:
During one of the last lessons at university (I'm a student), lecturer asked us to develop a database (MySQL Server if it matters) and tiny client app that would consume the database as data source.

One of requirements was that the identity column (which is the PK in every table) must be sequential, because it is a good practice (as per lecturer words). That is, when table row is deleted, it's PK must be reused in subsequent inserts. I have average knowledge in RDBMS, PKs and identity columns. From what I understand, that identity column is just a way to let DB to auto-generate PKs when inserting rows and nothing more. And identity column value shall not be related to row attributes in any way (as long as it is not natural key).

This requirement (strictly sequential identity column) was suspicious to me. I tried to ask the lecturer what is wrong if identity is not sequential (with gaps caused by deletions), but got very abstract answer like "it is convenient for users and useful for DB administrators who maintain the database". No specific examples. The argument "convenient for users" sounds silly, because it doesn't have any meaning in business domain.

Therefore I'm curious if these reasons are real? I can think only of one case when identity column reseed is required -- when identity space is exhausted. But this is more design issue when identity column type was chosen incorrectly, say simple int instead of bigint or uniqueidentifier when table contains billion rows. Suppose, an identity column is a clustered index: can gaps in identity column affect index performance? Maybe there are other real-world reasons for automatic identity column re-seed after each delete I'm not aware of?

Thanks in advance!


texts: Oracle How to select execution time for "ALTER TABLE DROP PARTITION"

texts:
I have following query which ran overnight.

ALTER TABLE TRANSACTION DROP PARTITION RANGE_B UPDATE GLOBAL INDEXES;

I'm trying to find the time taken for above query.I have tried following but, results does not have above query.

 select SQL_ID,EXECUTIONS,ELAPSED_TIME,CPU_TIME,SQL_TEXT
 from V$SQL
 WHERE SQL_TEXT LIKE '%DROP PARTITION%';

I have tried to find the execution time in Oracle Management Console's Monitored SQL section. There also could not find the above specific query.


texts: Postgresql Logical replication initial slot snapshot too large

texts:
I'm trying to replicate some data from one database to another with logical replication.

But it gets stuck into the catchup state. When I'm looking at the logs initial slot snapshot too large I'm not sure of the exact meaning?

I'm a bit stuck and not sure where to look at. And what scares me is that if I look at the lag the wal keep increasing.

SELECT pid, usename, application_name, state
, pg_current_wal_lsn() AS current_lsn
, sent_lsn
, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS sent_diff
, write_lsn
, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) AS write_diff
, replay_lsn
, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_diff
, write_lag, flush_lag, replay_lag
FROM pg_stat_replication
ORDER BY application_name, pid;
-[ RECORD 1 ]----+----------------
pid              | 23031
usename          | ...
application_name | ...
state            | catchup
current_lsn      | 36/1F6562B0
sent_lsn         | E/14669F88
sent_diff        | 160 GB
write_lsn        | E/4B6E1CA8
write_diff       | 159 GB
replay_lsn       | E/4B6E1CA8
replay_diff      | 159 GB
write_lag        |
flush_lag        |
replay_lag       |


texts: Three databases : one instance or three?

texts:
I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization.

I have identified the main databases that are mission critical to the organisation. We have a virtualised infrastructure. We currently run SQL Server 2008R2.

Fundraising Database (off the shelf package)
Case Load Services Database (Developed in house)
Finance Database (off the shelf package)

Now the question I have is (bearing in mind limited budget and resources) is whether I should put all 3 of these databases on 1 single instance on 1 single Windows Server 2008 VM. Or would it be better to have them on a single box with 3 named instances? Or possibly even 3 separate VM Servers with 1 databases each?

So even though each database is mission/business critical it is ok to have all of them on 1 single instance. So we would create a virtualised windows 2008 server called SQL01 for example. Install 1 instance of SQL 2008r2 and restore all 3 databases on that single instance? Or would it be better to create 3 names instances on that single server and have each database within its own named instance?


texts: SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover?

texts:
It is my understanding that for automatic failover to occur in an Always On AG environment, the replicas just need to be configured with "automatic failover," "synchronous commit" and the environment must have proper quorum (i.e. odd number of voting members).
However, according to the "SQL Server 2019 Always On" textbook by Peter A. Carter, for automatic failover to work, the secondary cannot be active and must be passive. Passive meaning the Readable Secondary setting is set to 'No' so it is unavailable for direct connections or read access, and obviously no backups can be done on them either.
Here is the exact statement from the textbook:

"Although we can change the value of Readable Secondary through the
GUI while at the same time configuring a replica for automatic
failover without error, this is simply a quirk of the wizard. In fact,
the replica is not accessible since active secondaries are not
supported when configured for automatic failover."

Source: Pro SQL Server 2019 Administration: A Guide for the Modern DBA
Here's a screenshot of the "quirk"

Note: According to the textbox, the Readonly Secondary should be set to 'No' if you want automatic failover to work. Thinking back, I don't recall a successful automatic failover occurring with this set up and I believe when an issue occurred, the AG was in a RESOLVING state. Note, we have third server for proper quorum.
I've searched through Microsoft's documentation to confirm whether or not the author's statement is true but have yet to find anything that explicitly states that the secondary replica must be passive for automatic failover to work. This Microsoft article (Failover and Failover Modes (Always On Availability Groups)) states the conditions required for automatic failover but does not mention needing a passive secondary replica.
So is the author correct and Microsoft has failed to advertise this OR is the author incorrect and automatic failover does work when the secondary replica is active/readable? My objective is to have a highly available environment with automatic failover but do we need to sacrifice offloading read operations in order to accomplish this? Adding another server is not an option.


texts: Postgres Logshipping with Streaming replication with Asynchronous with failover and Failback procedure

texts:
Need your help here with exact steps:
I configured the Logshipping between Master and standby server using the below link for postgres 12.x, i.e log shipping with streaming replication in Asyncronous method is configured.
https://www.tecmint.com/configure-postgresql-streaming-replication-in-centos-8/
My questions:

When master is down, how to change the slave as a master .. need with commands

and the original master is back , how to promote original master as master and stand by as standby?

Need your help in getting all the commands
This is to achieve when master is down and slave takes responsibility and when master is back , slave give the responsibility back to Master and act as Standby as like before.
Even if the steps are manual also fine , just need detailed steps.


texts: Bulk insert not retaining special chars of UTF-8 Encoded txt file Sql Server 2008

texts:
I have a stored procedure that bulk imports a text file and inserts it into my database.

CREATE TABLE DBO.TEMP_STORE
(
            ID nvarchar(max),
            [MONTH] nvarchar(max),
            [YEAR] nvarchar(max),
            STORE nvarchar(MAX),
            SUBMITTAL nvarchar(MAX),
            ENTITY nvarchar(MAX),
            SUBMITTAL_TYPE nvarchar(MAX),
            iCOUNT nvarchar(MAX),
            STATE nvarchar(MAX),
            COUNTRY nvarchar(MAX),
            REGION nvarchar(MAX),
            GLOBAL_REGION nvarchar(MAX),
            LOCAL_CURRENCY nvarchar(MAX)

 )
--SELECT * FROM DBO.TEMP_STORE
--prepare bulk insert query to load data from file to temp table
SET @SQL='BULK INSERT DBO.TEMP_STORE FROM '''+ @FilePath+''''
SET @SQL=@SQL+'WITH ('
SET @SQL=@SQL+'DATAFILETYPE = ''char'','
SET @SQL=@SQL+'FIELDTERMINATOR = ''|'','
--SET @SQL=@SQL+'ROWTERMINATOR = ''\n'','
SET @SQL=@SQL+'ROWTERMINATOR = ''' + nchar(10) + ''','
SET @SQL=@SQL+'FIRSTROW =2)'

--print @SQL
EXEC (@SQL)

For instance, importing Update ROLLING_CONE_SHOP_DETAIL set SHOP_STATE = 'São Paulo' results in storing the data as'S+úo Paulo' when I select the same row that was imported.  I can update the row and explicitly fix the fix the issue:

Update TEMP_STORE set STATE = 'São Paulo' where STATE= 'S+úo Paulo'

But when I do my Bulk insert, it does not retain the special character.  I have confirmed the text file I am importing is saved at utf-8 and contains the correct character.

How can I make sure my bulk inset retains the special character properly?


texts: inserting large quantity of data into mysql table

texts:
I need to insert about 400 000 000 rows of test data into the InnoDB table.
Table is quite simple:

CREATE TABLE IF NOT EXISTS `tgt` (
  `id_1` int(10) unsigned NOT NULL,
  `id_2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_1`,`id_2`),
  KEY `id_1` (`id_1`),
  KEY `id_2` (`id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data is coming from another table:

CREATE TABLE IF NOT EXISTS `src` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  # skip some columns
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I need to insert into tgt all possible combinations  of different ids from table src, so naive method is to execute:

INSERT INTO tgt (id_1, id_2) (SELECT  a.id,  b.id FROM `src` a INNER JOIN src b  WHERE a.id <> b.id)

And, of course it take  hours or even days... So I am searching for a faster method of doing that.


texts: How to collect NTFS file properties and insert into SQL Server table

texts:
I have to provide reports on file system usage.

I'm collecting statistics on file server usage down to individual file level so we can see who is using what files/folders, how much storage they're using, how many files they have, when they were created and last used.

To do this I have 2 powershell scripts.

The first reads through the file system and captures the attributes I want and saves them to a file.

dir -rec G:\ | Select LastWriteTime, Directory, Name, Extension, Length, @{Name="Owner";Expression={get-acl $_.FullName| select Owner}} | export-csv FileInfo.csv

The 2nd script reads the csv file and inserts the data into a table.

Once the data is in SQL I can parse the text and split it into various columns and and then produce a variety of reports and analyse the data in different ways. My approach works but it's cumbersome.

Is there a better way to collect NTFS information and save it into SQL Server? What are the alternatives? SSIS?

Edit: Could this all be combined to operate together in a single process?


texts: Running SQL Server on VMWare along with many other VM's

texts:
Excuse the long question, this is my first on the board.

I have just joined an organisation that is running a mission critical SQL server 2012 on a VMWare host along with another 9 (at least) VM's (web servers and batch processing servers mainly) on the same physical box.

The Physical box has 6 CPU's with 64GB of Memory at the moment. The disks are tiered so that tier 1 is pure SSD, tier 2 is SAS with SSD cache, and tier 3 something slower. I believe failover is provided at the VM level. It is not set up to reserve any CPU's or memory for the SQL server.

Our main bespoke application system suite runs the company and is a Tier 1 application which has 250GB data spread across multiple databases.

We do not have a massive IT team, and no one who is experienced at the VM and SQL level. Therefore we have hit a bit of an impasse.

Myself (with 25 years development experience) and our Experienced Contract DBA believe that a much better solution which would be simpler and easier to manage would be to avoid the VM route for the database boxes, or to use HyperV and allocate specific resources to the SQL server.

The SQL server (and it's clustered counterpart when we implement it) should live on separate physical boxes, or properly configured HyperV instances. One of the reasons is that the DBA wants to horizontally stripe some of the larger tables, and our infrastructure team have said separate drives per NDB file are not possible with the current setup. We could put the current years data on tier 1 and older data on tier 2.

From reading VMWare best practices, the host should really only contain instances of SQL server anyway, however we have all sorts of VM's installed there. You should also assign specific CPU's to HyperV CPU's for sole SQL use.

As you can imagine, when a slowdown occurs, at the moment, it's very difficult to identify the exact problem as VMware is spreading usage where it wants to.

Our Infrastructure team maintains that this is a valid configuration, but to be honest, myself and our DBA in our 40 odd years of experience have never seen anything like it. The solution we recommend needs to last for at least 3 years before we start looking at the configuration again.

My questions are (and treat this as if cost is not a major issue)

Is the current configuration good design/best practice?
Are there any benefits to the current configuration?
Would we not be better having two physical clustered SQL boxes at different sites and perhaps a mirrored VM host with web servers and possibly some service machines on them?
Any help or suggestions would be seriously appreciated.


texts: May I ask if its possible to have a table on Slave server. But it does not exist on Master Servers? how come this one happens? Thank you.


texts: Do stored procedures prevent SQL injection?

texts:
Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection even if we only use stored procedures. However, this problem does not exist in PostgreSQL.

Does the stored procedure implementation in PostgreSQL core prevent SQL injection attacks or is it something else? Or is PostgreSQL also susceptible to SQL injection even if we only use stored procedures? If so, please show me an example (e.g. book, site, paper, etc).


texts:
Assuming I have a sql database server with disk space as follows:

D Drive: 10GB (For the MDF file)

E Drive: 10GB (For the LDF file)

And I have only one database on the server with the following details:

Disk space occupied:
9GB MDf file and
9GB LDF file.

10 tables, Only one Schema (dbo), No indexes at all on any of the tables.

What I do:
I drop all the tables on the database, but the drive space remains the same(9gb) and I am left with just 1GB on both the drives.

How would I reclaim the space on D drive and what is the best way to do this without shrinking the mdf and without dropping the database?
Obviously without adding any disk space.

Shrinking log file would free up space but I am more concerned about getting the space back on the D drive which holds the MDF file.


texts: Can ProxySql balance load considering saturation of servers

texts:
I will be implementing a PXC Multi-master enviroment with ProxySql.
I need to know if this is doable and if it's a good approach:
Normal mode:
Node1 is primary for writes, N2 and N3 are backup_writes and reads.
Peak hours/days (traffic jups up to 500%):
N1 reaches threshold - e.g. connections, execution time, resource usage hit 70% of that server capability.
ProxySql splits load to N2 to help out. N2 reaches threshold - load is split to N3 as well.
Now they all take writes and reads. This goes on for a few hours, and load starts dropping.
N3 goes back to being read node. N2 follows this fashion when write load is manageable by N1 alone.
They go back to normal mode.
In normal mode i want to split logic to read/write mainly and i believe that having 1 node dedicated for writes is a good approach. The catch is in peak hours - these are the main reason behind master-master idea.
Did anyone test something similar and do you have any advice? Documentation that covers load balancing with ProxySql is talking about failover in case one node becomes unavailable. That's ok, but i need that logic to keep my nodes healthy as well, and i didn't find something that covers this kind of scenario.


texts: Query does not use index (according to EXPLAIN SELECT)

texts:
Can anyone give me an indication, why this query does not use the Key on the table 'gtg'):

+---+--------+------------------+-------+-----------------+---------+------+------+----+--------+---------------------------------+
| 1 | SIMPLE |      gtg |   ALL |         PRIMARY | NULL    | NULL | NULL |  2 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | hsv2 | range | PRIMARY,gtg| PRIMARY | 4    | NULL | 22 | 100.00 |  Using where; Using join buffer |
+---+--------+------------------+-------+-----------------+---------+------+------+----+--------+---------------------------------+

this is the query:

EXPLAIN
SELECT gtg.name,
       sum(h)
FROM hsv2
JOIN gtg ON gtg.id=hsv2.gtg
WHERE hsv2.id<1000
GROUP BY gtg.name
ORDER BY gtg.name

table definition (summary):

CREATE TABLE IF NOT EXISTS `hsv2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `gtg` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`,`pid`),
  KEY `pid` (`pid`),
  KEY `gtg` (`gtg`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
 PARTITION BY RANGE (pid)
(PARTITION hs_v2_0 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION hs_v2_1 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION hs_v2_39 VALUES LESS THAN (3800000) ENGINE = InnoDB,
 PARTITION hs_v2_40 VALUES LESS THAN (4000000) ENGINE = InnoDB)

gtg.id is the primary key.

any clues, I am lost. I thought the partitioning might have to do something with it, but but the gtg table is not partitioned, so....

mysql 5.5 on a standard debian 7


texts: Unexpected Row of Data when Joining two tables

texts:
I want to join both tables, and its giving me a much harder time and it should be, at least it doesn't look that complicated. Would be easier if table b would have a value for ID: 0,  but it doesn't.
So how can I get rid of the double rows in Table D

Table a

Article | ID  | Misc |
----------------------
x1      |0    |a     |
x2      |2    |b     |
x3      |1    |c     |

Table b

Article | ID  | Misc2|
----------------------
x2      | 1   | b-1      |
x2      | 2   | b-2      |
x3      | 1   | c-1      |
x3      | 2   | c-2      |

What I want is Table C

Article | ID  | Misc | Misc2 |
------------------------------
x1      | 0   | a    |       |
x2      | 1   | b    | b-1   |
x2      | 2   | b    | b-2   |
x3      | 1   | c    | c-1   |
x3      | 2   | c    | c-2   |

What I get is Table D

Article | ID  | Misc | Misc2 |
------------------------------
x1      | 0   | a    |       | correct
x2      | 0   | b    | b-1   | ID false
x2      | 1   | b    | b-1   | correct
x2      | 0   | b    | b-2   | ID false
x2      | 2   | b    | b-2   | correct
x3      | 0   | c    | c-1   | ID false
x3      | 1   | c    | c-2   | correct
x3      | 0   | c    | c-1   | ID false
x3      | 2   | c    | c-2   | correct

My query:

Select
  a.Article,
  a.ID,
  a.Misc,
  b.Misc2
From
  [table] a
    Inner Join
        [table] b
    On
        b.article =  a.article
        And (
            a.ID = 0
        OR  a.ID = b.ID


texts: shard migration failed with aborted at system.config.sessions collection!

texts:
A month ago, I have upgraded my mongodb cluster to version 4.4.1 from 3.6.
Then recently mongodb shard migration failed with error 'aborted'   as follows:
balancer:
Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
            2844 : Failed with error 'aborted', from mdbn1 to mdbn4
            2788 : Failed with error 'aborted', from mdbn1 to mdbn2
            2833 : Failed with error 'aborted', from mdbn1 to mdbn3

And I found it's caused from config.system.sessions collection as following log message
-- egrep -v '"s":"I"' /var/log/mongodb/mongod.log
{
    "t": {"$date": "2020-12-14T10:26:11.525-05:00" },
    "s": "W",
    "c": "SHARDING",
    "id": 23777,
    "ctx": "MoveChunk",
    "msg": "Error while doing moveChunk",
    "attr": {
        "error": "OperationFailed: Data transfer error: migrate failed: InvalidUUID: Cannot create collection config.system.sessions because we already have an identically named collection with UUID 587470bd-a60d-4f85-a56d-e31182c34d90, which differs from the donor's UUID 9a757a91-1335-4caf-8796-513271be534d. Manually drop the collection on this shard if it contains data from a previous incarnation of config.system.sessions"
    }
}

I have followed the above messages, tried to remove the system.sessions collection, but it failed because I am not authorized even I have loggeed in as a root.
I don't understand how to fix it.
If you have any idea please share with me.
mdbn1:PRIMARY> db.system.sessions.remove({})
WriteCommandError({
    "operationTime" : Timestamp(1607966914, 12),
    "ok" : 0,
    "errmsg" : "not authorized on config to execute command { delete: \"system.sessions\", ordered: true, lsid: { id: UUID(\"c26b3641-62ac-4496-91b2-1b8eb76594e0\") }, $clusterTime: { clusterTime: Timestamp(1607966903, 15), signature: { hash: BinData(0, 4B67FC70E037CB8F6C421CA759595A68617D0F42), keyId: 6864366270343544833 } }, $db: \"config\" }",
    "code" : 13,
    "codeName" : "Unauthorized",
    "$gleStats" : {
        "lastOpTime" : Timestamp(0, 0),
        "electionId" : ObjectId("7fffffff0000000000000042")
    },
    "lastCommittedOpTime" : Timestamp(1607966914, 12),
    "$configServerState" : {
        "opTime" : {
            "ts" : Timestamp(1607966914, 13),
            "t" : NumberLong(32)
        }
    },
    "$clusterTime" : {
        "clusterTime" : Timestamp(1607966917, 2),
        "signature" : {
            "hash" : BinData(0,"DQyhmJK4/XZVnIJv8Oj8lWg/PhE="),
            "keyId" : NumberLong("6864366270343544833")
        }
    }
})


texts: how to upload a file on a mysql server if access to temp folder is not present

texts:
I have a CSV file that i want to enter into a table in my database which is present on a remote server. But presently I don't have access to temp folder so I cannot copy my file on that folder. Is there a way I can upload file from any random folder onto the database?

This is my command in sql server:

load data local  infile 'C:/Users/trainee_aiyer/Downloads/cash_ord.csv' into table cm_ord fields terminated by ',' enclosed by '"' lines terminated by '\n';

and my file path is this:

C:\Users\trainee_aiyer\Downloads\cash_ord.csv

I switched '\' to '/' as the prior was causing syntax errors. but I get an error saying

ERROR 2 (HY000): File 'C:/Users/trainee_aiyer/cash_ord.csv' not found (Errcode: 2 "No such file or directory")


texts: Why all users excep sys and system can connect to oracle db after database deletion on multi database environment?

texts:
I have 2 database on one server.
I can connect to the database with normal users but cannot connect with sys and system users and invalid username/password occurs. before that I dropped one of the databases and during dropping that database, this error occurred:

  DIM-00014: cannot open the windows NT service control
  manager.O/S-Error:(OS 5) Access is denied

I check if I can connect sqlplus /nolog and conn / as sysdba and it did not worked and below error occurred:

  12560:  tns protocol adapter error

anybody help!


texts:
I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min.

For past couple of weeks it happens once in a week that it will stuck and keep running for hours. Stats maintenance is done daily for modified stats and automatic update stats on is ON

There is no change to SP neither too much of load being increased in that run time.

I recompiled that SP to see if the plan was bad due to parameter sniffing issue, but does not help.

Due to security constraints i am not able to share the plan or any table structure here.

I know without seeing plan and query its tough to comment, but need possible ideas on what else should i be checking here?


texts: Calculating Margin in CASE Statement and GROUP BY Clause

texts:
I think I am running into an error in how I have chosen to aggregate my columns in a group by statement.

I am working out Margin % and grouping into months using a case statement like so:

SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus <> 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]

So my equation ends up as: SUM( (Revenue - Cost) / Revenue )

Which in months with multiple transactions gives me the wrong value

I am sure my equation needs to be: SUM(Revenue - Cost) / SUM(Revenue)

But I cannot format my case statement like that. How would you work out this sort  of equation?

Thanks,


texts: Software for working with SQL Server databases in Windows Server 2008

texts: We have an ASP .NET project that uses SQL Server that we host on a Windows Server 2008 machine. Where does Microsoft keeps its documentation for the tools that can be used for administering SQL Server databases? I'm not sure where to start looking for the software to manage our db.


texts: JOIN require a roll up of data and a sort

texts:
I have table people, where one column is their name and the other is a reference to the Class table.
So People table:

PersonId ClassId  Name
5        1        John
6        2        Mary
7        1        Billy
8        2        Anne

and a class table, where:

ClassId  Class
1        English
2        French

I want to do a SQL query that will give me:

Class    Names
English  Billy, John
French   Anne, Mary

As in, the names are rolled up and they are sorted alphabetically.

So a simple join:

SELECT C.CLASS, N.NAME as NAMES from Class C inner join Names N on C.classId = N.classID

would give me:

English  Billy
English  John
French   Anne
French   Mary

But how would I roll up the names and in the roll up ensure they are alphabetical?


texts: Optimize query with a subquery (index is not used)

texts:
I have following query:

SELECT
`status`, `is_client_coordinator`, id, first_name, last_name
FROM `users` `t`
WHERE t.status!="CLOSED"
and t.is_client_coordinator
or t.id in (select client_coordinator_id from projects where status not in ("DELETED", "ARCHIVED"))
ORDER BY concat(first_name, last_name)

The explain gives this:

Individually queries take less than 0.1 seconds (first one 0.04 seconds and the subquery 0.07 seconds). Put together it takes more than a second to perform the query.
Both status columns are the type of ENUM.
What am I doing wrong here? Any advice about how to optimize the query?


texts: What happens when the index size of my Mysql instance exceeds the innodb_buffer_pool_size?

texts:
I am using Amazon's RDS db.m1.small.

innodb_buffer_pool_size is set to 1125.00 MB.

Current index size is 1110.00 MB and growing slowly but steadily.

I do not need more hard drive space.

I want to know if it is necessary to upgrade to a larger instance (db.m1.medium) in order to keep my app working smoothly. Is it important that the index fits into memory? What should be my next steps?


texts:
I am writing a script to quickly check the indexes in a given database. I think I have the script written ok but to remove duplicate rows I have done alot of SUM functions and I am unsure whether this is correct or not:

SELECT      OBJECT_NAME(i.object_id)                                                                                AS 'Table Name'
,           COALESCE(i.name, '')                                                                                AS 'Index Name'
,           i.type_desc                                                                                AS 'Index Type'
,           i.is_primary_key                                                                                AS 'Primary Key'
,           i.is_disabled                                                                                AS 'Index Disabled'
,           ROUND(CONVERT(FLOAT,SUM(user_seeks)) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_scans),0),1))),2)                                          AS 'Seeks Per Scan'
,           ROUND((CONVERT(FLOAT,SUM(user_seeks) +SUM(user_scans) + SUM(user_lookups))) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_updates),0),1))),2) AS 'Reads Per Write'
,           (SUM(user_seeks) + SUM(user_scans) + SUM(user_lookups))                                                                                AS 'Reads'
,           SUM(user_updates)                                                                                AS 'Writes'
,           SUM(user_seeks)                                                                                AS 'Seeks'
,           SUM(user_scans)                                                                                AS 'Scan'
,           ROUND(AVG(ps.avg_fragmentation_in_percent),2)                                                                                AS 'Avg Fragmentation %'
,           NULLIF((SUM(leaf_insert_count) + SUM(leaf_update_count) + SUM(leaf_delete_count)),0) / NULLIF(SUM(leaf_allocation_count),0)             AS 'Leaf Page Splits Per Write'
,           NULLIF((SUM(nonleaf_insert_count) + SUM(nonleaf_update_count) + SUM(nonleaf_delete_count)),0) / NULLIF(SUM(nonleaf_allocation_count),0) AS 'Non-Leaf Page Splits Per Write'
,           CAST (100.0 * SUM(row_lock_wait_count) / (1 + SUM(row_lock_count)) AS NUMERIC(15,2))                                                    AS 'Block %'
,           SUM(row_lock_wait_in_ms)                                                                                AS 'Row Lock Wait (ms)'
,           CAST(1.0 * SUM(row_lock_wait_in_ms) / (1 + SUM(row_lock_wait_count)) AS NUMERIC(15,2))                                                  AS 'Average Row Lock Wait'
FROM sys.indexes i
LEFT JOIN sys.objects o ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) s ON o.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_index_usage_stats u ON s.object_id = u.object_id AND u.index_id = s.index_id
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id
WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
AND NOT OBJECT_NAME(i.object_id) = 'sysdiagrams' -- exclude sysdiagrams
--AND i.is_primary_key = 0
GROUP BY OBJECT_NAME(i.object_id), COALESCE(i.name, ''),i.type_desc,i.is_primary_key,i.is_disabled
ORDER BY OBJECT_NAME(i.object_id), COALESCE(i.name, '')

Is this ok? Or is SUMing the rows bad?

Also, is there any information that you think would be helpful for finding indexes that aren't used/need re-indexing etc


texts: How should I (re) index the table while keeping the transaction log small?

texts:
I have a 170 GB table in a database. It has 70% fragmentation on its index (index has multiple columns in it). The last time I tried DBCC REINDEX the transaction log blew up in the hundreds of GBs.

Should I drop the index and recreate it or do a DBCC REINDEX?
What is the best way to do either one?
How can I do either one while keeping the transaction log as small as possible?


texts: Import indexes from MongoDB v2.4 to MonDB v3.6.4

texts:
I have migrated the collection.bson file from MongoDB 2.4 to MongoDB 3.6.4 as collection.json.

But their is 1 more file collection.metadata.json in MongoDB 2.4.

How can I import this to MongoDB 3.6.4?

content of collection.metadata.json is as below;

{
  "options": {
    "create": "collection"
  },
  "indexes": [
    {
      "v": 1,
      "name": "_id_",
      "key": {
        "_id": 1
      },
      "ns": "emgda.collection"
    },
    {
      "v": 1,
      "name": "customerId_1_elementId_1_siteId_1_createdTimestamp_1",
      "key": {
        "customerId": 1,
        "elementId": 1,
        "siteId": 1,
        "createdTimestamp": 1
      },
      "ns": "emgda.collection",
      "background": true,
      "sparse": true
    }
  ]
}

  mongorestore version is as below,

emgda@ubuntu:~/trial$ mongorestore --version
mongorestore version: r3.6.4
git version: d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856
Go version: go1.7
   os: linux
   arch: amd64
   compiler: gc
OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016

  NOTE: When I use mongoretore -d emgda auditing.bson, I get below error for the collection.

checking for collection data in auditing.bson
reading metadata for emgda.auditing from auditing.metadata.json
Failed: emgda.auditing: error creating collection emgda.auditing: error running create command: BSON field 'OperationSessionInfo.create' is a duplicate field


texts: How to disable ChunkManager logging for MongoDB? mongoconfig.log gets huge

texts:
I'm using MongoDB 3.4 for saving other systems logs and mongoconfig.log is getting too much verbosity. It's a sharded clusted, if that helps.

I checked the default mongo log verbosity and is set at default (0). But I believe this is another setting not found under /etc/mongod.conf

2017-06-13T02:54:44.868-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210965 based on: (empty)
2017-06-13T02:54:44.915-0400 I SHARDING [Balancer] ChunkManager load took 46 ms and found version 2392|1||58ade4d8189e1651990a98d2
2017-06-13T02:54:44.920-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210966 based on: (empty)
2017-06-13T02:54:44.921-0400 I SHARDING [Balancer] ChunkManager load took 0 ms and found version 3|10||58adf0e5189e1651990d9af8
2017-06-13T02:54:44.921-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210967 based on: (empty)
2017-06-13T02:54:45.026-0400 I SHARDING [Balancer] ChunkManager load took 104 ms and found version 4385|1||58adf0ed189e1651990d9c89
2017-06-13T02:54:45.035-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210968 based on: (empty)
2017-06-13T02:54:45.127-0400 I SHARDING [Balancer] ChunkManager load took 92 ms and found version 4258|1||58adf0f4189e1651990d9dd4
2017-06-13T02:54:45.136-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210969 based on: (empty)
2017-06-13T02:54:45.162-0400 I SHARDING [Balancer] ChunkManager load took 26 ms and found version 1349|1||58adf0f9189e1651990d9f26

Getting log components information returns this:

db.getLogComponents()
{
    "verbosity" : 0,
    "accessControl" : {
        "verbosity" : -1
    },
    "command" : {
        "verbosity" : -1
    },
    "control" : {
        "verbosity" : -1
    },
    "executor" : {
        "verbosity" : -1
    },
    "geo" : {
        "verbosity" : -1
    },
    "index" : {
        "verbosity" : -1
    },
    "network" : {
        "verbosity" : -1,
        "asio" : {
            "verbosity" : -1
        },
        "bridge" : {
            "verbosity" : -1
        }
    },
    "query" : {
        "verbosity" : -1
    },
    "replication" : {
        "verbosity" : -1
    },
    "sharding" : {
        "verbosity" : -1
    },
    "storage" : {
        "verbosity" : -1,
        "journal" : {
            "verbosity" : -1
        }
    },
    "write" : {
        "verbosity" : -1
    },
    "ftdc" : {
        "verbosity" : -1
    },
    "tracking" : {
        "verbosity" : -1
    }
}


texts: How should I delete branch from adjacency list in SQL Server?

texts:
Given a basic acyclic adjacency list like this:

What is the most effective way to handle deletions of branches?

My current approach uses a recursive CTE to find all descendant nodes and their depths (n). Then I iterate (ugh) over those depths in reverse and issue n deletes.

CREATE TABLE [dbo].[TreeNode](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentNodeId] [int] NULL,
    [Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TreeNode] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TreeNode]  WITH CHECK ADD  CONSTRAINT [FK_TreeNode_TreeNode] FOREIGN KEY([ParentNodeId])
REFERENCES [dbo].[TreeNode] ([Id])
GO

ALTER TABLE [dbo].[TreeNode]  WITH CHECK ADD  CONSTRAINT [CK_TreeNode_Id_ParentNodeId] CHECK  (([Id]<>[ParentNodeId]))
GO

ON DELETE CASCADE won't work here because it isn't allowed in self-referencing FKs


texts: Using EXCEPT before WITH RECURSIVE in SQLite 3

texts:
Is there anyway to do this? I'm trying to do something like this:

SELECT x
FROM table
EXCEPT
WITH RECURSIVE ...();


texts: Estimating IO Requirements for Bursty Usage

texts:
We have an application that queries a SQL database periodically throughout the day.  There are periods of zero or only light activity, interspersed with individual requests for relatively large amounts of data.  When those requests come in, the primary objective is to deliver the data quickly, and the secondary objective is to do that cost-effectively.  Due to the nature of the application, it is fairly unlikely that the data/indexes will have been cached in RAM from the previous query (different users, working on different portions of the data).

For a system that experiences relatively steady usage, I have heard the rule of thumb to observe the disk queue length and keep that number relatively small.  This will specifically run in AWS, where I have seen the rule of thumb that a disk queue length of 1 per 100 IOPS is reasonable.

How can I estimate the IO requirements for such a system?  Is the disk queue length a reliable indicator when dealing with individual, bursty queries?  Are there other metrics that I should consider?


texts: PostgreSQL 9.1 Hot Backup Error: the database system is starting up

texts:
I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue.  After restarting Postgres on the slave server the pgstartup log file and the daily log file under the pg_log directory reads with no errors. However, when I try to enter into the database using the psql command, I get the error:

FATAL: the database system is starting up.

The recovery.conf file also does not turn to recovery.done.  I have extensively researched this error and consistently find the same response: the database has not been cleanly shut down before I tried to restart Postgres. The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands.  After I receive this error, I kill all processes and again try to restart the database and still receive the same error.  I am at a loss of where to go from here and how to fix this issue.  Below is the exact process that I have done to complete the hot backup.

Master Server Configurations:

pg_hba.conf, added the line:

host replication postgres IPAddressOfSlaveServer trust

postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5
listen_address = '*'
port = 5432
max_wal_senders = 5
wal_keep_segments = 32

Slave Server Configurations:

postgresql.conf:

hot_standby = on

recovery.conf:

standby_mode = on
primary_conninfo = host=IPAddressOfMasterServer
port = 5432
user = postgres
restore_command = 'cp /var/lib/pgsql/9.1/data/pg_xlog/%f "%p"'

After configuring both servers

I change to the postgres user on the master server and run the commands:

psql -c "Select pg_start_backup('label',true);";
rsync -a -v -e ssh /var/lib/pgsql/9.1/data slave:/var/lib/pgsql/9.1/data \
        --exclude postmaster.pid
pgsql -c "select pg_stop_backup();";

After syncing database with slave server

I restart the slave server and the start up does not fail. The pgstartup.log reads:

Success. You can now start the database server using:

    /usr/pgsql-9.1/bin/postgres -D /var/lib/pgsql/9.1/data
or
    /usr/pgsql/9.1/bin/pg_ctl -D /var/lib/pgsql/9.1/data -l logfile start

the current day log file, postgresql-Thu.log, reads:

Log: shutting down
Log: Database system is shut down
Log: database system was shut down in recovery at 2012-4-10
Log: entering standby mode
Log: restored log file "logFileName" from archive
Log: consistent recovery state reached at 0/BF0000B0
Log: redo starts at 0/BF000020
Log: restored log file "logFileName" from archive
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: streaming replication successfully connected to primary

I researched unexpected pageaddr and from the postgres archives, it is my understanding that it is quite normal and one of the expected ways to detect end-of-WAL.

Any advice would be greatly appreciated.


texts:
I was looking for a solution in which my application, one which I can configure only one Listener param, if we can address multiple Availability Groups or not.
I have heard of using multiple listeners for one or multiple AGs, but is it possible to use a single listener and address multiple AGs?


texts: MSSQL - Query efficiency to many filters

texts:
I'm making an ASP.NET website which users can select more than 20 different filters at the same time. Something like 'Product Type', 'Year', 'State-of-Usage', 'Color', 'Size' and so forth.

Many of these items are related to other tables. So, my query makes a 'SELECT' using WHERE and 'INNER JOIN' to 12 other tables.

The problem resides in parameters that can be present or not.
I mean, depending of the user choice, some parameters will be NULL or not and I can't utilize them in my query (like using ISNULL()) because I'm affraid of return a wrong result caused by this.

So, my solution is build the query string depending of the parameters received , I mean, the query will consider only those which have real values.

declare @SEARCH_QUERY nvarchar(max);

set @SEARCH_QUERY = 'select AcervoID, Categoria, Fabricante, Personagem, Estado,
                        IIF(LEN(Description) > 120, LEFT(Description,120) + ''...'', Description) [mDescription],
                        Value, Availability, ActiveLink, Title,
                        RIGHT([thumb], CHARINDEX(''\'', REVERSE([thumb])) -1) [Image],
                        E.UsoID, E.FontAwesome
                        from Acervo A
                        inner join Categorias C on C.CatID = A.Categoria
                        inner join Fabricantes F on F.FabID = A.Fabricante
                        inner join Personagem P on P.PersID = A.Personagem
                        inner join EstadoUso E on E.UsoID = A.Estado
                        where A.Ativo = ''true'' and A.Disponiveis > 0'

  if @m_Cat >-1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.Categoria = ' + convert(nvarchar(4),@m_Cat)
  if @m_Fab >-1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.fabricante = ' + convert(nvarchar(4),@m_Fab)
  if @m_Pers > -1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.Personagem = ' + convert(nvarchar(4),@m_Pers)
  if @m_EU is not null
    set @SEARCH_QUERY = @SEARCH_QUERY + @m_EU

exec sp_executeSQL @SEARCH_QUERY

Is there a best way to do it?

Thanks in advance.


texts: SAN for SQL Server: One large spindle set for all loads, or discrete by load?

texts:
SQL Server 2014, Standard Edition (running under vmware, fwiw).
Adding a Dell MD3420 DAS box.

In the old school, you would set up discrete spindle sets for MDF, LDF, TEMPDB, BACKUP.

E.g.

MDF:  Qty 6: 300GB-15K, RAID 10  // 900GB storage
LDF:  Qty 4: 150GB-15K, RAID 10  // smaller data footprint, spindles to increase IOPS
TEMPDB: Qty 4: 150GB-15K, RAID 10  // smaller data footprint, spindles to increase IOPS
BACKUP: Qty 2: 2TB, RAID 1

Use Case:
This system has 5 databases, and is OLTP.

Nowadays, I am being told: Just set up Qty 10: 600GB-15K drives, and put MDF, LDF, TEMPDB and BACKUP all on there. The overall higher performance of the larger spindle set more than makes up for the conention writing to LDF and BACKUP concurrently during backups.

a) Is this new way the right way?
b) LDF is supposed to be discrete relative to MDF... I feel funny!
c) I really feel backups should be their own spindle set (for a bunch of reasons)

The winner is ?  SQL Tradition? Or the Dell Storage Architect?


texts: I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?

texts:
The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-fire way to lose our customers trust. We make heavy use of the entity pattern on the client.  This requires us to get collections of data and store them as collections on the client. My thinking when I was designing the database was that if I had the account id on each table, that would make getting all of the data easier without returning data from other account, with fewer or more performant queries. I realize that there are other ways to handle this, but we were on a very short deadline and had to build a full app with ~50 tables for a beta launch in 3 Months.  That being said, we also have many queries that heavily use joins and group by methods to prevent (n+1) trips to the database.  Is it simply that analyzing lots of data is something that requires a larger database? The big problem is that we only have 45 Active customers currently.  The app is fast and feels great, we are just pushing the limits of the memory of the database.

This is the pattern that I did.  I know it's not normalized for the keys but everything I have read did not seem like it would cause this issue.  But I'm not a database specialist and would appreciate any advice.

Account Table:
id
first_name
...etc

Record Table:
id
account_id
address
...etc

Analysis Table:
id
account_id
record_id
expected_return_on_investment
...etc

Comps Table:
id
account_id
record_id
analysis_id
cost
...etc


texts: How to restore only functions in windows

texts:
I have taken function backup using following command:

pg_dump -h localhost -u username -fc  -f db_dump dbname

How to list the backup functions and how can I restore those functions to other database in windows?

Version is PostgreSQL 9.6.8


texts: Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables?

texts:
My case:

I have many organisms (for example human, mouse and etc.) and a few models for each one. All models are independent from each other and I will never query them together.

Normally I will do it like that:

PK (organism, model) is FK in one big table with models rows. But I do not need have them in one place, because data under different (organism, model) are independent, so I will always query a sub set of that big table base at FK (organism, model).

My idea is use PK (organism, model) as a TABLE_NAMEs for many separated tables, each one contains independent data.
I am not proficient in DB, and I can not find how should I structured data like this. Can you help me? Is my idea OK? Should I solve it in other way? Where can I find any clues?

summarizing:

PK ---> TABLE_NAME


texts: How can I do an incremental `mysqldump` without dropping table?

texts:
I'd like to fetch the last 100 rows of a table via a mysqldump, but I already have the rest of the table. How can I only fetch the last 100 rows for a mysqldump and then replace (or overwrite) any duplicates I may have?

db1 Incremental Export

mysqldump -umy_user my_db1 my_table --single-transaction --replace --where="id > 900" | pigz > my_db1-my_table.sql.gz

db2 Incremental Import

pv my_db1-my_table.sql.gz | gunzip | mysql -umy_user my_db2

Unfortunately the mysqldump above creates a drop table my_table query. How can I skip this?

What I want to happen...

Say db2 has 950 records. And db1 has 1000. I want records 901-1000 from db1 on db2, as well as the 1-900 already on db2 (just leave them be). Replacing/Overwriting records 901-950 on db2 is preferred.

What Currently Happens...

After running the above, db2 only contains records 901-1000, and is now missing 1-900 (due to drop table).


texts: Why are multivalued dependencies named that way?

texts: I'm doing a relational design course on Stanford open courseware. I understand why functional dependencies are named that way - A->B means B is specified when A is specified, or in other words, the value of B functionally depends on the value of A. But I'm not able to think of a simple reason why multivalued dependencies are named that way. Understanding this would make it much easier to remember and use the concept.


texts:
I had problem with connection to root@localhost,because somebody used PC before me in company and set same password!

In terminal when I write mysql status,got some notification about how service is died or something like that, and on sudo service mysql start they can't find service, but mysql version i got some result MariaDB(some number)!

I tried a lot stuff from google and nothing, can me tell how to delete all stuff because i don't have some important database on localhost and install mysql again, from terminal.


texts: Security for Oracle linked server from SQL Server

texts:
I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data from linked server.

So I tried mapping SQL Security User to Oracle user/pass by inserting it in "Local server login to remote server login mappings:" area then "For a login not defined in the list above, connection will:" set to "Not be made".

When I click OK, I get:

Access to the remote server is denied because no login-mapping exists

Is it not possible to map SQL login to a Oracle login? Is there any way to get this to work?


texts: Any recommendation on my.cnf on aws.large instance?

texts:
Any best recommendation variables for my.cnf on percona server on a high 256GB RAM and 32 CPU with 4000 IOPS?

How about below ones?

> innodb_buffer_pool_size=180G
> innodb_buffer_pool_instances=16
> innodb_flush_neighbors=0

any other recommendations on Mysql percona 5.6?


texts: MongoDB Data loss following machine PowerOff with writeConcernMajorityJournalDefault true

texts:
Background:

I'm using mongodb 4.2.0
I have deployed a mongo cluster which contain 5 configs, 3 querys and 3  - shards. Each shard consist of 4 replicas and 1 arbiter.
All members are set on VMs.
ReplicaSets writeConcernMajorityJournalDefault flag is true.
Test conducted with pymongo

The Test:

Before implementing the cluster on the production environment, I've conducted several "stress tests". I have created a simple script that performs many inserts to the cluster and returns the amount of successful inserts.

When I run and stop the script everything is just fine. The number of inserts_count is identical to the count of documents in the collection.

BUT, When I run the script and then PowerOff the Primary member, I'm facing a hitch. My script's insert_count is bigger (10-20) than the count of documents in my collection. I assume that I'm losing data.

I got successful insert acknowledge even though my replicaSet is set writeConcernMajorityJournalDefault true.

Raising the primary doesn't help to retrieve the lost data.

I think the data was still in memory!

Conclusion:

I believe that there is some malfunction with the journaling setting.

Please help me understand what am I doing wrong.

P.S:

I tried insert with {w: majority, j: true, wtimeout: 5000} parameters.

Same results :/


texts: SQLServer Query Store not saving failed queries run by SP_ExecuteSQL

texts:
We are on SQL Server 2016
Query Store is activated on the database in question.
We have had some timeout problems reported on a nightly batchjob, and we have located the query in question.
The query is from a .Net application, generated by some framework
I have also found the query in Query Store.
But if i use 'tracked queries', all executions are reported as good, no timeouts (and no errors)
And if i query the QueryStore for that particular queryID, all executions has result 'Regular'
If i setup an extended events session to catch timeouts, the timeouts of the query is indeed collected and i can see that the actual call of the query is done with
EXEC SP_EXECUTESQL N'querystring'
does this somehow cheat the querystore into thinking it is ok, no matter what the result ?
regards
Peter


texts:
I am trying to import a postgresql database table sent to me in phpPgAdmin, I am following the instruction from this import database

But after doing so, several error appear, I also tried to import the database using cmd but the same error continues to persist

DROP TABLE
postgresql.sql:33: ERROR: relation "accessories_id_seq" does not exist
postgresql.sql:34: ERROR: relation "public.accessories" does not exist
BEGIN
postgresql.sql:40: ERROR: relation "public.accessories" does not exist
LINE 1: INSERT INTO "public"."accessories" VALUES ('1', 'Vario V8', ...
^
postgresql.sql:41: ERROR: current transaction is aborted, commands ignored until end of transaction block

I researched the problem here in stackoverflow but people who had the same error are making new database tables and and thus most of the errors comes from mixed-case spelling, I'm just pulling data from a database sent to me.
Any answers would be greatly appreciated.

In cmd I used this command

"%PROGRAMFILES%/Postgresql/9.3/bin/psql.exe" -v ON_ERROR_STOP=1 -U postgres -d postgres -a -f C:/Sites/xmastool/postgresql.sql

The file is an SQL file

After removing all the drop statements this error now appears

postgresql.sql:33: ERROR: syntax error at or near ""
LINE 1: /*
^
postgresql.sql:34: ERROR: relation "public.accessories" does not exist
BEGIN
postgresql.sql:40: ERROR: relation "public.accessories" does not exist
LINE 1: INSERT INTO "public"."accessories" VALUES ('1', 'Vario V8', ...
^

This is the first lines of the code

/*
 Navicat Premium Data Transfer

 Source Server         :
 Source Server Type    : PostgreSQL
 Source Server Version : 90207
 Source Host           :
 Source Database       : foo
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 90207
 File Encoding         : utf-8

 Date: 11/19/2014 15:42:40 PM
*/

-- ----------------------------
--  Table structure for accessories
-- ----------------------------

CREATE TABLE "public"."accessories" (
    "id" int4 NOT NULL DEFAULT nextval('accessories_id_seq'::regclass),
    "accessory_name" varchar(255) DEFAULT ''::character varying COLLATE "default",
    "accessory_description" text COLLATE "default",
    "accessory_size" varchar(255) DEFAULT ''::character varying COLLATE "default",
    "created_at" timestamp(6) NULL,
    "updated_at" timestamp(6) NULL,
    "price" varchar(255) DEFAULT '0.00'::character varying COLLATE "default",
    "active" int4 DEFAULT 1,
    "img_url" varchar(255) DEFAULT ''::character varying COLLATE "default"
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."accessories" OWNER TO "gptpgsxlzwywhc";

-- ----------------------------
--  Records of accessories
-- ----------------------------
BEGIN;
INSERT INTO "public"."accessories" VALUES ('');


texts: Have several rows, or one single row with a huge comma separated data?

texts:
I have many workers processes working at the same time. They grab an ID from a list called available_ids, work on that ID, and put that ID on a list, called used_ids.

The list available_ids can have the same ID several times, so when a worker pulls an ID from it, it has to check if that ID is already in used_ids. If it is, it skips that ID as there's no need to work on it anymore.

This is all file based, and I'd like to transfer the lists over to a mysql database.

My question is, should I have one table called used_ids, and add each used ID to it as rows (one ID per row), or should I have one single row, with all my IDs, comma separated?

There are thousands of IDs. My fear is, since my workers have to check every single ID before doing their task, by running through sereval rows, wouldn't there be a big overhead, slowing down everything?

Thank you.


texts: Does Sql Server maintain the free space defined by fillfactor?

texts:
I'm new to concept of what fillfactor is and why it is used.
So far, reading this article, I've learned that by setting the fillfactor configuration of an index, we specify how much space on each page should remain free, so that whenever a new record needs to be added to a page, there will be some free space for it.
My question is that:

Is that free space on each page, intended to remain free? I can guess that, that free space is used when an insert happens, but later the database engine, moves data, so that again we have some free space in each page. Is this what happens in a DBMS?


texts:
Can two tables have the same primary keys?

For instance:


texts: Where are SQL Server catalog views persisted?

texts:
It seems that sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere?
Thanks


texts: What to do when facts change frequently in a star schema for data warehouse?

texts:
I have a Quotation Management system that I built as a CRUD web app.

Over time, I realized I need to improve it so that management can have their reports.

Hence I studied the Kimball method of data warehousing.

I am now familiar with the concept of Dimensional and Fact tables.

On paper and pen, I have identified Quotation Line Item as the Fact table.

My issue is sometimes, the Sales Rep and the Customer have frequent back and forth regarding the Line Items so the line items will change quantities frequently or even removed.

I know that fact tables not supposed to have zero rows according to the Kimball book.

My question is how then do I handle such changes in the fact table?

According to a source, I am supposed to continuously update the fact table.

Please advise.


texts:
Like other RDBMS, view meta data is stored at view creation time.

From CREATE VIEW in the MySQL docs

  The view definition is “frozen” at creation time, so changes to the underlying tables afterward [sic] do not affect the view definition.

I can't see a way to refresh this view meta data cleanly, unlike other RDBMS:

SQL Server: EXEC sp_refreshview 'MyView'
Oracle: ALTER VIEW MyView COMPILE

The system here appears to be "do this manually" with DROP/CREATE which isn't my style. The usual crowd who do this manual process are on leave so it falls to me this week.

I don't have a minion to nominate either: so any solutions please...


texts: Can Azure Backup Server backup databases participating in Log Shipping?

texts:
We have a number of log-shipped SQL 2017 databases which we wish to start backing up using Microsoft Azure Backup Server (locally and to the cloud) on a 15 minute schedule.

Does anyone know if it is possible for Azure Backup Server to hook into the log backups made by SQL Server log shipping?


texts: How to optimize inner join query on one large table and one small table

texts:
I have table A with 15M records and table B with 5k records. I need to perform an inner join on both but the query time is considerably high.

explain analyze
SELECT distinct(a.student_id), b.student_name, a.class_year
FROM table_a a
INNER JOIN table_b b on a.student_id = b.student_id;

Explain Plan

                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3628096.85..3779293.37 rows=11780855 width=50) (actual time=35421.004..50690.702 rows=5078 loops=1)
   ->  Sort  (cost=3628096.85..3665895.98 rows=15119652 width=50) (actual time=35421.002..46385.451 rows=14264755 loops=1)
         Sort Key: a.student_id, b.student_name, a.class_year
         Sort Method: external merge  Disk: 890528kB
         ->  Hash Join  (cost=242.20..1308298.78 rows=15119652 width=50) (actual time=3.877..22332.795 rows=14264755 loops=1)
               Hash Cond: ((a.student_id)::text = (b.student_id)::text)
               ->  Seq Scan on table_a a  (cost=0.00..1268336.52 rows=15119652 width=25) (actual time=0.035..6168.042 rows=15119652 loops=1)
               ->  Hash  (cost=174.31..174.31 rows=5431 width=45) (actual time=3.822..3.822 rows=5431 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 483kB
                     ->  Seq Scan on table_b b  (cost=0.00..174.31 rows=5431 width=45) (actual time=0.008..1.886 rows=5431 loops=1)
 Planning time: 2.386 ms
 Execution time: 50822.593 ms
(12 rows)

I have an index on table_a for student_id

"student_id" btree (student_id)