texts: Updating a table from a temporary table where clause error in MySql Community

texts:
I am trying to update an existing table named "residential_summary" from a temporary table called "a". I can query the temporary table a but when I try to use it in an update statement I get

Error Code: 1054 Unknown column 'a.meter' in 'where clause'

Is this something that you just cant do or am I somehow misinterpreting the syntax for Update? Why is my column unknown? Please elaborate because I am new at databases. Here is my code and a screenshot.

UPDATE residential_summary
SET residential_summary.load_factor = a.load_factor
WHERE a.meter = residential_summary.meter;


texts: Aurora RDS MySqlException: Unknown database

texts:
We have a .NET Core 3.1 microservice running in an EC2 auto-scaling group (7 instances) which uses MySql.Data NuGet package 8.0.18. It connects to an Aurora MySQL RDS cluster in the same account. This service typically handles 10-50 thousand requests per minute and rarely has issues.
Database engine version is 5.6.mysql_aurora.1.19.2. We have three instances in the cluster, one writer and two readers. Failovers do occur but are rare. The most recent incident was not caused by a failover.
There is one intermittent issue for which we have not yet found a solution. Seemingly at random, one of the EC2 instances will start throwing the following exception.
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown database 'users'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.ReadOk(Boolean read)
   at MySql.Data.MySqlClient.MySqlConnection.ChangeDatabase(String databaseName)
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---

The service's health check operation calls the same database simultaneously but those calls will succeed, leading us to believe that this is possibly an issue with one or more connections within the pool. Our solution thus far has been to restart the service on the instance or simply replace the instance.
An issue at the database level would likely impact all EC2 instances, which does not occur. This same issue exists on our current Ubuntu OS and last year when we used Windows.
Any help is appreciated. Asking on DBA because this seems more specific to the MySQL driver and Aurora RDS than .NET, but please suggest a better stack site if you feel it should be moved.


texts: How does adding LIMIT work with Where conditions in mysql?

texts:
In my database, there is only one record which satisfies the condition iv_title = 'ball skills'.
Now I'm executing the following query :

SELECT
    *
FROM
    `imageAndVideo`
WHERE
    `iv_title` = 'ball skills' AND `iv_file_type` = 2
ORDER BY
    `iv_create`
DESC
LIMIT 1, 6

This above query doesn't output any record, I don't know why? Can somebody please explain this to me?
If I execute the below (removing LIMIT) query I get the desired limit.

SELECT
    *
FROM
    `imageAndVideo`
WHERE
    `iv_title` = 'ball skills' AND `iv_file_type` = 2
ORDER BY
    `iv_create`
DESC


texts:
I have a registration application. 10000 people want to access the server but only 3000 quotas available. 3000 users registered successfully online within 3 minutes on the registration day. However, some users encounter 504 error page (Gateway time out). I checked the max_used_connections variable, the value is 10 only. The max_connections value is 151.
The max_used_connections is defined as

the maximum number of connections that have been in use simultaneously since the server started.

I wonder why the value is only 10. Is it too small compared with 3000 users registered successfully online within 3 minutes?


texts:
I have the following sqlite3-Tables:

db.execute("""CREATE TABLE IF NOT EXISTS STATIONS
       (StationUID INTEGER PRIMARY KEY AUTOINCREMENT,
       StationNumber           TEXT    NOT NULL,
       StationName             TEXT    NOT NULL,
       StationLongitude        REAL    NOT NULL,
       StationAltitude         REAL    NOT NULL);""")

print "Created table STATIONS successfully"

db.execute("""CREATE TABLE IF NOT EXISTS TRAVELDAYS
       (TrainDaysUID           INT  NOT NULL,
       Day                     INT  NOT NULL,
       Value                   INT  NOT NULL,
       PRIMARY KEY (TrainDaysUID, Day));""")

print "Created table TRAVELDAYS successfully"

db.execute("""CREATE TABLE IF NOT EXISTS TRAINS
       (TrainUID INTEGER PRIMARY KEY AUTOINCREMENT,
       TrainNumber           TEXT    NOT NULL,
       TrainDaysUID          INT     NOT NULL,
       TrainType             TEXT    NOT NULL,
       TrainZone             TEXT    NOT NULL,
       FOREIGN KEY(TrainDaysUID) REFERENCES TRAVELDAYS(TrainDaysUID));""")

print "Created table TRAINS successfully"

db.execute("""CREATE TABLE IF NOT EXISTS ROUTES
       (RouteUID INTEGER PRIMARY KEY AUTOINCREMENT,
       StartStationUID       INT     NOT NULL,
       EndStationUID         INT     NOT NULL,
       FOREIGN KEY(StartStationUID) REFERENCES STATIONS(StationUID),
       FOREIGN KEY(EndStationUID) REFERENCES STATIONS(StationUID),
       UNIQUE (StartStationUID, EndStationUID) ON CONFLICT IGNORE);""")

print "Created table ROUTES successfully"

db.execute("""CREATE TABLE IF NOT EXISTS LEGS
       (LegUID INTEGER PRIMARY KEY AUTOINCREMENT,
       TrainUID            INT     NOT NULL,
       RouteUID            INT     NOT NULL,
       StartTime           INT     NOT NULL,
       EndTime             INT     NOT NULL,
       TravelTime          INT     NOT NULL,
       DwellTime           INT             ,
       FOREIGN KEY(TrainUID) REFERENCES TRAINS(TrainUID),
       FOREIGN KEY(RouteUID) REFERENCES ROUTES(RouteUID));""")

print "Created table LEGS successfully"

Table STATIONS has 10965 entries
Table ROUTES has 13026 entries
Table LEGS has 610593 entries
Table TRAINS has 44920 entries

I need to improve the performance of this query:

cr.execute("SELECT l.TrainUID, l.RouteUID, l.LegUID, l.StartTime, l.EndTime, l.TravelTime, l.DwellTime, s1.StationName, s2.StationName "
           "FROM LEGS AS l "
           "INNER JOIN ROUTES as r ON (r.RouteUID = l.RouteUID) "
           "INNER JOIN STATIONS AS s1 ON (r.StartStationUID = s1.StationUID) "
           "INNER JOIN STATIONS AS s2 ON (r.EndStationUID = s2.StationUID) "
           "WHERE l.StartTime >= 800 AND l.EndTime <= 1200 AND "
           "l.TrainUID IN ( "
                "SELECT DISTINCT l.TrainUID "
                "FROM LEGS AS l "
                "INNER JOIN ROUTES as r ON (r.RouteUID = l.RouteUID) "
                "INNER JOIN STATIONS AS s1 ON (r.StartStationUID = s1.StationUID) "
                "INNER JOIN STATIONS AS s2 ON (r.EndStationUID = s2.StationUID) "
                "INNER JOIN TRAINS AS t ON (t.TrainUID = l.TrainUID) "
                "INNER JOIN TRAVELDAYS as d ON (t.TrainDaysUID = d.TrainDaysUID) "
                "WHERE t.TrainDaysUID == d.TrainDaysUID AND d.Day == 300 AND d.Value == 1 AND (s1.StationNumber = 8000105 OR s2.StationNumber = 8000105)) ")

What can you recommend?

EDIT
I created now those indexes, but it is not really faster.

db.execute("""CREATE INDEX Idx1 ON TRAVELDAYS(TrainDaysUID, Day, Value);""")
db.execute("""CREATE INDEX Idx0 ON STATIONS(StationNumber);""")


texts: LCK_M_S causing blocking with session id < 50

texts:
We are experiencing high LCK_M_S wait times conflicting with idle blocking sessions whose ids are less than 50.

The waiting session is always a Select statement within a stored procedure.

The blocking session doesn't have any SQL statements and the monitoring tool we use does not show any logged last activity. All I can understand is it is always a session id less than 50 (25,27 or 30).

As can be seen below, blocked session uses an index hint about which I'm unsure whether it might be the culprit. I know it is not a good practice but someone had written it this way long ago and was not causing any problems until the last week.

The shortened and obfuscated version of the waiting statement is as follows:

SELECT
  CASE
      WHEN LEN(B)>200 THEN LEFT(B,200)+'...'
      ELSE B
   END B,
   dbo.somedatefunction(E) AS E,
FROM
   (
   SELECT ROW_NUMBER() OVER (ORDER BY A.H DESC) AS ROWNUMBERS,
      A.A,
      B,
      TN.G,
      OP.I + ' ' + OP.F AS F
   FROM ABC AS A
   WITH
      (
         NOLOCK,
         INDEX(IX_INDEX)
      )
   INNER JOIN DEF AS TN
   WITH
      (
         NOLOCK
      )
   ON TN.G = A.G
   LEFT JOIN H AS OP
   WITH
      (
         NOLOCK
      )
   ON OP.J = A.J
   WHERE Contains(A.K,@SomeVariable)
  AND dateadd(hour,2,datediff(day,0,A.L)) < GETDATE()
   ) AS AHK
WHERE ROWNUMBERS > ((@variable-1) * @somevariable3)
AND ROWNUMBERS <= @variable * @somevariable3
FOR XML PATH('Result')

What might be causing this wait to occur?


texts: how to drop job with lowercase name ORACLE (=> ORA-27475 )

texts:
How can I drop a job with lowercase name in Oracle?

It gives me an error ORA-27475 "JOB_NAME" must be a job

BEGIN
 DBMS_SCHEDULER.stop_job(job_name => 'MY_JOB_name');
END;
/

The job exists for sure .. do you know how to circumvent this issue?

Thx


texts:
I'm creating tables in a database currently and one of the tables has a status column. the status column has 4 different statuses that it could potentially have. I've made a separate table that has table_statuses and links them together via id. But I'm wondering if I shouldn't just type in the varchar directly.

i.e I have

-- -----------------------------------------------------
-- Table `m_document_version_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `m_document_version_status` (
  `document_version_type_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `value` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `m_document_version_status` (`value`) VALUES ("wip");
