texts: mssql + sssd Ubuntu cannot login via AD group

texts:
I've got mssql  14.0.3192.2-2 setup unter Ubuntu; I have joined my machine to AD as described here.

The Windows login via SSMS and sqlcmd works if I add invididual AD users, but not if I add groups.

Also, when I try to get the "Effective Permissions" for the group under Server Properties / Permissions, I get an error "Could not obtain information about Windows NT group/user 'UserName', error code 0x80090304."

I can do kinit without problems, I can query users via id (returns all assigned groups), I can query group members via members which successfully returns all group members.

If you have any idea what could be wrong I'd really appreciate your help!

Basic config:

Domain: ad.xxxx.com
Hostname: linux.ad.xxxx.com
NetBIOS name: LINUX
DC1, DC2: heart.ad.xxxx.com, file.ad.xxxx.com
AD DNS resolves linux.ad.xxxx.com to 10.0.10.13
Reverse DNS resolves 10.0.10.13 to linux.ad.xxxx.com

The SQL server host is located in a DMZ, with ports 88, 3268, 389, 1433 open.

My mssql.keytab:

slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2 MSSQLSvc/linux.ad.xxxx.com:1433@AD.XXXX.COM
   2    2 MSSQLSvc/linux.ad.xxxx.com:1433@AD.XXXX.COM
   3    2           MSSQLSvc/LINUX:1433@AD.XXXX.COM
   4    2           MSSQLSvc/LINUX:1433@AD.XXXX.COM
   5    3                        LINUX$@AD.XXXX.COM
   6    3                        LINUX$@AD.XXXX.COM
   7    3                        LINUX$@AD.XXXX.COM
   8    3                        LINUX$@AD.XXXX.COM
   9    3                        LINUX$@AD.XXXX.COM

My /etc/krb5.keytab:

slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    3                        LINUX$@AD.XXXX.COM
   2    3                        LINUX$@AD.XXXX.COM
   3    3                        LINUX$@AD.XXXX.COM
   4    3                        LINUX$@AD.XXXX.COM
   5    3                        LINUX$@AD.XXXX.COM
   6    3                    host/LINUX@AD.XXXX.COM
   7    3                    host/LINUX@AD.XXXX.COM
   8    3                    host/LINUX@AD.XXXX.COM
   9    3                    host/LINUX@AD.XXXX.COM
  10    3                    host/LINUX@AD.XXXX.COM
  11    3         host/linux.ad.xxxx.com@AD.XXXX.COM
  12    3         host/linux.ad.xxxx.com@AD.XXXX.COM
  13    3         host/linux.ad.xxxx.com@AD.XXXX.COM
  14    3         host/linux.ad.xxxx.com@AD.XXXX.COM
  15    3         host/linux.ad.xxxx.com@AD.XXXX.COM
  16    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
  17    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
  18    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
  19    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
  20    3       RestrictedKrbHost/LINUX@AD.XXXX.COM
  21    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
  22    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
  23    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
  24    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM
  25    3 RestrictedKrbHost/linux.ad.xxxx.com@AD.XXXX.COM

In AD I have a user linux.sql which has the following SPNs assigned:

Registered ServicePrincipalNames for CN=linux.sql,CN=Users,DC=ad,DC=xxxx,DC=com:
        MSSQLSvc/LINUX:1433
        MSSQLSvc/linux.ad.xxxx.com:1433

Also, the SQL server has a machine account LINUX.


texts: SQL server changes XML structure when inserted

texts:
I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert

              <xsl:value-of select="name/n/given" />
            <xsl:text> </xsl:text>
          <xsl:value-of select="name/n/family" />

When I read it back, it looks like this

              <xsl:value-of select="name/n/given" />
          <xsl:text />
          <xsl:value-of select="name/n/family" />

Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.

Is there some way to solve this?


texts:
Regarding UploadDir & SaveDir in phpmyadmin, I have them both set to './save' with permissions at 755.

I am the only one using phpmyadmin, but still, could I make this tighter?

Thanks

PHP 5.3.3 via FCGId
phpmyadmin 3.5.1
Centos 6.3 (Linux 2.6.32.59-28)


texts: Generate CREATE DATABASE FOR ATTACH for all existing user databases

texts:
Does anyone have a TSQL script that can automatically generate the code necessary to attach ALL existing user databases using the CREATE DATABASE FOR ATTACH syntax?

example:

CREATE DATABASE [mydatabase] ON
(FILENAME=N'E:\MSSQL\Data\mydatabase.mdf'),
(FILENAME=N'D:\MSSQL.1\MSSQL\Data\mydatabase_log.ldf'),
(FILENAME=N'E:\MSSQL\Data\mydatabase_ndf.ndf')
 FOR ATTACH

I have found many examples using sp_attach_db, but none with the CREATE DATABASE FOR ATTACH syntax.

Thanks,

Craig


texts:
I am trying to catch a SqlException on a SSRS report and display it on a pop-up. So far I have not been able to find this configuration on Report Builder and I thought maybe adding some code on the Code section of the report. Please note that I am not trying to render a report from a remote location, but only continue to render the current report.

A little more context: the SSRS report connects to SQL server on the same host, and executes a stored procedure that may throw an error when invalid input is provided. By default SSRS will output the error on the report itself and nothing else. I think it will be more intuitive for my end user to have the error displayed as a pop-up and not in the report. Microsoft has this article but it is not too descriptive as I wish: https://docs.microsoft.com/en-us/sql/reporting-services/report-server-web-service-net-framework-exception-handling/best-practices/using-try-and-catch-blocks

some tentative source could be:

public void RenderReport(param1, param2){
    try {
        //pseudocode
        execute current report ...
    }
    catch (SqlException e) {
        MessageBox.Show(e.Message); // could be an alert instead, not really sure ..
    }
}

Not sure if I am just over complicating this ... Can somebody help me to figure out this?


texts: How do I know that the high number of compilation/sec is because of memory pressure not bad queries?

texts:
My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of bad ad-hoc queries that looks new to SQL Server every time it sent to be executed?

Note that:

CPU is keeping above 80%.
Target memory and total memory PerfMon counters are always equal.
PLE has a very good value.
buffer cache hit ratio is 99%
Memory grand pending is always 0


texts:
Oracle architecture has two types of process: server process or foreground process and background process, but:

  Some  people use it to refer to foreground processes, others use it
  for background  processes

This is ambiguous. Please explain to me why do the concept of shadow process exist?


texts: Why is a 'Select distinct limit 100' using tmp tables?

texts:
I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community.
The following query should check row by row, save it to memory if it's unique from the other's already selected or skip if it is a repeat. Once results 100 are in memory it should terminate the query and output the result. Instead it goes through every single result, loads them into a temporary table on the hard disk sorts them and pulls the first hundred sorted ascending. It should take a fraction of a second, not several hours.
Query
SELECT DISTINCT `some field` FROM `myTable` LIMIT 100

EXPLAIN SELECT DISTINCT `some field` FROM `myTable` LIMIT 100:
id|select_type|table   |type|possible_keys|key|key_len|ref|rows      |Extra
1 |SIMPLE     |myTable | ALL|             |   |       |   |223193820 |Using temporary

There's no reason for it to sort anything aside from that's how it's programmed. It only needs to return 100 distinct values which I could do much faster manually parsing the raw text.
If it only needs 100 distinct values it doesn't have to check 220 million rows to make sure that a value isn't duplicated in the 100 rows that it is supposed to return.


texts: SQL Server 2008 R2 DB grows overnight and slow

texts:
I am running a SQL Server 2008 R2 SP2 DB. I have a DB that was running around 19 GB in size and three days ago grew to 34 for no good reason. We typically have about 100 MB growth in the DB every month or so, so this jump up by 15 GB is unexpected. At the same time I started seeing SQL Server eating up more and more RAM from the server. It's typical to see it run at around the 6-8 GB range but all of sudden its taking up to 24 GB of the 32 GB the server has. Again unexplained. I have rebuilt all the indexes, did a FILE shrink (not a DB and never a DB), did a full backup and nothing helped. I was forced to set MaxMemory limit at 16 GB to stabilize things but I have no clue what else I can do. The only change that was ever made over the last week was that a login name that was part of a Domain Controller (Admin account) was removed from SQL Server and replaced with a local Admin account.

I don't have performance states to share at the moment but wondering what could cause the sudden bloat in the DB and what can I look into or do about it? This DB has been running for 4 years without one single issue except for now. I am not a DBA so any help would be appreciated.


texts: Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list?

texts:
My code:

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

The error:

  Msg 8101, Level 16, State 1, Line 4

  An explicit value for the identity column in table
  'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list
  is used and IDENTITY_INSERT is ON.

What gives?


texts:
"node-rdkafka": "^2.7.4"
My producer end is publishing messages to a kafka broker using a HighlevelProducer . The callback function in the producer.Produce() contains error and offset. However, how do I easily map this offset to the published message when this message offset is set after the message reaches buffer on producer side?
Also, in case of error, error message does not give any info about the message the delivery for which failed.
The only way I found was to enable delivery_reports, along with messages in it so that I know which messages got delivered. In a productiopn system with millions of messages published every day, this is very bad way to do a reconcilation.


texts:
Okay so here's my scenario. I have two applications that share some tables from the same DB. Let's call them App1 and App2. Lets also assume the DB used here is called DB1. App1 and App2.

App1 is on the same machine as DB1. App2 is on a different server but connects to DB1 remotely via PHP. This setup sometimes slows down access to DB1 from App2 and causes timeouts.

I will be creating DB2 which will be a replica of DB1 and hosted on the same machine as App2. This DB2 should be automatically synched with DB1. Changes are only going to made on DB1, so just need to push changes to DB2 regulary.

How can I do this? I'm assuming the solution to do this will be using cron jobs and PHP but I do not know what code can help do this.

Thanks all!

I am hoping someone has already done something like this and can share what they did.


texts: Postgres continuous backups - should WAL numbers be sequential after pg_start_backup call?

texts:
I'm using PostgreSQL WAL archiving and pg_start_backup as outlined in the Continuous Archiving section of the Postgres manual to backup a large database.  Twice a month we do a full backup and every other night we just copy the WALs over to our backup space so if there's ever an issue we just do a restore of the last full backup + the WALs that have been created since then.

