texts: Overly eager explicit row locking causes deadlock

texts:
I'm running into deadlock problems on MariaDB using the INNODB engine.

In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those. I'm using a SELECT ... WHERE id IN (...) statement to load all relevant rows at once. I have been using LOCK IN SHARE MODE to ensure that other threads don't cause corruption.

While testing this on a table with a significantly reduced data set I have been experiencing deadlocks - although each thread works on a distinct set of rows. It seems the SELECT ... WHERE id IN (...) locks all rows when the number of ids exceeds a specific fraction of the size of the table. This causes a deadlock as threads attempt to acquire X locks on records which are S-locked by (all) other threads.

I've replicated this behaviour using 10.3.12-MariaDB-1:10.3.12+maria~xenial on a sample data set.

Setup:

CREATE TABLE items (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    some_col bigint unsigned,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO items (some_col)
    VALUES
        (12),
        (36),
        (72),
        (11),
        (81),
        (53),
        (28),
        (37),
        (58),
        (87)
;

Steps using two threads:

Thread A: Acquire locks

START TRANSACTION;
SELECT * FROM items WHERE id IN (1, 2, 3, 4, 5) LOCK IN SHARE MODE;

(Using WHERE id = 1 OR id = 3 OR ... has the same effect.)
Thread B: Acquire locks

START TRANSACTION;
SELECT * FROM items WHERE id IN (6, 7, 8, 9, 10) LOCK IN SHARE MODE;

Thread A: Update row with id = 1

UPDATE items SET some_col=0 WHERE id=1;

This thread hangs waiting for the X lock on the record with id = 1.
Thread B: Update row with id = 6

UPDATE items SET some_col=1 WHERE id=6;

At this point MariaDB will rollback one of the transactions because it detects a deadlock.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

SHOW ENGINE INNODB STATUS; indicates that thread A holds S locks on all records in the items table, not just the one returned by the SELECT query in step 1.

When using separate SELECT queries for locking in step 1, this issue does not arise.

  Thread A: Acquire locks

  SELECT * FROM items WHERE id = 1 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 2 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 3 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 4 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 5 LOCK IN SHARE MODE;

The documentation of LOCK IN SHARE says that "… a lock is acquired on the rows read by the query …". The observed behaviour indicates that this means all rows looked at, not just the ones selected.
ANALYZE format=json confirms that the access type is ALL for the SELECT query from step 1.

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0562,
    "table": {
      "table_name": "items",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "r_loops": 1,
      "rows": 10,
      "r_rows": 10,
      "r_total_time_ms": 0.0373,
      "filtered": 50,
      "r_filtered": 50,
      "attached_condition": "items.`id` in (1,2,3,4,5)"
    }
  }
}

Is this correct? I.e. does LOCK IN SHARE MODE lock all rows looked at during query evaluation and not just the ones selected?

Is there a reliable way to read / lock only the rows selected (without using individual SELECT statements)?

Some more observations about INs locking behaviour:

When there is only one id in the IN list, the access type is shown by ANALYZE as const and no "over-eager" locking occurs.
When there are multiple ids (less than 50 % of the rows in the table) in the IN list, the access type is shown by ANALYZE as range and no "over-eager" locking occurs.
When there are more ids in the IN list than 50 % of the row count of the table, the access type is shown by ANALYZE as all and "over-eager" locking occurs.


texts:
I am starting my first DB-Driven website project for a mock real estate agency and would like a bit of feedback on my general approach to how I will drive the site with the DB.

Specifically I'm worried about two things:

If my primary and foreign keys are correct/logical
If my approach to using image resources in conjunction with the DB is a good approach (See comments beside the tables in code below.)

A bit about the site:

Generally, the site structure will be categorized into Condo Building, and each Condo Building has a 1..* relationship with Condo Units. A user can browse by building and be presented a building overview when selecting a particular Condo Building, and a list of all Condo Units in that building which this agency is selling. There will be pictures for Condo Buildings and pictures for Condo Units and my job naturally is to chain them together with relationships.

Regarding list item #2:

I have done some googling and it seems the general consensus is that although MySQL supports storing of BLOB types, it is considered bad practice as it taxes the database. So my approach is to have an image directory for Condo Buildings and an image directory for Condo Units, and each image filename therein will be the primary key ID so that I have a way to hook into the images in my PHP

--------------------
| CondoBuildings   |
--------------------
| ID (Primary)     |  // Auto increment int
| //fields         |  // only that which pertains to the building and NOT the units
--------------------

--------------------
| CondoUnits       |
--------------------
| BLDG_ID (Foreign)|  // CondoBuildings Primary Key (ID)
| ID (Primary)     |  // auto increment int
| //fields         |  // only that which pertains to an individual unit
--------------------

--------------------
| UnitImages       |
--------------------
| UNIT_ID (Foreign)|  // CondoUnits Primary Key (ID)
| IMG_ID (Primary) |  //
--------------------

--------------------
| BldgImages       |
--------------------
| BLDG_ID (Foreign)|  //CondoBuildings Primary Key (ID)
| IMG_ID (Primary) |  //
--------------------

So basically this is my plan, but I need to be sure that this is a logical approach or if there's some substantial drawbacks to this approach that ppl with more experience could point out.

Ensuring images are properly named would be a concern perhaps...


texts:
Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row insertions/deletions while that query executes.

As I understood it, InnoDB doesn't store the row count in an internal variable, which is the reason for this problem.

My question is: Why is this so ? Would it be so hard to store such information ? It's an important information to know in so many situations. The only difficulty I see if such an internal count would be implemented is when transactions are involved: if the transaction is uncommitted, do you count the rows inserted by it or not ?

PS: I'm not an expert on DBs, I'm just someone who has MySQL as a simple hobby. So if I just asked something stupid, don't be excessively critical :D .


texts: Speed up simple MySQL query on large database

texts:
I have a database with 1 million records and I frequently run this very simple query:

select id from my_table limit 1000000;

It takes around 5 seconds to execute. And if I add more columns to the select it takes up to 10-15 seconds.

Is there a way to speed up this query? I know I select a large amount of data, but it is necessary in my case to do that. Can I speed it up somehow?

I tried to add a index on the columns I include in the select, but there is no difference on the performance after that.

The database is static and the data inside the database will not change.


texts: Duplicated rows (x) amount of times in a table

texts:
I have a table that looks like the following:

╔══════════╦═══════════╦════════╦
║      SO  ║   SO_Line ║  ...   ║
╠══════════╬═══════════╣════════║
║      ABC ║        1  ║        ║
║      ABC ║        3  ║        ║
║      ABC ║        5  ║        ║
║      DEF ║        1  ║        ║
║      DEF ║        2  ║        ║
╚══════════╩═══════════╝════════╝

What I'd like to do is duplicate a result set (x) amount of times.  For instance, given this result set: SELECT * FROM Table WHERE SO = 'ABC', I'd like to duplicate that result set 10 times.  Basically I want to keep all of the data in all of the other columns the same, but I'll change the primary key to be something else (for instance, ABC-1).  My final table after duplicating that set would be like this:

╔══════════╦═══════════╦════════╦
║      SO  ║   SO_Line ║  ...   ║
╠══════════╬═══════════╣════════║
║      ABC ║        1  ║  ...   ║
║      ABC ║        3  ║  ...   ║
║      ABC ║        5  ║  ...   ║
║    ABC-1 ║        1  ║  ...   ║
║    ABC-1 ║        3  ║  ...   ║
║    ABC-1 ║        5  ║  ...   ║
╚══════════╩═══════════╝════════╝

Normally the way I would do this is by inserting the records from the result set into a temporary table and then assigning a row number to each row.  Then I'd iterate through the temporary table using something like WHILE @i < @RowCount and do an insert into the main table using @i to increment the PK.  I'm basically asking if A) that's the most reasonable way to accomplish this, and if not, B) is there a more efficient way of handling this?


texts: Best tool to optimize SQL Server (not the queries, but the database itself)

texts:
Does anyone know what is the best tool to optimize an entire SQL Server database?

I'm not talking about how to write the queries but more how to manage the database file, indexes, paging and fragmentation.

PAINFUL TRUTH
The eternal fight between tools vs DBAs is still there ...but what I just wanted to know the basics till the product grows and then I can hire a non grouchy DBA :--)


texts: MariaDB InnoDB does not rollback on timeout

texts:
I have the ff. sample queries in this sequence:

START TRANSACTION
UPDATE
INSERT
COMMIT

Query #2 is timing out but I noticed that query #3 is not rolled back.
I am also trying to manually rollback by verifying (querying the table) if the updates went through before I commit. But when I do that, I get an explicit: General error: 2006 MySQL server has gone away error.
My questions:

I have innodb_rollback_on_timeout set to ON, isn't query #3 supposed to be rolled back?

Since I can't even manually roll back, what options do I have?

Note: This is on mariadb-10.4.17


texts: Can't enable wal2json in postgres 12 docker image

texts:
I'm trying to enable wal2json in an image extended from postgres:12 image. But i'm getting an error:

postgres=# create extension wal2json;
ERROR:  could not open extension control file "/usr/share/postgresql/12/extension/wal2json.control": No such file or directory

This might be because wal2json is also missing from pg_available_extensions:

postgres=# select * from pg_available_extensions where name ilike '%%json%%';
 name | default_version | installed_version | comment
------+-----------------+-------------------+---------
(0 rows)

Dockerfile:

FROM postgres:12
RUN apt update && apt install -y postgresql-12-wal2json postgresql-contrib

postgres server logs:

db_1  | 2020-05-10 13:53:04.611 GMT [1] LOG:  database system is ready to accept connections
db_1  | 2020-05-10 13:54:03.587 GMT [83] ERROR:  could not open extension control file "/usr/share/postgresql/12/extension/wal2json.control": No such file or directory
db_1  | 2020-05-10 13:54:03.587 GMT [83] STATEMENT:  create extension wal2json;

the postgresql.conf file has been tweaked to

wal_level = logical
shared_preload_libraries = 'wal2json'

Any help with getting this working will be appreciated.


texts: Cannot restore database because of stuck rollback

texts:
When I try to restore my database with

