Indexes: integer vs string performance if the number of nodes is the same I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model.

I am currently deciding whether to use an integer type or simply use a typical string type (e.g. character varying(255), which is the default in Rails) for the columns, as I'm not sure what the performance difference will be on the index.

These columns are enums. They have a fixed size for the amount of possible values they can have. Most enum lengths do not exceed 5, meaning the index would be more or less fixed throughout the lifetime of the application; thus, the integer and string indexes would be identical in the number of nodes.

However, the string that would be indexed could be around 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per character, then this holds). I don't know how database engines do index look-ups, but if it needs to "scan" the string until it matches exactly, then in essence that means that the string lookup would be 5x slower than an integer lookup; the "scan" until match for the integer lookup would be 4 bytes instead of 20. This is what I'm imagining:

The lookup value is (integer) 4:

  scanning............................ FOUND | getting records...
  |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|

The lookup value is (string) "some_val" (8 bytes):

  scanning.....................................................................................
  FOUND | getting records...
  |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|

I hope that that makes sense. Basically, because the integer takes up less space, it can be "matched on" faster than its string counterpart. Perhaps this is a completely wrong guess, but I'm no expert, so that's why I'm asking you guys! I suppose that this answer I just found seems to support my hypothesis, but I want to be sure.

The number of possible values in the column wouldn't change in using either one, so the index itself would not change (unless I added a new value to the enum). In this case, would there be a performance difference in using integer or varchar(255), or does using an integer type make more sense?

The reason I am asking is that Rails' enum type maps integers to string keys, but they aren't meant to be user-facing columns. Essentially, you can't do verification that the enum value is a valid one, because an invalid value will cause an ArgumentError before any validations can be run. Using a string type would allow validations, but if there's a performance cost I'd rather just hack away around the validation problem.

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows.

  string ... 20 characters long, which in memory is roughly 5x that of
  the integer (if an integer is 4 bytes, and the strings are pure ASCII
  at 1 byte per character, then this holds)

To be precise, character types (text or varchar) occupy exactly 21 bytes for 20 ASCII characters on disk and 23 bytes in RAM. Detailed assessment:

What is the overhead for varchar(n)?

Also important: COLLATION rules can make sorting character data more expensive - unlike numeric data types:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Index size is probably responsible for the lion share of performance difference in most cases. Consider the overhead per index tuple (basically the same as for a table): 4 bytes for the item identifier and 8 bytes for the index tuple header. So the index tuple for integer would amount to 20 bytes (including 4 bytes of alignment padding) and for varchar(20) with 20 ASCII characters it would be 36 bytes (also incl. padding). Details:

Configuring PostgreSQL for read performance

All the theory aside: it's best to just test:

Measure the size of a PostgreSQL table row

Postgres 9.5 introduced an optimization for sorting long strings of character data (key word "abbreviated keys"). But a bug in some C library functions on Linux forced the project to disable the feature for non-C collations in Postgres 9.5.2. Details in the release notes.

However, if you actually use Postgres enum types, most of these considerations are irrelevant, since those are implemented with integer values internally anyway. The manual:

  An enum value occupies four bytes on disk.

Aside: varchar(255) used to make sense for early versions of SQL Server, which could use a more efficient data type internally up to the limit of 255 characters. But the odd length restriction of 255 characters has no special meaning in Postgres at all.


Any and All sql clause select id
from master_tabe
where id = ALL (select id from user_table where name = 'Nikhil')

select id
from master_tabe
where id = ANY (select id from user_table where name = 'Nikhil')

Above mentioned queries are returning the same resultset. According to my knowledge there should be different resultset as the user_table has only one record related to name Nikhil. So according to this Any clause must return 1 row whereas All must return 0 records. Whereas when I add one more record in user_table than All clause statement is returning 0 rows and Any clause is returning only 1 row.

Can someone look into this?

The result you've got return seems very normal to me.

ANY will return TRUE when at least one value returned by the subquery compares positively.
Logically, you could compare this to using multiple OR statements, one OR comparison for each value returned by the subquery.

ALL on the other hand requires each value returned by the subquery to compare positively.
Logically, you could compare this to using multiple AND statements, one AND statement for each value returned by the subquery.

You stated that the subquery originally returned a single record. Therefore it didn't matter whether you used ALL or ANY. As soon as you added another record, there is a difference though. At that moment there are multiple values (ID's) in the subquery to compare to and they will logically be evaluated as OR/AND statements

Example

CREATE TABLE dbo.Tbl (ID int)
GO

INSERT dbo.Tbl VALUES (1),(2),(3)
GO

CREATE TABLE dbo.SubTbl (ID int)
GO

INSERT dbo.SubTbl VALUES (1),(2)
GO

SELECT ID
FROM dbo.Tbl
WHERE ID = ANY(SELECT ID FROM dbo.SubTbl)
GO

-- Has the same result as ANY:
SELECT ID
FROM dbo.Tbl
WHERE ID = 1 OR ID = 2
GO

SELECT ID
FROM dbo.Tbl
WHERE ID = ALL(SELECT ID FROM dbo.SubTbl)
GO

-- Has the same result as ALL:
SELECT ID
FROM dbo.Tbl
WHERE ID = 1 AND ID = 2
GO


Create index on 106 million row table I have an application that is installed world wide that uses SQL versions from SQL Server 2008 Express to SQL Server 2017 Enterprise edition.

I have tried several ways to create an index (3 columns with an include of 3 columns) on a big table where the Database is usually in simple recovery model but can be as Full recovery model... Hardware can vary drastically from one customer top another:

Alter table create index...
Create a tmp table with the new
    index and copy the data with an "insert into" followed with a drop
    table of the original table and a rename of the tmp table to replace
    the original one.
Create a tmp table with the new index and use a
    PowerShell script to copy the data followed by a drop table of the
    original table and a rename of the tmp table to replace the original
    one.

The quickest of the 3 was the 1st option (the traditional way of creating a non-clustered index) which took 1h15m for a bit over 106 million records (the table has 200 columns... not ideal but it's what I'm working with) the other options took triple the time and more)

Unfortunately, I can't use the online feature since there are a variety of SQL Server versions and the online is only good for enterprise edition.

An hour and 15 minutes in my lab is too long and I would like to try to find another way to bring that down, specially that in one of our next application updates, we will need to create 23 indexes... and I haven't seen an update that takes 5-6 hours nor will customers want to wait 5-6 hours for an update of a software.

Also, the update will require no activity on the database during the update.

All I'm looking for are ideas I can try to get my indexes created in a reasonable amount of time.  No code! just need theoretical ideas.

Any idea would be greatly appreciated.

I'm leaving this as an answer, even though it's mostly an Extended Comment®.

You don't mention the index definitions, or the types of columns you're trying to index. You say the table is 200 columns, but unless you're creating a clustered index, that shouldn't matter. Unless you're trying to create a nonclustered index on 200 columns. If so, please reconsider.

Given that you're doing this across many different versions, it's likely that they're all on different hardware as well. In the case of creating indexes, hardware and existing indexes will have a dramatic impact on how long it takes to complete. It's not like you can tune the query behind index creation. Likewise, the ability for Enterprise Edition to parallelize index creation can be an important factor.

Aside from hardware, system concurrency can play a part as well, either via blocking or overall resource usage. You don't mention if these are issues, so again it's a blind spot for anyone trying to answer.

In newer versions of SQL Server, you can choose to sort index creation in tempdb. This can help, as long as tempdb isn't Full Metal Potato© across your different environments.

Finally, you don't mention the recovery model of your databases. Since CREATE INDEX can be minimally logged, it may be worth switching to SIMPLE or BULK LOGGED recovery model if possible. This will depend on your RPO and RTO goals, and if they can be dismissed for a window to create the indexes.

A quick comment on step 2 that relates a bit to the comment above: If you create a table with no indexes, then do your data load and create indexes, it will likely be faster. Especially in SIMPLE or BULK LOGGED, you can use the TABLOCK hint to potentially get minimal logging on the insert as well as the index creation.

Your question will probably be closed as too broad, but I hope you find this Extended Comment® helpful.


Incorrect syntax near '+' in stored procedure sql server I am writing a stored procedure which will accept a parameter and send mail to some recipients. I am using the sql builtin stored procedure.

here is the code

create proc send_mail_admin(@usage_data as varchar(200))
as
begin

--get the list of mail admins from Users

declare @admin_list varchar(max);

set @admin_list = '';

--query to get all the admins

begin
    select
        @admin_list = @admin_list + email + ';'
    from
        Users;
end

--call the mail stored procedure

exec msdb.dbo.sp_send_dbmail
@profile_name='some_mail_profile',
@recipients=@admin_list,
@subject='Mail from sql server',
@body=@usage_data + ' end of data',
@importance='HIGH' ;

end;

I am getting an error in the + symbol. If I remove the + symbol and execute, the procedure is getting executed. Otherwise it is giving error.
A little help please.

I am trying this on a knowledge based. So feel free to share the disadvantages of sending a mail like this if there are any.

Thank you.

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value.

Consider changing the execution of sp_send_dbmail to this:

SET @usage_data += ' end of data'

exec msdb.dbo.sp_send_dbmail
@profile_name='some_mail_profile',
@recipients=@admin_list,
@subject='Mail from sql server',
@body=@usage_data,
@importance='HIGH' ;

An example with a user defined stored procedure:

CREATE PROCEDURE dbo.bla(@dbname varchar(255))
as
SELECT *
FROM sys.databases
WHERE NAME = @dbname;
GO

DECLARE @dbname varchar(200);
SET @dbname ='Half';

EXEC dbo.bla @dbname+'OtherHalf';

  Msg 102, Level 15, State 1, Line 46 Incorrect syntax near '+'.


SQL Server 2008 Database encryption In my company there is MS SQL SERVER 2008 standard edition installed.
My problem is we want to encrypt whole database but in standard edition there is encryption like transparent-data-encryption(TDE) not available and column level encryption is time consuming.
So i want to know is there any alternative method available in sql server gor whole database backup?

For encrypting data files at rest, you can use a third party product called NetLib Encryptionizer for SQL Server that basically does TDE through a filter driver.

For encrypting backups, you can use any number of third-party backup products. The most popular ones in alphabetical order are Idera SQL Safe, Quest Litespeed, and Red Gate SQL Backup - all of which can encrypt backups on any version/edition of SQL Server that they support. I've also seen cheaper ones, but I'd just caution you that you should only use backup products that have 24/7 support available. Don't play around with restores.