I've noticed that the WALs are sequential except where pg_start_backup is called (or technically it may be when calling pg_stop_backup I'm not sure which) at which point it skips a few.  Is this normal behaviour?  I know when calling pg_stop_backup moves to the next WAL segment but I'd expect that to be the next number incrementally or am I missing something?

For example on the 14th of March we had the following WALs after a 1st of March full backup:
000000010000008500000090
000000010000008500000091
000000010000008500000092
000000010000008500000093

After the backup it then went to:
000000010000008500000096
000000010000008500000096.00000020.backup

After this normal WAL creation resumed:
000000010000008500000097
000000010000008500000098
and so on

So should there be a 94 or 95 in this case?

I'm in the process of doing a test restore but as that involves copying 60GB (compressed) of data between two servers in different continents, I'd prefer to know sooner rather than later if I'm going to have an issue!


texts:
I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient at the moment as it is using fixed values.

Is there a way to get the code to auto generate a script that produces several statements for users, by looping through my table of users and extracting the usernames, placing these values into the declared variable U_USERNAME?

Would I have to use a SELECT INTO the U_USERNAME variable? How would that look syntax wise in this instance?

DECLARE
u_perm VARCHAR2(25);
u_table VARCHAR2(25);
u_username VARCHAR2(25);
u_code VARCHAR2(2000);

BEGIN
u_perm := 'INSERT';
u_username := 'J123';
u_table := 'TABLE_NAME';
u_code :='';
dbms_output.put_line('Grant ' || u_perm || ' on ' || u_table || ' to ' ||
u_username || ';' );

u_code := ('Grant ' || u_perm || ' on ' || u_table || ' to ' || u_username ||
';') ;
dbms_output.put_line ('u_code = ' || u_code|| '');

END;

---The user table that will store the username I will being trying to extract from.