INSERT INTO `m_document_version_status` (`value`) VALUES ("scheduled");
INSERT INTO `m_document_version_status` (`value`) VALUES ("current");
INSERT INTO `m_document_version_status` (`value`) VALUES ("deprecated");

-- -----------------------------------------------------
-- Table `m_document_version`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `m_document_version` (
  `document_version_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `version` varchar(12) NOT NULL,
  `major` int(4) NOT NULL DEFAULT 0,
  `minor` int(4) NOT NULL DEFAULT 0,
  `patch` int(4) NOT NULL DEFAULT 0,
  `document_type_id` int(1) NOT NULL DEFAULT 1,
  `status` int(1) NOT NULL DEFAULT 1,
  `created_date` DATETIME NOT NULL,
  `launch_date` DATETIME DEFAULT NULL,
  `deprecated_date` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

I'm wondering if I shouldn't just remove the version_status table and input the varchars directly for simplicity


texts: Oracle 19c Deinstall : Permission denied

texts:
I have an RHEL 7.7 box on which Oracle 12c is running. I have installed 19c on the same machine for the purpose of upgrading from 12c to 19c but need to uninstall it for some reason.
I get the below error when I tried to run,
[oracle@rhel7 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.3.0/dbhome_1

[oracle@ rhel7 ~]$  cd $ORACLE_HOME/deinstall

[oracle@ rhel7 deinstall]$ ./deinstall

Checking for required files and bootstrapping ...

Please wait ...

./deinstall: line 294: /tmp/deinstall<datetime>/perl/bin/perl: Permission denied

I have checked all files and directories including the one in /tmp and have granted 755 permission to all the files (installation dir and /tmp/), but to no avail.
I am executing this script as an oracle account which was used to install 19c.
Appreciate any help.


texts: SQL Server database has corruption, but displays no errors

texts:
We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and execute it there. Then when I tried to select from the table, it would just keep executing. It is an extremely simple table: two columns, both int, no rows at the moment. We were also unable to delete the table.

We suspect there is some type of corruption, but when we run error checking on tables or database it says there are no problems. I've included our version below, just a SQL Server 2008 R2 Express version. The only errors we get are when we run dbcc checkdb. But it only shows an error on the last line. It shows no errors on any individual tables.

We restarted the SQL Server and then we were able to delete the table. We can select from every table, and everything is now working normal.  But we still have that error at the end of checkdb.

Not sure what to do, it's a little weird. Everything is working, but we are a little afraid something might happen.  We have backups and everything, just trying to fix this if at all possible.

Last lines of checkdb:

  CHECKDB found 0 allocation errors and 0 consistency errors in database '....'.

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

When we run checkdb on another db:

  CHECKDB found 0 allocation errors and 0 consistency errors in database '....' .

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Our SQL Server database version:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)


texts:
I have 2 identical DBs, one of the tables has C1, C2, C3 , composite key.
I'm trying to get the rows from B that are not present in DB A.

I have tried many approaches, but at this time I even can't re-use this suggestion due too the late hour.

I think it should be by something like

Select * from B
where  not exists
     (SELECT a.C1, a.C2, a.C3
      from A.table as a, b.table as b
      where a.C1=b.C1
          , a.C2=b.C2
          , a.C3=b.C3)

But I know that I'm totally wrong.
What is the proper way to do this?


texts: Oracle: Recursively self referential join with nth level record


texts: Insert to a table using function with default arguments

texts:
I'm trying to write a function that makes an insert to the table defined below:

DROP TABLE IF EXISTS pl_table;
CREATE TABLE pl_table
(
    id          SERIAL PRIMARY KEY,
    mandatory   VARCHAR NOT NULL,
    option1     VARCHAR DEFAULT null,
    option2     VARCHAR DEFAULT null
);

The idea is to make only one argument mandatory and other two optional. The function listed below works well for all three inputs being present, but doesn't make an insert if less than three arguments are provided. What should I do to to make the function work for the Case1?

CREATE OR REPLACE FUNCTION pl_test_function(mand_arg varchar
                                          , opt_arg1 varchar DEFAULT NULL
                                          , opt_arg2 varchar DEFAULT NULL) RETURNS void AS
$$
BEGIN
      INSERT INTO pl_table (mandatory, option1, option2) VALUES
             (mand_arg, opt_arg1, opt_arg2);
END;
$$
      LANGUAGE plpgsql STRICT;

SELECT pl_test_function('abc'); -- Case1: doesn't work
SELECT pl_test_function('abc', 'xyz', 'def'); -- Case2: works

Please note that the pl_test_function doesn't return anything in this toy example, but in reality its more complex production cousin returns an integer.


texts: Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS


texts: why sp_ExecuteSql output parameter returns null when procedure shows correct value in results pane

texts:
declare @Table nvarchar(20) = 'tblsteps';
declare @IdFieldName nvarchar(20) = 'stepid';
declare @NumberFieldName nvarchar(20) = 'stepnum';
declare @recid nvarchar(10) = 3;

declare @sql nvarchar(200);
declare @params nvarchar(500);
declare @number_out nvarchar(20)

set @sql =  'SELECT ' +  @NumberFieldName + ' FROM ' + @table + ' WHERE ' + @IdFieldName +' = '+ @recid;

set @params= N'@number_out nvarchar(20) OUTPUT';

Execute sp_executesql @sql,@params,@number_out OUTPUT;

select @number_out as sp_Out;

select @sql as sqlString shows desired sql string;

Execute sp_executesql returned correct answer in results pane;

select @numberout as sp_out always returns NULL;


texts:
This is the first time I have come across this type of error.  I am running a process task and it runs fine until I add it to the scheduleer ( sql agent).

Date       3/2/2017 4:13:05 PM
Log        Windows NT (System)
Source     Microsoft-Windows-DistributedCOM
Category        (0)
Event       10016
User        NT SERVICE\SQLSERVERAGENT
Computer        CFBF-SQL2

Message

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.


texts: Restored DB Server Using FQDN Causing Distribution Issues

texts:
We had a complete RAID failure on one of our SQL Server 2008 R2 SP1 servers.  I setup a new server and restored everything and we're basically up and running again. But, I have one small issue: I am trying to restore the publications/subscriptions and I keep running in to errors with the server name.

It seems in my haste to get things back up and running I used the FQDN instead of the Netbios name somewhere.  If I do SELECT @@SERVERNAME, I get the netbios name ('KAI') but if I do an sp_helpservers then I see the FQDN ('kai.caymanport.com') for both the name and networkname fields.

How can I correct this or can I correct this?  I already tried to do a sp_dropserver, sp_addserver 'KAI', LOCAL (which worked) but that didn't help.


texts:
Given two tables:

Product

int    id
int    operator_id
string name
string description
int    price

Operator

int    id
string logo_path
string tos_path
int    tax_number

I need to call multiple Product a few times. Most of the times I call a Product, I need to get the Operator's name and logo.

I know that we should avoid duplicating data when possible, and be using more elegant ways to bind data, but in this case, isn't it more efficient to put int operator_name and int operator_logo into Product table instead of calling at each request, one more request to find the Operator?

Or is it the really point of relationships, DBs being OK with dealing with one more request instead of duplicating the data?

A bit beginner into optimization and not really knowing what and where to search into to find answers, thanks for yours.


texts: How to add the Debug button to SSMS v18?

texts:
The Debug button is present on this version of SSMS:

But it is not present on version 18, preview 4:

I have tried in several ways to add the Debug button to my SSMS but not successful.

Is there a way to add the Debug button to SSMS v18?


texts: limit keyword support in DB2 latest version

texts:
I could see limit keyword is supported in latest version v10.5.500.107 5 of db2 by default without setting MYS compatibility vector. My installation was DB2 Express-C. Is there any announcement for that? I couldn't find any online document.

EDIT

After the answer of Ian I checked the value of compatibility vector after unsetting it as below also.

db2set DB2_COMPATIBILITY_VECTOR=
db2stop
db2start

and found it was MYS. seems like it is default set to MYS. that's why it has limit keyword support.


texts:
I have a postgres database running on my application, and the postgres partition is 100% full. Upon checking the files in pgdata directory is see below files occupying lot of space.

smsstoregt1 ins /postgres/pgdata/base/16384> du -skh * | grep -i M
589M    16400
244M    16405

Please can anyone suggest how to cleanup the space and if we can move the files 16400 and 16405 to another path or delete them, or if we can purge any data from postgres database from DB login.

Below is my postgres partition

/dev/mapper/mpath0p1  939M  900M     0 100% /postgres


texts: How to make pg_basebackup use temporary replication slots

texts:
This article
https://blog.hagander.net/another-couple-of-steps-on-my-backup-crusade-235/
states
that temporary replication slots can be "used to prevent the wal on the server from being removed while a downstream replica or backup still needs it"

Apparently, temporary replication slots can now be used by pg_basebackup since 10.
My question is how do we enable this feature or does it happen now automatically with pg_basebackup?

Also, is this feature related to the -X stream option
"Stream the write-ahead log while the backup is created. This will open a second connection to the server and start streaming the write-ahead log in parallel while running the backup. Therefore, it will use up two connections configured by the max_wal_senders parameter. As long as the client can keep up with write-ahead log received, using this mode requires no extra write-ahead logs to be saved on the master." which is enabled by default?
https://www.postgresql.org/docs/current/static/app-pgbasebackup.html


texts: When does PostgreSQL move a WAL file to archive?

texts:
I'm a complete newbie with PostgreSQL - please forgive my gaffs - and have inherited a service using a master-slave cluster at the backend.

Currently on the slave, the disk is filling up with WAL files in the archive directory.

On a slave (replica) I presume the archived WAL files been applied to the local instance of the database since there are files in pg_xlog with current filestamps.

Q1: Is this correct?

Q2: What is the criteria by which the system decides that a WAL file is eligible to be moved from pg_xlog to the archive?

The backup/restore model is predicated around filesystem snapshots - so a restore operation would rely on crash recovery. I infer from this that I should only need the artchived WAL files since the last file-system snapshot to be available (although keeping at least 2 generations would be a better idea).

Q3: Is this correct?


texts: Strange query optimization issue - logically the same, different performance

texts:
I have a slow query and I don't understand why the DB is choosing to perform a sub-optimal execution plan. Server is MariaDB 5.5

Slow query (4-5 seconds) is:

SELECT VisitDate AS LASTDATE
FROM Visit
  JOIN Donor ON Donor.autoid = Visit.autoid
WHERE
  Donor.autoid = Visit.autoid AND
  LName LIKE '%%' AND
  FName LIKE '%bob%' AND
  Donor.autoid = '1234'
ORDER BY VisitDate DESC
LIMIT 1

EXPLAIN'd

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Donor   const   PRIMARY PRIMARY 8   const   1   ""
1   SIMPLE  Visit   index   Visit_autoid_index  Visit_VisitDate_index   4       1   Using where

Fast query (sub 100ms) is:

SELECT VisitDate AS LASTDATE
FROM Visit
  JOIN Donor ON Donor.autoid = Visit.autoid
WHERE
  Donor.autoid = Visit.autoid AND
  LName LIKE '%%' AND
  FName LIKE '%bob%' AND
  Visit.autoid = '1234'
ORDER BY VisitDate DESC
LIMIT 1

EXPLAIN'd

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Visit   ref Visit_autoid_index  Visit_autoid_index  12  const   1   Using index condition; Using where; Using filesort
1   SIMPLE  Donor   eq_ref  PRIMARY PRIMARY 8   NewDb.Visit.autoid  1   Using where

Donor Table (partial)

CREATE TABLE `Donor` (
  `autoid` bigint(20) NOT NULL AUTO_INCREMENT,
...
  `FName` varchar(15) DEFAULT '',
  `LName` varchar(25) NOT NULL,
...
  PRIMARY KEY (`autoid`),
...
) ENGINE=InnoDB AUTO_INCREMENT=3010 DEFAULT CHARSET=latin1

Visit Table (partial)

CREATE TABLE `Visit` (
  `VisitRecnum` int(11) NOT NULL AUTO_INCREMENT,
...
  `autoid` varchar(10) NOT NULL,
  `VisitDate` date DEFAULT NULL,
...
  PRIMARY KEY (`VisitRecnum`),
  KEY `Visit_autoid_index` (`autoid`),
...
  KEY `Visit_VisitDate_index` (`VisitDate`),
...
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Donor.autoid is a primary key and Visit.autoid has an index (a sort of poorly defined foreign key). VisitDate has an index.

It's like the query plan is ignoring the optimization it can make to ensure that Donor.autoid and Visit.autoid match, and scan a smaller set of rows where both autoid = '1234'.


texts: When SELECTing an expression that is an expression based index does the expression get processed?

texts:
I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
SELECT (first_name || ' ' || last_name) FROM people;


texts: DB stored procedures: how do they help increase security in web systems?

texts:
The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic related with data is performed through packages, procedures and functions, so application code becomes quite "dumb" and is only responsible for visualization part. I even heard some devs call such approaches and driving architects as database nazi :) because all logic code resides in database. I do know about DB procedure performance benefits, but now I'm interested in a "better security" when using thick client model and database procedures. I assume such design is mostly used when Oracle (and maybe MS SQL Server) databases are used.

I do agree such approach improves security but only if there are not much users and every system user has a database account, so we might control and monitor data access through standard database user security. However, how such approach increases the security for an average web system where thick client model is used: for example one database user with DML grants on all tables, and other users are handled using "users" and"user_rights" tables? We could use DB procedures, save usernames into context use that for filtering but vulnerability resides at the root - if the main database account is compromised than nothing will help. Of course in a real system we might consider at least several main users (for example frontend_db_user, backend_db_user).

So, does the usage of pl/sql stored procedures (all DML operations through procedures) help to increase security in thick client model systems (web systems)?


texts: Partition elimination and date variables

texts:
I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older than the retention date in order to catch any that are in the default.

The delete statement is not using partition elimination.

I select the date from a table into a date variable, then use that in a query:

declare v_retention_date date;

select retention_date
from schemaname.parametertable
into v_retention_date;

select count(*)
into v_row_count
from schemaname.tablename
where pdate < v_retention_date;

Other queries are doing partition elimination, those are passed in as text parameters to the procedure. Why might this query not do so?


texts: How to properly configure SQL Database Mail to send through an Office 365 SMTP server without using an on-prem SMTP server?

texts:
We used to use SQL Database Mail through an on-prem SMTP server, but have since transitioned to Office 365.

I created a new profile for O365 and tried configuring a new account in that profile like this:

I've also tried smtp.office365.com as the "Server name".

When I try sending a test email, I never receive it.
The error message I get in the Database Mail Log is below:


texts: jdbc: does setAutoCommit(true) commits past executions?

texts:
I would like to execute some statements, and commit the entire batch at the end. I've tried the following approach:

connection.setAutoCommit(false);            // Don't commit for each statement
int[] returnCodes = pstmt.executeBatch();   // Execute all statements
connection.setAutoCommit(true);             // Back to normal state - future statements
                                            // will be committed instantly
connection.commit();                        // Commit our batch

Which fails miserably:

java.sql.SQLException: Can't call commit when autocommit=true
--reference to the line with connection.commit()--

What's the right way to go? does connection.setAutoCommit(true) commits all executed batch statements?


texts: Settings for a MySQL backup only slave instance

texts:
I have two instances of MySQL running one a server, set up as master and slave.  It's all working smoothly together and I'm only using the slave instance for backups to avoid locking up the live databases.

What settings would be ideal to lower the Slave instance requirements as much as possible, that aren't important for replication performance.  For example, would setting the innodb buffer to the minimal amount make sense?  Are there any other bits of MySQL that could be lowered or turned off?

Alternative backup solutions may not be an option due to having to run on Windows.


texts: PostgresSQL copying newer entries from a table in one database to a table in a database on a remote machine

texts: I have two databases, I'm going to need to get more recent data from one database table and place it in another table that is on another PostgresSQL database on a different machine. The structure of the tables is identical. How is the best way to go about this?


texts: On DB2, who updated a record and what did the record look like before the update?

texts:
(SQL Server 2008 R2 Standard, database under full recovery)

I have a table with fields id, firstname, lastname. A statement is executed:

insert into dbo.sometable (id, firstname, lastname) values (1, 'John', 'Smith')

After a few hours another statement is executed:

update dbo.sometable set firstname='Matt' where id=1

Is there a method or a tool that would allow us to see the history of a record (that a record was inserted on X date by Y person and these were the values inserted, and that same record was updated on A date by B person and these were the new values after the update)?

Users familiar with DB2 for i (ie, DB2 on the iSeries) might know of a TAA toolset command called CRTDBFJRN that can dump all transactions on a given table into another table giving a clear picture of the insert, the update and the field that was updated.

SQL Server Enterprise has CDC but we have a Standard edition; I could use triggers to track changed data by storing in an audit table. The use of triggers will require two more objects - a trigger and an audit table, and triggers would be fired on each change. It would be nice if a tool could replay transactions on demand from a transaction file.

What tools do you use or tasks do you perform when an auditor asks for a history of a financial record (inserted and updated on a single day, for example) such as: Who changed this record, what was changed, when was this record originally entered, what was the data when it was original entered etc?

Any help will be appreciated. Thank you.


texts: Storing Social Security Number as DECIMAL(10,9) for Columnstore

texts:
I am working on a VERY large database (10B + rows) that performs matching on SSN and BirthDate to try and find duplicate records.  The table is using columnstore compression (SQL SERVER 2016) and it occurred to me that i could save the SSN as a DECIMAL(10,9) to preserve the leading zeros and not take the performance hit from a CHAR/VARCHAR.  I am just wondering if anyone has tried this or if there is a reason why this would not work as expected. I know I could cast to INT and just lose the leading zeros but this seemed like a better solution to me.

ISNULL(TRY_CAST('.' + SSN AS DECIMAL(10,9)),0) AS DecimalSSN

I can always cast it back to a string with RIGHT(TRY_CAST(DecimalSSN AS VARCHAR),9) AS SSN


texts:
I'm being asked to provide historical proof before I can maintain indexes - which is fine - over the past year I've kept a table full of index usage statistics for all the tables on the server.

I show some indexes that are barely used over the last year, and they're large sizes, and I have been asked which queries used those indexes.  Can I show them, can I prove that they won't be adversely affected or can we mitigate the effects.

Now, I know I am on SQL Server 2014, so I don't have the QueryStore, but I can look up Query Plans in the Cache... only it's difficult interpreting the XML to see if the index is being updated/inserted or if it's being used for query.  The problem here is, the cache is flushed regularly, and no references to these indexes are in it.

Any ideas on how on earth I can, short of waiting another year and saving the entire query cache regularly to a table, show which queries/stored procedures have used an index?


texts: Efficient way to move rows across the tables?

texts:
This is somewhat long question as I would like to explain all details of the problem.

System Description

We have a queue of incoming messages from external system(s).
Messages are immediately stored in the e.g. INBOX table. Few thread workers
fetch the job chunk from the table (first mark some messages with UPDATE,
then SELECT marked messages). Workers do not process the messages,
they dispatch them to different internal components (called 'processors'),
depending on message command.

Each message contains several text fields (longest is like 200 varchars),
few ids and some timestamp(s) etc; 10-15 columns total.

Each internal component (i.e. processor) that process messages works differently.
Some process the message immediately, others triggers some long operation,
even communicating via HTTP with other parts of the system. In other
words, we can not just process message from the INBOX and then remove it.
We must work with that message for a while (async task).

Still, there are not too many processors in the system, up to 10.

Messages are all internal, i.e. it is not important for user to browse
them, paginate etc. User may require list of processed relevant messages,
but that's not mission-critical feature, so it does not have to be fast.
Some invalid message may be deleted sometimes.

Its important to emphasize that expected traffic might be quite high - and we don't
want bottlenecks because of bad database design. Database is MySql.

Decision

The one of the decisions is not to have one big table for all messages, with some flags column that will indicate various messages states. Idea is to have tables per
processors; and to move messages around. For example, received messages will be stored in INBOX, then moved by dispatcher to some e.g. PROCESSOR_1 table, and finally moved to ARCHIVE table. There should not be more then 2 such movements. W

While in processing state, we do allow to use flags for indicating processing-specific states, if any. In other words, PROCESSOR_X table may track the state of the messages; since the number of currently processing messages will be significantly smaller.

The reason for this is not to use one BIG table for everything.

Question

Since we are moving messages around, I wonder how expensive this is with high volumes. Which of the following scenarios is better:

(A) to have all separate similar tables, like explained, and move complete messages rows, e.g. read complete row from INBOX, write to PROCESSOR table (with some additional columns), delete from INBOX.

or

(B) to prevent physical movement of the content, how about to have one big MESSAGES table that just stores the content (and still not the state). We would still have other tables, as explained above, but they would contain just IDs to messages and additional columns. So now, when message is about to move, we physically move much less data - just IDs. The rest of the message remains in the MESSAGE table unmodified all the time.

In other words, is there a penalty in sql join between one smaller and one huge table?

Thank you for your patience, hope I was clear enough.


texts: Best way of aggregating, storing and using data in SQL Server (triggers, scheduled jobs, SSAS?)

texts:
We have a table with spatial data and some attributes and need to aggregate it so it could be used in various queries. We have the mechanism to do the aggregation on-the-fly via CLR stored procedures but I was wondering what would be the best way of caching this aggregated data in SQL Server 2008 R2 as the aggregation is very slow.

So we have table like this:

[TableId] [int] NOT NULL,
[FeatureId] [int] NOT NULL,
[MeasureFrom] [float] NOT NULL,
[MeasureTo] [float] NOT NULL,
[Value] [smallint] NOT NULL,
[Timestamp] [datetime2](7) NOT NULL

FeatureId, MeasureFrom and MeasureTo basically define an interval and Rating is the value of this interval. Note that FeatureId actually references a real lines (shapes) from a different table. We have about 250k rows in this table currently with overlapping intervals and various timestamps (historical records). Our aggregation process tries to find the latest Value across all intervals so it needs to cut and join intervals based on timestamp and keeps the related value.

As the on-the-fly querying is slow and takes a lot of resources, we thought we might need to create another database storing the results of this aggregation. The source table is constantly updated (but not too often, once in a couple of days).

What would be the best way of creating this database and keeping the aggregated values up-to-date? I can think of using triggers, or scheduled task. I have no experience with SSAS but would that be more suitable?

Just to note, we have a few similar tables storing slightly different values (some with more that one Value column) and hence the reason for a separate database rather than another table in the original database.

How quick would be the querying of this separate database from the original database using cross-database joins?

Edited:
To demonstrate what our "aggregation" does, here is some sample data:

FeatureId | MeasureFrom | MeasureTo | Value | Timestamp
1         | 1           | 20        | 2     | 2015-01-01
1         | 5           | 15        | 3     | 2015-01-02
1         | 9           | 10        | 8     | 2015-01-03

And the result we are getting:

FeatureId | MeasureFrom | MeasureTo | Value | Timestamp
1         | 1           | 5         | 2     | 2015-01-01
1         | 5           | 9         | 3     | 2015-01-02
1         | 9           | 10        | 8     | 2015-01-03
1         | 10          | 15        | 3     | 2015-01-02
1         | 15          | 20        | 2     | 2015-01-01

As you can see, the result actually contains more rows than the original data. Basically we need the latest Value at any point within the area. The latest value is determined based on value in the Timestamp column. When there is a gap within intervals, this should propagate to the resulting dataset.

The code is actually written in .NET and it is quite complicated. The algorithm is as follows: Get all data for a feature of interest, order the records by timestamp descending, process all records in a loop. Take the interval (MeasureFrom, MeasureTo), intersect it with the result that you have, if you get some part that you don't have already, add it to the result. Continue with the next record. Data represents surveys done regularly so further you go in time, the more data will be thrown away as you will already have newer Value for the same Interval.

Surveys are coming in with random Timestamps so it is not always the latest one that comes in. Also same process is then repeated for other features as you might be interested in getting values for a couple of features at once. And this is the result people would query often.

Ideally we would like to keep the aggregation result as close to being real-time as possible.

To explain the required algorithm a bit more, see the link posted by @dnoeth (in the comment below) http://sqlmag.com/sql-server/packing-intervals-priorities . It solves similar problem with the difference of using priorities instead of Timestamps and the solution described uses features from SQL Server 2012.


texts: How do redundant indexes impact query performance?

texts:
The indexes are affecting the ADD/UPDATE operations however I was wondering whether the redundant indexes will affect the SELECT query performance or not?

How the redundant indexes are handled by the SQL Server in case more than one indexes are eligible to use by the select query?


texts: Linq compiled query uses clusted index instead of filtered index

texts:
Given the following table

CREATE TABLE Test(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Val INT
)

CREATE UNIQUE INDEX UX_Test_Val ON Test(Val) WHERE Val IS NOT NULL

DECLARE @i INT = 0
WHILE (@i < 30000)
BEGIN
INSERT INTO Test(Val) VALUES(@i)
SET @i = @i + 1
END

and I have a linq compiled query from a statement similar to

Test.Where(x => x.Val == 123).FirstOrDefault();

this gets compiled to something similar to

DECLARE @V INT
SELECT TOP 1 Id FROM Test WHERE Val = @V OR (Val IS NULL AND @V IS NULL)

Seems legit. But I am very disappointed when seeing the clustered index scan in the query plan.

The plan looks way better when I remove the second clause.

SELECT TOP 1 Id FROM Test WHERE Val = @V

This time the plan is a non clustered Index Seek with 1 row read

So my first guess is that the filtered index is the evil here and I created a second non-clustered not unique index on the same column

CREATE INDEX IX_Test_Val ON Test(Val)

This time both statements uses the newly created index and yields to the same index seek plan.

Here comes three questions:

Why does filtered index has such poor performance in this case?
How can I make linq to omit the IS NULL part?
Is there a way to enforce unique constrain that allows NULL with just one non filtered index?


texts:
My database server is being decommissioned at work. We have a new database server to take its place. Both servers have the same operating system and SQL Server 2008. I need to move all my databases and data over to the new server.

What is the best way to do this to ensure as few problems as possible?

My data is not just tables, views and stored procedures. There are also several SQL Server Agent jobs and several database maintenance plans and scheduled backups.

The system is not being used at night, so I have a late night opportunity to take the database offline, if need be.

Can I just copy the MSSQL directory over to the new server? Or should I just backup each database individually? If I do backup each database, should I back up the system databases (like master, model and msdb) too?


texts:
Description

I am building a database for storing social events. They can be one-time and recurring. The price and capacity can differ on per-date basis. The events have variants that can also alter the base price. Not all variants are available every date, thus dates are per-variant.

Tables

I was thinking of having the following structure (fields omitted for simplicity):

event  event_variant  event_date  event_recurrence  event_config
-----  -------------  ----------  ----------------  ------------
id     id             variant_id  variant_id        id
name   event_id       config_id   config_id         event_id
       plus_price     date        rrule             price
                                                    capacity

The recurring events would then be generated in a materialized view. If date and recurrence overlap, date would be preferred.

Event

The event table holds basic information important for searching, like the location, description, event category.

Event variant

A specific variant of an event. Each event has 1-n event variants. Event variants have their title and description, as well as additional price information.

Event date and recurrence

Recurrent dates are ones that are known to happen beforehand. Dates are for irregular events. Each irregular and recurring date has a variant assigned. Some variants may not be available on certain dates. Each date also has a config assigned, with price and capacity for the specific date/recurrence.

Event config

Holds information about the max capacity (current cap. calculated based on purchases) and the price of the current event.

Example

Event named escape room. Has a description, images, etc.

Variants

Basic - no extra cost, lasts 1h
Extended - more sub-rooms unlocked, lasts 2h. Costs 5$ extra

Dates

Every tuesday and friday at 18:00 (stored in event_recurrence), with special dates for some holidays (stored in event_date).

Prices

20$ during summer holidays, 15$ the rest of the year (recurring dates). Certain dates can cost extra (christmas, easter...).

The problem

Basically this rustles my jimmies in two different ways:

Does event_config need a reference to event via event_id? It makes sense to know to which event the config belongs to, but at the same time it feels redundant since it's implicit from the relationship with dates.
Isn't event_config an unneeded complexity? Its sole purpose is to provide shared fields to event_date and event_recurrence. Wouldn't it be better just to have the price and capacity columns both in date and recurrence?


texts: sp_prepexec (sp_execute) vs. sp_executeSQL

texts:
The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?

I am not a DBA, so please use little words. Our application sends over prepared statements that, from the profiler, I see run all SQL through sp_prepexec which is a system procedure for both running sp_prepare and sp_execute. What I'm trying to do is figure out if I am benefiting from temp table caching.

I've been using this guide with object_id() to examine behavior

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

Then point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can:
http://blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs-sp-executesql.aspx

In my query sent over via the client I have created a simple temp table.

DECLARE @foo int; -- set by JDBC, unused but required to force a prepared statement

SELECT 1 AS id
    INTO #tmp

SELECT OBJECT_ID('tempdb..#tmp');

In profiler, I can see:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int',N'declare @foo INT = @P1

SELECT 1 as id
    into #tmp

select Object_id(''tempdb..#tmp'');
DROP TABLE #tmp;',1
select @p1

I also get a cachehit from this. However, the object_id of the temp table appears to be changing on me, which is not the behavior I would see if this temp table were created in a real stored procedure. However, when I run this same code through sp_executeSQL, I'm also seeing that the object_id of the temp table has changed. This leads me to believe that only "real" user created stored procedures take advantage of temp table caching.


texts: SQL Server: Invalid Log Scan Number (...) passed to log scan in database (...) is not valid

texts:
We are getting a large number of the below errors on one of our production databases. CheckDB is completely clean.

We thought it was related to replication so we shut it down using sp_removedbreplication and the problem went away. For other reasons, we did a restore from last night backup which resurrected replication again.  Immediately the errors returned and have persisted despite my removing replication again.

Error: 9003, Severity: 20, State: 1.
The log scan number (390576:5234:44) passed to log scan in database 'XXXX' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the
problem results in a failure during startup.

I'm not finding much on the web related to this except articles for:

The master database and SQL Server not starting
Issues with replication

Any advice would be tremendously appreciated!


texts: How to deny a user to access (select, insert, delete, update) to tables but grant (select) a few

texts:
i am trying to deny a user to access (select, insert, delete, update) to tables on my database but i also need him to grant only select to a few tables,
to do this i created a user with login and gave him datareader permission then by using the code below i Denied user to Select data from a table,

DENY SELECT ON "dbo"."Customer" TO "test"

He can't see the table which is fine but he can still select the data by

select * from Customer

what i want is to deny select data from table, i don't care whether he sees the table.

for further step i will deny users to access tables but few views.

here is the image;


texts: presentation of non-ascii characters (ñ)

texts:
I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example.

What kind of setup do I need to perform at the database, in order to deliver non-ascci to a Python application?

To give you more detail. Until now, I changed all the character_set_* and collation_* variables to UTF8 . But is still not working.. Before these modifications, the charset and collation were configured to latin-1.

MySQL

`mysql> SHOW VARIABLES LIKE "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM tjs_stage.avisos WHERE  Collation="utf8_unicode_ci"\G;
*************************** 1. row ***************************
Field: Titulo
Type: varchar(32)
Collation: utf8_unicode_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: Aviso
Type: varchar(256)
Collation: utf8_unicode_ci
Null: NO
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
2 rows in set (0.00 sec)

mysql> SELECT * FROM tjs_stage.avisos \G;
*************************** 1. row ***************************
Id: 1
Titulo: PRIMERO AVISO
Aviso: MAÑANA EMPIEZAM VACACIONES
Creacion: 2016-04-06 18:12:52
Modificacion: 2016-04-06 18:12:52
*************************** 2. row ***************************
Id: 49
Titulo: Aviso de Vacaciones
Aviso: Mañana empezam las vacaciones!
Creacion: 2016-05-26 21:46:34
Modificacion: 2016-05-26 21:46:34
2 rows in set (0.00 sec)`

Python

Here is the code which I developed just for test purposes:

`#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
import sys

try:
    con = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "drootmysql", charset='utf8')
    print "\nMySQL connection: OK\n"