RESTORE DATABASE MyDatabase
FROM DISK = '\path\to\my\backup.bak'
WITH REPLACE, RECOVERY

It says

  Exclusive access could not be obtained because the database is in use.

After some googling I tried this instead:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyDatabase
FROM DISK = '\path\to\my\backup.bak'
WITH REPLACE, RECOVERY
GO

However, this seemed to take forever and when I eventually cancelled the command it told me that

  Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.

The message repeated multiple times and the progress was always 0%.

I'm not a DBA --sadly my company does not employ any-- and I have no clue where to go from here.


texts:
I have 2TB of data currently spread across many CSV files that looks like:

{
 'rec_date': "2010-01-29",
 'rec_time': "09:15:00",
 'site_no': '46',
 'data_owner': '1',
 'flow_period': '5',
 'vehicle_count': '60',
 'detector': '8'
}

I would like to insert this into mongodb so that I can quickly do queries that typically get the vehicle volume for a site and its surrounding sites for the past n Mondays of the past m years. (I wish to analyse the history of a site to determine if there's abnormal traffic flow for this time of day at this time of year).

Initially I wanted to store a document for each site, that contained its entire history, like so:

{
    site_no: 46,
    history: [{
            $date: 1264756500000,
            readings: [{sensor:1,vehicle_count:60},
                       {sensor:2,vehicle_count:32},
                       ...
                      ]
            },{
            $date: 1264756800000,
            readings: [....]
            }
    ]
}

But this would make each document very large, as the entire history for each site will be more than 16MB. More traffic volume data is coming in every 5 minutes from every site and I would need to be appending to the history array very often.

So my question is, how should I format my data so that I can perform the queries I want but minimising data redundancy? Should I just make a new record for every sensor reading?


texts: SQL Server 2008 Containstable generate negative rank with weighted_term

texts:
I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow

 SELECT ID, CAST(Res_Tbl.RANK AS Decimal) AS Relevancy , Title
  FROM table1 AS INNER JOIN
    CONTAINSTABLE(table1,Title,'ISABOUT("pétoncle" weight (.8), "p&#233;toncle" weight (.8), "P&#201;TONCLE" weight (.8))',LANGUAGE 1036 ) AS Res_Tbl
     ON ID = Res_Tbl.[KEY]

When I execute this query I get : Arithmetic overflow error for type int, value = -83886083125.000076.

If I remove one of the two ';' in the ISABOUT function the query complete successfully.

Note you need to have some results if there is no result the query complete successfully.

Does anybody know how to solve this ?

This question is also on Stackoverflow


texts: Using VBA to open Access in full screen and in foreground

texts:
I have 2 issues.

I am using VBA code to create a macro in PPT that opens an Access report. The macro works but does not open it in full screen. I have tried to add the DoCmd.maximize but not sure where to put it. So far it hasn't worked.
PPT won't let you save unless its a macro-enabled file. When I save it as one the macro works but opens in the background. How can I make it open in the foreground?

Here is what I have:

Dim accessApp
Set accessApp = CreateObject("Access.Application")
accessApp.Visible = True
accessApp.UserControl = True
accessApp.OpenCurrentDataBase ("C:\sts\263X.mdb")

accessApp.DoCmd.RunMacro "SKTL3P09A"


texts: Slave stopped : Could not parse relay log event entry

texts:
My Slave machine stopped somehow, and when I ran START SLAVE to start replication, I got following error,

    SLAVE 1 !!! > show slave status \G
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: < My Master's IP >
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: master-bin.000777
            Read_Master_Log_Pos: 358597143
                 Relay_Log_File: slave-relay.000634
                  Relay_Log_Pos: 95144877
          Relay_Master_Log_File: master-bin.000777
               Slave_IO_Running: Yes
              Slave_SQL_Running: No
                Replicate_Do_DB:
            Replicate_Ignore_DB:
             Replicate_Do_Table:
         Replicate_Ignore_Table:
        Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table: mysql.%,information_schema.%, tmp1.%,performance_schema.%,mysql.%,information_schema.%, tmp1.%,performance_schema.%
                     Last_Errno: 0
                     Last_Error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                   Skip_Counter: 0
            Exec_Master_Log_Pos: 95264000
                Relay_Log_Space: 358481990
                Until_Condition: None
                 Until_Log_File:
                  Until_Log_Pos: 0
             Master_SSL_Allowed: No
             Master_SSL_CA_File:
             Master_SSL_CA_Path:
                Master_SSL_Cert:
              Master_SSL_Cipher:
                 Master_SSL_Key:
          Seconds_Behind_Master: NULL
    1 row in set (0.00 sec)

I followed this post,

https://stackoverflow.com/questions/12097696/mysql-replication-fails-with-error-could-not-parse-relay-log-event-entry

But my doubts are not clear and I don't want my slave to be crashed, So, could anyone please guide me to solve it ?

In My case :
should I try

CHANGE MASTER TO master_log_file='master-bin.000777', master_log_pos=358597143;  ?

Note :
My Read_Master_Log_Pos and Exec_Master_Log_Pos are not same


texts: Why am I seeing OPTION (MAXDOP 1) in my Actual query plan even though I've not set it in the stored proc?

texts:
SQL Server is set with MAXDOP 0 by default. In the stored procedure I'm testing I keep seeing WITH (MAXDOP 1) in the Actual Execution Plans even though I've not specified any hints other than SORT_IN_TEMPDB = 1. The main Stored Procedure calls another procedure which grabs the table, generates scripts on each disabled index, then executes that series of scripts (with the afformentioned query hint). The funny thing is I'm seeing a high CPU cost for the operation so I imagine there's benefits to parallelism.

The table has a few million rows and is very wide.

Unfortunately, the database is file and TempDB file are on the same datastore and the TempDB is just 1 file as opposed to several. This configuration is out of my control but perhaps it's related to the reason for no parallelism.

The server is on a VM with 4 cores and 64 GB RAM.

Any thoughts on why SQL Server insists on dropping parallelism on such a large operation on a large table?

EDIT: Added script for the Inner stored procedure that gets called during Upserts:

CREATE PROCEDURE etl_Index_EnableDisable
@DatabaseName NVARCHAR(50)
,@TableName NVARCHAR(50)
,@EnableDisable NVARCHAR(50)

AS
BEGIN

 IF @DatabaseName IS NULL
   RAISERROR('Null values not allowed for @DatabaseName', 16, 1)
 IF @TableName IS NULL
   RAISERROR('Null values not allowed for @TableName', 16, 1)
 IF @EnableDisable IS NULL
   RAISERROR('Null values not allowed for @EnableDisable', 16, 1)

 DECLARE @SQL NVARCHAR(4000)

 -- Interpret 1 as enable and 0 as disable. Also set to Upper case
 SET @EnableDisable = CASE UPPER(@EnableDisable) WHEN '1' THEN 'ENABLE'
                                                 WHEN 'REBUILD' THEN 'ENABLE'
                                                 WHEN '0' THEN 'DISABLE'
                                                 ELSE UPPER(@EnableDisable)
                      END

 SET @SQL = ' DECLARE @SQLIn NVARCHAR(4000)
              SELECT @SQLIn = COALESCE(@SQLIn,'' '')
              +  SQL1 FROM ( (SELECT '' ALTER INDEX '' + si.name + '' ON ' + @DatabaseName + ' .dbo.'' + st.name + ' + CASE @EnableDisable WHEN 'DISABLE' THEN ''' DISABLE '''
                                                                                WHEN 'ENABLE' THEN ''' REBUILD WITH (SORT_IN_TEMPDB = ON) ''' END + '
                 AS SQL1
                  FROM ' + @DatabaseName + '.Sys.indexes si
                       JOIN  ' + @DatabaseName + '.Sys.tables st ON si.object_id = st.object_id
                  WHERE st.name = ''' + @TableName + '''
                        AND (si.is_unique = 0
                             AND si.is_unique = 0
                             AND si.is_primary_key = 0
                            AND si.[type] = 2
                            )
                       AND si.is_disabled = ' + CASE @EnableDisable WHEN 'DISABLE' THEN '0'
                                                                    WHEN 'ENABLE' THEN '1'
                                                END + '
                       AND si.Name IS NOT NULL
               ) ) tb

             EXEC sp_sqlexec @SQLIn '

 EXEC sp_sqlexec @SQL

END`


texts: Extract N rows from 2 tables (t1, t2) - if t1 doesn't match N rows, then (and only then) extract data from t2

texts:
I would like to extract 100 names from table t1 and t2 where id = 'value', in such a way so that if table t1 hasn't 100 names, I will then go and take the remaining names from table t2.

The first part of the query would be (for table t1)

SELECT distinct name, id
FROM t1 WHERE id = 'value'
ORDER BY id
LIMIT 100;


texts: Intermittent RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Stats

texts:
I'm trying to troubleshoot some intermittent CPU spikes that we're witnessing on one of our production SQL Servers.  We're running SQL Server 2008 R2 Standard Edition with 28 GB RAM and 4 CPU cores.  When this happens, we are noticing a large number of RESOURCE_SEMAPHORE_QUERY_COMPILER waits, which lasts for about a minute or two and then stops, which then the CPU usage returns to normal.

After researching this, I understand that this is normally caused by compiling lots of non-reusable execution plans, which we are currently working on changes to our application to address.

Can this behavior also be triggered by plan cache evictions due to memory pressure? If so, how would I check for this?  I'm trying to see if there are any short-term remedies we can do, like upgrading the server RAM, until we deploy our application fixes.  The only other short-term option I can think of is to move some of our busiest databases to different server.


texts: mysql query with 1 derived table and 1 inner join takes forever to load

texts:
I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows of data. This works perfectly fine if I have 1-50 row of data

SELECT negotiations.id
FROM
(SELECT n.id,n.business_rank,db.status,
case status
        when '11' then IF(DATEDIFF(IFNULL(apply_date, curdate()), curdate()) < 1, true, false)
        when '12' then IF(DATEDIFF(IFNULL(db.contract_date, curdate()), curdate()) < 1, true, false)
        when '13' then IF(DATEDIFF(IFNULL(db.settlement_date, curdate()), curdate()) < 1, true, false)
        when '20' then IF(DATEDIFF(IFNULL(db.apply_cancel_date, curdate()),  curdate()) < 1, true, false)
        when '21' then IF(DATEDIFF(IFNULL(db.contract_cancel_date, curdate()),  curdate()) < 1, true, false)
end as to_notify
FROM negotiations AS n
INNER JOIN deal_buildings AS db ON db.id = (
    SELECT id
    FROM deal_buildings AS db2
    WHERE db2.deal_id = n.id
    AND n.main_member_id = 79
    AND db.status in (11,12,13,20,21)
    ORDER BY status DESC
    LIMIT 1
) ) as negotiations
WHERE ((
(business_rank = '17' and status = 11) or
(business_rank = '19' and status = 12) or
(business_rank = '22' and status = 13) or
(business_rank = '18' and status = 20) or
(business_rank = '20' and status = 21)) is false )
and to_notify


texts: Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

texts:
I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently:

SET ANSI_NULLS { ON | OFF }
SET QUOTED_IDENTIFIER { ON | OFF }
SET ANSI_PADDING { ON | OFF }

While I was researching these Settings I stumbled across the following Note for ANSI_NULLS on Microsofts technet (Link):

  In a future version of Microsoft SQL Server ANSI_PADDING will always
  be ON and any applications that explicitly set the option to OFF will
  produce an error. Avoid using this feature in new development work,
  and plan to modify applications that currently use this feature.

Because of this I would like to enforce a "global" Setting. I have been searching for the better part of a day but as I understand it you can always change these settings. To be completely sure I would like to ask here:

Is it possible to either enforce a Setting like ANSI_NULLS ON or to disable the possibility of changing these Settings on "any" level.
I would like to use this to test if the Database would "survive" if the ability to set a specific option to OFF is disabled.

I hope I was able to describe my problem. This is not my first language.
Thanks in advance.

PS: On StackOverflow I was advised to Ask this Question here too.
Original Question: Here


texts:
I have the following table

CREATE TABLE TaskJournal (
  TaskJournalId INT NOT NULL AUTO_INCREMENT,
  TaskId INT NOT NULL,
  TaskStatus TINYINT NOT NULL,
  TaskStart INT NOT NULL,
  TaskEnd INT NULL,
  TaskInfo TEXT,
  PRIMARY KEY (TaskJournalId)
) ENGINE = InnoDB;

INSERT INTO TaskJournal (TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo)
VALUES (2, 1, 1533660028, 1533660500, "No issues present"),
(3, 3, 1533660505, 1533660506, "Exception found: Line 95"),
(2, 1, 1533660015, 1533660018, "No issues present"),
(2, 3, 1533660509, 1533660512, "Exception found: Line 95"),
(2, 1, 1533660515, 1533660530, "No issues present");

The problem is this, I want to gather a list of errors but only if at a future time there is no resolution.

Ideally I'd get something like this back

| TaskJournalId | TaskId | TaskStatus | TaskStart  | TaskEnd    | TaskInfo          |
| 3             | 2      | 1          | 1533660015 | 1533660018 | No issues present |

Only because in the future there isn't some TaskId that has the exception present.

So I thought I could do this via group of sub queries something like as follows:

SELECT *
FROM TaskJournal
WHERE TaskId = 2
GROUP BY TaskId, TaskStatus
HAVING TaskStatus=3
ORDER BY TaskStart DESC

But the problem with this is that it returns both rows 4 and 2 (chronological descending order). But at some later point there appears to have been a resolution to TaskId=2 but not for TaskId=3.

Any ideas would be helpful.


texts: SSISDB on AlwaysOn Group running package on Secondary Replica

texts:
I have sql server 2016 Enterprise Edition. I have created AlwaysOn Group. and added SSISDB. I do have couple SSIS packages running on primary replica with no errors. but when failing over to second replica, the jobs fails.

Asking to create master Key or certificate.

so my question is this: is there a way to automate this and whenever FaillOver happen the jobs should be running as they are on Primary replica?

Thank you all.

Rashid


texts:
I have a PostgreSQL 10 script for which I would like to declare variables once at the beginning and then use them throughout the script (with possible transactions).

I know I can do:

DO $$
DECLARE test float := 0.1 ;
BEGIN
    < do whatever here>
END $$

but that makes me declare the variables each time I have a BEGIN / END block.


texts: Migrate from innodb_file_per_table to off in MySQL

texts:
We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large (~5GB), and queries per second is low, just lots of small databases and users. Each account created for our application get's their own database and user (this architecture cannot change unfortunately).

We are seeing very poor performance, especially when trying to stop mysqld (often times it just times-out). Starting mysqld and spawning (ready for connections) takes over 3+ minutes. Running mysqldump brings the entire server to its knees. Load, CPU usage, and iowait, all skyrocket during mysqldump, and cause other queries to timeout and fail.

It was recommend that we transition off of using innodb_file_per_table because of the excessive number of open file descriptors, shear number of directories and files, and i/o operations.

Changing innodb_file_per_table to off does this, but how can we convert all existing databases and tables to utilize this?

Below is our current /etc/my.conf just for reference, in-case there are further optimizations we can make to optimize for lots of small databases.

[mysqld]
datadir=/mysql/data
socket=/mysql/mysqld.sock
symbolic-links=0
default-storage-engine=InnoDB
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql_slow.log
expire_logs_days=30
max_connections=50

bind-address=192.241.X.X
port=3306
max_allowed_packet=4M
net_retry_count=5
max_connect_errors=100
wait_timeout=14400
connect_timeout=10

open_files_limit=65535
innodb_open_files=65535
key_buffer_size=256M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=4M
group_concat_max_len=16k
max_sort_length=16k
max_length_for_sort_data=16k
query_cache_type=1
query_cache_limit=1M
query_cache_size=64M
innodb_thread_concurrency=8
thread_concurrency=8
thread_cache_size=128
thread_stack=1M
read_buffer_size=1M
join_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
table_open_cache=32768
table_definition_cache=65535
tmp_table_size=33554432
max_heap_table_size=33554432
innodb_log_file_size=1024M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table
log-warnings
innodb_flush_neighbors=0

user=mysql
old_passwords=0

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


texts:
Is there any way to disable TTL deletion thread in mongoDB?

I know that you can modify the ttl index expireAfterSeconds value to a high number in order to "technically" stop ttl from deleting, but i am interesting in disabling the ttl thread completely for some time and enable it later.

Thanks in advance


texts:
I have MongoDB setup like this:

 Primary   | Server1
-----------+---------
 Secondary | Server2
-----------+---------
 Arbiter   | Server3
-----------+---------
 Backup    | Server4

I have a backup server for daily backup using mongodump. Now I have to test my backup dump using mongorestore.

The main question is the following: What is the best way to restore the backup?

Q1: I have to follow these steps?

Stop arbiter & secondary
Drop database in primary
Restore backup to primary
Drop database on secondary
Restart all the server

Q2: Restore the backup on primary without stopping any of the servers?

Q3. Make secondary as primary and restore the backup to secondary ( previous primary), and make it primary.

Q4: Is there is any way to restore both the server?

Please suggest the best approach to restore MongoDB.


texts: Returning Right of a string with no specific known length

texts:
I want to return the data from a column from the right. This would mean using the RIGHT() function, however this means entering a length of what to return. For example, I have the following data:

NAME:

A. Brown
S. Smith
C. Taylor
G. L. Potter
L. M. Lyle

I want to return just the surname, so ideally from the right up to the first space which is a different length with each name. So I want to return Brown, Smith, Taylor, Potter, Lyle.  Im not sure what the best way to do this is... Would substring be better? How would I get that to return from the right too?

Thanks


texts: Repairing buggy query server-side – without changes in the app

texts:
More external apps are accessing our ERP SW database. As we were adding some columns into our structure, some queries from the external app become buggy because they are not specifying the targeted table in the query and errors out with Ambiguous column name… message.
The app vendor is inaccesible. We cannot repair the query from the app point nor change our structure (it’s for more customers).
Can we do something at the database server level?


texts:
I'm trying to mount my database but sqlplus returned the following error :

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             679477352 bytes
Database Buffers          385875968 bytes
Redo Buffers                5455872 bytes
ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version
11.2.0.0.0
ORA-00202: control file: '/oracle/product/12.1.0/dbhome_1/control01.ctl'

I know that the official documentation said :

Action: Either restart with a compatible software release or use CREATE CONTROLFILE to create a new control file that is compatible with this release.

So I would like to create my new control file with the following code :

create controlfile reuse database "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 256
MAXINSTANCES 1
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 '/oracle/oradata/local/redo01.log' size 20M,
GROUP 2 '/oracle/oradata/local/redo02.log' size 20M
DATAFILE
'/oracle/oradata/local/DEVELOPMENT.dbf',
'/oracle/oradata/local/DEVIL_INDEX.dbf',
'/oracle/oradata/local/example01.dbf',
'/oracle/oradata/local/syseaux01.dbf',
'/oracle/oradata/local/system01.dbf',
'/oracle/oradata/local/temp01.dbf',
'/oracle/oradata/local/undotbs01.dbf',
'/oracle/oradata/local/users01.dbf'
CHARACTER SET AL32UTF8;

and when I execute that on sqlplus it returned me the following error :

SQL> @/oracle/scripts/create_controlfiles.sql
create controlfile reuse database "ORCL" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01210: data file header is media corrupt
ORA-01110: data file : '/oracle/oradata/local/DEVELOPMENT.dbf'
ORA-01130: database file version 12.1.0.2.0 incompatible with ORACLE version
11.2.0.0.0
ORA-01110: data file 2: '/oracle/oradata/local/DEVELOPMENT.dbf'

So

my database files seems to be configured for oracle 12.1.0 (which is my current oracle version 12c) but not compatible with older version.
my control file seems to be configured for oracle 11.2.0 so not compatible with my current version.

Any suggestion ?


texts: Why SMO's Transfer tries to login into linked server when scripting?

texts:
Consider this code sample:

            var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword";
            var serverConnection = new ServerConnection
            {
                ConnectionString = sourceDatabaseConnectionString
            };

            var server = new Server(serverConnection);
            var builder = new SqlConnectionStringBuilder(sourceDatabaseConnectionString);
            var database = server.Databases[builder.InitialCatalog];
            var transfer = new Transfer(database);

            // setting transfer options
            transfer.CopyAllObjects = true;
            transfer.CopyAllSynonyms = true;
            transfer.CopyData = false;

            transfer.Options.WithDependencies = true;
            transfer.Options.DriAll = true;
            transfer.Options.Triggers = true;
            transfer.Options.Indexes = true;
            transfer.Options.SchemaQualifyForeignKeysReferences = true;
            transfer.Options.ExtendedProperties = true;
            transfer.Options.IncludeDatabaseRoleMemberships = true;
            transfer.Options.Permissions = true;
            transfer.PreserveDbo = true;

            // scripting database
            var script = transfer.ScriptTransfer();

It tries to get a blank from existing database.

Some objects in this database depend from objects, located on linked servers. When this code tries to make a script for such objects, ScriptTransfer fails with "Login failed for user 'MyLogin'".

Log analysis shows, that error happens when MyLogin tries to log into liked server. But what is the need for this? After all, I just want to get a bunch of CREATE ... scripts for MyDatabase, I don't want to read any data from linked server.

My SQL Server and SMO version is 2012 SP1.


texts: How to sort categorical values by an arbitrary order?

texts:
I have a table called ANIMALS which looks like this

| name      | category |
------------------------
| fluffer   | CAT      |
| tooter    | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer    | DOG      |

If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG

How can I achieve this result?


texts: sp_BlitzIndex gives self-contradictory recommendations

texts:
I've used the Brent's sp_BlitzIndex and received the interesting results. For the first run it advises me to create an index. I've done it and executed it again. This time it has told that the newly created index is too Aggressive.
With this logic DBAs will feel like racing horses going in circles.
Brent, if I am a crazy blind horse, show me the way, please.


texts: Find consecutive entries in a table and group into another table

texts:
So ideally I'd like a table output such as:

consecutive_days | max_date

for a given user

where consecutive days is the number of days a person logged something and max_date was the last date in that series.

I have a table which is

id | user_id | value | created_at | updated_at

and I can't figure out how to do this at all.
I know I need to select all the dates for a given user and then order by that date. I'm thinking I'd then want to group them by the consecutive series but that's where my limited knowledge of mysql flies straight out the window.

Here is an actual screen shot of the table. ignore plan_id.

The created_at column is when an entry was added so the consecutive days would be based on the date n that column.


texts: Package state not re-intialized on raise_application_error

texts:
When a package has state and the header is changed, the first call gets an error stack something like this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "LRIFFEL.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package "LRIFFEL.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "LRIFFEL.PKG1"
ORA-06512: at "LRIFFEL.PKG2", line 7
ORA-06512: at line 1

A subsequent call from the same session re-initializes the package state and therefore runs successfully.  The interesting thing is that this behavior seems to change when the caller catches the exception and does a raise_application_error.  As expected the raise_application_error is included in the stack, but contrary to expectations, the ORA-04068 is not.  Here is an example error stack:

ERROR at line 1:
ORA-20001: Failed
ORA-06512: at "LRIFFEL.PKG2", line 7
ORA-04061: existing state of package "LRIFFEL.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package "LRIFFEL.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "LRIFFEL.PKG1"
ORA-06512: at line 1

This doesn't just seem to be a display issue as repeated calls to the procedure continue to get the same error.  Only when converting the raise_application_error back to a simple raise (or eliminating the exception block entirely) does the next execution include the ORA-04068 and the following execution succeed.

Can anyone confirm and/or explain this behavior?  Here is a recipe for reproducing the behavior:

--Session 1 - Create Objects
create or replace package pkg1 as
   vInteger Integer := 7;
   procedure procA;
end;
/

create or replace
package body pkg1 as
   procedure procA is
   begin
      DBMS_Output.Put_Line('ProcA');
   end;
end;
/

create or replace
package pkg2 as
   procedure procB;
end;
/

create or replace
package body pkg2 as
   procedure procB is
   begin
      pkg1.procA;
   exception
      when others then
         raise;
   end;
end;
/

--Session 2 - Test execution.
execute pkg2.procB;

--Session 1 - Change package header and thus invalidate package state.
create or replace package pkg1 as
   vInteger Integer := 8;
   procedure procA;
end;
/

--Session 2 - Observe failure due to changed package state.
execute pkg2.procB;

--Session 2 - Observe success due to re-initialized package state.
execute pkg2.procB;

--Session 1 - Change Raise to Raise_Application_Error.
create or replace
package body pkg2 as
   procedure procB is
   begin
      pkg1.procA;
   exception
      when others then
         raise_application_error(-20001,'Failed',True);
   end;
end;
/

--Session 1 - Change package header and thus invalidate package state.
create or replace package pkg1 as
   vInteger Integer := 9;
   procedure procA;
end;
/

--Session 2 - Observe failure due to changed package state.
execute pkg2.procB;

--Session 2 - Observe unexpected continued failures.
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;

--Session 1 - Change back to raise.
create or replace
package body pkg2 as
   procedure procB is
   begin
      pkg1.procA;
   exception
      when others then
         raise;
   end;
end;
/

--Session 2 - Observe failure on the first execution.
execute pkg2.procB;

--Session 2 - Observe success.
execute pkg2.procB;

Bug 229349 looks similar except that a trigger is involved.


texts: Setting Up Multi-Server Administration---SQL Server Account

texts:
I am trying to set up Multi Server Administration so that I can easily update multiple instances of the same database across our network with ease. Where I am getting lost is how to set up the SQL SERVER AGENT service on the target machines.
Right now the SQL SERVER AGENT services on the machines are set to log on as NTSERVICE\SQL SERVER AGENT.
My SQL Server Agent process is set to log in as LocalSystem
My overall goal here is to figure out why I can't set any target servers from my machine, as I always run into the following error:
TITLE: Microsoft.SqlServer.Smo
------------------------------

MSX enlist failed for JobServer 'MYPC'.

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'MYPC'.) (Microsoft SQL Server, Error: 22026)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

When looking at the log on my machine I see the following error when trying to enlist the target machine:
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.


texts: Shortest method to upload database to server

texts:
I have a SQL Server script file which ranges size of 3gb and I am in need to upload the script file to an external server.

i generated script for my 6gb database by following these steps

right-clicked the database-->tasks--->genrate scripts-->script entire database and objects and at scripting options i clicked advance -->type of data to script-->set it  as  schema and data and finished the data..

What are the shortest methods to upload faster into the server?


texts: Receiving error when running DBCC CheckDB - multiple IAM pages

texts:
Running DBCC CHECKDB (dbname)  WITH NO_INFOMSGS, ALL_ERRORMSGS results in this error:

  Table error:
   Multiple IAM pages for object ID 813245952, index ID 1,
  partition ID 72057594073972736, alloc unit ID 72057594083213312 (type
  In-row data) contain allocations for the same interval. IAM pages
  (1:4611577) and (1:2040821). CHECKDB found 1 allocation errors and 0
  consistency errors in table 'tablename' (object ID 813245952). CHECKDB
  found 1 allocation errors and 0 consistency errors in database
  'dbname'. repair_allow_data_loss is the minimum repair level for the
  errors found by DBCC CHECKDB (dbname).

Has anyone experienced this issue before and have a solution for repair?

Note:- I have not run the statement with repair_allow_data_loss yet.


texts: How to identify hung queries, their importance, and then take action using command line?

texts:
From a production support perspective, how can we find hung queries and then find how important they are and then how to deal with them?

I tried only processlist which gives currently running queries but how to decide their importance and then act accordingly?

We don't have any MySQL performance tool with UI in existence in our environment, so how can I do this using command line?


texts: MariaDB Inserts are getting slower and slower (7x tables, ~ 2.8M and 200MB)

texts:
I have auto increment on each of a table.
One unique ID that is consistent of 10 numbers, and each table have ~ 6 big int columns (values are small from 1-60k), and from 0 to 4 var chars (~ up to 500 characters, on average from 5 to 50 characters).

Quickly tested and with fresh database (dump and clean all tables) I started with 1000/s, then within seconds dropped to 400 => 200/s, after ~ just 116MB & ~ 500k records in 5 main tables it dropped to 159/s and keeps dropping. (memory usage 1.6%, disk writes ~ 1.2MB/s, reads => 0B/s).

Removed all other keys other than auto increment, so not sure why it is so slowing down.

This is nothing as I've worked with DB that was holding trillions of columns and auto increment and huge varchars. (however paid solution :().

I already tweaked the config so many times but still getting to the point where server is using ~ 950% & .net core 25% (of all cores).

Machine has i9 9900k 8c/16t, 64GB RAM, 2x NVME 2TB

I can't even run @ 5 minute API test as it won't be able to process all data from a queue :( (API can accept ~ 20k/s).

Buffers, read io, inno_db other tweaks for a commit etc. were applied, nothing seems to be working.

Looks like it cannot for some reason handle just so little data and I cannot figure out why (I never had any real experience with free databases, so I only assume that it should be able to insert 300k records within 60 seconds and sustain this for ~ 10TB).


texts:
Due to low reputation point i am not able to include the picture.

I am trying to make u guys understand what i am asking for:

I had created a report. And that Report contains these fields and values:

  Name, YTDPlan, Actual, YearPlan
  Rev ,  4418  ,  4410 ,  24069

I have included the first row of this report, for that i have to create a line graph. Can anyone please tell me what fields should be taken on which axis.?


texts:
I'm trying to relocate system databases files.
I managed to do that with the tempdb database, but I'm stuck with msdb.

When I run:

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN DB_ID('msdb')

it shows that the logical names are  'MSDBData' and 'MSDBLog'. Then when I run below mention TSQL query

alter database tempdb modify file (name = MSDBData, filename = 'C:\Data\MSDBData.mdf')

I am getting the following error: MODIFY FILE failed. File 'MSDBData' does not exist.

MS Sql 2008 R2 server version: 10.50.1600


texts:
I got this code (python - psycopg2) which takes about 2 minutes to complete. I need each row (60 rows) in each column (288 columns) to get the value from its following column (and corresponding row). Is there any SQL trick to make it happen faster?

for i in range(1, 288):
    oy = i + 1
    for izi in range(1, 61):
        cur.execute("UPDATE d2 SET stav_" + str(i) + " = (SELECT stav_" + str(oy) + " FROM d2 WHERE usek = " + str(izi) + ") WHERE usek = " + str(izi) + ";")
        conn.commit()


texts: How MongoDB indexes differs from MySQL indexes?

texts: I am a newbie to MongoDB. I know how Indexes works and what it's importance in MySQL. Are MongoDB indexes are the same as MySQL ?


texts: Row Level Trigger for insert/update to a table

texts:
Given a table ITEM as below :
CREATE TABLE ITEM (
ItemNo        int not null,
category      varchar(20) not null,
Quantity      int not null,
Price       decimal(10,2) not null,
Total       decimal(10,2) not null,
CONSTRAINT pk_item primary key (ItemNo)
);

I need to write a row-level trigger called update_item that will fire whenever Quantity and/or Price is inserted/updated. When this happens, the Total column will be set by the trigger as the product of Quantity and Price.
Any help will be appreciated.


texts: Beginner Question: Trouble turning a field with comma separated values into a multi-valued field

texts:
It's my first time using Access, and I have my first of what I assume will be many questions. Yay!

So I imported some data from Excel to a table, including a column with a comma separated tagging system (let's say x, y, z). After some poking around, I went to design view and did the following in the Lookup tab for this "tagging" field:

What happened: entries with just an x, y, or z tag worked. When I click the drop-down arrow for a given entry, I see x, y, and z as choices with the correct one selected. For entries with multiple tags though, it shows up as a new box to be checked. So if I have x, z in the cell, there's a x, y, z, and x, z selection in the drop-down box.

So my question is, how can I make the boxes with multiple tags work correctly?


texts: Cassandra Table data modeling for a event system monitoring data

texts:
I have been working in a cassandra database to keep the real time data of an event/monitoring system that has quite a large number of events per hour(100k+) and I have hit a wall in a specific need: I need a way to get the data by event state(open,close) for all the events.
Each event has the following fields: id(this is generated by the event system), state, start_date,end_date,node, description.
My first aproach was to have a table with state as partition key and id as clustering:
CREATE TABLE alarms_by_state (
id,state,start_date,end_date,node,
PRIMARY KEY((state),id))

This that didn't work, as it keeps 2 states for each id.
What I need is that for example we receive event with id = 1 and state=open and the application saves it into the database, later another event with id=1 and state=closed and replaces the previous record.
I know this could all be done in the application side, but I'm trying to keep that control to a minimum due to the number of events I receive per hour.
Thank you for your time


texts: Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?

texts:
Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued function returns one row. This causes the selection of a poor execution plan for the calling statement if it actually returns many rows.

Does adding WITH SCHEMABINDING to the RETURNS clause of the CREATE FUNCTION result in a more correct cardinality estimate for the return value of the function?

If we assume that we are passing a UserId to this function and getting back a table of RecordId values that the user is allowed to access, and that some users are only allowed to see a few records and that some are allowed to see many or even all records, would either the function or the calling statements (or the procedures that include them) benefit from using FORCE RECOMPILE? Does the use of WITH SCHEMABINDING in the function change this answer?

I realize that I could figure this out by experimentation, but I am hoping that someone has already figured out the answer. A pointer to someplace where this is well documented would be helpful.


texts:
I need a tool/command which will check a table then say something like that:

columnA use tinyint instead of int
columnB use enum instead of varchar

I've seen something like this while ago so I do know it exists, but search gives nothing or I'm asking it in a wrong way.


texts: Auditing which tables get joined most often

texts:
I'm trying to establish some metrics associated with which tables in our database get joined together the most often. How can I go about accomplishing this?

edit: I should explain my reasoning; we are moving to a microservices-based architecture, and I would like to have join data in order to best inform the domain-driven-design.

Seeing which tables need to be joined most often would allow me to design certain data layer microservices in a way that they will always have access to easy joins of the most frequently-joined tables.

So if Table1 and Table2 are ALWAYS being joined, I can assess this and have them controlled by the same service. If not, I'll have different services control them.


texts: Replace 2 spaces by 1 space in all values of a column

texts:
I need to replace 2 spaces by only 1 space for all entries of 1 column in an Oracle database. I found this and tried it:

SELECT REGEXP_REPLACE('MODEL', '[[:space:]]{2,}', ' ') new_string
FROM ops.TBL_PRODUCT_DEIN_DESIGN;

MODEL is the column name. But it has not replaced the double spaces.

How to use that solution or what could be an alternative?


texts: Row estimate more accurate for primary key column than for others?

texts:
Let's create two test tables in a PostgreSQL 13 database:
CREATE TABLE foo (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  value int NOT NULL
);
CREATE TABLE bar (
  id bigint PRIMARY KEY,
  category_id bigint NOT NULL,
  foo_id bigint REFERENCES foo (id),
  value int
);
CREATE INDEX bar_category_id_ix ON bar (category_id);

and disable autovacuum for these tables:
ALTER TABLE foo SET (autovacuum_enabled = false);
ALTER TABLE bar SET (autovacuum_enabled = false);

Insert 500000 (half of a million) records into foo, transfer them to bar and analyze the tables:
INSERT INTO foo (value) SELECT * FROM generate_series(1, 500000);
ANALYZE foo;
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 1, id, value FROM foo WHERE value <= 500000;
ANALYZE bar;

Optionally ensure that only ANALYZE (no autovacuum) was performed on these tables:
SELECT relname, last_autovacuum, last_vacuum, last_autoanalyze, last_analyze FROM pg_stat_user_tables WHERE relname IN ('foo', 'bar');

Insert another chunk of 500000 records (but don't run ANALYZE):
INSERT INTO foo (value) SELECT * FROM generate_series(500001, 1000000);
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 2, id, value FROM foo WHERE value > 500000;

Since we did not run ANALYZE table statistics is outdated, its related to the stage when foo and bar contained half of million records. Now let's check the query plans:
EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 2;
----
Nested Loop  (cost=0.85..12.89 rows=1 width=40)
  ->  Index Scan using bar_category_id_ix on bar  (cost=0.42..4.44 rows=1 width=28)
        Index Cond: (category_id = 2)
  ->  Index Scan using foo_pkey on foo  (cost=0.42..8.44 rows=1 width=12)
        Index Cond: (id = bar.foo_id)

and
EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id;
---
Hash Join  (cost=32789.00..71320.29 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..17351.64 rows=999864 width=28)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

I understand that 1st query plan has wrongly estimated only 1 row (rows=1) for condition category_id = 2 because the statistic is outdated (the ANALYZE was performed before inserting records with category_id = 2). (1) But then, how did the 2nd query plan arrive at a good estimation (rows=999864) for condition bar.foo_id = foo.id?
Also if we run:
EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 1;
----
Hash Join  (cost=32789.00..73819.95 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..19851.30 rows=999864 width=28)
        Filter: (category_id = 1)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

(2) Why does the planner estimate 999864 rows for condition category_id = 1? The statistics should show about 500000 of rows satisfying it?
NOTE: I came to these questions because empirically I observed that conditions containing only primary key columns will produce a better query plan even if the table was not analyzed, but I did not find anything about this behavior in PostgreSQL official documentation.


texts: MariaDB Galera Cluster - first node won't reconnect after restart... Ever

texts:
Firewalls disabled, replication worked fine, IP's accessible..

All config copied from here:
https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mysql-5-6-on-ubuntu-16-04

As soon I try to restart the first node it won't reconnect.

I cannot restart the whole cluster as it supposed to be serving content 24/7 with at least 3 nodes available at all times...

Any idea how I can setup Galera so it would survive a restart of ANY node? (restart of a cluster is a pretty much fatal crash as leads to losses going in xx k of $ and the main reason behind selecting Galera was the low cost vs Microsoft (which can survive without a problem any number of resets while setup correctly)...

As I am trying to push Galera instead of Windows, I have to prove that indeed it is a commercially viable solution (which means that it have to survive main node restart, especially that we have dozens of "main" servers so "first" node is strictly theoretical).

I was testing in ~ 2 weeks ago and unfortunately, it ended up with a restart and data loss (due to having test servers offline)


texts: SQL 2014 Always On Availability Groups - Read Replica

texts:
I'm not familiar with Always On Availability Groups, but our company is using it to provide a read-only replica for our main production database so that certain users can report against it and generate ad-hoc queries.

Since this is read-only, I know that no modifications can be made to this database. However, I was wondering if it's ever necessary to rebuild indexes or regenerate statistics on a read replica like this? Or, does this happen automatically when the the source database has indexes rebuilt or statistics regenerated?


texts:
Let's say I have two tables in a banking application and table names are all_transactions and helpline_requests. The 'all_transactions' table keeps track of all kinds of transactions including deposits, transfers, etc. The 'help_requests' table keeps track of the helpline calls a user has issued. For brevity the table schemas and sample data is given:

all_transactions
---------------
id, class, date, user_id
1, 'transfer', '2019-04-15 11:02:45', 789
31, 'transfer', '2019-04-14 05:02:45', 789

helpline_requests
-----------------
id, user_id, date
2, 789, '2019-04-14 11:02:45'

I need to see if a given customer has deposited and called helpline within a 24 hours timespan, more than once in last one week.

For example, user_id:789 has transferred money on June 19th morning and called helpline on the same day June 19th evening. This is one occurrence because it happened within a 24-hours timespan. If we detect any such occurrence once more in last 7-days, starting from his latest helpline request date, I need to return something indicating it, may be print something for now.

I have achieved this using a CURSOR approach and my code is below:

DECLARE @occurencesFound TINYINT
DECLARE @withReqId INT, @requestDateForLoop DATETIME
SELECT @occurencesFound = 0

-- A cursor for last 7 days data
DECLARE helpline_Cursor CURSOR FOR SELECT id, [date] FROM helpline_requests WHERE [user_id] = 789 and [date] >= DATEADD(day, -7, getdate()) order by id desc;
OPEN helpline_Cursor;
FETCH NEXT FROM helpline_Cursor INTO @withReqId, @requestDateForLoop;
WHILE @@FETCH_STATUS = 0 AND @occurencesFound < 2
    BEGIN
        SELECT @withReqId, @requestDateForLoop
        IF DATEDIFF(HOUR,
                        (select
                         top 1 t.[date]
                        from
                            [all_transactions] t
                        where
                            t.user_id = 789
                            and t.class = 'transfer'
                        order by
                            t.[date] desc),
                        @requestDateForLoop) <= 24
        BEGIN
            SELECT @occurencesFound = @occurencesFound + 1
            SELECT 'ok'
        END
        FETCH NEXT FROM withdraw_Cursor INTO @withReqId, @requestDateForLoop;
    END;
CLOSE helpline_Cursor;
DEALLOCATE helpline_Cursor;

IF @occurencesFound = 2
BEGIN
    SELECT 'This customer has transferred money and called helpline within 48 hours more than 1x a week'
END

Can I avoid cursor in this case?


texts: Does mysqld wait for init-script code to complete before opening the instance?

texts:
I'm trying to automate the execution of an application code at MySQL startup.
The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete before making the instance and the databases available.

I tried to set up a quick test case that doesn't seem to work as I expect:

create a simple test table and a procedure that just inserts a record

create table init_test_tab (x char);
delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  do sleep(300);
  insert into init_test_tab VALUES(p);
END
$$

call the procedure from an init-file:

$ cat /var/lib/mysql/mysql-init.sql
call xxxx.init_test('y');
$ grep init /etc/mysql/my.cnf
init-file = /var/lib/mysql/mysql-init.sql

Restart the server to verify the timings and check the content of the test table:

session 1 - the table is empty:

[Wed Nov 28 15:54:26 2012]> select * from init_test_tab;
Empty set (0.00 sec)

session 2 - restart mysqld:

# time service mysql restart
mysql stop/waiting
mysql start/running, process 3031

real    0m10.116s
user    0m0.024s
sys     0m0.068s

Note that it takes just 10s to restart.

session 1 - connection lost (as expected):

[Wed Nov 28 15:54:33 2012]> select * from init_test_tab;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR:
Can't connect to the server

[Wed Nov 28 15:54:45 2012]> select * from init_test_tab;
No connection. Trying to reconnect...
Connection id:    2
Current database: xxxxx

+------+
| x    |
+------+
| y    |
+------+
1 row in set (0.01 sec)

[Wed Nov 28 15:54:50 2012]>

Note that the procedure is invoked automatically (as expected) as the table is not empty anymore. But the insert should have been executed only after the sleep(300) call, not immediately (or, to be accurate, just in a few seconds).

So now I'm trying to understand:

If I put a procedure call (or whatever application code) in the init-file, will it be executed as expected (in my test case the sleep call seems to be skipped)?
Could I have some kind of logging of what gets executed (apart from modifying the code itself)?
If the code in the init-file takes time, could that affect the availability of the MySQL instance right after the restart?

Thanks
Dimitre


texts: Why MySQL Workbench creates more than two foreign keys for many to many relations?

texts:
I'm using MySQL Workbench. I know that an additional table is needed to define a  many to many relation. But why sometimes workbench creates this table with more than two foreign keys?
Is it added managing performance issues?
How should I make queries with this structure?

For example this many to many relation is created automatically with three foreign keys:


texts:
I have installed pgagent on centos. Now I am trying to connect it to postgres database. I ran:

/usr/bin/pgagent_94 -l 2 host = 127.0.0.1 dbname = postgres user = postgres port = 5432 password=mypass

I get error:

FATAL:  Ident authentication failed for user "postres"


texts: Comparing values from two tables in a nested where clause.

texts:
This is a rather specific question. I'm working to create row restrictions in Business Objects and the customer wants records to fall within a certain range from another table. The problem is, you can only select one table. A work around I found was that I can select other tables in the WHERE clause.

For Example:

a.RE_UID = b.RE_UID

I have to use:

a.RE_UID IN
      (select A.RE_UID
       from
        TABLE1 A
        join
        TABLE2 B
        ON
        A.RE_UID = B.RE_UID
      )

Where I'm stumped is that I need compare off another table with a BETWEEN. Something like:

A.CL_FROM_SVC_DTE BETWEEN B.EFF_DTE AND B.END_DTE

If I just try like I did above I get errors that it expected another character after WHERE:

SELECT *
FROM
  TABLE2
WHERE IN
(select A.CL_FROM_SVC_DTE
FROM TABLE1 A
JOIN TABLE2 B ON A.RE_UID = B.RE_UID
)
BETWEEN B.EFF_DTE AND B.END_DTE


texts:
I have Visual Studio 2013, and SQL Server 2012. From what I'm reading, it seems that the SSDT add-in for VS2013 only works for SQL Server 2014. Is that correct?

I've installed it, and I built a SSIS package that runs fine from Visual Studio. But when I attempt to import it to SQL Server, I get a generic

  0xC0010014 "One or more error occurred...This occurs when CPackage::LoadFromXML fails...

My guess is the DTSX format isn't readable by SQL Server 2012.

Is there something I can do to make VS2013 save in SQL Server 2012 format, or another way to import my package to SQL Server? I'd rather not have to install VS2012 on this machine since I've already got 2013.


texts:
I am curious about what the 3 parts of a Log Sequence Number (LSN) are and how they are generated.

A log sequence number is an ever-increasing, three-part number that uniquely defines the position of a log record within the transaction log. Source

I looked around a bit, and found at least one answer that said I should "not be concerned with how these are generated" but I still want to know.
If I run DBCC FILEHEADER ([AdventureWorks2014], 1) I get results that show me some important LSN's in my AdventureWorks

MaxLSN
45000000001600001
FirstLSN
44000000084000074
RedoStartLSN
45000000001600001

They do seem to have 3 distinct parts, what are they, what do they mean, and how are they generated?


texts: how to design a good database design which suits for both RDMS and noSQL

texts:
At present I am working in a startup company. There I am involved in both development and in database design. For this project we are using MySQL. In future we may migrate it to noSQL things like MongoDB or some other based on the traffic. So here my doubt is what should we keep in mind when designing a database, that should be fit for both RDBMS and nosql databases?

I know RDBMS and noSQL are entirely different. But what points should be considered when designing a database? How to give a good database design which will suit for RDBMS and noSQL?


texts: How to make SQL query to calculate the range from permutation of 123

texts:
ex: select 123 from dual; --123!

Result

123
 132
 213
 231
 321
 312

6 rows return


texts:
I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that time onwards i am getting this error. I refresh the database, make a copy of it, delete it & try to attach it again and thus the error occurs.

The log cannot be rebuilt because there were open transactions/users when the database
was shutdown, no checkpoint occurred to the database, or the database was read-only. This  error could occur if the transaction log file was manually deleted or lost due to a hardware  or environment failure.

If i use the log file while attaching the db, i got this error

The log scan number (1224:41:1) passed to log scan in database 'test' 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.

Kindly help
Thanks


texts: Postgres restore from WAL-E backup never finishes

texts:
Doing a restore on a WAL-E backup to S3, postgres never seems to complete the restore process. The log repeats the following:

< 2016-04-08 13:41:55.801 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:55.801 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:56.809 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:56.809 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:57.812 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:57.812 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:58.815 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:58.815 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:59.817 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:59.818 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:42:00.820 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:42:00.820 PDT >FATAL:  the database system is starting up
lzop: <stdin>: not a lzop file
wal_e.blobstore.s3.s3_util WARNING  MSG: could no longer locate object while performing wal restore
        DETAIL: The absolute URI that could not be located is s3://groupsiopostgres/main/wal_005/0000000100000A8000000071.lzo.
        HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration.
        STRUCTURED: time=2016-04-08T20:42:01.071643-00 pid=28784

The URI does increase approximately every minute. I understand these aren't errors. But psql continues to in this loop until systemctl kills it for taking too long.

Here's my recovery.conf file:

restore_command = '/usr/local/bin/envdir /etc/wal-e.d/env /usr/bin/wal-e --terse wal-fetch %f %p'
standby_mode = 'on'
trigger_file = '/var/lib/pgsql/9.3/data/failover'

How can I get postgres to complete before systemctl kills it?


texts: Why is MySQL database data-files deployed under /var/lib/mysql?

texts:
I was wondering why do we deploy data files under /var/lib/mysql/. Is there a logical reason for that, or is that just a "traditional" place for the data?

I will value more answers based on standards like the LSB or the POSIX, and would appreciate links to the policies of your favourite Linux or Unix implementation that dictate this is the expected place to deploy your data.

Many thanks in advance for your attention and answers.

Kind regards,

-- LMC


texts: Which database is designed for storing and incrementing values?

texts:
We're trying to build a stats server that stores visits for each document on a section for a specific date, so we need a table that has the following parameters:

documentID
sectionID
date
visits

The plan is then to increment the visits field.

Now, we tried to do this using MongoDB (albeit a very simple instance of it) and it turns out this is too write intensive for it. Is there another database system that's better suited for this task? Or should we just start growing the MongoDB infrastructure that we're using?


texts: SQl query to create a report based on multiple relational tables

texts:
I have an e-commerce website and I'm in the process of creating a sales report. These reports need to be divided by year and month.

Each sale made on the website will generate a relational link between multiple tables, where I need to get some values to display the proper report. Besides showing just what items were sold, I also need to show, for example, how many of those sales were made using credit card, money, etc... How many of them were shipped via postal office, third part services, etc.

Before I continue, keep in mind I'm not an advanced user in dataBases/BackEnd, so any improvement/suggestions is very welcome.

I'm using PHP/SQL on the backend and a MySql database with a model as the following image:

The basic information I need to know is (all of these are the total for each month):

Value: The total amount of sales;
Sales: Total sales (based on orderStatus);
Payment: Total sales using each payment type;
Delivery: Total sales using each delivery type;
Reported sales: Total sales reported by user (based on orderStatus);
Canceled sales: Total sales canceled (based on orderStatus);

I was able to built this report, but It seems to be very poor and/or not the right way to do this. It felt very messy, so this is why I'm here to ask your help to guide me on how to build this type of query and to improve my code.

To create the segment by Year/Month I'm using this code:

SELECT DISTINCT YEAR(created) as 'year' FROM order
for ($i=1; $i<=12; $i++) { ... }

And this is the code I'm running inside the for to get the sales for each month:

//Range between 3-6 are status of OK (shipped, delivered, taken)
$query = "
    FROM order a, orderStatus b
    WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i'
    AND b.id = a.id_status AND b.status >= 3 AND b.status <= 6
";

$totalAmount = sql("SELECT SUM(a.totalValue) as 'totalValue' $query"); //Total amount in $
$totalSales  = sql("SELECT COUNT(a.id) as 'totalSales' $query");

//Total by delivery type
$totalDelivery = sql("SELECT COUNT(a.id_delivery) as 'totalDelivery' $query AND a.id_delivery = 1");
$totalTaken    = sql("SELECT COUNT(a.id_delivery) as 'totalTaken' $query AND a.id_delivery = 2");

//Total by payment type
$totalOnline = sql("SELECT COUNT(a.id_payment) as 'totalOnline' $query AND a.id_payment = 1");
$totalCard   = sql("SELECT COUNT(a.id_payment) as 'totalCard' $query AND a.id_payment = 2");
$totalMoney  = sql("SELECT COUNT(a.id_payment) as 'totalMoney' $query AND a.id_payment = 3");

//Total canceled/Reported sales
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 7");
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 8");

As you can see, the sql works, but it's not very optimal. Also, It feels like I'm repeating myself a lot.

For example, I created a variable with the most common piece of code used in each query, most of them need to match an specific range of orderStatus, and I need to get the total count of those informations. With the exception of the canceled and reported sales, which are of a different orderStatus. Besides that, i'm creating a manual check on the delivery and payment status, it could be somehow automated.

The way I'm doing will also return every type of result, for example, if there were no payment with money, it will execute the query and return 0. This isn't needed, as I can check for a valid and existing information on the FrontEnd. If this can be done, great, but if not, it's not a big problem to get only when there is data stored.

So, how can I optimize this code, or even my database structure in order to have a better result/query to generate these sales report?


texts: Identifying root cause of slow query outliers from pt-query-digest?

texts: I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of these sporadic slow queries. Is there another tool I might use to shed more light on this situation? What might cause the same query to only be slow some of the time?


texts:
I need to setup database replication (for high availability) on a database that uses FileStream. This is on SQL Server 2012 Standard Edition, so Always On or Peer-to-Peer replication is not an option. Also, database mirroring does not work with FileStream.

The only option I have left is transactional or merge replication. I have found that both replication types work fine when all machines are on, but what happens if one machine is off?

This is what I have (servers run on Microsoft Azure as virtual machines):

Server A is DC server
Server B is SQL Server 2012 Standard and Application server (IIS)
Server C is SQL Server 2012 Standard and Application server (IIS)

Microsoft performs maintenance at least once a month, where at most one of the servers is turned off each time. How can I set up replication with these machines so I can achieve HA when one server is down?


texts: find out why table insert is slow in postgres cluster

texts:
Recently i did create a PostgresQL-HA 11, cluster using bitnami helm charts.
There is 1 master and 1 replica, pg_pool and metrics.
I am using NFS as file storage for the cluster and it is connected to a vmware guest machine with enough memory and cpu and a HP fast hard disk drive, in same broadcast domain on same VSwitch with a 1Gbps v-port.
But insert into database using a js written batch script is slow like this:
Insert 2000 records in 13.952 seconds.
The script inserts very fast on my local non-clustered postgresql database, on my local machine and inserts 2000 records in less than a second.
So since I am not a DBA or someone like that, how i can find why it is slow? can metrics or sth else help?
This is the output of pg_test_fsync on postgress master node when the cluster was idle:
p1-postgresql-ha-postgresql-0:/bitnami/postgresql$ pg_test_fsync -f c.o
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                         3.918 ops/sec  255261 usecs/op
        fdatasync                             2.874 ops/sec  347913 usecs/op
        fsync                                 2.905 ops/sec  344256 usecs/op
        fsync_writethrough                              n/a
        open_sync                             2.379 ops/sec  420340 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                         1.932 ops/sec  517651 usecs/op
        fdatasync                            23.182 ops/sec   43137 usecs/op
        fsync                                37.396 ops/sec   26741 usecs/op
        fsync_writethrough                              n/a
        open_sync                            15.793 ops/sec   63321 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
         1 * 16kB open_sync write            34.263 ops/sec   29186 usecs/op
         2 *  8kB open_sync writes           15.586 ops/sec   64159 usecs/op
         4 *  4kB open_sync writes            9.276 ops/sec  107799 usecs/op
         8 *  2kB open_sync writes            3.959 ops/sec  252602 usecs/op
        16 *  1kB open_sync writes            1.914 ops/sec  522347 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
        write, fsync, close                  16.808 ops/sec   59497 usecs/op
        write, close, fsync                  70.123 ops/sec   14261 usecs/op

Non-sync'ed 8kB writes:
        write                               107.174 ops/sec    9331 usecs/op


texts: Take average of multiple nullable columns

texts:
I have an example table (year) as below in PostgreSQL 9.5:

Name | 2010 | 2011 | 2012
-------------------------
A    |   10 |      |   40
B    |   10 |   20 |   30

Now, if I write a simple query as shown below to take average for columns (2010, 2011, 2012) I will get the correct result for B but result for A will be NULL because of the NULL in the 2011 column:

select (2010+2011+2012)/3 as avg from year

Is there any way to write a query so that I can take average of only non-NULLs in a row?


texts: AWR dbms_workload_repository ash_global_report_text extract text from output

texts:
I want to build a scheduled job in Enterprise Manager to trigger every hour the following query:
SELECT output FROM TABLE(dbms_workload_repository.ash_global_report_text(584958394, Null, SYSDATE-1/24, SYSDATE, l_wait_class=>'Scheduler'));
I want to capture Events and the percentage only if I get one with Scheduler resmgr:cpu quantum
And then I want to parse this output only out.
I get now the big varchar(320) column output when I hit the query, but I want also to build a PL/SQL package where I can add variables inside and also to parse out only the important percentage of the resmgr: cpu quantum event. Other events are not important to me.
Any help would be appreciated.
Kind regards.


texts: Insert data in current table and update another table MySQL

texts:
I'm having problem with inserting in purchase table and updating facility table. Let's say, user made a purchase with product_id and product_quantity.

When user hit submit, I want to insert product_id and product_quantity into purchase table. At the same time, updating facility table with product_id and product_quantity that associated with it.

Here is my code

<?php
include 'dbconn.inc.php';
include 'functions.inc.php';

$sql1 = "SELECT * FROM facilities";

$res = $mysqli->query($sql1);
$facilities = array();

while( $row = $res->fetch_array(MYSQLI_ASSOC) ){

$facilities[]['id'] = $facilities_id;
$facilities[]['product_id'] = $facilities_product_id;
$facilities[]['product_current_quantity'] = $product_current_quantity;
}

$id = $mysqli->real_escape_string ($_POST['id']);
$purchase_id = $mysqli->real_escape_string( $_POST['purchase_id'] );
$facility_id = $mysqli->real_escape_string( $_POST['facility_id'] );
$product_quantity = $mysqli->real_escape_string( $_POST['product_quantity'] );

$sql1 = "UPDATE facilities
    SET

`product_current_quantity` = '$product_quantity + $product_current_quantity'

    WHERE $facility_id = $facilities_id AND $id = $facilities_product_id ";

$sql = "INSERT INTO purchases
(
 `purchase_id`,
 `facility_id`,
 `product_quantity`,,
 `product_id`
 )
VALUES
(

 '$purchase_id',
 '$facility_id',
 '$product_quantity',
 '$id'

 )";

$ok = $mysqli->query( $sql );

$ok1 = $mysqli->query( $sql );

if( $ok && $ok1){
redirect( 'new_purchase.php' );
} else {
echo 'Couldn\'t add new purchase';
}

I did some research and I think I need to use triggers. But I never work with triggers before. Any helps would be great. Thank you!


texts: Database having only db_datareader shows invalid properties for a index

texts:
If a SQL server database user has only public permission in server roles and for a particular database only db_datareader and public permission then while trying script index as CREATE To option in SSMS, the script generated will not be correct.

I mean if an index has filtered condition then that filtered condition is not displayed

So for a user who has owner permission script index as CREATE To will show

USE [test]
GO

/****** Object:  Index [filtered]    Script Date: 12/30/2013 18:54:19 ******/
CREATE NONCLUSTERED INDEX [filtered] ON [dbo].[Table_1]
(
    [b] ASC
)
WHERE ([Table_1].[b] IS NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

For a user who only has db_datareader permission script index as CREATE To will show

USE [test]
GO

/****** Object:  Index [filtered]    Script Date: 12/30/2013 18:55:13 ******/
CREATE NONCLUSTERED INDEX [filtered] ON [dbo].[Table_1]
(
    [b] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

It would be OK if a user with db_datareader is not allowed to see the script of an index and shows access denied error. But it shows invalid script. Also if index properties is checked then only filtered condition is not shown and other properties of index are shown. Why this happens?


texts: Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU

texts:
My investigation found the following query is causing high Lck_M_U wait. I noticed there are multiple instance of this query running obviously competing for the same resource. I wonder if there is anything I can do with this query (listed below) to minimize the wait?

UPDATE LBMebrs
                SET DelayedPoints = 0, Points = ISNULL(computed.Points, 0)
                FROM LBMebrs Le1
                LEFT JOIN
                (
                    SELECT lm.UserId, SUM(ua.Points) Points
                    FROM LBMebrs lm
                    INNER JOIN LBrds l ON l.Id = lm.LBrdsId
                    INNER JOIN UserActivities ua ON ua.UserId = lm.UserId
                    WHERE l.Id = @LBrdsId AND ua.Pub BETWEEN l.StartDate AND l.EndDate
                    GROUP BY lm.UserId
                ) computed ON computed.UserId = Le1.UserId
                WHERE Le1.LBrdsId = @LBrdsId

Executions Plan:

https://www.brentozar.com/pastetheplan/?id=SJ0byVETr

Indexes defined on LBMebrs table:

Indexes defined on UserActivities Table

Indexes defined on LBrs Table

Multiple update queries running on instance (only once instance on whole server)

Waits on Server:


texts: Connecting to a self signed mongodb server though mongo shell doesn't require `pem` file

texts:
I am using nodejs mongodb driver to connect to a self signed mongodb instance. The MongoDB and Mongo Shell version is 4.0.0. Below is the command to launch a self signed mongodb instance:

mongod --port 27018 --sslMode requireSSL --sslPEMKeyFile mongodb.pem  --dbpath data

when I connect to this server with mongo shell, I can use below command without pem file:

mongo --port 27018 --ssl --sslAllowInvalidCertificates

I wonder what the PEM file is used for in the connection.


texts:
I have a table on PostgreSQL 10 that looks like:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES (
  1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "group2",]},
                       {"name": "perm-name-2", "who":["group1", "group3"]}]');
INSERT INTO forms (id, form) VALUES (
  2, '"permissions": [ {"name": "perm-name-1", "who":["group5", "group6"]},
                       {"name": "perm-name-2", "who":["group7", "group8"]}]');

The JSON for column "form" has more key-value pairs which are irrelevant here.

I need to query based on the permissions "name" and one or more "who" values e.g. :

Get all rows whose permission "name" is "perm-name-1" and "who" is any of "group1", "group5" should match all the rows of the example above.
Get all rows whose permission "name" is "perm-name-1" and "who" is just "group1" should match row with id 1.

Also, any insight/comment on a JSON schema which would help the database to perform better is welcomed!


texts: Do indexes on primary keys default to fillfactor of 90%, and should they be set to 100%?

texts:
Does Postgres automatically set the fillfactor to 100 instead of 90 when an index is made on a primary key column? If not, is there any reason not to manually set it to 100%?

https://www.postgresql.org/docs/current/static/sql-createindex.html

  B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected.


texts: Increase shared_buffers without restarting PostgreSQL

texts:
According to the official PostgreSQL documentation:

shared_buffers (integer)
Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (128MB), but might be less if your kernel settings will not support it (as determined during initdb). This setting must be at least 128 kilobytes. (Non-default values of BLCKSZ change the minimum.) However, settings significantly higher than the minimum are usually needed for good performance. This parameter can only be set at server start.

Is anyone aware of any way of increasing this parameter without restarting the process or (if you have to) without affecting an app that is making a bunch of batched UPDATE/DELETEs on the database?


texts: Granting specific permissions on ALL tables

texts:
I have a tutorial that users need to type in PostgreSQL commands. I don't give the script to users simply because it's a learning experience. Everything in the script below is typed in except for the grants for table data, which is imported.

There should be a way to grant the permissions needed without having to include all the table names. If I can cut all that out I can have the users actually type the grant commands in instead id inserting from a script.

Any help would be greatly appreciated...

create database archive;

create database snort;

create user snort with password 'l0gg3r';

create user base with password 'an@l1st';

\connect archive;

\i d:/winids/barnyard2/schemas/create_postgresql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql.sql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql_extra.sql;

GRANT ALL ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr, acid_ag, acid_ag_alert, acid_ip_cache, acid_event, base_roles, base_users TO base;

\connect snort;

\i d:/winids/barnyard2/schemas/create_postgresql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql.sql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql_extra.sql;

GRANT INSERT, SELECT, UPDATE ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr TO snort;

GRANT ALL ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr, acid_ag, acid_ag_alert, acid_ip_cache, acid_event, base_roles, base_users TO base;


texts:
I keep getting this error whenever I start MySQL.

121028  1:38:55 [Note] Plugin 'FEDERATED' is disabled.
121028  1:38:55 InnoDB: The InnoDB memory heap is disabled
121028  1:38:55 InnoDB: Mutexes and rw_locks use Windows interlocked functions
121028  1:38:56 InnoDB: Compressed tables use zlib 1.2.3
121028  1:38:56 InnoDB: Initializing buffer pool, size = 16.0M
121028  1:38:56 InnoDB: Completed initialization of buffer pool
121028  1:38:56 InnoDB: highest supported file format is Barracuda.
121028  1:38:57  InnoDB: Waiting for the background threads to start
121028  1:38:58 InnoDB: 1.1.8 started; log sequence number 3137114
121028  1:38:58 [ERROR] Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL
121028  1:38:58 [ERROR] Can't init tc log
121028  1:38:58 [ERROR] Aborting

121028  1:38:58  InnoDB: Starting shutdown...
121028  1:38:58  InnoDB: Shutdown completed; log sequence number 3137114
121028  1:38:58 [Note] C:\PROGRA~2\EASYPH~1.1\MySql\bin\mysqld.exe: Shutdown complete

I have already tried this.

I have an EasyPHP 12.1 setup on Windows 7x64 PC.


texts: How to add a check constraint in sql azure data warehouse?

texts:
I'd like to add a check constraint to a table in azure data warehouse.

I have the below script:

alter table [FactTbl_Test] add constraint Ck_Test_id check ([Test_Id] != 'Unknown')

This causes the below error:

  Parse error at line: 2, column: 74: Incorrect syntax near 'check'.

Edit:

Also appears to fail with the same error if the constraint is created inside the table declaration.


texts: How to compare data between staging and operational tables?

texts:
I just want to ask how to compare two database tables, staging vs. operational tables.

After we did the transformation and convert it to correct datatype like convert flag column (Y/N) nvarchar to bit or to int, how to compare data between the two tables?


texts: Defining OLAP or OLTP on table level in Oracle/postgres/SQLserver

texts: Usually companies have separate databases for OLTP and OLAP (Row stores and column stores) where data is loaded through ETL jobs to another database for analytical processing. I am particularly interested to know if there is any way we can define  which tables or objects to store for OLAP and which tables or objects to store for OLTP queries in any RDBMS like oracle or postgres on any level of granularity in the same database.


texts: Is there a good way to run a trigger for each record in a postgres table?

texts:
I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the replicated tables, and store it in the processed form that I need, while stripping out the records that I want to ignore.

Right now, after setting up a new replica, I run an update and set a value back to itself (eg, UPDATE tablename SET field=field) to force the trigger to run, but some of the tables are millions of records, and growing, and it can take 30min.  (and then there's the vaccuum, too).

Is there some better way to trigger it, or some way to write a function such that it'll work with either input passed in or NEW depending on calling context?  I'm reluctant to keep two different functions around, as I've seen too many times where one gets updated, and not the other.


texts: Effective duplication of DELETE ... LIMIT in Pg

texts:
I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable WHERE COND LIMIT X, but in Postgresql this is clearly not an option.

In some cases I do need to delete the last N records; in other cases it doesn't matter as long as they match the overall criteria. Other than primary key and timestamp, the records are identical.

So what is an (elegant) means of duplicating this functionality? It was suggested that I could do a join on ctid, but that doesn't strike me as an especially "nice" solution; is there something cleaner--and more portable--that I can do instead?


texts: I cant figure out the error probably with the date field in this mysql stored procedure

texts:
This is my empDetails table I am trying to create a stored procedure on.

mysql> describe empDetails;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(3)      | NO   | PRI | 0       |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
| dob     | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

  There is already a procedure with the same name I am trying to replace

mysql> DELIMITER //
mysql> create or replace procedure getEmpInfo(in n INT(3),out nam varchar(10),ou
t addres varchar(20),out db date)
    -> begin
    -> select name,address,dob into nam,addres,db from empDetails where id=n;
    -> end//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'proce
dure getEmpInfo(in n INT(3),out nam varchar(10),out addres varchar(20),out ' at
line 1

There seems to be a problem with the date field but I am new to mysql and I can't get this right.


texts:
I have the following query (in a Oracle Database):

          SELECT SUM (CANTITATE)
             FROM ANEXA2A_TABEL2_ROWS
            WHERE TIP_ACTIUNE = 'COLECTATE'
                  AND TIP_AMB = 'STICLA'
                  AND COD LIKE '%*'

The problem is that if I don't have any row with * in COD, the result is a empty cell and I want it 0. How can I do that?

Thanks!


texts:
i have a table with user id-s, and many times for the same users, like this:

id    time
1     13:00:01
1     13:00:01
2     13:00:09
1     13:00.23
1     13:00:24
2     13:00:11
2     13:00:11

I would like to get this dataset:

id    time
1     13:00:01
2     13:00:09
1     13:00.23
2     13:00:11

So i would like remove all the rows with the same id-time pair and with the same id, but where the time between the same user times is bigger than 20s. Can anybody help to me? I'm hanging on this problem for 4 days :(


texts:
It's the first time here so I don't know if it's the right place to ask.. I'm not good in database design, just learning, so I'd like some advice if possible.

The case:

I'm building a really small application, a plugin, who's tables will likely have not too much records in real life (less than 50 in extreme situations), so performance is not crucial.

The user can create n Groups. A Group should have a 'hook' field.
For each Group, the user can create n BannerBoxes. So, a Group can have many BannerBoxes, a BannerBox can have only one group. Therefore a 1-many relation.
There can be n languages, and the Language table is already in place and has id_lang field. The language-group relation is 1 (language)-many (groups)

I'll often have to ask the db for all BannerBoxes saved for a certain language, grouped by Group. So, first find all Groups of a language, then for each Group, find its BanneBoxes.

I've thought about 2 solutions, but I cannot decide which could be better, and since I'm definitly a newbe, both could be just wrong.

a. Group entity with [hook, id_lang] fields; BannerBox entity with an [hook] field (desides the others). So when selecting, I can join the Group table with Language table on id_lang and then join with BannerBoxes on hook field.

b. Only the BannerBox entity, in this case with both [hook, id_lang] fields. I could join with Language by id_lang and then group by hook.

I also guess that a Group_BannerBox table [id_group, id_bannerbox] would be useless since I think it's a solution for many-many relations, am I right?

So, what would you do? a. or b.?

Thank you!