CREATE TABLE  tblss_user(
user_id       INTEGER NOT NULL,
user_name     VARCHAR(15),
reg_date      DATE


texts:
I am having a heck of a time trying to convert a FLOAT(53) field to a VARCHAR where there are no trailing zeros or scientific notation.

The field could have a number that is really big or really small. I need to convert it to VARCHAR and show, at most, 5 digits after the decimal but no trailing zeros. So 123.456789 should be 123.45678 and 123456.78 should be 123456.78.

I have an SQL Fiddle showing what I have tried: http://sqlfiddle.com/#!6/bd392/4


texts: Create table permission for a user in specific schema

texts:
I want to give Create , alter and drop permission to user A on a schema1 of a database.
I guess this question has been already asked, and what i have found is to Grant Alter to the schema and also grant create table to the User A:
GRANT ALTER,  DELETE, EXECUTE, INSERT,  SELECT, UPDATE ON SCHEMA::schema1 TO user A;

GRANT CREATE TABLE TO User A;

If this is right then , will the userA will be able to create table on other schemas too?


texts: Controlling or deugging validation behavior of SSIS packages

texts:
After investigating a production issue, I have found a number of references to SSIS packages getting stuck in 'validation' when run with DTEXEC or through as SQL Agent job steps.

Specifics of my situation aside, is there a technique to make this more robust so that in a production environment, I am notified if a job starts, but does not complete?

Is it possible to have a timeout period on validation so that the execution explicitly fails? Or have a way to specify number of retries?


texts: import .dmp in Toad : Invalid format of Import utility name Verify that ORACLE_HOME is properly set

texts:
When I tried to import a DMP file using TOAD (9.0.1) Import Utility Wizard I get an error

Invalid format of Import utility name  Verify that ORACLE_HOME is
properly set

Any suggestions please?
I have attached a screen print.


texts:
I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects.

The table in question is pretty small, with only 5396 records.  However, I'm seeing queries like this show up in our slow query log:

# Query_time: 17.482010  Lock_time: 0.000088  Rows_sent: 0  Rows_examined: 0
UPDATE `submissions` SET `updated_at` = '2011-11-15 20:12:44', `status` = 'eligible',   `published_at` = '2011-11-15 20:12:44' WHERE `submissions`.`id` = 5334

Submissions.id is the primary key.

We are relatively read heavy, so we had query_cache enabled.  I've turned that off and it helped somewhat, but the updates still seem to be way too slow.

It only seems to effect this particular update, and the database overall has fairly light load with an average of 20 queries per second.

Any ideas on places to look would be greatly appreciated!

Clarifications

Mysql version: mysql  Ver 14.12 Distrib 5.0.51, for pc-linux-gnu (x86_64) using readline 5.2

Applications talking to the database are built with Ruby on Rails.  No explicit table locks.


texts: How to handle temporary tables with mutliple connections?

texts:
In Is there a way to hint to query optimizer to MySQL which constraints should be done first? I created a subquery.

The subquery is often not optimized.

I think a better way is to create a temporary table and then query that temporary table.

The problem is say I create a temporary table and name that "BusinessesWithinABox" table, which are businesses in the area I am interested in, say 10 by 10 km squares.

Say I have 300 users.

All of which create this temporary tables named BusinessWithinABox. Yes I know that the tables are temporary.

Then when I do query, which tables go which?

Actually some tutorial detail on how to create temporary tables and run and use that in PhP would be fine.


texts: xml parameter to distributed query - difference between SQL2005 & SQL2012

texts:
I have inherited an old SQL2005 Enterprise database (on Windows 2003) that allows an xml parameter to a distributed query such as this:

exec sp_executesql 'exec [server].[database].dbo.sproc @Id=@Id,@PItems=@PItems output','@PId bigint, @PItems xml output', valueOf@PId, valueOf@PItems output

This no longer works in SQL2012, the error says:

  Msg 9512, Level 16, State 1, Line 1 Xml data type is not supported as
  a parameter to remote calls.

My question: is there a permission or tickbox (facet?) to allow xml parameters to distributed queries or does 2012 no longer support it?

Note: the tables do not have any columns of datatype xml. Only the parameter is xml.


texts: Total Memory used by SQL Server (64 bit)

texts:
My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named sys.dm_os_process_memor that gives you physical working set.

But I am not sure ...

  is there a DMV or perf counter that could tell me total (buffer pool +
  non buffer pool i.e. MemToLeave) physical memory used by SQL Server
  process for 64 bit SQL Server 2008 R2 and SQL 2012 running on 64 bit
  Windows OS?


texts: What impact does using backup preference on Availability Groups have?

texts:
I am using Availability Groups (database level) SQL Server 2017:

I have set my availability group to "prefer secondary". I have allowed read access on replica.
I have created a maintenance plan with full backups on all databases on replica using "copy only" and then also on primary.

But now I'm wondering does AG actually make any decisions? Both Mainenance plans will run and from what I can tell won't change "copy only" to primary's maintenance plan if there's a failover.

Besides just preventing me from running backups if I change the setting..... besides enforcing rules for what [another] dba may or may not be able to do: what is the point of this?

Or, did I setup my backups totally wrong?


texts: Are there situations where queries are formed geometrically?

texts:
The topic came up in this other question. We were discussing sign value on a primary key having any significance.

If there are good times to use geometry in building queries the sign value could be somewhat useful. Are there any such times?

For instance I could query rows based on a Doppler pattern, increasingly skipping rows as I get further away from the starting point.


texts: How do you use pvkconverter.exe - not working for me?

texts:
I tried to following this link to convert a .pfx file to the .cer and .pvk file pair.

When I run this command:

"C:\Program Files (x86)\Microsoft\PVKConverter\PVKConverter.exe" -i TDE_Cert.pfx -o Out_Cert -d mydecryptpassw

It does not produce any file, nor does it give any error. Could someone help?

I actually had the above command wrapped inside a .bat file.  This .bat file and the .pfx are both in the same directory.  I uninstalled the x86 install and re-installed the x64 version thinking I should be running the x64 version on my Win7 Prof SP1 machine.  Same issue.  When I deliberately misspelled the password or even omit it, no errors.  Nothing!  I don't know what's going on.


texts: How to create lots of SQL-Developer Connections quickly?

texts:
I wonder if there is a way to create lots of SQL-Developer connections in "one shot" ?

  Eg: By modifying a config file or password file.


texts: What order do foreign key constraints run in? Why does my foreign key not work after import?

texts:
I have two tables tags and tag_memberships in a PostgreSQL database. On the tag_memberships table, I have two constraints. The first is this:

ALTER TABLE tag_memberships
  ADD CONSTRAINT tag_memberships_tag_id_fkey FOREIGN KEY (tag_id)
      REFERENCES tags (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE

Which means when a tag is deleted, delete all tag memberships that are referencing it.

The second constraint is:

ALTER TABLE tag_memberships
  ADD CONSTRAINT tag_memberships_organization_id_fkey1 FOREIGN KEY (organization_id, tag_id)
      REFERENCES tags (organization_id, id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION;

The purpose of this constraint is to ensure that the organization_id on this record will always be the same as the one on the tag it is referencing.

Now these constraints worked fine until I moved my database to a new laptop. Now I get this error on deletion:

  ERROR: update or delete on table "tags" violates foreign key
  constraint "tag_memberships_organization_id_fkey1" on table
  "tag_memberships"

  Detail: Key (organization_id,
  id)=(38712483-a0f3-46d3-b9fc-6085327475b0,
  6936db98-000e-4490-b439-eab99cdac8ce) is still referenced from table
  "tag_memberships".

Why would the constraints suddenly not work? Is it because when I re-created the database they were created in a difference order and that is important for foreign key checks?

-- Create table scripts

CREATE TABLE tags
(
  id uuid NOT NULL,
  name character varying(255) NOT NULL,
  organization_id uuid NOT NULL, -- tenant id
  PRIMARY KEY (id)
);

CREATE TABLE tag_memberships (
  id uuid NOT NULL,
  tag_id uuid NOT NULL,
  template_id uuid NOT NULL,
  organization_id uuid NOT NULL, -- tenant id
  PRIMARY KEY (id)
);


texts: How to change MySql Master-Master Replication to Master-Slave Replication

texts:
I currently have MySql Master-Master replication set-up with Read_only on Master2.
There were lot of sync issues so I've stopped replication from Master2 to Master1 by stopping the Slave in Master1. Master1 is currently replicating to Master2 with no issues.
Is this enough or is there another best way to revert to Master-Slave replication.
Should I run RESET SLAVE on Master1 to completely stop Replication from Master2 to Master1.


texts: "When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained"?

texts:
The MSDN online article "Snapshot Isolation in SQL Server" states:

"An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled"

Isn't it self-contradictory paragraph ("until" vs. "retained")?

Then, if "the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained" after closing the connection and returning it to a pool, how it should be understood:

that the default isolation level will have arbitrary value (different connections in the pool will have different isolation levels, and its value will depend on the connection being re-opened)?
or all the default values on all connection in thу pool will be changed to last one? but again quite unknown before hand?


texts: Database design: 4 types of users but have different functionality , separate or one table?

texts:
I have 4 types of users:

Admins , normal user , company , service provider

Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share some attributes too (id .company name ,phone ,fax ,mail ).

They interact too with other entities in application to access some feature like post job or event or apply for it

Is it better to put them all in one user table like tbl_users or is it better to create separate table to every one ? or add to two tables one for (Admins and normal user) and other for ( company and service provider)


texts:
I would to hear the solution for the below requirement.

I have table A with key number and Fruits, table B with Fruits and rating with the below data. I have to retrieve result with least rating from B mapping keynumber A by based on fruits.

My result should be like

Keynumber   Fruits  Rating
123         Orange  4
456         Apricot 2

Table A

Key number  Country
123         Apple
123         Orange
456         Cherry
456         Apricot

Table B

Country Rating
Apple   5
Orange  4
Cherry  8
Apricot 2

I used the below query but it is displaying the two records of the same keynumber

select A."key number", A.fruits, B.fruits, B."rating from"
table A
  left join table B on A.fruits = B.fruits
order by a."key number"

Keynumber   Country Rating
123 Orange  4
123 Apple   5
456 Apricot 2
456 Cherry  8


texts: Arbitrary queries on n:m relationship, including "all" and "any"

texts:
I'm using postgres >= 9.6. I have tasks, tags and task_tags tables, for a typical n:m relationship between tasks and tags.

I'd like to be able to programmatically build queries against the tasks table that supports queries against the actual fields of tasks, but also on the tags (tag names) of a task.

Queries on the task fields themselves are straight-forward. Queries on the tags for a "does it have tag A?" are also straight-forward. What I am struggling with is coming up with a select/query structure that would allow me to also match things like "does it have tag A and tag B"?

The best I've come up with is a lateral join on a subquery with an array aggregation, and then using array matching functions, e.g.:

SELECT DISTINCT ON (tasks.id) tasks.*
FROM   tasks, LATERAL (SELECT array_agg(tags.name) AS tags
                       FROM   task_tags
                       INNER JOIN tags
                       ON task_tags.tag_id = tags.id
                       WHERE task_tags.task_id = tasks.id
                       GROUP BY task_tags.task_id) tt
WHERE  tt.tags @> array['tag1'::varchar, 'tag3'::varchar];

That way, it should be possible to programmatically build a WHERE clause (using tasks.* and tt.tags) satisfying all of the conditions of the user-provided "query".

However, I'm not sure if this is the best way of doing it - thoughts? Is this query any efficient? Is there any index I could create that would improve it?

Similarly, is there any way at all of making it work with wildcards against the tag names? Normal array matching wouldn't allow that, and solutions I've seen suggest using unnest (or, well, not using arrays in the first place), but then I'd lose the ability of saying "it needs to have both tagA and tagB".

Is there any other way of building a query on these relationships that would allow that kind of "both tagA and tagB" matching?


texts:
My ibdata1 file is keep on increasing in size and for a temporary solution, we want to move the file to a separate location with more hard disk space.

But, when I change the my.cnf file parameters data_dir and innodb_data_home_dir and restart the service, it is not starting and throwing errors like

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
ERROR! The server quit without updating PID file

Please help. I need it immediately as it is the produciton database and the space is almost full.

Here is the configuration file for your reference:

[mysqld]
port=3316
datadir=/data/databases/mysql
socket=/var/lib/mysql/mysql.sock
user=root
slow_query_log = 0
long_query_time = 10
log-output = FILE
sql_mode = STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_UNSIGNED_SUBTRACTION
max_sp_recursion_depth = 40
group_concat_max_len = 30000
max_tmp_tables = 64
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 256
sort_buffer_size = 8M
read_buffer_size = 8M
join_buffer_size = 8M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size = 16M
binlog_cache_size = 8M
thread_stack = 384K
thread_concurrency = 23
innodb_thread_concurrency = 46
innodb_data_home_dir = /data/databases/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/databases/mysql
innodb_buffer_pool_size = 30G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 600
innodb_rollback_on_timeout = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysqlhotcopy]
interactive-timeout

MySQL version is 5.5.20


texts: Whats the difference between the following queries? Getting the top 3 salaries for different department

texts:
I am practicing SQL and came across this issue. I am trying to find the top 3 salaries for each department. The table structure is basic with one employee table with columns (Id,name,Salary,DepartmentId) and a department Table with columns (ID,DepartmentName). In one query I use a join on the departmentId column, but this query takes longer than when I don't use a join and simply use two table as stated in the second query. My understanding was both are one and the same thing. Please shed some light about the difference between these two queries. What makes one faster than the other?

SELECT d.name as Department, e.name as Employee ,e.salary as Salary
FROM Employee e , Department d
where e.DepartmentId=d.Id and
    (SELECT COUNT(distinct Salary)
     FROM Employee
     WHERE Salary > e.Salary
     AND DepartmentId=e.DepartmentId) < 3

The above query works well but when I write it in the similar way it takes long time.

SELECT d.name as Department, e.name as Employee ,e.salary as Salary
FROM Employee e
JOIN Department d
ON e.DepartmentId=d.Id
AND
(SELECT COUNT(distinct Salary)
 FROM Employee
 WHERE Salary > e.Salary
 AND DepartmentId=e.DepartmentId) < 3


texts: How do I tell which versions support a given DMV column?

texts:
Each version of SQL Server potentially adds new columns to the DMVs. These new columns are available even if you change the compatibility level of the database to a previous version.

Is there a list somewhere that shows which columns are available for a given version of SQL Server?


texts: Finding the bottleneck by analyzing Munin graphs

texts:
We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had this issue for a couple of weeks. At first we got problems like

  FATAL: remaining connection slots are reserved for non-replication superuser connections

But after optimising the code by using memcached to let of some of the load from postgres as well as making som smarter queries, we got rid of this problem. The postgresql log of slow queries looks a lot nicer now, but we are still experiencing a lot of timed out requests during peak hours. So the question is: Where is the bottleneck?

We’ve installed munin to log a lot of parameters to check the health status but acctually, we don’t get any closure out of this.

Is there anything that look strange in the graphs?
What should we focus on gettings more efficient?
Is there any indications on what may cause the issues during peak
hours? (19:00 – 23:00)

Look at the benchmark here: http://bit.ly/WwJPKA

Thanks!

Some info about our current setup:

8 CORES
16GB RAM
100 GB, RAID-10 * SAS 15.000 RPM
PostgreSQL 9.1.5

For more info about our PostgreSQL.conf checkout this


texts: Slave start to generate relay log files but not master server

texts:
I am settings up two new servers. One is the master and the other is the slave. Below is the config file for both.
Master DB

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=1
binlog_do_db=data1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave DB

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=2
master-host = 202.*.*.*
master-user = replicationUser1
master-password = ********
master-port = 3306
replicate-do-db=data1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

So first in the master I run this reset master command it gave me this error. reset master;
ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER. Then on the slave I first ran stop master then reset slave then start slave. THen I notice this files are generated in the slave master.info,mysqld-relay-bin.000001, mysqld-relay-bin.index and relay-log.info. In addition I have also ran this command on slave and it gave me no errors.

change master to MASTER_HOST='202.*.*.*', MASTER_USER='replicationUser1', MASTER_PASSWORD='******',

What should I do now I am quite stuck do not know how to move it next? Thank you.


texts:
I have daily full backups going on for my Oracle 12c databases on linux. Daily retention on disk is 6 days.

This is what happens:

Full backup happens at location

  /orcl_bkup/backup_full

After each day's full backup gets over, I make sure that the above mentioned path only contains that day's backup pieces in that path and the older backups will be moved to another folder

  /orcl_bkup/old_bkup

The reason for the above mentioned movement of backup pieces is necessary for my OS team for some activity at their end.

My question is that, in this scenario, will my control file lose the information of older backups? Do I need to do catalog start with the /orcl_bkup/old_bkup and catalog the backup location to delete the obsolete backups?


texts: PowerShell: Test-SqlAvailabilityReplica running script never completes

texts:
I'm still fairly new to PowerShell so I'm unsure of all the troubleshooting steps I can take to solve this problem. I would like to use the below command to determine if the replica is primary or not.

I believe I have successfully loaded both the provider and smo. Intellisense appears to recognize the command.

If I run the following command

Test-SqlAvailabilityReplica

The script will run but will never finish. I'm running it on one of the replicas so I'm assuming it should just be grabbing the local host. If I add the -WhatIf flag it still runs without finishing.

I've also tried Test-SqlAvailabilityGroup and it never finishes either.

What can I do to get this command to work? How do I find out what is causing it to lock up?

EDIT:
It looks like my issue is with the path.... This path location works

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\FooAg\AvailabilityReplicas\

but then I try using this path location and it fails.

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\FooAg\AvailabilityReplicas\MainReplica

Edit2:

Are we sure it's

<ServerName>

at the end? I can set my location to

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabiltyGroups\FooAg\AvailabilityReplicas\

just fine. When I do a DIR to see what is available I see my two replicas listed as

"FooNode1\FooInstance"

and

"FooNode2\FooInstance"

So for the "MainReplica" part of the path do I replace that with FooNode1 or FooNode1\FooInstance?


texts: 2 similar queries in large sql server table - 1 takes too long

texts:
I have a huge table in my database that contains distances between cities.  This enables my application to find nearby cities around the world when a starting city is selected.  It contains 4 columns - ID, StartCityID, EndCityID and Distance and contains about 120 million rows.  I've got indexes set up on the startcityID, endcityID, another one for both, and another one each for startcity + distance, and endcity + distance.  (This is my first real dealings with indexes so not 100% sure if I'm doing it correctly).

Anyway - I do the following 2 queries:

Select distinct StartCityID
From Distances where EndCityID = 23485

and

Select distinct EndCityID
From Distances where StartCityID = 20045

They both return the same number of cityIDs, but the top one takes 35 seconds to do, and the bottom one returns results immediately.  When I look at the indexes, they seem to be set up to serve startCity and endCity in the same way.  Anyone know why they might be acting differently? I'm at a loss...

NB - this may offer more insight, but the one that takes 35 seconds - if I press execute again straight away with the same ID, it returns results immediately as well that time.  Unfortunately that isn't good enough for my website but it may be useful information.

Thanks


texts:
I am having the table structure

id |  name  |   desig    |  age
1  |   John | supervisor |   M/28
2  | Deborah | Manager   |  F/44
3  | Joan   |Area Manager|  M/52

I am trying to get the count of employees by their desig grouped by age .
Here is the query that is working

SELECT sum(case when desig='Manager' then 1 else 0 end ) AS Manager
     , sum(case when desig='Area Manager' then 1 else 0 end ) AS Amanager
     , sum(case when desig='Superwisor' then 1 else 0 end ) AS SW
FROM employee_details
WHERE substring_index(age,'/',-1) < 30

So that i can get the count of employees grouped by designation below 30. I need to get similar counts for the age group between 30-40 , 40-50 and above 50 . Should i go for four different SELECT  queries or is there a different ,more performant way to do this .


texts:
I am unsure reading the MySQL documentation if creating a user with the GRANT option will give them the power to create users and grant privileges, or change the privileges of other users databases.

I have been creating databases for users like this

CREATE DATABASE user;
USE user;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password'
WITH GRANT OPTION;

Is this the best way of doing it or have I just given my users too much control? They are people I am hosting sites for. Thankfully at this point they are trustworthy. I use quotas.

Edit: I have realized I have been granting users access to all databases. This is obviously stupid I should be using this:

GRANT ALL PRIVILEGES ON database.* to 'user'@localhost' IDENTIFIED BY 'password'

What is the simplest way to revoke privileges for every user except root so I can quickly end this catastrophic rookie mistake?


texts: Difference between SQL batch, statement and RPC?

texts:
What is the difference between SQL batch, T-SQL statement and Remote Procedure Call?
How can I tell if part of the T-SQL code is a batch or statement?


texts:
I have 2 tables Customer and Customerkpi

In both tables we have the column Customer_id which should contain unique values.

Customer_id in Customerkpi is a foreign key of the Customer_id of the table Customer which is the primary key.

I need to create indexes on customerkpi in the best way.

Should I create a primary key on Customer_id and mentioned also the foreign key in this way it creates automatically a clustered index, or should I just declare it as a foreign key and then I create a cluster unique index on Customer_id.

which solution give the best performance?


texts: How to change the default output format for boolean values

texts:
In a Postgres database, I know how to use case statements to get only TRUE or FALSE for a boolean field but was wondering whether there is a cleaner way to set this up like changing any configuration in the db.

Update: in Postgres, a the boolean representation for true is not only TRUE but also: yes, y, on, "t". So, I am inserting values with value TRUE. However, the SELECT command returns those values as "t".


texts: Why sys.columns.is_nullable can be null?

texts:
I'm writing a query that will be mapped to a C# class, and when getting the type of the query's columns, it results that some columns in sys.columns can be NULL

is_nullable
is_replicated
is_merge_published
is_dts_replicated

And in the same sys.columns there are others that are NOT NULL

is_rowguidcol
is_identity
is_computed

NOTE: Those columns are bit

I think they should always be NOT NULL, because SQL Server must know the columns' properties.

So, why some/those columns in sys.columns can be NULL and/or in which cases they will be?


texts: I will be changing the existing SQL Server Service accounts from the default to Domain accounts from the SSCM which i believe will set all the required permission. One thing I am not sure whether the service account needs to be added as database logins and if so what are the minimum permission needed.


texts: Can I take backup of those tables which are created with NOLOGGING in Oracle?

texts:
Can I take backup of those tables with data which were created with NOLOGIING option in Oracle.

Is it true that backup can not be taken for these tables and I can not restore my data if I have some crash or corruption.


texts: SQL DR Availability Group cluster validation failure

texts:
I'm new to DBA out of necessity at work so please explain or point me in the right resources if you see anything I ask below is wrong:

I'm trying to setup a test environment for Disaster Recovery using Availability Group for MS SQL 2016 Enterprise.  I have two nodes at two different subnets, one is primary and one is secondary.

This is my current database setup, it shows the database is available and replicating from primary to secondary replica.  I also setup the AG's listener and that my application is currently using this listener in its connection string; The application is working normal.

Here is how it looks in my Window Failover Cluster Manager.

My first question is: Is it normal to see one of the server (10.30.8.90) offline? in my opinion it is okay because in a listener, you only want the primary replica VM to be online while the rest of the replicas are offline.

My second question is:  When I run the Validate cluster wizard on this cluster, I keep getting error at the network level.  Is it normal and if it isn't how can I correct it? I made sure:

All firewall are turned off when I run the validation to rule out any
firewall blocking certain TCP or UDP port.
I can ping using DNS and IP between the two nodes.

Edit: Here is my latest attempt of testing the tcp port using powershell and it works from both nodes:


texts: Error during CSSCAN of Oracle11 database

texts:
I have an issue when trying to change the character set on an Oracle 11.2 database.

Here are the particulars:

Oracle 11.2 64 bit, running under Oracle Linux 7
This is a new installation (i.e. the only users are those that were created during the Oracle install), using the defaults given during the Oracle installation
The current NLS_CHARACTERSET is AL32UTF8, and I need to
convert it to either WE8MSWIN1252 or WE8DEC

Most of my experience with Oracle has been with Windows, and I've never had to change the character sets before.

Now I can't just change the character set from AL32UTF8 to WE8DEC or WE8MSWIN1252 because those aren't supersets. So, I ran CSINST.SQL, and ran the CSSCAN command. Most of the scan was successful, but I ended up receiving the following errors:

  . processs 1 scanning XDB.PRIN_PT[AAAN9DAACAAAEGoAAA]
  ORA-30967: operation directly on the Path Table is disallowed
  CSS-00144: failed to scan table XDB.PRIN_PT

Does anyone have any idea what the problem can be and/or how to fix it?

I've done some searching.I did find someone who has mentioned deleting and recreating XML indexes, but I've never done that.


texts: How to improve performance when joining against coalesce

texts:
In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. This is probably less efficient than making a single view with all of the rules built in, but it's much easier to think about everything this way.

Here's the main view:

CREATE VIEW access AS
SELECT c.id AS content_id, u.id AS user_id,
    LEAST(MIN(simple.relevant_date),
        MIN(with_prereq.relevant_date),
        MIN(with_delay.relevant_date),
        MIN(with_both.relevant_date)) AS relevant_date
FROM content AS c
    LEFT OUTER JOIN simple ON simple.content_id = c.id
    LEFT OUTER JOIN with_prereq ON with_prereq.content_id = c.id
    LEFT OUTER JOIN with_delay ON with_delay.content_id = c.id
    LEFT OUTER JOIN with_both ON with_both.content_id = c.id
    CROSS JOIN base_user AS u
WHERE u.id = COALESCE(
    simple.user_id, with_delay.user_id, with_prereq.user_id,
    with_both.user_id)
GROUP BY c.id, u.id;

Each of the "rule views" by themselves are fast, so I'm pretty sure the least efficient step is the u.id = COALESCE(... that I'm using to join. (The logic of relevant_date is bogus and irrelevant, but enough to prevent the answer of "just UNION them" from being trivial; I need to calculate various attributes differently depending on which rule is the source of access.) In any case, the end result needs to be a distinct set of content/user ids with some fiddly aggregates.

This is slow (~7s) on our (~10K users, ~10K content) data. What can I do to speed this up? I don't want to cache, I don't want to combine the "rule" views, and I couldn't figure out how to use UNION.

Here's the individual views that are pulled together by the above.

CREATE VIEW simple AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    cc.relevant_date AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NULL OR cc.delay = 0)
    AND cc.prereq_id IS NULL;