except:
    print "\nConnection failed...\n"
    sys.exit()

cur = con.cursor()

insert_query = "INSERT INTO tjs_stage.avisos (Titulo,Aviso) VALUES('Aviso de Vacaciones','Mañana empezam las vacaciones!')"
cur.execute(insert_query)
con.commit()

select_query = "SELECT * FROM tjs_stage.avisos"
cur.execute(select_query)
con.commit()

records = cur.fetchall()

for row in records:
    print row`

Here's the result:
    `$ python mysql_json_data.py

MySQL connection: OK

(1L, 'Aviso de Vacaciones', 'Ma\xc3\xb1ana empezam las vacaciones!', datetime.datetime(2016, 5, 26, 16, 38, 40), datetime.datetime(2016, 5, 26, 16, 38, 40))  `

I just want to retrieve the data as it is from the database.
Is there any other setup that should perform in MySQL in order to server the non-ascii characters as it is ?

Thanks in advance guys!


texts: Does pt-table-checksum work in circular replication environments?

texts:
I have 3 masters in circular replication. I run pt-table-checksum in one of the masters only.
A->B->C->A

The queries do get replicated the "next" server B but I don't think they are being replicated "properly" to the third server C. The checksums are always identical to the B server.

We are using MySQL 5.6.17 and Percona toolkit 2.2.12. Also we are using mixed binary log mode in all three servers.


texts: Read only database for time series event data

texts:
I have a time-series dataset (around 500 million rows, 4 columns) that stores public transport ticket validations.

Not sure if it matters, but it's an event driven dataset, without constant timestamp intervals, and with possibly overlapping timestamps.

The data will be written only once, so write speed is not important to me.

My work is mostly related to data analysis and processing, so a high read speed is crucial.

Having almost 0 database experience, I started looking into popular time-series databases and experimented with influxdb. However, reads were excruciatingly slow.

From what I understand, influxdb (and other time-series databases) is designed to support high write speeds, which I don't need.

What database fits my requirements?

EDIT:

3 columns are ints (4 bytes), 1 column is a bool


texts: How to enable force ssl on AWS Aurora Postgres?

texts:
In AWS's direct postgres offering RDS Postgres, you can require SSL by setting the parameter group item rds.force_ssl to 1.

This is as per the RDS postgres documentation:  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

However Aurora Postgres does not have this parameter item, and there is no ability to add new parameters either.  How do you turn on require ssl for an Aurora instance?

I can see in the Aurora version that it supports SSL:

create extension sslinfo;
select ssl_is_used();
select ssl_cipher();

And I can optionally connect via ssl:

psql -h my-ssl-test1.cwzhlddlylx.us-east-1.rds.amazonaws.com -p 5432
-U myuser -d mydb sslrootcert=rds-ca-2015-root.pem sslmode=verify-full


texts: MySQL get next unique value without auto increment

texts:
I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization.  There are multiple 'sites', and each site has work order numbers starting at 100.

WorkorderID    SiteID    SiteWorkorderNum
     1           1            100
     2           1            101
     3           2            100

WorkorderID is the auto increment field.
SiteID and SiteWorkorderNum are both set as a unique constraint.

Whenever a new work order is about to be inserted for a specific site, the application checks for the max(SiteWorkorderNum) for the siteid of whoever is logged in and returns that value to the insert statement.  The problem I want to avoid is if two users of the same site both enter a new work order at the exact same time.

I have a couple of possible solutions but I want to see if there is a better way as each has it's drawbacks, but one I'm sure will be better than the other, and of course I'm open to new ideas.

1) Lock the table to guarantee no other users retrieve a SiteWorkorderNum value until after we have retrieved and inserted our values (however let's pretend we have thousands of users trying to enter work orders every minute, wouldn't the table locks slow things down?)

2) Don't lock the table, and retrieve the next available SiteWorkorderNum and try to insert into the database, if I receive a unique constraint error, catch the error and try again until I'm successful.  (this might keep the table freed up, but again pretending we have thousands of users at the same site, would the multiple database calls be a little inefficient?)

Am I being too paranoid about how either of these solutions 'could' effect performance?  Of course I don't have thousands of users, but I'm wanting to apply best practice in this design in case I ever work on a project which does have high traffic.


texts:
I've been reading all the articles (and comments) linked from Stop Shrinking Your Database Files. Seriously. Now. I understand that shrinking is bad.

What I have a hard time figuring out is how to do it right when its actually required.

The backstory is that I have inherited this archive DB with a handful of tables containing PDF's that should have been expired after 5 years, but the procedures to do this on a schedule was never set up. I don't have the row counts at the moment but it's a few million, and about 3TB. I guesstimate about half of this will be deleted and never come back.

I've been talking to our infrastructure providers DBA, but I find the advice they give (delete, reindex then shrink) to be highly dubious. I'm a system consultant not a DBA, but it seems I get to play one this time :-/

Paul Randal recommends two approaches

1

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup
  using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

Unfortunately I don't have access to the actual server just SSMS, and this is quite a bit outside my comfort zone anyway. I'm not actually a DBA..

2

  DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

Yes but how.. there are quite a few parameters to choose from, and this seems optimized to get the size absolutely minimal, and not what I need which is to get rid of the bloat while keeping the DB as healthy as possible.

In other words I don't care if the DB grows some, it needs space to operate and it will grow some as document production increases year by year, but the nightly, weekly or monthly purging will now keep this in check.

I should also add that I can take this DB offline at night without much trouble.


texts: ERROR: improper qualified name (too many dotted names) when trying ALTER FUNCTION

texts:
Working with Amazon Aurora Postgres Version 2.0, which is basically  PostgreSQL 10.4 with a modified storage layer.

Trying to restore a dump that contains multiple lines like:

ALTER FUNCTION myschema.f_myfunc(anyarray, anyelement) OWNER TO myrole;

I get an error message for every ALTER FUNCTION like that:

ERROR:  improper qualified name (too many dotted names)

Trying the same in psql produces the same error. Even with the simplest form:

ALTER FUNCTION foo() OWNER TO myrole;

There are no dotted names. I found similar complaints here:
https://forums.aws.amazon.com/thread.jspa?messageID=872096&tstart=0

Must be a bug in Aurora - or am I missing something?


texts:
I am new to MySQL and linux and also AWS. Previously i have worked with windows azure and .NET framework.

Currently I am trying to get DBT2 benchmark to run for AWS RDS for MySQL. I was able to connect to RDS with shell command line from MySQL. But i was not able to configure or install the benchmark on the DBInstance.

To configure the dbt2 one needs to run shell script on the system. So far i have 'cd' to directory where files for dbt2 are located. But when i try to run './configure' it gives error that "'.' is not recognized as an internal or external command, operable program or batch file." Same error if i use 'sh ./configure'.

I am just a starter in this, is there something i am doing wrong?

How can i even run any shell script on AWS RDS system?

Any help is appreciated.


texts: Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?

texts:
As the question states, what (if any) is the effect of max_allowed_packet on LOAD LOCAL INFILE?

Does setting it to a large value load data files of large size such as 700MB into the db quickly?


texts: Database normalization, 3 mistake [stucked] - Homework

texts:
I've been given a homework at university, which says I should make a database with 7 tables [see picture below]. The scheme I made is good, except some normalization is needed, as my tutor told me. (Side note: I have already done normalization with CONTINENTS and COUNTRIES tables)

After I have contacted with my tutor, he told me I'm missing two normalization in this schema and there is a database modelling mistake. However, this is all I know about my mistakes.

My thoughts about my possible mistakes:

In CUSTOMERS table COUNTRY_NAME should be a FK, referencing COUNTRY table's COUNTRY_NAME.

(In this case the CONTINENT_ID is not necessary in my opinion)
In ORDERS table the TOTAL_AMOUNT is redundant because of ITEM table's QUANTITY. Is it a problem, and if so, how can I get rid of it?
There are lots of 'CITY' columns with VARCHAR2 type. This can be a problem when 1) the user misspell them in other tables or 2) VARCHAR2 is taking up too much storage. Should I make a ZIPCODES table with zipcode and city columns and use it instead?
Repeating records: In WORKERS table there is a column called EMAIL. In my opinion a worker can have multiple e-mail addresses, so in this case: Can this cause me trouble, i.e. causing repeating records?
CUSTOMER/WORKER table and DIVISION/WORKER table have a N:M (?, not so sure) relationship. So is it necessary to make another table between them?

I would be really thankful if somebody can find the missing normalizations and answer my questions.

Responses to clarification requests

About the modelling mistake: I don't have much information about business rules. It's because of this lesson is about normalization, so this is kind of a cherry on the cake. Not a big problem if I won't find it.

About the normalization: I'm pretty sure, that I should decompose the current structure up as mentioned via comments. The question is, what I'm missing here. I'm starting to go blind because of staring too much at them.


texts:
I am trying to install SQL Server 2012. During the install I get an error saying

  Error installing SQL Server Database Engine Services Instance Features
  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: TCP
  Provider, error: 0 - No connection could be made because the target
  machine actively refused it.) Error code: 0x84BB0001

I have run the setup  and given my user login and password as the account and password credentials for the all SQL server services (I'm happy with this, just need the program up and running to extract some data from a .mdf file)

I have set the database engine configuration to Mixed Mode and specified my password for the system administrator account.

The database engine does not install and the program tells me to uninstall these components and re-install them. I have done that several times now (using different credentials etc) not sure what to do next.

I have previously had SQL 2008 running on this machine so dont think there would be anything preventing me from getting SQL 2012 to work?

I also managed to install the express version which installs fine. But the .mdf file I need to use is larger than 10gb.


texts: MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss

texts:
Originally posted to https://www.facebook.com/groups/GNUAndLinux/permalink/10155020539805019/ but no replies received.

I run Percona Server 5.6.21-70.0-688 x64 on Ubuntu Server 12.04 LTS x64, 64GB RAM, 2x2TB SATA* in hardware RAID0 and Intel Xeon E5-1620 v2 3.70GHz. InnoDB/XtraDB is the primary engine (with Barracuda format), with 20GB (21 after recent change) buffer pool size divided to 20 (21 after recent change) pools. The zlib compression is maximized to 9.

The Database Servers and its threads are dropped to nice -20 and ionice -c 1 -n 1.

Almost every Server on rautamiekka.org depends on MySQL databases, especially Minecraft.

I found out how to make a perfect list of IPv4 addresses into a text file. I created a table with auto-increment column and 1 column for each address section, to maximum of 5 columns in total. I made a Python script that started a transaction and explicitly disabled auto-commit and uploaded those addresses. The script had uploaded so many addresses from the 115GB file it had about 51GB of data in, likely already compressed. Then Percona Server 5.6.21-70.1 came out. I thought I'd run the routine update process cuz I thought there'd be no problems so I shutted down Minecraft Servers and any other Server depending on MySQL and forgot about the Python script. However, after fighting with aptitude and apt-get for few minutes I realized Database Server was still running and not accepting any connections since it was the in the shutdown process. I noticed the Python script had quitted by way or another.

That was Monday 2014-11-24, and the many processes/threads have been running since then, at 20-50KB/s (read) or even 200, and 120-500KB/s (write). It's mainly 1 that runs read and 2 that run write, with 1 process running at least twice as fast as the second, and only 2 processes, mainly, use CPU, but they're not the only ones. I presume it's committing the data into the table and reading the compressed data to verify it.

My estimations say the shutdown will take 9 days if we assume it writes 80GB at 120KB/s at minimum, and I warned ppl it might not be ready until Monday 2014-12-08 although it's been running for days now.

I made changes to the config just recently:

[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

I've had

[mysqld]
innodb_fast_shutdown = 0
innodb_flush_method=O_DIRECT

for ages to ensure InnoDB will write everything before going down so we'll have smaller chances of data loss, but due to that we've had to wait for ages, and probly have to for at least a week, which I hate.

For reference, I posted a change request to MySQL bugs asking for changing the way MySQL handles order to shut down to prevent this kind of downtime: http://bugs.mysql.com/bug.php?id=75110

What can I do here ? I can consider every method to bring the process down, even loss of data if the loss ain't big, particularly if I lose just a portion of the IPv4 cuz I'll use MySQL script to continue filling up the table. However, I'm afraid I could lose something else, which is almost not an option, but I'll see.


texts:
Now I hear that the best practice for naming foreign key columns is the name of the table and field (and maybe the function if there are multiple foreign keys to the same table), for example, a foreign key that references the users table id column would be:

creatorUserId

Now the thing is that right now I am building my application and for the sake of development speed, I am going to be referencing everything by a surrogate foreign key (INT AUTO_INCREMENT).  I really don't want to get into the whole which is faster than what in which case this early in development, I rather leave that stuff to the end (to try to prevent premature optimizations and I am more interested in working on the front-end of this application then the backend).

With that being said, a concern I have is if I in-fact want to change some of the foreign keys from the surrogate key to a natural key (all relavent tables will have both) and I use column names like

creatorUserId

I am going to have to go through all the backend code in order to make the change, a process that would not be very pleasant.

I was thinking about doing something that just use the function and table name for foreign keys column like:

creatorUser

In the case, if I change what column I am using for the foreign key, the backend code changes are a lot simpler and there would be a lot less of them (if you are using an ORM which I am).  Also, since surrogate keys are generally numbers and natural keys are generally alpha characters, it should still be relative easy to know which one is being used just by looking at the value.

Would this be considered bad practice?


texts: Why does sql server prefer the nonclustered index over the clustered index?

texts:
I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However, as I have run queries against the table I have found that SQL Server always wants to use the nonclustered index instead of the matching clustered index. On top of that, when needed SQL Server will properly do an index seek on the nonclustered index, but will always perform a scan on the clustered index.

Why does SQL Server prefer the nonclustered index?

And how can I rewrite this so I still have the performance increase but only the clustered index?

I have the following table structure:

CREATE TABLE [dbo].[Variables](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Header] [varchar](255) NULL,
    [FullVariables] [varchar](max) NULL
)

Clustered index:

ALTER TABLE [dbo].[Variables] ADD  CONSTRAINT [PK_Variables] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)

Nonclustered index:

CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20190307-091011] ON [dbo].[Variables]
(
    [ID] ASC
)
INCLUDE (   [Header],
    [FullVariables])

My current knowledge leads me to believe that in this case both of those indexes should contain the data laid out in the same fashion with [ID] being the key column and then [Header] and [FullVariables] as extra data contained on the index instead of being pointers. If you have some source of knowledge that you could link I am more than eager to read more.

I should specify that I don't always want a seek and I understand that a scan is better in some cases (otherwise why would be have it). The table contains about 60GB of data due to row size (several million) multiplied by the varchar(MAX) (which contains strings that are 16000+ characters long). Before inserting into the table, a scan is done to ensure no duplicates are insterted (matching on Header for elimination and on FullVariables). Then the table is joined in several views on the ID field where the seeks are desired.


texts:
I have a global database, as I all users should have access to it. Is it possible to grant permission on a database to all users? something like

GRANT ALL ON my_database.* TO *@localhost;

Even (for security reason), it can be read permission only.


texts: Database Stuck in "Restoring" in SSMS After Running From an Outside Process

texts:
I am running a RESTORE DATABASE procedure with RECOVERY and REPLACE from an outside process (Windows Batch using a Python script) and when I do so, it makes my database stuck in "Restoring..." mode every time.  But if I run the procedure manually, it works just fine.  Any thoughts on what might cause this?  I have come up blank in my research [I wasn't sure whether to put this here or the Python community, so I started here].

For example:

Python executes this:

exec DB_RESTORE 'MyDatabase','MyDatabase_BACKUP'

Which runs this:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE DB_RESTORE
    @FROMDB VARCHAR(MAX) = '',
    @TODB VARCHAR(MAX) = ''
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX)
    DECLARE @FILENAME VARCHAR(MAX)
    DECLARE @PATH VARCHAR(MAX)

    SET @PATH = 'C:\temp\'
    SET @FILENAME = @PATH + @FROMDB + '_' + 'backup.bak'
    SET @SQL = ''

    SET @SQL ='RESTORE DATABASE ' + @TODB
    SET @SQL = @SQL + ' FROM DISK = ''' + @FILENAME + ''''
    + ' WITH RECOVERY, REPLACE, STATS=10'

    PRINT @SQL
    EXECUTE(@SQL)
END
GO


texts: How to interpret timestamps stored as 10-digit negative integers?

texts:
I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers.

How do you process these as if they were DATETIME in SQL queries?

If it simply can't be done through SQL and must instead be done through a programming/scripting language, what maths/process is required to transform these values into human-readable dates and times?

Example values:
-1358667448, -1358667348, -1358667248

The data was captured during the period 2006-2019 so the timestamps will be within that time frame.


texts: Using SELECT within to_tsvector call in CREATE INDEX

texts:
I'm attempting to create a GIN index for full-text search on a field that may have text in it that is one of several different languages.  The languages are pre-set and we'll know which language we want to search in at query time.  Here is the schema I've got thus far...

CREATE SCHEMA source;
CREATE SCHEMA common;

CREATE TABLE common.lang (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    code TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

INSERT INTO common.lang (name, code) VALUES
  ('english', 'en'),
  ('arabic', 'ar'),
  ('chinese', 'zh'),
  ('undefined', 'und');

CREATE TABLE source.user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    screen_name TEXT NOT NULL UNIQUE,
    profile_lang_id INTEGER NOT NULL REFERENCES common.lang(id) ON DELETE CASCADE ON UPDATE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX source_user_names_idx ON source.user USING GIN (to_tsvector(SELECT common.lang.name FROM source.user INNER JOIN common.lang ON common.lang.id = source.user.profile_lang_id, name || ' ' || screen_name));

It reports a syntax error at or near "SELECT", which makes sense...I didn't think I could just shoehorn a select statement in there, but I'm not sure how to correctly use a select statement within the to_tsvector call.

I looked at https://stackoverflow.com/a/21299033/4471711, however that hardcodes the languages, whereas I want the languages to be pulled from a table dynamically at index creation time.  Obviously, if/when we add a language, we would need to recreate the index.

I also looked at https://stackoverflow.com/a/15135730/4471711, however that is not in support of creating an index and only requires 1 parameter to to_tsvector, whereas I seem to need to pass in both the language and the text column.

I am also trying to create a full-text search index on a more document-style field as below:

CREATE TABLE source.post (
    id BIGSERIAL PRIMARY KEY,
    text TEXT NOT NULL
);

CREATE INDEX status_text_idx ON source.post USING GIN (to_tsvector(common_lang_id, text));

Again, with the above index, I want to also use the language which is specified by the foreign key to the lang table.

My two questions are:

How do I properly use the SELECT statement to grab the language from the lang table using its foreign key in the user table?
What if the lang is "undefined" as is one of the options?  I wouldn't mind defaulting to english for purposes of the to_tsvector call...How do I add in that default while still allowing the user table to reference "undefined"?


texts: Store last elements in a dedicated table

texts:
i'm thinking about something.
Consider a table "messages" which stores messages between member. This table can potentially billions messages.
Looking for all message from a message would become a long query (in term of time)
is it a good idea to put last message in a dedicated table ?
So each entry in the table 'last_message' looks like

id | msg | user_pk

This table is updated each time a user add a new message, the previsous one goes to the table "message"
So each time we look for all last message we just have to look into the last_message table which contains less data than "messages"

To me the main advantage is the request speed, the disadvantage is the data management bewteen "messages" and "last_messages" tables and update requests.


texts:
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of relationships, so a relational database seems to make sense.

The common solution that I have seen is to add an integer column order:

CREATE TABLE AS your_table (id, title, sort_order)
AS VALUES
  (0, 'Lorem ipsum',   3),
  (1, 'Dolor sit',     2),
  (2, 'Amet, consect', 0),
  (3, 'Elit fusce',    1);

Then, we can sort the rows by order to get them in the proper order.

However, this seems clumsy:

If I want to move record 0 to the start, I have to reorder every record
If I want to insert a new record in the middle, I have to reorder every record after it
If I want to remove a record, I have to reorder every record after it

It's easy to imagine a situations like:

Two records have the same order
There are gaps in the order between records

These could happen fairly easily for a number of reasons.

This is the approach that applications like Joomla take:

You could argue that the interface here is bad, and that instead of humans directly editing numbers, they should use arrows or drag-and-drop—and you'd probably be right. But behind the scenes, the same thing is happening.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

Is there a better way to store order in a table?


texts:
I'm new to programming and databases and working on my first application using .Net, Entity (for SQL Server) and Identity.

My application will allow users to create html pages for online courses. Think of it like a course content management system. Instead of users (instructors) having to know HTML markup to create pages for their online courses they will use form fields to create html elements paragraphs<p>, headers<h1> etc. Then they can preview and export these pages for use in their online courses on another system.

A user in the application must have an overall role which will have permissions to do admin things like add users, create courses etc. Users need to be assigned to Courses to access them and can have different roles across Courses. E.g. Person A can be an "Instructor" in Course A, and "Observer" in Course B.

So there are 2 sets of roles and permissions - One at the organization level to do admin tasks and one at the Course level to create and manage content.

Since course development is a collaborative effort with multiple people working on a course, there will be a commenting system next to html elements that users have created.

Schema Design

Am I on the right track? Does it look normalized and are the relationships correct?


texts: sql*plus 11.2 client connection to oracle timing out on Linux

texts:
One of our DBAs is seeing connection issues with his sql*plus client. Whenever he connects to the Oracle v10g server, the connection seems to time out for no apparent reason.

We tried checking iptables but we have no state-based rules.

We also have older servers on the same switch so we know it isn't a switch issue. My hypothesis: It's a client issue.

This is kind of a shot in the dark but can anyone suggest some troubleshooting steps to help out? I'll update the question with whatever details you may need to make a recommendation. Just ask in comments.

Update

Per comments:

I tested telneting to the Oracle DB server to port 1521. That worked.
Also, the initial test query works. However, after an hour, when I try the query again I get ORA-03135: connection lost contact. Full error below.

ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 14848
Session ID: 1219 Serial number: 1551


texts: Cannot retrieve values from LEFT JOIN (2 tables)

texts:
Here it is my query:

$query_products = "
SELECT
    p.Id
    , p.product_sku
    , p.product_name
    , p.hide_product
    , p.new_style_image
    , p.ext
    , p.product_thumb_image
    , p.product_full_image
    , SUM(s.stoc_final)
    , s.active
FROM
    produse p
    LEFT JOIN stoc_intern s ON p.product_sku=s.cod_website
WHERE
    (
        (" . $product_sku_search . ")
        OR (" . $product_name_search . ")
        OR ("  .$product_culoare_search . ")
    )
    AND p.hide_product != 'd'
GROUP by p.product_sku
ORDER BY SUM(s.stoc_final) DESC
LIMIT " . $items_per_page . "
OFFSET " . $_offset . "";

My problem is: the last field I'm selecting, the s.active one, has in the table the value of "n" but when I display it, it shows empty (echo $s_active);
If I change the field s.active with say other field s.description, it will work.

Also, in the table "stoc_intern" some fields that I select do display well, some do not display a thing (even if table data contains info).

Why is that?


texts:
The issue that I am experiencing is easy to reproduce.

When attempting to update the data of a row using the data tab of SQL Developer the cell will receive the first letter twice.

This happens "intermittently"; ~50% of the time when I go to enter fresh data into the cell.

This is most noticeable when you have ~10 columns of Y/N flags and want to simply Y Tab Y Tab Y and end up with Y YY YY and then Shift+Tab rolls you another ~50% chance of fixing it. See image at bottom of question.

Does anyone know of a solution?

Environment specs:

Windows 7 x64
SQL Developer 4.1.5.21.78
C:\Program Files\Oracle\SQL Developer\4.1.5.21.78\jdk\jre\release | JAVA_VERSION = 1.8.0_102

I've been using the latest versions for over 3 years now and this issue has been persistent in all of the versions.

Example:


texts:
Query:
EXPLAIN ANALYZE select count(*) from product;

ROWS: 534965
EXPLANATION :
Finalize Aggregate  (cost=53840.85..53840.86 rows=1 width=8) (actual time=5014.774..5014.774 rows=1 loops=1)
  ->  Gather  (cost=53840.64..53840.85 rows=2 width=8) (actual time=5011.623..5015.480 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=52840.64..52840.65 rows=1 width=8) (actual time=4951.366..4951.367 rows=1 loops=3)
              ->  Parallel Seq Scan on product prod  (cost=0.00..52296.71 rows=217571 width=0) (actual time=0.511..4906.569 rows=178088 loops=3)
Planning Time: 34.814 ms
Execution Time: 5015.580 ms

How can we optimize the above query to get the counts very quickly?
This is a simple query, however, its variations can include different conditions and join with other tables. Consider a situation where a user searches something from the frontend of the website and we want to display X number of results match your search. And this can include lots of filters.
Following improves performance but uses more resources.
set max_parallel_workers_per_gather to 4;
It performs a bit faster now as compared to the originally posted question due to the right index and using parallel_workers. Still, looking for a more efficient way, since it's a common problem people try to find a solution, particularly to support client side applications, specially in dashboards.
My use case right now is to retrieve some records to display on the client-side application along with pagination.
Overall there are two queries. The first one is to select the records; the second one is to select the count (on the frontend for pagination as well). The first query takes less than a second and the second query takes much more time than that.
The overall time to return response is more than 4-5 seconds depending upon filters. On the frontend overall performance becomes slow.
I am using PostgreSQL 11.8.


texts: High disk IO with MySQL master-master replication

texts:
We've been evaluating an active-passive master-master replication setup for our MySQL database, running on version 8.0. While diagnosing some general slowness issues, we were surprised to see very high disk IO ranging from 500MB/s - 1500MB/s on both servers.

I've found that stopping the slave on either server brings the disk IO back to expected levels, 15MB/s - 30 MB/s, and I'm concerned that we have a misconfiguration somewhere.

Here is what I've checked:

The server ID's are unique.
We are doing row-based replication.
We are using the semi-synchronous replication plugin.
The writes are happening in an LVM volume that exclusively houses the data directory.
vmstat shows no swapping.
From the network perspective, we see a few megabits per second going between the servers at most.

Here is the redacted configuration:

[mysqld]
...

# General
server_id                        = <unique_between_servers>
event-scheduler                  = ON
sync_binlog                      = 1
log_bin                          = /var/lib/mysql/mysql-bin
binlog_format                    = ROW
innodb_file_per_table
innodb_flush_log_at_trx_commit   =  1
skip-external-locking

# Replication
relay_log                        = /var/lib/mysql/mysql-relay-bin
relay_log_recovery               = ON
log_slave_updates                = 1
sync_master_info                 = 1
sync_relay_log                   = 1
sync_relay_log_info              = 1
rpl_semi_sync_master_enabled     = 1
rpl_semi_sync_master_timeout     = 10000    # 10 seconds
rpl_semi_sync_slave_enabled      = 1

# Fine Tuning
key_buffer_size          = 16M
max_allowed_packet       = 16M
thread_stack             = 192K
thread_cache_size        = 8
myisam-recover-options   = BACKUP
max_connections          = 800
max_connect_errors       = 1000
innodb_buffer_pool_size  = 192G    # Server has 256GB RAM
innodb_log_buffer_size   = 256M
innodb_log_file_size     = 2G
innodb_flush_method      = O_DIRECT
innodb_lock_wait_timeout = 60
...

My problem sounds suspiciously similar to this person's dead thread from 2013 (Who were you, DenverCoder9? WHAT DID YOU SEE!?)

EDIT:

In an effort to zero in on the problem, we simplified the setup for testing. We used one master and one slave, both running 8.0.11, same configuration as above, and we saw the following write patterns according to iostat (master is blue, slave is red):

(The "Time" on the X-axis is the minutes and seconds of the current time.)

Those per-minute 1+ GB spikes on the master were tracked down to a script updating two small tables (1000-2000 rows each), around 10-15 MB in size on disk. These tables had keys, but the keys were not primary or unique. The script would DELETE all rows in the tables and then repopulate them by performing one INSERT statement per row, all within a transaction. Despite the glaring problems, I still don't understand how operations on tables this small can result in this much disk IO.

Without fixing the problems, we then returned to the master-master active-passive setup and saw the following (active master is blue, passive master is red):

So some additional questions I have are this. Why is the slave consistently writing much more than the master in the first graph? Also, why in master-master replication is there the ping-pong effect seen above where writes to the master result in heavy IO on the slave, which results in heavy IO again on the master?


texts:
I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market values.

Works:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY SUM( MKT_VAL ) DESC;

Fails:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY MarketVal DESC;

It appears when I try to ORDER BY the Alias (MarketVal), Access asks for MarketVal parameter value. However, when I ORDER BY SUM( MKT_VAL ), the code executes fine. I can't understand why this is, can anyone tell me why?


texts: Single DELETE statement deadlocking in Sybase ASE

texts:
I've got a fairly straightforward process running on ASE 15.7, that creates rows in two tables and later deletes them.  The tables are both configured to use datapages for the locking scheme, and the column I'm using to delete the rows is the first column in the clustered index.  Chained transaction mode is on, but the stored procedure is not part of some external transaction.

For some reason, I occassionally see deadlocks on the first line of the stored procedure.  What's weird is that it's deadlocking with another spid running that exact same line!  If the rows they're both trying to delete share data or index pages, shouldn't one spid be blocked while the other one acquires the locks and then completes?  I'm at a loss as to how to resolve or even explain this, and haven't found anything in Sybooks Online.

This is the procedure - I've anonymized the object names, but otherwise the code is copied directly from the source file.

create procedure spCleanup (
    @firstColumnInClusteredPrimaryKey int
) as
begin

delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

delete from relatedTableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

end
go

This is the deadlock information from Sybase's errorlog file.  Again I've anonymized the object, server and user names, but the text is otherwise copy-pasted directly from the log.

00:0011:00000:00166:2013/10/08 00:41:56.16 server  Deadlock Id 100 detected
Deadlock Id 100 detected. 1 deadlock chain(s) involved.

Deadlock Id 100: Process (Familyid 0, Spid 134, Suid 13) was executing a DELETE command in the procedure 'spCleanup' at line 8 at nested level 1.
Deadlock Id 100: Process 134 was involved in application 'appName'.
Deadlock Id 100: Process 134 was involved on host name 'serverName'.
Deadlock Id 100: Process 134 was involved in transaction '$del'.
SQL Text: spCleanup  2916
Executing procedure: spCleanup
Subordinate SQL Text: delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

Deadlock Id 100: Process (Familyid 0, Spid 166, Suid 13) was executing a DELETE command in the procedure 'spCleanup' at line 8 at nested level 1.
Deadlock Id 100: Process 166 was involved in application 'appName'.
Deadlock Id 100: Process 166 was involved on host name 'serverName'.
Deadlock Id 100: Process 166 was involved in transaction '$del'.
SQL Text: spCleanup  2911
Executing procedure: spCleanup
Subordinate SQL Text: delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

Deadlock Id 100: Process (Familyid 0, Spid 166) was waiting for a 'exclusive page' lock on page 67858 of table 'tableName' in database 'dbName' but process (Familyid 0, Spid 134) already held a 'exclusive page' lock on it.
Deadlock Id 100: Process (Familyid 0, Spid 134) was waiting for a 'update page' lock on page 67941 of table 'tableName' in database 'dbName' but process (Familyid 0, Spid 166) already held a 'exclusive page' lock on it.

Deadlock Id 100: Process (Familyid 0, Spid 134) was chosen as the victim.
Victim process host = `serverName', user = `userName' program name = `appName' host processes = `dbServerName' .