I have no affiliation with any of those products. (I used to work for Quest.)


Database logging users out I have a CiviCRM database on a Drupal CMS using a MySQL database. I'm having problems because users keep getting logged out. First thoughts/suggestions were related to being switched between http/https, or perhaps related to Drupal modules. I've discounted all of these.

However, when I tried to deliver training to the new users (we're due to go live on 3rd April!), it only seemed to log us out when we were trying to write to the database, e.g. creating new contacts or events. Also, for a handful of contacts I hit an error when I try to view their events, but this is fine for the majority. The events were imported directly to the database using phpMyAdmin, rather than the UI in CiviCRM.

Is it possible that there's an issue with the database? I'm using MySQL v5.7.

I'm totally out of ideas and I really need to fix this!

You cannot assume that a user stays logged in forever.  Glitches occur everywhere, especially in networks.  You must code to recover from such.

When running in a browser, each page is necessarily a separate database connection.  Code accordingly.


How to Restrict Schema Access to Specific IP Address & user? I want to lock down access to specific schemas. Restrict the access to specific users when the connection is made from the specific  IP addresses.
Eg Only Access to USER1 and USER2

An AFTER LOGON trigger can be used for this purpose.

CREATE OR REPLACE TRIGGER system.check_ip_addresses
AFTER LOGON
ON DATABASE
BEGIN
IF USER IN ('USER1', 'USER2') THEN
IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN
('192.168.0.10','192.168.0.11') THEN
  RAISE_APPLICATION_ERROR(-20000, 'Can not log in from this IP address (' ||
l_ip_address || ')');
END IF;
END IF;
END;
 /

Remember the following points.

The trigger must use ON DATABASE, as making it schema specific would prevent the RAISE_APPLICATION_ERROR from preventing the logon.
The trigger will fire for all connection attempts, so make sure you restrict which users it affects carefully to reduce the overhead.


need to find instances which have multiple rows with the same userID and ResourceID and have a location of 1 and also have other locations I have a table with

User_ID, Resource_ID, Location_ID, INstance_ID
1,1,1,1
1,1,2,2
1,1,2,3
2,2,3,4
2,3,3,5
2,3,5,6
3,1,2,7
3,1,7,8
4,4,1,9
4,4,2,10
5,5,1,11
1,1,3,14
4,4,1,15
1,1,10,18
1,1,1,19
5,6,1,12
6,7,3,13
6,7,1,17

I need a list of instance_IDs where, for a specific User_ID and Resource_ID, that user/resource combination has a row in Location_ID 1 AND has rows where Location_ID is not 1.

For example:

Instance id 1 (as user 1 has resource 1 in location 1 and location 2)
Instance id 9 (as User 4 has resource 4 in locations 1 and location 2)

Expected output  (the 5 rows that have Location_ID = 1 and there is at least one other row with same User_ID and same Resource_ID but different than 1 Location_ID):

User_ID  Resource_ID  location_ID  Instance_ID
-------  -----------  -----------  -----------
1        1            1            1
1        1            1            19
4        4            1            9
4        4            1            15
6        7            1            17

select distinct
  InstancesinLocation1.user_id,
  InstancesinLocation1.resource_ID,
  InstancesinLocation1.location_ID,
  InstancesinLocation1.Instance_ID
from
  (
    select User_ID,resource_ID, location_ID, Instance_ID
    from usage
    where location_ID=1
  ) InstancesinLocation1
join
  (
    select User_ID,resource_ID, location_ID, Instance_ID
    from usage where location_ID<>1
  ) InstancesNotinLocation1
on
  (
    InstancesinLocation1.User_ID = InstancesNotinLocation1.User_ID
    and
    InstancesinLocation1.resource_ID = InstancesNotinLocation1.resource_ID
  )
;

User_ID  Resource_ID  location_ID  Instance_ID
-------  -----------  -----------  -----------
1        1            1            1
1        1            1            19
4        4            1            9
4        4            1            15
6        7            1            17


Drop role postgresql I just inherited an AWS RDS PostgreSQL 12.5 instance (so, no superuser access) and I'm required to do some cleanup and delete a bunch of groups/roles/users. I'm executing the following commands with user 'master' provided by AWS on the only available database (apart from postgres db)
When trying a:
DROP ROLE xxx

I get:

ERROR: role "xxx" cannot be dropped because some objects depend on it
DETAIL: N objects in database xxx

After a LOT of googling I ended up with:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON SCHEMA some_schema FROM "xxx";
REVOKE USAGE ON SCHEMA some_schema FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON SEQUENCES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON TABLES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON FUNCTIONS FROM "xxx";
REVOKE ALL PRIVILEGES ON DATABASE xxx FROM "xxx";

But still, the DROP fails:

ERROR:  role "xxx" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new types belonging to
role master in schema some_schema
SQL state: 2BP01

Tried to check dependencies using (taken from some stackoverflow question) but query comes up with nothing:
SELECT *
FROM
    --r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
    (SELECT N.NSPNAME AS SCHEMA_NAME,
            C.RELNAME AS REL_NAME,
            C.RELKIND AS REL_KIND,
            PG_GET_USERBYID(C.RELOWNER) AS OWNER_NAME
        FROM PG_CLASS C
        JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
    UNION ALL
    -- functions (or procedures)
    SELECT N.NSPNAME AS SCHEMA_NAME,
            P.PRONAME,
            'p',
            PG_GET_USERBYID(P.PROOWNER)
        FROM PG_PROC P
        JOIN PG_NAMESPACE N ON N.OID = P.PRONAMESPACE) AS FOO
WHERE OWNER_NAME = 'xxx'

Then tried to check privileges using (again, came up empty):
SELECT
    grantee AS user,
    CONCAT(table_schema, '.', table_name) AS table,
    CASE
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee
HAVING grantee = 'xxx'

This is my first time with PostgreSQL, coming from SQL Server.

You have to revoke the default privileges:
ALTER DEFAULT PRIVILEGES FOR ROLE master IN SCHEMA some_schema
   REVOKE ALL ON TYPES FROM xxx;


how to set default charset while create new field Is there any command in mysql that makes the following:
if I create a new field and I set the type to TEXT than it automatically knows that by this I mean not latin1_swedish_ci but utf8_hungarian_ci charset.

I would like to apply this changes to the whole database without create a new table.

You can specify this option in a field definition - CREATE TABLE Syntax, ALTER TABLE Syntax.
Try this ALTER statement -

ALTER TABLE table1
  ADD COLUMN column2 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_hungarian_ci DEFAULT NULL AFTER column1;


Database schema for job offers with some common attributes I'm trying to design a database in order to store IT job offers. My data comes from 4 different websites and unfortunately only part of their attributes (column names) are common. It looks like this:
Website 1: title, overall experience level
Webiste 2: title, overall experience level, skills required, skills nice to see
Website 3: title, overall experience level, skills required, level of required skills (junior or mid or senior)
Webiste 4: title, overall experience level, skills required, level of required skills (in years), skills nice to see, language, level of langauge

I would like to know how would you design a database with this kind of data? I did some research and found that it can be done in different ways:
Approach 1: only one table with all attributes (there will be a lot of nulls) but to be honest I think it's bad idea.
Approach 2: one table storing common attributes to all websites and rest of attributes in separate tables. Also I must notice that each job offer has multiple values in skills required and skills nice to see attributes so I think these two must be in many-to-many relationship with JobOffer table

Approach 3: Four tables with attributes assigned to each of websites (so each table represents each website with its atrributes). And like in previous approach skills required and skills nice to see are in many-to-many relationship with almost (except WEBSITE1 table) every website table

Approach 4: Your own approach because none of these above fit well to this problem. (I've never been designing database before so I'm aware that all 3 approaches proposed by me could be wrong)
Thanks in advance!

Considering "nice to see skills" and "required skills" as wholly separate attributes is a mistake. They are all just skills. Each Job has Skills then those JobSkills are either Required or not (yes/no). In particular, a skill might be required for one job and optional for another. Further, I'd suggest that a language is just another skill.
Mostly, it would seem like this would fit into a schema that looks something like this, where several of the things you have in separate tables become attributes on the Job-Skill cross reference.

Skill

SkillId
Name
Type (technical, language, etc)

Job

JobId
Name
Description
Overall level
Source website

JobSkill

JobId
SkillId
IsRequired
SkillLevel


MySQL Notifier Error I'm getting an error when opening Mysql notifier:

  High Severity Error Element 'UpdateCheck' was not found. Line 3, position 2.

My MySQL server is running. Why is it showing this error?

The error appears due to a corrupted configuration file or some dummy MySQL services (Leftovers from server installations not completely removed, this is due to the auto-detect mysql services functionality). Please make sure you're running the latest Notifier version available (that can be downloaded at mysql downloads page).

How to solve the corrupted file configuration:

See the answer from Colin Pickard.

How to solve the dummy MySQL services leftovers:

If the problem persists, open a Run command window and type the following:

  services.msc

Take note of any MySQL service that shouldn't be listed (attempted uninstall before).
Open an Admin rights command window and type the command:

  sc delete "< Dummy_service_name_here >".

Restart Notifier and see if that corrected the problem.

If a similar error still appears afterwards, check if the stack trace shows the word CreateScheduledTask somewhere, that would mean something is wrong when Notifier tries to add a schedule task at startup to check for updates of any installed MySQL product.

A workaround is to open in notepad the settings.config file that was re-generated. Inside the file, look for a property called "AutoCheckForUpdates" and change the default value of "True" for "False" and run Notifier again, as noted in this post.


Is Primary Key Non Clustered recommended? I am designing a database table.
Columns are RouteId ,Origin , Destination.

RouteId is a string, it is always unique but length will be more than 60 characters.

Since RouteId is unique, I am thinking to make it  Primary key and since the size is large (nvarchar(1000)) I am thinking to make it non clustered index.

My Question: Is it a best practice to make Primary Key (nVarchar(1000)) non clustered index? Will it have effect on performance? What is the best practice in this situation

In a general sense, the Clustered Index should be what is used most often to access and sort the data. A major factor in deciding what to use for a Clustered Index is that the key field(s) will be copied into all Nonclustered Indexes on that table.

The main concern for choosing a PK is finding the field(s) that uniquely identify a row, never being NULL, and preferably never changing. And given that this field will be copied into related table so that the child tables can be Foreign Keyed back to this PK, you generally want something smaller as any large field will have a multiplying affect on disk space. An additional consideration to keep in mind for string fields is the Collation since you are not going to want to JOIN on a field while doing a case-insensitive comparison, as that will be far less efficient than using either a case-sensitive collation or even better would be a binary collation (though the best option is still usually one of the integer types: TINYINT, SMALLINT, INT, or BIGINT).

That all being said, this question is nearly identical to this one:

Primary Key choice on table with unique identifier

And my answer would generally be the same.

BUT, there is one very important difference here: an index can only hold 900 bytes, hence your NVARCHAR(1000) is invalid for being indexed. Unless, of course, the data is never more than 450 characters, but in that case, the field should be NVARCHAR(450) instead of NVARCHAR(1000).

So, create an INT NOT NULL IDENTITY(1, 1) {TableName}ID field and use that as the Clustered PRIMARY KEY.

If you are curious about the total impact of using a large field in either a Clustered Index or Primary Key (given that those values are copied into Nonclustered Indexes and/or child tables), I wrote an article detailing the down-stream effects of this decision:

Disk Is Cheap! ORLY?


Correct Selection of SQL server instance db owner login name I am attempting to connect RSA Archer (5.2) to an instance of SQL server 2008 r2 on WS 2008 R2.
When Archer attempts it's initial connection I get -
The ConnectString property has not been initialized. Please ensure that all databases on the server have a dbowner.

That instance I was connecting to did indeed have a db owner and it had
a DBO role too. I suspect it is the login account that I am associating to this DB owner user ID.

Does anyone have any suggestions how I can get around this problem?

Thanks in advance.

Not sure about your specific case, but I would check to see if the login you're using is a member of the dbo role in the database you're trying to connect to.  You can query role membership using this query:

use YourDatabase;

select
    r.name role_name,
    u.name db_login,
    u.type_desc
from (select * from sys.database_principals where type = 'R') r
    join sys.database_role_members rm on (r.principal_id = rm.role_principal_id)
    join (select * from sys.database_principals where type != 'R') u on rm.member_principal_id = u.principal_id
where r.name = 'db_owner'
order by r.principal_id,u.type_desc,u.name;

If you need to add the login to the DBO role:

Use YourDatabase;
exec sp_addrolemember 'db_owner','FOO\bar';


How do I get the size of a Vertica database? I have a Vertica database. How can I tell how big the database is?

I need a rough answer for the purposes of reporting basic statistics about our environment to upper management.

There are several ways to get the size of a database, each suited to a slightly different use case. It's important to note that Vertica uses raw and compressed data in different ways, and that you should be conscious of which size you require. For example, licensing is based on the raw data size.

Raw Size

The raw size is useful for capacity planning or monitoring license utilization (it's the space the data would take up if it wasn't compressed). To get the raw data size of the entire database, you can either use the GET_COMPLIANCE_STATUS() function, or query the system table v_internal.license_audits.

The GET_COMPLIANCE_STATUS() function retrieves information about the most recent audit. An audit estimates the raw data size of the database and stores the information in v_internal.license_audits. By default, audits take place daily at 23:59 and can be configured or manually run.

Here's some example output:

dbadmin=> SELECT GET_COMPLIANCE_STATUS();
                            GET_COMPLIANCE_STATUS
---------------------------------------------------------------------------------

Raw Data Size: 4.83TB +/- 0.24TB
License Size : 30.00TB
Utilization  : 16%
Audit Time   : 2014-05-11 23:59:49.763799+00
Compliance Status : The database is in compliance with respect to raw data size.

License End Date: 10/30/2014
Days Remaining: 171.18

If you have permissions, you can directly query the license_audits table:

SELECT /*+ label(license_utilization)*/
       audit_start_timestamp,
       database_size_bytes / ( 1024^3 ) AS database_size_gb,
       license_size_bytes / ( 1024^3 )  AS license_size_gb,
       usage_percent
FROM   v_catalog.license_audits
ORDER  BY audit_start_timestamp DESC
LIMIT  30;

Further, if you wish to get raw data size at the schema level, you can use this (from vertica.tips):

SELECT /*+ label(estimated_raw_size)*/
       pj.anchor_table_schema,
       pj.used_compressed_gb,
       pj.used_compressed_gb * la.ratio AS raw_estimate_gb
FROM   (SELECT ps.anchor_table_schema,
               SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb
        FROM   v_catalog.projections p
               JOIN v_monitor.projection_storage ps
                 ON ps.projection_id = p.projection_id
        WHERE  p.is_super_projection = 't'
        GROUP  BY ps.anchor_table_schema) pj
       CROSS JOIN (SELECT (SELECT database_size_bytes
                           FROM   v_catalog.license_audits
                           ORDER  BY audit_start_timestamp DESC
                           LIMIT  1) / (SELECT SUM(used_bytes)
                                        FROM   V_MONITOR.projection_storage) AS ratio) la
ORDER  BY pj.used_compressed_gb DESC;

Compressed Size

The compressed size is the actual size of the data on the disk. This is useful for estimating disk space usage as Vertica recommends that at least 40% of space is available at all times. You can get the compressed size from column_storage or projection_storage system tables.

Using projection_storage will also return any empty tables (from vertica.tips):

SELECT /*+ label(compressed_table_size)*/
       anchor_table_schema,
       anchor_table_name,
       SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb
FROM   v_monitor.projection_storage
GROUP  BY anchor_table_schema,
          anchor_table_name
ORDER  BY SUM(used_bytes) DESC;


Does the collation of ENUM columns impact their size? What does it mean when we say that an ENUM has a charset of utf8mb4, versus latin1?
My understanding is that the ENUM holds a numeric index (1, 2, 3, 4...) that identifies which of the ENUM values (abc, def, ghi, jkl...).
If I use utf8mb4 for an ENUM column, is the column using more storage than it would if I used latin1?

No difference.
ENUM is stored as a 1- or 2-byte number, where 0 maps to the first item in your list.  You deal with the column as a string, but the disk only sees a number.
2-bytes is used only when there are more than 255 options.  There are some who shun the use of ENUM altogether; there are some who frown on more than a few options.  I say use ENUM for a small, unlikely to change, list of options.
It may be possible to get charset or collation problems if you aren't consistent in database and connection specifications.


Stop agent job after executing for a certain number of hours I have a scheduled job which runs nightly. I want it to stop itself at 07:00AM in the morning whether or not it is completed.
I have seen some duplicates in the site but they don't exactly address the problem.
Thanks.

Personally I would create another job and schedule it to run each morning at 7am.

A job step within that job could execute something like

USE msdb ;
GO

EXEC dbo.sp_stop_job
    N'<Your job name>' ;


From RDBMSs to MongoDB I've worked exclusively with RDBMSs for the past 10 years and am trying to explore something new to me like MongoDB. I’m trying to learn about data model design in this environment and because normalization is so engrained in my brain, it feels weird to denormalize and know how and when to do it. A number of questions are coming to mind:

What is Mongo’s answer for modeling a one-to-many relationship?
What is Mongo’s answer for modeling many-to-many relationships?
So, if I have a users collections and a comments collection, where as user has many comments, and a comment references the user document, how do we join this data functionally in code? For each comment displayed, do we “query” the users collection to retrieve their associated username? Or do we embed that user’s username directly into the comments and then push an update to the embedded username in comments in the event that the user’s username would change?
How do we make a decision about when to embed information versus creating a new collection?

Thanks.

I agree with Markus W Mahlberg that this a very broad question and I am looking forward to read his blog. In my answer below I providing a few guidelines that should provide you with a good starting point when designing your data models.

As as a rule of thumb you should favour embedding, unless you need to regularly query the embedded objects on its own or if the embedded array is likely to get too large(>~100 records). Don't worry about the overhead of joins when splitting up a document into multiple collections as application-level joins are generally comparable in speed compared to a server-side join in a relational database provided your collections are properly indexed.

When normalising you should also take in  consideration the write/read ratio of your data. A field that will mostly be read and only rarely updated is a good candidate for denormalisation. If you denormalise a field that is updated frequently then the extra work of finding and updating all the instances is likely to outweigh the savings that obtained from denormalising.

Looking at your particular case, you are probably better off splitting users and comments in separate collections unless you do not allow users to change their username. You could store the ObjectId of a user in the comments collection to link the two collections. You can then use an application-level join like the one below. In this example query the the 10 most recent comments for that particular user are returned:

 // Fetch the user documents identified by this user_name
> user = db.users.findOne({user_name: "john doe"});// assumes unique index
   // Fetch all the comments that are linked to this user
> comments = db.comments.find({_id: user._id  }).sort({CreatedAtUtc : -1}).limit(5000).toArray() ;


Select Permissions Across Multiple Schemas Within the Same Database I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no underlying tables must be available to the user.

The problem I have encountered in that the SQL within the view touches 3 different schemas (All within the same database):

Pupil
Provider
Security

The view is:

CREATE VIEW dbo.vTestPermissions
AS
SELECT a.Column1,
       b.Column1,
       c.Column1
FROM Pupil.Table1 a
JOIN Provider.Table2 b ON a.Column1 = b.Column1
JOIN Security.Table3 c ON a.Column1 = c.Column1

The owners of the Tables/View are as follows:

Pupil.Table1 - Owner Pupil
Provider.Table2 - Owner Provider
Security.Table3 - Owner Security
vTestPermissions - Owner dbo

When I select from the view I get an error:

  The SELECT permission was denied on the object 'table3', database
  'TEST', schema 'Security'

I have tried giving SELECT permission with and without the GRANT option to the schema, and the tables, but that makes the underlying tables available to the user.

Any help on this will be greatly appreciated.

Granting select permission on the view would be sufficient if the view and tables all had the same owner. This is called Ownership Chaining:

  When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Where the objects have different owners, ownership chaining does not work.

So, the simplest answer is to change the ownership of the tables to match the view:

ALTER AUTHORIZATION ON Pupil.Table1 TO dbo;
ALTER AUTHORIZATION ON Provider.Table2 TO dbo;
ALTER AUTHORIZATION ON Security.Table3 TO dbo;

Granting select on the view:

GRANT SELECT ON dbo.vTestPermissions TO USER = 'your_user';

...will then allow access through the view, via ownership chaining, while preventing direct table access.

If that is not suitable for your needs, we will need to look at a more complex arrangement.


SQL - Find Results with MAXimum for given colum I have a question on how to select best match in PostgreSQL for a given result/condition:

I have a setup containing 3 tables.
1 is a result overview table with reliability and status|
2 is a detailed segments overview with details per segment
3 is a general table with information on the targets.

Goal is to map segments onto targets based on results from table 1

Setup:

table1: Results_table

DROP TABLE IF EXISTS result_fcd;
CREATE TABLE result_fcd
(
    source_geometry_id integer NOT NULL,
    reliability double precision NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    geometry geometry(LineString,4326) NOT NULL,
    CONSTRAINT result_fcd_pkey PRIMARY KEY (source_geometry_id)
);

table2: segments (links to originals)

DROP TABLE IF EXISTS  segments;
CREATE TABLE segments
(
    source_geometry_id integer NOT NULL,
    index integer NOT NULL,
    target_link_id integer NOT NULL,
    reversed_target_link boolean NOT NULL,
    start_fraction double precision NOT NULL,
    end_fraction double precision NOT NULL,
    geometry geometry(LineString,4326) NOT NULL,
    CONSTRAINT segments_pkey PRIMARY KEY (source_geometry_id, index)
);

table 3: Originals

DROP TABLE IF EXISTS originals;
CREATE TABLE originals
(
    gid integer NOT NULL DEFAULT nextval('originals_gid_seq'::regclass),
    linknr double precision,
    name character varying(50) COLLATE pg_catalog."default",
    direction double precision,
    length numeric,
    anode double precision,
    bnode double precision,
    namenr double precision,
    geom geometry(LineString,28992),
    CONSTRAINT originals_pkey PRIMARY KEY (gid)
)

Example of output (simple left join)

SELECT target_link_id,reversed_target_link,segs.source_geometry_id,reliability
FROM segments segs
LEFT JOIN result_fcd res ON res.source_geometry_id = segs.source_geometry_id
WHERE reliability > 80
AND status = 'match'
LIMIT 10

results:

target_link_id  reversed_target_link    source_geometry_id  reliability
750 true    6156655 "96.90882243480968"
750 true    6156654 "96.3385845004076"
759 false   299963  "97.88820671302537"
759 false   299962  "90.81686447596023"
759 false   4339973 "83.83639328022115"
981 true    304625  "94.25089913251661"
981 true    304624  "96.521137788089"
981 true    304626  "94.21941014522639"
981 true    304621  "91.93785338486143"
981 true    304622  "95.27831743865612"

now the goal is to merge the results into:

750 true    6156655 "96.90882243480968"
-- 750  true    6156654 "96.3385845004076"
759 false   299963  "97.88820671302537"
-- 759  false   299962  "90.81686447596023"
-- 759  false   4339973 "83.83639328022115"
-- 981  true    304625  "94.25089913251661"
981 true    304624  "96.521137788089"
-- 981  true    304626  "94.21941014522639"
-- 981  true    304621  "91.93785338486143"
-- 981  true    304622  "95.27831743865612"

I tried using a MAX(reliability) and grouping by target_link_id,reversed_target_link,source_geometry_id but that did not really get the results.

Essentially i need the best performing source_geometry_id (max reliability) for each target_link_id

Anyone able to assist with this?


Forced Parameterization and CASE statements We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid cache bloat, and encourage plan re-use.

One of the more complex queries is not being completely parameterized, and I have narrowed it down to literal values being used in CASE statements:

SELECT
CASE WHEN auth_date > '2019-02-03 08:48:03' THEN authorisations.authorisation_amount END
FROM authorisations
WHERE auth_date > '2019-02-03 07:48:03'
AND merchant_id = 400000000031

(This query may not look intuitive, but it is a fragment of a larger set of nested subqueries, and we have to use this structure at the lowest level. This works as an example.)

The parameterized statement used by the query plan is:

StatementText="select case when auth_date &gt; '2019-02-03 08:48:03' then authorisations . authorisation_amount end from authorisations where auth_date &gt; @0 and merchant_id = @1"

You'll notice that the literals in the WHERE clause have been parameterized, but the date literal in the CASE statement has not.

Why is this? What am I missing? What could I try to fix this? The literals will change for every execution, so at the moment, even with forced parameterization, I will end up with a new plan per execution.

So I should have done some more digging before I posted this. The restriction is explained deep down on this page:
Forced Parameterization | Microsoft Docs

Specifically, this section:

  The <select_list> of any SELECT statement. This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.

This tells me it's not specifically the CASE, but that I have included literals in the SELECT list.

Still, if anyone has any suggestions for working around this, I'd be interested.


Pass All Table Values Into Function I found a function that will properly capitalize text strings.  I have a table where my fields need this function run on.  Is it possible to run the function on my table?

Create Table @Garbage
(
  fullname varchar(500)
  ,saddy varchar(500)
)

Insert Into @Garbage (fullname, saddy) VALUES
('BlAmE iT oN tHe RaIn', 'APPALACHACOLA FUNVILLE FOOTHILLS')
,('RED DIRT ROAD', 'purple visors block THE sun')
,('blueBERRIES taste YUM', 'bLaCk CaTs')

ALTER FUNCTION [dbo].[fnConvert_TitleCase] (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
  SET @Char = SUBSTRING(@InputString, @Index, 1)
  IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
  IF @Index + 1 <= LEN(@InputString)
BEGIN
  IF @Char != ''''
  OR
  UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
  SET @OutputString =
  STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
  SET @Index = @Index + 1
END

RETURN ISNULL(@OutputString,'')
END

Verify your intended change using SELECT

SELECT
    fullname = dbo.fnConvert_TitleCase(fullname),
    saddy = dbo.fnConvert_TitleCase(saddy)
from
 @Garbage

and then, if you're satisfied, run the update below

update @Garbage set
    fullname = dbo.fnConvert_TitleCase(fullname),
    saddy = dbo.fnConvert_TitleCase(saddy)

That being said, scalar functions don't really perform very well - a better solution would be to create a table value function and then you could use CROSS APPLY


comparing two values in same column with another column using AND condition Column A is MOBILE NO.
Column B is Agent Id  calc

MOBILE NO     Agent Id
7024226667    A1EXF8ZF  unique
7042004489    A1XKV3J1  unique
7042004940    A1JA23X1  unique
7042005480    A1UFIHOD  unique

Formula in excel they have used column calc is

IF(A2="","Unique",IF(AND(A2=A3,I2=I3),"Multiple","Unique"))

I'm trying in sql server to implement the logic.


How to get only those docs which has all the match fields in mongoose Let say I have the following collection named survey:

{ _id: 1, results: [ { product: "abc", active: true}, { product: "xyz", active: true} ] }
{ _id: 2, results: [ { product: "abc", active: false }, { product: "xyz", active: true } ] }
{ _id: 3, results: [ { product: "abc", active: false }, { product: "xyz", active: false } ] }

by doing:

db.survey.find(
   { results: { $elemMatch: { active:  false } }
)

I will get all the docs where at least one object in the array is false i.e.

{ _id: 2, results: [ { product: "abc", active: false }, { product: "xyz", active: true } ] }

{ _id: 3, results: [ { product: "abc", active: false }, { product: "xyz", active: false } ] }

but how can I get only those docs which has all the key active: false
 i.e:

{ _id: 3, results: [ { product: "abc", active: false }, { product: "xyz", active: false } ] }

TIA..

so here I got one solution, which is perfectly apt for my need:

db.getCollection('survey').aggregate([
    { "$match": { "results.active": false } },
    { "$redact": {
        "$cond": {
            "if": {
                "$allElementsTrue": {
                    "$map": {
                        "input": "$results",
                        "as": "result",
                        "in": { "$eq": [ "$$result.active", false ] }
                    }
                }
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])


Can Oracle 11g Express Edition Work on Parallels I am using a Mac and need to use Oracle 11g. I have Parallels installed with Windows 8.1 on it. Do I need to download anything else or just download Oracle 11g Express Edition and run it?

Better solution would be to install docker or virtualbox and run the OracleXe image within one of those containers. Once docker is running you can easily launch a new OracleXe container with two commands. Already configured to allow easy access via SQLDeveloper. I tested my own steps by downloading the docker toolbox, installing and running the OracleXe image. Everything started up without an issue on my desktop. If all possible I would run this on a droplet at DigitalOcean since Oracle is a hog on space 2.3G!

OracleXe container can be found here: OracleXe Docker Container
Docker Install on Mac: Docker Install


How to keep database size low in MongoDB? Newbie here.

TL;DR : What is the best way to compact data in MongoDB? Will it use much more space than other database systems?

I wanted to create a database for something more or less like a forum. I started learning MySQL, but I don't need a RDBMS for what I want (most of my relations are 1 to 1 (at most, 1 to Many), even if I have a little data redundancy I'd rather store the information as field-and-value pairs of a document, I think it's faster for JSON than the many table joins in MySQL I would otherwise need). I also prefer JavaScript to SQL.

But looking now at the storage requirements, I find nothing like a TINYINT, CHAR(3), etc. I wanted to keep costs down with server's space, but at the same time record a lot of information (time, ratings, votes, comments, tags, karma, favorites, etc.). I hope I'm wrong, but MySQL seems to waste reasonably less space (although I imagine this is compensated in perfomance by MongoDB).

What are the best practices for reducing size in MongoDB? And am I completely wrong in thinking MongoDB uses more space than MySQL and other DBs (disregarding the benefits of joining tables in MySQL to avoid data redundancy)?

So far, these have been the best articles I found on it:
How to limit MongoDB database size (force size limits on the database)
https://www.compose.com/articles/sizing-and-trimming-your-mongodb/ (cap database and release prefetched space)
https://docs.mongodb.com/manual/reference/command/compact/ (use compact, specially on WiredTiger DBs)
https://stackoverflow.com/questions/2966687/reducing-mongodb-database-file-size (repairing data, and same as above)
As @Fyodor Glebov reminds in his answer, there are also 2 compression algorithms: snappy and zlib.

Thanks for any advices!

Because data is not normalised the storage requirements generally bigger.

Don't forget about compression. When you use gzip it will use more CPU power, but disk size is much smaller than snappy. It depends on your data.

Some MongoDB consultant made benchmarks about different compression algorithms, but this file not public. Maybe some MongoDB guy provide an improved answer ;)

No compression
Snappy (enabled by default) – very good compression, efficient use of
resources
zlib (similar to gzip) – excellent compression, but more resource
intensive

see the startup parameter storage.wiredTiger.collectionConfig.blockCompressor

  Default: snappy

  New in version 3.0.0.

  The default type of compression to use to compress collection data.
  You can override this on a per-collection basis when creating
  collections.

  Available compressors are:

  none snappy zlib storage.wiredTiger.collectionConfig.blockCompressor
  affects all collections created. If you change the value of
  storage.wiredTiger.collectionConfig.blockCompressor on an existing
  MongoDB deployment, all new collections will use the specified
  compressor. Existing collections will continue to use the compressor
  specified when they were created, or the default compressor at that
  time.


Aurora postgres grant permission to pg_attribute In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. For example students and student_history, where the history table store the historical stages of the students.

When deleting a column in students, the corresponding column does not delete in students_history.
Which c
I clever college of mine(who found a better job) found a solution of changing the 'pg_attribute.attislocal'(see script below) for the table which solved the problem.

UPDATE pg_attribute
SET attislocal=false
WHERE attrelid = '{parent_table_name}_history'::regclass
AND NOT attisdropped
AND attnum > 0
AND attinhcount > 0;

So I run the script user the super user account I get:

permission denied for relation pg_attribute

So how do I grant he super user permissions to change the pg_attribute or is there anything else I'm overlooking?

Only a superuser can do that, and you probably won't get a superuser in a hosted database.

You will have to drop the history tables and re-create them new as inheritance children (without specifying the duplicate columns!).


SQL Server 2008 upgrade advisor tool We upgraded to SQL Server 2008 from SQL Server 2000. But due to some legacy scripts/queries in our health care application, that run only in 2000, we had to run 2008 version on backwards compatibility mode.

My question is what do I need to be aware of if I have to turn it on to full compatibility mode in terms of potential conflicts/issues etc. Is the Upgrade Advisor tool still useful in my case as we already upgraded but running in backwards compatibility mode. Is there value in running that tool?

Any other information any one can share , will be useful.

The upgrade advisor will work, but you need to make sure to analyze a trace file of activity.  Just pointing it to the database and running it will only analyze the TSQL code stored in the database and not any TSQL code within your application.

In addition to the upgrade advisor, you can run a Profiler trace to capture the events: "Depreciation Final Support" and "Depreciation Announcement".  Both will identify code that will need to be evaluated because of syntax or reference to objects that don't exist in new version.

Check out Books Online for a decription of each of those events: Depreciation Event Category.


SELECT 1 - idle in transaction Across my problems with the load on the database, I just have found a new, odd one. I understand, that some queries might be in IDLE IN TRANSACTION status, when I did something wrong in my application layer. However, just now I have spotted this query having this status for almost 20 seconds now:

SELECT 1;

This is the internal mechanism of JBoss AS, checking if the connection is still valid. I cannot understand how it be idle in transaction. Any hints welcome.

idle in transaction means pretty much what it suggests: there is an open transaction doing nothing at the moment.

What you see can be easily reproduced.  Do the following in one session (disable autocommit in your client if necessary - usually it isn't, an explicit BEGIN; will do that automatically):

test=>
    BEGIN;

    SELECT 1;

In an other session, let's see how it looks in pg_stat_activity:

test=> SELECT pid,
              datname,
              usename,
              xact_start,
              query_start,
              state,
              state_change,
              query,
              now()
       FROM pg_stat_activity;

pid          │ 13639
datname      │ test
usename      │ dezso
xact_start   │ 2015-10-23 10:14:36.853529+02
query_start  │ 2015-10-23 10:15:15.838252+02
state        │ idle in transaction
state_change │ 2015-10-23 10:15:15.838365+02
query        │ SELECT 1
now          │ 2015-10-23 10:16:24.340721+02

Here we see four different timestamps:

the time when the transaction started (xact_start),
the time when the query started (query_start),
the time when the transaction state last changed (state_change, I suppose it's the end of the query) and
the current timestamp (when the above query was run).

Between the last two the transaction is idling, but the last query is still shown.

To answer your question: many clients have autocommit disabled by default.  In this case, a transaction is started silently when the first command (SELECT 1;, for example) is issued - and then never closed.  In this case you'll see what you experience currently and I reproduced above.

AFAIK there is a way to persuade JBoss AS to turn it off.  This might help: https://developer.jboss.org/wiki/Non-transactionaldataaccessandtheauto-commitmode


single node stuck in WSREP has not yet prepared node for application use I have a four node galera mariadb cluster based on mariadb 10.3 that has been running for about a year.

For unknown reasons a particular node tends to fall out of the cluster and is unable to rejoin automatically about once every 3 months.

Most attempts at recovery fail and I have taken to using the manual method described at Manual SST of Galera Cluster Node With Mariabackup to get it back online.

That method no longer works.

After manual recovery the node gets stuck reporting 'WSREP has not yet prepared node for application use' in response to any SELECT query.

Here is a log of an example recovery after performing the manual steps and then restarting the node:

2019-11-26  3:51:44 7 [Warning] WSREP: Gap in state sequence. Need state transfer.
2019-11-26  3:51:46 7 [Note] WSREP: Prepared SST request: mysqldump|10.4.44.86:3360
2019-11-26  3:51:46 7 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2019-11-26  3:51:46 7 [Note] WSREP: REPL Protocols: 9 (4, 2)
2019-11-26  3:51:46 7 [Note] WSREP: Assign initial position for certification: 29059601, protocol version: 4
2019-11-26  3:51:46 0 [Note] WSREP: Service thread queue flushed.
2019-11-26  3:51:46 7 [Note] WSREP: IST receiver addr using tcp://172.24.0.34:4568
2019-11-26  3:51:46 7 [Note] WSREP: Prepared IST receiver, listening at: tcp://172.24.0.34:4568
2019-11-26  3:51:46 0 [Note] WSREP: Member 0.0 (galera) requested state transfer from 'galera3'. Selected 3.0 (galera3)(SYNCED) as donor.
2019-11-26  3:51:46 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 29059601)
2019-11-26  3:51:46 7 [Note] WSREP: Requesting state transfer: success, donor: 3
2019-11-26  3:51:46 7 [Note] WSREP: GCache history reset: 57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:0 -> 57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:29059601
2019-11-26  3:51:46 10 [Warning] IP address '10.4.44.82' could not be resolved: Name or service not known
2019-11-26  3:51:47 0 [Note] WSREP: (0f639e64, 'tcp://0.0.0.0:4567') turning message relay requesting off
2019-11-26  3:51:49 17 [Note] WSREP: SST received: 57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:29057993
2019-11-26  3:51:49 17 [Note] WSREP: wsrep_start_position set to '57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:29057993'
2019-11-26  3:51:49 7 [Note] WSREP: Receiving IST: 1608 writesets, seqnos 29057993-29059601
2019-11-26  3:51:49 0 [Note] WSREP: 3.0 (galera3): State transfer to 0.0 (galera) complete.
2019-11-26  3:51:49 0 [Note] WSREP: Member 3.0 (galera3) synced with group.
2019-11-26  3:54:00 0 [Warning] WSREP: Protocol violation. JOIN message sender 3.0 (galera3) is not in state transfer (SYNCED). Message ignored.

SELECTs return ''WSREP has not yet prepared node for application use' and the system remains in the state permanently

If I keep an eye on the doner node it goes into the donor state for a while and then returns to the synced state.

The other 3 nodes, including the donor are all working correctly, at least as far as I can tell.  The other 3 are willing to run SELECT queries.

Any ideas on how I might resolve this issue?

I met this issue too, and solved it by re-sync that node through SST. I use RSYNC as SST method to make sure the consistence between nodes.


Query Netezza to find all tables in views I am trying to recreate the following query in netezza and am struggling:

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = '<giveViewName>'
ORDER BY view_name, table_name

Any tips on how to do this?

Check this Link and provide the error that its generated in your current query.

or its this query not returning data ? in that case maybe could be the where statement


Sending a mail using DB2's Proceude UTL_MAIL I'm trying to send mail from DB2 using the procedure UTL_MAIL.
I'm using the DB2 Express Edition 10.1.
First I was getting the error of SMTP Server.
After using the below command,

update admin cfg using smtp_server LOCALHOST

I am using the following command to send the mail:

CALL UTL_MAIL.SEND('test@mycomp.com','myid@hotmail.com',NULL,NULL,'Test Email','Test body')

Now I am getting the following error:

SQL1336N  The remote host "" was not found.  SQLSTATE=08001

I searched a lot and found out that I must CATALOG the server details.

Can anyone please help me regarding this?

Based on what you are trying to do you would want to set up the STMP server at the database level rather than the DAS level. You set it up at the DAS level if you want DB2 to email alerts to you. See the Information Center here.

You set it up at the database level if you want to use the UTL_MAIL module as you are wishing to use.

You would need to do the following:

db2 connect to mydb -- or whatever your database name is.
db2 update db cfg using smtp_server '<server or ip>:<port>'
-- you can have a list of smtp servers if you want, just use a comma between them.
db2 connect reset

This setting takes affect immediately. Please see Information Center for more details.


See the MySQL documentation or a Step by step replication setup blog if you need to review.

If these doesn't help please share the output of show slave status\G (as such it appears connected) and also output of my.cnf of master/slave.
Mainly you need to worry about server-ids, replication filters (replicate-to-db/table etc)


Select values from 2 tables with same properties I am using SQL server 2012, running on windows data-center 2012, under windows azure cloud platform virtual machine.

I have the following situation :

I have a base settings table lets say as following:
ID as a key (int), and many settings column

I have another "extended settings" table that has the following:
ID as a key,
some information (some settings column),
SETTINGS_ID as int related to the settings table ID key

I need a query that would get me the settings from the settings table and would overwrite any value it can find from the extended table that is not null, in a generic way:

Get a settings_id as int, get an "extended_settings" id as int,

Returns a select of all the settings from the settings table by its id but for every setting that is similar by its property name in the extended settings table, and different than null (in the extended table), take the one from there,

I need it to be generic...

I could write the following:

SELECT ISNULL(EXTENDED_SETTINGS.A, SETTINGS.A),
       ISNULL(EXTENDED_SETTINGS.B, SETTINGS.B),
       ISNULL(EXTENDED_SETTINGS.C, SETTINGS.C),
       ISNULL(EXTENDED_SETTINGS.D, SETTINGS.D),
       SETTINGS.name , SETTINGS.SOME_OTHER_INFO_THATS_NOT_IN_THE_EXTENDED_TABLE
FROM SETTINGS INNER JOIN EXTENDED_SETTINGS ON SETTINGS.ID = EXTENDED_SETTINGS.SETTINGS_ID
WHERE
EXTENDED_SETTINGS.ID = @extSetId AND SETTINGS.ID = @settID

this would work, but i want to make a generic query so that if i add a column named "J" to both tables it would also work, and if i add a column to the settings table it would also work, and i wont have to modify this query every time i change my tables...

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';

SELECT @sql += N'
  ' + QUOTENAME(c.name)
  + ' = COALESCE(es.' + QUOTENAME(c.name)
       + ', s.' + QUOTENAME(c.name) + '),'
FROM sys.columns AS c
INNER JOIN sys.columns AS c2
ON c.name = c2.name
AND c.[object_id] = OBJECT_ID('dbo.SETTINGS')
AND c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
AND c.name NOT IN (N'name', N'ID' /* , ... potentially others ... */);

SET @sql += N' s.name, s.ID';

SELECT @sql += N',
  s.' + QUOTENAME(c.name)
  FROM sys.columns AS c
  WHERE [object_id] = OBJECT_ID('dbo.SETTINGS')
  AND name NOT IN (N'name', N'ID')
  AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns AS c2
      WHERE c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
      AND c2.name = c.name
  );

SET @sql += N'
  FROM dbo.SETTINGS AS s
  INNER JOIN dbo.EXTENDED_SETTINGS AS es
  ON s.ID = es.SETTINGS_ID
  WHERE es.ID = @extSetId AND s.ID = @settID;';

PRINT @sql;
--EXEC sp_executesql @sql;

Please use table aliases and schema prefixes, and here is why I prefer COALESCE over ISNULL. And if those tables really aren't stored as upper case in a case sensitive collation, do you really need all caps? Also, ID is a horrible and ambiguous name for a column. It should be the same name across the schema; always describing exactly what it is - especially when you're doing lazy dynamic things like this.


Amazon RDS Slow Query Log Does Not Show SQL Statement I enabled the slow query log on Amazon RDS and I executed a "select sleep(5)". But when I look at this table, how is it that sql_text does not show the actual SQL statement?
mysql> select * from mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2021-01-27 15:45:08.033183
     user_host: XX
    query_time: 00:00:05.002547
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: test
last_insert_id: 0
     insert_id: 0
     server_id: 3213123123
      sql_text: 0x73656C65637420736C656570283529
     thread_id: 103213

Below are the settings:
log_output = TABLE
slow_query_log = 1
long_query_time = 1
log_slow_admin_statements = 0
log_queries_not_using_indexes = 0

What I am not sure is why I got this output:
sql_text: 0x73656C65637420736C656570283529 which I suspect is the select sleep(5) ?

Update:
I ran this again to make sure I didn't execute select HEX(sleep(5)), and I didn't do that. Also, the sql_text are all the same even though I ran different SQl statements.
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.09 sec)

mysql> select * from mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2021-01-27 15:45:08.033183
     user_host: XX
    query_time: 00:00:05.002547
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1499
      sql_text: 0x73656C65637420736C656570283529
     thread_id: 107032
*************************** 2. row ***************************
    start_time: 2021-01-28 21:26:11.324022
     user_host: XX
    query_time: 00:00:02.000258
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1499
      sql_text: 0x73656C65637420736C656570283229
     thread_id: 103121
*************************** 3. row ***************************
    start_time: 2021-01-28 21:26:46.160363
     user_host: XX
    query_time: 00:00:03.000205
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1499
      sql_text: 0x73656C65637420736C656570283329
     thread_id: 110323
3 rows in set (0.10 sec)

Also, I tested with log_output = FILE and I see the correct SQL statements. See below.
# Time: 2021-01-28T21:18:10.623686Z
# User@Host: XX Id: 131232
# Query_time: 1.000215 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1611868690;
select sleep(1);

Check various settings:
log_output = TABLE    # versus FILE
slow_query_log = ON
# slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON       # optional
log_queries_not_using_indexes = OFF  # optional


Limit access to specific database only, and restrict access to system tables I have a user test, which can view data from the system tables such as pg_class. The idea is to isolate him to have access to a specific database only
and not have any access to system resources.

I don't want him having access to tables like pg_stat_replication, pg_stat_activity, or using any of the functions like pg_current_xlog_location();.

The question is, how can one achieve this?

For example:

Create user test with password test

I cannot create DB or create another user or role, however I have access to let's say pg_class (by default, even the path is not set to public it is set to test schema), which I actually would like to prevent in general.

In Oracle (where I come from), there is the principle of least privilege in other words you create a user and it does not have any privilege or role granted to him.

You can achieve this goal by revoking certain privileges.  You might have the reasons to do so - the only scenario where I'd start thinking about it is multi-tenancy.  Still, in this case, it is better not to allow the tenants to directly touch the database.

So, after this detour, what do you have to revoke?  For functions it's the EXECUTE privilege, for tables and views SELECT - ordinary users cannot change data in pg_catalog anyway.  For cleanliness, it is possibly better to revoke ALL, which then includes the former two as applicable.

Let's see now what happens:

# CREATE USER lame_bob [WITH PASSWORD 'l'];  -- note that this way the password
                                             -- might appear in the database logs,
                                             -- so use psql's \password command instead

# REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM lame_bob;

# \c test lame_bob

> SELECT pg_current_xlog_location();

 pg_current_xlog_location
──────────────────────────
 0/26517910

Oops.

The trick here is the public pseudorole, which has privileges on certain objects (like EXECUTE on the function in this example).  To disable Lame Bob, this has to be also revoked:

> \c test dezso

# REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM public;

# \c test lame_bob

> SELECT pg_current_xlog_location();

ERROR:  permission denied for function pg_current_xlog_location

In turn, this might disable other users, too, who should keep the ability of using this function.  If necessary, you can grant them the EXECUTE privilege one by one - but there is a better way:

# CREATE ROLE can_use_system_functions; -- no login rights

# GRANT EXECUTE ON FUNCTION pg_current_xlog_location() TO can_use_system_functions;

# GRANT can_use_system_functions TO able_alice;

And then able_alice (and all other users that eventually gets the membership in can_use_system_functions) will be able to do what they need to do.  Allowing new users to these objects is just a matter of granting them this single umbrella role.


MySQL: How to create Column if not exists? I am trying to create a Column for my table only if it does not exist.
I have researched a lot but I could not find any solution yet.

Is this really possible to conditionally create Column ?

Actually exists now for Maria DB 10.219

ALTER TABLE test ADD COLUMN IF NOT EXISTS column_a VARCHAR(255);

Bonus, it works for MODIFY as well

ALTER TABLE test MODIFY IF EXISTS column_a VARCHAR(255);


To partition or not to partition? Having already read several questions on SO, external blog posts and manual

SO: Foreign Key constraint to partitioned table in Pg
dba.SE: Different ways of handling FK to partitioned table in Pg
Manual: Inheritance
Manual: Partitioning
Manual: Constraint triggers
Blog: Postgres modelling with inheritance

I still find myself wondering if I should go with partitioning considering my case or not.

The case - simplified

Storing customer data. All names of tables mentioned below are made up for clarity.

Having objects that are identifiable by customer and are non-physical beings, also their physical objects in which they are actually stored in case of needing to send some objects back to the customer on demand, or process it in some other ways. They are mapped in a many-to-many relationship. objects_nonphysical, objects_physical, objects_mapping_table.
Second many-to-many relationship is between those non-physical objects and their metrics. There are objects which are bound with some metrics. metrics, metrics_objects_nonphysical
Both non-physical and physical objects have their hierarchy tables which are child-parent relations. objects_nonphysical_hierarchy, objects_physical_hierarchy

Depending on each customers' needs and requirements the data about physical objects can be supplied or might need to be created from scratch. Basically, what I need to do is:

Maintain internal system for fast INSERT and SELECT statements, because here is where the mapping is going to take place.
Maintain the system for external customer to view and operate on their non-physical objects - fast retrieval of data. Strong need of efficiency for SELECT statements - this data is available for many customers to search whenever they want.

My consideration

There can be a customer, who may access the data, view and operate on it, but that doesn't need to be a contractor that we got the data from / are processing the data for.

This has lead me to introduce table partitioning into my system, considering that I always know which partition data should fall into (partitioning for contractors), and then to mainain system for external customer where I need partitioning for customers (this would be done with some delay using automation tools and set of rules to rewrite the data in customers manner, so that for each customer we'd only scan one partition for each table.

Data volume

My data is going to grow constantly, especially when importing new customers' objects and metrics. The pace of new data arriving into the system is unpredictable at the moment for the long run. There is really no way to measure it not knowing who is going to be the next customer. Right now there are just 2 customers with more or less 1M rows for every customer in every table. But in future I predict new customers to come with a volume of 10M rows or so as well.

Questions

These questions are all related to each other.

Should partitioning really be considered here, or is that an overkill? I consider it to be of use since I'm always scanning exactly one partition.
If partitioning is the way to go, how do I enforce FK constraint the most effectively considering my needs? Should I go for constraint triggers, or just keep it in the application layer for internal system, or maybe some other method?
If partitioning isn't the way to go, what should I dive into?

If there is not enough data supplied, please let me know in the comments below.

There are many open ends in your question, but partitioning by customer could to be the way to go - especially if:

you expect many customers,
each of them could have tons of data ("tons" means much more than RAM cache size),
most of their datasets will be mutually exclusive (each customer sees different subset of data).

RULEs or triggers are a performance overhead, and can be avoided.

Consider something along these lines:

BEGIN;

CREATE USER tenant1;
CREATE USER tenant2;

CREATE SCHEMA app;
CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;

CREATE TABLE app.objects_nonphysical(id int);
CREATE TABLE app.objects_physical(id int);
CREATE TABLE app.objects_mapping(id int);
CREATE TABLE tenant1.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant1.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant1.objects_mapping() INHERITS(app.objects_mapping);
CREATE TABLE tenant2.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant2.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant2.objects_mapping() INHERITS(app.objects_mapping);

GRANT USAGE ON SCHEMA tenant1 TO tenant1;
GRANT USAGE ON SCHEMA tenant2 TO tenant2;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant1 TO tenant1;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant2 TO tenant2;

/* TEST: simulate login as customer */
SET SESSION AUTHORIZATION tenant2;
/* No schema needed - default search_path works */
SELECT count(*) FROM objects_nonphysical;

ROLLBACK;

You do not need any triggers / rules to maintain it.

There are open ends here - that's just a draft... Some issues:

PK, FK and indexes are not "inherited".
even if you create them, the PK is not enforced on master table
you can overcome this by using same sequence for all tenants
obviously, application must be adjusted for this model


Group by multiple column acquired from join this is part of a query result :

all three columns are user_ids acquired from multiple tables join .
as you see each row has only one user_id and two other user_ids are necessarily null .
I'm going to group this rows by user_id regardless of the column it is on.

so i need to have all user_ids in one column (instead of three) to make me enable to perform group by on it. the ultimate result should be like this:

You can do this using COALESCE. If table1.user_id is null then it will use table2.user_id. If that is null then it uses table3.user_id and so on.

SELECT COALESCE(table1.user_id, table2.user_id, table3.user_id) AS `xx_user_id`
FROM ...table joins...
GROUP BY `xx_user_id`

You won't be able to use the derived xx_user_id column in a WHERE clause however you can use it in a HAVING clause

SELECT COALESCE(table1.user_id, table2.user_id, table3.user_id) AS `xx_user_id`
FROM ...table joins...
GROUP BY `xx_user_id`
HAVING `xx_user_id` = 123;


Postgres: find all FK constraints associated with a given PK Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK.
I also need table and schema's names where each particular FK constraint is located.

So basically I am looking for all tuples ( schema_name.table_name, FK_constraint_name ) such that FK_constraint_name is referencing input PK.
As an example, the input PK ani.animals.id has:

fk_vet_animal in table ani.vets
fk_person_animal in table usr.people

Any ideas how to tackle it?
My ultimate goal is to, given some PK, drop all the found FK constraints, and re-create them with ON UPDATE CASCADE.

Does the following query work for you?
SELECT nr.nspname AS table_schema,
        r.relname AS table_name,
        c.conname AS constraint_name,
        split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 2 ), ')', 1 ) AS column_names,
        nr2.nspname AS r_table_schema,
        r2.relname AS r_table_name,
        split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 3 ), ')', 1 ) AS r_column_names,
        CASE c.confmatchtype
            WHEN 'f' THEN 'MATCH FULL '
            WHEN 'p' THEN 'MATCH PARTIAL '
            WHEN 'u' THEN 'MATCH NONE '
            ELSE ''
            END
            || CASE c.confupdtype
                WHEN 'c' THEN 'ON UPDATE CASCADE '
                WHEN 'n' THEN 'ON UPDATE SET NULL '
                WHEN 'd' THEN 'ON UPDATE SET DEFAULT '
                WHEN 'r' THEN 'ON UPDATE RESTRICT '
                WHEN 'a' THEN 'ON UPDATE NO ACTION '
                ELSE ''
                END
            || CASE c.confdeltype
                WHEN 'c' THEN 'ON DELETE CASCADE'
                WHEN 'n' THEN 'ON DELETE SET NULL'
                WHEN 'd' THEN 'ON DELETE SET DEFAULT'
                WHEN 'r' THEN 'ON DELETE RESTRICT'
                WHEN 'a' THEN 'ON DELETE NO ACTION'
                ELSE ''
                END AS constraint_rule,
        d.description AS comments
    FROM pg_class r
    INNER JOIN pg_namespace nr
        ON ( nr.oid = r.relnamespace )
    INNER JOIN pg_constraint c
        ON ( c.conrelid = r.oid )
    LEFT OUTER JOIN pg_description d
        ON ( d.objoid = c.oid )
    INNER JOIN pg_catalog.pg_class r2
        ON ( r2.oid = c.confrelid )
    INNER JOIN pg_namespace nr2
        ON ( nr2.oid = r2.relnamespace )
    WHERE r.relkind = 'r'
        AND c.contype = 'f'
        --AND nr.nspname = ?
        --AND r.relname = ?
        AND nr2.nspname = ?
        AND r2.relname = ?


Measure compilation time of stored procedure I need to measure how much time it takes for the MSSQL-2012 to compile a stored procedure.

Is it possible?

Yes, you can get this information. It is buried inside properties of the select statement in an actual execution plan. To do that:

Set Actual Execution plan to on
Execute your SP with recompile option
Go to Actual Execution plan, choose 'select' part
Go to properties (Press F4) and there you can see CompileTime

However, I'm not sure how accurate or what measures this information displays.

I am assuming, you want to discover how much it does affect your performance. You can search this article for 'compile time' https://technet.microsoft.com/en-us/library/cc966540.aspx


Apply Definition of MVD (multivalued dependency) to Example? Supposedly this multivalued dependency (MVD) and this functional dependency (FD):

  {W} ↠ {N,D} and {W} → {N}

hold in this table:

  V   W   N   D
=================
| v | w | n | 1 |
-----------------
| v | w | n | 2 |
-----------------

How do I show that this relation has those two dependencies via the following definition?

  Let R be a relational schema and let A ⊆ R and B ⊆ R (subsets). The multivalued dependency
  A ↠ B
  (which can be read as A multidetermines B) holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[A] = t2[A], there exist tuples t3 and t4 in r such that
  t1[A] = t2[A] = t3[A] = t4[A]
  t3[B] = t1[B]
  t3[R - B] = t2[R - B]
  t4[B] = t2[B]
  t4[R - B] = t1[R - B]

A FD is a MVD "in disguise". If an FD holds then a certain MVD also holds. With MVDs 1 is a special case of multi(ple). This can be seen from the rules of inference for MVDs at the definition link: RA → B implies A ↠ B.

Hint: t1 & t2 can be t3 & t4.

Hint: In the example there are only two tuples, so t1 & t2 must be those, and you know the MVD so you know A & B, and there are only two tuples, so if t3 & t4 exist they must be those. So fill in the equations and see whether given those two tuples (t1 & t2) there are also two tuples with the properties needed (t3 & t4).


AlwaysOn read only replica slower then prime I’m running into an issue. My readOnly replica (on Node B) takes twice as long to execute a query as on my primary (Node A).
But when I fail over to Node B (which becomes my primary) it runs fine, but then it runs slow on the new replica so I don't think its the hardware.

I checked the index fragmentation and they are identical and there is currently nothing running on the servers except my query.
Execution Plans are identical.
The power is set to High Performance.

We are running SQL 2017 on windows 2016 server.

Any suggestions on what else to check?

To fix our problem we had to enable Trace Flag 3459.

We are currently having this issue on SQL Server 2017 CU6 (14.0.3025.34). Not much documentation on this Flag, but from what I gather, enabling this flag will slow down our failover process. I'm going to test some more, and update if i run into anything show stoppers.


Which column to trust about the event order in a profiler result? Hi I recorded some events using the SQL profiler and I want to work out the exact order of what happened first. But I am having trouble in this basic task, because what ORDER BY EventSequence showed was different to ORDER BY EndTime and also different to ORDER BY RowNumber

Which column should I trust?

Taken from Microsoft's SQL Server PSS Blog:

  The Event Sequence is the order the trace event was internally created in the server which can be different from the physical file order due to various timing and design issues.   The Event Sequence gives you a more accurate representation of the event order.  When looking at trace data from SQL Profiler or one loaded in the database you should order by the Event Sequence.


DLATK: Resolving (1045, "Access denied for user 'root'@'localhost' (using password: NO)") I am currently using the Differential Language Analysis ToolKit (DLATK) that relies partly on SQL.
I tried accessing a SQL file dla_tutorial in the DLATK file using this code in my terminal (MacOS):
dlatkInterface.py -d dla_tutorial -t msgs_zz -c user_id --add_ngrams -n 1 2 3 --combine_feat_tables 1to3gram

However, I get this error:
*MYSQL Connect ERROR on db:dla_tutorial
(1045, "Access denied for user 'root'@'localhost' (using password: NO)")

I have seen other answers that recommend changing my password for SQL. However, I had some troubles understanding and executing them, and I'm not sure if this is a DLATK-specific problem. If anyone could offer any leads, that would be super helpful!
Thanks!

This project is written around a passwordless user (and MyISAM as a default engine) indicating databases's aren't the projects strong suite.
The username is fixed as the current logged in user getpass.user
So recommends:
create a non-root user the same as your unix user (replace XXX below with your local user name).
create user XXX identified by '';
grant all on DATABASE.* to XXX@localhost;

DATABASE is the name of the database where the samples where loaded which is probably dla_tutorialby your command line.


Seperate volumes for tempdb, data, and backups on SQL Servers in VMs When configuring new SQL Servers on VMs, it is a good idea to have the Database, TempDB, logs, and backups on separate logical drives, even if the underlying storage is the same? I know that this is a good practice on physical servers; the separation actually helps us to reduce the pains caused by concurrent I/O.

I am trying to understand if having 3 drives instead of 5 would make sense. Can someone please provide their views/suggestions for this idea?

I like to separate them even if they are pointing to the same drives for disk space reasons. If I run out of space on my backup drive/mount point, it's not going to break the database (not right away at least).

This is how  I usually separate things at a minimum:

Data files for user databases
Log files for user databases
Data file(s) for tempdb
Log file for tempdb
Data and log files for system databases, except tempdb
Backups (though I prefer to use a network share instead)

Each of the above would be a separate drive or separate mount point. Depending on the system, I might have separate devices for each database's data files.

But my systems have always used a SAN rather than local disks. If you had the choice between 3 drives or 5 drives, I would just ensure backups are separate and tempdb is separate. But again only for disk space reasons.


MySQLfrm - Using multiple frm files I am busy rebuilding an entire MySQL DB. There are multiple schemas and multiple tables in each schema.

I am restoring them using *.frm files as all other methods used have either failed or corrupted the data.

I am using the command:

mysqlfrm --server=root:password@localhost:3306 F:\DirectoryStructure\File.frm > file.txt --diagnostic

Is there a way to export ALL frm files from the same command using only one command? As doing each file one by one is going to take forever. I have tried using:

mysqlfrm --server=root:password@localhost:3306 F:\DirectoryStructure\*.frm > filename.txt --diagnostic

but this doesn't work.

Any help is really appreciated

You can use online service https://recovery.twindb.com/ (I'm the author). Basically, it's a wed interface to mysqlfrm.

To restore structure from multiple frm files you can run this

find /path/to/frm_files -name '*.frm' | \
xargs mysqlfrm --basedir /usr --port 3333

In /usr mysqlfrm should find mysqld (it starts it to read the frm).


Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middle I'm stuck at trying to write the correct SQL code for retrieving and displaying all results from querying three tables. Simply put, I have three tables: A, B, and C. A and C are not connected to each other and must use B to get the referenced data that A needs from C. Below are simple examples of the tables:

Table Users
-----------
user_id     username
-------     ---------
1           bob
2           harry
3           jill

Table User_Emails
-----------------
email_id    user_id
--------    -------
1           1
2           1
3           2

Table Emails
------------
email_id    address
--------    --------------
1           bob-email1@test.com
2           bob-email2@test.com
3           harry-email1@test.com

In this example, the user_emails table holds an id that links an email address to a user account, letting a user have multiple email addresses. What I am trying to do is retrieve all the users and the email addresses for those users and if they do not have an email address just display a null value.

I cannot change the table as this is how it is set in stone. The database that is being used is Oracle 12c. Currently I have a cursor that grabs all the users and then loops through each user, checking it against the user_emails table. While this would work for a small result set, the reality is that there are thousands of records and I know this is inefficient, however I cannot figure out how to display the usernames and email addresses together along with null values for email addresses if the username doesn't have an id in the user_emails table. I asked the in-house DBA and didn't get anywhere, so I am asking for some SQL help from the gurus as this dummy (me) cannot figure it out.

Thank you for your time and help.

This is a very simple query:

SELECT
    u.username,
    e.address
FROM
    Users  u
  LEFT JOIN User_Emails  ue
    ON ue.user_id = u.user_id
  LEFT JOIN Emails  e
    ON ue.email_id = e.email_id
ORDER BY
    u.user_id,
    e.address ;

You need the LEFT joins so users without emails are shown too (with NULL in the address).
You can add more columns in the SELECT list if you like, e.g. the u.user_id or the e.email_id.
The ORDER BY is also optional and can be changed according to your requirements.


Why are my results returned twice? I've this query:

 SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id,
  `default_wall_posts_comments`.`comments` as comments,
  UNIX_TIMESTAMP() - default_wall_posts_comments.date_created AS pass_time_comment,
  `default_profiles`.*, `default_wall_posts`.*,
  UNIX_TIMESTAMP() - default_wall_posts.date_created AS pass_time
 FROM (`default_wall_posts`)
 LEFT JOIN `default_profiles`
    ON `default_profiles`.`user_id` = `default_wall_posts`.`userid`
 LEFT JOIN `default_wall_posts_comments`
    ON `default_wall_posts`.`p_id` = `default_wall_posts_comments`.`post_id`
 WHERE `default_wall_posts`.`userid` =  '1'
 ORDER BY `default_wall_posts`.`date_created` DESC LIMIT 7

But results for the main table which is default_wall_posts are repeated for each default_wall_posts_comments.

I'm trying to get all comments for one default_wall_posts. Can be this query optimized in some way?

That's how joins work!  Don't select the wall posts in the same query if you just want the comments.


On a 12c R2 RAC DB, Oracle instance information on crsctl is shown incorrectly crsctl status output is showing incorrect information for ora.***.db.
Instance 1 and 2 are running on Node1 and Node2 respectively but crsctl status shows as below

As we can see below, its showing Instance 1 is running on Node2 and Instance 2 is running on Node1.

ora.stgcdb.db
      1        ONLINE  ONLINE       node02                 Open,HOME=/u01/app
                                                             /oracle/product/12.2
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node01                 Open,HOME=/u01/app
                                                             /oracle/product/12.2
                                                             .0/dbhome_1,STABLE

When we see srvctl status for database, it shows as below :

[oracle@node01 ~]$ srvctl status database -d stgcdb
Instance stgcdb1 is running on node node01
Instance stgcdb2 is running on node node02
[oracle@node01 ~]$

With a valid support contract, you can check this:
Database Instance Resources Sequence in "crsctl stat res -t" Output Mismatches With Output of "olsnodes -n" (Doc ID 1580894.1)

  The node ordering is not guaranteed to be same as instance number
  ordering. CRSCTL output in sorted order is not guaranteed.

  ...

  This is expected behavior.


Return json list of values in SQL Server 2016 using data from 2 columns I have a database with 2 columns

A     B
--    --
X     1995
Y     2005
C     1962
D     2003

I'm trying to create a SQL statement that will take a string of comma delimited values and return a json list of values in B where any value in the string is in A

so if the comma delimited string was 'X,C' the json list would be [1995,1962]

I've been using json path to try this, but I can't get it exactly like I want it and I've been spinning my wheels for too long

This is what I've tried:

Select mt.B as json_list_b_values
 From  [dbo].[myTable] mt
Where  mt.A in (Select value From String_Split('X,C', ',')) for json path

This is the ouput:

[ {"json_list_b_values":"1995"}, {"json_list_b_values":"1962"} ]

If you are able to use FOR XML PATH('')

SELECT  DISTINCT
 QUOTENAME((STUFF(
    (
      SELECT ',' + try_convert(varchar(4),mt.B)
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR XML PATH('')
    ), 1, 1, '')));

If not, you could do a lot of REPLACE()ments

    SELECT  DISTINCT
 QUOTENAME(REPLACE(REPLACE(REPLACE(REPLACE((STUFF(
    (
      SELECT  mt.B as '}'
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ), 1, 1, '')),'"',''),'}',''),'{',''),':',''))

Result for both:

(No column name)
[1995,1962]


Reinitializing Subscription Performance I have 4 Servers, Servers A, B, C, D.

Server A publishes a Replication instance, and Servers B, C, D have Pull Subscriptions to it.

Server B's subscription needs to be Reinitialized, but I am concerned that reinitializing it (Using the "current snapshot", not by generating a new one) will impact Replication Performance to the other Servers.

Should I be concerned, or because Servers B, C, and D have Pull (instead of Push) subscriptions, am I just over-thinking this?

It really depends on how old the snapshot is.  After the snapshot is applied, the agent will need to also send all of the changes that have occurred since the snapshot was taken.  Depending on how many changes have occurred, this may or may not affect other existing subscribers.

I tend to take a new snapshot if possible before reinitializing a subscriber although sometimes that is not an option.

I wouldn't worry too much about it impacting other subscribers.


Prepared statements with pgBouncer I have a PostgreSQL server with pgBouncer as connection pooler.
My application is running on Elixir.
This is my config file for pgBouncer:
* = host=X.X.X.X  port=5432
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin
pool_mode = transaction
ignore_startup_parameters = extra_float_digits
server_check_query = select 1
server_check_delay = 30
max_client_conn = 10000
default_pool_size = 5
min_pool_size = 3
reserve_pool_size = 3
server_reset_query = DEALLOCATE ALL;

When I use the transaction pooling I'm getting this error:
ERROR 08P01 (protocol_violation) bind message supplies 4 parameters, but prepared statement "ecto_323" requires 2

Then I changed it to session pooling mode. This time I'm getting this error:
ERROR 26000 (invalid_sql_statement_name) prepared statement "ecto_83" does not exist

How do I fix this from pgBouncer?
When I connect the DB directly, I didn't see any errors. It was running more than a year without any proxy. We are implementing pgBouncer now.

The error in session pooling mode indicates that you are doing something different that is not connected with pgBouncer: if the application has the same session all the time, the only explanation for a missing prepared statement is that it either was never declared or deallocated. That should happen just the same without pgBouncer.
The maxim here is that everything should work with session pooling mode.
The error with transaction pooling mode indicates that you are using the same prepared statement name for different statements. Now when an application thread gets some other thread' session in the next transaction, and it gets the prepared statements along with it, that is no surprise.


Shared network location in Availability Groups configuration I am configuring Availability Groups feature in azure virtual machines nodes. In the below screen wizard, it's asking me for shared location. where and how I need to create this shared location?

Do I need to have new VM with disk attached, which can be used as shared location?

OR

Do I have to create FILE SHARE feature in azure storage?

That is for a backup to be taken and restored to all your secondaries, for an initial synchronization of the database. Generally that share is just for a one-time use, it will not use it again once they are in sync.

If you want to use it to sync up the databases it can just be a shared folder on the local server that all your replicas have access. The SQL Server service account for your other replicas would need read access to the share if you use it.

Alternatively if you restored the database, yourself, to the other replicas using WITH NORECOVERY you could just use the Join Only option.

The last option, simply means join it to the primary for right now and that is it. I will work on adding it and sync'n the database up to all the replicas later.


How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections? From PostgreSQL docs:

Number of Database Connections -> How to Find the Optimal Database Connection Pool Size

  for optimal throughput the number of active connections should be
  somewhere near ((core_count * 2) + effective_spindle_count)

Tuning Your PostgreSQL Server -> max_connections

  Generally, PostgreSQL on good hardware can support a few hundred
  connections.

For me - not an experienced DBA -  there's a discrepancy somewhere in here, especially looking at the offerings of some DB-as-a-Service providers.

For example,  at this time Amazon RDS's largest machine (db.r3.8xlarge) has 32 vCPUs, which according to the first formula would perhaps manage to run optimally with 100 connections in the pool, given many disks. Wouldn't it though run very badly with the "few hundred connections" from the second formula?

Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well?

If I'm misunderstanding something, please let me know. Many thanks!

"Can support" != "optimal throughput".

You can use lots of connections, but it's slower.

If you use fewer connections and queue work, you get the same amount of work done in a smaller time.

  Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well?

Either they're using a connection pooling frontend like PgBouncer in transaction pooling mode, or it won't work well.

People like big numbers though, so they'll give you big numbers.

They're actually hurting performance by doing so. PostgreSQL has some costs that scale linearly with max_connections, so even if the connections are not used it still has a performance impact.

Additionally, even idle connections have some further housekeeping costs.

If the connections are actively working, then you also have contention on system resources and on internal locks.

I routinely run into people who're having PostgreSQL performance issues - and who try to solve them by adding more connections, more workers in their application, etc. Especially people running queuing systems. It's surprisingly hard to persuade them that lowering the number of workers will make the system go faster, and that their original performance issues stemmed from having too many in the first place.