CREATE VIEW with_delay AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    cu.relevant_date AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NOT NULL AND cc.delay > 0)
    AND cc.prereq_id IS NULL;

CREATE VIEW with_prereq AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    GREATEST(cu.relevant_date, cc.relevant_date) AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NULL OR cc.delay = 0)
    AND cc.prereq_id IS NOT NULL;

CREATE VIEW with_both AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    LEAST(cu.relevant_date, cc.relevant_date) AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NOT NULL AND cc.delay > 0)
    AND cc.prereq_id IS NOT NULL;

Bonus: here's some minimal create statements to get this to be explainable:

CREATE TABLE content (
    id SERIAL
);
CREATE TABLE base_user (
    id SERIAL
);
CREATE TABLE circle (
    id SERIAL
);
CREATE TABLE circleuser (
    circle_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    relevant_date TIMESTAMP with time zone
);
CREATE TABLE circlecontent (
    circle_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL,
    delay INTEGER,
    prereq_id INTEGER,
    relevant_date TIMESTAMP with time zone
);

Which should be enough to get EXPLAIN SELECT * FROM access WHERE user_id = 1 to show you the mess of operations.


texts: Optimization of complicated sums and counts in related tables

texts:
I have a categories table, which have two columns, id and name.

I have a types table, which have three columns, id, category_id and points.

I have a products table, which have three columns, id, type_id and customer_id.

This means each product has a type, and each type has a category. One category has many types, one type has many products.

I am using MySQL and PHP.

From PHP, given a customer_id, I want to know how much points that customer has in each category separately (and display a table to the user, for example).