End of deadlock information.


texts: Use a condition to limit the databases a policy checks

texts: I'd like to create a Policy that checks, on demand, one database in an instance for tables that have no index. I can see how to create a Condition, linked to the 'Database' Facet and checks for my database's GUID. I can also see how to create a Condition, linked to the 'Table' Facet that checks for the presence of an index. When I create a Policy, however, I seem to be able to only check one Condition. I know you can add clauses to Conditions, which would be fine if I didn't have to specify a Facet and so limit my options. Can you not have multiple Conditions in a Policy?


texts:
What SQL syntax would split this column into three new columns (split would be on the underscore)?

There are other columns in the table, but I would like to split these into three (underscore based).


texts: Getting number of records in mysql inconsistency

texts:
When i check the number of records in a MySQL table using "show table status like 'table_name", the number of rows given is different from the number of rows when i run "select count(*)" on the same table.

I would like to know why the number is different, and what each number means.


texts: how wsrep_local_index is defined or could be changed?

texts:
I have a cluster of 3 nodes using Galera with MariaDB, every time I want to backup I check the node with the highest wsrep_local_index and then do:

SET GLOBAL wsrep_desync = ON;

Take the backup, once finish I do:

SET GLOBAL wsrep_desync = OFF;

But I notice that the wsrep_local_index value changes during this process, also if I reboot a node, the wsrep_local_index values are reorganized, therefore would like to understand more about how this value is defined in the first place and what are the cases that make it change, from the docs I only found:

wsrep_local_index

This node index in the cluster (base 0).

The reason for taking the backup on a cluster with the highest wsrep_local_index is that the cluster is behind a proxy that only serves traffic from the node with index 0.


texts: Best way to export excel table into MySQL db

texts:
I'm trying to export an Excel table which contains about 400 records of HR stuff (e.g. name, surname, age, job, ecc.) into a MySQL db.
I'm actually using mysql for excel and started reading a db book, but I noticed many format issues such datetimes from trying to convert from %d/%m/%Y to %d-%m-%Y.

Best practice hints for db administration are well accepted!


texts: How to Exclude Foreign Keys during Publish action in sqlpackage

texts:
I am trying to publish a .dacpac extract to a target database, I want to exclude just the foreign key constraints in all the tables.  After going through the ExcludeObjectTypes options I do not find a clear way to exclude just the foreign keys.

I need to migrate the schema structure of the source except for the foreign keys as I will be loading the data in-parallel by other means. So the requirement is to just exclude the foreign keys when I perform Publish


texts:
I have a very busy database and I've been asked to look for transactions that rollback.

I used SQL Server Profiler to monitor the TM: Rollback Tran completed event, which shows me that dozens of these events are happening every minute, but it doesn't give me any indication as to what transaction is being rolled back, what objects are affected by the rollback, or why the rollback occurred.  In short, it seems of very limited use.