My solution:
I would first do a query to find what are all category ids, then for each category_id I would find all types inside that category (that is, all types with the given category_id); then for each type_id I would count how many products have both that type_id and the given customer_id, and I would multiply that amount for the points of the given type_id. Adding all the results for each type_id, I have in the end the total points the given customer has on that category_id. And as I said, I would have to do this for each category_id. My solution will work, but seems to me that it would be VERY slow. I am looking for advice on how to solve this problem in a better way - with less queries, or faster queries, or both. The way it is now, it can potentially do as many queries as the amount of categories.

Example:

/* TABLE: categories */

name           id
A              1
B              2
C              3

/* TABLE: types */

points         id          category_id
1000             1                1
500              2                1
200              3                3
50               4                2
20               5                3
1                6                3

/* TABLE: products */

id           type_id          customer_id
1               2                   13
2               4                   13
3               5                   13
4               6                   13
5               5                   13
6               3                   13
7               4                   13
8               5                   13
9               6                   13

If the given customer_id is 13, I want get something like the following:

500 total points in category A
100 total points in category B
262 total points in category C

1. What is a good way to do this without changing how my database is structured?

2. Is there a good way to change my database design to allow a better way to do this? (of course I have other fields in the tables that I omitted for simplicity).


texts: Was my Postgres cluster built with the ICU libraries available for Postgres 10 and later?

texts:
I wonder if my installation of Postgres 10 Beta 2 has been built to include the new International Components for Unicode (ICU) collations. For background info, see More robust collations with ICU support in PostgreSQL 10 by Peter Eisentraut.
I used the pg_config utility to detect the build info. When running pg_config --configure I get output where the last line is 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'.
➠ Does this mean I have the ICU libraries installed, or not?

/Library/PostgreSQL/10Beta2/bin/pg_config --configure
'--with-icu' '--prefix=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx' '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--with-bonjour' '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-uuid=e2fs' '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include:/opt/local/Current/include/security' '--docdir=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx/doc/postgresql' '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 'CFLAGS=-isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk -mmacosx-version-min=10.8 -arch i386 -arch x86_64 -O2' 'LDFLAGS=-L/opt/local/Current/lib' 'ICU_CFLAGS=-I/opt/local/Current/include' 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'

➠ Is there another way to detect if my Postgres has the ICU collations available? Some way via SQL?
When I execute SELECT * FROM pg_collation ;, of the 845 locale names listed, 575 end in -x-icu. Does that happen only if the ICU libraries are in place?


texts: VACUUM VERBOSE outputs, nonremovable "dead row versions cannot be removed yet"?

texts:
I have a Postgres 9.2 DB where a certain table has lots of nonremovable dead rows:

# SELECT * FROM public.pgstattuple('mytable');
 table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 2850512896 |      283439 | 100900882 |          3.54 |          2537195 |     2666909495 |              93.56 |   50480156 |         1.77
(1 row)

Normal vacuuming also shows lots of nonremovable dead rows:

# VACUUM VERBOSE mytable;
[...]
INFO:  "mytable": found 0 removable, 2404332 nonremovable row versions in 309938 out of 316307 pages
DETAIL:  2298005 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.90s/2.05u sec elapsed 16.79 sec.
[...]

The table only has around 300.000 actual data rows, but 2.3 million dead rows (and this appears to make certain queries very slow).

According to SELECT * FROM pg_stat_activity where xact_start is not null and datname = 'mydb' order by xact_start; there is no old transaction accessing the database. The oldest transactions are some minutes old and haven't modified anything on the table yet.

I've also checked select * from pg_prepared_xacts (to check for prepared transactions) and select * from pg_stat_replication (to check for pending replications), both of which are empty.

There are lots of inserts, updates and deletes performed on that table, so I can understand that lots of dead rows are being created. But why aren't they removed by the VACUUM command?


texts: postgresql: there is no unique constraint matching given keys for referenced table

texts:
I don't know how to resolve my situation:

create Table VIPUsers(
  id int primary key,
  parent int references Parent(id)
);

create Table Parent(
  id int not null,
  childType int not null,/*indicates ChildA or ChildB*/
  unique (id,childType)
);
create index on Parent(id);

create Table ChildA(
  parent int references Parent(id),
  propertyA int
);

create Table ChildB(
  parent int references Parent(id),
  propertyB date
);

There is M to N relationship between tables VIPUsers and ChildA, ChildB.
Table Parent is a junction table containing information whether VIPUsers uses instances of ChildA and/or instances of ChildB

I need to add rows:

insert into Parent (id,subtype) values (1,1)
insert into Parent (id,subtype) values (1,2)

This gave me an error 'duplicate primary key' so I deleted the primary key and I'd use an index instead, just as it is shown above.

But now I can't create tables VIPUsers, ChildA, ChildB as I get the error

'there is no unique constraint matching given keys for referenced table ...'

I know it is because VIPUsers would not reference a unique row. But that's what I need. I need a join where for each parent I get one or more childTypes. For example:

  VIPUsers ParentId   childType
    1         1          1
    1         1          2

If I make Parent.id a primary key, there will be only one childType for each user. If I remove primary key, DBMS starts complaining about referencing a non unique column.

My goal is to then cross join the above example table with ChildA or ChildB on ParentId to get table like this:

 VIPUsers  ParentId   childType  ChildA.propertyA  ChildB.propertyB
    1         1          1             50                 null
    1         1          2            null              2/2/2017

And based on the childType I'm able to tell what kind of information this row represents.

I don't like putting childType into each Child table because it would allow for nonsense values like ChildA being of type ChildB and vice versa.

What are the usual ways of going about this?


texts: What is the difference between membership and securables? Does one override the other?

texts:
When setting permissions for a database user, what is the difference between 'Membership' and 'Securables'?

From what I've read in the MSDN documentation (Securables and Database-Level Roles), I gather 'Membership' options are database-wide, whereas 'Securables' are more specific. If this is the case, which would be overridden if there was a conflict?

For example, if I wanted to set a user's access for a particular table to be read-only, but all others read-write, would I select 'db-datareader' and 'db-datawriter', and then specify a securable for the read-only table (with all options except 'Select' set to 'Deny')?


texts: Why do we not store names in a separate table?

texts: Practically, I would never store names for customers in a separate table.  But why not?  It's a one to many relationship (a person can have one name but that name can be used by multiple people).  Isn't it proper design to separate one to many relationships over multiple tables, or am I missing something?


texts:
When I installed MySQL 8.0, I selected sha256 password authentication method instead of legacy, When I tried to return to native password authentication it shows an error message:
mysql: [ERROR] unknown variable 'default-authentication-plugin=mysql_native_password'.
when I type (mysql -u root -p) command in Debian 10 Linux.


texts: Maintaining integrity between 3 tables when reference could be through or direct

texts:
Say we have the following 3 tables:

tUsers (ID, name, countryID, regionID)
------
1  |  Joe  |  1   |  1
2  |  Sue  | NULL |  2
3  |  Bob  |  2   |  NULL
4  |  Amy  | NULL |  NULL

tCountries (ID, name, regionID)
------
1  |  USA   |  1
2  |  China |  3

tRegions (ID, name)
------
1  |  North America
2  |  Central America
3  |  East Asia
4  |  Europe

Is there a way from the DB to prevent a user from having mismatched Country/Region while still allowing one or both to be NULL?  For example, the query UPDATE tUsers SET regionID=4 WHERE ID = 3 would fail since Bob's country is China which is not in region Europe.


texts: How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?

texts:
I am having the exact same problem as this person here: How can I prevent mysqldump from prepending the database name in CREATE VIEW?

I am using MariaDB 10.4.7 x64 on Windows. For just ONE of my views, whenever I run mysqldump, it always prepends the database name to each table. I have tried dropping and recreating the view to no avail. This is what the result from mysqldump looks like.

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `view_x`
    AS
(
SELECT * FROM
databasename.table1
LEFT JOIN databasename.table2 ON databasename.table2.id = databasename.table1.id
...
);

This is causing an issue when I import the database into a test server using a different database name because the previous database name is hard coded into the view.

Has anyone ever experienced this? I'm afraid my googlefu has failed me.

EDIT: After looking at all of my views, this particular view (and the only view) uses derived tables in the FROM clause. After removing these derived queries and all traces from the SELECT statement and recreating the view, the database name was no longer being prepended to the tables. So...I don't know if this is a bug or expected behavior. I think I will file a bug report.


texts: Using psql, how to test if I am using streaming, hot_standby or warm replication?

texts:
I am on Postgresql version 10.4

How can I know using psql that if the server I am using is in a streaming, hot_standby, or warm replication mode?

I really have no idea what these replications do other than hot_standby is only a read-only server, and it and warm use WAL archives and streaming archive does not use WAL.

And also, are these the only modes of replication or are there some other modes of replication too?

If possible, explain like I am five because this is my second day of using Postgresql.


texts: set session wait_timeout inside of phpmyadmin for export/import

texts:
I need to increase wait_timeout variable in mysql only when access mysql from phpmyadmin. I don't want to edit my.cnf file or set it globaly in mysql server.

I've read documentation, but haven't found if I can run any queries after connection to specific database.

Any help is appreciated.

Application details below:

  phpMyAdmin version 4.4.1

  mysql is percona 5.6.


texts: Data deleted with sqlplus still showing up in SQL Developer

texts:
I wrote a script to run with SQLPlus which deletes the previous seven days of data in my Oracle XE database. I logged into the database with SQLPlus, ran the script, and it showed me the correct rows had been deleted. I tried to query the same date range again just to make sure, and the query returned no results (i.e. the records were deleted).
However, when I then connect to the database using Oracle SQL Manager, I can go to the same table and all the data is still there! If I run the query in SQL Manager, it is then deleted from the table in SQL manager.
Anyone know why this is happening? The credentials I use to log in are the same in both instances and I've made sure to be logged out of SQL Manager when running SQLPlus and vice-versa. I'm running version 18c of XE database.


texts: Job not running as agent but as engine account

texts:
I recently created a new SQL Server environment and I am trying to use best practices where I can, especially the least privileged approach. Other servers in our environment just have jobs running with combinations of Domain Admin rights and SA rights which is "simpler" and scary.

That aside, I have the Database Engine running with the domain account DOMAIN\sqlengine and the Agent running with DOMAIN\sqlagent.

I have a simple maintenance plan that does a full backup of all databases on the server to a remote UNC Path. I had assumed/planned that the agent account (DOMAIN\sqlagent) would be executing the jobs of the plan. The jobs were failing with an OS-driven Access Denied error.  So I added the DOMAIN\sqlagent account modify rights to the directory so that it could perform the backup.  To my surprise it failed again.  When I check the security logs of the target server I see that DOMAIN\sqlengine was the account authenticating.  That account was not given rights to the UNC path.  Why is the engine account, not the agent account, running the job?

If I look at the job owner I had set DOMAIN\sqlagent for all of the jobs in that Maintenance Plan.  Under the job step it is set to run as the "SQL Server Agent Service Account".

What I am doing wrong or not understanding?


texts: How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?

texts:
It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too.

In the following, I have a query that functions correctly as far returning results goes but requires a second query that is the same as the one presented here but without OFFSET and the output is just a COUNT(*) of all of the rows.

I have two objectives:

Write the query so that COUNT(*) is returned in the same query. Indeed I have been looking at help pieces such as the excellent SQL SERVER – How to get total row count from OFFSET / FETCH NEXT (Paging) with different ways of solving the problem, but then there's another piece...
Rewrite the join with a window function (e.g. OVER(PARTITION BY) or some more performant way as that query seem to generate an INDEX SCAN and INDEX SEEK on the table. The real query is a bit more complicated in the WHERE part, but it looks to me even one scan could be enough if the query were a bit more straightforward so that the COUNT and MAX could be had simultaneously with the outer query. Even this would be a win, but combined with having the overall COUNT would be even bigger.

Maybe I'm trying to chew a teeny bit more than I can chew currently, but on the other hand, maybe there is now a chance to learn something.

Here are the table and data

CREATE TABLE Temp
(
    Id INT NOT NULL PRIMARY KEY,
    Created INT NOT NULL,
    ParentId INT,
    SomeInfo INT NOT NULL,
    GroupId INT NOT NULL

    CONSTRAINT FK_Temp FOREIGN KEY(ParentId) REFERENCES Temp(Id)
);

-- Some root levels nodes.
INSERT INTO Temp VALUES(1, 1, NULL, 1, 1);
INSERT INTO Temp VALUES(2, 2, NULL, 2, 2);
INSERT INTO Temp VALUES(3, 3, NULL, 1, 3);
INSERT INTO Temp VALUES(13, 13, NULL, 1, 1);

-- First order child nodes.
INSERT INTO Temp VALUES(4, 4, 1, 2, 1);
INSERT INTO Temp VALUES(5, 5, 2, 1, 2);
INSERT INTO Temp VALUES(6, 6, 3, 2, 3);

-- Second order child nodes.
INSERT INTO Temp VALUES(7, 7, 4, 1, 1);
INSERT INTO Temp VALUES(8, 8, 5, 2, 2);
INSERT INTO Temp VALUES(9, 9, 6, 1, 3);

SELECT
    Id,
    newestTable.SomeInfo,
    newestTable.Created,
    CASE WHEN newestTable.RootCount > 1 THEN 1 ELSE 0 END AS IsMulti
FROM
   Temp as originalTable
   INNER JOIN
   (
        SELECT
            SomeInfo,
            Max(Created) AS Created,
            Count(*) AS RootCount
        FROM
            Temp
        WHERE ParentId IS NULL AND GroupId = 1
        GROUP BY SomeInfo
    ) AS newestTable ON originalTable.SomeInfo = newestTable.SomeInfo AND originalTable.Created = newestTable.Created
/*WHERE
(
    originalTable.SomeInfo = 1
)*/
ORDER BY newestTable.Created ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

P.S. Also How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql looks interesting.

<edit:

It looks like

SELECT
    Id,
    SomeInfo,
    GroupId,
    ParentId,
    MAX(Created) OVER(PARTITION BY SomeInfo) AS Created,
    COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
    CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) > 1 THEN 1 ELSE 0 END AS IsMulti
FROM
    Temp
WHERE
(
    GroupId = 1 AND ParentId IS NULL
)
ORDER BY Created ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

gets close to there. The problem is, though, there are now two result rows and it appears to me this is due to the original INNER JOIN joining back to Temp that cull it to one row. I wonder if there is a way to apply the conditions somehow either before or after the windowing to match more closely the original query. (And this isn't the same query, to be clear. There's just so little data, hence the queries look like being close to each other.)


texts: Multiple like conditions for same column?

texts:
How do I write a query for a Oracle database when I have several "like" values for the same column (in my example data "name")?

My example data for table "address":

name like "John"
name like "Jon%"
name like "Jonathan"
name like "Frank"

Is there something like IN() with LIKE in Oracle or do I have to write it for each value OR separated?


texts: Need help figuring out a daisy chain issue