If I try to also monitor, for example, SQL:StmtStarting to give some context to the rollback then there is simply too much noise on this busy environment to be able to identify which statement is responsible for the rollback.

I also tried running the statement

SELECT *
FROM fn_dblog (NULL, NULL)
WHERE Operation = 'LOP_ABORT_XACT';

but this did not return any results (which seems odd given the number of rollbacks being reported by SQL Profiler).

Is there any way that I can produce a report showing transactions that have rolled back?

TIA!


texts: Is there a way to restrict new database creation to a particular zone?

texts:
We have a sharded mongo cluster with a database per client model. Given many roughly same size clients - the default db creation has proven to work well at evenly distributing the load, while avoiding multi-shard queries.

Up till now, all shards were homogeneous SSD based deployments, however now, we are trying to add heterogeneous larger but slower HDD based shards - so each clients can have few large bulk collections located there (while their primary shard stays on SSD). This is easliy accomplished with the zoning method (2 zones, hdd and ssd) - and would work very well if for one issue:

TL;DR: Are there any ways to restrict new database creation to only happen on SSD shards?

Only way I know of is to let DB creation happen, then check where it got created and use movePrimary if it is in wrong zone. Issue with this is that process is manual and involves hunting down and restarting every mongos router - as movePrimary was not quite designed to be production ready....