texts:
We have a goofy process (don't we all?) that daisy chains (or cascades) on a table that needs flattening out for our data warehouse.

The field "alt_key" is an identifier field. It ends up being the parent to other alt_key and alt_key2 data.

What I have below is an example of how the data looks. This table is named MEMBER

The record where alt_key = 05526396 is our "master" record, and the other records daisy chain down alternating between the alt_key and alt_key2 fields as shown in the picture below:

[![Record Daisy Chain][1]][1]

In the end, the records need to look like this. alt_key will be named primary_pmi and alt_key2 will be named pmi. if the row is a parent,  alt_key and alt_key2 will be the same as will the primary_member_nbr and member_nbr fields. pmichain_id and pmi_order can be ignored:

[![Final Daisy Chain Transformation][2]][2]

Not sure how I can get to the end dataset. I've tried a recursive CTE but I think I have too many records to process. How else can I go about this? Records can have many children


texts: Speeding up linking Oracle tables in Microsoft Access

texts:
Many people in my office use Oracle tables by linking them in Access (either 2010 or 2016) databases.

They always complain about the fact that, when Access is about to present them the list of tables they have access to in a specific Oracle instance for selection, they have to wait 10 to 15 minutes for the selection window to pop-up.

The reason seems to be the huge number of accessible tables, in turn related to the huge number of accessible schemas, on that particular instance, so that the resulting list contains tens of thousands of table names.

There was a similar problem with some DB2 databases, but was solved by adding a 'SCHEMALIST' option in the ODBC configuration (db2cli.ini) for those databases, to limit the number of schemas visible to the connection.

I had a look at Oracle ODBC documentation, but couldn't find a similar option, so I'm stuck.

Does somebody have a trick to share about how to filter schemas/tables visible to an ODBC connection to Oracle or otherwise speed-up linking those tables?


texts: Why does SQL Server estimate less rows will be emitted from a join after inserting some rows?

texts:
The below is a simplified version of something I came across in production  (where the plan got catastrophically worse on a day where an unusually high number of batches were processed).

The repro has been tested against 2014 and 2016 with the new cardinality estimator.

CREATE TABLE T1 (FromDate  DATE, ToDate DATE, SomeId INT, BatchNumber INT);

INSERT INTO T1
SELECT TOP 1000 FromDate = '2017-01-01',
                ToDate = '2017-01-01',
                SomeId = ROW_NUMBER() OVER (ORDER BY @@SPID) -1,
                BatchNumber = 1
FROM   master..spt_values v1

CREATE TABLE T2 (SomeDateTime DATETIME, SomeId INT, INDEX IX(SomeDateTime));

INSERT INTO T2
SELECT TOP 1000000 '2017-01-01',
                   ROW_NUMBER() OVER (ORDER BY @@SPID) %1000
FROM   master..spt_values v1,
       master..spt_values v2

T1 contains 1,000 rows.

The FromDate, ToDate, and BatchNumber are identical in all of them. The only value that differs is SomeId with values between 0 and 999

+------------+------------+--------+-----------+
|  FromDate  |   ToDate   | SomeId | BatchNumber |
+------------+------------+--------+-----------+
| 2017-01-01 | 2017-01-01 |      0 |         1 |
| 2017-01-01 | 2017-01-01 |      1 |         1 |
....
| 2017-01-01 | 2017-01-01 |    998 |         1 |
| 2017-01-01 | 2017-01-01 |    999 |         1 |
+------------+------------+--------+-----------+

T2 contains 1 million rows

but only 1,000 distinct ones. Each repeated 1,000 times as below.

+-------------------------+--------+-------+
|      SomeDateTime       | SomeId | Count |
+-------------------------+--------+-------+
| 2017-01-01 00:00:00.000 |      0 |  1000 |
| 2017-01-01 00:00:00.000 |      1 |  1000 |
...
| 2017-01-01 00:00:00.000 |    998 |  1000 |
| 2017-01-01 00:00:00.000 |    999 |  1000 |
+-------------------------+--------+-------+

Executing the following

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

Takes about 7 seconds on my machine. The actual and estimated rows are perfect for all operators in the plan.

Now add 3,000 additional batches to T1 (with batch numbers 2 to 3001). These each clone the existing thousand rows for batch number 1

INSERT INTO T1
SELECT T1.FromDate,
       T1.ToDate,
       T1.SomeId,
       Nums.NewBatchNumber
FROM   T1
       CROSS JOIN (SELECT TOP (3000) 1 + ROW_NUMBER() OVER (ORDER BY @@SPID) AS NewBatchNumber
                   FROM   master..spt_values v1, master..spt_values v2) Nums

and update the statistics for luck

 UPDATE STATISTICS T1 WITH FULLSCAN

And run the original query again.

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

I let it run for a minute before killing it. By that time it had output 40,380 rows so I guess it would take 25 mins to output the full million.

The only thing that changed is that I added some additional rows not matching the T1.BatchNumber = 1 predicate.

However the plan has now changed. It uses nested loops instead and whilst the number of rows coming out from t1 is still correctly estimated at 1,000 (①) the estimate of the number of joined rows has now dropped from 1 million to a thousand (②).

So the question is...

Why does adding additional rows with BatchNumber <> 1 somehow affect the estimates for rows joined when BatchNumber = 1?

It certainly seems counter intuitive that adding rows to a table should end up reducing the estimated number of rows from the query as a whole.


texts: SQL Agent job for SSIS execution fails with "An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed."

texts:
A SQL Agent job with a SQL Server Integration Services Package step fails with the following message:

  Executed as user: DOMAIN\SQLAGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.4259.0 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    An invalid reporting option was specified.  Only E, W, I, C, D, P, V, and N are allowed.  The command line parameters are invalid.  The step failed.


texts:
I have this query

SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
FROM single
GROUP BY user_id, stage

table engine is innodb, plus I haven't use any indexes except one key which is game_id it takes 0.16 sec to execute the query on 250k rows.

well I added INDEX(user_id_stage) to my indexes and execution time reduced to 0.07 sec
and EXPLAIN SELECT shows that mysql uses my index, but when I run this query, mysql don't use the index, what's the problem here? what am I doing wrong?

SELECT user_id, SUM( ts.highscore ) AS highscore, MAX( ts.stage ) AS stage, SUM( count ) AS total_played
        FROM (

            SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
            FROM single
            GROUP BY user_id,stage
        ) AS ts
GROUP BY user_id

EDIT: I mixed up the order of my group bys sorry, and there's another group by in the outter select hich I also forgot

here's table defenition:

`single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`user_id`,`stage`)
)

this table stores savegames for players, user_id comes from members table.
each member can play a stage as much as he wants, then I need a ranklist which requires max highscore of each stage for each member, then sum all of highscores for each member and sort them.
so a member can play for example 100 times each stage, and I want to show this in my ranklist, that's why I'm counting total attemps of a stage for each member for each stage, and then sum these values for each member in order to get that value.

here's EXPLAIN SELECT for second query
EXPLAIN SELECT results

I use user_id to inner join this table with members table to get member's username.


texts: Make a newer version of the LDF-file work with an older version of the MDF-file

texts:
Is it possible to combine a "newer version" of the LDF file with an older version of the MDF file and keep changes logged in the LDF? Lets assume there has been no log-backups in the time between as I guess that would make this truly impossible.

Steps:

Database in full recovery mode
Take a full backup
Make some changes
Detach database
Make a filecopy of the LDF-file
Restore from full backup in step 2
Detach database
Make a filecopy of the MDF-file
Throw away the backup (important step)
Delete the database
Somehow make the MDF and LDF play together with the change made in
 step 3 still in place.

Just attaching the MDF and LDF gives an error:

  The log scan number (38:195:41) passed to log scan database 'mic' is
  not valid.

I also tried a sequence of

Create database
Take database offline
Replace the MDF and LDF files
Bring online
Do a log backup
Drop database
Create database with attach MDF-file only
Make full backup
Restore full backup (norecovery)
Restore log-backup from Step 5

I have a vivid memory that this worked just fine for a while but then it did not any more.

  The log in this backup set terminates at LSN 38000000021300001, which
  is too early to apply to the database. A more recent log backup that
  includes LSN 39000000006500001 can be restored.


texts: How to increase Max Concurrent with Insert trigger

texts:
Currently, I used PostgreSQL 12 with Ubuntu 16 (12 Vcpu & 24 GB of Memory)
I want to setup PostgreSQL for 450 Concurrent but Now That's maxed on 333 TPS
PostgreSQL Configuration
Max Connection = 500
Share_memory = 8GB
I have a load test with insert transaction then trigger to insert to another table,
when disabling the trigger I can get 700 TPS.
What should I do to increase concurrently to 450 TPS with Trigger?
Update Infomation
#The trigger definition
CREATE TRIGGER "TG_TranslogDetail_Insert"
AFTER INSERT
ON public."TranslogDetail"
FOR EACH ROW
EXECUTE PROCEDURE public.sp_updatetranslog();

#Full code of the trigger function
DECLARE
 cn_trans INTEGER;
 cn_type INTEGER;
 fix_trans INTEGER;
 cn_credit INTEGER;
 use_credit INTEGER;
 trantype varchar(5);
BEGIN
     fix_trans := 1;
     select "Credit", "CreditUsage" into cn_credit, use_credit from public."Person" where "PersonID" = NEW."PersonID" FOR UPDATE;
     select "credit" into fix_trans from public."TransactionType" where "TranType" = NEW."TranType" FOR UPDATE;
     if fix_trans is null then
       fix_trans := 0;
     end if;
     INSERT INTO public.test_update("cn_code","FixTrans")
     VALUES (cn_credit,use_credit);
    RETURN NEW;
END;

#Table to insert
CREATE TABLE public."TranslogDetail"
(
    id integer NOT NULL DEFAULT nextval('"TranslogDetail_id_seq"'::regclass),
    "PersonID" uuid NOT NULL,
    "APITypeID" uuid NOT NULL,
    "Parameter" character varying(500) COLLATE pg_catalog."default",
    "TranDate" timestamp without time zone NOT NULL,
    "createdAt" timestamp with time zone NOT NULL,
    "TokenID" uuid NOT NULL,
    "updatedAt" timestamp with time zone DEFAULT now(),
    "TranType" character varying(6) COLLATE pg_catalog."default",
    "Result" character varying(500) COLLATE pg_catalog."default",
    "LineID" character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT "TranslogDetail_pkey" PRIMARY KEY (id)
        USING INDEX TABLESPACE tbs_gisiddb
)

#insert statements from JS (load test)
TranslogDetail.stamp = async function (data) {
    try {
      let stamp = await TranslogDetail.create({
        PersonID: data.id,
        APITypeID: data.apiTypeID,
        Parameter: data.keyword,
        TokenID: data.tokenID,
        TranType: data.tranType,
        TranDate: new Date(),
        Result: data.result,
        LineID: data.lineId
      })


texts: How can I trace the cause of a memory spike in Postgres?

texts:
The title tells the story, but how can I trace the cause of a memory spike in Postgres 11.10? As you can see from the screen shot below there was a memory spike all the way up to 32 GB which caused the OOM to start and kill a bunch of connections. According to /var/log/postgresql/postgresql-11-main.log the database system went into recovery mode at the same time and we lost quite a bit of data as well.
I suspect that the number of connections coupled with very large insert statements might have something to do with it, but need a way of proving (or disproving) the hypothesis.


texts: Are there benefits to using more than 4 CPUs for MySQL?

texts: We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs.  We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing the number to 8?


texts: list of batch aborting errors in SQL server

texts:
In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) and some errors will abort the whole batch (for example supplying parameters to a stored procedure that doesn't take parameters). [Reference]: http://www.sommarskog.se/error-handling-I.html#scope-abortion.

What I would like to know is whether there is a definitive list of which errors are batch aborting and which ones are statement terminating.


texts:
I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute .

Scenario :

PostgreSQL 9.2
Need To Run A SP To 'Compile' Information That has been inserted into the database.

No I Can't trust the App to be online and call this on the client side.
Job(s) must execute every minute.
Unfortunately I can't use trigger based or any 'EventBased' Model since I don't know when all the data has arrived.
The cluster is made of headless machines.
I could install  CRON on every server but that could cause unnecessary chatter
inside the Cluster , DeadLocks , Calls at 3 AM , Pain , etc

Problem 1:
Now my problem is PGAgent requires ( As far as I know) a GUI , X and so on things I would like to avoid specially for 'just' running jobs.

Problem 2:
Also what if the server that runs the Agent ( call it Master ) dies the cluster will self heal but will the agent survive?

So how do you do it?


texts: PostgreSQL suitabilty of Hash Index on PK and FK

texts:
I read this not so recent article about Hash Index performance on PostgreSQL, where the author writes:

  ...the hash index performs better than the btree index and the performance difference is in the range of 10% to 22%. In some other workloads we have seen a better performance like with hash index on varchar columns and even in the community, it has been reported that there is performance improvement in the range of 40% to 60% when hash indexes are used for unique index columns.

Assuming that your PostgreSQL is at leat version 10 (to have the index WAL-logged) and (as usually happens) you only have equality comparisons on PK and FK columns, my question is:

Shouldn’t be Hash index preferred to B-Tree index on PK and FK column, due to the (slightly) better performance and for the smaller memory footprint?

While I’m writing, I’m asking if the IN operator can use that kind of index, since it might be the only one other operator (other than =) usually involved in lookup queries on those kind of columns, and if not, this may respond to my answer (negetively).

Thank you!


texts: Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

texts:
Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything.

But,

Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN PROGRESS" and never stops. It does not affect the system as it does not consume any CPU / memory, it simply just starts and never stops.

Ive found many with the same problem but there is no solution for this problem.

I have run a mysqlcheck on all tables, and they are all OK.

All tables are of Innodb

Does anyone have any idea how I can solve this?

Mysql version 5.6.13-log


texts: do user_scans and no user_seeks mean the index is useless?

texts:
I'm digging into sys.dm_db_index_usage_stats and finding that a number of our indexes are used exclusively in user_scans, not user_seeks.  Does this imply that the index is useless and should be redesigned?

Thanks!


texts:
I'm using mysql server connected to Access, and I want to do a form, to fill the table.

My question is how to do it. I need to select the data from a listbox, that's okay this is already done. And I need that the listbox only shows the options that have more than 1 in "stock". Should I do a query to count how many are left and apply that query as data source? If yes, how can I do it? Or any other ideas?


texts: Merge multiple table output but indicate which tables the records came from

texts:
I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table.  Now, I’ve been asked to make it pull from two tables, but it’s not just a straight join.  The tables have overlapping records (a common ID field between them), so my end result needs to have a field that indicates whether that row had a record from Table A only, from Table B only, or if it had records in both Tables A and B.

I am not a SQL guru (my queries are usually just selects with simple joins), so I’m having a hard time figuring this out.  My current thought is to create three temp tables (one for A-only records, one for B-only records, and one for A+B records) then union them together for the output, but I’m sure there’s a better, more elegant/proper way to do this.  Any thoughts on how this could be accomplished?


texts: Behaviour of performance counter "Checkpoint Pages/sec" under load

texts:
I'm working on performance analysis of reports, resulting from different test cases.  I'm using MS SQL Server 2016 Dev edition

Regarding one of the performance counters ('Checkpoint Pages/sec'), I understand the meaning of the counter. During a 22 second interval, the graph drops from 400 to 200 written changed pages per second, and then returns to 400.

Result from the graph:

1-13 seconds, value is around 400
14-16, falls down to 200, then returns to 400
16-38, value is around 400
39, falls to 200 then returns to 400
61, falls to 200 then returns to 400
84, falls to 200 then returns to 400

The values in the performance counter 'Disk Writes/s' are identical to 'Checkpoint Pages/sec'.

I expected these counters to be a straight line (without the peaks and valleys).

Can you explain this behavior?  What do the drops in Checkpoint Pages/sec from 400 to 200 mean?


texts:
I'm trying to wrap my head around the difference between various kinds of database indexes. I have created a little example for myself and I don't know whether I'm understanding everything correctly.
Let's say we have an fictional database table like this:
addr col1  col2
===============
1    a     b
2    c     b
3    a     c
4    d     d
5    c     a
6    a     b
7    c     b
8    d     d
9    a     c

addr here is a physical location of the corresponding tuple. Here is my understanding of how a single-column, a multi-column and a covering index would look like (not physically, rather on a conceptual level):
create index on col1
a - 1, 3, 6, 9
c - 2, 5, 7
d - 4, 8

create index on (col1, col2)
a b - 1, 6
a c - 3, 9
c a - 5
c b - 2, 7
d d - 4, 8

create index on col1 include col2
a - 1b, 3c, 6b, 9c
c - 2b, 5a, 7b
d - 4d, 8d

So my question is: do I get it right?


texts:
Some tables are very dynamic in terms of additions and subtraction of records.  The application (rails 4.2) deletes a large number of records daily.  However, the size of the database to the OS - or dumped via pg_dump does not seem to go on a diet.

As a test, all records from three related tables were deleted by the application. However, when  running \d+ one table has 42MB size.

So then a manual exploration.

durc_production=# VACUUM FULL commonpassaggios;
durc_production=# VACUUM FULL posiziones;
durc_production=# VACUUM FULL passaggios;

run.  The relative times they take are coherent with the table and index sizes. But then \d+ returns the same data sizes.

I need to get the vacuum demonstrably running before configuring postgresql.conf...

Update  This question ends up as a red herring.  Craig's reply allowed to pick up on the problem.  it was not related to postgresql at all.  see subsequent comments for culprit identification.


texts:
I need to develop a production system capable of storing a 100 kb textfile and a serial number for every piece produced. I know the approximate output over 2 years, the time the data has to be stored, and have calculated a need of around 25-30 TB of storage.

I have some experience working with databases, but handling that amount of data is new to me.

My first guess would be to simply store the files as a blob inside a table in the database. What are the problems of storing that amount of data in a single table? What are possible backup scenarios?

Another approach would be to store the data in several tables (e.g. a table per month).

A third approach would be to store references to the data in SQL Server, zip the files, and store them somewhere else.

The purpose of this storage system is to query the text file by using the serial number.

What would you do?


texts: Relationship between number of transactions and redo generation

texts: If I execute some SQL statements over many transactions instead of just committing once after all statements, will this have an effect on the amount of redo log generated? And hence on the size of the archiving log directory?


texts: Oracle flashback query syntax - all tables to same timestamp

texts:
I'm not finding a lot of examples online of how to use Oracle Flashback Query and I'm hoping somebody can clarify syntax. I'm trying to execute a query with a large number of tables in it, but the syntax seems to require me to specify a timestamp for every single table involved in the query.

For example, the following query gets me consistent data, but it requires me to specify a timestamp for each table:

select t1.Field1, t2.Field2
  from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1
  join table2 as of timestamp to_timestamp(2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t2
    on t1.somekey = t2.somekey

The following example (which is how most examples online look) gives past data for table1, but joined to current data from table2 - they don't line up:

select t1.Field1, t2.Field2
  from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1
  join table2 t2
    on t1.somekey = t2.somekey

Since I have a large number of tables, I'm hoping for a way I can execute the entire query against a flashback timestamp - something like this:

select (as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss'))
       t1.Field1, t2.Field2
  from table1 t1
  join table2 t2
    on t1.somekey = t2.somekey

Anybody know if this is possible, or if the current syntax doesn't allow it? I'm using 11.2 if it matters, but nothing I see online suggests that this has changed in 12.


texts: SQL Server Log Shipping: File copy options?

texts:
I usually use redgate sql backup which has a transaction log copy mechanism built in.

If I roll my own log shipping, what are established ways to move the log file?

I want to emphasize: The way log shipping works in the log shipping feature is a mess. The requirement of a file share, and all the firewall and auth overhead of that are simply over the top.

In my dreams, there would be an automatic way to do log shipping via azure files. Click click click. Think how great this would be:

1) No file shares needed!
2) Your backups and tran logs are automatically backed up in azure cloud (DR step #1 !)
3) The remote can be anywhere
4) I can now easily do one-to-many log shipping, because the point to point architecture of the built in log shipping is side stepped.


texts: Insert New Foreign Key Row for new Strings

texts:
Lets say I have a table new_data like this:

ndx    | someval | somemachine
serial | integer | integer
----------------------------

And let's say I have a reference table new_data_someval_ref:

someval | someval_str
integer | varchar
---------------------

(Where, obviously, new_data_someval_ref.someval is foreign key on new_data.someval)

And I want do inserts like so:

INSERT INTO new_data
  (someval, machine)
VALUES (
  (SELECT someval
   FROM new_data_someval_ref
   WHERE someval_str = ?),
  ?)

BUT, if there is no match in the reference table, I would like to add a row containing the new string and an incremented someval, then do the insert above.

So my question is there clean, possibly even constraint-based way to do this?

(I use both Postgres and SQL Server regularly, so an answer addressing both would be ideal, but not necessary.)


texts: How to setup multi master replication in mysql

texts: After hours of searching I find lots of tutorials that do only 2 server Master Master. Now I wonder how to setup multi master setup because this is more suitable in my business. Because when the WAN connection is down my transaction in area's are still on going and then when the connection is back the changes in servers will be sync on. That's why i need multi master setup. Thanks in advance for any help and tips


texts: Want to know the No. of rows,space used,space reserved in each table against all the databases on server level

texts:
I need to find the No. of rows,size of table,index size,data used,datareserved in each table against all the user created database on server level.Is it possible in a single script or query.or i have to query each database.Write now the script which i have got is this.

//---script i have to run on each database.
use MyDbName;

GO
CREATE TABLE #TableSpaceUsed
(
       Table_name NVARCHAR(255),
       Table_rows INT,
       Reserved_KB VARCHAR(20),
       Data_KB VARCHAR(20),
       Index_Size_KB VARCHAR(20),
       Unused_KB VARCHAR(20)

)

INSERT INTO #TableSpaceUsed
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT TOP 1 db_Name() As DB_Name,Table_name,Table_Rows,
  CONVERT(INT,SUBSTRING(Index_Size_KB,1,LEN(Index_Size_KB) -2)) as indexSizeKB,
  CONVERT(INT,SUBSTRING(Data_KB,1,LEN(Data_KB) -2)) as DBSizeUsed_KB,
  CONVERT(INT,SUBSTRING(Reserved_KB,1,LEN(Reserved_KB) -2)) as DBSizeReserved_KB,
  CONVERT(INT,SUBSTRING(Unused_KB,1,LEN(Unused_KB) -2)) as unusedKB
  FROM #TableSpaceUsed
  ORDER BY DBSizeUsed_KB DESC

DROP TABLE #TableSpaceUsed


texts: Decimal or Point Data Type for storing Geo location data in MySQL

texts:
I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options:

1) 2 columns for Each Address:
Latitude DECIMAL(10, 8),
Longitude DECIMAL(11, 8)

2) One Column for Each Address:
GeoLocation POINT

My program need to find all Address that fall under 1 Kilometer Radius from current Location (Mobile Client).

Also below command does not work for me, not sure what I need to have for this.

CREATE SPATIAL INDEX GeoLocation ON tblAddress (GeoLocation);

So what is the suggested approach to store data for faster calculation and retrieval here. Please advise.


texts:
I analyse web logs (nginx) and want store (organize) result in DB.
After analyse and aggregate my result looks like:

Date and Time (Truncate it by hours)
Country
City
URL (Page on the website)
Referrer (source website)
UTM_Medium
UTM_Campaign
c_sessions
c_views
avg_session_time
c_js_event_1
c_js_event_2
c_js_event_3

And so on, about 30 columns.

For that data I prepare "big table"

CREATE TABLE public.cca_data_hours
(
  datetime timestamp without time zone NOT NULL,
  id_geo_country integer NOT NULL,
  id_geo_city integer NOT NULL,
  id_web_page bigint NOT NULL,
  id_web_referrer bigint NOT NULL,
  id_utm_campaign bigint NOT NULL,
  id_utm_medium bigint NOT NULL,
  c_sessions integer,
  c_views integer,
  avg_sd integer,
  c_jse_1 integer,
  c_jse_2 integer,
  c_jse_3 integer,
  ...
  CONSTRAINT cca_data_hours_pri PRIMARY KEY (datetime, id_web_page, id_web_referrer, id_utm_campaign, id_utm_medium, id_geo_country, id_geo_city)
)

I expect 100 000 000 records.

My selections

I want see all data by countries, cities
I want see data per page
I want see data per source (referrer)
I want see data per page, source and utm campaign
Some other combinations

My questions

Is it normal use so many columns as primary key (index)? Primary key now contains 7 columns, it may up to 10-15 columns in the future.
I think this is a simple solution how to organize this data in one  object-relational DB table. Do you know better solution, practice?


texts: Retreive a Customized List with input data

texts:
I have a database that has about 223 tables and I need to locate the name of the constraint.

My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the name of the table and its column. In other word, two column. You show the list that is using the specific constraint based on input data.

I do not know how to create it. I'm a rookie in SQL server

Thanks!


texts:
Can  any one  tell why t-sql  not allowing using RAND function in side a function

SELECT  CAST (RAND() * 10000000 as varchar(20))

the error is

Invalid use of a side-effecting operator 'rand' within a function.